How to fix `The table '/tmp/mysql/#sql_xxxxx' is full`? Ubuntu server 16.04, php7, nginx 1.10, mysql 5.7 in Hyper-V on Windows 10 host

How to fix  The table '/tmp/mysql/#sql_xxxxx' is full ?

In mysql-error.log

  [ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please
  add another file or use  'autoextend' for the last file in setting
  innodb_temp_data_file_path

  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full
  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

  [ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please
  add another file or use  'autoextend' for the last file in setting
  innodb_temp_data_file_path

  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full
  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

But in /tmp/mysql/ - nothing.

In mysqld.cnf

Секция InnoDB

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 5M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1G
innodb_log_buffer_size         = 8M
innodb_max_undo_log_size       = 50M
innodb_temp_data_file_path     = ibtmp1:12M:autoextend:max:512M

If you remove the limit to 512 MB, the file fills all the available space in a few minutes.

innodb_temp_data_file_path     = ibdata1:10M:autoextend:max:512M
innodb_thread_concurrency      = 8
innodb_buffer_pool_instances   = 8
innodb_buffer_pool_dump_pct    = 75
innodb_flush_log_at_trx_commit = 0
sync_binlog                    = 0
innodb_io_capacity             = 600
innodb_io_capacity_max         = 1000
innodb_read_io_threads         = 32
innodb_write_io_threads        = 16
innodb_fast_shutdown           = 0
innodb_max_dirty_pages_pct     = 0

# df -h

udev             5,0G            0  5,0G            0% /dev
tmpfs            1,0G         110M  915M           11% /run
/dev/sda2         35G          22G   12G           65% /
tmpfs            5,0G            0  5,0G            0% /dev/shm
tmpfs            5,0M            0  5,0M            0% /run/lock
tmpfs            5,0G            0  5,0G            0% /sys/fs/cgroup
/dev/sda1        511M         3,4M  508M            1% /boot/efi
tmpfs            1,0G            0  1,0G            0% /run/user/1000

The relevant configuration is this:

  ibtmp1:12M:autoextend:max:512M

You are restricting your temporary tables (and similarly your main innodb tablespace via innodb_data_file_path) to 512M total.  This includes implicit (disk) temporary tables that might be created by various ORDER BY / GROUP BY / DISTINCT operations.

MySQL reports this failure relative to your configured tmpdir, although it's actually using the ibtmp1 file.

Increase or remove the :max:<size> limit to resolve this.

Alternatively you might find a way to avoid these sort of temporary tables by inspecting the offending queries - possibly via adding an index, changing data types or changing the query itself among other strategies.


MySQL for loop to show grouped results I'm trying to show results for 12 months using this query:

SELECT
    COUNT(DISTINCT user_id) as customers,
    SUM(amount)*.01 as amount,
    SUM(amount_refunded)*.01 as refunded,
    MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH
AND `user_id` IN (SELECT `id` FROM `users` WHERE `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH);

Could easily do this programmatically using PHP to output into an HTML table where each row includes shows a different month by incrementing the month and querying the DB again each time.

How would I do that with this query using a MySQL FOR loop instead? Would that even be the right approach?

As @Rick James mentioned, a GROUP BY function should do the trick here, and you can use  This SO Answer to limit it to the last 12 months.  For your example, the query would look something like this:

SELECT
    COUNT(DISTINCT user_id) as customers,
    SUM(amount)*.01 as amount,
    SUM(amount_refunded)*.01 as refunded,
    MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- AND `user_id` IN (
--  SELECT `id` FROM `users` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- )
GROUP BY MONTH(`created_at`);

NOTE: I commented the subquery for user_id because it would mean the results were only for those customers who both created an account and made a purchase in the last year, and I wasn't sure if that was your desired result.  If so, just uncomment it.  I edited the subquery so that it should work in that manner


What configs influence whether user credentials/grants are written to the binlog in Mysql I have a fairly trivial master-master setup.
When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific database only:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      694 | my_db        |                  |
+------------------+----------+--------------+------------------+

Why are DDL queries like (CREATE USER) and inserts into mysql.user logged as well? I can't see any mechanisms/configs in the manual which explain this, seemingly, exception.

If it matters, the binlog format is:

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

* = Let's forget about the bad-practice of using binlog-do-db for now

You are replicating only my_db.

There are valid security reasons for having the option to not replicate mysql.

The quick solution is to add binlog_do_db=mysql and restart.  However, any GRANTs and CREATE USERs have already been lost.  Either re-issue them and hope they replicate, or replay them on the Slave(s).


T-SQL for Query that can be done in SQL. What do they want? We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId...)
I am going over a book exercise that asks for a T-SQL statement to find
all vendors with at least one invoice. But I am confused, since this can be perfectly done with standard SQL:

SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
GROUP BY VendorName having Count(InvoiceId) > = 1

So, what could they be asking? Should I come up with a procedure to select all vendors with more than n invoices; n= 0,1,2 ?

If so, why is this not working for the general case of n invoices:

 CREATE Procedure InvoiceCount @NumberofInvoices Int
AS
BEGIN
SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
WHERE Count(InvoiceId) > = @Number of Invoices
EXEC InvoiceCount @Number of Invoices = n

Why doesn't this work?
  Thank you.

You don't need the WHERE clause because you are doing an inner join, which ensures you will only get vendors that have an invoice:

SELECT DISTINCT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID

OR

SELECT DISTINCT VendorName
FROM Vendors V
WHERE EXISTS (SELECT 1 FROM Invoices I WHERE V.VendorId=I.VendorID)


How to configure multiple hot standbys in Postgres? I currently have a postgres server setup with a hot standby.  Is it possible to setup a second standby server?  The purpose of this copy would be to provide real-time reporting off the database, not be a true standby server.

I cannot find any information on configuring a second standby server, so I would like to know:

Is it possible?
If so, how to configure it in recovery.conf
If not, other suggestions for replicating for reporting purposes.

Yes, it is possible.
I assume (based on you term "hot standby") that you are using streaming replication right now with your actual standby server.

For this you setup the master server with a pg_hba.conf allowing the slave to connect to the special database "replication". For the additional slave you need an additional entry (don't forget the reload)

# The standby server must connect with a user that has replication privileges.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  replication     replication     192.168.0.20/32         md5

The user "replication" is created by using psql:

$ CREATE ROLE replication WITH REPLICATION PASSWORD 'password' LOGIN

The configuration on the master should be there (wal_level = hot_standby and probably an activated archiving).

On the reporting-slave you just need a full backup and a valid recovery.conf. If you use pg_basebackup it can create a recovery.conf for you (see the blog entry https://www.depesz.com/2012/02/03/waiting-for-9-2-pg_basebackup-from-slave/).
Don't forget hot_standby = on to use the slave for read-only-queries.

You also can use the "old" slave as master for the reporting-slave (cascading replication).


Issues with self signed certificates, SSL and MongoDB Note: As specified in my comment I've figured out a solution to this issue regarding a single client/mongo connection.  The next step is to figure out a solution for a replica set involving this fix.

So I've been looking into adding SSL into my MongoDB replica set recently and ended up rebuilding Mongo 2.7.8-pre using SCONS.  It built fine and I was able to test the newer version of mongo.exe, mongod.exe and mongos.exe with the "allowSSL" mode without generating any certificates.

I was looking around at different tutorials on generating server and client side SSL certs with a root CA and came across this site:
http://acs.lbl.gov/~boverhof/openssl_certs.html

If you don't want to navigate to the page it basically:

Creates a CA
Creates a server key/cert pair
Creates a client key/cert pair

After generating everything I concatenated the keys and certificates into a server.pem and client.pem file because from the docs it seems like Mongo needs both in a .pem file in order for it to work properly.

This left me with:
 - ca.pem file with the root certificate
 - server.pem file with the server cert/key (subject - ...O = company1, OU = dept1...)
 - client.pem file with the client cert/key (subject - ...O = company1, OU = dept2...)

I was able to restart my services fine using ca.pem and server.pem.  However, when I went to connect to the nodes using the client with the following command:

mongo --ssl --sslPEMKeyFile "C:\MongoDB\ssl\client.pem" --sslCAFile "C:\MongoDB\ssl\ca.pem"

It gives me this error:

E NETWORK  SSL peer certificate validation failed:self signed certificate

I've tried to add the client cert to the root CA that I generated because it was suggested that this is my issue but it does not resolve the problem.  I tried adding the subject from the client certificate as a new user to the database as suggested for x509 authentication but this also does not resolve the issue.

I would appreciate any help or suggestions as to why my certificates are failing trust because I think at the moment I'm just being road-blocked by a lack of understanding.

Thanks!

The answer to my question comes from an article I found this afternoon and I completely understand what I was doing wrong before.
http://demarcsek92.blogspot.com/2014/05/mongodb-ssl-setup.html

I'll explain a little more because of the suggestion from Markus.

Originally I was generating client and server key/certification pairs from a root CA that I had created.  I was concatenating (adding) the other certificates that I was making to the root CA and using this as the input for --sslCAFile.  The issue I was creating was using my server.pem key/cert for each node and then trying to pass the client.pem file to the server for validation which I found out throws the generic "Self signed certificate" error.  Apparently it happens whenever invalid certs/keys are passed to the server to create a connection.

(I'm going to gloss over how to make the server/client key/cert as it is in the article and I would like people to go there for more explanation as it is this gentleman's solution and not my own.)

Create server.key and server.crt
Use "type server.key server.crt > server.pem" (for Windows)

Create client.key and client.crt
Use "type client.key client.crt > client.pem"

For the server the setup will be:
--sslPEMKeyFile = server.pem
--sslCAFile = client.pem

For the client the setup will be:
--sslPEMKeyFile = client.pem
--sslCAFile = server.pem

This solution, as is, works for a single node and single client connection.  I was able to trace the line with Wireshark and see that Mongo had stopped identifying itself and that when I drilled down into the packets using the Follow TCP Stream option the only information it was exposing was part of the subject used in creating my certificates (ok behavior).

Find "Client Hello" transmission from Mongo by:
Right-clicking on one of tranmission messages > Decode as... > Transport tab > SSL

On the "Client Hello" transmission from Mongo:
Right-click the packet > Follow TCP Stream > You should see the packet encrypted

NEXT STEP:
My next step is to figure out how to setup SSL certificates for a 3 node replica set.  I'm still trying to wrap my head around creating certificates for each node and how they can all be linked so it will allow for each to trust each other and a client connection.

(I'm going to look into the Stack Exchange rules but I was just thinking about chaining to the next topic with a link in this one)


Optimizing a MySql query Here's the query:

set @_YEAR = 2014;
set @_MONTH = 1;
set @_PRODUCTS = '53';

set @_START = date(@_YEAR * 10000 + 1 * 100 + 1);
set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 year);

explain SELECT TG, TS, `MONTH`

FROM (

    SELECT SG.company, SUM(SG.Gain) as TG, SUM(SG.Spill) as TS, SG.`MONTH`

    FROM (

        (SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := if(@prevComp <> c.calling AND @prevCust = c.called AND @prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Gain,
                @S := 0 as Spill,
                @prevCust := c.called as prevCust,
                @prevComp := c.calling as prevComp,
                @prevDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as prevDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @prevComp := 0, @prevCust := 0, @prevDate := 0) prevVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` limit 5000000)

    UNION ALL

        (SELECT c.called as customer, c.calling as company, MONTH(c.`end`) as 'MONTH',
                @G := 0 as Gain,
                @S := if(@nextComp <> c.calling AND @nextCust = c.called AND @nextDate = DATE_FORMAT(c.`end`,'%Y-%m-%d'), 1, 0) as Spill,
                @nextCust := c.called as nextCust,
                @nextComp := c.calling as nextComp,
                @nextDate := DATE_FORMAT(c.`end`,'%Y-%m-%d') as nextDate
        FROM cdrdata_archive c CROSS JOIN
             (SELECT @nextComp := 0, @nextCust := 0, @nextDate := 0) nextVals
        where c.`end` >= @_START and c.`end` < @_END
        AND c.called != 'Anonymous'
        AND CAST(c.`duration` AS UNSIGNED) > 30
        ORDER BY c.called, c.`end` desc limit 5000000)
    ) SG

     GROUP BY SG.company, SG.`MONTH`

) SGgrouped

JOIN products p ON p.Number = SGgrouped.company
Where p.id IN (@_PRODUCTS)
ORDER BY SGgrouped.`MONTH`

Here's the input:

CREATE TABLE `cdrdata_archive` (
  `CustomerID` varchar(10) DEFAULT NULL,
  `callid` varchar(20) NOT NULL,
  `called` varchar(14) DEFAULT NULL,
  `description` varchar(20) DEFAULT NULL,
  `calling` varchar(16) DEFAULT NULL,
  `dtn` varchar(14) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `answered` datetime DEFAULT NULL,
  `duration` varchar(5) DEFAULT NULL,
  `talktime` varchar(5) DEFAULT NULL,
  `tta` int(3) DEFAULT NULL,
  PRIMARY KEY (`callid`),
  UNIQUE KEY `callid_UNIQUE` (`callid`),
  KEY `called` (`called`,`start`,`end`),
  KEY `abandoned_from` (`calling`,`talktime`,`end`),
  KEY `called end` (`calling`,`end`),
  KEY `start` (`start`),
  KEY `calling` (`calling`),
  KEY `endcalling` (`end`,`calling`),
  KEY `productdur` (`calling`,`end`,`duration`),
  KEY `endcallingdur` (`end`,`calling`,`duration`),
  KEY `company` (`CustomerID`,`end`),
  KEY `calling end` (`end`,`called`,`calling`),
  KEY `end` (`end`) USING BTREE,
  KEY `call_id_desc` (`callid`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Here's the EXPLAIN statement:

Here's the output from SHOW VARIABLES LIKE '%buffer%';

Q: How can I optimize this query?

Based on the EXPLAIN, I would recommend focusing on second SELECT in the UNION.  It is probably the slowest piece, and the hardest to optimize.  Can you explain what it is doing?

One thing to try is to use literals in place of @_START and @_END.  @variables, even if constant, do not optimize well.

You have a lot of non-sargable expressions in the WHERE clauses.  See http://en.wikipedia.org/wiki/Sargable
Rewrite them.  One of note is that

@prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d')

is the same as

@prevDate = c.`end`

because of the way DATETIME works.  Fix all the non-sargable expressions; that will give the optimizer more options.

You would be better turning this into INT:

`duration` varchar(5) DEFAULT NULL,

As a VARCHAR it is useless in an index.  If you need non-integer values, either use NULL or have an extra column.

Please explain what the query is supposed to do -- in English.

Is limit  5000000 real?  Or an optimization trick?

What version of MySQL are you using?

(called, callid, and calling look so much alike that the query is hard to read.)

If you must name the INDEXes, at least be consistent:

KEY `called end` (`calling`,`end`),

Actually (called, end) might be better, at least for Derived #3.  I can't tell, but this might be the quickest partial fix.  It might avoid a 'filesort' necessary for

ORDER BY  c.called, c.`end`

However, due to the mixing of ASC and DESC, this won't be helped:

ORDER BY  c.called, c.`end` desc

Will it run any faster without @S := and @G :=?  These seem to do nothing inside the query, but perhaps lead to some kind of 'output' from the whole query.  And perhaps their presence forces the optimizer to do something sub-optimal.

Carrying data from one row to the next (re: @nextComp) is likely to be an optimization-killer.  One trick to avoid such things, is to have a sequence number (perhaps an AUTO_INCREMENT in a temp table) that lets you do a "self JOIN" to match up a row and the preceding row.  This is complex, but potentially beneficial.

You have a lot of redundant indexes; DROP them.  (This won't help the question in hand.)

After you have fixed what you can, post the new SELECT and the new CREATE TABLE.  Then we can continue this discussion.


Why does coalescing make this query quicker? I have the following query

SELECT SQL_NO_CACHE
    `table1`.*
FROM
    `table1`
LEFT JOIN `table2` ON table2.id = table1.table2_id
WHERE
    (`table2`.`date_assigned` >= '2015-06-21')
    AND (
        `attempt_1_date`IS NOT NULL
        OR `attempt_2_date`IS NOT NULL
        OR `attempt_3_date`IS NOT NULL
        )
    AND (
        `attempt_1_date` >= '2015-08-16'
        OR `attempt_2_date` >= '2015-08-16'
        OR `attempt_3_date` >= '2015-08-16'
        )
    AND (
        `callback_date`IS NULL
        AND `callback_by_account_id`IS NULL
        AND `callback_result`IS NULL
        )
    AND (
        (
            `attempt_1_result` NOT IN ('complete,incorrect,decline,prospecting')
            OR `attempt_1_result` IS NULL
            )
        AND (
            `attempt_2_result` NOT IN ('complete,incorrect,decline,prospecting')
            OR `attempt_2_result` IS NULL
            )
        AND (
            `attempt_3_result` NOT IN ('complete,incorrect,decline,prospecting')
            OR `attempt_3_result` IS NULL
            )
        )

    #AND (`table2`.`date_completed` IS NULL)
    AND (COALESCE(`table2`.`date_completed`, '') = '')

If I coalesce the date_completed field first, my Database program says the query comes back in 0.000 seconds, yet if I used the above (commented out) line, just checking IS NULL, it takes just over 10 seconds. Both only return the same 5 results.

table1 has 24 columns, and ~171,000 rows, and table2 has 172 columns*, and 1.7 million rows. In 1.4 million of those rows, date_completed is null.

If you need any more information, just let me know.

Few more details I was advised to include:

MySQL version: 5.5.41-0ubuntu0.14.04.1-log

Explain outputs:

Using COALESCE

`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`
1, 'SIMPLE', 'table1', 'index_merge', 'table2_id,attempt_1_date,attempt_1_result,attempt_2_date,attempt_2_result,attempt_3_date,attempt_3_result', 'attempt_1_date,attempt_2_date,attempt_3_date', '9,9,9', NULL, 8, 'Using sort_union(attempt_1_date,attempt_2_date,attempt_3_date); Using where'
1, 'SIMPLE', 'table2', 'eq_ref', 'PRIMARY,ind_table2_date_assigned', 'PRIMARY', '4', 'database.table1.table2_id', 1, 'Using where'

Using IS NULL

`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`
1, 'SIMPLE', 'table2', 'ref', 'PRIMARY,ind_table2_date_completed,ind_table2_date_assigned', 'ind_table2_date_completed', '9', 'const', 10, 'Using where'
1, 'SIMPLE', 'table1', 'ref', 'table2_id,attempt_1_date,attempt_1_result,attempt_2_date,attempt_2_result,attempt_3_date,attempt_3_result', 'table2_id', '5', 'database.table2.id', 1, 'Using where'

Few more notes.

date_completed is type DATETIME
There are no entries where date_completed = ''

*120 of those rows relate to 60 questions, in the format question_x_score, and question_x_value

The timings could be bogus because of caching.  You probably have the Query cache on.  Try again, but with SELECT SQL_NO_CACHE ...;

Recommend you add a new possibility to the ENUM behind this:

`attempt_1_result` NOT IN ('complete,incorrect,decline,prospecting')
        OR `attempt_1_result` IS NULL

That way you could avoid the NULL test and the OR.

AND `table2`.`date_assigned` >= '2015-06-21'
AND `table2`.`date_completed` IS NULL

would benefit from INDEX(date_assigned, date_completed) (in either order).

Why do you have LEFT JOIN?  It seems like JOIN would give you the same result?

Why do you even mention table2?  You are not fetching anything from it.


Linking SQL Server tables to a PostgreSQL Server I have a SQL Server 2005 instance and also a PostgreSQL 9.3 running on the same network, I would like to know if its possible to somehow create or have 'views' on a database within the PostgreSQL server that show data coming from SQL Server.

Use:

A foreign data wrapper like odbc_fdw or tds_fdw (see foreign data wrappers list, foreign data wrapper documentation); or
dbi-link - see github repo


Multiple SPIDs in sp_who2, Deadlocks and Parallelism There were reports of slowness on our web application - screens hanging, some unable to log in. It's an OLTP system with approx 50 concurrent users. Upon investigation I found a few things:

Number of connections to the backend were reaching the maximum 50.
There were a number of deadlocks, some processes deadlocking themselves. These locks did however release when I refreshed couple seconds after.
I ran sp_who2 and it returned 4 cases where an SPID was repeated 25 times (screenshot attached).

Am I correct in saying this is a result of parallelism? If so, is one obvious fix to amend our MAXDOP to a suitable setting and possibly change the threshold cost to higher than 5 second (server has 2 numa nodes, 24 threads total)?

If so, I'm thinking one drawback of this would be that overall performance on a day to day basis might be affected, as all the small queries will be using less resources and only the larger ones will get use of the full resources.

Please correct me if I'm wrong on that, I'm just looking to see if this change is advisable for an OTLP system and am looking to see possible negative impacts.

Answering this question:

  Am I correct in saying this is a result of parallelism?

Yes. Activity Monitor and sp_who2 display one row for each thread (execution context) involved. It is unnecessarily verbose and confusing, but Microsoft have not responded to requests to improve Activity Monitor. It is even less likely that the (technically undocumented sp_who2) will be updated.

Adam Machanic's sp_WhoIsActive is a much better alternative.

There is not enough information in the question to address your other points, but it does seem you might be running with an excessive degree of parallelism, possibly the default of zero (= 24 for your configuration).

Setting max degree of parallelism to 8 might be about right. See MAXDOP setting algorithm for SQL Server for detailed advice.

And, as Dan Guzman said:

  Parallel queries in a pure OLTP workload are often due to inattention to query and index tuning detail. In addition to reducing maxdop and cost threshold, view the execution plans for optimization opportunities.


Changing the representation of NULLs in pg_dump plaintext output I'm trying to convert a large-ish Postgres DB (500 GB) to SQL Server 2012.  After investigating a few third-party tools and being disappointed in features, performance, or both, I started pursuing a simple pg_dump/bulk import solution.  Things looked promising until I realized that pg_dump represents NULLs in plaintext as "\N", which causes the bulk insert to vomit in cases of type mismatch.  Even if I were to automate the pg_dump process to produce a single file per table, some of the individual tables involved are very large (20-50 GB) and performing a comprehensive find-replace using even fast file editing options in linux, or a Perl script, add too much overhead to the time required for the import/export.

I'm hoping there's a way to modify the NULL representation in the pg_dump output that I'm not aware of, or failing that, to get some recommendations for alternative approaches to this process in terms of tools or strategies.  Thanks in advance for your help.

I think the best for your situation is to use the COPY ... TO with one file for each table, and use pg_dump only for schema.

With COPY you can do change the output format (including NULL), example:

COPY foo TO '/path/to/foo.output' WITH NULL '<your null>';

You can even use CSV or other format (I don't know what MSSQL recognizes).

To automatize, you can easily check all the tables with a shell script. To achieve that, first edit as needed the following and save in getcommand.sql:

SELECT 'COPY '||c.oid::regclass||' TO ''/path/to/'||c.oid::regclass||''' WITH NULL '''';'
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind='r' AND n.nspname !~ '^(information_schema$|pg_)';

With that, you use the following to execute the copys (you can even use xargs to parallelize it):

psql <conn_info> -XAtf getcommand.sql | psql <conn_info>

It will generate and execute the COPY commands to export all tables data.

EDIT1: Added regclass to filename and corrected a syntax error on the generated COPY command (the WITH NULL goes at the end, not begining). Added -X to psql to avoid unexpected input through pipe.

EDIT2: Parallelism

I have made the test with xargs to parallelize it, and the result is the following:

psql -XAtf getcommand.sql | xargs -d '\n' -L 1 -P <num jobs> psql -c

On a simple test I did, with a small database I use for tests (~800MB), with no parallelism it took 6 seconds to complete, and with 4 jobs it took little more than 1 second. I imagine that for a 500GB database on a good server, the difference will be bigger. If you want to compare:

time ( psql -XAtf getcommand.sql | xargs -d '\n' -L 1 -P 4 psql -c )
time psql -XAtf getcommand.sql | psql

NOTICE: For those thinking in use it for parallel backups (e.g. to substitute pg_dump): don't do it! Because this solution uses different sessions (and transactions) for each table, so it can't provide consistency on the result if there are other users working on the database. On PG 9.2+ we can use transaction snapshot to guarantee consistency, but PG 9.3 (still beta) already has parallelism on pg_dump.


Determining replication traffic is there any way to determine from server statistics* running MySQL, how much traffic(bits per second) WOULD replication consume between 2 servers, without setting-up the actual replication between those 2 servers ?

*Statistics = mysql commands over time, Disk IO per second, traffic, ram cpu...(e.g. CollectD rrd data)

Thank you. :)

On Apri 18, 2012, I addressed a similar question : MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
I mentioned the following

Bytes_sent : IO Thread requesting binlog entries from the Master
Bytes_received : IO Thread reading binlogs entries from its Master
Bytes_received : SQL thread reading its own relay logs

You can find out what these are for all connections past and present by doing
SELECT * FROM information_schema.global_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

You can do so an individual session by doing
SELECT * FROM information_schema.session_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

I have good news and bad news. First ...
BAD NEWS
You cannot get a SQL thread to query information_schema.session_status. That's a violation of the first and fifth amendments (if applied against Replication's SQL thread).
GOOD NEWS (Sort of)
The only way you can track any kind of byte traffic is to do the following

Enable Binary Logging on the Slave

Dump all binlogs on the Slave using
mysqlbinlog --server-id=(server_id of the Master) ... > Binlogs_From_Master.txt

Take the filesize of Binlogs_From_Master.txt and that's your best guess.

Subtract that filesize from whatever Bytes_received is, and that's the internal byte traffic on the Slave.
CAVEAT
You will have to set up replication to find that number out.
As an alternative, you could just use one the the following from the Master

Bytes_sent
Bytes_received
Bytes_sent + Bytes_received

as an educated guess.
Give it a Try !!!


Count values of a column I've got two database tables:

Persons
id   user   product
1     1       3
2     1       5
3     1       7

products
id   kilo
3     8
5     15
7     3

I'm wondering how to count values from a database, in this case I want to get the total kilo's of products for user 1. The result should be '26'. Is this possible?

SELECT kilo FROM `products` WHERE `id` IN (SELECT `product` FROM `person` WHERE `user` = '1')

This will solve your problem, use SUM and join in 1 query.

SELECT SUM(`kilo`) total_kilo
FROM `person`
    INNER JOIN `products`
    on `person`.`product` = `products`.`id`;

Or selecting/counting for user 1 alone

SELECT SUM(`kilo`) total_kilo
FROM `person`
    INNER JOIN `products`
    on `person`.`product` = `products`.`id`
WHERE `person`.`user` =1;

Or selecting/counting for every user

SELECT `person`.`user`, SUM(`kilo`) total_kilo
FROM `person`
    INNER JOIN `products`
    on `person`.`product` = `products`.`id`
GROUP BY `person`.`user`;


How to best store a directory tree in a database? I want to represent my directory structure in some format (currently I'm just using JSON.)

This is how a sample JSON might look. For those curious it was generated using unix tree command: tree /path/to/folder -J --noreport -h.

{
    ...
    "type":"directory",
    "name":"dev",
    "size":4096,
    "contents":[
        {"type":"directory","name":"protocols","size":4096, "contents":[]},
        {"type":"file","name":"architecture.txt","size":4716},
        {"type":"file","name":"exceptions.py","size":31263},
        {"type":"file","name":"models.js","size":101882},
        {"type":"file","name":"proxy.cpp","size":29097},
        {"type":"file","name":"keylogfile.xyz","size":7889},
        {"type":"file","name":"Readme.txt","size":8857},
    ]
    ...
}

So this is just a representation of the entire folder structure of some path as JSON.

I can have many such separate JSON files, each representing a directory tree. There's no co-relation / links between these files.

On running the tree command on standard Windows "C:\" partition, I get a JSON file of ~30 MB in size. So I think we can assume that the max file size that a user would upload will be ~100 MB.

Once the files are stored, these are the operations I plan to make on a file:

Get the entire file.
Given a path, get it's immediate children (akin to doing ls on that path.)
Given a path, get the complete sub tree of the path.
Modify metadata of some item, say change its name or add a new note with it.

2 & 3 are the operations that I expect to happen the most.

Here are the ways of storing this data that I've come up with:

NO DB:

Store the file as-is on disk (/home/forest/<uuid>.json)
Operation no 1 becomes fast & easy - just send the entire file
but the others can get slow because they all involve parsing the entire JSON first and then iterating on it.

NO SQL

I've never used NO SQL databases before (only read a few posts about their use-cases etc.)
I think op 1 (entire file read) would be fast
but no idea if there will be any improvement on the other ops as compared to just using files.

RDBMS

I've used relational DBs before but don't think my data has anything to do with tables
I did google around though and found that postgres has an ltree type to store hierarchical data, but I'm not sure If that is what I need.

If it is, HOW will I get the data in?

Graph Databases?

Again, no prior experience with these, just shooting in the dark
At the end of the day, a directory is just a tree
Instead of creating a vanilla JSON, maybe I could generate a format that one of the Graph DBs can read-in
Once I have some graph DB object, maybe all the operations become fast enough.

My question is this: For my use case, what is the best way to store the data?

Replies to comments.

  Why do you think you need a database for this?

  Depending upon what you are trying to do you might not need to store the data in a db at all.

To be really honest, I don't know if I need databases, I know I want this data stored in a format that allows me to perform the above defined operations reasonably fast.

  What are you trying to achieve by putting this into a db? Reporting? Analysis? Will it be used by an application?

I'm doing this for a web application. Once the data is stored in a way I'm satisfied with, I plan to create a Web API (probably JSON based) that performs the operations I've listed above. The data will be sent to the client where it will get displayed on the frontend in some way.

  Do you want 1 row per file? What other meta data do you want to store? Size? date? File owner?

Yeah, I want the metadata that's usually associated with files.

A friend asked this, so I'm clearing it up here: I don't just have 1 JSON file (representing a tree). I could have n number of such trees (they are basically uploaded by the user and I expect them to be <100 MB in size.)

I'm a DB noob as well, I do recall postresql having json datatype for storing JSON structures. Maybe you can review the postgresql doc and decide if it works for you.


SQL Server : Is it possible to have a linked server link to another linked server? Hello to all and thanks ahead of time for taking the time to look at my question!

I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2.

Setup:

Windows Server SBS2012
SQL Server 2008R2 64bit
SQL Server 2012 Express 32bit
Sybase Advantage Database Server (ADS) 8.1 32bit (can't change)

What I've done so far is to install a 32bit SQL Server Express on the same machine. I have setup a linked server to the Advantage database on that instance and successfully accessed data. So, I know that much is working fine.

Problem is, when I try to setup a linked server on the 64bit 2008R2 server, it errors out with the following...

  OLE DB provider "SQLNCLI10" for linked server "[--MyLinkedServerName--]" returned message "Login timeout expired".
  OLE DB provider "SQLNCLI10" for linked server "[--MyLinkedServerName--]"
  returned message "A network-related or instance-specific error has
  occurred while establishing a connection to SQL Server. Server is not
  found or not accessible. Check if instance name is correct and if SQL
  Server is configured to allow remote connections. For more information see SQL Server Books Online.".
  Msg 65535, Level 16, State 1, Line 0
  SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Like I said, I can access the data using the 32bit SQL Server Express, so I know that much of it works. I just can't seem to access it from within the 64bit 2008R2. I know the login info is correct.

So, Is it not possible to do?

I found my answer at MS SQL Server Forum: SQL Server 2005 X64 Linked Server error

Turnes out I had "Level zero only" checked

So, it goes something like this: x64 Query > Linked Server to x86 > View > Linked Server to ADS
So, something like this now works...

SELECT * FROM OPENQUERY([HAL2011\SQLEXPRESS], 'SELECT PartNum, OnHand FROM [master].[dbo].[vHPQoH]')

Where [HAL2011\SQLEXPRESS] is the x86 Server with a view [vHPQoH] to a lined server pointing to the Sybase ADS.

But thanks to everyone for your help.  I still learned a lot!  This place is great!


SQL SP: How to return one inactive record along with active records I am working on this peculiar sql stored proc where the business case is as follows:

Business Case:
Table Specialties contains all the Specialties and there is a bit field for each record telling if it's Active or Inactive. We always display only the active records from that table in form of dropdown. Users may select a Specialty which can later on be deactivated. New requirement is to be able to pull that Inactive record along with all the active records in the result set.

Here's how I thought I should do this:
If no specialty is assigned to the person I am pulling up then the dropdown is going to be populated by all active records.
If there is a inactive specialty associated with the person I am pulling up then I send that specialtyID in stored proc as a parameter and return that inactive records along with active records to populate the dropdown.

Below is what I got so far:
So far if I dont pass in any specialtyId then I am returning active specialty records which is working. When I send in a specialtyId  parameter then it just returns that one inactive record but not rest of the other active records. I need the rest of the active records too along with that one inactive record.

   DECLARE  @specialtyId  INT = null;
  BEGIN
    IF isnull(@specialtyId,'')=''
      BEGIN
        SELECT SpecialtyID AS Id, Specialty AS Name
          FROM dbo.Specialties
         WHERE IsActive = 1
         ORDER BY Specialty;
      END
    ELSE
      BEGIN
        SET @specialtyId = @specialtyId ;

        SELECT s.SpecialtyID AS Id, s.Specialty AS Name
          FROM dbo.Specialties s
         WHERE specialtyId = @specialtyId
         GROUP BY s.Specialty, s.SpecialtyID
         HAVING (Specialty IS NOT NULL)
            AND (max(SpecialtyID) IS NOT NULL)
         ORDER BY Name;
      END
  END

It looks to me like a simple WHERE IsActive = 1 OR specialtyId = @specialtyId should work.


Log shipping - Job schedules When we configure the Log Shipping, there are jobs automatically created that run on schedule. What happens if db size grows or there is network latency, does SQL Server auto handle the job schedules?

Example: What happens if backup is not ready, will the copy job still run. Similarly, what if the copy is not compete - will the restore job still run? Will SQL Server dynamically handle this or will it skip one round?

All the Log Shipping jobs run on their fixed schedule, while the number of files they process each time may be variable.  The Copy jobs perform a "best effort" copy of all files that haven't been copied yet and aren't currently in use.  The Restore jobs perform a "best effort" restore of all the files available that haven't been restored yet.  However, that is contingent on adhering to any other rules you may have specified (ex. Setting a restore delay of 2 hours so you can possibly retrieve data off your Secondary if someone does an unintentional change/delete of data on the Primary).

Essentially, you can think of each job as taking a kind of Work Queue approach that first creates a list of work to perform before processing each item on that list.  If a transaction log backup is still writing to file DBTranLogBackup.bak at the time the Copy runs, that particular file will not make it onto the list of work that can be performed and will get processed during the next run of the job.

Hope that helps.


Back-end with encription and user-control for MS Access2003-VBA My client has an Access 2003 "multi-user" (<6) "database" (<3k records) with "encryption" located in a LAN folder and an easy Form I polished with VBA(DAO library). Access 2003 encryption can be broken in less than a second with very easy tools.
A decent level of encryption should be applied to the data (and maybe user control). My client needs an "easy" solution; something they can manage on their own (like they did with the access mdb file) without the help of engineers.
Some computers have access 2003, some Access 2007 (so 2007's marvellous and straight forward encryption isn't a solution). Access 2003 refuses to connect to an access 2007 database via ODBC and also stores the passwords for linked tables in plain text (and doesn't ask for them).

I have thought of the following:

Encripting individual fields. User generates a sha-512 hash that is substracted from every field to get the decrypted field. Problem: Manual editions of the Access table are common in this database.
MySQL in a computer-server, they connect to the database via ODBC. Or SQL Express (seems better with Access). Problem: Someone has to configure and manage the server periodically. It seems disproportionaly complex given the scarce amount of data and the not frequent use of them. sqlite (which also has an ODBC driver) doesn't have good encryption, it seems.
I believe a complex solution in their case would have greater security risks than a simpler one...
A mdb file inside a zip file, the decription consists of extracting the file when they need to use it. Would solve the problem but is not elegant or safe enough (if a hacker went into the remote folder when the database is opened he would gain access to all the data). The file is only opened once or twice every day.

What do you suggest? Do you have another ideas? I have thought many times and it seems I'll have to go for SQL Express 2008; I don't like this solution.
Thank you

I have talked to them. They consider moving to Access 2007, which provides data protection but no decent per-user control.

It's a bad solution. I will investigate JDBC-ODBC GPL bridges and modern GPL databases.


Unable to view Registered Servers on Central Management Server I have a MS-SQL instance configured to be a CMS for 50+ instances of MS-SQL Server 2008-2017. When checking the msdb.dbo.sysmanagement_shared_registered_servers_internal I see a list of the servers and their associated groups.
However when I connect to the instance remotely (from my Desktop via SSMS), the Registered Servers list only displays an offline SQLExpress instance in "local server groups" associated with my local PC - and nothing at all under "Central Management Servers".
When RDP'ing to the CMS directly, in SSMS the Local server group correctly shows the CMS instance under "Local Server Groups" but it's empty - and again, "Central Management Servers" is empty.
Any help on how to usethe existing CMS server list would be appreciated, as rebuilding it would take some time and I'm unsure on any side effects of essentially overwriting another CMS configuration on top of the existing one.
Note: I didn't build the existing CMS nor have I used it previously.

Did you ,from the machine where you are running SSMS, specify the machine where the server registration exists? I.e., you need to "point out" the CMS instance. Right click the CMS folder, and "Register Central Management Server", and in there you specify the SQL server instance where the CMS list exists in the table in msdb.


migrate from sql server 2012 to sql server 2008 R2 I have been looking for the information since yesterday and could not find anything...
I have sharepoint 2012 fundation and my SQL Server 2010 Evaluation is short of space (we already used 10GB), however I have SQL Server 2008 R2 license.

Question:

Is it possible to migrate my SQL Server 2012 sharepoint database to
SQL Server 2008? Will the new database be compatible with my sharepoint?
could you please send me a link with the
instructions?

thank you very much

One way to do this is to use a tool such as Redgate SQL Data Compare. I have had to do this in the past and it has worked well. First you run a schema compare to create an empty database in the 2008 server and then run data compare to pump the data in.

There are other vendors offering similar tools and I believe the new Visual Studio also has something like this. The Redgate offering does give you a two week trial which may be good if you only need it for a one of migration.

Click here for the trial.


Is time to deliver data included in time measured for a Slow Query I'm looking at some horrible numbers in a SLOW_QUERY_LOG (one example)

Count: 2563 Time=23.21s (59498s) Lock=0.00s (1s) Rows=21.4 (54857)

This is for a simple SELECT statement that runs for less than a second when I try to run it manually.

The database services a java/tomcat application that I know have some problems.

My question:

Is it possible for the calling application to cause these abnormal numbers in the database? For example by asking the database for some data and then taking it's sweet long time to accept it?

It is absolutely possible the application taking a long time can cause those numbers. From the slow query manual (emphasis mine):

  mysqld writes a statement to the slow query log after it has been executed and after all locks have been released

So if your application is taking a long time to release any lock that it acquired for the session, the query time written will reflect that.

Now in your specific scenario, is it possible that the slow execution times are occurring during some heavy load on your database server? That would cause abnormal query times irregardless of slow lock release.


1000's of SQL Update Statements taking forever to complete I have to update stock prices in Pastel Evolution and need to create the update statements in C#, then execute the in SQL Server.

Each update statement looks like this..

UPDATE t1
SET t1.fExclPrice = 521.95, t1.fInclPrice = 1
FROM dbo._evPriceListPrices AS t1
INNER JOIN dbo.StkItem AS t2 ON t1.iStockID = t2.StockLink
WHERE t2.Code = '108085R'
AND t1.iPriceListNameID = 1 AND t1.iWareHouseID = 5;

The problem I'm experiencing is that there are more than 14000 update statements to execute in the batch and it takes forever.

Is there a way I can speed this up?

Here is my Estimated Execution Plan.

I could not add my Trace result here as it's too large :(

Try modifying your query so it isn't overwriting values with the same value.
It will reduce logging.

UPDATE t1
SET t1.fExclPrice = 521.95, t1.fInclPrice = 1
FROM dbo._evPriceListPrices AS t1
INNER JOIN dbo.StkItem AS t2 ON t1.iStockID = t2.StockLink
WHERE t2.Code = '108085R'
AND t1.iPriceListNameID = 1 AND t1.iWareHouseID = 5
--Dont overwrite records with the same values it generates extra logging
AND
(
    t1.fExclPrice IS NULL
    OR t1.fExclPrice <> 521.95
    OR t1.fInclPrice IS NULL
    OR t1.fInclPrice <> 1
)


Can't access MySQL database with created user through dll I have a web service that connects to a MySQL database from a dll, both of them are on the same machine. If I use the root user and password it will connect no problem but if I use a user that I created it comes back with

Access denied for user '####'@'%' (using password: YES).

These are the commands I used to create the user

CREATE USER '####'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'%';

My understanding from these commands is that the user can connect to the database from any location. Since I kept getting that error message every time I connected I then thought that '%' didn't include 'localhost' so I then created the same user with these commands

CREATE USER '####'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'localhost';

This also didn't work, just the same error message only this time with 'localhost' in place of '%', then I thought that since the root user works I'll copy all the user hosts it has, so I used these commands

CREATE USER '####'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'127.0.0.1';
CREATE USER '####'@'::1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'::1';

I'm connecting to the same database for both users, I'm 100% sure the password is correct for both of them and I'm connecting by localhost. I can connect to the database with no problems through MySQL Workbench and the MySQL Client using the created user. There must be some permission I haven't given to the created user, but I don't know what, could anyone help me solve this?

I issued FLUSH PRIVILEGES; after creating all the users. skip-networking is off and I granted all privileges to the new user and I was able to access the database. I've created an exe program to connect to the same database, with the privileges removed and it worked. I don't understand why the exe works but not the dll. I'm going to try and grant privileges one by one, which might narrow down the problem.

I've eventually figured it out, after gaining a few grey hairs.

I had to give the new user the FILE privilege because my dll was using LOAD DATA INFILE which requires it to work. Here is the info on the FILE privilege.


Index Seek Operator Cost For the AdventureWorks sample database query below:

SELECT
    P.ProductID,
    CA.TransactionID
FROM Production.Product AS P
CROSS APPLY
(
    SELECT TOP (1)
        TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = P.ProductID
    ORDER BY
        TH.TransactionID DESC
) AS CA;

The execution plan shows an Estimated Operator Cost of 0.0850383 (93%) for the Index Seek:

The cost is independent of the cardinality estimation model in use.

It is not a simple addition of the Estimated CPU Cost and Estimated I/O Cost. Neither is it the cost for one execution of the Index Seek multiplied by the Estimated Number of Executions.

How is this cost number arrived at?

The full cost derivation logic is complex, but for the relatively simple case in the question:

Inputs

The number of times the operator is executed
This is the Estimated Number of Executions: 504
The cardinality (total number of rows) in the index
The TableCardinality property of the Index Seek operator gives this: 113,443
The number of data pages in the index: 201
This number can be obtained multiple ways, for example from sys.allocation_units:

SELECT
    AU.data_pages
FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
    ON P.hobt_id = AU.container_id
WHERE
    AU.[type_desc] = N'IN_ROW_DATA'
    AND P.[object_id] = OBJECT_ID(N'Production.TransactionHistory', N'U')
    AND P.index_id =
        INDEXPROPERTY(P.[object_id], N'IX_TransactionHistory_ProductID', 'IndexID');

The density (1 / distinct values) of the index: 0.002267574
This is available in the density vector of the index statistics:

DBCC SHOW_STATISTICS
(
    N'Production.TransactionHistory',
    N'IX_TransactionHistory_ProductID'
)
WITH DENSITY_VECTOR;

Computation

-- Input numbers
DECLARE
    @Executions float = 504,
    @Density float = 0.002267574,
    @IndexDataPages float = 201,
    @Cardinality float = 113443;

-- SQL Server cost model constants
DECLARE
    @SeqIO float = 0.000740740740741,
    @RandomIO float = 0.003125,
    @CPUbase float = 0.000157,
    @CPUrow float = 0.0000011;

-- Computation
DECLARE
    @IndexPages float = CEILING(@IndexDataPages * @Density),
    @Rows float = @Cardinality * @Density,
    @Rebinds float = @Executions - 1e0;

DECLARE
    @CPU float = @CPUbase + (@Rows * @CPUrow),
    @IO float = @RandomIO + (@SeqIO * (@IndexPages - 1e0)),
    -- sample with replacement
    @PSWR float = @IndexDataPages * (1e0 - POWER(1e0 - (1e0 / @IndexDataPages), @Rebinds));

-- Cost components (no rewinds)
DECLARE
    @InitialCost float = @RandomIO + @CPUbase + @CPUrow,
    @RebindCPU float = @Rebinds * (1e0 * @CPUbase + @CPUrow),
    @RebindIO float = (1e0 / @Rows) * ((@PSWR - 1e0) * @IO);

-- Result
SELECT
    OpCost = @InitialCost + @RebindCPU + @RebindIO;

db<>fiddle


Combine multiple similar queries into one I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5.

------------------------------------------------------
id | var      | weight | metric_1 | metric_2 | week
------------------------------------------------------
1  | 9        | 0.56   | 4        | 2        | 8
2  | 3        | 1      | 3        | 1        | 7
3  | 7        | 0.98   | 3        | 5        | 8
------------------------------------------------------

Here's the query I'm using. I need same query for each metric, from 1 to 10.

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score
FROM
    (
        SELECT
            COUNT(surveys.id) AS sample_size,
            SUM(weight),
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN pond END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN pond END) AS detractor,
            MAX(week)
        FROM surveys
        WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
    ) t

I there a way to combine those queries instead of having one query for each metric? The only difference is the WHERE clause:

        WHERE (...) AND (metric_1 BETWEEN 1 AND 5) /* metric_2, ...10 */

metric columns are redundant in survey table. we can create a new metric type column to differentiate multiple metrics and to normalize survey table. we can even do this normalization in inline view.

following sample query may explain this. it use SurveyNormalized inline view and it take aggregate group by metricType.

CREATE TABLE #Surveys(id int, VAR int, [weight] float, metric_1 int, metric_2 int, [week] int)

INSERT INTO #Surveys VALUES(1, 9, 0.56 , 4, 2, 8), (2, 3, 1, 3, 1, 7), (3, 7, 0.98 , 3, 5, 8)

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score,
    t.metricType
FROM
    (
        SELECT
            COUNT(id) AS sample_size,
            SUM(weight) [weight],
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN 1 END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN 2 END) AS detractor,
            MAX(week) wk,
            metricType
        FROM (
                SELECT id, var, weight, metric_1 AS metric, 'metric_1' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
                UNION
                SELECT id, var, weight, metric_2 AS metric, 'metric_2' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_2 BETWEEN 1 AND 5)
            )SurveyNormalised
            GROUP BY SurveyNormalised.metricType

    ) t


Alwayson Manual Failover failed We have SQL Server 2016. We tried to manually fail-over our availability group (has 2 replicas no listener), and an error message was given while failing-over.

Windows cluster service is up and running with no errors in cluster services. I tried adding NT AUTHORITY\SYSTEM  as sysadmin on the SQL server nodes.

The problem still persists.

Error message :

Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Hadr.FailoverTaskWorkItem.DoWork()
   at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

===================================

Failed to perform a manual failover of the availability group 'XX' to server instance 'XXX'. (Microsoft.SqlServer.Management.HadrModel)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.16000.28+((SSMS_Rel).161022-0456)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.HadrModel.Task.Perform(IExecutionPolicy policy, CancellationToken token, ScenarioTaskHandler taskDelegate)
   at Microsoft.SqlServer.Management.Hadr.FailoverTaskWorkItem.DoWork()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.DoCustomAction(String script, String toplevelExceptionMessage)

===================================

Failed to move a Windows Server Failover Clustering (WSFC) group to the local node (Error code 5963).  The WSFC service may not be running or may not be accessible in its current state, or the specified cluster group or node handle is invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
Failed to designate the local availability replica of availability group 'XX' as the primary replica.  The operation encountered SQL Server error 41018 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.2164&EvtSrc=MSSQLServer&EvtID=41018&LinkId=20476

------------------------------
Server Name: XX
Error Number: 41018
Severity: 16
State: 0
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

We have 3 nodes, 2 local and 1 Azure.
I have added SYSTEM as sysadmin to all three nodes. Before failing over I change the availability mode to synchronous and make sure there is no data loss.

We have found the issue it was a Quorum disk resource attached to the AGCLUSTER which was not able to take into to the other nodes.

We removed the disk and issue is gone

Thank you everyone for your support and your valuable time!!


Select rows based on latest date This are the tables:

Material (Code, ID)
Itemtrans (IteID, FtrID, Tradecode)
Fintrade (Ftrdate, ID)

Material.ID=Itemtrans.IteID
Itemtrans.FtrID=Fintrade.ID

I want to list the last record on all the codes (Material.Code), tradecodes (Itemtrans.Tradecode) and Ftrdate (Fintrade.Ftrdate) based on Fintrade.Ftrdate (the date when registered).

I made this, but i think i am losing something :(

SELECT
         MAT.CODE COD_ARTICOL,
         FTR.FTRDATE DATA_DOCUMENT,
         FTR.TRADECODE DOCUMENT
FROM ITEMTRANS ITM
LEFT JOIN
  ( SELECT
        ID, TRADECODE,
        MAX(FTRDATE) AS maxFTRdate
    FROM FINTRADE
    GROUP BY ID
  ) AS x ON x.ID = ITM.ITEid
LEFT JOIN FINTRADE FTR ON  FTR.id = x.id
                   AND FTR.FTRDATE = x.maxFTRdate

LEFT JOIN MATERIAL MAT ON MAT.id = ITM.ITEID
LEFT JOIN FINTRADE FTR ON FTR.ID = ITM.FTRID

I need some help :(

SELECT m.code,
       i.TradeCode,
       f.Ftrdate
       FROM ( SELECT MAX( Fintrade.Ftrdate ) as Ftrdate,
                     Fintrade.ID as ID
                     FROM Fintrade
            GROUP BY Fintrade.Ftrdate )  as f
INNER JOIN (SELECT itemtrans.iteID as iteID,
                   itemtrans.FtrID as FtrID,
                   MAX( itemtrans.TradeCode ) as TradeCode
            FROM itemtrans
            GROUP BY itemtrans.FtrID ) as i on f.ID = i.FtrID
INNER JOIN ( SELECT MAX( material.code ) as code,
                    material.id as id
            FROM material
            GROUP BY material.id ) as m
                    on i.iteID = m.id


Why is my indexed DISTINCT ON so much slower than my INNER JOIN? I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the latest_purchase_id column and update it with a trigger whenever I add a purchase (see https://dba.stackexchange.com/a/243988/186435).

I'd rather not use a trigger, so I tried using a DISTINCT ON query with an index, but it's much slower and I'm not sure why.

Table customers:

       Column        |  Type    |                       Modifiers                        | Storage  | Stats target | Description
---------------------+----------+--------------------------------------------------------+----------+--------------+-------------
 id                  | integer  | not null default nextval('customers_id_seq'::regclass) | plain    |              |
 latest_purchase_id  | integer  |                                                        | plain    |              |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_latest_purchase_id" btree (latest_purchase_id)
Foreign-key constraints:
    "customers_latest_purchase_fk" FOREIGN KEY (latest_purchase_id) REFERENCES purchases(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "purchases" CONSTRAINT "purchases_customer_fk" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

Table purchases:

     Column   |  Type     |                        Modifiers                       | Storage  | Stats target | Description
--------------+-----------+--------------------------------------------------------+----------+--------------+-------------
 id           | integer   | not null default nextval('purchases_id_seq'::regclass) | plain    |              |
 customer_id  | integer   |                                                        | plain    |              |
Indexes:
    "purchases_pkey" PRIMARY KEY, btree (id)
    "purchases_customer_id_id" btree (customer_id, id)
    "purchases_customer_id" btree (customer_id)
Foreign-key constraints:
    "purchases_customer_fk" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "customers" CONSTRAINT "customers_latest_purchase_id" FOREIGN KEY (latest_purchase_id) REFERENCES purchases(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

DISTINCT ON query:

EXPLAIN ANALYZE SELECT DISTINCT ON (customer_id) id, customer_id FROM purchases ORDER BY customer_id DESC, id DESC;
 Result  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.050..1478.196 rows=823 loops=1)
   ->  Unique  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.047..1477.754 rows=823 loops=1)
         ->  Index Only Scan Backward using purchases_customer_id_id on purchases  (cost=0.43..157850.96 rows=1866163 width=8) (actual time=0.045..1066.759 rows=1866132 loops=1)
               Heap Fetches: 1363529
 Planning Time: 0.096 ms
 Execution Time: 1478.408 ms

INNER JOIN query based on latest_purchase:

EXPLAIN ANALYZE SELECT c.id, p.id FROM customers c JOIN purchases p ON c.latest_purchase = p.id;
 Nested Loop  (cost=0.43..43877.27 rows=7594 width=8) (actual time=0.508..112.665 rows=755 loops=1)
   ->  Seq Scan on customers d  (cost=0.00..213.94 rows=7594 width=8) (actual time=0.006..2.861 rows=7594 loops=1)
   ->  Index Only Scan using customers_purchase_pkey on purchases p  (cost=0.43..5.75 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=7594)
         Index Cond: (id = c.latest_purchase)
         Heap Fetches: 583
 Planning Time: 1.032 ms
 Execution Time: 112.861 ms

This is the answer:

  There are a lot (thousands) of purchases per customer.

DISTINCT ON is fast for few purchases per customer. See:

Select first row in each GROUP BY group?

This should be much faster:

SELECT c.id AS customer_id, p.id AS purchase_id
FROM   customers c
LEFT   JOIN LATERAL (
   SELECT p.id
   FROM   purchases p
   WHERE  p.customer_id = c.id
   ORDER  BY p.id DESC
   LIMIT  1
   ) p ON true;

Subtle difference: every customer is in the result, even with no purchase at all.

Your index "purchases_customer_id_id" btree (customer_id, id) is good for this. An index on (customer_id, id DESC) would be even slightly better.

See:

Optimize GROUP BY query to retrieve latest row per user

Aside 1:

The 1st plan shows rows=823, the 2nd rows=755. Suggests you have purchases.customer_id that have no match in table customers, which typically shouldn't be. Add a FK constraint from purchases.customer_id to customers.id and make purchases.customer_id NOT NULL to enforce referential integrity.

Aside 2:

Lots of Heap Fetches at the end of each query plan. Are you vacuuming enough. See:

How does PostgreSQL perform ORDER BY with a b-tree index on the field?


how to add an extended events tracking session to SSAS? I would prefer to have a script to achieve this, but as I am not so familiar with SSAS I am using the wizard to create an extended events session in SSAS.

The way I am doing it is right clicking on the following place and getting a new session created:

wonderful,

but on the second screen, I pick this default tracking to start with, which is fine, but then I get this nasty error message that I am not aware as how to fix:

There are no valid data storage targets for this session.

I move on, to the events tab and check which events are included

Just to get stuck in the next tab, Data Storage:
which does not allow me to proceed.

How can I get over this

There are no valid data storage targets for this session.

or even more specifically, how can I set up an extended events session to track what is going on inside my ssas?

I would prefer a script, but any ways to see what processes and\or queries are running inside my ssas databases is fine.

The target is where the event data is stored.  For example, to write the data to a file use the event_file target type.  To create an extended event and overcome the error on the Data Storage page you're seeing, a target will need to be selected.  There's a wide range of events and exactly what activities you want to track will determine what events you'll want to use.  The script below is an example that creates an extended event that is saved to a local file.  Once this is created you can view the event data by double-clicking on the file, and it will open in SSMS.  This particular extended event tracks the QueryBegin event, which will records queries against the SSAS database.  By default, this will include fields such as the time the query began, the query text, and the NT User Name of of the account that submitted the query.  The default values were left for options such as the max_file_size and maxMemory properties, and of course you'll want to adjust these as necessary.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ObjectDefinition>
    <Trace>
      <ID>XE_Query_Test</ID>
      <Name>XE_Query_Test</Name>
      <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
        <event_session name="XE_Query_Test" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <event package="AS" name="QueryBegin" />
          <target package="package0" name="event_file">
            <parameter name="filename" value="C:\Test\XE_Query_Test.xel" />
            <parameter name="max_file_size" value="4096" />
            <parameter name="max_rollover_files" value="10" />
            <parameter name="increment" value="1024" />
          </target>
        </event_session>
      </XEvent>
    </Trace>
  </ObjectDefinition>
</Create>


Display dates not in ascending for To_Char can i know why and how i can solve the problem whereby if i use To_Char it does not sort the date in ascending. Below is my code

    select distinct To_Char (attendance_Date, 'dd/mm/yyyy')
    from DIT_2010MAR_CIT4114A_FYP1_NO
    order by 1 asc

result obtained is

DATE
----------
01/05/2016
02/05/2016
03/04/2016
04/04/2016
05/04/2016
06/04/2016

If you sort by dates formatted in DD/MM/YYYY, then the sorting will put all the first of month first (of every month), then the seconds of month, etc.

You can't sort by a column not included in a select distinct, though. To sort by ascending date, group by and use the actual date column instead of the formatted date via to_char:

select To_Char (attendance_Date, 'dd/mm/yyyy')
from DIT_2010MAR_CIT4114A_FYP1_NO
group by attendance_Date
order by attendance_Date asc

If attendance_Date is a timestamp, cast it to date first (everywhere in the query).


Postgresql 9.6 best settings for large admin work (min_wal and max_wal) We have a server that's disconnected from the world. It's a high end system with 48GB memory and 500GB SSD hard disk, 16 core CPUs. We're trying to do a pg_restore of a simple database dump of less than 10 tables, no binary data or blobs, just simple text (a comments system). But one table has about 200GB of data, so it's large.

There's no other jobs for this DB. Only this maintenance task. What are the best settings for this purpose given the config above? PGSQL's documentation does not help me too much. My specific question is around wal settings.

If we're OK with using the whole of this server just to do a pg_restore, and nothing else other than PG is on this server, what settings should we use? This is what we have now:

maintenance_work_mem = 1500MB
fsync = off
synchronous_commit = off
wal_level = minimal
full_page_writes = off
wal_buffers = 64MB
#-----  checkpoint_segments = 512
#-----  max_wal_size = (3 * checkpoint_segments) * 16MB
#-- min_wal_size = 100MB    # 80MB is the default
max_wal_size = 24576MB   # based on 512 checkpoint_segments
max_wal_senders = 0
wal_keep_segments = 0
archive_mode = off
autovacuum = off

Note that using top, we find that memory is not the issue. The CPU cores are spiking to 100 or so, and then dipping. This is an intensive write process, so that makes sense. Welcome any simple-to-understand guidance on how the min_wal_size should be set -- note that it's commented for us now.

I guess you only want to test if the restoration of the dump works and nothing more, which means you can do some unsafe configuration changes. Let's first start with your settings.
These one are a good call:
fsync = off
synchronous_commit = off
full_page_writes = off
wal_level = minimal
autovacuum = off

These three are only important if you are using replication, and because you already set wal_level to minimum, you are not using it, so they are not important:
wal_keep_segments = 0
archive_mode = off
max_wal_senders = 0

You have a lot of RAM which won't be used for anything, I would bump this one up even more:
maintenance_work_mem = 3GB

I would leave wal_buffers to it's default:
wal_buffers = -1

and bump shared_buffers (wall_buffers will be automatically calculated):
shared_buffers: 4GB

What you should try to concentrate is to have no, or as less checkpoints as you can during your restore. Checkpoints are controlled by max_wal_size and checkpoint_timeout. First of bump checkpoint_timeout to something like 20h, so that a timed checkpoint never happens while you restore:
checkpoint_timeout = 20h

Then you can set max_wal_size to a value as high as your disk space allows you. If your restored DB is 200GB and your disk 500GB, you should be safe to set max_wal_size to 100GB because Postgres can store up to two checkpoints of wals (which is 2xmax_wal_size):
max_wal_size = 100GB

min_wal_size won't matter that much in your case, but you can probably bump it to 10GB
min_wal_size = 10GB

I would also recommend that you use pg_restore with --jobs=NUM where NUM would probably be the number of CPU cores, but it also depends on the speed of your SSD, so you can play with this parameter.
Besides Postgres settings, I would also recommend that if possible you add an additional SATA drive (7200RPM will be fine) and symlink pg_wal directory to that SATA disk. That is the directory where Postgres saves WALs, and because they are written in append, SATA is fast enough for them. It will reduced the load on the SSD, but will also mean you will be able to bump max_wal_size even more (depending on the size of the SATA disk).
Finally don't forget to restore your settings to the sane values after the dump is restored.


SQL Server - SQL Agent Alerts - Alert 824 warning about old problems I have alerts enabled on a SQL Server 2017 where corruption was encountered and repaired over a year ago. I have also confirmed that there is no new corruption on any database on this server.

When restarting the SQL Server it sends out an error 824 alert message that states the following:

  DATE/TIME:  1/17/2019 7:54:22 PM

  DESCRIPTION: SQL Server detected a logical consistency-based I/O
  error: incorrect checksum (expected: 0xe660dd36; actual: 0xcd481907).
  It occurred during a read of page (1:386939) in database ID 7 at
  offset 0x000000bcef6000 in file 'C:\DATA\database.mdf'.  Additional
  messages in the SQL Server error log or operating system error log may
  provide more detail. This is a severe error condition that threatens
  database integrity and must be corrected immediately. Complete a full
  database consistency check (DBCC CHECKDB). This error can be caused by
  many factors; for more information, see SQL Server Books Online.

The issue here is that the date/time is from 11 months ago. What I am looking for is details on what the alert for error 824 is checking or what needs to be purged from 11 months ago to prevent this false positive alert from being sent out.

I have checked msdb.dbo.suspect_pages - there are no entries in that table.
All SQL server logs have rotate out in that time period so it is not something in the log.
I have ran checkDB a couple of times to confirm no new corruption.

Any suggestions?

I'm assuming that this is an agent alert, if so you could try to right click on the alert -> properties -> History and check the 'Reset count' box and it should clear out.


How to capture PRINT statements from TSQL called by CLR stored procedure I have a CLR stored procedure that (among other things) calls a TSQL stored procedure. The TSQL stored procedure runs some dynamic SQL, and it prints the SQL before it runs for debugging purposes. Nothing from the PRINT statements shows up in the client. What is a good way to allow us to see these commands when we need to troubleshoot?

Sample code:

C# code in dll

public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
            conn.Open();
            using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
            {
            c.ExecuteNonQuery();
        }
    }
}

Proc called by CLR

CREATE PROC DBO.Print_TSQL AS
PRINT 'WTF'
GO
--Exposing proc to SQL
CREATE PROC dbo.Print_CLR AS
EXTERNAL NAME
[CLRUtility].[CLRUtility.CLRUtility].Print_CLR
GO
--Executing, nothing is PRINTed for the client
EXEC dbo.Print_CLR

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event.

The basic implementation is as follows:

[SqlProcedure()]
public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.InfoMessage += new SqlInfoMessageEventHandler(CaptureMessage);
        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {
            c.ExecuteNonQuery();
        }
    }
}

private static void CaptureMessage(object sender, SqlInfoMessageEventArgs args)
{
  // do something with args.Message

  return;
}

At this point, you still need to do something with the Message. While there are a few options, some of them have security implications due to the environment. SQL Server's CLR host is highly constrained compared to the main CLR running on the OS that is used for Windows Apps, Console Apps, and ASP.NET apps.

If you use the static method approach as shown above, then what you do with the Message will impact the PERMISSION_SET value for the assembly containing this code:

If you simply want to print the message back to the user, then you can do something like the following which can be done in a SAFE assembly:

SqlContext.Pipe.Send(args.Message);

If you want to write the messages to a file, then you can do something like the following which can be done in an EXTERNAL_ACCESS assembly:

File.AppendAllText(FILE_PATH_CONSTANT, args.Message);

This might cause extra latency and not be worth it. But on the other hand, if the process fails in the middle, whatever was written to the file would still be there, so there is definitely some benefit to this approach.
But if you want to store the messages in a variable to do something with later, then you will need a static class variable since the method to capture the message is static. For example, you can declare private static m_Messages StringBuilder = new StringBuiler("");. Then, in your CaptureMessage method you can do the following:

m_Messages.AppendLine(args.Message);

This seems simple enough. And for most any environment outside of SQLCLR it is. However, using a static variable in SQLCLR requires that the assembly have a PERMISSION_SET of UNSAFE, and it is best to avoid that if at all possible. And the reason that the assembly is required to be marked as UNSAFE is that the AppDomain where this code is running is shared across all Sessions / SPIDs. Hence, a static class variable is also shared across Sessions and that can lead to very odd behaviour.

Thankfully, all hope is not lost if you want to collect messages to an instance variable. To do this, you need to define the handler inline using an anonymous delegate. The anonymous method is within the same scope as the rest of your code so it has access to local variables. This is quite handy as it allows you to easily store the messages in an assembly that has a PERMISSION_SET of SAFE (which is highly preferred).

[SqlProcedure()]
public static void Print_CLR()
{
    StringBuilder _Messages = new StringBuilder("");

    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs args)
        {
            SqlConnection _Connection = (SqlConnection)sender;
            // _Connection.DataSource is the "Server" of the ConnectionString
            // _Connection.Database is the CURRENT database of the Connection

            _Messages.Append(args.Message);

            return;
        };

        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {
            c.ExecuteNonQuery();
        }
    }
}


How to automate Oracle DB installation (like a 'hands-free' install)? I installed the Oracle 11g2 'standard' edition using the following zip-files:

linux.x64_11gR2_database_1of2.zip
linux.x64_11gR2_database_2of2.zip

I used the installation guide by Oracle which I found quite tedious, e.g. you need to install/configure an X-Server because the default installer is a GUI which you have to 'babysit' for some time.

Is there a way to automate a default Oracle installation?

I mean something like a script where I can specify: tell me what yum/apt-packages are missing, here are my oracle-zip-install-files, install it under $MY_BASEDIR and for everything else use some sane defaults!

And no GUI.

I know that there is an express-edition which comes as an rpm - this looks convenient on the first glance, but it seems to exclude some stuff the 'standard' edition includes, e.g. Pro*C.

Two solutions:

Install Oracle manually the first time. Then zip all ORACLE_HOME and for future installation simply:

Unzip oracle_home into the new home
Run $ORACLE_HOME/bin/relink all
Run ./runInstaller -silent -attachHome ORACLE_HOME="<Oracle_Home_Location>" ORACLE_HOME_NAME="<Oracle_Home_Name>"

SID is a property of the database/instance and is separate from installation of the software.

Other solution (not my preferred but probably the preferred for mass deployment) is to create a response file. The sample is available in the installation dir:

<11g Disk>/database/response/enterprise.rsp

Copy this file, edit it and replace default values with your ones (mainly the path, install type, etc). Then run:

./runInstaller -silent -responseFile myresponsefile.rsp

You can also install the DB manually the first time and record the response file witht he command:

./runInstaller -record -destinationFile myresponsefile.rsp

The first one is my preferred because you can easily install and patch the database with the last PSU available, then you have a consolidated installation can be moved around. Of course all systems must be already prepared with needed packages.


Database Shrinking as a logged operation I would like to know if shrinking a data file is a logged operation or not. I do not have a definite answer to this. I have heard people say it is a logged operation but so far I did not see an evidence of this.

I would like to know if shrinking a data file is a logged operation or not.

It is fully logged operation and is severely bad process so ALWAYS try to avoid it. What it does is, when you hit the shrink button, quoting from Paul Randal blog

  A data file shrink operation works on a single file at a time, and
  uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS
  and other allocation maps) to find the highest page allocated in the
  file. It then moves it as far towards the front of the file as it can,
  and so on, and so on. In the case above, it completely reversed the
  order of the clustered index, taking it from perfectly defragmented to
  perfectly fragmented.

So basically it does page movement and frees empty space from the pages. This movement of page is logged in transaction log file. Due to this movement the logical ordering of pages changes and hence logical fragmentation comes in. The point to note here is this is not an atomic operation. If you stop the shrinking operation in between the changes done so far is not lost it is maintained and when you start next time it will continue from where it left.

See this Blog from Paul Randal for data file size management if you are worried about growing data file size.


Retrieve parent row when key appears in either one of another two tables My actual data and sql is more complicated, but I've stripped it down to the following. I have 3 tables.

match
+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
| 2  | match 2 |
| 3  | match 3 |
| 4  | match 4 |
+----+---------+

home_players
+----+----------+--------+
| id | match_id | player |
+----+----------+--------+
| 1  | 1        | Angus  |
| 2  | 1        | Bertie |
| 3  | 1        | Carl   |
| 4  | 1        | Dave   |
+----+----------+--------+

away_players
+----+----------+--------+
| id | match_id | player |
+----+----------+--------+
| 1  | 2        | Angus  |
| 2  | 2        | Bertie |
| 3  | 2        | Carl   |
| 4  | 2        | Dave   |
+----+----------+--------+

I want to know the match.id and match.title for all matches that Dave has played in, so expecting two rows:

+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
| 2  | match 2 |
+----+---------+

I've tried the following:

SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN home_players h ON m.id = h.match_id
INNER JOIN away_players a ON m.id = a.match_id
WHERE h.player = 'Dave' OR a.player = 'Dave'

But this only ever returns "match 1" and misses "match 2", which Dave has also played in.

+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
+----+---------+

It's actually returning a number of rows, but the DISTINCT sorts that out.

What's the sql I need here?

A good way to do that is likely to use the UNION of two queries, one for home_players, and one for away_players.  Something like:

SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN home_players h ON m.id = h.match_id
WHERE h.player = 'Dave'
UNION
SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN away_players a ON m.id = a.match_id
WHERE a.player = 'Dave'


Database stuck in restoring mode during log shipping The error log say -
The database is marked RESTORING and is in a state that does not allow recovery to be run.

This is expected behavior for a log shipping job set to norecovery.  The logs are being restored with norecovery, which means that more logs can be restored on top, which is what you want.  No other transactions can occur on the target database;  it's a copy of the original.  If something goes wrong with the original, you bring the target online with Restore YourDB with recovery.

I've never used log shipping with any options other than norecovery, but there's also a standby option that will allow the target database to be used read-only (but the users will all be kicked off during the next restore). If this doesn't suit your requirements, maybe you need to look at something other than log shipping.


Using xtrabackup-v2 as SST method in MariaDB Galera cluster I have configured mariadb galera cluster as docker swarm service using official docker image:
during cluster startup rsync (default SST method) works fine.
Wanted to change it to xtrabackup or xtrabackup-v2, so used the following configuration in my.cnf according to docs:

[mysqld]
wsrep_sst_auth = <wsrep_sst_user>:<password>
wsrep_sst_method = xtrabackup-v2 (or xtrabackup)
datadir = /var/lib/mysql/

[client]
socket =/var/run/mysqld/mysqld.sock

donor server starts fine, but getting the errors below during joiner server startup:

  2018-05-07 13:08:26 139750451541760 [Warning] WSREP: Gap in state sequence. Need state transfer.
  2018-05-07 13:08:26 139750232831744 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.0.0.37' --datadir '/var/lib/mysql/'   --parent '108'  '' '
  WSREP_SST: [INFO] Logging all stderr of SST/Innobackupex to syslog (20180507 13:08:26.937)

  2018-05-07 13:08:29 139750249850624 [Note] WSREP: (fdead878, 'tcp://0.0.0.0:4567') turning message relay requesting off

  2018-05-07 13:10:07 139750232831744 [ERROR] WSREP: Failed to read 'ready ' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.0.0.37' --datadir '/var/lib/mysql/'   --parent '108'  ''
          Read: '(null)'

  2018-05-07 13:10:07 139750232831744 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.0.0.37' --datadir '/var/lib/mysql/'   --parent '108'  '' : 32 (Broken pipe)

  2018-05-07 13:10:07 139750451541760 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
  2018-05-07 13:10:07 139750451541760 [ERROR] Aborting

  Error in my_thread_global_end(): 1 threads didn't exit

Other wsrep values:

wsrep_on=ON
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm:// (for first node to start, and virtual_ip of the first node on 2 other nodes)

How to configure xtrabackup as SST method in MariaDB correctly?

The two scripts (or maybe just the last one, but you need both) /usr/bin/wsrep_sst_common and /usr/bin/wsrep_sst_xtrabackup-v2 are broken in 10.2.13, but you can get fixed versions from bug ticket MDEV-15254.


Fixing a MySQL query that miscounts cases where count is 0 I have the following SQL code:

SELECT widgets.campaign_name, widgets.enddate, widgets.active,
       widgets.id_widget,COUNT(*) AS entries
  FROM widgets
  LEFT JOIN entries ON widgets.id_widget = entries.id_widget
  WHERE widgets.active IN ( 0 )
    AND id_user = 8
  GROUP BY widgets.campaign_name

It does exactly what I want to do beside one thing. If I have 0 entries on a campaign, it still shows 1 entry. I want to get rid of this problem and make it count exactly how many entries are. If 0, it should display 0.

The code above works fine for 1,2....n entries.

The problem is that outer joins produce NULL values in columns of the (right) table for rows of the left table that don't have a match. So you should use COUNT(entries.id_widget) which counts not null values in that column and not COUNT(*) which counts rows.

Some more notes:

you can use aliases for tables to make the query more readable.
It's good to prefix all columns with the table name (i.e. widget.id_user) or alias (w.id_user). Otherwise the query will break if a column with the same name is added later in table entries.
Unless the column campaign_name is the primary key of widgets or has a unique constraint, you should not use GROUP BY this way. All columns in the SELECT list should be either used in aggregate functions or appear in the GROUP BY list. MySQL does no such check and indeterminate results can be returned by queries that do not follow this rule.
If the columns (in SELECT) depend on the columns in GROUP BY (and that would be true in this case if campaign_name had a unique constraint), that's one exception that the results will be determinate and you can use this "feature" of MySQL. You should also check that ONLY_FULL_GROUP_BY setting is disabled if you want to use this.
For more details, read the MySQL Documentation: MySQL Extensions to GROUP BY and the discussions here: Why does MySQL add a feature that conflicts with SQL standards?.

The query becomes:

    SELECT w.campaign_name, w.enddate, w.active, w.id_widget,
           COUNT(e.id_widget) AS entries
    FROM widgets AS w
      LEFT JOIN entries AS e  ON w.id_widget = e.id_widget
    WHERE w.active IN ( 0 )
      AND w.id_user = 8
    GROUP BY w.id_widget,
             w.campaign_name,
             w.enddate, w.active ;


SQL Server Linked Servers and Remote Statistics I'm trying to get a better understanding of how SQL Server uses statistics on remote SQL Servers (e.g. a Linked Server). I understand that the user needs db_owner/db_ddladmin rights to be able to use the stats on the remote box. At first this seems really straight forward based on the below text from Microsoft.

  To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

The pieces that is confusing me is the word "User". We use a standard security context for all connections rather than the user's login. This login/user has the needed permissions as defined above over the databases in question. Additionally, we do not use Impersonation.

I have tried running queries and capturing the traces on both ends using accounts with and without elevated permissions on the remove server. It looks to me like the remote server is not collecting statistics unless the actual user (the guy at the keyboard) has the elevated rights. I believe this to be true because I am only seeing calls to sys.sp_table_statistics2_rowset when the user has the needed rights.

I really hope this makes sense.

Does anyone have an understanding of how this really works in SQL 2008 R2 and below?

What I do is this 'trick':

Give this 'linkuser' read, whatever else needed and ddl_admin privileges, then run the following script to ensure that none of the ddl_admin privileges can actually be used.

But by being technically member of ddl_admin the user can now see statistics in the remote db! (note: from SQL2012SP1 there is no need for this hack, the stats are visible as they should be)

DENY CREATE TABLE TO [LinkUser]
DENY CREATE VIEW TO [LinkUser]
DENY CREATE PROCEDURE TO [LinkUser]
DENY CREATE FUNCTION TO [LinkUser]
DENY CREATE RULE TO [LinkUser]
DENY CREATE DEFAULT TO [LinkUser]
DENY CREATE TYPE TO [LinkUser]
DENY CREATE ASSEMBLY TO [LinkUser]
DENY CREATE XML SCHEMA COLLECTION TO [LinkUser]
DENY CREATE SCHEMA TO [LinkUser]
DENY CREATE SYNONYM TO [LinkUser]
DENY CREATE AGGREGATE TO [LinkUser]
DENY CREATE MESSAGE TYPE TO [LinkUser]
DENY CREATE SERVICE TO [LinkUser]
DENY CREATE CONTRACT TO [LinkUser]
DENY CREATE REMOTE SERVICE BINDING TO [LinkUser]
DENY CREATE ROUTE TO [LinkUser]
DENY CREATE QUEUE TO [LinkUser]
DENY CREATE SYMMETRIC KEY TO [LinkUser]
DENY CREATE ASYMMETRIC KEY TO [LinkUser]
DENY CREATE FULLTEXT CATALOG TO [LinkUser]
DENY CREATE CERTIFICATE TO [LinkUser]
DENY CREATE DATABASE DDL EVENT NOTIFICATION TO [LinkUser]
DENY CHECKPOINT TO [LinkUser]
DENY ALTER ANY SCHEMA TO [LinkUser]
DENY ALTER ANY ASSEMBLY TO [LinkUser]
DENY ALTER ANY DATASPACE TO [LinkUser]
DENY ALTER ANY MESSAGE TYPE TO [LinkUser]
DENY ALTER ANY CONTRACT TO [LinkUser]
DENY ALTER ANY SERVICE TO [LinkUser]
DENY ALTER ANY REMOTE SERVICE BINDING TO [LinkUser]
DENY ALTER ANY ROUTE TO [LinkUser]
DENY ALTER ANY FULLTEXT CATALOG TO [LinkUser]
DENY ALTER ANY SYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY ASYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY CERTIFICATE TO [LinkUser]
DENY REFERENCES TO [LinkUser]
DENY ALTER ANY DATABASE DDL TRIGGER TO [LinkUser]
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO [LinkUser]


How does MySQL DELETE work internally? I have two major doubts:

Does a DELETE query first identify all rows to be deleted (from the where clause) and then delete them all, or does it delete rows as and when they match the where clause?
What level of lock does a DELETE query take? Is a full table lock? If not then how is the case of insertion/update while the query is running handled?

For instance I run a DELETE where column X has value 0. At the time the DELETE query examined a particular row the column X had value 1, then later (but before the query finished) some other user updated that row's X column to 0. So what happens when the delete finishes? Will that row be present or absent?

DELETE deletes rows as it sees them while using record locks. The delete actually happens at commit time. A log of the changes is made place so other transactions that started before the delete can still see the values.

If an UPDATE happens a small period later, then either a deadlock will happen, or it will lock wait and return no rows found.

So main thing to note is programming:

always handle errors like deadlocks, there's no obvious way for the database to resolve to consider your business case which is why it exits.
use transactions when you need all or none approaches to data changes.


Moving log files & data in MySQL Is it possible to separate out data files and log files of MySQL from one drive to another?

For eg:

Data (actual database data and system tablespace ibdata) in one partition, say /data and log files (ibdata0 and ibdata1 only) in one partition, say /redo?

Is it possible? I know the binary logs can be moved to a different partition but I have the doubt for above scenario.

I'm using Percona Server 5.6 on linux.

Edit:

Just checked out, If I set innodb_log_group_home_dir to a different location, would it work?

Yes, using innodb_log_group_home_dir will put your ib_logfile* on the specified location. You can also opt to do the following:

[mysqld]
datadir=/var/lib/mysql
innodb_log_group_home_dir=/iblog
innodb_data_home_dir=/ibdata

So the system tablespace (ibdata1) goes inside /ibdatafiles folder, while the redo log files (ib_logfile*) goes inside /iblogfiles.

# ls -l /ibdata
total 12296
-rw-rw---- 1 mysql mysql 12582912 Jul 17 08:59 ibdata1
# ls -l /iblog
total 98320
-rw-rw---- 1 mysql mysql 50331648 Jul 17 08:59 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 17 08:59 ib_logfile1
# ls -l /var/lib/mysql
total 48
-rw-rw---- 1 mysql mysql  56 Jul 17 08:59 auto.cnf
-rw-rw---- 1 mysql mysql   5 Jul 17 08:59 centos1.pid
drwx------ 2 mysql mysql   0 Jul 17 08:59 mysql
-rw-rw---- 1 mysql mysql 151 Jul 17 08:59 mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 Jul 17 08:59 mysql-bin.index
srwxrwxrwx 1 mysql mysql   0 Jul 17 08:59 mysql.sock
drwx------ 2 mysql mysql   0 Jul 17 08:59 performance_schema
drwx------ 2 mysql mysql   0 Jul 17 08:59 test


What is the best way to remove large amount of data from large tables? I have two large SQL 2008 tables:

table_header with 7 columns and ~ 2 million records
table_detail with 22 columns and ~ 78 million records

The common field between the two is called item_name which is varchar(32).

I need to remove all records from table_detail where item_name is not in table_header.

What is the best way to accomplish this task?

This is a busy production server. There is no maintenance scheduled anytime soon, but this data needs to be gone now. I can't just run something like:

delete from table_detail
where item_name not in (
select item_name from table_header);

I've faced such problems a few times so I have 2 tips for you.

1 Use this script that breaks your delete in packages so you will not run the risk of crashing the log

declare @var int, @ var2 int
set @var = (select count (1) from <Table_to_Delete> WHERE <Your-delete-clause>)
set @ var2 = 0

while @ var2 <> @var
begin
set rowcount 50000 - batchsize

print getdate ()
delete from <Table_to_Delete> WHERE <Your-delete-clause>

set @ var2 = @ var2 + @@ ROWCOUNT
print getdate ()

checkpoint - it's optional to avoid log grow

end

2) Now if the amount of rows that should remain in the table is really small, example if you are to delete 80 ~ 90% of the table I recommend you copy the table structure and copy the data to the new table. This option is valid if the table structure is simple because you will have to remember to copy all indices, foreing keys, and explicit permissions.