Address database design (subjective?) I've been gathering information about how addresses in different countries are written. While doing that I've also been working on a database design to hold this world wide information most probably used for making deliveries.

I started off with GeoNames.org data and came up with a first design (the higher table has the lower table as a child) :

Continent
  Country
    City
      Address

With Currency (as a FK) and Language (with a many-to-many table) linked to the Country table. Also a many-to-many Capital table linking a Country and a City.

Soon I realized the US for example uses the state's code in their address. So an extra Province table was inserted between Country and City:

Continent
  Country
    Province
      City
        Address

Now I stumbled upon United Nations Code for Trade and Transport Locations (UN/LOCODE). This organization provides a list with all the cities in each country with some detailed information. This data fits pretty good with my 2nd design. I only renamed the Province table to SubDivision which is in the US the state, in Belgium the province and in France the department. This is very similar to the ISO 3166-2 codes.

Continent
  Country
    SubDevision
      City
        Address

To me, this looks like a good detailed database structure that won't need to be altered in the future. Like I said in the beginning, Currency is a FK in the Country table easy editable or removable. Language has a many-to-many relationship with Country and is also easy editable or removable. My biggest concern is that ISO also has more than 1 subdivision level fear certain countries. For Belgium for example there they have the provinces (provided in the UNLOCODE code list), but ISO (ISO 3166-2:BE) also has 3 regions that are higher than the provinces in Belgium and are pretty much in-between the Country and the Province.

Should additional (optional) tables be added? How this would be implemented is a question to me though.
Another database design I can think off is all loose tables, no FK's, and 1 table linking all required tables together with following columns: AddressID, CityID, SubDiv1ID, SubDiv2ID, ... , CountryID, ContinentID. Not sure if this is a good approach.
Add a different table per Country based on their amount of SubDivisions. This means Continent and Country and Address stay unchanged. City will be Country specific with the US for example having a USState (SubDiv 1) -> City and Belgium BERegion (SubDiv 1) -> BEProvince (SubDiv 2) -> City.
I did a little more searching and found this answer.

  Consider creating lookup tables for city, state and country entities. The city/state/country columns of the address table then consist of FKs pointing to these lookup tables. This allows you to guarantee consistent spellings across all addresses and gives you a place to store additional metadata (e.g., city population) if needed in the future.

The answer will very much depend on the use you wish to make of the addresses. If you simply require a bunch of glyphs to print on a delivery docket then what you have looks good enough. The ADDRESS table will simply absorb the local variations in formatting, sequencing and line breaks. If you require to use this for, say, automated sorting and delivery route planning you will need a lot more detail than your five level hierarchy. If your purpose is to qualitative compare regions or subdivisions across countries then you must analyse that in detail and model at the level that suits that need.

Trying to chase down the One True Address model is a fool's errand. Leave that to each national postal organisation and concentrate on what adds unique value to your application.


Kerne module for Oracle 11gR2 RAC on Linux As per Oracle, it requires three Linux packages to run ASMLib:

oracleasm - kernel module
oracleasmlib-2.0 - ASMLib libraries
oracleasm-support-2.0 - Admin for ASMLib

This library versions are OS dependent, but my RAC is working fine without the oracleasm  kernel module.

 $ rpm -qa | grep 'oracleasm'
oracleasm-support-2.1.8-1.el6.i686
oracleasmlib-2.0.4-1.el6.i686

And

$ oracleasm listdisks
DB_DISK1
DB_DISK2
FRA_DISK1
FRA_DISK2
OCR_VOTE1
OCR_VOTE2
OCR_VOTE3

Someone can please guide why its running perfectly without having main kernel module?

Without knowing what kernel you're running, I can't say for sure, but my guess is that you're running a recent UEK kernel, and it has the kernel module built in.


How to set the first row of CSV data as column name in SQL Server? I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file?

If not, after reading the data from the csv (from second row), how can I add column names to it?

Any help/comments/suggestions are much appreciated.

Assuming you are importing a standard CSV, you can dynamically create the table as such:

DECLARE @sql NVARCHAR(MAX)
DECLARE @filePath NVARCHAR(MAX) = 'C:/SomeFolder/yourImportFile.csv'
DECLARE @tableName NVARCHAR(MAX) = 'yourTableName'
DECLARE @colString NVARCHAR(MAX)

SET @sql = 'SELECT @res = LEFT(BulkColumn, CHARINDEX(CHAR(10),BulkColumn)) FROM  OPENROWSET(BULK ''' + @filePath + ''', SINGLE_CLOB) AS x'
exec sp_executesql @sql, N'@res NVARCHAR(MAX) output', @colString output;

SELECT @sql = 'DROP TABLE IF EXISTS ' + @tableName + ';  CREATE TABLE [dbo].[' + @tableName + ']( ' + STRING_AGG(name, ', ') + ' ) '
FROM (
    SELECT ' [' + value + '] nvarchar(max) ' as name
    FROM STRING_SPLIT(@colString, ',')
) t

EXECUTE(@sql)

and then bulk insert the data as @scsimon suggested:

BULK INSERT dbo.yourTableName.
FROM 'C:/SomeFolder/yourImportFile.csv'
WITH (
    FORMAT='CSV',
    FIRSTROW = 2,
    ROWTERMINATOR = '\n',
    FIELDQUOTE= '"',
    TABLOCK
)


One or several tables for attributes I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something.

The overall goal is to create the ability to attach open-ended free-text attributes for each of three (possibly 4) entities.

Let's call the business entities Device, Location and Part; they have relationships between themselves.

Design A: Create DeviceAttribute, LocationAttribute and PartAttribute tables, each of which has an ID, Reference ID (FK to respective table), Name, Value and Type.

Design B: Create Attribute table with (ID, Name,Value and Type columns) and three reference tables - each holding a reference from one of the entity tables to one of the Attribute tables' IDs.

The concern is mostly about performance:
 - will 3 separate xxxAttribute tables perform better when querying only for an
   Entity's data, such as "give me Device X and all its attributes", or
   do both designs perform the same?
 - will 1 Attribute table (Design B) perform better when querying for entities with given attribute name/value, such as "give me all entities that have Attribute.Name='GPS'", or is it equivalent to querying a view that combines the 3 tables of Design A?
 - in case of Design B: does update on an entity table (Location, Device, Part) cause a lock on querying in other entity tables?

The system may have tens of thousands of Parts, thousands of Devices and hundreds of Locations and may have to process in the order of tens to few hundreds queries per second.

A couple of reasons I prefer a single table:

(1) with multiple tables you'll almost always be performing unions and you pay for the core data multiple times

(2) you can easily optimize for a subset of attributes or a combination of devices and attributes with indexes and use sparse columns to minimize wasted space

I wrote a bit about EAV here - it is by no means perfect, but we used it to solve many performance problems (at the cost of others) at a previous job.


MySQL Galera Cluster mass update delay I have migrated my single MariaDB setup to a 3 node Galera cluster. I am currently in staging and most of normal operations run fine.

I have one issue though: if I do a mass update of one column of one table (500K rows) then the new value is available on the node where I ran the SQL UPDATE immediately after the transaction is done while it takes several seconds to propagate to the other nodes.

I thought Galera cluster was 100% synchronous and thus suprised to see that as it means that users could see different data...

My conf is:

[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_flush_log_at_trx_commit=0
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="xxxxx"
wsrep_cluster_address="gcomm://192.168.0.100,192.168.0.101,192.168.0.102"
wsrep_node_address="192.168.0.102"
wsrep_sst_auth="sst:yyyyyy"
wsrep_sst_method=xtrabackup-v2
wsrep_on=ON

Is this really expected or is there something to tweak?

Thanks!

Galera replication is not synchronous, only what they call 'virtually synchronous'. It's synchronous only up to the point where the write set is to be applied.

  There might be a small period of time when a slave is out of sync.
  This happens because the master may apply events faster than the
  slave. And if you do read from the slave, you may read the data that
  has not changed yet.

  However, this behavior can be changed by setting the
  wsrep_causal_reads=ON variable. In this case, the read on the slave
  will wait until the event is applied (this will obviously increase the
  response time of the read). The gap between the slave and the master
  is the reason why this replication is called virtually synchronous
  replication, and not real synchronous replication.

Source: Multi-Master Replication (Percona XtraDB Cluster - this page also has a diagram which explains the problem).

Another approach is to define one of the nodes as the 'master', and then write only to that node. Critical reads should then also be directed at the 'master' node, whereas less-critical reads can go to any node.

PS: Note that if you plan to write to all the nodes, you might find the following article useful: Galera’s big gotcha for MySQL users.

Edit:

Note that wsrep_causal_reads=ON has been deprecated by wsrep_sync_wait=1.


Oracle Enterprise Manager Installation First I install oracle 11g database with this configuration

Step 2 of 9

I selected Create and configure a database

Step 3 of 9

I selected Desktop Class

Step 4 of 8

Global database name: Blitz

Administrative password: Pass1234

After that it gives me this information

Global Database Name: Bliz

System Identified(SID): Blitz

Server Parameter File name: C:\app\annie\product\11.2.0\dbhome_3\database\spfileBlitz.ora

The Database Control URL is https://localhost:5501/em

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.
The encryption key has been placed in the file: C:/app/annie/product/11.2.0/dbhome_3/localhost_Blitz/sysman/config/emkey.ora.
Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

Then configure this:

Username - Password

SYS      - Pass1234

SYSTEM   - Pass1234

Then I try to install Oracle Enterprise Manager 12c

First in Command Prompt I enter the following command to get the host name:

sqlplus / as sysdba

select host_name from v$instance

that gives me PC1

Then in Step 6 of 9 of Enterprise Manager Installation

I provide the following for Database Connection Details

Database Host Name: PC1

Port: 1523

Serivice/SID: Blitz

SYS Password: Pass1234

But I got this error

There was a problem connecting to the database on the host and port specified. Check to make sure the host is up or that the database listener is running on that host.

So I tried tried  to changed the Database Host Name to localhost

But I got this error

Enterprise Manager Database Control schema exists in the database. To proceed with the installation of Enterprise Manager Cloud Control, you must deconfigure the same by running the following command:

To deconfigure Database Control for a single instance database, do the following command on database host:
 (a) Set the environment variable <ORACLE_HOME> to the database home.(b) Run the following command:
<Database ORACLE HOME>/bin/emca -deconfig dbcontrol db -repos drop -SYS_PWD <sys password> -SYSMAN_PWD <sysman password>

To deconfigure Database Control for an Real Application Clusters (RAC) database, do the following command on database host:
(a) Set the environment variable <ORACLE_HOME> to the database home.(b) Run the following command:
<Database ORACLE HOME>/bin/emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD <sys password> -SYSMAN_PWD <sysman password>

NOTE: If the existing database is on different host, then install Oracle Management Agent on the database host, to monitor the database.

So in Command Prompt I enter this command:

set ORACLE_HOME=C:\app\annie\product\11.2.0\dbhome_3

C:\app\annie\product\11.2.0\dbhome_3\bin\emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD Pass123 -SYSMAN_PWD Pass123

Then it ask me for the details so I input the details.

Database Unique Name: Blitz

Service Name: Blitz

Listner port number: 1523

Listener Home_Oracle: C:\app\annie\product\11.2.0\dbhome_3

But I go this error:

And I'm now stack up with this. Please Help.

Why do you use the    -cluster - option ?
Is you database repository part of a cluster ?

Make sure first that you can connect to the database using the tns names.

Example :

sqlplus system/password@'tns_identified'

Then to deconfig you em repo :
Try using this syntax :

 emca -deconfig dbcontrol db -repos drop -SYS_PWD oracle -SYSMAN_PWD oracle

For some nice articles on how you can prepare you Oracle Enterprise Grid Control Monitoring environment check the link


Why earthdistance  operator returns different value than earth_distance function? Seeing a different value with <@> and earth_distance

with points as (
  select
    '(1,1)'::point as p1
    ,'(2,2)'::point as p2
)
select
  ((p1 <@> p2) * 1609.34::double precision) as d1
  ,(earth_distance(ll_to_earth(p1[1], p1[0]), ll_to_earth(p2[1], p2[0]))) as d2
from points

Result is:

157224.717583288 157402.326073052

Almost 200m difference - why is that?

They do different things,

earth_distance Returns the great circle distance between two points on the surface of the Earth.
<@> Gives the distance in statute miles between two points on the Earth's surface.

So this is the difference between the average of the two (assuming neither is correct) is given as,

SELECT ((d2-d1) / ((d2+d1)/2)) * 100 AS distance_diff FROM results;
   distance_diff
-------------------
 0.112652419817465
(1 row)

Which is 0.11%. Per the Great-circle distance on wikipedia

  The Earth is nearly spherical (see Earth radius) so great-circle distance formulas give the distance between points on the surface of the Earth (as the crow flies) correct to within 0.5% or so.

So which one is more accurate? <@>, clearly. But, we can do better as the docs say,

  In this module, the Earth is assumed to be perfectly spherical. (If that's too inaccurate for you, you might want to look at the PostGIS project.)

SELECT ST_Distance(
  ST_MakePoint(1,1)::geography,
  ST_MakePoint(2,2)::geography
);
   st_distance
-----------------
 156876.14940189
(1 row)

But if your coordinators are SRID 4326, the correct-most distance is 156876.14940189.


What is the potential fallout of changing SQL Server's log on account? I need to be able to read files from a UNC path in TSQL and am getting an access denied error.  I've read I must change the user SQL Server logs in as to a domain user, it's currently running as NT Service\MSSQLSERVER.

If I make the change, what potential issues should I watch out for, if any?

Changing the service account for SQL Server or changing the service account password (if you were already using a domain account) is common practice.

The NT SERVICE\MSSQLSERVER is a service account and not a formal user login in Windows. When you update the service account to a domain user account it will get linked to that service account and retain the permission required on the OS and file system.

Simply ensure any adjustment you make to the service account (change it or updating the password) is done via SQL Server Configuration Manager. This tool handles applying the permissions properly to a new service account.


SELECT query slowing down the wal replay in replication server While running a SELECT query (bit large query ~1GB of data output which took ~15minutes to execute) in hot standby replication host, its halts the wal archiving process. Once the Query done with the execution, the replication takes place and started replicating. Is that normal behaviour of postgres or need to tune system runtime configuration?

I have tried to change work_mem=64MB to work_mem=96MB, but still I couldn't solve the delay.

(using PG 9.5)

Replication can be delayed if a query on the standby needs data the master has removed. The WAL from the master with the resource removal cannot be replayed until the standby query finishes, or the standby query must be cancelled.

You can:

Let the standby cancel the query after a time limit - see the max_standby_archive_delay and max_standby_stremaing_delay parameters; or
Enable hot_standby_feedback on the standby so the master won't remove resources the standby needs while its queries are running. This isn't perfect, conflicts that delay replication or cause query cancels can still happen when the standby just recently started up. But it helps.


Minutely Log Backups - Missed Jobs I tried searching for this but couldn't find an existing question about this. I'm considering implementing Brent Ozar's suggestion to backup minutely for our critical databases. I don't have a great test environment where I can try this and get useful results. I'm wondering if the log backup job runs over one minute, what happens to the next minutely backup job(s). In these situations, I'm assuming the jobs will fail but I'm worried they'll queue up and I could wind up with a vicious circle where I can never get caught up on the backups. Can anyone else advise if how SQL Server will handle this situation? Also, if the jobs fail, does it fail "elegantly" (i.e., the backup chain is not broken and I can continue to restore using the logs) or will it bugger it up and break the backup chain?

Also, if I have full and differentials scheduled throughout the day as well, and a minutely log is running will it keep the full and diffs for running?

I appreciate any help you all can provide.

You did not mention your workload, but I run log backups every minute in our OLTP environment without issue (granted, we don't process thousands of transactions per second).  If a log backup happens to take over 1 minute, there is no failure.  The job will merely run again on the minute mark after the current job completes.

As Brent says in What happens to transaction log backups during full backups?

  Backups are beautiful things. You should take full ones, and probably
  differential ones, and if you’re in full recovery model, definitely
  log ones. How often you take them is up to you and your boss. Or maybe
  their boss. But it’s definitely not up to you. Unless you’re your
  boss.

  Log backups during full backups won’t hurt anything, and may end up
  helping things if your full backup fails, and you need to restore
  something.

  Log backups during full backups will not truncate the log. That has to
  wait until the first log backup after the full finishes.

Some additional information regarding Understanding Schedules and Long-Running Jobs, which I believe still holds true today:

  What happens when a scheduled job runs longer than the scheduled
  interval time period? For example, if a job is scheduled to execute
  every 10 minutes and takes 35 minutes to complete, does the job
  execute again while it's still running?

  On the SQL Server Most Valuable Professional (MVP) newsgroup, a
  private forum for SQL Server MVPs, Microsoft published two design
  facts about the SQL Server Agent that can help answer this question.
  First, the SQL Server Agent runs only one instance of a job at a time.
  Second, the SQL Server Agent calculates the Next Run Date at the end
  of the job. So, if a job execution takes longer than its scheduled
  interval, the job will rerun at the next recurrence point. For
  example, imagine that Job A is scheduled with a 1-minute recurrence
  interval. Job A starts at 1:25 p.m. and runs for 1 minute 45 seconds.
  Job A is still running at 1:26, when the next scheduled recurrence
  should happen, and the SQL Server Agent will run only one instance of
  a job at a time—so nothing happens at 1:26 p.m. When Job A ends at
  1:26:45 p.m., the SQL Server Agent resets the Next Run Date. Based on
  the specified 1-minute recurrence interval, the next time the job will
  run is 1:27 p.m.


Disable MongoDB journaling? Are there any benefits in disabling mongodb journaling besides performance gain?
I know journaling provides write durability and crash resiliency. But I can tune syncdelay parameter to make data files flush more frequently, but mongodb docs recommends against this. What effect would it have on my mongodb setup and why should I not set syncdelay on production setups?

Thanks,
Abhi

Are there any benefits in disabling mongodb journaling besides performance gain?

The only other marginal benefit is if you are using a 32-bit build (which is also not recommended for production use). Since 32-bit builds are limited to ~2Gb of addressable data for memory mapped files, the journal is off by default to allow for more data (with journal enabled, the 32-bit data limit is halved to ~1Gb). In all other cases, the journal is enabled by default. For 64-bit systems, the addressable virtual memory is not a limiting factor for data size.

Any performance gain from disabling the journal is a tradeoff for data & operational risk. I would strongly advise against disabling the journal unless your data is ephemeral (i.e. you don't care about losing it or can easily recreate it from another source of truth).

If you are concerned about possible contention or overhead from disk I/O of journaling, you could always move the journal to a separate volume. NOTE: moving the journal to a separate volume may also change your backup strategy, particularly if you were relying on filesystem snapshots to take a comparatively quick backup without having to fSyncLock() and quiesce your database.

  I know journaling provides write durability and crash resiliency. But I can tune syncdelay parameter to make data files flush more frequently, but mongodb docs recommends against this.

Writes to the journal are fast append-only operations. As you've noted, these provide durability and crash resiliency. If MongoDB stops unexpectedly, it can recover from the journal and data will be in a consistent state.

Without the journal, your data is in an unknown state after an unexpected shutdown and you need to validate it by running repair or (in the case of a replica set node) resync from a known-good copy of the data.

  What effect would it have on my mongodb setup and why should I not set syncdelay on production setups?

The syncdelay setting controls the frequency where the in-memory view is synced with the on-disk view of data (aka the "background flush interval").

By default, the journalCommitInterval is 100ms and the syncdelay (background flush) happens every 60 seconds.

If you adjust the syncdelay too low, you can end up creating a lot of additional I/O (and reduced performance) because writes to dirty pages can no longer be effectively batched together and the same page will be re-written multiple times. If you adjust the syncdelay higher, you can create I/O spikes (and reduced performance) with a large volume of changes being committed in a single batch.

The syncdelay and journalCommitInterval parameters are available as tuneable settings because in some cases it may make sense to adjust these (for example, to try to temporarily help an under-provisioned I/O system by reducing write spikes). In a healthy production system, it would be best to leave these settings as-is.

For more information, How MongoDB’s Journaling Works is a helpful read.


How to match regular expressions in Oracle? I'm using the following query:

select * from table
WHERE POSTAL_CODE LIKE '[a-Z][0-9][a-Z] [0-9][a-Z][0-9]';

However, this is not capturing anything. For example, a typical POSTAL_CODE looks like 'B3M 3K5'. Why would this not match?

You'd need to use regexp_like not like if you want to use regular expressions.  It sounds like you want

with x as (
    select 'A1B C2D' postal_code from dual union all
    select 'XXX 777' from dual union all
    select 'a1B 2C3' from dual
 )
 select *
   from x
  where regexp_like( postal_code, '^[A-Z][0-9][A-Z] [0-9][A-Z][0-9]$', 'i' );


Performance impact of a group by function call I have the following query, which for simplicity sake I use the simple example format below:

SELECT
  col1, col2, dbo.fun3(col3)
GROUP BY col1, col2, dbo.fun3(col3)

This took 30 seconds to run and returned 6000 records.
When I removed dbo.func3(col3) in both the SELECT and GROUP BY it sped up to only 5 seconds to return 6000 records.

So I guess my performance impact comes down to the following:

dbo.func3() is slow and need further work to improve its performance
The function call in the GROUP BY causes the slowness.

I wanted to isolate how slow is dbo.func3() is so I ran the following query without GROUP BY:

SELECT col1, col2, col3

versus

SELECT col1, col2, dbo.fun3(col3)

The first one returns around 900,000 records in 15 seconds.
The second one return 500,000 records in 15 seconds.

This prove I should improve my func3() function.  However, do you know of anything I can do in the group by part of the query to reduce the delays?

Is this function being called twice because I have it in the select and the group by portion of my query?

So I guess my performance impact comes down to the following

Since your question is about performance I advise you to start using performance tools that you already have at your disposal to dig into this problem. This way your guess will be more precise or better yet: you won't have to guess at all.

Display an Actual Execution Plan

SET STATISTICS TIME (Transact-SQL)

SET STATISTICS IO (Transact-SQL)

If after using these tools you are not able to figure out your answer, update your questions adding their result plus the function code and we'll try to help you further.

  However, do you know if anything I can do in the group by part of the
  query to reduce the delays?

Without your actual code it's not possible to help very much.

  Is this function being called twice because I have it in the select
  and the group by portion of my query?

It can be verified on the execuion plan.


What problem prevents well defined behavior of a foreign key on a non-unique column? In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem.

Is that correct?  If not, please explain to me why a foreign key's behavior on a non-unique column is not well defined.

Does behavior not well defined include that if a foreign key's referenced column is not unique then it will delete the keyed row even though there are valid entries?

You can't create a foreign key that targets a relation without a unique constraint in PostgreSQL. I'm somewhat astonished to hear that you can in MySQL.

It's really against the principle of a "foreign key" - if you can have multiple values, then the target is not in fact a key at all, it's just another data column.

That said, PostgreSQL isn't just preventing you from doing this for fun. AFAIK its foreign key implementation requires on unique b-tree indexes and cannot function properly without them. Unique indexes have some important properties, in particular regarding locking and transactional updates.

In theory I guess Pg could let you use any index, but it'd then have to deal with the mess of what happens when two concurrent transactions each delete one of two key rows. Each commit, individually, leaves the key relationship intact, but if both commit, one would have to trigger a cascade delete (which must happen before commit) or an error rollback. So PostgreSQL would have to serialize the transactions, forcing one to happen after the other, which would mean taking locks on all other rows with the same value for the key, irrespective of whether they're otherwise targeted by the query. Even then it wouldn't fully do the job because a concurrent insert could re-validate the key; Pg would really need to take predicate locks.

Implementation detail aside, what does a foreign key that targets a non-unique column actually gain you? Are you trying to model a non-optional m:n relationship?

If I had to do this, I'd do it with my own triggers, but I'd do it very carefully, as it's really hard to get it right.


SSAS - how to script the General settings? where are them stored? how can they be queried? I believe I am an administrator on server BIREPLON1 SSAS as you can see on the picture below.

These security permissions can easily be scripted.

this is an example:
apologies foe the horizontal scroll, but that's how it is.

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object />
        <ObjectDefinition>
            <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>SASBIDEV01</ID>
                <Name>SASBIDEV01</Name>
            </Server>
        </ObjectDefinition>
    </Alter>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object>
            <RoleID>Administrators</RoleID>
        </Object>
        <ObjectDefinition>
            <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>Administrators</ID>
                <Name>Administrators</Name>
                <Members>
                    <Member>
                        <Name>mycompany.CO.UK\BIUsersDEV</Name>
                    </Member>
                </Members>
            </Role>
        </ObjectDefinition>
    </Alter>
</Batch>

Is there a way to script the settings found on the "General" tab, as seen on the picture below?

otherwise,

where are these permissions stored?

how can they be queried?

The settings that you are looking for are stored in msmdsrv.ini

\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config\msmdsrv.ini

or <your Analysis service custom path>\OLAP\Config\msmdsrv.ini

Note: Above path applies for Default installation.

Its a good practice to create a backup of that file, if you are changing any config value.


MySQL InnoDB page_cleaner settings might not be optimal Seeing this note in mysqld.log:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)

There seems to be mention of something like this here:
MySQL instance stalling "doing SYNC index"

My question is: what action should be taken, if any, when this note is seen in the logs?

MySQL and OS versions:
mysql-community-server-5.7.9-1.el7.x86_64
centos-release-7-1.1503.el7.centos.2.8.x86_64

Running SHOW VARIABLES LIKE 'innodb%'; as suggested shows:

innodb_page_cleaners | 1

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances

Check innodb_buffer_pool_instances with:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_instances'

You can only set innodb_page_cleaners as high as innodb_buffer_pool_instances. If you want innodb_page_cleaners=4 then you also need innodb_buffer_pool_instances=4.


How to change the reporting services report service mode from Native to integrated? In SSRS-2016, I am trying to change the Reporting  services report service mode from Native to integrated.

I added 2 new shared features to existing installation (See in the Pic)

I am trying to change the report server mode from native to integrated. I opened Reporting services configuration manager and add new report serve and add a database I am seeing just native mode option.

I am not seeing integrated option.

could you please suggest me on how I can make the report server mode to integrated?

I don't see that option. (I restarted after installing the 2 features)

You might be able to do this by creating a new ReportServer database and choosing integrated mode as mentioned here
rather than going through a full re-install.


How can I take backup of particular tables in SQL Server 2008 using T-SQL Script I want to take a backup of particular tables available in my database in a .bak file, and all these should be done using a T-SQL script.

Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. But there is a workaround  for this

Taking backup of SQL Server table possible in SQL Server. There are various alternative ways to backup a table in sql SQL Server

BCP (BULK COPY PROGRAM)
Generate Table Script with data
Make a copy of table using SELECT INTO
SAVE Table Data Directly in a Flat file
Export Data using SSIS to any destination

Here I am explaining only the first one rest you might be knowing

Method 1 – Backup sql table using BCP (BULK COPY PROGRAM)

To backup a SQL table named "Person.Contact", which resides in SQL Server AdventureWorks, we need to execute following script, which

-- SQL Table Backup
-- Developed by DBATAG, www.DBATAG.com
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want    to backup
SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

OUTPUT

Note -

You must have bulk import / export privileges
In above Script -n denotes native SQL data types, which is key during restore
-T denotes that you are connecting to SQL Server using Windows Authentication, in case you want to connect using SQL Server Authentication use -U -P
This will also tell, you speed to data transfer, in my case this was 212468.08 rows per sec.
Once this commands completes, this will create a file named "AdventureWorks.Person.Contact_20120222" is a specified destination folder

Alternatively, you can run the BCP via command prompt and type the following command in command prompt, both operation performs the same activity, but I like the above mentioned method as that’s save type in opening a command prompt and type.

bcp AdventureWorks.Person.Contact out C:\MSSQL\Backup\AdventureWorks.Person.Contact_20120222.dat -n -T


Postgres migration local to remote, issue with terminal command I'm trying to use the first answer here to transfer a local postgre db to elephantSQL

https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server

The command given is: pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

I have no chance to enter a password in the following:

owner@G700:~$ pg_dump -C -h localhost -U rep1 repdb1 | psql -h baasu.db.elephantsql.com -U bgpacbpe bgpacbpe
Password:
Password for user bgpacbpe: pg_dump: [archiver (db)] connection to database "repdb1" failed: fe_sendauth: no password supplied

How can I resolve this?

If there is a less error prone way to do this I would also like to try that.

owner@G700:~$ psql --version
psql (PostgreSQL) 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

Having two programs in the same pipeline both trying to prompt for a password at the same time is confusing your terminal.

There may be a shell-specific way to fix this, but I'd fix it at the PostgreSQL level by either changing the local server to use an authentication method that doesn't need a password, or making a .pgpass file to accommodate the local connection (and maybe the remote connection, too).

For what it is worth, a shell way to get around this would be:

pg_dump -C -h localhost ... | (sleep 10; psql -h ...)

Where the sleep gives you enough time to enter the local password.


JOIN on LIKE when input udtt count is > 1 This query accepts a User-Defined Table Type variable and then joins that with a table.  If the input is a single record, I want the join to be LIKE '%'+input+'%', otherwise it should be =input.  This is essentially what I have now, but it's quite slow for larger inputs (testing around 500 takes around 5min).

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )
SELECT b.* FROM @inputUDTT a
INNER JOIN Tabl b ON (@inputCount>1 AND b.name = a.val)
    OR (@inputCount<2 AND b.name like '%' + a.val+ '%')

I've also tried having the SELECT COUNT(*) directly in the join and formatting the condition as a CASE WHEN switch, but neither helped.  I know I could alter this into a dynamic query, but that seems excessive for something that seems like it should be fixable in a simpler method.

I'd start by breaking this out to get a better idea of which part is slow. This should clarify the execution plan overall.

From there you can start sorting out other techniques, like temp tables, recompile hints, indexes, etc.

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )

SELECT b.*
FROM @inputUDTT a
INNER JOIN Tabl b
ON (@inputCount > 1 AND b.name = a.val)

UNION ALL

SELECT b.*
FROM @inputUDTT a
INNER JOIN Tabl b
ON (@inputCount < 2 AND b.name like '%' + a.val+ '%')

For example, in the first query where you have an equality predicate, the optimizer can choose different join types (though it may only ever choose nested loops because of the low row estimates for a table variable. In the second query, the only join type available with no equality predicate is nested loops.

Hope this helps!


Character Set Encoding in a Table I have the following data:Trollhättan

If I print out and setting up the header to utf-8, then this is the output in the browser.
If I'm not setting up the utf-8 in the header, TrollhÃ¤ttan. However,when I'm storing the data in the database and checking through phpmyadmin, I am getting the following string : TrollhÃƒÂ¤ttan.
When I am setting up the header to utf-8, I'm getting this string : TrollhÃ¤ttan beside this Trollhättan.

Since the table is latin1_swedish_ci, do I have to use in the table UTF-8?

You need to make sure that when you are "storing the data in the database", the client you use is using utf8. Then setting the http header to utf8 completes the round trip.

The surprise is that it doesn't matter much what character set is used in the database (although it might well be convenient for that to be utf8 too).


Does canceling update operation perform automatic rollback I ran into a bit of confusion. I'm under the belief that database operations are automatic - that is either the query succeeds or the transaction is automatically rolledback. For example - if you have a long running UPDATE statement and you "cancel" it before it finishes - IE clicking red circle X button in SQL Developer (which you should receive a ORA-01013: user requested cancel of current operation error). Upon canceling the query the query is rolled back and no rows were affected. I believe the same should hold true for DELETE statements.

I looked around for some documentation on it and I found this article that really isn't authoritative.

If you do that, the statement is rolled back, not the transaction. Below I hit Ctrl+C after starting the UPDATE.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

23849 rows created.

SQL> insert into t1 select * from t1;

47698 rows created.

SQL> insert into t1 select * from t1;

95396 rows created.

SQL> insert into t1 select * from t1;

190792 rows created.

SQL> insert into t1 select * from t1;

381584 rows created.

SQL> commit;

Commit complete.

SQL> conn bp/bp
Connected.
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                                   0
transaction rollbacks                                                     0

SQL> set timing on
SQL> update t1 set object_id = object_id + 1;
update t1 set object_id = object_id + 1
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:01.51

SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                              173323
transaction rollbacks                                                     1

Elapsed: 00:00:00.00
SQL>

1 transaction was rolled back and 173323 undo records were applied to perform the rollback. But that is because I had no other change in that transaction.

If I have other changes in the transaction, those changes will not be rolled back automatically:

SQL> conn bp/bp
Connected.
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                                   0
transaction rollbacks                                                     0

Elapsed: 00:00:00.00
SQL> insert into t1(object_id) values (-1);

1 row created.

Elapsed: 00:00:00.00
SQL> select count(*) from t1 where object_id = -1;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.09
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                                   0
transaction rollbacks                                                     0

Elapsed: 00:00:00.00
SQL> update t1 set object_id = object_id + 1;
update t1 set object_id = object_id + 1
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:02.16

SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                              274294
transaction rollbacks                                                     0

Elapsed: 00:00:00.01
SQL> select count(*) from t1 where object_id = -1;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.04
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> select count(*) from t1 where object_id = -1;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.06
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                              274295
transaction rollbacks                                                     1

Elapsed: 00:00:00.00

Notice how the value of transaction rollbacks remained 0 this time, and how the INSERT was not rolled back, and I was still able to rollback the transaction manually.


Data missing in PostgreSQL dump I'm trying to verify that I am actually getting data in my pg_dump from a PostgreSQL 9.3 server. I'm specifically running the following to get a dump of my Foreman database:

pg_dump -Fc foreman -U foreman -h foreman-db -p 5432 > foreman.sql

I am reading the dump using pg_restore -l foreman.sql but when I search for known fqdns of hosts that should be in the hosts table, I can not find them.

I'm no DBA, but it seems I'm doing something very wrong or I don't know how to search well enough yet to find data within pg_dump's.

Any assistance is much appreciated.

This turned out to be my own fault and not making sure that the Foreman host had a postgresql93 client installed so that the rake task that imports the database could actually do the pg_restore on the valid dump.

I was able to use pg_dump -Fp to get the plain text and validate that my data was in there. Thank you everyone for your help. Chalk this one up to a newbie trying to learn Postgres.

Cheers.


SQLite and Write Ahead Logging: why so large files I have been doing some operations on SQLite databases recently.

The other day I ran a command to DELETE a bunch of rows from a 1GB database file and the WAL file got over 8GBs at which point it filled my partition.

Why did it do this?  How can I stop it?

Here are some of the PRAGMA's that are setup for this database:

PRAGMA wal_autocheckpoint = 1000
PRAGMA page_size          = 1024
PRAGMA max_page_count     = 1073741823
PRAGMA journal_size_limit = -1
PRAGMA journal_mode       = WAL
PRAGMA auto_vacuum        = 0

What can I change to stop WAL files getting so big when I want to make the database smaller because it is starting to eat my disk space?  And will such a thing affect performance?

I do know the library was custom compiled, but I am not sure of the compilation time options.  Can this be found out somehow?

This is running on and SSD in Debian Linux  if that is relevant.

The WAL file records all changes to the database since the last checkpoint.

(Automatic) checkpoints cannot run when the database is locked, so ensure that there is no other connection with an active transaction.

The documentation says:

  By default, the auxiliary write ahead log and shared memory files used for transaction control are automatically deleted when the latest connection to the database closes.

So ensure that all database connections are closed at some time.

(If you have disabled deleting WAL files with SQLITE_FCNTL_PERSIST_WAL, you have to set PRAGMA journal_size_limit to a nonnegative value to truncate the WAL file to zero bytes in that situation.)


Why is Microsoft SQL Server creating a ton of dump files? How to determine what is causing Microsoft SQL Server to create dump files over and over taking up 170 GB of disk space? How do I troubleshoot what is causing the dumps?
The path of the dump files is
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSQL\Log\Polybase\dump
Can I delete these?
I have looked at The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?) but it doesn't really answer my question - it mainly states to contact Microsoft.

You can check the SQL Server Error Log to get more information as to why dump files were generated. Microsoft also provides the sys.dm_server_memory_dumps dmv for troubleshooting, but it isn't clear to me how that dmv would be helpful.


TDE cannot encrypt database In order to encrypt a SQL db, I run the following commands:

CREATE CERTIFICATE <certname> ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
WITH SUBJECT = 'certificate subject', EXPIRY_DATE = '20291031';
go

USE <databasetoencrypt>;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <certname>;
GO

When trying to execute the CREATE DATABASE ENCRYPTION KEY command, I receive this message and don't know what to do:

  Cannot use certificate 'name', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

When I omit the ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>' during the CREATE CERTIFICATE command, it works. However, I need to create a password protected certificate.

Do you have any idea?! Thank you.

You cannot create a password-protected-certificate, then use that certificate to encrypt a database via TDE since the SQL Server will not be able to open the database.

From Microsoft Docs:

  Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM.

Steps involved in encrypting a database using TDE:

Create a master key
Create or obtain a certificate protected by the master key
Create a database encryption key and protect it by the certificate
Set the database to use encryption

An example of the above, taken from the Microsoft Docs page on TDE:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

That page also says this:

  TDE certificates must be encrypted by the database master key to be accepted by the following statements. If they are encrypted by password only, the statements will reject them as encryptors.

    CREATE DATABASE ENCRYPTION KEY
    ALTER DATABASE ENCRYPTION KEY
    DROP DATABASE ENCRYPTION KEY

  Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.


Best way to capture changes on OLTP for Data Warehouse: Create/Update column or CDC What is a preferable way to move ETL data into Data Warehouse? Create/Update date on the OLTP application, or Change Data Capture (CDC)?

Given the two are allowed on a system and resourcing is not an issue, I would think CDC is preferable. As sometimes, I have seen software application/developers make mistakes where the Create/Update columns do not work. Just want someone to validate thinking.

Also curious to what Kimball mentioned, seems he prefers CDC here?

https://www.kimballgroup.com/2007/10/subsystems-of-etl-revisited/

https://www.kimballgroup.com/2009/10/six-key-decisions-for-etl-architectures/

You've answered your own question!

It's ALWAYS better to go with your vendor's solution rather than "rolling your own"... How many people will test your "home-cooked solution"? How many edge cases will they encounter?

I'm willing to go out on a limb here and state that maybe, just maybe, it might conceivably be within the bounds of possibility that your customer base isn't quite as large as that for Microsoft SQL Server... Microsoft have literally had MILLIONS of people test their solution - and it's likely any bugs will be patched PDQ!

This is a quote from an answer I gave previously - original quote is from Jonathan Lewis) who wrote these books! Not related at first glance, but the relevance will hopefully become obvious!

  Chapter 10: Design Disasters, by Jonathan Lewis

  More war stories, for fans of Chapter 8! "Now prepare yourself to read
  all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.

  This chapter describes some of the most common mistakes in development
  Oracle database applications. You'll certainly recognise some of them,
  because so many people stubbornly cling to certain beliefs. I know I
  like to bring up several of his points when I get into common
  arguments like these:
  1. We want our application to be "Database Independent."
  2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.
  3. We want to use sequences for our primary keys.

Just substitute "implement Change Data Capture" for "check data integrity" in item 2 and you get the picture.

I implore you to think of yourself, your fellow employees (who'll be clearing up the mess long after you've retired!), your company (if they're a decent employer) and your customers (if you care anything about them).

Step away from the keyboard and have a long hard think about this and then come up with the answer towards which I'm gently guiding you! :-)

[EDIT]: In response to points raised in the OP's comment.

No, the industry is not "switching viewpoints"! NoSQL is series of different types of compromise, solving some problems while creating a whole set of new ones! NoSQL is still niche and will forever remain so. See this article. Many NoSQL vendors are scrambling to put a) SQL interfaces over their products (cf. Google's Spanner/F1 system) and b) to implement ACID semantics - who wants an incorrect bank balance (BASE)?

You are being deafened by the sound of the wheels of a big bandwagon rolling through town and the clamouring of those who've hopped on board. Check out the writings of Michael Stronebraker on the topic - a real database pioneer and still going strong with his latest NewSQL gig, VoltDB!

Take a look at where the action is going to be in the future - it will be with systems like Spanner (two of which I have used for College projects are CockroachDB and TitaniumDB, also check out YugaByte and ActorDB) and, although still a bit "raw", they have architectures which are ACID compliant, while simultaneously answering many of the concerns of the NoSQL crowd!

As for the remarks about Azure Data Warehouse and (some) AWS systems not having FOREIGN KEYs - I'll betcha a dollar to a doughnut that this data came from systems which do have FOREIGN KEYs. Those who use them are deliberately choosing to (potentially) compromise on data integrity for the speed tradeoff that they will get in these DWs!


$addToset do not work in update if record not exists I am trying to use following query

db.TestColl.update({ "tags" : "silk" , "host" : "host.com"},{ "$set" : { "cat" : "MOVIE" , "$addToSet" : { "articleId" : { "$each" : [ "20442242"]}} , "publishTime" : 1370427540000} , "$inc" : { "count" : 1}},true,false);

The reason is if a record exists and a new article id comes it will be added to the set and count will be updated but if the record does not exists then it should store a record. But it gives me an error that while storage $addtoSet and $each are not valid modifier.

I resolved the issue. I made an error in the update statement. The correct version is:

db.TestColl.update({
    "tags" : "silk" ,
    "host" : "host.com"
  }, {
    "$set" : { "cat" : "MOVIE", "publishTime" : 1370427540000 },
    "$addToSet" : {
      "articleId" : { "$each" : [ "20442242" ]}} ,
      "$inc" : { "count" : 1}
  },
  true, false)


InnoDB MySQL table with a trigger that inserts into a MyISAM table Background

I have a Java web application that is used by 300 - 500 users at any given time.  On a separate Linux server we have a 5.7.19 MySQL Community Database Server that handles all the transactions for this application. We have a very active InnoDB table called "Requests".  This table is involved in many queries.  70% are select statements, 20% are updates, 10% are inserts.

On any CRUD operation against Requests, there is a trigger that does an insert into a different table called Audit.  That audit table is MyISAM.  We have having these very weird performance issues once or twice a day that are caused by Lock Wait Timeouts (at least that is what I see in the log file for Apache Tomcat).  So I wrote a shell script that dumps all the processes in MySQL whenever I see that entry in the Tomcat log file.  What I see is that there is a full table lock Requests.

Question

Can a InnoDB table with a trigger that does a insert into a MyISAM table cause a table level lock on the InnoDB table?

Inserting into a MyISAM table may take a table lock on that table.  Since that INSERT is coming from a Trigger on a InnoDB table, it is inside a transaction.  So, if the table lock takes a long time, the transaction takes a long time, hence "lock wait timeout".

The exception on the table lock is if there have been no updates or deletes from the MyISAM table and the PK is AUTO_INCREMENT.  (Or something like that.  Dig in the docs for the specifics.)

Is it possible to add to audit outside the InnoDB transaction?

You say "full table lock requests" -- which table?  What is the specific text?

Another thing that can mess up inserting a MyISAM table -- a big SELECT.  Perhaps you do a big 'report' twice a day?

Rethink whether to use MyISAM for audit.


Is it best practice to revoke server permissions to the server "public" role on SQL Server instances? We have evaluated the Microsoft "best practice" policies (located at C:\Program Files (x86)\Microsoft SQL Server\number\Tools\Policies\DatabaseEngine\1033) on each of our SQL Server instances (example here). The following issue has been flagged up to us:

This provides a link to a Microsoft page which states the following as being best practice:

  Do not grant server permissions to the server public role.

On reviewing the security, we can see that the public role has server permissions to view any database by default. We have run the following and re-evaluated the policy, but the issue is still flagged up.

REVOKE VIEW ANY DATABASE TO [public] AS [sa];

The only other permissions attributed to this server role are endpoint permissions to be able to connect via TCP/VIA/Named Pipes/Local Machine. If we remove these, surely the role will be superfluous.

QUESTIONS

Should we be concerned about this? Is this something we really should look to address?
What else do we need to do to properly resolve this issue?
Are there any implications for doing this or not doing this?

Yes, you should fix this. If you have multiple DBs in the server, a legitimate user in one DB might have nothing to do in another DB and will not be a user in that and hence 'public'. Why would you give pubic user permission to view/access objects in a DB they are not entitled to?

Make sure to test the application after you fix these. Sometimes even a legitimate user might be missing appropriate grant permission and might have been working based on public Access, which won't work after you fix the 'public' role access. They needs to be granted explicit access through grant stmt to specific objects or via database roles or via user roles.


Compare each day of year with DATEFIRST = 1 and DATEFIRST = 7 To Get a list of weeks, i simply run this (found online):

SET DATEFIRST 7
;with DateCTE   as
(
    select cast('01/01/2017' as datetime) DateValue
    union all
    select DateValue + 1
    from    DateCTE
    where   DateValue + 1 < '1/1/2018'
)

Select
    cast(CAST(DateValue AS CHAR(11)) AS DateTime) AS [FullDate],
    DATEPART(wk, DateValue) AS WeekOfYear,
    DATEPART(yy, DateValue) AS CalendarYear
from   DateCTE
OPTION (MAXRECURSION 0)

Obviously if I SET DATEFIRST = 1, then it gets different results.

I need to compare the days in one table, without using table variables.
This is what I'd like to achieve:

Thus I'd like to see the same 365 days that will be available in both CTE's,

I want to compare the WeekOfYear based on different DateFirsts.

I do not know how to do this effectively. I can only get it to work when I

Create table variable 1 and run the CTE for DateFirst7
Create table variable 2 and run the CTE for DateFirst1

This is inefficient and I am looking for a better way to do this.

There are a few things working against this
1. I dont know how to set the DateFirst inside the query. I only know how to set it before  the cte.
2. I would like to use nested CTE's for this, but OPTION (MAXRECURSION 0) does not work well with nested CTE's

Is this possible - using CTE's to get required results?

I started with an attempt at fixing Max Vernon's answer so that it would work for any year, but ended up with two different approaches.

Approach 1. Being clever

In this method I am calculating the Sunday-based week number directly from the offset from the year's first Sunday-based week's Sunday.

A year's first Sunday-based week's Sunday can be obtained like this:

SET DATEFIRST 1;
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @FirstDayOfYear) % 7), @FirstDayOfYear);

You are taking the week day of January 1 as a number of days and subtracting it from January 1, unless that date falls on Sunday, in which case you are subtracting nothing (that is what the … % 7 is for). For Monday-based week numbers, the result of the above formula will always be a Sunday, and in this case it will be the Sunday of the first Sunday-based week of the year.

Before going on to calculating Sunday-based week numbers, here is another formula for turning any date into January 1 of the same year:

SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, @Date), @Date);

Basically, take the date and subtract its DAYOFYEAR value and add one day.

Now here is a query that is using Max Vernon's set of CTEs, the above two formulae and another one to get the actual week number:

SET DATEFIRST 1;
WITH Nums AS (
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Num)
)
, Dates AS
(
    SELECT Date = DATEADD(DAY
                , ROW_NUMBER() OVER (ORDER BY n1.Num, n2.Num, n2.Num)
                , N'2016-12-31T00:00:00')
    FROM Nums n1 --10 rows
        CROSS JOIN Nums n2 --100 rows
        CROSS JOIN Nums n3 --1000 rows, add more cross joins for more rows
)
SELECT [Date] = FORMAT(d.Date, 'dddd MMM dd, yyyy')
    , WeekOfYearStartingMonday = DATEPART(WEEK, d.Date)
    , WeekOfYearStartingSunday = DATEDIFF(DAY, y.Sunday1, d.Date) / 7 + 1
FROM Dates AS d
CROSS APPLY (
    SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, d.Date), d.Date)
) AS x (January1)
CROSS APPLY (
    SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, x.January1) % 7), x.January1)
) AS y (Sunday1)
ORDER BY d.Date;

Approach 2. Using system loophole

This method is completely different in that it relies on a built-in peculiarity of the DATEDIFF function.

As you probably know, setting DATEFIRST affects the results of such functions as DATEPART(WEEK) and DATEPART(WEEKDAY). Unlike them, DATEDIFF(WEEK) does not respect the DATEFIRST setting. It always calculates the difference as if DATEFIRST were set to 7, that is always treating weeks as Sunday-based.

Knowing that, you can calculate the difference in weeks between January 1 and any date of the same year, add 1 and use that as the Sunday-based week number:

SET DATEFIRST 1;
WITH Nums AS (
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Num)
)
, Dates AS
(
    SELECT Date = DATEADD(DAY
                , ROW_NUMBER() OVER (ORDER BY n1.Num, n2.Num, n2.Num)
                , N'2016-12-31T00:00:00')
    FROM Nums n1 --10 rows
        CROSS JOIN Nums n2 --100 rows
        CROSS JOIN Nums n3 --1000 rows, add more cross joins for more rows
)
SELECT [Date] = FORMAT(d.Date, 'dddd MMM dd, yyyy')
    , WeekOfYearStartingMonday = DATEPART(WEEK, d.Date)
    , WeekOfYearStartingSunday = DATEDIFF(WEEK, x.January1, d.Date) + 1
FROM Dates AS d
CROSS APPLY (
    SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, d.Date), d.Date)
) AS x (January1)
ORDER BY d.Date;


Why would I not specify ROWLOCK to get rid of page locks? I have a query that goes something like this:

SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC;

This answer says that it's hard to say for sure but it will probably obtain a page level lock. I'm now profiling my database (actually just staring into sys.dm_exec_requests periodically) and it looks like when this query is executed from multiple clients the multiple instances of the query "get into a line" - like the earliest one locked something and all the others are waiting till that something is released.

I'd like to try add ROWLOCK to enforce obtaining a row level lock but it's kind of risky in the production database and I cannot fully reproduce the same load in my test database (at least until I understand all the subtleties).

Why would I not use ROWLOCK hint here? What could likely break?

Assuming the availability of an index to provide the requested sort order (SomeColumn ASC) the sample query you posted will in all likelihood take a row lock, even where the index in question is not covering for the query.

The query processor is smart enough to know that reading a single row from one end of an index (possibly followed by a lookup to retrieve additional columns) is a low-cost enterprise that suits a row locking strategy.

If there is no such index, or the execution plan is more complex than the sample query suggests (perhaps because the table is partitioned, or there is a WHERE clause), so that a significant amount of data must be read (and probably sorted) then the engine may start with page locks.

Note that ROWLOCK hints are suggestions. The engine can and will ignore the hint in some circumstances. This differs from other 'hints' like FORCESEEK that will throw an error if it cannot be honoured.

One reason not to use ROWLOCK is that it runs the risk of a very large number of locks being taken (and possibly held to the end of the transaction, depending on the exact circumstances). If there are conflicting locks on the table (or partition), these row locks would be prevented from escalating to a partition or table lock to release memory, which could affect the entire instance.

Ultimately, the details depend on localized details that are not provided in the question. That simple query should not require a ROWLOCK hint, given correct indexing. Nevertheless, the evidence suggests that page locks are currently being taken, which READPAST cannot skip.

If this query is being used to implement some sort of queuing mechanism, you would probably benefit from reading Remus Rusanu's excellent article on Using Tables As Queues.


Is there any way to get the estimate cost of executing a query in MySQL? In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation of other tools? I'm using MySQL-5.6.16.

In short

Run your SELECT ... query
SHOW STATUS LIKE 'last_query_cost'
if the answer was 0 rerun the query with
select  SQL_NO_CACHE ... and do step 2 above again


PG - Select a record not reserved yet I have a PG (12) database with two tables:

RESERVABLES

id               uuid

RESERVATIONS

id               uuid
reservable_id    uuid
reserved_between tsrange

Where reservations.reservable_id is a foreign key on reservables.

Now, given a tsrange (i.e. [2020-02-01 14:30, 2020-02-01 15:30]) I need to find a Reservable not reserved yet for that time interval. However, I have no idea how to proceed.

A big plus would be to find the Reservable with the most free time (fewer reservations) for that date.

This is my (untested) query:

SELECT p.id
FROM reservables AS p
   LEFT JOIN reservations AS c
      ON p.id = c.reservable_id
         AND NOT c.reserved_between
                 && tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]')
WHERE NOT EXISTS (SELECT 1 FROM reservations AS s
                  WHERE p.id = s.reservable_id
                  AND s.reserved_between
                      && tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]'))
GROUP BY p.id
ORDER BY count(c.id) NULLS FIRST
LIMIT 1;

The important part is the NOT EXISTS condition, the left outer join, GROUP BY and ORDER BY are just to collect the big plus. If the performance is not good enough, go for the simpler query.


Raising an error with a system message_id in SQLCLR If call CREATE DATABASE foo twice I get the error message:

Msg 1801, Level 16, State 3, Line 1
Database 'dropme' already exists. Choose a different database name.

The error message is listed in sys.messages:

SELECT * FROM sys.messages WHERE message_id = 1801 AND language_id = 1033

I have a SQLCLR procedure that basically does a CREATE DATABASE FOR ATTACH. It knows the name of the created database at the beginning, but does a lot of work before attaching it. Therefore, I'd like to make sure the database doesn't exist before proceeding and give that error if it does.

If I try to use RAISERROR(1801, 16, 1) on that message it will not let me:

Msg 2732, Level 16, State 1, Line 1
Error number 1801 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.

Do I have any option other than raising the error and having the msg_id being 50000?

Do I have any option other than raising the error and having the msg_id being 50000?

You can define custom messages that have an id > 50000, or you can choose to not define a message and use id = 50000. Internal error numbers can't be raised by users because allowing that would open up all sorts of avenues of abuse...

How you implement this really depends on your environment, so I'm not sure we can give a recommendation for what to do in this case.

One alternative to the built-in methods (I hate that custom messages are global within an instance) which is a bit out-of-the-box, and may or may not be appropriate, is to raise your errors with the default message id of 50000, and return a custom error code in the message itself (with or without an actual error message). This will let you localize the message outside the context of SQL Server, while still retaining the ability to capture specific types of messages. (Note: best done if you have some kind of client-side framework that handles the errors.) The disadvantage is that this kind of behaviour isn't usually expected by callers.


SQl Server Password Change after installation I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in Windows authentication mode, and I want to access the database in mix mode.  For that I need to know the username and password. Please tell me how I can change the password after installing SQL Server 2005.

Enterprise Manager - Object Explorer - Server - Properties - Security - Server Authentification - Sql Server and Windows Authentification mode.

To change password:  Security - Logins - sa - Password

Don't forget to restart te service


Cancelling / Stopping ALTER INDEX REORGANIZE ALTER INDEX [myIndex] ON [dbo].[myTable] REORGANIZE WITH ( LOB_COMPACTION = ON )

I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it has over 10 Billion rows. (About 1 TB of data including myIndex (non-clustered)).

I know there is progress as when I query for fragmentation, I see the number decrease.

If I cancel / stop the execution of the ALTER INDEX, can I resume it later safely, or will it cause to a rollback?

Will it rollback roll just the last page swapped or the whole operation ?

Yes, you can stop a reorganize and it won't cause a big rollback like you are talking about.  You will be left with where the operation left off (that's a good thing).  It's a rebuild that would have the rollback behavior.


How to create a graph connecting predecessors and successors in time from a timeseries table in PostgreSQL 10? I have the following table:

╔════╦═══════════╦═════════════════════╦═══════╗
║ id ║ sensor_id ║        time         ║ value ║
╠════╬═══════════╬═════════════════════╬═══════╣
║  1 ║         1 ║ 2018-01-01 00:00:01 ║     1 ║
║  2 ║         1 ║ 2018-01-01 00:00:02 ║     2 ║
║  3 ║         1 ║ 2018-01-01 00:00:03 ║     3 ║
║  4 ║         1 ║ 2018-01-01 00:00:03 ║     4 ║
║  5 ║         1 ║ 2018-01-01 00:00:04 ║     3 ║
║  6 ║         2 ║ 2018-01-01 00:00:01 ║     1 ║
║  7 ║         2 ║ 2018-01-01 00:00:01 ║     2 ║
║  8 ║         2 ║ 2018-01-01 00:00:02 ║     3 ║
║  9 ║         2 ║ 2018-01-01 00:00:03 ║     4 ║
║ 10 ║         2 ║ 2018-01-01 00:00:04 ║     5 ║
╚════╩═══════════╩═════════════════════╩═══════╝

CREATE TABLE sensor_time_series
(
    id SERIAL PRIMARY KEY,
    "time" TIMESTAMP NOT NULL,
    sensor_id INTEGER NOT NULL,
    value NUMERIC NOT NULL,
);

It's a timeseries table that represents the value of a sensor at a specific time. Yes, I know it's strange that "time" is not unique inside each "sensor_id", that's an error from the dataset.

What I want is to make a new table/view with a graph structure, connecting each "sensor_id" sample to its successor in "time". The table should look something like this:

╔════════════╦══════════════╗
║ current_id ║ successor_id ║
╠════════════╬══════════════╣
║          1 ║           2  ║
║          2 ║           3  ║
║          2 ║           4  ║
║          3 ║           5  ║
║          4 ║           5  ║
║          6 ║           8  ║
║          7 ║           8  ║
║          8 ║           9  ║
║          9 ║          10  ║
╚════════════╩══════════════╝

CREATE TABLE sensor_time_series_graph
(
    current_id INTEGER,
    successor_id INTEGER,
    FOREIGN KEY (current_id) REFERENCES sensor_time_series(id),
    FOREIGN KEY (successor_id) REFERENCES sensor_time_series(id)
);

Both columns (current_id and successor_id) FOREIGN KEY's id from the first table
How can I create something like this in PostgreSQL 10?

I was looking into PostgreSQL window functions and I think they can help me, but did not realized how yet.

SELECT t1.id
     , t2.id
FROM sensor_time_series t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id
                                 AND t1."time" < t2."time"
LEFT JOIN sensor_time_series t3 ON  t1.sensor_id = t3.sensor_id
                                AND t1."time" < t3."time"
                                AND t3."time" < t2."time"
WHERE t3.id IS NULL;

or (thanks to Lennart for the idea)

SELECT t1.id
     , t2.id
FROM ( SELECT id
            , sensor_id
            , lead("time") over(partition by sensor_id order by time) AS leading
       FROM sensor_time_series ) t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id
                                 AND t1.id != t2.id
WHERE t2."time" = t1.leading;

In the last query the join/where conditions can be freely moved between those sections for the best visibility.

fiddle


large SQL Performing quite slow (roughly 2 seconds for 290 rows) when having to deal with entries that can be NULL SELECT version_stamp,
           pscdesc_id,
           psc,
           pscdesctext
    FROM   pscdesc_history
    WHERE  ( psc IN (SELECT psc
                     FROM   pso_history AS c
                     WHERE  ( c.project = 121
                               OR c.project IS NULL )
                     AND EXISTS (SELECT * FROM   pso_history AS x
                                        WHERE  x.project = 121
                                        AND x.pso_id = c.pso_id ))
              OR psc IS NULL )
              AND EXISTS (SELECT * FROM   pscdesc_history AS xyy
                       WHERE  xyy.psc IN (SELECT psc
                                          FROM   pso_history AS c
                                          WHERE  ( c.project = 121
                                                   OR c.project IS NULL )
                                                 AND EXISTS (SELECT * FROM   pso_history AS x
                                                             WHERE  x.project = 121
                                                                    AND
                                                            x.pso_id = c.pso_id))
                       AND xyy.pscdesc_id = pscdesc_history.pscdesc_id )

Maybe in prosa what I am trying to do:

Entries in the pscdesc_history table represent changes. Each entry is a change.

so a record with pscdesc_id can get changed, say pscdesctext gets changed. That gives us a new entry with the same pscdesc_id but a new version_stamp and pscdesctext the rest can be empty.
To which project these entries (crucial, since I only want to deal with one project) belong, I don't know. I have to look at the table pso_history. psc is the foreign key to a third table.

Why don't I just join? Since this is a table full of records of changes, the actual foreign key psc might be null. The same applies for pso_history where the forgein key project might be null too.

So i have to get all records of pscdesc_history that are related to a record in pso_history with a pso_history.project of 121 or that are related to a record with pso_history.project that was at some point 121.

Is there something you can see where you would think I could speed up things ? replace the AND EXISTS with something else?

Use DISTINCT everywhere you can and I don't understand why not to join instead of correlating

                 SELECT psc
                 FROM   pso_history AS c
                 WHERE  ( c.project = 121
                           OR c.project IS NULL )
                 AND EXISTS (SELECT * FROM   pso_history AS x
                                    WHERE  x.project = 121
                                    AND x.pso_id = c.pso_id )

                 SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS x
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121
                    AND   x.pso_id = c.pso_id

And the entire query will be

SELECT  xyx.version_stamp, xyx.pscdesc_id, xyx.psc, xyx.pscdesctext
FROM    pscdesc_history AS xyx
JOIN    pscdesc_history AS xyy
ON  (xyx.psc IN (SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121
                    AND   x.pso_id = c.pso_id)
     OR xyx.psc IS NULL)
AND xyy.pscdesc_id = xyx.pscdesc_id
AND  xyy.psc IN ( SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121
                    AND   x.pso_id = c.pso_id)

This must work twice farster I think, check the indexes also.


PostgreSQL Database Data File Integrity Check When running a PostgreSQL database system how do I know my database as a whole has 100% integrity?  Basically how do I know if my data files and pages are all 100% good with no corruption?

In the Microsoft SQL Server world there's a command that you can execute DBCC CHECKDB that will tell you if there're issues.  Here's a link if your intrested in learning more on the command. DBCC CHECKDB (Transact-SQL)

I'm a paranoid database integrity minded person (which anyone who works with the database in a DBA type role should be) and this type of stuff makes it hard for me to sleep well at night.  A utility like this is a must!  Searches on google have found a few attempts at tools like this and in my opinion unless it's an official accepted tool by the PostgreSQL project I won't trust it for something this important.

Here are some links to people asking similar questions with what I consider no real definitive answer. And in my opinion shows that PostgreSQL needs to have some tools in place that Oracle and Microsoft SQL Server seem to have.

The first link is the most interesting I have found on this subject.  I think a comment on the article that probably sums it up states: "Postgres is pretty lame when it comes to identifying database corruption and repairing it. The only way to detect it is by dumping the database or select * from every table in the database."

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption - Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

I believe there is a chance 9.3 might have some corruption checking features. It appears there may be hope to having page files check summed if one chooses.  So things are looking bright if you consider using ZFS and/or a future version of Postgres with page check summing.
https://commitfest.postgresql.org/action/patch_view?id=759

UPDATE: 14-JAN-2012 - Seems like using a file system based on ZFS can detect corruption by check summing each block of data.  I will have to look into this further and see if this is a work around to allow one to sleep well at night knowing their database data is not silently going corrupt.

UPDATE: 17-JAN-2012 - How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3

PostgreSQL prior to 9.3 did not have block checksums. The feature was added in 9.3 (long after this question was posted).

To answer your needs, I would probably develop my own checksumming (triggers?) - working on attribute values, not data pages.


How to get the size of an index created in a plpgsql block I'm trying to benchmark something. I want to create an index and get it's size in a plpgsql block.

This works:

haki=# create index ix on t(n);
CREATE INDEX

haki=# select pg_relation_size('ix');
 pg_relation_size
------------------
         22487040

When I try the same in a plpgsql block, I'm getting ERROR: relation "ix" does not exist:

haki=# DO $$DECLARE
haki$#     s int;
haki$# BEGIN
haki$#     PERFORM 'create index ix on t(n)';
haki$#     SELECT pg_relation_size('ix') INTO s;
haki$#     RAISE NOTICE 'size is %', s;
haki$# END$$;
ERROR:  relation "ix" does not exist
LINE 1: SELECT pg_relation_size('ix')

I'm guessing it has something to do with the fact that DDL is transactional and the block is not committed (I might also be wrong!). However, I am unable to find a way around that.

Is it possible to get the size of an index created in a plpsql block?

My idea is to use execute and rollback then. I would suggest you to read it "what are the difference between perform and execute on PL/pgSQL?"

DO
$$
declare
  s int;
begin
  execute 'create index ix on t(n)';
  select pg_relation_size('ix') into s;
  raise notice 'size is %', s;
  select 1/0; -- This is for ROLLBACK
END
$$;

NOTICE:  size is 8192
ERROR:  division by zero
CONTEXT:  SQL statement "select 1/0"
PL/pgSQL function inline_code_block line 8 at SQL statement


MySQL crashed, InnoDB fails to recover On MySQL 5.0 on CentOS 5.11, MySQL service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database DBNAME:

160511 10:10:54  InnoDB: Error: table 'DBNAMEnew/tablename1'
InnoDB: in InnoDB data dictionary has tablespace id ....,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

Following MySQL troubleshooting, part "Tablespace Does Not Exist", it asks to perform 2 steps:

Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.
Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

/var/lib/mysql/ has DBNAME directory. I've created empty .frm files for all the tables appearing in the error, in DBNAMEnew directory and tried to start the service, which fails, similarly.
Also, since the mysqld wouldn't start, how can I "DROP TABLE" as instructed in the second step?

innodb settnigs in my.cnf

#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2400M
innodb_file_per_table
#innodb_data_home_dir = <directory>
innodb_file_io_threads = 4
innodb_force_recovery=3
#innodb_force_recovery=4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120

I've already backed up all /var/lib/mysql. Do you know how to recover from this crashing state?

Question UPDATE:
I've copied all, .frm files from DBNAME directory to DBNAMEnew. To have matching .frm file for this orphan table. Still getting the following

160512 09:10:52  InnoDB: Error: table 'DBNAMEnew/tablename1'
InnoDB: in InnoDB data dictionary has tablespace id ....,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

QUESTION UPDATE
I've created a database with the same schema, and copied over the .frm files to the failing database. Now, getting the following errors:

160515 10:54:11  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3602 935861248
160515 10:55:39  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 160515 10:55:41 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

Since MySQL fails to start, I'm unable to proceed. Is there any way to recover when InnoDB internal data dictionary is in such a state?

Empty frm files won't work because they have a specific syntax which is being checked by mysql.

[root@server test]# touch test_one.frm
[root@server test]# chown mysql test_one.frm
[root@server test]# mysql test

Although it is showing up in show table because it only checks for the existence of the file ...

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
....
| test_one       |
...
+----------------+

... any operations on it will fail.

mysql> show create table test_one;
ERROR 1033 (HY000): Incorrect information in file: './test/test_one.frm'

mysql> drop table test_one;
ERROR 1051 (42S02): Unknown table 'test.test_one'

The documentation says:

  Create a matching .frm file in some other database directory and copy
  it to the database directory where the orphan table is located.

Now, matching (same structure) is actually not mandatory if you only want to drop it but you need to have a valid frm file.

If you do this you should be able to drop the table afterwards:

mysql> create table test_recoverer (id int unsigned primary key);
Query OK, 0 rows affected (0.31 sec)

[root@server test]# cat test_recoverer.frm > test_one.frm

mysql> drop table test_one;
Query OK, 0 rows affected (0.11 sec)


how to insert data into extra columns of target avro table when source table is having less no of columns compared to target using hive or impala? Suppose I am having a source Avro table having 10 columns and my target Avro table is having 12 columns, while inserting data into the target table I need to add null values to the extra 2 columns.
But when I execute the below query it has thrown the exception

AnalysisException: Target table 'target_table' has more columns (8) than the SELECT / VALUES clause returns (7)

insert overwrite table target_table select * from source_table;

How to make advantage of Avro table automatic schema change detection here?
Note: Suppose if I want to insert only 5 columns to the target and the rest should be default null. So how to achieve this?

You need to specify which columns you want to insert into in the insert statement. In the statement you wrote, the engine doesn't know which 7 columns out of the 8 to put the data in.
http://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_insert.html

Insert overwrite table target_table (col1, col2, col3, ...)
Select col1, col2, col3 ... From source_table;

NULL will be inserted into columns not mentioned in the column list.


Create SQL Randomized Date of Birth Does anyone have any SQL code to automatically generate randomized birth dates, where the date of birth is less than today? Please add Date of Birth Range parameters, eg: from 18 to 70 years old.

Is there any inline SQL or function to do this?

We are trying to obfuscate our column [Date of Birth].

Thanks,

This will add a random number of days to 1st of January, 1900:

SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

According to the Microsoft Docs, CRYPT_GEN_RANDOM "returns a cryptographic random number generated by the Crypto API (CAPI). The output is a hexadecimal number of the specified number of bytes."

So CRYPT_GEN_RANDOM(2) returns a two-byte number in the range of 0x0000 to 0xFFFF, when converted into a signed-integer and "added" to 1900-01-01, will result in dates in the range of 1900-01-01 to 2079-06-06.

For a table named dbo.MyTable, with a column named [Date of Birth], this will update all column values to randomly generated dates:

UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

One could reverse the logic such that you have people of various ages from 0 days old to approximately 59 years old with this:

UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, (1 - CONVERT(int, CRYPT_GEN_RANDOM(2)) / 3), GETDATE());

The following example will randomly choose birth dates resulting in ages between 10 and 20 years old:

DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    );


Insert performance with Geography column I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly.

I started out with no geography column and it took 6 minutes, then I added a geography column and it took 20 minutes (again same data). Then I added a spatial index and it took 1 hour and 45 minutes.

I'm new at anything spatial, but this seems like really bad performance. Is there anything I can do to help speed this up or is this just the performance I'm going to see when dealing with SQL Server spatial data?

After doing some research, including looking at this question, it appears it isn't directly possible to efficiently bulk load any of the CLR-based types, including geography. 1

You said that adding just the geography column added a significant amount of time to the load process -- this may, in fact, be entirely reasonable, depending on the amount of data that's going into that column.

The most concerning thing is that it's taking so long to load the data without the geography column involved.

When doing large data loads, maximizing throughput is the most important part of optimizing the process, particularly if the client application and database are separated by a network connection. (By the way, I'm going to assume that using a client application to load the data is the best architectural decision for the problem here.)

To maximize throughput means minimizing other types of overhead, such as data preparation on the client, and network roundtrips between servers.

Based on the comments, you have some type of loop happening that reads a row from the source and writes it to the target immediately. Something like this (pseudo-code):

while (more rows to read from source)
{
    read one row from source
    write one row to target
}

This is inefficient primarily because it maximizes network roundtrips and small chunks of I/O on the target, which are both expensive in aggregate.

Probably the easiest way to modify your code to fix this issue is to introduce a delayed-write scheme, where a chunk of rows are read from the source, and flushed to the target all at once. Something like this:

while (more rows to read from source)
{
    read 1..batchSize rows from source
    write 1..batchSize rows to target
}

Even using INSERT ... VALUES with multiple rows at a time (note: there is a 1000-row limit per statement) will significantly improve performance. The test harness I constructed saw a 43x improvement between a batch size of 1 and a batch size of 1,000. 2

A note about parameterization. While this is a best-practice when it comes to application security, for a batch loading process, parameterized statements can cripple performance because of the associated processing time. If you start batching things together, it's still possible to parameterize the whole statement, but the performance gets worse and worse the more parameters you use (there's a limit of something like 2,000 parameters anyway). Assuming the data source values are "safe," I would actually recommend doing your own escaping (replace ' with '') for the string columns and concatenate all values directly into the SQL text as literals. By the way, if you're using .NET, make sure you use a StringBuilder to concatenate strings.

About how to move the geography column around efficiently. Simply batching the INSERTs may be enough. If not, it may be advantageous to attempt loading (or bulk loading) the text definition of the column (I'm assuming you're given a text list of points or something from the data sources) along with the rest of the data into a staging table, then populate the target by doing an INSERT/SELECT to convert the text definition into the CLR type. (Note: I haven't tested this.)

Either way, if you haven't reached network saturation after doing that, and depending on how fast you can read from the data source vs. write to the target, the code could be refactored into a producer/consumer architecture where reads and writes happen asynchronously, and possibly multithreaded on either or both sides. The details of how to do this are out of scope for this site, but I figured it should be mentioned. I don't think you'll have to go to this length to get acceptable performance.

Finally, for completeness of this answer, in terms of indexes, for a one-time load, it's advantageous to create nonclustered indexes after all the data has been loaded into the table. This reduces the number of internal index inserts, much in the same way that batching INSERT statements reduces network roundtrips. It's probably best to create the clustered index, however, before loading, so there isn't a need to rewrite the whole table to give it order (assuming the clustering key is ever-increasing).

1 Well, probably. If you extract Microsoft.SqlServer.Types from the resource database and reference it in an assembly, it should be possible; however, this is wildly unsupported and will probably break the process horribly if Microsoft updates it between versions.

2 This was a single-threaded test inserting constant values of integers and strings, not selecting from a data source; 1,500,000 rows at 201 bytes/row; using INSERT ... VALUES took 771,971ms for a batch size of 1, and 18,120ms for a batch size of 1,000. I also tested the same data with SqlBulkCopy for comparison, and it took 16,576ms for a batch size of 1,000, and 13,829ms for a batch size of 10,000 (this batch size saturated throughput). You can download the source from here -- add the program to a console application; I used .NET 4.0.