understanding mysqldump auto-backup performance issues and inner functionality Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the dumping is in progress?

what if I reduce the time interval to 5 seconds (in order to have a real time update), does this affect the performance of my database server a lot?

Also how does the mysqldump command generate the sql file? ( does it generate the statements from scratch again and again as we execute the command, or it just has the statements generated ready, and just gives it when we execute the mysqldump command? )

From here, the complete mysqldump command for a hot backup of a MySQL database using only InnoDB tables is: (from here).

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

In addition to the Enterprise solution mentioned by @TrentLloyd, there is a F/LOSS solution available from Percona (who are big hitters in the MySQL world). I would recommend without hesitation any tool by Percona and I would put this first on my list for testing on any MySQL system which I work with.

What they offer is Percona XtraBackup which has an incremental backup functionality. The documentation is here.

You might also be interested in LVM snapshots - it's not a technology with which I am currently familiar but it's on my to-do list (like a lot of stuff :-) ). If you're familiar with all that stuff about mounting logical volumes &c., this may be of interest to you.

You could also look at zmanda - (mentioned in "High Performance MySQL").

I would concur with @TrentLloyd that regularly taking a full backup (at such small time intervals) doesn't make sense - your system will not be able to do any real work with a constant resource drain like that.

For completeness, you might want to look at the options here:
(Credit where credit is due, I "stole" this image from here :-) )

On a final note, there is an endless number of shell (mostly bash) scripts out there. A Google of "mysql incremental backup bash script" (or similar) will get you a shedload of these. One of the best (IMHO) of which (and one of the most popular) is automysqlbackup, but you may like to try out any/all/some of the others.

With bash now being available natively on Windows (traditionally poor in the MySQL backup area), these might be worth investigating for users of that OS.

An interesting variant on automysqlbackup (disclaimer - I haven't used it) is a script which will perform incremental backups which is to be found here.

Another way to backup - which is "hot" is to use replication - this process is explained in detail here. The MySQL documentation is available here.


How to prevent transaction log getting full during index reorganize? We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. The main reason I want to reorganize is to reclaim space and any performance benefit because of that is an added bonus.

The fragmentation level of the table is 30 - 35%. On some of these machines I get "transaction log full" error and reorganize fails. The transaction log size reaches upto 48gb. What is a good way to counter this? We do not have auto increment switched on and I am reluctant to do so.

I can increase the log size to a larger value but as the table size increases in the future, the value may not be enough. Also it defeats the purpose of doing reorganize to reclaim space if I am going to increase the log size equally. Any ideas on how I can effectively counter this? Using bulked mode is not an option since data loss is not acceptable.

Best practice is to REORGANIZE below around 30% fragmentation and REBUILD above this. Simply, REBUILD makes a clean copy, REORGANIZE does it in-situ.

Check what you're actually doing: you don't have a maintenance plan doing both do you?

On larger tables (50GB table is getting there) I've seen REORGANIZE consume all transaction log space if you follow this rule. Not often: only one system with a certain load pattern. The REORGANIZE just ran until the log expanded and consumed all disk space.

We switched to REBUILD instead with no more problems, but ignored fragmentation below 25%. This worked better for us: you'll have to see if it works for you.

REBUILD may affect the performance more than REORGANIZE in production, but this can sometimes be mitigated with the ONLINE option (Enterprise Edition required).


Large INSERT INTO SELECT [..] FROM gradually gets slower I've written a program that does a large INSERT in batches of 100,000 and shows its progress.

The source table contains 2.5GB of data:

CREATE TABLE wikt.text (
    old_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    old_text MEDIUMBLOB NOT NULL,
    old_flags TINYBLOB NOT NULL,

    PRIMARY KEY (old_id),
    KEY old_id (old_id)

)   ENGINE=INNODB
    AUTO_INCREMENT=23565544
    DEFAULT CHARSET=binary;

CREATE INDEX old_id ON text (old_id);

This is the destination table:

CREATE TABLE domains.dictionary_language (
    text_id     INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    english     TINYINT(1) UNSIGNED NOT NULL,

    PRIMARY KEY (text_id),
    KEY         english (english)

)   ENGINE=INNODB
    AUTO_INCREMENT=23565544;

This is the query being run in batches of 100k:

INSERT INTO domains.dictionary_language
    SELECT      old_id,
                IF(old_text LIKE '%==English==%', 1, 0)

    FROM        wikt.text

    LIMIT       {batch}, 100000;

The query is getting slower and slower. The first 1 million records inserted in 8 minutes. After that, records #1.2m - #1.3m alone took 7 mins. Now #2.3m - #2.4m just finished in 15 mins.

Here's the log of how long each batch of 100k takes to import.

As you can see, the first 12 batches (1.2 million records) insert in < 1 minute each. After that, the performance drops, with each batch taking a bit longer than the last!

24/11/2013 19:18:40 Ready

24/11/2013 19:18:42 Dictionary import started from Wiktionary
24/11/2013 19:18:42 Records:    3,729,613
24/11/2013 19:18:42 Batches of: 100,000

24/11/2013 19:19:11 Batch 1 finished in 00:00:29.3146767
24/11/2013 19:19:33 Batch 2 finished in 00:00:22.2142706
24/11/2013 19:19:41 Batch 3 finished in 00:00:07.6104353
24/11/2013 19:19:53 Batch 4 finished in 00:00:12.7057267
24/11/2013 19:20:08 Batch 5 finished in 00:00:14.9248537
24/11/2013 19:20:25 Batch 6 finished in 00:00:16.9849715
24/11/2013 19:20:43 Batch 7 finished in 00:00:17.7930177
24/11/2013 19:20:49 Batch 8 finished in 00:00:06.2453572
24/11/2013 19:21:07 Batch 9 finished in 00:00:17.2549869
24/11/2013 19:21:38 Batch 10 finished in 00:00:31.4577993
24/11/2013 19:22:02 Batch 11 finished in 00:00:23.7003556
24/11/2013 19:22:17 Batch 12 finished in 00:00:15.4078813
24/11/2013 19:23:40 Batch 13 finished in 00:01:22.9637452
24/11/2013 19:25:25 Batch 14 finished in 00:01:44.8639979
24/11/2013 19:27:40 Batch 15 finished in 00:02:15.1387295
24/11/2013 19:30:07 Batch 16 finished in 00:02:26.7553939
24/11/2013 19:33:01 Batch 17 finished in 00:02:54.3109701
24/11/2013 19:36:17 Batch 18 finished in 00:03:15.8252006
24/11/2013 19:39:57 Batch 19 finished in 00:03:40.1275906
24/11/2013 19:44:28 Batch 20 finished in 00:04:30.3824650
24/11/2013 19:49:48 Batch 21 finished in 00:05:20.6873423
24/11/2013 19:55:45 Batch 22 finished in 00:05:56.7674059
24/11/2013 20:02:37 Batch 23 finished in 00:06:52.0925703
24/11/2013 20:10:32 Batch 24 finished in 00:07:54.8921622
24/11/2013 20:18:12 Batch 25 finished in 00:07:39.9433072
24/11/2013 20:26:34 Batch 26 finished in 00:08:21.4696824
24/11/2013 20:36:00 Batch 27 finished in 00:09:26.3163915
24/11/2013 20:45:07 Batch 28 finished in 00:09:07.1472950
24/11/2013 20:54:48 Batch 29 finished in 00:09:41.0222326
24/11/2013 21:04:19 Batch 30 finished in 00:09:31.2316726
24/11/2013 21:14:35 Batch 31 finished in 00:10:15.3521962
24/11/2013 21:25:10 Batch 32 finished in 00:10:34.9583176
24/11/2013 21:36:27 Batch 33 finished in 00:11:17.6047568
24/11/2013 21:47:52 Batch 34 finished in 00:11:24.3261412
24/11/2013 21:59:32 Batch 35 finished in 00:11:40.2410515
24/11/2013 22:12:31 Batch 36 finished in 00:12:59.1605654
24/11/2013 22:26:12 Batch 37 finished in 00:13:40.9209540
24/11/2013 22:40:12 Batch 38 finished in 00:13:59.8160347

24/11/2013 22:40:12 Dictionary import finished

It's getting slower and slower with each batch of 100k! Why? Here's the EXPLAIN output.

The server itself is tuned up with a 4GB buffer pool etc.

The large offsets can have this effect. I would try to remove the offset and use only LIMIT 10000:

INSERT INTO domains.dictionary_language
  (text_id, english)
    SELECT      t.old_id,
                IF(t.old_text LIKE '%==English==%', 1, 0)

    FROM        wikt.text AS t
      JOIN      ( SELECT COALESCE(MAX(text_id), 0) AS offset
                  FROM domains.dictionary_language
                ) AS m
                ON  t.old_id > m.offset

    ORDER BY    t.old_id
    LIMIT       100000;


Remove sql server service from windows 7 I had installed and later uninstalled SQL Server 2005 . I used registry cleaner tools and deleted all files related to SQL Server . Still the services console shows entry for previous installed database instance name .
Services management console I get this entry in Description column.
Failed to Read Description Error code 2
How to delete them otherwise new installtion is showing problem.

Well, the crude way is to run regedit as admin, go down to

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services

and remove the service entry from registry and reboot...

Or perhaps use the sysinternals process explorer tool to do the same thing....


How to duplicate huge postgres table? I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single fast transaction delete the old one and rename the new one to original name.

What is the fastest way to duplicate such huge table?

Generally the fastest way to duplicate a table is simply:

CREATE TABLE table2 AS SELECT * FROM table1;

Parallel INSERTs may be faster, but only with a very fast disk subsystem (when data is interleaved on many drives). Otherwise this will be slower.

Once you're done with modifying table2, it can take the new name with:

BEGIN;
DROP TABLE table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;

The DROP TABLE command needs an exclusive lock, which affects concurrent readers in a way you may want to anticipate:

DROP will wait for any pending read on the table from other transactions to finish.
Any new transaction attempting to read that table in the meantime will be put it in wait, and then fail since the original table1 no longer exists. The error would look like "could not open relation with OID oid"

To avoid the second issue, you may rename table1  to old_table1  instead of dropping it, and then drop it only later outside of the transaction, when these readers are done with it. So the sequence above would become:

BEGIN;
ALTER TABLE table1 RENAME TO old_table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;
...
DROP TABLE old_table1;


Huge difference in execution time of update statement with default value Last week I tried deploying a new version of an application on an app server. Prior deploying, some database scripts needed to be executed on an Oracle 11g database.

The exact version of the database is Oracle Database 11g Release 11.2.0.1.0 - 64bit Production . Theree are no triggers on the tables.

The statement causing the headache is this one:

ALTER TABLE EMS_DATA ADD STATUS VARCHAR2(1) DEFAULT 'V' NOT NULL;

on a DDL that looks like this:

create table EMS_DATA
(
    CHANNEL_ID NUMBER not null,
    TSTAMP TIMESTAMP(3) WITH LOCAL TIME ZONE not null,
    VAL FLOAT,
    constraint EMS_DATA_PK primary key (CHANNEL_ID, TSTAMP)
)

The EMS_DATA PROD table contains around 350 million records and our ACC table contains around 280 million records. Both are Oracle 11g instances.

I ran the statement twice on the ACC table and the execution time was approx. 50ms and 300ms. The reason why it SHOULD be fast as this is the following:

  Metadata-Only DEFAULT Values

  Prior to Oracle 11g, adding a new column to an existing table required
  all rows in that table to be modified to add the new column.

  Oracle 11g introduced the concept of metadata-only default values.
  Adding a NOT NULL column with a DEFAULT clause to an existing table
  involved just a metadata change, rather than a change to all the rows
  in the table. Queries of the new column were rewritten by the
  optimizer to make sure the result was consistent with the default
  definition.

  Ref: https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1

So yes I was fairly confident the update would cause no issue on the PROD database. Instead, this happened;

The execution of the statement took > 120mins and eventually came to a halt because what had happened was that the disk drive was full. After inspection we saw that 180GB of .ARC (Archived Redo Logs) files have been written over this period of time.

TL;DR
What can cause this alter table statement to have this HUGE difference in duration and why does one database generate a ridiculous amount of .ARC files when the total amount of data in the table isn't even close to 180GB of disk space.
Can this Metadata-Only optimisation be switched off?

It's important to note that the application server was turned off at the moment of the script execution, so no outside process could have caused this.

From Database SQL Language Reference, 11g Release 2 (11.2):

  When you add a column, the initial value of each row for the new column is null.

  If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

  This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any update triggers defined on the table. In this release, no triggers are fired because the default is stored only as metadata. The optimized behavior is subject to the following restrictions:

  The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
  If the table has a Virtual Private Database (VPD) policy on it, then the optimized behavior will not take place unless the user who issues the ALTER TABLE ... ADD statement has the EXEMPT ACCESS POLICY system privilege.
  The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column.

  If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable.

Maybe the default values were actually written to the rows. So check the version of your database and the structure of you table!

There is a hidden parameter _ADD_COL_OPTIM_ENABLED in 11g that can be used to disable this metadata optimization and maybe the parameter COMPATIBLE. This is described in the metalink note

Init.ora Parameter "_ADD_COL_OPTIM_ENABLED" [Hidden] Reference Note (Doc ID 1492674.1)

But all these parameters are valid system wide and not only for a table. So if for one table everything works as expected the parameters seems to be set properly for the instance.

In this metalink noted cited above also the following query is published that shows the columns that were added with this metadata optimization:

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#;

With this query you can check if your columns are set with this metadata optimization. You can search for your table and column by

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#
    and object_name='EMS_DATA'
    and name='STATUS'

If this returns

no rows selected

then all rows where updated. The query

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#
    and object_name='ACC'
    and name='STATUS'

should return at least a row for your ACC table.


Moving one TempdB on 3 instance server I have a large virtual SQL Server (Full 2008R2).

I run 3 SQL Server instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file.

The trouble I am having is that, even though the query ALTER DATABASE executes successfully, and then restarting that particular instance's SQL Server Service, does not relocate that database file.

Do I have to restart the whole server to move this database?

Tempdb files are recreated each time the sql server starts.  After you change their location using ALTER DATABASE, you can verify your change by checking the dmv tempdb.sys.database_files.  (not sys.master_files, which won't be updated until sql restarts).

Sql will create new files for tempdb, and will leave the old ones on disk, so you should delete them.


Ensure Mongo sharding rebalance latest chuck The shard key i use is :

"_id" : {
        "pdm" : "1990-07-11",
        "hdocid" : "f23b7d7b2fc94339aed19983baddaeb3"
    }

pdm is a day level grouping key.
hdocid is a random hash key.

When I add new shard to the existing mongo, it rebalance the old chuck like "1990-07-10" rather then newer data like "1990-07-11".

The data I insert is from 1990-07-10 -> 1990-07-20 by order.

Thanks.

Yes, it is balancing "old" data because your choose bad shard key. Shard key should not start with linearly increasing key like time/date/number. Because (of the key) you add always data to the "end" of chunk, chunk is splitted at middle and lower (older) part is moved away.

If you want to distribute your query/insert load evenly to whole shard you could use {pdm:"hashed"} as shard key. Of course changing order of those two keys {hdocid:1, pdm:1} would distribute load too.


Switching a partition on a table with one to many relation There is two main tables in database (SQL Server 2014)

table1:
----------------------------------------------
id         name         datetime        image

table2:
--------------------------------
id         Address      phone     person_id

As you can guess there is an "one to many" relation between two tables, and the foreign key of [table2] is [person_id].

I'm trying to use partition table and sliding window features on table1. Everything perfectly done (I tested sliding window and partitioning on single table successfully) except one thing.

For sliding window , the oldest partition will be switch to an Auxiliary table (with same structure of table1) and the newest partition will split for new datas.

Partition function and scheme is created on datetime column.

As Microsoft mentioned,:

  For switching task, the all indexes must be aligned.

In our situation the table1 has a primary key that is unique non-clustered index too (the database made it automatically) and the foreign key of table2 connected to that.

When stored procedure tried to switching out the oldest partition, it rises an error:

  the table1 is partitioned but the [primary key index] column is not partitioned

So now the all indexes is not aligned because of this primary key !

I tried to include datetime to primary key, but database created an index for new primary key automatically on filegroup instead of partition scheme. Also tried other situations but no difference.

My final purpose is to switch last partition to auxiliary table and truncate that(about one billion records per partition). So the related records in table2 must delete too.

I'm looking for a solution to solve this situation and handle partitioning, switching and truncating on related tables with related columns (like this one).

Please don't attend so much to the structure or image storing stuffs! , I just tried to propose a concept from my circumstance.

You mentioned you added datetime to the PK, but it was not partitioned. You also need to specify the partition scheme when you recreate the PK index so that it is aligned.

Also, note there are restrictions on tables with foreign key relationships. See Transferring Data Efficiently by Using Partition Switching.

  Unfortunately, SQL Server didn't ask anything about index creation on partition or filegroup. I performed that with visual features.

The GUI is nice for some simple quick tasks but not for partitioning a table with billions of rows. Use T-SQL so that you know exactly what is going on. Large tables are unforgiving.


The difference between using parallelism "/*+parallel(4)*/" in the subquery or the outer query in terms of performance in Oracle I have the below query :
select /*+parallel(4)*/ ---> First Location
       <Desc_1> Amount,
       <Desc_2> Amount,
       <Desc_3> Amount
from (select /*+parallel(4)*/ ---> Second Location
             <Column_1>,
             <Column_2>
      from <Example_Table>
      where <Conditions>
      )
pivot(sum(Column_1) Amount
for <Column_2> in (1 as <Desc_1>,
                   2 as <Desc_2>,
                   3 as <Desc_3>)
                   );

The point is that <Example_Table> is a very big table and I want to ask where is the best to location to use the /*+parallel(4)*/ ? Is it better to use it in theSubQuery or in the outer select? What if we use /*+parallel(4)*/ in both outer and inner select? Is it bad for performance?

Well, if you have just one table, and your outer select is not accessing other tables, I would guess that "Second Location" would be the only one the optimizer looks at.  In practice, easy to test:  just do an explain plan on the two options, and see.  Then invoke it both ways, and verify what happens.
Oracle is pretty smart in doing the right thing for parallel queries; take a look at the following parameters, if neither option is producing a parallel execution plan:
select name, value from v$parameter where name like 'parallel%' order by 1;

And look at Oracle Database Reference section on "Initialization Parameters" such as parallel_adaptive_multi_user for details.
So nothing terrible is likely to happen if you put it in both places, but definately test in a development environment first.


How to speed up a multiple select query EDIT - Putting my whole stored procedure:

I have the below stored procedure:

CREATE PROCEDURE [dbo].[data_Update]
  @Owner NVARCHAR(255),
  @ID VARCHAR(255),
  @Division VARCHAR(255),
  @Manager VARCHAR(255),
  @TeamMembers VARCHAR(255)
  AS
  BEGIN
    DECLARE @OwnerName VARCHAR(255),
    @ManagerName VARCHAR(255),
    @Country VARCHAR(255),
    @TEMP VARCHAR(255),
    @Vendor1 VARCHAR(100),
    @Vendor2 VARCHAR(100),
    @Vendor3 VARCHAR(100),
    @INDEX INT,
    @Division1 INT,
    @Division2 INT

    SELECT @Manager = NAME, @Country = Country FROM Users WHERE EMAILID = @Manager
    SELECT @Owner = NAME FROM Users WHERE EMAILID = @Owner

    IF(@Division IS NOT NULL)
    BEGIN
        SELECT @INDEX = CHARINDEX('-', @Division)
        IF(@INDEX > 0)
        BEGIN
            SELECT @TEMP = SUBSTRING(@Division, 1, @INDEX - 1)
            SELECT @Division1 = ID FROM DivisionOne WHERE Value = @TEMP
        END
        IF(@INDEX = 0)
            SELECT @Division1 = ID FROM DivisionOne WHERE Value = @Division
        IF(@INDEX > 0)
        BEGIN
            SELECT @TEMP = SUBSTRING(@Division, @INDEX + 1, LEN(@Division))

            SELECT @Division2 = ID FROM DivisionTwo
            WHERE Value = @TEMP AND Division1 = @Division1
        END
    END

    DECLARE @MemberEmails TABLE(col VARCHAR(255))
    DECLARE @MemberNames TABLE(col VARCHAR(255))
    DECLARE @Names VARCHAR(255)

    INSERT INTO @MemberEmails
    SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

    INSERT INTO @MemberNames
    SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

    SELECT @Names =
       STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
       FROM   @MemberNames b
       FOR XML PATH('')),1,1,'')

    DECLARE @Vendors TABLE(col varchar(50))
    DECLARE @itm1 int, @itm2 int, @itm3 int,
            @Vids varchar(50)
    INSERT INTO @Vendors
      SELECT VendorID from VendorMapping where ID = @ID
    SELECT @Vids = STUFF((SELECT '; ' + col
                FROM   @Vendors
                FOR XML PATH('')),1,1,'')
    SELECT @itm1 = X.value('x[1]', 'int'),
           @itm2 = X.value('x[2]', 'int'),
           @itm3 = X.value('x[3]', 'int')
      from (select cast('<x>'+replace(@Vids, ';', '</x><x>')+'</x>' as xml)) as T(X)

    SELECT @Vendor1 = Name FROM Vendors WHERE Id = @itm1
    SELECT @Vendor2 = Name FROM Vendors WHERE Id = @itm2
    SELECT @Vendor3 = Name FROM Vendors WHERE Id = @itm3

    -- Populate tblDocuments
    DECLARE @Docs VARCHAR(MAX)
    DECLARE @pos INT
    DECLARE @len INT
    DECLARE @type VARCHAR(100)
    DECLARE @value VARCHAR(8000)
    DECLARE @name VARCHAR(1000)
    SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID
    SET @Docs = @Docs + '$'
    SET @pos = 0
    SET @len = 0

    WHILE CHARINDEX('$', @Docs, @pos + 1)>0
    BEGIN
      SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
      SET @value = SUBSTRING(@Docs, @pos, @len)

      IF CHARINDEX('|', @value) >0
      BEGIN
        SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
                @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
        INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)
      END

      SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1
    END

    --- update table
    UPDATE tblProjects SET OwnerName = @Owner, OwnerCountry = @Country,
    ManagerName = @Manager, Division1ID = @Division1,
    Division2ID = @Division2, Team = @Names, Vendor1 = @Vendor1,
    Vendor2 = @Vendor2, Vendor3 = @Vendor3 WHERE ID = @ID
  END

I am running this for every row in tblProjects, inside an SSIS job.
Currently this step takes a lot of time to complete, for the whole set of records.
Anything that can even slightly speed up the queries is appreciated.

I have added a SET NOCOUNT ON; in the procedure, and it seems to have given a slight performance boost.

It's quite difficult to give any suggestions based on that information, but here's the approach I normally use for performance tuning. The order of different steps can vary, but these usually give quite a good idea where the improvements should be done.

1) Check for blocking

if someone else is blocking the query, you should focus on the blocking query first

2) Set statistics io on.

This will show you the I/O count per table per statement when you run the procedure. If your sql is simple statements, I/O count and time spent in that table should be quite linear. Amount of data in cache makes a big difference for first vs. second run. If you're using user defined functions, I/O generated by those is not shown with statistics io.

3) Query plan, preferably actual plan.

Look at think arrows (=a lot of rows). There might be something to improve here, indexing, statistics, something to adjust in the query itself. This should be give similar picture as statistics io.

Key lookups, especially ones with big percentages or big actual execution count. If the number of output columns is small you could add include columns to the index. Remember, percentages are just estimates.

Look out for index / table scans, especially in cases where you're not reading most of the table.

4) Plan cache.

You can find statement level statistics from sys.dm_exec_query_stats. I usually look into total_worker_time and total_logical_reads, other columns can also give valuable information about where the problem could be.


Access Linked Server error "The provider did not give any information about the error." A little information.

I'm running SQL SERVER 2008 R2 64bit.
I have Microsoft.ACE.OLEDB.12.0 provider installed.

On my production server I've setup a linked server to the access db using the provider listed above and everything works great.

Copy the Access db to our dev environment and try to setup linked server useing the same provider.
Test Connection comes back as success
Select from linked server comes back with

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYdb" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYdb".

Both servers are running same version of SQL Server.  Both are Windows NT 6.1 64bit OS.

When I set AllowInProcess to 1 the test connection fails.

Any help would be greatly appreciated as Google is failing me.

Thanks,

Chris

I found the issue but I'm not sure why its happening.

To query the linked server I have to right click SSMS and select Run as Admin.  Then I can query the access db.

I dont know why this is the case as I'm remoted into the machine.  I'm an administrator on the machine.  I created the Linked Server to the Access Database which resides on the C:\Drive of the server.

But this will at least allow me to hand run my code.  Since this is my development server I think that will suffice at least for now.


MYSQL reduce column length without changing datatype Can we change the size of a LONGTEXT column in mysql without changing the datatype?

If you change the column length you still have to specify the data type in the ALTER TABLE statement, as well as NOT NULL, DEFAULT and COMMENT unless you want to use the defaults (i.e. NULL DEFAULT NULL without any comment):

CREATE TABLE table1 (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  col1 VARCHAR(45) NOT NULL DEFAULT 'a' COMMENT 'My favourite column'
);

ALTER TABLE table1 MODIFY COLUMN col1 VARCHAR(20) NOT NULL DEFAULT 'a' COMMENT 'My favourite column';


Backup SQL Server 2016 Instance Logins I need to backup a Instance of SQL Server 2016, I already have nightly backups of desired databases however I need to backup the users and roles for the instance..... How can i do this? Built in job or custom T-sql I can schedule to run?

I am not very good with SQL if anyone can point me in the right direction

In General

I would consider reading up on what information the master database contains and why you need to backup the system databases (master, msdb, ...).

Reference: Back Up and Restore of System Databases (SQL Server) (Microsoft Docs)

master Database

The master database contains the following information for each SQL Server instance:

  The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

(emphasis mine)

Reference: master Database (Microsoft Docs)

msdb Database

The msdb database contains the following information of a SQL Server instance:

  The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

  For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb. This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored. SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows application log, and the SQL Server error log. To help your protect the information that is stored in msdb, we recommend that you consider placing the msdb transaction log on fault tolerant storage.

(emphasis mine)

Reference: msdb Database (Microsoft Docs)

Conclusion

If you backup the master database, then all the login information for the instance is safe. However, if you want to separately back up (as in: used to transfer logins to different instances, used to reset passwords) the SQL Logins of the instance, then you would have to apply a different solution.

Individual SQL Server Login Backup Solutions

Tool Reference List

dbatools.io (dbatools.io)

Copy-DbaLogin
Export-DbaLogin
Copy-DbaCredential
Copy-DbaAudit

Transfer Login Task (Microsoft Docs)

Added bonus

How to transfer logins and passwords between instances of SQL Server (Microsoft KB article)

The last reference is an interesting article on how to manually export the SQL Server logins and the hashed passwords in order to manually transfer them to a a new SQL Server instance. The article involves creating two stored procedures and then running one to create the information.

Here is the code from the refercned article to create the two procedures:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Once you have create the stored procedures run sp_help_rev_login to retrieve the information.

EXEC sp_help_revlogin

  The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Reference: How to transfer logins and passwords between instances of SQL Server (Microsoft KB article)

Enjoy


How can I Search for a string in all fields across all tables of a MySQL database? I want to find a string and I don't know exactly which column and which table.

How can I find it?

Thanks.

This is a grueling problem and I have grueling suggestions
SUGGESTION #1: Use information_schema
See my old posts on how to go about doing this

Jul 19, 2012 : Query to find and replace text in all tables and fields of a mysql db
Mar 19, 2013 : How to search whole MySQL database for a particular string

SUGGESTION #2: Use mysqldump
You should be able to hunt down the string using mysqldump
Let's say the string you are looking for is 'Hello, World'
You can mysqldump all user-defined databases into a text file and grep the text file:
STRTOFIND="Hello, World"
DUMPFILE=mysqldump_to_search.sql
SRCHFILE=Search_Resutls.txt
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers -B ${DBLIST}"
mysqldump ${MYSQL_CONN} -A ${MYSQLDUMP_OPTIONS} > ${DUMPFILE}
grep -n "${STRTOFIND}" ${DUMPFILE} > ${SRCHFILE}

The search results file will not only have the line the string is located, but also the linenumber within the dump file.
GIVE IT A TRY !!!


I have been tasked with Mysql Master-Master replication? I have done some study and i came to know that Mysql master-master replication violates the ACID properties and there has been problem with primary key collision.Please Let me Know,Apart from these are there any disadvantages?

When using Master-Master Replication Setups (a.k.a Circular Replication), we tend to take things for granted. There are two aspects worth mentioning:

ASPECT #1 : PRIMARY KEY COLLISION

You mentioned primary key collision. That will definitely happen if you write to the same table on both Masters within the same window of time and that table has an auto_increment primary key. To circumvent this, MySQL added the following

auto_increment_increment
auto_increment_offset

The key to using these is the following:

set auto_increment_increment to be the same value on all nodes in Circular Replication topology.
set auto_increment_offset to be the any unique value >= 0 and less than auto_increment_increment

There are two instances where you do not have to use auto_increment_increment and auto_increment_offset in a two-node setup (DB1 and DB2)

INSTANCE #1: All writes (INSERTs, UPDATEs, DELETEs, DDL) occur in DB1 only

This allows DB2 to to be a hot standby
You can perform SELECTs from DB2 to reduce read I/O on DB1
You can perform mysqldump backups from DB2 without disturbing DB1
This allows you to add additional read-only slaves (See my post Adding slave to existing master-master config throws foreign key constraint failed error)

INSTANCE #2: In the event you have multiple databases in each node

If you write to the mydb1 on DB1, you read from mydb1 on DB1 and never from DB2
If you write to the mydb2 on DB2, you read from mydb2 on DB2 and never from DB1

I have discussed before : (May 07, 2012 : Setting Circular Replication in mysql)

ASPECT #2 : DATA DRIFT

This is a rather murky situation when binlog events do not executed on the Slave.

The factors involved here

Binary Logs
Relay Logs
Storage Engine
Network Transmission

It is relatively easy to setup Replication. It also relative easy to forget that binary logs, relay logs, and moving parts of InnoDB and MyISAM require conscientious flushing to disk.

Settings for Flushing Logs (See my post MySQL Master-Master Replication not syncing)

sync-binlog
sync-relay-log
sync-relay-log-info
sync-master-info

Flushing Storage Engines

Set innodb_max_dirty_pages_pct to 0 to increase flushing activity
Running FLUSH TABLES tblname; on MyISAM tables after bulk INSERTs, UPDATEs, and DELETEs
cronjob to run SET GLOBAL innodb_buffer_pool_dump_now = 1; (MySQL 5.6 only, see innodb_buffer_pool_dump_now). This dumps a numeric map of the Buffer Pool's data and index pages to Disk. You would also configure innodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_load_at_startup
This is not an all-inclusive list. Your application must become more cognizant of the need to flush to disk

Networking

You must make sure the network traffic is not inundated, your switches are fine, any crossover cabling to bypass the network is not defective, there are no dropped packets,.
In terms of MySQL, the Replication I/O thread is dependent on the network. You must make sure mysqld can regularly heartbeat its master. Perhaps semisync replication could be installed and configured for this.


Implementing json_object_agg() in Postgres 9.3 I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table click_activity of data that looks like

user | offer | clicks
-----|-------|--------
fred |coupons| 3
fred |cars   | 1
john |coupons| 2

But I want to turn it into this: (aggregate the activity per user)

user | activity
-----|----------
fred | {"coupons": 3, "cars": 1}
john | {"coupons": 2}

I think that the json_object_agg() function of Postgres 9.4 would do this perfectly, all I would have to call is

select user, json_object_agg(offer, clicks) from click_activity group by 1

Is there a way to do this in 9.3? Thank you!

I was able to emulate json_object_agg using string_agg (which is available in 9.3).

Your example would be:

select user, ('{' || string_agg('"' || offer || '": ' || clicks, ',') || '}')::json as activity
from click_activity
group by user


Sql Server 2016 Counter Reporting Zero I utilize the resource governor on my 2016 Sql Server.  In prior 2016 sql server  versions, this query:

select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'

Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load.  However, now they are always zero.  I also used perfmon to confirm this (along with running legit queries).  Does anyone know why or if this counter changed?  According to the msdn article, it should still work.

You posted a bug on this at Microsoft Connect:

SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3

Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.


Which first: upgrade mysql version or convert storage engine? I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets.

I am planning on:

upgrading to 5.5
converting existing databases to innodb

I will only do one at a time:

upgrade --> wait --> convert

or

convert --> wait --> upgrade

What is best to do first?

Upgrade to 5.5
Convert to InnoDB?

Of the choices, I would update, then convert.

InnoDB received some massive performance boosts in 5.5, so by upgrading first you get some features that 5.5 introduces, and then you can convert to InnoDB to get full innodb benefit.

5.0 InnoDB performance was not great, so you might see some strange issues by converting to InnoDB prior to updating to 5.5

As always though, read through the MySQL 5.5 upgrade notes to make sure there's nothing new that would break your current implementation.

And, of course, test it out on a non-production database first.


SQL Server cluster failover If I have a cluster and I pause the SQL Server Service on the active server with the cluster interpret this as a failure and attempt to fail-over?

I tried this on a failover cluster test on my laptop (Windows 2008 Enterprise eval running SQL Server 2008 R2 SP1), open SQL server configuration manager and stop SQL server service on the (active) node wait for couple of minutes but somehow SQL server db service did not failed over?

Failover clustering works to provide constant service in the event of a failure (defining a failure as an "abnormal termination of the previously active application, server, system, or network" - http://en.wikipedia.org/wiki/Failover).  Manual manipulation (start, restart, pause, and stop) upon the service being clustered (SQL Server, File Services, etc.) does not qualify as an abnormal termination of the service.

If I recall, at least in Windows Server 2008, you can simulate a failover test case in the Failover Management Console under Administration Tools.  Check this out for a list of testing methods: http://blogs.technet.com/b/vipulshah/archive/2009/06/17/failover-cluster-testing-methods.aspx


What would be the most efficient way to check for null values in all columns for multiple tables? Say I have three tables or four tables that I want to check for values present and I don't want any of them to be null. IF they are null, I will send an email notification per row. What would be the best way to go about this? Should I make a stored procedure with IF statements separately?

I say this because all these tables are not having any constraints, they just pull data out of an excel sheet. So making a table type variable and unioning or inner joining returns nothing as even the column names are different, but the information they store is the same kind of information. So for example if in one table, a column exists called 'DueDate', in the other table, this same sort of information is called 'PaymentDate'. How would I perform such a check on all these tables?

Update: Here's what I've thought of as of now: Say the two tables are A and B, then

DECLARE @messageBody NVARCHAR(MAX)
DECLARE @iterator int
DECLARE @Rowcount int
DECLARE @varTableA TABLE(tablecounter int IDENTITY(1,1), DueDate date, Account NVARCHAR(20), Retailer NVARCHAR(20), Interest float)

INSERT INTO @varTableA (DueDate, Account, Retailer, Interest)
SELECT DueDate, Account, Retailer, Interest
FROM TableA
WHERE DueDate IS NULL OR Account IS NULL OR Retailer IS NULL OR Interest IS NULL

SET @Rowcount = @@ROWCOUNT
SET @iterator = 1

WHILE(@iterator <= @Rowcount)
BEGIN

SET @messageBody = (SELECT 'No values in following columns' + 'Duedate: ' + ISNULL(Duedate, 'No value') + CHAR(10) + CHAR(13)
+ 'Account: ' + ISNULL(Account, 'No value') + CHAR(10) + CHAR(13)
+ 'Retailer: ' + ISNULL(Retailer, 'No Value') + CHAR(10) + CHAR(13)
+ 'Interest: ' + ISNULL(Interest, 'No Value') + CHAR(10) + CHAR(13)

                    FROM @varTableA
                    WHERE @tablecounter = @iterator)

EXEC msdb.dbo.sp_send_dbmail @profilename = [name], @recipients = [reclist], @subject = [sub], @body = @messageBody
SET @iterator = @iterator + 1
END

And to repeat this in either the same or a separate stored Procedure for TableB and Table C. Is there a better way of doing this or do you think I am going about this the right way?

For part 1 of your question, I created these three tables in an empty database:

CREATE TABLE dbo.table1(a INT, b INT);
CREATE TABLE dbo.table2(a INT, b INT);
CREATE TABLE dbo.table3(a INT, b INT);

INSERT dbo.table1(a,b) VALUES(1,1),(1,2),(1,NULL);
INSERT dbo.table2(a,b) VALUES(1,1),(NULL,NULL),(1,NULL);
INSERT dbo.table3(a,b) VALUES(1,1),(1,2),(1,2);

Then I wrote this code to extract various bits of NULL information:

CREATE TABLE #x
(
  t           NVARCHAR(512),                     -- table name
  nullrows    INT NOT NULL DEFAULT 0,            -- number of rows with at least one NULL
  nullvalues  INT NOT NULL DEFAULT 0,            -- total NULL values in table
  nulldist    NVARCHAR(MAX) NOT NULL DEFAULT N'' -- how many NULLs in each column
);

INSERT #x(t) VALUES(N'dbo.table1'),(N'dbo.table2'),(N'dbo.table3');

Now some dynamic SQL fun derived from metadata:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'
  UPDATE #x SET nullrows = nullrows
    + (SELECT COUNT(*) FROM ' + t.t
    + N' WHERE (' + STUFF((SELECT N' OR '
    + QUOTENAME(c.name) + N' IS NULL'
  FROM sys.columns AS c
  WHERE OBJECT_ID(t.t) = c.[object_id]
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),
    1, 4, N'') + N')) WHERE t = N''' + t.t + N''';'
FROM #x AS t;

EXEC sys.sp_executesql @sql;
SET @sql = N'';

SELECT @sql = @sql + N'
  IF EXISTS (SELECT 1 FROM ' + t + N' WHERE '
    + QUOTENAME(c.name) + N' IS NULL)
  UPDATE #x SET nullvalues = nullvalues
    + (SELECT COUNT(*) FROM ' + t + N' WHERE '
    + QUOTENAME(c.name) + N' IS NULL),
    nulldistribution = nulldistribution + '''
    + c.name + N':'' + RTRIM((SELECT COUNT(*) FROM '
    + t + N' WHERE ' + QUOTENAME(c.name) + N' IS NULL))
    + N'','' WHERE t = N''' + t + N''';'
  FROM #x AS t
  INNER JOIN sys.columns AS c
  ON OBJECT_ID(t.t) = c.[object_id];

EXEC sys.sp_executesql @sql;

SELECT * FROM #x;

Results:

t             nullrows   nullvalues   nulldistribution
dbo.table1    1          1            b:1,
dbo.table2    2          3            a:1,b:2,
dbo.table3    0          0

I'll leave it as an exercise to the reader how to send an e-mail based on those results - that does not exactly seem like the hard part of this.

For part 2 of your question, as I said in a comment:

  There is no code you could possibly write that would tell you that DueDate and PaymentDate contain the same kind of information, other than checking that they share the same data type. This is what source control and documentation are for.

I would argue that this is a different and unanswerable question.


Are there other ways to select a dynamic list of columns? I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that.

1. Using refcursors

CREATE OR REPLACE FUNCTION selecttestwithcolumnlist(
ticker character varying,
columnlist character varying)
  RETURNS refcursor AS
$BODY$
DECLARE
  ref1 refcursor;
BEGIN

OPEN ref1 FOR EXECUTE
'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'
USING     Ticker;
RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE

This function is very easy to invoke from my Ado.Net client. All I need to do is pass the parameters. However, if I want to test this function from pgAdmin, the result set is open on screen only if I keep my transaction open. This is inconvenient. Of course, it is easy to expose the data as an HTML table or an Excel spreadsheet, but this is kind of a minor inconvenience.

2. Using setof records

CREATE OR REPLACE FUNCTION SelectPrices(colList VARCHAR)
 RETURNS SETOF record AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || ' FROM prices ORDER BY Ticker, ASOfDate';

END
$func$ LANGUAGE plpgsql;

Unfortunately, this complicates my client code. I cannot issue a simple SELECT like this:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') ;

I must explicitly provide the structure of my result set:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker')
AS f(price NUMERIC,AsOfdate TIMESTAMP,ticker VARCHAR);

This is doable, but inconvenient.

Are there other ways to return dynamic column lists?

Edit to protect against SQL injection, I typically split the comma-separated list and join it against a system view. Anything that is not an actual column name is not returned. I did not mention that originally, just to keep the question short.

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question.

CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || '
 FROM   prices
 WHERE  ...
 ORDER  BY ...';

END
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE t (col1, int, col2, date);
SELECT * FROM select_prices(NULL::t, 'col1, col2')

Or, to keep results in the temp table:

INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2')

If you need multiple tables in the same sessions, employ a sequence to get unique names. Related answer on SO:
Create a temporary table from a selection or insert if table already exist

However, this method (just like the other two in your question) are susceptible to SQL injection. You need to make sure it can't be abused.
SQL injection in Postgres functions vs prepared queries

Again, I would try to use this simple statement instead:

CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;


Select a table with multiple columns according to IN clause I've a query

SELECT t1.user,t1.answer as 'Q1',t2.answer as 'Q2'
from
(select answer,user from survey_answer where question_id=45) t1
JOIN
(select answer,user from survey_answer where question_id=46) t2
ON
t1.user=t2.user

In this the question_id i.e. 45,46,47,48,...etc. is in hundreds
the number on join increases number of columns as q1,q2,q3... so on...

like :

________________________
user | q1 | q2 | q4 | q5
------------------------
1    | ok | yes|    | no
------------------------
2    |    | yes| no |

Is there a way to form the query using IN(45,46,47,....) or use loop in procedure/function...

If you don't need a column for each answer you can use group_concat() to build a single string with all answers per user.

drop table if exists survey_answer;
create table if not exists survey_answer(user int, question_id int, answer varchar(200));
insert into survey_answer values
(1,60,'answer 1'),
(1,61,'answer 2'),
(1,62,'answer 3'),
(2,61,'answer 4'),
(2,52,'answer 5'),
(3,40,'answer 6'),
(3,62,'answer 7'),
(4,61,'answer 8'),
(4,62,'answer 9'),
(4,10,'answer 10'),
(4,32,'answer 11');

select distinct(t1.user),
       (select group_concat(t2.answer, ',')
        from survey_answer t2
        where t2.user = t1.user) answers
from survey_answer t1
;

drop table if exists survey_answer;

This is the result:

| user | answers                                   |
|------|-------------------------------------------|
| 1    | answer 1,,answer 2,,answer 3,             |
| 2    | answer 4,,answer 5,                       |
| 3    | answer 6,,answer 7,                       |
| 4    | answer 8,,answer 9,,answer 10,,answer 11, |

Rextester here


Clean up orphaned documents in MongoDB error While trying to clean cleanupOrphaned  in my cluster

I am getting this error.

OK..

Step 1. database and collection must be originally created (or sharded) thru mongoS

Step 2. command db.runCommand({cleanupOrphaned: "test.collection"}) must be ran on individual shard node (if replica set, then primary) at admin database. Not on mongoS, if you run that command on mongoS, you get that error message (and of course that error don't tell you that mongos cannot support that command).

#>mlaunch init --sharded 2 --single
launching: mongod on port 27018
launching: mongod on port 27019
launching: config server on port 27020
replica set 'configRepl' initialized.
launching: mongos on port 27017
adding shards.

#>mongo --port 27017
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
MongoDB Enterprise mongos> use test
switched to db test
MongoDB Enterprise mongos> db.collection.insert({})
WriteResult({ "nInserted" : 1 })

#> mongo --port 27018
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27018/
MongoDB server version: 3.4.6
MongoDB > use admin
switched to db admin
MongoDB > db.runCommand( {cleanupOrphaned: "test.collection"})
{ "ok" : 1 }


Need help with my MySQL PROCEDURE #1193 - Unknown system variable I've this simple table:

CREATE TABLE IF NOT EXISTS `testing`
(
    `testing_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `meter` INT NOT NULL,
    `price` DECIMAL(12,2) NOT NULL,
    `price_per_meter` DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`testing_ID`)
)

And I've written this MySQL PROCEDURE:

DELIMITER $$
CREATE PROCEDURE `price_per_meter_calc` (price DECIMAL(12,2))
BEGIN
    SET price_per_meter = price/meter;
END
$$
DELIMITER ;

but I've got an error #1193 - Unknown system variable 'price_per_meter'. I believe it's pretty straightforward code. What I want is simply MySQL to automatically calculate price_per_meter given the user will only insert price and meter values.

You need to declare variable first and then only you can set the value.

Something like:

DECLARE price_per_meter DECIMAL(6,2);

Then other part is just logics.


Exporting/Importing a large MySQL database without mysqldump I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy.

After running a simple --all-databases dump succeeds, but the import always encounters random (and frequent) errors. These are generally syntax errors in the SQL statements because of incorrectly escaped quotation marks within the dump file.

I've done raw data transfers before by copying the actual data files, however the destination server I seek to import into does not allow me to access the file system.

Are there any other tools that are less buggy than mysqldump for very large exports/imports?

For MySQL, you have these methods (beside mysqldump):

Select ... into outfile: support all engines, in can be use for a warm backup, it's slow for backup and recovery.
mk-parallel-backup: support all engines, used it for warm, backup and recovery speed is medium.
ibbackup: all engines, warm (if you use this method for only INNODB it can be hot), and it's fast for backup and recovery

here is a good image about backups.


Check Constraints on SQL Server Database advisable for number range & year & month? I'm busy designing a website and I have a requirement for the following columns in my SQL Server database:

allocation (values from 0 to 100)
year (only)
month (only)

I have read about check constraints and I'm wondering if the following would be feasible / and if it would be the best solution for the above:

allocation  | tinyint | not null | check constraint: 0-100
year        | date    | not null | check constraint: year
month       | date    | not null | check constraint: month

I will be validating from my front end side as well but I heard it's good practice to also design your DB with these restrictions in place where appropriate.

Yes, use check constraints

Yes, you should add check constraints in your database to ensure valid values, as well as checking in your app. The database side makes for a double-check in case your app’s code has a hole or bug. Also, you may use other apps such as db admin tools to interact with your data, so it is good to have the database enforce the rules.

This kind of data validation is exactly the purpose of the check constraints.

Check year and month too

By the way, your year and month columns should not be of data type DATE. Use a small int for them too, and add constraint checks for reasonable year values such as >2016 and for months being 1-12.

ISO 8601

Also consider storing your year-month values as a single column of type text/varchar, formatted according to the ISO 8601 standard: YYYY-MM such as 2017-03. Sorting alphabetically happens to also be chronological.

By the way, Java includes a YearMonth class to represent this kind of value. That class can generate and parse that ISO 8601 formatted string by default with its toString and parse methods.

Avoid keywords as names

Avoid using any of the over one thousand keywords and reserved words used by various  databases. Your "year" and "month" names may be problematic.

Simple solution I use is to append a trailing underscore in all my SQL names (catalog, schema, table, column, index, etc.). So, year_ and month_. The SQL spec explicitly promises that no keyword will ever have a trailing underscore.


How to select only first column dynamically on mysql I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following:

id
article_id
user_id
product_id
page_id
etc..

Is there any way always getting only first column. For example:

SELECT 0 FROM table;

or

SELECT FIRST_COLUMN FROM table;

It's not clear why you need this but technically you can do it with dynamic SQL like this

SET @sql = NULL;
SELECT column_name
  INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = 'users'
   AND ordinal_position = 1;

SET @sql = CONCAT('SELECT `', @sql, '` FROM users');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

You can wrap it up into a stored procedure

DELIMITER $$
CREATE PROCEDURE select_first_column(IN _tname VARCHAR(64))
BEGIN
  SET @sql = NULL;
  SELECT column_name
    INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
     AND table_name = _tname
     AND ordinal_position = 1;

  SET @sql = CONCAT('SELECT `', @sql, '` FROM `', _tname, '`');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL select_first_column('users');
CALL select_first_column('orders');

Sample output:

| USER_ID |
|---------|
|       1 |
|       2 |

| ORDER_ID |
|----------|
|      101 |
|      202 |

Here is SQLFiddle demo


Find nearest values from MySQL Table I want to fetch nearest rates for a given value from below table.

mysql> select * from rates;
+------+----------+----------+
| ID   |   Type   |   Rate   |
+------+---------------------+
| 1    |    A     |    12    |
| 2    |    A     |    18    |
| 3    |    A     |    25    |
| 4    |    A     |    27    |
| 5    |    A     |    34    |
| 6    |    A     |    38    |
| 7    |    B     |    40    |
| 8    |    B     |    43    |
| 9    |    B     |    50    |
| 10   |    A     |    55    |
| 11   |    A     |    58    |
| 12   |    A     |    62    |
+------+---------------------+

For example I want to get rates nearest to 12 it should return 12, 18, 25 and like wise nearest to 25 are 18,25,27

I tried many ways but I'm unsuccessful.

SELECT ID,
       TYPE,
       Rate
FROM (
        (SELECT *
         FROM
           (SELECT *
            FROM rates
            ORDER BY Rate DESC) a
         WHERE a.Type ='A'
           AND a.Rate <=25 LIMIT 0 ,
                                 1)
      UNION
        (SELECT *
         FROM
           (SELECT *
            FROM rates
            ORDER BY Rate ASC) b
         WHERE b.Type ='A'
           AND b.Rate >25 LIMIT 0 ,
                                3)) r
GROUP BY r.Rate
ORDER BY r.Rate ASC LIMIT 3

It gives me 25, 27, 34 but in fact it should return me 18,25,27. Any suggestions?

SELECT id, type, rate
FROM
( ( SELECT id, type, rate, 25-rate AS diff
    FROM rates
    WHERE type = 'A'
      AND rate < 25
    ORDER BY rate DESC
      LIMIT 3
  )
  UNION ALL
  ( SELECT id, type, rate, rate-25 AS diff
    FROM rates
    WHERE type = 'A'
      AND rate >= 25
    ORDER BY rate ASC
      LIMIT 3
  )
) AS tmp
ORDER BY diff
LIMIT 3 ;

Test at SQL-Fiddle


Do data types go into a Relational Data Model (RDM)? I have recently been asked to draw a Relational Data Model for a flight information database. I came up with the design in the picture below, but I was told that the drawing is not correct because that is not quite what is meant by RDM and also because the data types are not included. Is this true? I have looked into many articles and books and I have not seen yet any RDM with data types in it.

Thanks!

In a nutshell:
Your diagram would be more complete if it showed the data types -- and a relational DB design must have data types specified -- but as a brief summary, it is OK to omit types for brevity, especially if your field names are descriptive enough.

Strictly speaking, the relational DB model includes the table and the column where a table is a tuple of columns and each column is of a particular type.

What you are drawing is a relational DB schema or design -- an application of the model for a particular business case.
Although, again, strictly speaking, what you have drawn is a hybrid between an entity-relationship diagram (ERD) and a relational schema -- even though it is a very common and useful style of diagram.

An ERD would not have any surrogate (system-generated) keys, as those are not part of the business semantics, but would have the lines -- labelled with 1 or m -- representing logical connections between entities.

On the other hand, a relational schema would not have any connecting lines as these links are not explicitly part of the relational model -- the links are implied by fields having the same data type -- but it would have surrogate keys.

As for the missing data types, yes, ideally both the DB schema and the ERD are much more complete if/when they also specify those, but that is not to say you cannot have diagrams of different levels of detail for economy of communication.


mariadb: setting AUTO_INCREMENT using SELECT I am migrating a MariaDB database from my personal machine to a test
server.  The database schema has changed so I need to recreate the
tables.  The source database is also a mix of data that I want to keep
and a lot of test junk that I weeded out of the dump text file
manually. Since I dropped a lot of data, I want to update the AUTO_INCREMENT
value back to the new maximum.

This works:

MariaDB [imok]> alter table accounts AUTO_INCREMENT 114;
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0

But when I try to create a generic statement using a select, MariaDB
complains:

MariaDB [imok]> alter table accounts AUTO_INCREMENT (select max(AcctID)+1 from accounts);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select max(AcctID)+1 from accounts)' at line 1

I tried using a variable, with the same result:

MariaDB [imok]> set @autoinc=(select max(AcctID)+1 from accounts);
Query OK, 0 rows affected (0.00 sec)

MariaDB [imok]> select @autoinc;
+----------+
| @autoinc |
+----------+
|      114 |
+----------+
1 row in set (0.00 sec)

MariaDB [imok]> alter table accounts AUTO_INCREMENT @autoinc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@autoinc' at line 1

QUESTION: How do I set the AUTO_INCREMENT value to something other than
a literal value in MariaDB?

NOTE: I am using MariaDB not MySQL.

NOTE: The '=' in the ALTER statement is optional, MariaDB gives the same complaint
with or without it; setting AUTO_INCREMENT using a literal value 113 works
without or without the '='.

I think best would be to remove the value of auto_increment from import script/dump - let it start at 1, then insert all the rows with fixed ID and let InnoDB do the work:

"If you insert a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value." - from manual

If this is not possible, then you can use a variable in the ALTER. You might do that using prepared statement:

SET @query = CONCAT('ALTER TABLE accounts AUTO_INCREMENT = ', @autoinc, ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


MYSQL Optimize tables on Innodb in production environment advice According to mysqltunner.pl I have 177 Fragmented tables. I have over 5000 tables and 200 databases.

If I run optimize table on each of them, do you think I should take down the application to prevent reads and writes while I do this? I know that optimize locks the table, but if it is a pretty quick operation I don't know if it was cause any problems.

You can optimize a table without interrupting service by using pt-online-schema-change:

$ pt-online-schema-change --alter="ENGINE=InnoDB" --execute D=sakila,t=actor

Also, MySQL 5.6.17 supports OPTIMIZE as an online DDL change, allowing concurrent DML.


How does SQL*Plus connect to Oracle without a listener? sqlplus can be used even if there is no listener in the system, or if it isn't running.

How does SQL*Plus connect to Oracle without a listener?

I can connect any user using SQL*plus even with no listener running.

As @BalazsPapp mentioned, the oracle db can be connected locally even without a listener. In this case, the BEQ (Bequeath) protocol is used, instead of the tcp-based normal oracle communication.

Although on Balazs's link there is nothing about the details of the BEQ. On unix, it probably uses unix domain sockets. On windows, it uses probably some local rpc.


Have I misunderstood the amount of "automatic optimization" that PostgreSQL does without me having to be "clever" when designing my tables? Suppose I have a table such as this, for logging all web requests made to my website:
CREATE TABLE weblog
(
    id      bigserial,
    moment      timestamp with time zone NOT NULL DEFAULT now(),
    ua      text, -- user agent
    ref     text, -- referer
    "IP-addy"   inet NOT NULL,
    PRIMARY KEY (id)
)

Most people will be sending the same user-agent, such as "MOSAIC 1.0". Many will send the same referer as well. This means that, although the id, moment, and IP-addy columns will differ, a huge amount of records/rows in the table will end up having the same values for "ua" and "ref".
This really got me thinking about storage space and efficiency, but having experimented with this for a few weeks now, I've basically come "full circle", back to thinking that this does make sense after all. Allow me to explain.
Although seemingly a gigantic waste, storing these repeated values on the disk may be necessary for performance. I'm not sure about it, but it seems like it probably is.
When I back up my database, which was my biggest worry rather than it taking a lot of disk space while "live", I apply the maximum compression to pg_dump (level 9), which I assume means that all of those duplicated values will turn into a single string stored, and then just referenced with small bits for each record which contains the same cell value. This seems to be the case because the difference between using level 0 (no compression) and 9 is pretty huge in the file size of the resulting .pg_dump archive (using the oddly named "custom" (binary) format).
I didn't originally think about this, but since it applies compression, the backups probably already are as small as they could be, regardless of my table structure.
Currently, I have them structured like this:
CREATE TABLE weblog
(
    id          bigserial,
    moment          timestamp with time zone NOT NULL DEFAULT now(),
    ua          bigint, -- user agent
    ref         bigint, -- referer
    "IP-addy"       inet NOT NULL,
    PRIMARY KEY     (id),
    FOREIGN KEY     blabla,
    FOREIGN KEY     blabla
)

And then I have one table for each of "ua" and "ref", where I just store a unique string value with a bigserial id and then associate it with the ua/ref bigints (they are no longer text). This makes both SELECT and INSERT queries cumbersome and require an extra query for each INSERT where it puts in the new string (if it doesn't already exist in the table).
Basically, it's an "ugly" (not very elegant) solution to the user (me).
And it makes me wonder if this method saves so much disk space anyway. I have only done this for my biggest tables -- not for ones that I know will remain "reasonable". It's not worth it to do this for every single text column, and it would feel like I'm really fighting PostgreSQL and probably am doing something wrong.
What do you all say about this? Have I fundamentally misunderstood something? Does PG just "expand" my table structure back into the original structure internally, in order to boost performance at the expense of "live" disk space taken up by my database?
I feel as if I have a bad understanding of just how much (or little) PG actually "abstracts away", and how "clever" I have to be in my structure these days. I may be mentally stuck in the 1970s or something by thinking like this. Maybe I've gained nothing from all this work with extra tables, FKs, and extra/complicated queries?

Having separate tables for the ua and ref strings will result in space savings if those values are long enough and repeated often. The added complexity during query and insert is nothing I would worry about.
A bigint uses 8 bytes of storage, and a row in a table has an overhead of at least 24 bytes. The latter is irrelevant if the values are repeated often enough.
A string like MOSAIC 1.0 will occupy 11 bytes on disk after TOASTing, so the savings over the 8 bytes of a bigint will be minimal. I wouldn't consider using a separate table in that case just for saving space.


How to aggregate pairs of id values into groups I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example below to illustrate the problem.

id1 id2
--- ---
1    2
1    3
3    4
3    5
6    7

I want to group together ids so that all linked ids are grouped together even if there is not a direct link between 2 id values.

id group_id
-- ----
1   1
2   1
3   1
4   1
5   1
6   2
7   2

I would like to achieve this in sql without using cursors if possible.

All advice gratefully received.

Update: I thought it would be useful to add a bit more background on the data this represents. The data represents records for businesses sourced from different data sources

The main table looks like this

id business_name postal_address business_classification x y group_id

The main table can contain duplicates / similar records which need to be associated. The table with id1 and id2 columns is a result of de-duplication using fuzzy string matching (levenshtein, word matching etc) and spatial proximity matching. I think that this table is actually an undirected graph containing many disconnected sub graphs e.g. group_id 1 and 2.I want to join all ids in the same network and update the group_id in the main table.

I think that the question is now how to identify all disconnected sub graphs on a graph using SQL server Find All Disconnected Sub Graphs - Java

Well this worked for the given sample data but not the cycles scenario / may not work for a more complex data set.  Thought I would post anyway as a starter for 10:

DECLARE @t TABLE ( id1 INT, id2 INT )

INSERT INTO @t VALUES
    ( 1, 2 ),
    ( 1, 3 ),
    ( 3, 4 ),
    ( 3, 5 ),
    ( 6, 7 )

    --( 1, 2 ),
    --( 2, 3 ),
    --( 3, 1 )

;WITH cte AS
(
SELECT ROW_NUMBER() OVER( ORDER BY id1 ) group_id, id1 id
FROM @t a
    CROSS APPLY ( SELECT TOP 1 id2 FROM @t c WHERE a.id1 = c.id1 ) d
WHERE NOT EXISTS ( SELECT * FROM @t b WHERE a.id1 = b.id2 )
  AND a.id2 = d.id2
UNION ALL
SELECT group_id, t.id2
FROM cte c
    INNER JOIN @t t ON c.id = t.id1
)
SELECT *
FROM cte
ORDER BY 1, 2


MySQL Community Edition Capacity I am using MySQL Community Edition on Amazon RDS service. It is a very large database, 2/3 tables has about 10 millions records and 4-5 tables has 7-8 millions records.
Questions

How much capacity does MySQL Community Edition have?
Should I have to purchase MySQL Enterprise Edition for my application?
Where can I get pricing information for the same?

Thanks

The server core is similar (identical?) on the GA and Standard Edition (SE)/Entreprise Edition (EE). What you're getting with SE/EE is stuff like (from here)

MySQL Partitioning
MySQL Enterprise Backup
MySQL Enterprise Monitor
MySQL Enterprise HA
MySQL Enterprise Scalability
MySQL Enterprise Security
MySQL Enterprise Audit

You also get support and some access to goodies on Oracle's MOS (My Oracle Support, used to be Metalink). You can tell your PHB - "look, if anything goes wrong, at least _you_ won't be blamed!".

The funny thing is that many of these "enterprise" solutions are available from open source contributors in various guises, and I for one am convinced that it's possible to have at least an entreprise standard setup using only Open Source components. With the advent of WebScaleSQL, that situation could move up to  carrier grade.

As for pricing - see here, it's basically $2.000 (Standard), $5.000 (Enterprise) or $10.000 (Carrier grade) per year. See the features table for a comparison.


Simulate more RAM with SQL Server Background: I'm in the process of optimizing a Data Warehouse style query with big dimension tables. The optimizer does not like to pick the typical star schema join plan at the moment. When I force this plan (by using an index hint) I can see that this star join plan is just a little more expensive than the plan I'm using right now. I suspect that is because the dimensions do not fit into RAM on my development machine. The version is SQL Server 2014 but I hope that the question can be generalized to all recent versions.

Question: Is there a way to make the optimizer believe that it has a lot of RAM at its disposal? For testing purposes I would like to obtain the plan that it would generate if it had 1TB of RAM available (instead of 8GB at the moment).

The DBCC WHATIF command (also see http://devondba.blogspot.co.uk/2014/03/dbcc-optimizerwhatif.html) can be used to simulate a certain hardware configuration for the current session:

dbcc OPTIMIZER_WHATIF ({property/cost_number | property_name} [, {integer_value | string_value} ])

The MemoryMBs property must be set to some value.

After that it appears to be necessary to recompile the query in question (for example using DBCC FREEPROCCACHE on a dev box, or using OPTION (RECOMPILE) if that hint is acceptable for later production use).

It is easy to tell that the WHATIF value "took" by the fact that it influences the <OptimizerHardwareDependentProperties> values in the XML execution plan.


Get full tree hierarchical query I want to get all data of tree by ID.

My table is:

ID     NAME     PARENT_ID
------------------------
1      A        NULL
2      B        1
3      C        1
4      D        3
5      E        4
6      F        NULL
7      G        6
8      H        NULL

I want the output result based on the following:

Input(ID)       Output(IDs)
-----------------------
1               2,3,4,5
2               1,3,4,5
3               1,2,4,5
4               1,2,3,5
5               1,2,3,4
6               7
7               6
8               NULL

How can i do that?

Procedure code

DELIMITER @;

DROP PROCEDURE IF EXISTS GetBranch@;
CREATE PROCEDURE GetBranch()
BEGIN
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (id INT, parent_id INT, UNIQUE (id, parent_id)) ENGINE=Memory;
INSERT INTO temp (id, parent_id)
    SELECT id, COALESCE(parent_id, id) FROM test;
INSERT IGNORE INTO temp (id, parent_id)
    SELECT parent_id, id FROM temp;
REPEAT
    SELECT COUNT(*) INTO @count1
        FROM temp;
    INSERT IGNORE INTO temp
        SELECT t1.id, t2.parent_id
        FROM temp t1, temp t2
        WHERE t2.id = t1.parent_id;
    SELECT COUNT(*) INTO @count2
        FROM temp;
UNTIL @count1 = @count2 END REPEAT;
UPDATE temp
    SET parent_id = NULL
    WHERE id = parent_id;
DELETE t1.*
    FROM temp t1, temp t2
    WHERE t1.id = t2.id
      AND t1.parent_id IS NULL
      AND t2.parent_id IS NOT NULL;
SELECT id, GROUP_CONCAT(parent_id ORDER BY parent_id) branch
    FROM temp
    GROUP BY id
    ORDER BY id;
DROP TABLE temp;
END;
@;

DELIMITER ;

Test code

DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, name CHAR(1), parent_id INT);
INSERT INTO test VALUES
    (1,'A',NULL),
    (2,'B',1),
    (3,'C',1),
    (4,'D',3),
    (5,'E',4),
    (6,'F',NULL),
    (7,'G',6),
    (8,'H',NULL);

CALL GetBranch;

DROP TABLE test;

Test output

mysql> CALL GetBranch;
+------+---------+
| id   | branch  |
+------+---------+
|    1 | 2,3,4,5 |
|    2 | 1,3,4,5 |
|    3 | 1,2,4,5 |
|    4 | 1,2,3,5 |
|    5 | 1,2,3,4 |
|    6 | 7       |
|    7 | 6       |
|    8 | NULL    |
+------+---------+
8 rows in set (0.31 sec)


How to measure Key Lookups per Second on SQL Server I am working on reducing the amount of transactions doing key lookups on our SQL Servers.

First I want to define a baseline for the normal or tolerable number on that metric and as we constantly have releases in production, then, I want to create an automation based on it that notifies me when it goes south.

The problem that I am having is actually finding the right counter either on perfmon or a SQL DMV to get the data from.

I looked on the obvious object SQL Server, Access Methods Object yet, I could not find a specific one. I also tried other counters or SQL views but could not find it.

Any suggestions would be very appreciated!

I don't know of a fully accurate and reliable way to track this.

One way to get at least something potentially useful is to keep snapshots of sys.dm_db_index_usage_stats, specifically the user_lookups column for index_id 0 (RID Lookup) and 1 (Key Lookup). This DMV can be reset for many reasons, including database or instance restarts. Rebuilding (but not reorganizing) and index also clears the related DMV entry on SQL Server 2012 and later, which can make things more difficult. You will need to capture information fairly regularly and use a heuristic to decide if the DMV reset between captures.

The index usage stats DMV also only returns a count of the number of times a plan containing a Lookup is executed. A plan that contains a single Key Lookup will increment the counter by 1, regardless of the number of lookups actually performed. It will also increment even if the Lookup is not executed at all.

The sys.dm_db_index_operational_stats DMV records the number of singleton lookups actually performed, but does not distinguish between singleton seeks on the index directly, and those that result from a Key or RID Lookup, so it is not useful for your purpose.

Singleton seeks are another name for Probe Scans as reported by the Access Methods Object. There is no way to distinguish between a 'normal' singleton seek on a unique index, and a singleton seek resulting from a Lookup. This means the Access Methods Probe Scans counter is not useful to you. The AM counters are very noisy anyway, and there's no way to correlate the counters with a particular index.


MYSQLDUMP --skip-lock-tables --skip-lock-tables

when i am taking mysqldump with this option or without this,I am not able to identify any difference. Tables are not getting locked in any of these cases. I thought mysql will lock tables without this option and after this option i am able to query table, however in both of this cases i am able to query to all the tables. Please suggest.

When performing a backup of a database, MySQL locks the tables by default in order to ensure that the data does not change while the backup is under way. Instructing MySQL to skip this process opens the data to the possibility that some user will insert or delete or do some other operation that would result in an incomplete backup. Performing a normal query should not be a problem since data is not being changed.

You might want to try an insert or delete operation while a backup is under way to test more fully. The type of database ISAM/Innodb is also an issue since the behaviour may be different.

See for example MySQL docs and a similar question here.


Avoiding "Waiting for table metadata lock" when `ALTER TABLE DROP PARTITION`? I have some tables that many users need to access to:

mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
       Table: v3_cam_date
Create Table: CREATE TABLE `v3_cam_date` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `totalview` int(11) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` int(11) unsigned NOT NULL DEFAULT '0',
  `uv` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `uc` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `ctr` decimal(5,3) NOT NULL DEFAULT '0.000' COMMENT '=-1: meaning not available(N/A)',
  `moneyc` int(11) unsigned NOT NULL DEFAULT '0',
  `moneyv` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_campaignid_dt` (`campaignid`,`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734502) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (734683) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (734863) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (734959) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (735141) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (735210) ENGINE = InnoDB,
 PARTITION MERGER_2013227 VALUES LESS THAN (735291) ENGINE = InnoDB,
 PARTITION pcurrent_2013227 VALUES LESS THAN (735292) ENGINE = InnoDB) */

When an user want to drop a partition ALTER TABLE v3_cam_date DROP PARTITION pcurrent_2013227, it may cause many transactions to be in the Waiting for table metadata lock state:

     Id: 31560182
   User: alice
   Host: 192.168.3.40:36132
     db: db
Command: Query
   Time: 806
  State: Waiting for table metadata lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _campaigncomplete
        FROM `ox_campaigns` A
        INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
        INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
        WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(    (SELECT `sys_date_cpc` FROM `000_sys_params_v4`) , INTERVAL 1 DAY)
                AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',17) ) ,A.`uid` =  NAME_CONST('userid',5770)))

What is the efficient way to accomplish this without locking?

What you are asking for is impossible. Regardless of storage engine, DDL of any kind will lock a table. If you must remove a partition from a table that is active, you should:

Setup MySQL Replication (if you haven't done so already)
Perform all SELECTs involving v3_cam_date against the Slave
STOP SLAVE; on the Slave
Perform ALTER TABLE ... DROP PARTITION on the Master.
Perform all SELECTs involving v3_cam_date against the Master
START SLAVE; on the Slave (Replicates ALTER TABLE ... DROP PARTITION to the Slave)

This is probably your only recourse. The only other recourse is to simply wait out the ALTER TABLE ... DROP PARTITION.
This situation requires some intervention in the application. Within your application, you would have to create a Write DBVIP on the Master and use the Read DBVIP with one of the following three(3) options:
OPTION #1

Keep the Read DBVIP on the Slave

OPTION #2

Keep the Read DBVIP on the Master
When you must do an ALTER TABLE, move the Read DBVIP to the Slave
When ALTER TABLE has been completed, move the Read DBVIP to the Master

OPTION #3

Setup Read DBVIP on a LoadBalancer
Remove Master from LoadBalanced DBVIP on demand

Option #1 seems to be the simplest way in the long run.


Why do I get Invalid identifier error even though column exist WITH distinct_appl_temp AS (
  SELECT DISTINCT
    c_number
   ,j_group
   ,pi_c_dt
   ,act_can
  FROM app_fact vd
  WHERE
      vd.c_number   = 12335
    AND vd.act_can    = 1
    AND vd.pi_c_dt    = (
        SELECT
          MIN( v2.pi_c_dt )
        FROM app_fact v2
        WHERE
            v2.c_number   = vd.c_number
          AND v2.j_group    = vd.j_group
          AND v2.act_can    = vd.act_can
          AND add_months( v2.pi_c_dt,+6 ) > vd.pi_c_dt
      )
  GROUP BY
    vd.c_number
   ,vd.j_group
   ,vd.pi_c_dt
   ,vd.act_can
  ORDER BY
    vd.j_group
   ,vd.act_can
) SELECT
  dt.c_number
 ,dt.j_group
 ,dt.pi_c_dt
 ,dt.act_can
 ,da.pi_c_dt
FROM distinct_appl_temp dt
 ,   app_fact da
WHERE
    dt.c_number   = da.c_number
  AND dt.j_group    = da.j_group
  AND dt.pi_c_dt    = da.pi_c_dt
  AND dt.act_can    = da.act_can

Code Review

line 3-6

Columns are not fully qualified

line 2 & 21-25

DISTINCT and GROUP BY doesn't make sense in this context.  drop one

line 26-28

I wouldn't trust an ORDER BY clause in a (refactored) sub-query. Move it to the main query or drop it.

line 35-41

It's 2019.  Please use ANSI JOINs

line 34

There are duplicate column names in a SELECT statement.  Give one of them an alias.

Most likely, your problem will be fixed by (5)


Indexing to support advanced search queries I have a Person table 2.5 million rows, typical demographic fields.  Need to support an advanced search screen that allows up to 10 search params (f/l name, phone 1 through phone 6, email, nickname, etc.) all varchar fields.  The 3 name fields are a real puzzle -  all need to support partial string matches, front and back LIKE (% @LastName %) or something like that.  They can supply just one param, or all ten, or any combination.
So, forgetting about sniffing for a minute, I just want to design the best possible index(es) to support these searched.  Tried a number of things, what is working best is an index with all 10 fields (see below), leading with the one most likely to be filled in.  It's "just ok" performance wise, and I'm wondering if I'm missing something.
CREATE NONCLUSTERED INDEX [IN_Person_Search] ON [dbo].[Person] (FnameLname, LnameFname, NickName, Phone1, Phone2, Phone3, Phone4, Phone5, Phone6, Email)
I realize the answer might be wrapped up in the SELECT statement, so a quick word on that - I'm using the "Email = @Email OR @Email IS NULL" method to deal with empty params.

You're trying to write one magical query that will handle all possible combinations of search criteria efficiently. I call this the kitchen sink but it's simply not achievable to do this for 10 parameters with one query and one index. It's just not possible.
The bad news: You're not going to be able to create an index (or even 100 indexes - see here to get an idea of how many combinations, not permutations, you would have to cover) that can satisfy all possible combinations of parameters. And if you write a query with a bunch of OR conditionals, the plan that gets compiled will be based on the first combination of parameters that are passed in, which may not be good for any subsequent query, never mind all subsequent queries.
The good news: You can handle this scenario in a way that lets you prioritize.
Here's how I do it - I create a dynamic SQL version of the query for each possible combination as I see them. I append only the clauses necessary to search based on the set of parameters provided. This way I don't use queries optimized for @p1 when @p1 isn't even passed in. I also set up some qualitative criteria to determine whether I need to recompile for parameter skew. When the parameter represents a unique index key, there is no reason to recompile, but if different values could produce vastly different plans (based on my knowledge of the data, selectivity, usage patterns, etc.), then it might be useful to do so.
CREATE PROCEDURE dbo.SinkMe
  @p1 int,
  @p2 varchar(32),
  @p3 datetime
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT <cols> FROM dbo.table WHERE 1 = 1';

  SELECT @sql += CASE WHEN @p1 IS NOT NULL THEN N' AND c1 = @p1'    ELSE N'' END
              +  CASE WHEN @p2 IS NOT NULL THEN N' AND c2 LIKE @p2' ELSE N'' END
              +  CASE WHEN @p3 IS NOT NULL THEN N' AND c3 >= @p3'   ELSE N'' END;

  IF @p1 IS NULL
  BEGIN
    SET @sql += N' OPTION (RECOMPILE)';
  END

  SET @sql += N';';

  EXEC sys.sp_executesql @sql,
                         N'@p1 int, @p2 varchar(32), @p3 datetime',
                         @p1, @p2, @p3;
END

You can periodically review the plan cache to see which combinations of parameters get used most often and/or lead to the worst-performing queries, and create additional supporting indexes based on those queries (rather than trying to create all potentially helpful indexes up front and maintaining them all even though some may rarely/never get used).


How to load a table using BCP Context:

I created a dump of a sql table in XML (Including data and schema) using this command from the command prompt.

BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" queryout  C:\data.xml -w -r "" -S localhost -T

Now that I am equiped with the XML File containing all the information that I need, I would like to load this into another database.

Since it is that easy to create this table dump into a xml file, I suppose there must be a simple way to load the XML File into a database (Creates the Table and then loads the data). Does anyone know how to do this?

Disclaimer: I am not a DBA and this is my first experience with BCP, the documentation was talking about creating a Format file but it didn't seem to work for me.

Thanks in advance for your responses

I wrote a blog post showing some sample BCP commands, including how to create the format file, at https://www.sqlserverscience.com/tools/example-bcp-export-import-commands/

This command should create the format file that is essential for import via bcp:

bcp "dbo.Oranges" format nul -S "FRUIT\PEARS" -T -d "Fruit" -n -f "C:\some\path\Oranges.fmt" -x

dbo.Oranges is the table name.  FRUIT\PEARS is the SQL Server instance name.  Fruit is the database name.

If you're having trouble with the XML file, I would recommend using the native binary format generated by BCP  It ensures the export and import will work without any data loss, and is fully supported across various versions of SQL Server.


SSRS on 2008 R2 and its Database on 2016 Can we have SQL Server Reporting Service on SQL Server 2008 R2 and its Database(ReportServer and ReportServerTempDB) on SQL Server 2016?

PS: Both are enterprise edition.

SSRS just needs a SQL Connection to a database that supports all of the features it requires. The connection type that it uses should be forward compatible to SQL 2016 (and even 2019).

I want to ask, why not upgrade SSRS as well though?


Listing all databases in PostgreSQL I have a basic question about PostgreSQL:
When connecting to my database server via pgadmin3, I can see that there are multiple databases.
For example, let's say I can see "test", "test1", and "postgres".

From there, I can choose which database I want to connect to.

However, from the command line, I'm doing the following to connect:

psql -U adminusername

This brings me to a postgres=# prompt.

Once connected, I tried to do a \l command to see all databases, but the list I get back is different from what I see using pgadmin3.

Just wondering what I might be doing wrong.

Most probably you are connecting to different database clusters.
Compare host and port in your settings in pgAdmin and for psql default connection.

Read the manual here about connecting with psql.