MySQL connection count much larger than query count I'm maintaining a relatively simple LAMP server. It does how ever have a some what high load.
What I'm noticing is that in a one hour time span there are roughly 50k connections made to the mysql server, yet there are only 17k-18k actual queries made. There are only 12 aborted clients.
I've enabled the general log, but I couldn't see any empty connections.
I don't maintain the PHP scripts that connect to the database myself,
but I do have access to them. They always execute at least one query.
The bulk of the connections are made by a php script that does:

Connect to mysql
Execute one very fast query
Close connection

So the connection hardly ever show up in the processlist.
There are no connections left open.
netstat only shows the 3 connections made from my own computer.
The processlist shows the same thing.
Does anyone have suggestions on how I could trace the 34k connections made, that apparently don't do anything.
Here is a screenshot from the phpMyAdmin Status tab:

Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.

Why do I say regardless of who issued each query ?

There are queries that are launched internally by mysqld. Here is a simple example:

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 56    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 58    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 59    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 60    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Please note that issuing a simple query like show global status like 'Questions'; is a query even if your are just requesting internal data from mysqld.

Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.

I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"

Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run netstat and look for DB Connections to mysql that have the TIME_WAIT status.

There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP:

http://bugs.mysql.com/bug.php?id=36872
http://bugs.mysql.com/bug.php?id=56979
http://www.webhostingtalk.com/showthread.php?t=251177


MongoDB: Mongoimport server selection error , connection refused, too many files open Trying to insert several JSON files to MongoDB collections using shell script as following,
#!/bin/bash

NUM=50000
for ((i=o;i<NUM;i++))
do
    mongoimport --host localhost --port 27018 -u 'admin' -p 'password' --authenticationDatabase 'admin' -d random_test -c tri_${i} /home/test/json_files/json_${i}.csv --jsonArray
done

after several successful adding, these errors were shown on terminal
Failed: connection(localhost:27017[-3]), incomplete read of message header: EOF
error connecting to host: could not connect to server:
server selection error: server selection timeout,
current topology: { Type: Single, Servers:
[{ Addr: localhost:27017, Type: Unknown,
State: Connected, Average RTT: 0, Last error: connection() :
dial tcp [::1]:27017: connect: connection refused }, ] }

And below the eoor messages from mongo.log, that said too many open files, can I somehow limit the thread number? or what should I do to fix it?? Thanks a lot!
2020-07-21T11:13:33.613+0200 E  STORAGE  [conn971] WiredTiger error (24) [1595322813:613873][53971:0x7f7c8d228700], WT_SESSION.create: __posix_directory_sync, 151: /home/mongodb/bin/data/db/index-969--7295385362343345274.wt: directory-sync: Too many open files Raw: [1595322813:613873][53971:0x7f7c8d228700], WT_SESSION.create: __posix_directory_sync, 151: /home/mongodb/bin/data/db/index-969--7295385362343345274.wt: directory-sync: Too many open files
2020-07-21T11:13:33.613+0200 E  STORAGE  [conn971] WiredTiger error (-31804) [1595322813:613892][53971:0x7f7c8d228700], WT_SESSION.create: __wt_panic, 490: the process must exit and restart: WT_PANIC: WiredTiger library panic Raw: [1595322813:613892][53971:0x7f7c8d228700], WT_SESSION.create: __wt_panic, 490: the process must exit and restart: WT_PANIC: WiredTiger library panic
2020-07-21T11:13:33.613+0200 F  -        [conn971] Fatal Assertion 50853 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 414
2020-07-21T11:13:33.613+0200 F  -        [conn971]

***aborting after fassert() failure

Depending on your OS, there's multiple ways to modify the maxopenfile of MongoDB.
On Debian and Ubuntu, in /lib/systemd/system/mongod.service there's the LimitNOFILE argument which can be increased if needed.
You have more infos on other OS on https://docs.mongodb.com/manual/reference/ulimit/


Data from Same Column Joined in Two Different Ways In the following sybase query I would like to pull the vendor column twice: the first time I would like to pull the vendor associated with the ReceiveWorksheet table by associating it with a key RCW_VEN_FK, and I then would like to pull from the same table again but this time associating it with the StockInventoryLocal table by the key SIL_VEN_FK_Default.  The goal is to pull the data into two different columns - one which will show the default vendor and one which will show the vendor associated with the receiving worksheet.

I've been looking for quite some time on how to accomplish this.  The first time I received an error that the item was already created (when trying to simply pull from the Vendor table twice), I then tried with aliasing and was unable to accomplish it in that manner.

Does anyone have a suggestion or a resource on how to pull this information from the Vendor table twice and associate it with different keys in different tables and have it populate as two different columns?

ALTER VIEW "ecrs"."00_Test3_BD"( /* view_column_name, ... */ )
AS
SELECT "ecrs"."Vendor"."VEN_Code",
"ecrs"."Vendor"."VEN_CompanyName",
"ecrs"."StockInventoryLocal"."SIL_VEN_FK_Default",
"ecrs"."ReceiveWorksheet"."RCW_VEN_FK"
FROM ( "ecrs"."Vendor" JOIN "ecrs"."StockInventoryLocal" As "DefaultVendor" ON "ecrs"."Vendor"."VEN_PK" = "ecrs"."StockInventoryLocal"."SIL_VEN_FK_Default" ) JOIN "ecrs"."ReceiveWorksheet" As "WorksheetVendor"  ON "ecrs"."Vendor"."VEN_PK" = "ecrs"."ReceiveWorksheet"."RCW_VEN_FK"

I tried the above and received an error "Table 'DefaultVendor' not found."

Assuming ecrs is a user/schema, and keeping in mind I'm not quite following the textual description (eg, not sure which column(s) from the Vendor table you're trying to display twice), see if the following gets you started ... we can tweak based on your feedback:

SELECT v.VEN_Code,
       v.VEN_CompanyName,
       sil.SIL_VEN_FK_Default,
       rcw.RCW_VEN_FK

FROM   ecrs.Vendor v

JOIN   ecrs.StockInventoryLocal sil
ON     v.VEN_PK = sil.SIL_VEN_FK_Default

JOIN   ecrs.ReceiveWorksheet rcw
ON     v.VEN_PK = rcw.RCW_VEN_FK

I'm assuming this isn't really what you want since you mention wanting to pull different vendors, ie, v.VEN_PK = sil.SIL_VEN_FK_Default = rcw.RCW_VEN_FK (all the same vendor).

If sil.SIL_VEN_FK_Default and rcw.RCW_VEN_FK are different values, then we'll need two references to ecrs.Vendor, but then I don't understand what the relationship is between StockInventoryLocal and ReceiveWorksheet.

In other words, let's say we join Vendor(#1) with StockInventoryLocal, and we join Vendor(#2) with ReceiveWorksheet, how do we join these two sets of joins to each other (eg, is there a relationship between StockInventoryLocal and ReceiveWorksheet? is there a relationship berween the two Vendors?)


Uninstall Postgres 10 on macOS – EnterpriseDB installer Title says it all… How to completely remove a Postgres cluster from a macOS computer?

I used the installer app provided by EnterpriseDB as a courtesy to the community.

This uninstallation question has been asked multiple times on Stack Overflow. But those questions and answers are years out-of-date.

postgres user

The user account named postgres (by default) created by the installer is actually a macOS user account.

Apple allows deleting a user account in the more recent versions of macOS: System Preferences > Users & Groups > - button in list, after authenticating with padlock icon in lower corner.
In older macOS versions that do not delete user accounts, you may be able to hide that user account.

Every other trace of Postgres can be removed.

Backup/Export

Of course, first backup your database with a tool like pgBackup. And/or export your data.

Uninstaller app

The installer app provided by EnterpriseDB does install an app named Uninstaller. Find it in the Postgres folder, probably located at the root Library folder (not the Library folder in your home folder).

  /Library/PostgreSQL/10

Run that uninstaller app, and supply your macOS system password. After the run, the data folder remains as that is owned by the postgres user rather than your regular macOS user. The uninstaller app reminds you of this fact.

  /Library/PostgreSQL/10/data

data folder

Select that data folder in the Finder, and choose File > Move to trash. When prompted, provide your own macOS user account password.

Delete that outer Postgres folder(s) that contained data.

Applications folder

Remove the PostgreSQL 10 folder in your Applications folder if it remains. Again, provide your own macOS user account password.


Merge Replication Validation Failure We are getting a bizarre error with some of our users while syncing with SQL Server 2008 R2 Service Pack 3. Validation fails even though the row count and expected row count actually match!

Error messages:

  Data validation failed for one or more articles.
  Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200953
  Article 'Quote' failed data validation (rowcount and checksum).
  Rowcount actual: 973, expected: 973.

Would it cause a problem disabling the validation? Is there a good example on how to create your own validation?

The error could say:

  Article 'Quote' failed data validation (rowcount only).

However your error states:

  Article 'Quote' failed data validation (rowcount and checksum).

The validation of the merge performs a binary checksum like this:

select count_big(*), sum (convert(numeric, binary_checksum(*) ) )
from [dbo].[tablename] WITH (TABLOCK HOLDLOCK)

As stated by others, the validation during merge can be a bit buggy and you may find it better to roll your own validation.

You can turn off validation by removing the -Validate x parameter from the merge agent.

Regarding your error: Microsoft recommends trying again later, as if the error is temporary. As for code to validate it, you could try the code I link to, but I think you will need to write your own to validate it based on business rules for your data. There shouldn't be harm in disabling the validation, if there was inherent harm the option wouldn't exist. But I would try it with a temporary/test server first.


where is memory going in In-Memory OLTP? I have only memory optimized table in my database. When I am running below query, I am getting pages_mb for that DB as 710 MB.
SELECT type
 , name
 , memory_node_id
 , pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'

But when I run below query, I am getting mem_alloc_total_mb for that table as 2 and others as 0.
   SELECT
   object_name(object_id) AS table_name,
   memory_allocated_for_indexes_kb / 1024 as mem_alloc_index_mb,
   memory_allocated_for_table_kb / 1024 as mem_alloc_table_mb,
   memory_used_by_indexes_kb / 1024 as mem_used_index_mb,
   memory_used_by_table_kb / 1024 as mem_used_table_mb,
   (memory_allocated_for_table_kb + memory_allocated_for_indexes_kb) / 1024 as mem_alloc_total_mb,
   (memory_used_by_table_kb + memory_used_by_indexes_kb) /1024 as mem_used_total_mb
   FROM sys.dm_db_xtp_table_memory_stats
   where object_id = object_id('<table_name>');
   go

Data from both the above queries are contradicting each other. Am I missing anything?
NOTE: Durability setting for the memory optimized tabe is "SCHEMA_AND_DATA".

Memory for In-Memory OLTP is under the sp_configure setting for max memory, but is not part of the buffer pool. Not sure if that helps or not. You could have a lot of stale rows that are waiting until there is memory pressure in order to be "garbage collected". I wrote a script that's part of Brent Ozar's First Responder Kit that might be of some help to you, located here:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_BlitzInMemoryOLTP.sql


Merge multiple rows into fewest number of rows of distinct values In SQL Server does anyone know of a nice way to merge/flatten multiple rows of data into the fewest possible rows containing only the distinct non null values.

I.e.

A dataset like this:

As this:

If it helps, the before dataset is a pivoted line listing but without the aggregate.  I can't aggregate it during the pivot as I want to keep each of the distinct values and not take the MAX or MIN.

The only way I can think of doing it involves splitting the data up and joining it all back together which wont be very efficient.

Here are a couple ways you might approach the task by assigning an arbitrary row number to each of the non-NULL values for each column (avoiding extraneous sorts since the problem statement does not require ordered data) and then pivoting the resulting data into your final result set.

Create test data

CREATE TABLE #table (
    ID INT NOT NULL,
    Col1 VARCHAR(100) NULL,
    Col2 VARCHAR(100) NULL,
    Col3 VARCHAR(100) NULL,
    Col4 VARCHAR(100) NULL,
    Col5 VARCHAR(100) NULL
)
GO
INSERT INTO #table (ID, Col1, Col2, Col3, Col4, Col5)
VALUES  (37850, '1A', NULL, '3A', NULL, '5A'),
        (37850, NULL, NULL, '3B', NULL, NULL),
        (37850, NULL, '2A', '3C', '4A', '5B'),
        (37850, NULL, NULL, NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, NULL, '5C'),
        (37850, NULL, '2B', NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, NULL, '5D'),
        (37850, NULL, NULL, NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, '4B', '5E'),
        (37850, NULL, NULL, NULL, '4C', NULL),
        (37850, NULL, NULL, NULL, '4D', NULL)
GO
CREATE CLUSTERED INDEX CI ON #table (ID)
GO

Perform the desired merge and flatten with one scan of the data per column and only a single sort (or hash group) on the final set of rows

SELECT ID,
        -- Pivot the data for each ID / row number pair,
        MIN(CASE WHEN colNumber = 1 THEN val END) AS Col1,
        MIN(CASE WHEN colNumber = 2 THEN val END) AS Col2,
        MIN(CASE WHEN colNumber = 3 THEN val END) AS Col3,
        MIN(CASE WHEN colNumber = 4 THEN val END) AS Col4,
        MIN(CASE WHEN colNumber = 5 THEN val END) AS Col5
FROM (
    -- Within each ID, assign an arbitrary row number to each non-NULL column value
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 1 AS colNumber, Col1 AS val FROM #table WHERE Col1 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 2 AS colNumber, Col2 AS val FROM #table WHERE Col2 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 3 AS colNumber, Col3 AS val FROM #table WHERE Col3 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 4 AS colNumber, Col4 AS val FROM #table WHERE Col4 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 5 AS colNumber, Col5 AS val FROM #table WHERE Col5 IS NOT NULL
) x
GROUP BY ID, rowNum
GO

In SQL Server 2017+, perform the desired merge and flatten with a single scan over the data and no sorting

SELECT s.ID,
    -- Pivot the data for each ID / row number pair,
    MIN(CASE WHEN v.colNum = 1 THEN v.value END) AS Col1,
    MIN(CASE WHEN v.colNum = 2 THEN v.value END) AS Col2,
    MIN(CASE WHEN v.colNum = 3 THEN v.value END) AS Col3,
    MIN(CASE WHEN v.colNum = 4 THEN v.value END) AS Col4,
    MIN(CASE WHEN v.colNum = 5 THEN v.value END) AS Col5
FROM (
    -- For each ID, build the list of non-NULL values, using
    -- a delimiter that will not exist in your data
    SELECT t.ID,
        STRING_AGG(t.Col1,CHAR(0)) as stringAgg1,
        STRING_AGG(t.Col2,CHAR(0)) as stringAgg2,
        STRING_AGG(t.Col3,CHAR(0)) as stringAgg3,
        STRING_AGG(t.Col4,CHAR(0)) as stringAgg4,
        STRING_AGG(t.Col5,CHAR(0)) as stringAgg5
    FROM #table t
    GROUP BY t.ID
) s
OUTER APPLY (
    -- For each ID, unpivot the list of non-NULL values,
    -- appending an arbitrary row number to each value
    SELECT value, 1 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg1,CHAR(0)) UNION ALL
    SELECT value, 2 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg2,CHAR(0)) UNION ALL
    SELECT value, 3 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg3,CHAR(0)) UNION ALL
    SELECT value, 4 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg4,CHAR(0)) UNION ALL
    SELECT value, 5 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg5,CHAR(0))
) v
-- For each ID, group together all column values with same row number
GROUP BY s.ID, v.rn
GO

Visualize the test data

Visualize the results


PostgreSQL Binary Replication -do i really need to copy pg_xlog separated? I am doing postgresql replication by issuing:

On the master:

>select pg_start_backup('clone', true);
>\q
#tar -cz main -f main.tar.gz

(Explanation: main is the data directory on my system /var/lib/postgresql/9.6/main)

Then I transfer main.tar.gz to the slave....

On the slave:

--Uncompressing main.tar.gz....
#tar -zxf main.tar.gz
--Copying previously created recovery.conf
#cp recovery.conf main/
--And then just starts the slave
#service postgresql start

On the master:

--And stoping backup mode on the master:
>select pg_stop_backup();

What I get is that replication is working fine, and it really works nice.

But I am with doubt after reading the official documentation that after the copy I should copy pg_xlog  in an additional step... https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

But I never do that, and never experienced a problem - do a lot of tests, like counting every table lines, in both master and slave and much more, but what worst can happen?

It works fine, until it doesn't.  Then you get a backup which won't start, will start but won't connect the master to stream new data, or perhaps one which will start but is silently corrupted (unlikely, but I wouldn't rule it out). Have you tested this method for a very large server under very heavy load?

If you use pg_basebackup, it will either take care of it for you, or will notify you that the backup failed before you discover that the hard way, or notify you that you need to have a separate WAL archive.  Depending on which version of PostgreSQL and which options you give to pg_basebackup.


Choosing the right algorithm in HashBytes function We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario?

We want to ensure the risk of having duplicate hash value for two different nvarchar value is the minimum. Based on my research on the internet MD5 seems the best one. Is that right? MSDN tells us (link below) about the available algorithms, but no description on which one for what conditions?

HASHBYTES (Transact-SQL)

We need to join two tables on two nvarchar(max) columns. As you can imagine the query takes along time to execute. We thought it would be better to keep the hash value of each nvarchar(max) data and do the join on the hash values rather than the nvarchar(max) values which are blobs. The question is which hash algorithm provides the uniqueness, so that we don't run into the risk of having one hash value for more than one nvarchar(max).

The HASHBYTES function only takes up to 8000 bytes as input. Because your inputs are potentially larger than that, duplicates in the range of the field that gets hashed will cause collisions, regardless of the algorithm chosen. Carefully consider the range of data you plan to hash -- using the first 4000 characters is the obvious choice, but may not be the best choice for your data.

In any event, because of what a hash function is, even if the inputs are 8000 bytes or less, the only way to ensure 100% correctness in the results is to compare the base values at some point (read: not necessarily first). Period.

The business will dictate whether or not 100% accuracy is required. This will tell you that either (a) comparing the base values is required, or (b) you should consider not comparing the base values -- how much accuracy should be traded off for performance.

While hash collisions are possible in a unique input set, they are infinitesimally rare, regardless of the algorithm chosen. The whole idea of using a hash value in this scenario is to efficiently narrow down the join results to a more manageable set, not to necessarily arrive at the final set of results immediately. Again, for 100% accuracy, this cannot be the final step in the process. This scenario isn't using hashing for the purpose of cryptography, so an algorithm such as MD5 will work fine.

It would be extremely hard for me to justify moving up to a SHA-x algorithm for "accuracy" purposes because if the business is going to freak out about the miniscule collision possibilities of MD5, chances are they're also going to freak out that the SHA-x algorithms aren't perfect either. They either have to come to terms with the slight inaccuracy, or mandate that the query be 100% accurate and live with the associated technical implications. I suppose if the CEO sleeps better at night knowing you used SHA-x instead of MD5, well, fine; it still doesn't mean much from a technical point of view in this case.

Speaking of performance, if the tables are read-mostly and the join result is needed frequently, consider implementing an indexed view to eliminate the need to compute the entire join every time it's requested. Of course you trade off storage for that, but it may be well worth it for the performance improvement, particularly if 100% accuracy is required.

For further reading on indexing long string values, I published an article that walks through an example of how to do this for a single table, and presents things to consider when attempting the full scenario in this question.


What are the pros and cons of having more data-bearing members in a MongoDB replica set? A 3 member replica set has the same fault tolerance if all 3 members contain a copy of the data or if one is an arbiter so why would I ever want all 3 members to have a copy of the data?

What is a situation where all members having a copy of the data makes sense?

There are a few key aspects to fault tolerance in a replica set:

write availability: a majority of voting replica set members is required to maintain or elect a primary
data redundancy: writes should be acknowledged by multiple members of a replica (ideally a majority, to avoid rollbacks).

A three member Primary-Secondary-Arbiter (PSA) replica set supports the first aspect: if any single member of the replica set is unavailable, a primary can still be maintained or elected.

However, an arbiter does not support the second aspect of fault tolerance since it does not store any data.

When a PSA configuration is degraded to P_A (one secondary unavailable), there are some significant operational consequences:

Until you have a second data-bearing member online, data redundancy is compromised and writes can only be acknowledged by the current primary.
You no longer have active replication. If your secondary is offline for too long, it may fall off the oplog and need to be re-synced.
Applications and internal processes can no longer use a majority write concern. If you haven't planned for this, writes requesting majority acknowledgement may block until a majority of data bearing members are available or a timeout is reached (if set for the write command). If your replica set is part of a sharded cluster, this can also prevent successful chunk migrations between shards.
Applications and internal processes will not see the newest data on the primary using a majority read concern. Some features (for example, change streams in MongoDB 3.6+) rely on reading majority committed data to avoid the chance that changes may be rolled back. If the majority commit point cannot be advanced, there will also be increased pressure on the WiredTiger cache.

The above consequences can be avoided by having a Primary-Secondary-Secondary (PSS) configuration.

It is worth keeping in mind that fault tolerance doesn't only apply to failure modes. There are normal maintenance tasks such as software or hardware upgrades that may require a member to be briefly unavailable.

  What is a situation all members having a copy of the data makes sense?

If you are running a three member replica set in production with MongoDB 3.6 or newer (or any version of MongoDB where this replica set is backing a shard): always.

If you are running an older version of MongoDB in production: always, unless you are prepared to accept the above operational consequences.


Use of null in case expression In the customers' table, many customers do not have state data in the state column therefore when we select customers, the state column displays NULL values, which is not meaningful for the reporting purpose.

╔════════════════╦════════════════════╦═══════╦═════════╗
║ customerNumber ║    customerName    ║ state ║ country ║
╠════════════════╬════════════════════╬═══════╬═════════╣
║            103 ║ Atelier graphique  ║ NULL  ║ France  ║
║            112 ║ Signal Gift Stores ║ NV    ║ USA     ║
╚════════════════╩════════════════════╩═══════╩═════════╝

I am trying to improve the output by using the case, but getting an error:

SELECT
    customerNumber,
    customerName,
    CASE
      WHEN state IS NULL, THEN 'N/A'
      ELSE state
    END) as state,
    country
FROM
    customers;

Error:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' THEN 'N/A' ELSE state END) as state, country FROM customers' at line 5

Desired Result:

╔════════════════╦════════════════════╦═══════╦═════════╗
║ customerNumber ║    customerName    ║ state ║ country ║
╠════════════════╬════════════════════╬═══════╬═════════╣
║            103 ║ Atelier graphique  ║ N/A   ║ France  ║
║            112 ║ Signal Gift Stores ║ NV    ║ USA     ║
╚════════════════╩════════════════════╩═══════╩═════════╝

You need to remove the comma from this part of your code:

CASE
  WHEN state IS NULL, THEN 'N/A'
  ELSE state
END

The correct version would be:

CASE
  WHEN state IS NULL THEN 'N/A'
  ELSE state
END

You can also use the coalesce function:

COALESCE(<VALUE>,<OUTPUT IF NULL>)

The COALESCE function translates well this code:

CASE WHEN <value> IS NOT NULL
     THEN <value>
     ELSE <value_2>
     END

Coalesce returns the first non null value in the list.

Using COALESCE, your query will appear like this:

SELECT
    customerNumber,
    customerName,
    COALESCE( state, 'N/A') as state,
    country
FROM
    customers;


SSRS Migration To a separate Network We are building a new SSRS 2008R2 environment on a separate network.

How can I migrate the reports from our current network to the new network?

Can I backup the reports from the existing SSRS server and then put them in a location where the server on the new network can pick them up?

This is a simple backup and restore process, with the additional step of making sure you backup the Reporting Services key, so you can restore that as well.

A number of links on the internet, but this one is a step by step guide with screenshots,  much easier than trying to follow MSDN

http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases

or if you are only talking a small number of reports and you want to change the credentials,etc you could simply import the .rdl definition files from SourceControl (hopefully :) )

If the 2 domains can authenticate with one another, then the current credentials will work. otherwise you will need to change the account used.


sp_BlitzFirst fails when outputting values to tables I have had an agent job running sp_AskBrent for some time, running with the following parameters:
EXEC sp_AskBrent @Expertmode=1
,@OutputDatabaseName = 'DBName'
, @OutputSchemaName = 'dbo'
, @OutputTableName = 'AskBrentResults'
,@seconds = 30
, @OutputTableNameFileStats='AskBrentResultsFileStats'
,@OutputTableNamePerfmonStats='AskBrentResultsPerfmonStats'
, @OutputTableNameWaitStats='AskBrentResultsWaitStats'

Today I wanted to update to the new renamed procedure sp_BlitzFirst. After creating the new procedure, I changed the code in the agent job to run sp_BlitzFirst instead, with the same parameters.
Everything seems to go OK until it is time to output values to the tables, then there seem to be some kind of conversion error.
The whole history output is as follows:

Setting up configuration variables [SQLSTATE 01000] (Message 50000)
Now starting diagnostic analysis [SQLSTATE 01000] (Message 50000)
Capturing first pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Beginning investigatory queries [SQLSTATE 01000] (Message 50000)
Finished running investigatory queries [SQLSTATE 01000] (Message 50000)
Waiting to match @Seconds parameter [SQLSTATE 01000] (Message 50000)
Capturing second pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Analyzing changes between first and second passes of DMVs [SQLSTATE 01000] (Message 50000)
Analysis finished, outputting results [SQLSTATE 01000] (Message 50000)
Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241).  The step failed.

Anyone have any suggestions to what might cause this? Have the output tables changed somehow since the procedure was called sp_Askbrent? The version of sp_Askbrent was reported this way: sp_AskBrent (TM) v20 as of Jan  1 2016 12:00AM.

When we renamed it sp_BlitzFirst and went open source, we made several breaking changes, one of which was changing the history table's DATETIME field to be DATETIMEOFFSET instead:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/288

I would use a new history table name, and then it'll get automatically created with the right datatype.


Database design: where do I put this data? EventCategory
=============
EventCategoryId PK,
Name

Event
=====
EventId PK,
EventCategoryId FK,
Name

This is what I've got now. However, events of some event category type need extra data fields. So where do these fields go? This is what I'm thinking...

EventCategory
=============
EventCategoryId PK,
Name

Event
=====
EventId PK,
EventCategoryId FK,
Name

SpecialEvent
============
EventId PK,
Extrafield

So there's a 1-1 relationship between Event and Special Event (this structure will map to a single SpecialEvent class in C#) and all events and only those events that reference the pertinent category will have a row in this new table.

Is this a fair design? My qualm is that an event will have a a corresponding row in the SpecialEvent table and yet won't reference the required event category or vica versa.

What you are doing here is called specialization.

You can look at this answer to a similar question at stackoverflow which will point you in the right direction. I've implemented a slightly modified version with success.

   *An other option (that I do not advise!!) is the use of EAV. *

Edit: I've created an example for your situation. (Don't mind the sample data ;) )

CREATE TABLE EventType (
EventTypeID INT NOT NULL PRIMARY KEY,
EventType VARCHAR(200) NOT NULL
);

INSERT INTO EventType (EventTypeID, EventType) VALUES (1, 'Play Rockband'),(2, 'Play Call of Duty');

CREATE TABLE Event (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Event VARCHAR(200) NOT NULL,
    CONSTRAINT UQ_Event UNIQUE (EventID, EventTypeID),
CONSTRAINT FK_Event_EventType FOREIGN KEY (EventTypeID) REFERENCES EventType(EventTypeID)
);

INSERT INTO Event (EventID, EventTypeID, Event) VALUES (1, 1, 'Play the bass'),(2, 2, 'Sniper!');

CREATE TABLE SpecialEvent1 (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Extrafield VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent1 */
CONSTRAINT CHK_EventTypeID_1 CHECK (EventTypeID = 1),
/* Check if event exists */
CONSTRAINT FK_SpecialEvent1_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);

INSERT INTO SpecialEvent1 (EventID, EventTypeID, Extrafield) VALUES (1, 1, 'Expert');

CREATE TABLE SpecialEvent2 (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Extrafield2 VARCHAR(200) NOT NULL,
Extrafield3 VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent2 */
CONSTRAINT CHK_EventTypeID_2 CHECK (EventTypeID = 2),
/* Check if event exists */
CONSTRAINT FK_SpecialEvent2_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);

INSERT INTO SpecialEvent2 (EventID, EventTypeID, Extrafield2, Extrafield3) VALUES (2, 2, 'Character X', 'Team Z');

You can query the events like this:

SELECT EventType.Eventtype AS [Type of event], Event.Event, SpecialEvent1.Extrafield AS Difficulty
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent1 ON Event.EventID =  SpecialEvent1.EventID AND Event.EventTypeID = SpecialEvent1.EventTypeID;

SELECT EventType.Eventtype AS [Type of event], Event.Event [Play As], SpecialEvent2.Extrafield2 AS Character, SpecialEvent2.Extrafield3 AS Team
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent2 ON Event.EventID =  SpecialEvent2.EventID AND Event.EventTypeID = SpecialEvent2.EventTypeID;

SQL Fiddle: http://sqlfiddle.com/#!6/d1911/1/0


Deadlock in SQL SERVER 2014 I am experiencing sporadic deadlock behaviour when my application tries to execute any DROP TABLE statement against my database. I tried capturing the deadlock graph and this is the output from deadlock xml

<deadlock>
     <victim-list>
      <victimProcess id="process2bddc28" />
     </victim-list>
     <process-list>
      <process id="process2bddc28" taskpriority="0" logused="0" waitresource="KEY: 6:281474978938880 (85262e079cd2)" waittime="1900" ownerId="76493296" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:15.867" XDES="0x621cb38e0" lockMode="S" schedulerid="6" kpid="10748" status="suspended" spid="119" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-02-22T05:58:16.080" lastbatchcompleted="2016-02-22T05:58:16.080" lastattention="1900-01-01T00:00:00.080" hostname="DEV01" hostpid="13420" isolationlevel="read committed (2)" xactid="76493296" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
       <executionStack>
        <frame procname="mssqlsystemresource.sys.sp_columns_100" line="138" stmtstart="12020" stmtend="17532" sqlhandle="0x0300ff7f78a964e13656e600e7a5000001000000000000000000000000000000000000000000000000000000">
      </frame>
       </executionStack>
       <inputbuf>Proc [Database Id = 32767 Object Id = -513496712]</inputbuf>
      </process>
      <process id="process45c8ca8" taskpriority="0" logused="6748" waitresource="KEY: 6:562949956108288 (5db581fe7a0b)" waittime="1900" ownerId="76493663" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:16.087" XDES="0x16aeded90" lockMode="X" schedulerid="1" kpid="18268" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-02-22T05:58:16.090" lastbatchcompleted="2016-02-22T05:58:16.090" lastattention="1900-01-01T00:00:00.090" hostname="DEV01" hostpid="20128"  isolationlevel="read committed (2)" xactid="76493663" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
       <executionStack>
        <frame procname="adhoc" line="1" sqlhandle="0x01000600702bfd0bd083047f0600000000000000000000000000000000000000000000000000000000000000">
    DROP TABLE "dbo"."USER_TABLE_1"    </frame>
       </executionStack>
       <inputbuf>
    DROP TABLE "dbo"."USER_TABLE_1"   </inputbuf>
      </process>
     </process-list>
     <resource-list>
      <keylock hobtid="281474978938880" dbid="6" objectname="DEV_01.sys.sysschobjs" indexname="clst" id="lock35af27b80" mode="X" associatedObjectId="281474978938880">
       <owner-list><owner id="process45c8ca8" mode="X" /></owner-list>
       <waiter-list><waiter id="process2bddc28" mode="S" requestType="wait" /></waiter-list>
       </keylock>
      <keylock hobtid="562949956108288" dbid="6" objectname="DEV_01.sys.syscolpars" indexname="nc" id="lock453c34600" mode="U" associatedObjectId="562949956108288">
       <owner-list><owner id="process2bddc28" mode="S" /></owner-list>
       <waiter-list><waiter id="process45c8ca8" mode="X" requestType="convert" /></waiter-list>
      </keylock>
     </resource-list>
    </deadlock>

This behaviour was not seen in earlier versions of SQL Server. Any thoughts on this?

Just my 5 cents contribution to the topic (not a complete answer)

I had a similar issue and I've caught the blocking query with sp_whoisactive - so it is

create procedure sys.sp_columns_100
(
    @table_name         nvarchar(384),
    @table_owner        nvarchar(384) = null,
    @table_qualifier    sysname = null,
    @column_name        nvarchar(384) = null,
    @NameScope          int = 0,
    @ODBCVer            int = 2,
    @fUsePattern        bit = 1 -- To allow users to explicitly disable all pattern matching.
)
...

and as @Cody_Konior suggested I do have "many active ODBC connections" from legacy SSIS packages in my ETL system, so...

perhaps it's time to finally change them to OLEDB or ADO.NET versions as I wanted for some time already but posponed


Named Parameter Markers in DB2 for z/OS I have a query where I will be using the same value twice for one of the parameters. This query will be run under IBM Data Studio to see the results. Below is a sample query:

SELECT *
FROM SYSADM.STATISTICAL_TABLE S
WHERE S.END_RECORD_TIMESTAMP BETWEEN
    CONCAT(?, '-12.00.00.000000') AND
    CONCAT(?, '-22.00.00.000000');

When run in Data Studio or any similar graphical query building environment I am prompted to enter values for both parameters, even though the values will always be the same for both.

I believe it is possible to used named parameter markers in other RDBMSes to indicate that the value should only be given once. Example:

SELECT *
FROM SYSADM.STATISTICAL_TABLE S
WHERE S.END_RECORD_TIMESTAMP BETWEEN
    CONCAT(?:queryDate, '-12.00.00.000000') AND
    CONCAT(?:queryDate, '-22.00.00.000000');

Is there a way to use named markers in DB2 for z/OS to indicate that the value will be the same so graphical tools only prompt for one input? On queries that have 20+ inputs, this can be time consuming.

Is there a better way that I should be doing this?

First of all, the syntax for a named parameter marker is CONCAT(:queryDate, '-12.00.00.000000'), that is, it begins with a colon, not a question mark.

Processing of parameter markers is performed on the client side, not on the server, and it's irrelevant if your server happens to be DB2 for z/OS or not. The IBM Data Server Client (the common driver for IBM database servers) supports named parameter markers.

Whether Data Studio SQL editor can properly parse named parameter markers and pass them to the driver is a different question, to which I'm afraid I don't know the answer.


Update existing rows with squence number/char or any unique data I added a new column in the Table X

This column "cn" has to be unique and mandatory, but old data don't have any value.

How to update the existing records with sequecely or random unique data?

Thank you.

If you are happy with a number starting from 1 you can use row_number().

update T
set cn = rn
from (
       select cn,
              row_number() over(order by (select 1)) as rn
       from TableX
     ) T


How to persist selection of foreign key in related table I have a database design that I need to get some help with

Suppose I have three tables, namely preferences, books, genres. The tables are related as follows:

The following tables are accessible in a view, in which the user is allowed to add a book to his personal page.

Currently, the view dictates that the user first choose a genre, before he gets the opportunity to select a book (A basic filter).

However, imagine that the user has selected a genre and decides to come back to select a book at a later point - Then this architecture doesn't support that the selected genre is saved as a part of the preferences. Meaning that, even though the user saved his preferences with a genre, he will have to, once he gets back to add his book, select the genre again in order to then select the book.

Therefore, how would I optimally "refactor" this architecture in order to support what I want to achieve.

My first intuition would be to add one more column to the preferences table named "genres_Id" and have a foreign key pointing to the genres table. I guess this is not good practice though..

Currently you have something like this, as unusual as it may seem.

user {USR}
  PK {USR}

genre {GNR}
   PK {GNR}

book {BOK, GNR}
  PK {BOK}
  FK {GNR} REFERENCES genre {GNR}

preference {USR, BOK}
        PK {USR}
       FK1 {USR} REFERENCES user {USR}
       FK2 {BOK} REFERENCES book {BOK}

For the stated requirement:

user {USR}
  PK {USR}

genre {GNR}
   PK {GNR}

book {BOK, GNR}
  PK {BOK}
  SK {BOK, GNR}

  FK {GNR} REFERENCES genre {GNR}

pref_genre {USR, GNR}
        PK {USR}
        SK {USR, GNR}

       FK1 {USR} REFERENCES user  {USR}
       FK2 {GNR} REFERENCES genre {GNR}

pref_book {USR, BOK, GNR}
       PK {USR}

      FK1 {USR, GNR} REFERENCES pref_genre {USR, GNR}
      FK2 {BOK, GNR} REFERENCES book       {BOK, GNR}

And if you want to allow more than one preference for each user, just change keys on pref_:

pref_genre {USR, GNR}
        PK {USR, GNR}

       FK1 {USR} REFERENCES user  {USR}
       FK2 {GNR} REFERENCES genre {GNR}

pref_book {USR, BOK, GNR}
       PK {USR, BOK}

      FK1 {USR, GNR} REFERENCES pref_genre {USR, GNR}
      FK2 {BOK, GNR} REFERENCES book       {BOK, GNR}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key


Why won't PostgreSQL 9.3 start on Ubuntu? I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04.

The install (this time) seems to have gone ok, but perhaps there is an error I'm not understanding:

* No PostgreSQL clusters exist; see "man pg_createcluster"
Setting up postgresql-9.3 (9.3.0-2.pgdg12.4+1) ...
Creating new cluster 9.3/main ...
  config /etc/postgresql/9.3/main
  data   /var/lib/postgresql/9.3/main
  locale en_US.UTF-8
  port   5432
update-alternatives: using /usr/share/postgresql/9.3/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode.

So I then try to add myself as a PostgreSQL user, but I get this:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I cannot see PostgreSQL running in system monitor, and there is no file in the /var/run/postgresql/ folder...completely empty.

EDIT: On the VM's, there is a file in /var/run/postgresql/ called 9.3-main.pid

There is nothing on the host machine log file located /var/log/postgresql

So... what's going on here that isn't going on in my VM's? Like I said, the other installations on the VM's, including PostGIS and PGAdmin came in perfect...no idea why this host machine isn't going through...

My locale settings were not properly configured when PostgreSQL was installed. Purging and reinstalling didn't help. I followed the instructions here and that did the trick for me.

Essential parts of the linked information reproduced below:

The problem showed itself in the following manner:

warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
...
are supported and installed on your system.

The first one was very easy to solve by executing:

#dpkg-reconfigure locales

...and choosing the preferred locales.

But after that PostgreSQL still refused to start. This is due to the fact that the installation process tried to create a cluster at installation time but because of the bad locales this wasn't done. So we have to redo this step by executing:

#pg_createcluster 9.3 main --start

(For the 9.3 version of PostgreSQL)

After that step PostgreSQL starts flawlessly via

#/etc/init.d/postgresql start


How to improve the estimated cardinality of inner join lateral I'm currently running a query that has the following structure:
select
  v1.*,
  v2.*,
  ...
from t1
  inner join lateral f1(t1.id1, t1.id2) as v1 on true
  inner join lateral f2(t1.id1, t1.id2) as v2 on true
  ...
where
  t1.id1 = 123 and t1.id2 = 234;

This works fast enough, but I've noticed the following problem with explain analyze.
For every inner join lateral, it seems to multiply the amount of estimated rows by 1000 even though the joins don't add new rows. This leads to the query plan having more rows that the jit_above_cost which results in the JIT running and adding significant delay (450ms on a 40ms query).
I'd like to avoid disabling jit_above_cost as I'm sure that it can be useful for optimizing queries, but I've tried searching for ways to improve the cardinality estimate, but I've had not luck.

If the joins can't add new rows, then why are f1 and f2 defined to be a set-returning function in the first place?  If they were not set-returning functions, this multiplication would not happen.
Alternatively, declare the functions to be expected to return only one row.
alter function f1 rows 1;

etc.,.


Backup without using SQL Server Management Studio I am trying to take a backup on a Windows Server 2003 and SQL Server 2005 installed on it. But, there is no SQL Server Management Studio on it. How can I take full backup of a particular database.

You can use sqlcmd from the command prompt.

It's default location for SQL 2012 is here :- C:\Program Files\Microsoft SQL Server\110\Tools\Binn

For windows authentication run :- sqlcmd -SSERVERNAME

or for sql authentication run:- sqlcmd -SSERVERNAME -ULogin -PPassword

You can then run a backup sql statement.

http://msdn.microsoft.com/en-us/library/ms180944.aspx


Number of connection running within certain period of time? Is there any way to capture number of connections or processes which is running on the prod server each 10 mins of interval? And MySQL version is 5.1.47.

Something like this might do the trick, assuming your mysqld is listening on port 3306:

$ netstat -ant | grep ":3306" | grep ESTABLISHED | wc -l
50

You can put this in a script and execute every 10 minutes through cron.


Optimize performance for query with lots of OR's I have the following query
select Id
from Table1
where column1 = @inputparameter1
or column2 = @inputparameter1
or column3 = @inputparameter1
or column4 = @inputparameter1
or column5 = @inputparameter1
or column6 = @inputparameter1
or column7 = @inputparameter1
or column8 = @inputparameter1
or column9 = @inputparameter1
this query runs quite often from the app tier and so far the only way I have been able to make it run fast is by creating a filtered index on each of the columns in the WHERE clause  (it does 9 index seeks + merge joins and it takes < 20 ms) but then I ended up adding 9 more indexes to a 20 million records table, even though I created the indexes as filtered to save some space and the query uses the indexes I'm still looking for other ways to make it run this fast without having to add the 9 indexes. I tried creating a single index with all the columns but it will inevitable cause an index scan and takes around 6000 ms of CPU, any recommendations?
Thank you

One thing you can try is re-writing the query to use the UNION ALL clause instead of a bunch of OR operators in the WHERE clause's predicate. It depends on the execution plan you were getting previously vs what this one generates though.


SQL - referential integrity - Foreign key and Check constraint Please let me know if below two declarations are same.

create table R1
( a INT PRIMARY KEY,
  b INT references K(w));

Note: K is a table with single attribute w as primary key

create table R1
( a INT PRIMARY KEY,
  b INT CHECK (b in (SELECT w FROM K));

To answer the question of whether they are equivalent, you need to declare K. Assuming:

create table K (x int not null primary key, w int, unique(w));
insert into K (x) values (1);
insert into K (x) values (2);

The following would satisfy the CHECK constraint:

insert into R1 (a,b) values (1,1);

because:

CHECK (b in (SELECT w FROM K))

evaluates to:

CHECK (b in (null, null)) <=> CHECK (null)

The rule is that the constraint must not evaluate to false. I don't know any DBMS that support subqueries in CHECK constraints, so I can't verify that.

I tested the foreign key variant against PostgreSQL 11, and the insert is rejected

 ERROR:  insert or update on table "r1" violates foreign key constraint "r1_b_fkey" DETAIL:  Key (b)=(1) is not present in table "k".


INSERT in a remote server is far too slow, how to speed it up? I need to load 480.000 rows into a remote database. The database can't be accessed locally, it's an Azure WebApp, so i can't load the SQL statements on the server.
I was wondering if there is a way to speed up things, as the process has been really slow, like 4500 rows in 25 minutes, which feels really like I'm doing something wrong.

I'm using

 mysql -uxxxx -pxxxx -hremoteapp-on-azure azure_db < .\wp_term_relationships.sql

to load and the sql file is

INSERT INTO `wp_43_term_taxonomy` VALUES ('2', '2', 'link_category', '', '0', '8');
INSERT INTO `wp_43_term_taxonomy` VALUES ('3', '3', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('4', '4', 'post_tag', '', '0', '1423');
INSERT INTO `wp_43_term_taxonomy` VALUES ('5', '5', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('6', '6', 'post_tag', '', '0', '106');
INSERT INTO `wp_43_term_taxonomy` VALUES ('16', '16', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('18', '18', 'post_tag', '', '0', '154');
INSERT INTO `wp_43_term_taxonomy` VALUES ('20', '20', 'post_tag', '', '0', '279');
INSERT INTO `wp_43_term_taxonomy` VALUES ('21', '21', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('25', '25', 'post_tag', '', '0', '429');

I've read about optimizing for INSERT, but in this case the query feel really slow. On my local machine the whole insert is taking 20 seconds, or less, not 20 minutes.

Should i somehow use multiple statements to avoid round trips?i could stream the file in PHP and submit it in 1000 INSERT chunks

EDIT - To be clear, I need to be sure i optimized everything I can optimize from my side as I have no control on the server

Since  most mysql versions turn on autocommit, use a transaction to have the server sync data to disk only once and not on every row.

Add at the top of your .sql:

BEGIN TRANSACTION;

and append at the end:

COMMIT;

This will have the added advantage that either all will be inserted or none at all. Same for your php script. Or you may add begin/commit every say 50000 rows to commit in batches.


Replicating from master to slaves and from slaves to master I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local changes on the slave node should be reflected to the master node.

For example let's say I have a master machine, lets call it "M", and two slave machines lets call them "A" and "B"

All these machines have a table named test_table with a column named "id".

I insert data to the test_table on M machine.

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|

(B)test_table
   |id|

now this change is reflected to the slaves:

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|
   |1 |

(B)test_table
   |id|
   |1 |

Now on slave A, I make a local change.

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |1 |

Now this change is reflected to the master server:

(M)test_table
   |id|
   |2 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |1 |

And then master server replicates this change to the slaves:

(M)test_table
   |id|
   |2 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |2 |

What is this kind of replication named? And how can I achieve this on postgresql?

What you are looking for is multi-master replication.  Multi-master replication has a large number of potential problems and consequently the best anything can offer is a toolkit rather than a guaranteed solution (imagine two concurrent updates on the same row on different servers).

Bucardo will do multi-master replication for two servers, but not three.  You could probably play some fun tricks with Slony and table partitions but this ends up getting complex and is definitely expert territory.

My recommendation is avoid multi-master replication if at all possible.  It is mathematically impossible to make it just work all the time without making it more failure prone than a single system would be.  Try hard to design so that you don't need it.

If you do need it, please look at getting some professional help whether it is a second set of eyes or a consultant (who can also get another consultant to review the design).  There is so much that can go wrong here that you really want additional eyes on the problem.


What Query Browser tools are out there available for Sybase Database? What are the free and commercial tools available for Sybase Database?

As Sybase also has a JDBC driver any Java/JDBC tool should work too (at least the basic stuff).

These are the free JDBC based tools that I know of:

DbVisualizer  (not open source, but has a free version)
ExecuteQuery
SQL Developer That's not the one from Oracle! It's not open source but free as well
SQL Workbench/J
Squirrel has already been mentioned.

Another commercial tool that comes to my mind is: DB Solo

Edit, some more suggestions here:
https://stackoverflow.com/questions/5001722/sybase-gui-client
https://stackoverflow.com/questions/2481071/any-alternative-to-sybase-sql-advantage
https://stackoverflow.com/questions/428387/whats-a-good-front-end-query-tool-for-sybase/


SQLCMD Mode in SSMS and @@variable expansion When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL variables.

Problem definition

I want to use the power of SQLCMD's variable expansion to substitute environment specific values in our deploy scripts instead of the existing tsql string building mash I've walked into. With the one exception of current environment, the use of SQLCMD to handle deploys has gone extremely well.

--
-- define 2 sqlcmd variables that will be expanded in scripts
--
:setvar dbServer "DEVA2\DEV2"
:setvar dbNotServer @@servername

SELECT
    '$(dbServer)' AS hard_coded_value
,   @@servername AS [servername]
,   '$(dbNotServer)' AS dbNotServer

And that generates the following results.

hard_coded_value  servername  dbNotServer
DEVA2\DEV2        DEVA2\DEV2  @@servername

Meat Loaf says 2 out of 3 ain't bad, but I'd rather have a 3 out of 3 solution.
When that script gets deployed the test server, I don't want to trust the deployment folks with editing the script.

If the only solution to using SQLCMD is to invoke scripts completely from the command line, I can accept that but wanted to throw this out here as I'm green to using SQLCMD.

Desired output

:setvar dbNotServer @@servername
SELECT '$(dbNotServer)' AS worked

Results

worked
DEVA\DEV2

Fruitless pursuits

The first BOL link showed promise, all I had to do was use the SQLCMDSERVER but to no avail. Run within the context of SSMS in SQLCMD mode, it will throw a fatal scripting error

-- A fatal scripting error occurred.
-- Variable SQLCMDSERVER is not defined.
SELECT '$(SQLCMDSERVER)' AS [FatalScriptingError]

Tumbleweed update

2011-08-12
In an attempt to reduct my problem to the simplest form, based on the answers, I oversimplified my queries (my apologies). A portion of the query I used is below. The two reponders are correct in their answers that identify wrapping @@servername in tick marks results in the literal value and to get the expanded value, I'd need to unwrap it from the quotes. My desire was to use sqlcmd variable substitution rather than string building which unwrap would entail.

INSERT INTO
    dbo.SSISCONFIG
(
    ConfigurationFilter
,   ConfiguredValue
,   PackagePath
,   ConfiguredValueType
,   Application
,   Category
,   Subcategory
,   Comment
)
SELECT
    'Default.Accounting' AS ConfigurationFilter
,   'Data Source=$(dbServer);Initial Catalog=Accounting;Provider=SQLNCLI10.1;Integrated Security=SSPI;Packet Size=32767;' AS ConfiguredValue
,   '\Package.Connections[Accounting].Properties[ConnectionString]' AS PackagePath
,   'String' AS ConfiguredValueType
,   'Defaults' AS Application
,   'Connection' AS Category
,   'Database' AS Subcategory
,   'Default connection string' AS Comment
SELECT
    'Default.$(sqlVersion).CorporateReporting' AS ConfigurationFilter
,   'Data Source=$(dwServer);Initial Catalog=CRDS;Provider=SQLNCLI10.1;Integrated Security=SSPI;Packet Size=32767;' AS ConfiguredValue
,   '\Package.Connections[CorporateReporting].Properties[ConnectionString]' AS PackagePath
,   'String' AS ConfiguredValueType
,   'Defaults' AS Application
,   'Connection' AS Category
,   'Database' AS Subcategory
,   'Default connection string' AS Comment

Command line invocation works (as expected)

C:\>sqlcmd -E -d master -S DEVA2\DEV2 -Q "SELECT '$(SQLCMDSERVER)' AS [servername]"

I've tried and discarded other permutations of the :setvar x @@servername in an attempt to get the evaluated value of the database variable stored into the SQLCMD variable. At this point, I'm fairly certain the sqlcmd variables are susbstituted in queries prior to query compiliation but would love to be proven wrong.

BOL references

Using sqlcmd with Scripting Variables
Editing SQLCMD Scripts in the Transact-SQL Editor
Using the sqlcmd Utility
Tutorial: sqlcmd Utility
sqlcmd Utility

Unless I'm missing something, doesn't this work, without the apostrophes?:

:setvar dbNotServer @@servername
SELECT $(dbNotServer) AS worked

It returns SERVER\INSTANCE for me in SQLCMD mode through SSMS.

Edit 02/12/2018
After coming across my answer to another question, and reviewing, I can see the issue, I believe it boils down to this:

:setvar dbNotServer @@servername
print '$(dbNotServer)'

This outputs @@servername, and the desired output is the value of @@servername.
The only way I've seen to do this is in the accepted answer here


One table used in two databases I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different database? I have few thoughts:

Writing trigger on update/delete/insert one table and update another same time.
Creating view on product table and used in second db (I don't know how)
replication (I don't know how)

Can you guide me on the best way?

I would just make a synonym in db2 (assuming the table exists in db1).  This will let you point directly to the source table without obfuscating behind a view, and will prevent you from needing to maintain a view if the schema of the table changes:

CREATE SYNONYM dbo.CommonTableName FOR db1.dbo.CommonTableName


Are there tools to perform data comparison between two different schemas? We are migrating our database to a new schema but want to validate the data has been moved correctly.

Traditional data comparison tools can compare two databases for differences if the schemas are the same. In our case there have been changes to the table designs but all the data from the old schema is in the new one, it's just been moved around a bit, and I need to make sure it is correct. We have tens of millions of rows so manual inspection is not an option.

Are there any tools which could aid in this type of comparison?

If not are there any libraries/frameworks which could help kick start the development of a custom solution?

I'm happy to use a database specific solution if necessary, in this case for SQL Server 2008.

My soluton: I'm comparing the two data sets by creating a VIEW of each table on the old database with the same fields as the new database table.

I then compare the data using the technique described here: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

I'm lucky in this migration as the overall table structure is similar to the old database, with fields moved from one table to another, dropped or added. In the case of the dropped and added there is nothing to compare, for the fields which have moved or been aggregated I do calculations in the view to provide the correct information for comparison.

The UNION comparison shows me only the rows with differences, so as soon as the data is correct I get an empty result set.

It's not impossible to compare two different schemas, it's a calculation of how confident you are in the result.  I've essentially borrowed from Bank Reconciliation techniques

Important: This reconciliation isn't about making sure the destination exactly matches the source in it's data context (there's a reason you're migrating to a new system), but you do need to explain any discrepancies!

Basis:

Identify metrics that you will use to measure (ie: total number of users, sum of all their ages, list of user id's and their postcodes...) I try to use several metrics from: Totals, Averages & sample/detailed records
Dump this data into a common location (via views/reports/whatever is reasonable)
Compare your data and make sure any discrepancy can be explained

I've generally split the comparison into several methods (in particular for the detailed metrics):

By Table: Usually using the Source Table as the definition, creating a set of views on the destination DB to try and reproduce the Source Table Data
By Object: Depending on the DB and what you're storing it might make more sense to create a holistic view of the objects (ie: the user) across several tables, similar to above creating a set of views to return that object as a set of results
By Reports: If the Source DB has a good comprehensive set of Management Reports, another technique is to reproduce those reports with the exact same formatting

Techniques:

No matter what method I've used to produce what will be compared, I end up with a set of files/views/dbs that hold the reconcilable Source and Destination data, then depending on the medium I can use one of the commonly available tools to compare them.  My own preferences are:

File Comparison
Create two different folders for SourceDB and DestinationDB (time/version stamped so I know when I did the reconciliation) and dump out the output of my metrics as relevantly named files, then use a comparison tool (such as CSDiff) to identify differences
Excel Comparison
In particular when dealing with Management reports, I'll create a set of Excel of workbooks to compare the reports (in essence making use of VLookups and comparing totals)
Data Comparison
Rather than outputting the reconciliation data to files or reports, output them to separate DBs, then use something like Redgate SQL Data Compare to compare the DBs

Other tools:

Not tried any of these, but a quick google search on 'Data reconciliation tools' gives:

n-Tierfs Data Reconciliation Software
Testing Performance Data Reconciliation

There's others, but those were the ones that looked reasonable


Statistics not collected on Insert-Select Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics gathered after an insert-select operation.
I have set the parameter _optimizer_gather_stats_on_load to TRUE.
As per requirement (this only works for new tables), I created a new table. There are no rows prior to insert-select and 500 rows post insert-select operation.
Is there something else I am missing?

This only works with direct path loads: INSERT /*+ APPEND */ INTO ... SELECT ....
It does not apply to conventional path insert (INSERT INTO ... SELECT ..., without /*+ APPEND */ hint).
This is also explained on the site behind the link.


How to predict index size? I have a table with ~100M records of unique urls VARCHAR(1023) like /t/some_page_name_..., an average length is 51 chars.

I'm trying to select the best way to apply a unique index on the table, so I need to calculate by which column unique index will have the smallest size:

VARCHAR(1023) (url) - data size ~52 bytes, index size - ??? bytes
VARCHAR(64) (SHA2(url, 256)) - data size - 65 bytes, index size - ??? bytes
BINARY(32) (UNHEX(SHA2(url, 256))) - data size - 32 bytes, index size - ??? bytes

Can you help me?
Thanks

First approximation:  An InnoDB table or index will be 2-3 times as large as you would expect from adding up the column sizes.

I get 53 for VARCHAR(1023) with an average of 51 characters.  VARCHAR for non-small max needs 2 bytes for length.  This also assumes there are no non-English letters in the url.  For example, each Chinese character takes 3-4 bytes.  The index size needs to be computed in bytes.

For your 3 examples, there won't be a lot of difference, since most of the 2x-3x comes from BTree overhead, padding, row overhead, etc.

Another thing that can make a significant difference in index size is whether the rows are are inserted in sorted order -- or not.  That leads to well packed BTree blocks -- or not.

Also, your numbers are incomplete.  Three different cases:  MyISAM index, InnoDB PRIMARY KEY, InnoDB secondary key.

For InnoDB secondary key, the PRIMARY KEY is tacked on.  So, you need to add whatever sized those column(s) are.  Typical case: 4 bytes for INT.

Even if you get past all that, there will be anomalies.  A 1-row table will have 16KB for each secondary index.  At a certain table size, the "allocation unit" switches from 16KB to 4MB.  After that, adding a row will rarely change the index size, but sometimes will show a big jump.

Etc, etc.

An aside...  Indexes using SHA2 (or other digests/hashes) are notoriously bad for performance on huge tables.  They lead to random accesses, which blows out cache, which leads to reads and writes being I/O bound, which is deadly for huge tables.


How to speed up counting and filtering on table with a lot of columns and filter conditions? I have TABLE_A in my database that has a lot of columns.

Let's say there are 100 columns: COLUMN_1, COLUMN_2, COLUMN_3, .. COLUMN_100

There are so many columns not because of denormalized design, but because each row represent Entity with a lot of properties (in this example - with 99 properties, first column is just an id)

My Application should do the following tasks:

Receive a set of filter conditions from the user
Execute a query in the database that filters the data on given conditions and then should count the result rows

There are no limits on the number of filter conditions:

there may be no filter conditions
there may be filter conditions on every column of the table
there may be filter conditions on some columns of the table (for example on 50 columns out of all)

So below is an example query that my application executes:

SELECT
    COUNT(*)
FROM
    TABLE_A
WHERE
    COLUMN_1 = 'test'
    AND COLUMN_2 != 'q'
    AND COLUMN_45 > 5
    AND COLUMN_45 < 511
    AND COLUMN_92 LIKE '%ddd%'
    AND COLUMN_98 > 1000

TABLE_A doesn't have any indexes - each column can be used for filtering and there are no sets of queries that are run much more frequently than the rest.

I also don't use any kind of cache on any level: insert and update operations happen not very often but more often than at least 2 query with the same filter conditions occur.

So in case of every query the sequential search is executed. It was not a problem earlier, but now the run-times of the query became unacceptable (number of rows in the table increased a lot).

So..

Is there any solution on how to speed up the execution of the task?

Any suggestions would be appreciated - I can change anything except one thing - I should use SQL. Currently I use PostgreSQL, but I would change DBMS if it will help.

I feel your pain, nik. We have a similar use case here, and have been struggling through as best we can with a metric ton of indexes.

I know you said you need to stick with SQL, but I really think you might want to consider a column store (https://en.wikipedia.org/wiki/Column-oriented_DBMS), perhaps one SQL-like enough for you to still find workable.

MariaDB seems to be embracing this now too:
https://mariadb.com/resources/blog/why-columnstore-important


Does it make sense to add indexes to a clustered columnstore index table? Question is similar to this one, but the answer doesn't seem to answer this question.
My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is stored in some physically ordered manner, implying that each column already has what amounts to a clustered index on it.  If that were the case, it wouldn't make a lot of sense to add any more indexes on the table... or would it?  Perhaps a composite index?
Is my thinking correct on this?

My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is stored in some physically ordered manner...

All columns are sorted in the same order. I say this to distinguish from a possible reading of your statement that each column is sorted in the best way for that column, which isn't true.
Each column is encoded and compressed individually in a segment within each row group. The order of operations is:

Encode values in all columns (e.g. value offset, or using a dictionary).
Determine the single 'optimal' row ordering.
Compress each column (e.g. RLE, bit-pack).

The common sort order chosen may be great for some segments, but not others from a compression point of view (primarily run length encoding). SQL Server uses Vertipaq technology to choose a sort order that gives a good overall compression result.

...implying that each column already has what amounts to a clustered index on it

This isn't the right mental picture because column store 'indexes' do not support seeks, only b-trees do. SQL Server can locate an individual row in a column store for lookup purposes (as in the linked Q & A) but that isn't the same as supporting key seeks in general.
Demo of a lookup on a clustered columnstore index.

The distinction is between finding row #n in row group #m versus finding rows with a given value 'x' for a particular column. A column store index can do the former, but not the latter.
Additionally, b-tree indexes can return ordered data; column store indexes cannot (due to encoding) -- except for the trivial case of a lookup where at most one row will be returned.

Does it make sense to add indexes to a clustered columnstore index table?

Additional b-tree indexes make sense when we need to locate a single value, or a small range of values. The alternative would be to scan the column store index (albeit with possible row group elimination if the stars align correctly - as well as partition elimination if the table is partitioned).
More generally, b-tree indexes support OLTP-type queries; column store is for analytics. Providing both on the same table enables a hybrid approach (HTAP - Hybrid Transactional/Analytic Processing).
From the Microsoft Research Paper Columnstore and B+ tree –
Are Hybrid Physical Designs Important? (PDF):

B+ trees outshine columnstores when query
predicates are selective even when all data is memory resident; and
the trade-off shifts further in favor of B+ trees when data is not
memory resident. Likewise, B+ trees can be a better option for providing data in sorted order when server memory is constrained. On
the other hand, columnstores are often an order of magnitude faster
for large scans whether or not the data is memory resident. For
updates, B+ trees are significantly cheaper. Secondary columnstores
incur much lower update cost compared to primary columnstore
indexes, but are still much slower than B+ trees. This empirical
study indicates that for certain workloads, hybrid physical designs
can provide significant performance gains.


CPU column 5x higher on one machine vs another? We're running into a strange issue in a deployed version of our product where the performance is just not very good. When we restore a backup of their DB on one of our machines, it performs as we would expect.

While there are plenty of obvious things to look at, I'm focused on one area for the moment: the number of CPU cycles is 5-7 times higher in profile traces on their machine than it is on ours - running the same queries against the same database.

How does the CPU column in SQL perfmon get calculated on a vm? Do cycles get clocked while the virtual CPU is waiting for a real one? Could the switching of the real CPU under the virtual one lead to cache loss and higher CPU numbers? Am I wasting my time here?

Details:
- Both servers are on ESX hosts with SAN storage.
- I believe their infrastructure may be overloaded.
- VM has 4 CPUs, 16 (or more) GB ram minimal real load on either from within the VM
- SAN host is running loads far less than max
- ESX hosts are said to be not overloaded (although their infrastructure is expanding soon) - I will be digging into this more tomorrow.

I know there are a lot of other variables involved, but my curiosity is killing me about the huge difference in CPU numbers for the same queries on the same DB - even if it ends up not being the problem, I'd love to know more about what causes these differences!

Over-committing CPU and memory on a VM can certainly give you false positives. The true performance bottlenecks are "hidden" from the OS, so you may not see it as easily as you would on a normal windows box.  I have seen this countless times before, so we always recommend reserves on assigned resources, and where possible, dedicated LUNs for the SQL Server.

Also, if your client is using "older" (for e.g. pre-Nehalem) processors, the actual virtualisation may be impacted by shortcomings of the hardware.  I'd never recommend putting a SQL Server VM (especially not a high-IO one) on a host that has older hardware.  Nehalem family introduced VT-d (if memory serves), which (similarly to how VT-x attaches a VM ID tag onto the TLBs, enabling the hardware to know which VM a processor call belongs to) means your hardware is optimised for directed I/O.  All of these help in freeing up the hypervisor from having to keep track of these when emulating processor, memory and I/O calls.

Also, other things that may impact performance at a VM level is DRS, and VMotion, but the newer the hardware, the less of a concern they become, especially when you have VT-d, VT-c and VT-n enabled processors and chipset.

Pending feedback from you, I will be extending this answer, so please provide comments...


How to restore a filegroup from its backup in SQL Server I need guidance on how to restore a file group in Database from the File group backup taken from another same DB server. Is it possible to restore the filegroup by running restore DB for File Group.

I was trying to restoring filegroup from its back up like below

RESTORE DATABASE <DBName > FILEGROUP = 'MFG12' FROM DISK = 'E:\MFG12.bak'
  WITH RECOVERY

The backup set holds a backup of a database other than the existing DBNAME database.

When I was trying to restore in to same DB , restoring works perfectly,

Processed 840976 pages for database 'DB', file 'MFG12_File' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 840976 pages in 59.375 seconds
(110.654 MB/sec).

When I run the Select query against partion of filegroup. it throws error

 One of the partitions of index '' for table 'xyz'
 (partition ID 72057594656129024) resides on a filegroup ("MFG12")
 that cannot be accessed because it is offline, restoring,
 or defunct. This may limit the query result

When I see status of all file group.

I see one of the File Group MFG 12 has status = "Restoring" , How to set back to Online Mode.

When I try to restore log file also.i get below error

The roll forward start point is now at log sequence number (LSN) 66787000000001800001.
Additional roll forward past LSN 66787000000008900001 is required to complete the
restore sequence”.

Please help how do I bring back the missing file group data even though filegroup backup has been taken from another same Database in another server or in the same server DB

After you restore the filegroup, you need to take a tail log backup of the restored database .
Restore the lastest differential you may have to  with norecovery.
Restore all the tlog backups to  with norecovery.
Restore the tail log backup from step 1 to  with recovery.


Export all MySQL users I need export all of a MySQL database users (including permissions) for a migration. There is over 5,000 users, what is the best way to do this?

Should I just dump the mysql.user table?

If you are moving the users to another DB Server running the same major version of MySQL, copying mysql.user is not sufficient. If the users have access to specific databases, copying mysql.user brings the user and the password.

Then, you would have to copy the following

mysql.db for database-level grants
mysql.tables_priv for table-level grants
mysql.columns_priv for column-level grants

Here is logical way to dump it: as SQL GRANT commands !!!

SUGGESTION #1

If you already have Percona Toolkit installed, run pt-show-grants to a text file

GRANT_FILE=MyDatabaseUSers.sql
pt-show-grants -uroot -p > ${GRANT_FILE}

SUGGESTION #2

If you don't have Percona Toolkit and can't wait, here is my personal emulation of it

GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' > ${GRANT_FILE}

EPILOGUE

On the new DB Server, after migrating the data, you login to mysql as root and run

mysql> source MyDatabaseUSers.sql

GIVE IT A TRY !!!


More tables or More records performance wise i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below:
  id
  url
  region_1
  region_2
  region_3
  region_4
  date_created

My colleague creates a table in each category a url appears so the database looks like this
angular
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created

 react
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created

 asp

Same schema and it goes on and on.....
my proposed schema will look like this
sites
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created
   tech_category

This would satisfy the ORMs (Dapper/EF) on the application level to be more maintainable and will just use a single table. What is the right approach? Is it more favorable to create a single table to hold the data or is multiple tables with smaller sets of data?

Both of these schema anti-patterns have been repeatedly refuted:

Do not have multiple 'identical' tables.

Do not spread an array across columns.

So have one table with lots of rows:
    sites
       id
       url
       region
       value
       date_created
       tech_category

Use suitable composite indexes for efficiency.  (We need to see your SELECTs to advise further.)
Use "pivoting" to make the 4 region columns.  (Or >4 when you increase the number of regions.)
As for "performance"...  If you don't have a billion rows, performance is probably not a problem.  When the table gets that big, we can discuss "summary tables".


Can indexes be deactivated for particular queries in DB2? I'm trying to evaluate the impact of a new index on various queries that run against a DB2 11.1 database.

It would be convenient to be able to force the optimizer to ignore the new index on a query-by-query basis. Oracle would let me do this through optimizer hints, for example NO_INDEX(EMPLOYEES EMPLOYEE_PK).

The convenience comes from not having to delete and re-create alternative indexes when comparing their effects on a query, from not requiring "stateful" configuration changes that you must remember to revert, and from avoiding interference with other users' queries.

Is there an analogous mechanism in DB2 for that?

There is no equivalent Access Request with Db2 Optimization Guidelines unfortunately.
You may specify either desired index to use or another access method (TBSCAN, for example) for your table. But not "not to use some particular index" guideline.


Writing a large update/delete script that doesn't provoke locks While running a T-SQL script that is performing massive amounts of deletes, I had the unpleasant experience of causing some sort of lock contention that prevented inserts of data in some tables for other connections. I tried to remedy this by breaking down the request into smaller transactions with the hopes of avoiding an overarching transaction and locks:

set implicit_transactions off

declare @idsToDelete table (id bigint)
declare @currentId bigint
declare @deleteCursor cursor

begin transaction
    insert into @idsToDelete
    select someId from someTable with (nolock) where someCondition = 'some value'
commit

set @deleteCursor = cursor for select id from @idsToDelete
open @deleteCursor
fetch next from @deleteCursor into @currentId

while @@fetch_status = 0 begin

    begin transaction

        delete from relatedTable
        where id = @currentId

        delete from someTable
        where id = @currentId

    commit

    fetch next from @deleteCursor into @currentId

end

close @deleteCursor

My understanding is that set implicit_transactions off should enable me to not have a top-level transaction. Therefore, each of the deletes would run, complete, and we'd be done with it. I was able to see progress with sets of rows being deleted in piecemeal fashion, so I thought it was all good. However, in actuality, inserts into this table were being blocked. And worse, when I noticed and cancelled the run, the whole thing rolled back, continuing to block as it did so!

Is there a way for me to get this thing to run with independent transactions and no locks (except row locks which are OK)? I need to write a big update script that could have similar problems if I'm not careful.

@Jacob - I would say we've all had an unpleasant experience at some point in our lives, so don't beat yourself up.  It's admirable that you're seeking guidance and learning from it.  So hats off to you.

Some key things to target might be the impacted rowcounts.

If you're inserting into @idsToDelete, ideally you have a recordset around 1000 rows or less.  Higher rowcounts (thousands+) will cause your queries to slow significantly.  I've done a side by side inserting into #temptable vs. @temptable with 1000+ rows, or using a CTE and you will find time and time again that the #temptable or CTE with higher recordsets will outperform @temptable significantly.
Deleting from a table where you are sure you're deleting more than 1 row (by ID or something), should include a WITH (ROWLOCK) hint.  This means that when scanning a table for a specified recordset to delete, it will not lock the entire tables and cause this massive blocking you're experiencing.

I suspect that a combination of the slowed performance against the table parameter with significant rowcounts, along with deleting potentially thousands of rows from each related table without that lock hint, you would find things crawl and show persistent blocking patterns.

Regarding the transaction, I would keep the transaction to deleting around the delete statements only, or consider utilizing/wrapping the tsql into a stored procedure.  It depends on the usage, but typically I recommend steering away from cursors, for performance and best practices.

I can provide an example too if that's helpful.  :)

And if you're ever still unsure, if you're able to find a second set of eyes  - that's saved a few too!

Cheers!

-skibunnysqldiva


Can I default to T-SQL only view in VS 2012? When creating new tables in a database project in VS 2012, opened sql files default to a split view. One is the T-SQL text editor view, and the other is the Design view. I've never used the design view, and probably never will. I just prefer the text editor. Is there a way to tell VS to default to the T-SQL view?

I haven't found an option to turn off the design portion of the split view. Nor have I found a way to have the order of the windows remembered after closing a window, which is annoying!

However, if you want to ditch the SSDT editor completely you can change the default for .sql files in Tools/Options/Text Editor/File Extension, as shown below:


GROUP_CONCAT without limit by WHERE Buildings and Facilities have a many-to-many relation with a linking table of ids in between.  I would like to select Buildings with specific Facilities, and also show each Building's other Facilities in a single column.

Using GROUP_CONCAT works if I SELECT based on TABLE Buildings,

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/10

but if I also SELECT based on Facilities, only that Facility is returned in the GROUP_CONCAT.

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 AND `facility_id` = 2
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/11

How can I return results including all of each Building's facilities?

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END ORDER BY `facility_type`) AS `facilities2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`;

http://sqlfiddle.com/#!9/ceec20/13

  do you know a way to get rid of the rows with NULL for facilities2 in the third query?

We cannot refer to alias from output list in WHERE clause. To remove records with facilities2 IS NULL we must use appropriate condition in HAVING clause:

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END) AS `facility2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`
HAVING `facility2` IS NOT NULL;

http://sqlfiddle.com/#!9/ceec20/17


RMAN validate incremental level 1 backups? I am somewhat new to RMAN, and have taken an Oracle class on it, but I am stumped on validating my backups when using level 0 and 1; in order to save space, I am considering doing a level 0 (full) incremental backup Sunday, level 1 differential backups Monday and Tuesday, and level 1 cumulative on Wednesday.  Then leve1 1 differential backups Thursday and Friday, and level 1 cumulative on Saturday.

The thought was after a successful level 1 cumulative backup on Wednesday, I could delete the differential level 1 backups from Monday and Tuesday.  So, on Wednesday, to verify before I do something drastic to test, I did a

RMAN> restore database validate;

And away it went, validating files; but it only looked at files from the level 0 Sunday backup.

I would have expected it to also validate at least the incremental level 1 backup from Wednesday.

So two questions:

Is my strategy of deleting differential level 1 backups after a successful incremental level 1 backup valid?
How can I test to ensure this is correct?

First, you need to discover which backup is your level 0 and your 1. Then you need to validate the level 0 and the level 1 backups, independently.

 LIST BACKUP SUMMARY;

From the list, find the key for your level 0 and your level 1 backups.
Then validate them via:

 VALIDATE BACKUPSET 370, 412;

Substitute your key values in that. You can also add in the key values for archive log backups, if any.

See
https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta053.htm#RCMRF162 Example 2-157 Validating a Backup Set, for more details.

This is beyond the scope of your original question, but may be of benefit to you:

I run RMAN backup configuration scripts as part of my "new Oracle instance" checklist. That script looks like this:

run {
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
    configure compression algorithm 'MEDIUM' optimize for load false;
    configure device type disk backup type to compressed backupset;
    configure device type disk parallelism 2;  }

Then I do my backups via this, with either a level = 0 or a level = 1, depending on the day of the week:

backup device type disk incremental level = 0 section size 1g database plus archivelog tag 'level 0';
backup current controlfile tag='Controlfile Level 0';
backup spfile tag='SPFile Level 0';

I then do the following. Probably not necessary, but...

allocate channel for maintenance type disk;
backup validate check logical database archivelog all;

crosscheck archivelog all;
crosscheck backupset ;
crosscheck backup device type disk;
release channel;


SELECT with a GROUP BY column? The situation is like this:

I have a table that has one row for each copy of a letter, which also contains the letter ID.  I want a single query that outputs each letter copy row with the letter copy ID, letter ID and count of all copies for the letter ID ...

Table Rows

Letter Copy ID      Letter ID
1                   1
2                   1
3                   1
4                   2
5                   2
6                   3

Expected Output

Letter Copy ID      Letter ID      Letter Copy Count
1                   1              3
2                   1              3
3                   1              3
4                   2              2
5                   2              2
6                   3              1

If you don't have access to window-functions, you can use a subquery:

SELECT Letter_Copy_ID
     , Letter_ID
     , ( SELECT COUNT(1)
         FROM T AS T2
         WHERE T1.Letter_ID = T2.Letter_ID ) AS Letter_Copy_Count
FROM T AS T1

If you have access to window functions:

SELECT Letter_Copy_ID
     , Letter_ID
     , COUNT(1) OVER (PARTITION BY Letter_ID) AS Letter_Copy_Count
FROM T

You should avoid space in your identifiers (I used _ in my example), but if you feel you have to use them, you can quote them with ", as in "Letter Copy Count". Note that this makes the identifier case sensitive, so you will have to use the exact spelling.


SQLVDI error when backing up to Azure Storage BLOB I'm running a patched SQL 2014 trying to backup a database to one of our Azure Storage BLOBs, using:

BACKUP DATABASE [DB]
TO URL = N'https://storage.blob.core.windows.net/server-mssqlserver/DB.bak'
WITH CREDENTIAL = N'AzureCredential'
    ,NOFORMAT
    ,NOINIT
    ,NAME = N'DBA_DB-Full Database Backup'
    ,NOSKIP
    ,NOREWIND
    ,NOUNLOAD
    ,COMPRESSION
    ,STATS = 5
GO

but the query throws the following error:

  Msg 3292, Level 16, State 9, Line 1
  A failure occurred while attempting to execute Backup or Restore with a URL device specified. Consult the Windows Event Log for details.
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

Checking the server's Event Logs shows the actual error as:

  SQLVDI: Loc=IdentifySQLServer. Desc=MSSQLSERVER. ErrorCode=(5)Access is denied.
  . Process=4668. Thread=6596. Client. Instance=MSSQLSERVER. VD=.

I have made sure that the SQL Server Agent service's account has the Create global objects policy, and also made sure the SQL VSS Writer service is running under the Local System account. The error keeps happening!

Is there something I can do to fix it, or just log some more detailed error messages than the "SQLVDI: Loc=IdentifySQLServer" one above?

Crikey, this is one of those "no idea how I fixed it" things.

Before I went to lunch, I could reliably generate the error in question by running the provided T-SQL, yet when I came back from lunch the BACKUP command completed fine!

The main thing I remember changing was on the Azure side, where I created a SAS (Shared Access Signature).

This is supposedly not required for SQL 2014 as it uses an actual Azure Credential to connect to the storage instead. I actually created the SAS for an instance of SQL Server 2016 that I want backing up to the same container, and that may have opened the access pathway to the container for SQL Server 2014 too!

Many thanks to Sean Gallardy for recommending ProcMon, which showed a whole ton more error log information than the "ErrorCode=(5)Access is denied" message did.


Vertica INSERT speed highly depends on server load I faced a problem with very slow INSERTs in Vertica DB.
I made such test: create empty table with no projections and start inserting in it in batch, using

> INSERT INTO table
SELECT 111,222,333 UNION ALL
SELECT 444,555,666 UNION ALL
SELECT 777,888,999;

1000 rows in one query.

On idle server one query runs about 2sec and it's alright, but then I start generating some load making SELECTs (not very big load though, about 10 LA on 32 core server) and INSERTs start to performs 20x slower ( 40-60 sec each 1000 rows INSERT). It's too slow for my conditions. What can I do besides copying files to Vertica server and make COPY which seems to perform good, but too complicated?

You shouldn't be surprised that a load takes longer when there's more resource usage. However, there's probably a simple explanation for your poor performance. I would examine the following:

The INSERT with UNION ALLs is highly suspicious. Is the load more than 100MB? If yes, use COPY.
Are the projections optimized for load?
Is the query profiled and no locks being used on the target table?
Most importantly, is the load local? From a client across a wide network? Test a load from a local file as a benchmark.

Your "1000 rows" isn't helpful because your row can be 10MB wide, or you could be loading JSON, loading LONG VARCHAR, etc. An actual load size is more valuable. On a typical DL380 Gen2, you should see around 350GB/HR/node.


Tools to ingest data to database in periods We have an application that can work with Oracle or MySql or SQLServer databases. Also, there is a 3rd party data source that we need to have it's latest data records in our database every 15 minutes. We can call some API or raw data endpoint to pull data from 3rd party source.

If we write an application to do this job, it should do the following operations:

Call the 3rd party endpoint every 15 minutes (Calling the endpoint)
Convert the data to our database format (Data integration)
Insert data in our tables (Oracle or MySql or SQLServer)

So, is there any predefined tool to do this job for us or we should develop this application in-house? This is some kind of database sync job but it should be able to work with different databases and also it should be able to do the data integration.

Solution

Your solution contains two parts

Schedule a Job
Extract, Transform, and Load (ETL) the data

Job Schedulers

OSes have them (cron / task scheduler)
some databases have schedulers (oracle DBMS_SCHEDULER)
and there are enterprise COTS (Common Of The Shelf) software for inter-job scheduling and resource control

ETL

The simplest form would be a combination of shell scripts and stored procedures.

The shell script first copies (Extract) the data ( ftp,scp, REST call via curl ).
Then, it Loads the data into the database.  All RDBMS have tools to load CSV data.
After that, a stored procedures and/or SQL script is called to Transform the data into its final state.

These would most likely use INSERT...SELECT or MERGE statements.

Finally, the shell script archives the data file.

On the high end, COTS Software is available.

Build or Buy?

That's up to you.  You will need to do much more research.