What's the approach to figure out reason for mysql crash? The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" query running when it crashes. Sometimes the table is corrupted so I have to drop it and rebuild, sometimes the table is just fine (both cases are when the "load data infile" is running).

This happened many times and I've recovered the database several times. But it keeps happening and I want to find out the reason and resolve the cause. I've follow the recovery link, and I've tried to search around the failure message. But couldn't figure it out..

So what will you do to find out the crash cause? Is there a guideline? Really appreciate it.

  2014-12-08 11:01:52 30924 [Note] InnoDB: Resuming purge

  2014-12-08 11:59:10 7f945e0e3700  InnoDB: Assertion failure in thread 140275209877248 in file buf0buf.cc line 3687

  InnoDB: Failing assertion: buf_fix_count > 0

  InnoDB: We intentionally generate a memory trap.

  InnoDB: Submit a detailed bug report to (a link was here, I don't have enough reputation to post links yet).

  InnoDB: If you get repeated assertion failures or crashes, even

  InnoDB: immediately after the mysqld startup, there may be

  InnoDB: corruption in the InnoDB tablespace. Please refer to (a link was here, I don't have enough reputation to post links yet)

  InnoDB: about forcing recovery.

  19:59:10 UTC - mysqld got signal 6 ;

  This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at percona bugs webiste (a link was here, I don't have enough reputation to post links yet)

  key_buffer_size=33554432

  read_buffer_size=131072

  max_used_connections=6

  max_threads=1002

  thread_count=3

  connection_count=3

  It is possible that mysqld could use up to

  key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 432019 K  bytes of memory

  Hope that's ok; if not, decrease some variables in the equation.

  Thread pointer: 0x0

  Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong...

  stack_bottom = 0 thread_stack 0x40000

  /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x8c821e]

  /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x671f44]

  /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f98c3646cb0]

  /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f98c2a9c425]

  /lib/x86_64-linux-gnu/libc.so.6(abort+0x17b)[0x7f98c2a9fb8b]

  /usr/sbin/mysqld[0xa5e1c8]

  /usr/sbin/mysqld[0xa754ce]

  /usr/sbin/mysqld[0xa766b3]

  /usr/sbin/mysqld[0xa5a727]

  /usr/sbin/mysqld[0xa3de9c]

  /usr/sbin/mysqld[0x9e384d]

  /usr/sbin/mysqld[0x9e0b7d]

  /usr/sbin/mysqld[0x9e1ecc]

  /usr/sbin/mysqld[0x9b299c]

  /usr/sbin/mysqld[0xa09f26]

  /usr/sbin/mysqld[0x9fc62c]

  /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7f98c363ee9a]

  /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f98c2b59ccd]

  You may download the Percona Server operations manual by visiting (a link was here, I don't have enough reputation to post links yet). You may find information in the manual which will help you identify the cause of the crash.

  141208 11:59:14 mysqld_safe Number of processes running now: 0

  141208 11:59:14 mysqld_safe mysqld restarted

This appears to be a regression bug that exists in Percona Server 5.6.14 and possibly some previous versions of 5.6.
https://bugs.launchpad.net/percona-server/+bug/1247021
It appears it was fixed in Percona Server 5.6.16-64.0:

Due to a regression in the buffer pool mutex split, a combination of InnoDB compression, write workload, and multiple active purge threads could lead to a server crash. Bug fixed #1247021.
— http://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.16-64.0.html


ERROR: requested character too large for encoding I'm using PostgreSQL 9.6 on Linux.
I got an error when I do a test on chr() function.

postgres=# select chr(1199111);
ERROR:  requested character too large for encoding: 1199111
postgres=# select chr(55296);
ERROR:  requested character not valid for encoding: 55296
postgres=# select chr(100000);
 chr
-----

(1 row)

Here, as you can see with value 100k it worked (didn't raise error), however others didn't. I'm curious about that. Could someone please explain to me why ?

I attach test script

do
$$
declare
str text ;
begin
for i in 1..1200000
loop
begin
select chr(i) into str;
exception when others then raise notice '=> i: %     => str: %', i , str ;
#exit; -- you can uncomment this star key
end ;
end loop;
end
$$ ;

UPDATED

postgres=# show server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

Because it didn't raise an error, doesn't mean it worked. None of these work, but only one of them is so absurd as to make chr() explode.

100000 (186A0) maps to the Supplementary Multilingual Plane, but there is nothing there (yet).
55296 (U+D800) maps to the  Basic Multilingual Plane, but nothing is there (yet).
1199111 (U+124C07) is not even valid, it maps to a plane so high that Unicode doesn't even support it.

Unicode only supports 1,111,998: 17 planes × 65,536 characters per plane - 2048 surrogates - 66 noncharacters. See this answer for more information. 1.11 Million code points, is far less than 1.19 Million code points.


create specific DB-Admin Account is it possible to create in Mysql an admin-user that can modify everything exept the mysql-root user?

Thanks,
starflighter

Yes It's possible. As per MySQL documentation here
After connecting to the server as root, you can add new accounts. The following example uses CREATE USER and GRANT statements to set up four accounts:

For example

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by those statements have the following properties:

Two accounts have a user name of finley. Both are superuser accounts
with full privileges to do anything. The 'finley'@'localhost'
account can be used only when connecting from the local host. The
'finley'@'%' account uses the '%' wildcard for the host part, so it
can be used to connect from any host.

The 'finley'@'localhost' account is necessary if there is an
anonymous-user account for localhost. Without the
'finley'@'localhost' account, that anonymous-user account takes
precedence when finley connects from the local host and finley is
treated as an anonymous user. The reason for this is that the
anonymous-user account has a more specific Host column value than the
'finley'@'%' account and thus comes earlier in the user table sort
order. (user table sorting is discussed in
here.)
The 'admin'@'localhost' account can be used only by admin to connect
from the local host. It is granted the RELOAD and PROCESS
administrative privileges. These privileges enable the admin user to
execute the mysqladmin reload, mysqladmin refresh, and mysqladmin
flush-xxx commands, as well as mysqladmin processlist . No privileges
are granted for accessing any databases. You could add such
privileges using GRANT statements.
The 'dummy'@'localhost' account has no password (which is insecure
and not recommended). This account can be used only to connect from
the local host. No privileges are granted. It is assumed that you
will grant specific privileges to the account using GRANT statements.


MySQL server won't start with foreign_key_checks=0 I'm running Ubuntu with MySQL version 5.5.44

  mysqld  Ver 5.5.44-0ubuntu0.14.10.1 for debian-linux-gnu on x86_64 ((Ubuntu))

I have a DB with foreign keys. I would like to have the foreign key check disabled at the system level (not at the session).

I saw the system var foreign_key_checks that seems to control this behavior but when I set it in /etc/mysql/my.cnf (by adding foreign_key_checks = 0) the server won't start. The error log looks like:

150729 15:38:18 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150729 15:38:18 [Note] Plugin 'FEDERATED' is disabled.
150729 15:38:18 InnoDB: The InnoDB memory heap is disabled
150729 15:38:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150729 15:38:18 InnoDB: Compressed tables use zlib 1.2.8
150729 15:38:18 InnoDB: Using Linux native AIO
150729 15:38:18 InnoDB: Initializing buffer pool, size = 128.0M
150729 15:38:18 InnoDB: Completed initialization of buffer pool
150729 15:38:18 InnoDB: highest supported file format is Barracuda.
150729 15:38:18  InnoDB: Waiting for the background threads to start
150729 15:38:19 InnoDB: 5.5.44 started; log sequence number 56348937
150729 15:38:19 [ERROR] /usr/sbin/mysqld: unknown variable 'foreign_key_checks=0'
150729 15:38:19 [ERROR] Aborting

How can I configure my MySQL server to start with foreign key checks off at the system level?

Edit: behavior is the same with MySQL 5.6.25:

  mysqld  Ver 5.6.25-0ubuntu0.15.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

Root Cause

foreign_key_checks is not a global option you can preset at startup. Why ?

When you click on that link to the Documentation on foreign_key_checks, there is no chart that says it is global or session scope. Other options will specify the scope in a chart

ft_boolean_syntax says Variable Scope Global ; Dynamic Variable Yes
innodb_doublewrite says Variable Scope Global ; Dynamic Variable No
max_allowed_packet says Variable Scope Global ; Dynamic Variable Yes
sort_buffer_size says Variable Scope Global,Session ; Dynamic Variable Yes

With foreign_key_checks, it is session scope only.

Further proof of this is when you go to the command line and run this

mysqld --help --verbose > /tmp/mysqld.txt
vi /tmp/mysqld.txt

When you look inside the text file, you do not see --foreign-key-checks as a command line startup option. The other four variables I showed you can be used on the command line and in my.cnf.

If you have data to load, you can set that before loading:

SET foreign_key_checks = 0;
source mydata.sql

Proof of this is a standard mysqldump header ( first 10 lines )

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Please note line 8

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

What does the bottom of a mysqldump look like ? Like this:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Please note the line

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Epilogue

Bottom Line: foreign_key_checks is not meant of use in my.cnf. That make the most sense because (if allowed) starting mysqld with that would be damaging to referential integrity from startup.


mySQL for analytics and updating same row Looking for the best way to handle using mySQL/PHP for analytics and thus having multiple connections trying to update the same counters in the same row at the same time.

The problem being if I have a row like 'year-month-day-hour','hits' and I want to increase hits for every pageview, that's going to be a lot of connections trying to update that same field in the same row. I'm worried about contention and/or writing the wrong values.

Also this will be packaged for many clients on different kinds of hosting. Thanks for any suggestions.

You may need to create the counter table with 250 slots to spread the row locks.

First, here is the table:

CREATE TABLE HourCounter
(
    hr INT,
    slot TINYINT,
    cnt INT UNSIGNED,
    PRIMARY KEY (hr,slot)
) ENGINE=InnoDB;

All you need to do is insert into the random slots like this:

SET @NumberOfSlots = 250;
SET @X = UNIX_TIMESTAMP();
SET @uthr = @X - MOD(@X,3600);
INSERT INTO HourCounter (hr,slot,cnt)
    VALUES (@uthr,RAND() * @NumberOfSlots + 1,1)
    ON DUPLICATE KEY UPDATE cnt = cnt + 1
;

If you want to collect the counts, run this:

SELECT hr,SUM(cnt) HourCount
FROM HourCounter GROUP BY hr;

If you want to collapse the slots into a single number, do this once a week:

UPDATE HourCounter c
    INNER JOIN
    (
        SELECT hr,SUM(cnt) cnt,MIN(slot) mslot
        FROM HourCounter GROUP BY hr
    ) x USING (hr)
SET c.cnt  = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot = x.mslot, 0, c.slot);
DELETE FROM HourCounter WHERE slot <> 0 AND cnt = 0;

To reclaim all the disk space for this table, do this once a month:

ALTER TABLE HourCounter ENGINE=InnoDB;
ANALYZE TABLE HourCounter;

Give it a Try !!!

To give credit where is credit is due, I got the answer from page 140 of the Book

I have the book and refer to it well.


Replica is stuck waiting for Running two PostgreSQL 9.2.3 instances.  One is master and other is slave.

All was working for awhile, I could make changes on master and see them on the slave but now my slave is not getting changes any more.

1000     25069  0.2  0.1 131616 11280 pts/0    S    01:02   0:00 /usr/local/pgsql/bin/postmaster -D /mnt/pgdb/v9.2.3 -i
1000     25074  0.0  0.0 131724  1440 ?        Ss   01:02   0:00 postgres: startup process   waiting for 000000040000000700000083

I've tried countless things including completely blowing away the slave, running initdb and rsyncing the master back to the slave, launching the slave only to find it still stuck in this same dam state.

What the hell am I doing wrong here?

Slave's recovery.conf looks as follows:

standby_mode = 'on'
primary_conninfo = 'host=172.16.0.14'
trigger_file = '/tmp/pgfailover'
restore_command = 'cd .'

I've also tried the following on the master to no avail:

$PGDIR/psql -U $UNAME -d postgres -c "select pg_start_backup('clone',true);"
rsync -av --exclude pg_xlog --exclude postgresql.conf /mnt/pgdb/current/* 172.16.0.8:/mnt/pgdb/current/
$PGDIR/psql -U $UNAME -d postgres -c "select pg_stop_backup();"

UPDATE - Including relevant information pertaining to replicate problem.

postgresql.conf on the Master node

wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'

max_wal_senders = 3
wal_keep_segments = 5000
hot_standby = on
hot_standby_feedback = on

The Slave Node's postgresql.conf has these settings:

wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'

max_wal_senders = 3
hot_standby = on
hot_standby_feedback = on

It looks like the standby doesn't successfully connect to the master.

In order to be able to use the streaming replication, the master needs to have enough WALs in the streaming buffer that it can send to the standby.
Since you're not using log shipping, it needs to store all the logs from when you started the backup to when the standby has been started.

You can control the number of WAL's stored in the buffer by setting wal_keep_segments to a high enough value.

You should also make sure that max_wal_senders is set to the number of standby servers you have.

However, it is always best to have the WAL restore set as a backup for when the streaming replication doesn't work for any reason. You can achieve that by setting the archive_command in postgresql.conf and the restore_command in recovery.conf

For instance like this, assuming that /pgsql/backups/archive_logs is mounted as a nfs share accessible on both servers:

archive_command = 'cp %p /pgsql/backups/archive_logs/%f'
restore_command = 'cp /pgsql/backups/archive_logs/%f %p'


log_statement_stats: What is the significance of values in enclosed in brackets? I'm reviewing the output of log_statement_stats and would to like to understand the following questions.

[0.526408 user 0.112125 sys total]
=> The previous line already shows CPU seconds of user and system. What is the significance of this line?

Every other line as values in brackets. What is the significance of those values in enclosed in brackets?

2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT:  SELECT field1 AS a1, field2 AS a2, field3 AS a3, field4 AS a4, field5 AS a5, field6 AS a6, MSISDN AS a7, field8 AS a8, RETRY_COUNT AS a9 FROM table WHERE ((field3 <= $1) AND (field8 IS NULL)) ORDER BY field3 ASC LIMIT $2 OFFSET $3
2016-03-03 10:33:29.259 CET [56868] user@db LOG:  EXECUTE MESSAGE STATISTICS
2016-03-03 10:33:29.259 CET [56868] user@db DETAIL:  ! system usage stats:
        !       0.000006 elapsed 0.000000 user 0.000000 system sec
        !       [0.526408 user 0.112125 sys total]
        !       0/0 [0/0] filesystem blocks in/out
        !       0/0 [0/1281] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [3516/0] voluntary/involuntary context switches

Thank you

It seems that the values in the brackets refer to the raw usage/stats.  Note that the values outside the brackets are adjusted with reference to the last reset (See ResetUsage() above ShowUsage() in the link I shared).  The values inside the brackets are the values without that adjustment.


MySQL: How index speed up query execution I have read multiple articles about MySQL indexes. Some of the important pages are below:
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work

Its correct that query using indexes executes much faster because there is no need to traverse every records. But as a novice RDBMS guy, all other remaining columns are dependent on indeed column. In a table with thousands of records, system need to traverse the index first (one by one, in any defined order I don't know).

To avoid a big description, I am limiting the scope of this question as

How MySQL traverse indexes?
How indexes arrangement (B-trees,
hash,etc.) are determined?
What is the difference between traversing
rows one by one and traversing index column value?

Here you have a video on the basic inner structure and inner workings of indexes. I recommend you to watch it all.

Basically, indexes are ordered structures on disk (although they can be cached, and they normally will for better performance) that will allow certain operations to be done faster. In particular, in MySQL, B-tree/B+tree (the most common ones) will allow you:

Perform filtering faster, with conditions like a=3, where a is an indexed column. This is possible because you can locate values that are 3 with less read operations
Obtain ordered results (ORDER by a): as values in an index are ordered, in certain cases, there will be no need to reorder them with a sorting algorithm, as we are already reading them in the right order
Reading values without accessing the whole row (covering index): SELECT a ... WHERE a = 3; as the index is smaller, it is less amount of bytes read, reduced operations and usually cached, improving read performance
Speeding up certain operations, like max(a) or min(a): the optimizer just needs to read the first or the last value on the index structure

In the case of B-trees, (look at this slide), if it is looking for the value 1, it only needs to read the root node. As 1 is before 3, it knows it is on the left node. In one step it discarded 50% of the reads. Imagine the advantage with millions of nodes, it is exponentially (or actually, logarithmically) better. If all rows have to be read, then reading may not be faster, in fact, it may be even slower. That is why when the selectivity is low MySQL refuses to use an index and performs a FULL TABLE SCAN. However, even in some cases, performing a full index scan may be an advantage (if less data has to be read overall), but it won't be as effective as if we perform a filtering with high selectivity.

In the case of hash indexes, the advantage comes from the use of a hash table. This can be even faster in some cases, but it cannot be used in all cases (for example, for > or < comparisons). The major engines on MySQL (InnoDB, MyISAM) do not support it, but they use internally in some structures hash maps.

MySQL will not select the index type automatically, it is selected on creation time (ADD INDEX my_index (col_a, col_b) USING BTREE), but defaults to BTREE.

MySQL and other DBRMSs support other type of indexes, too, like R-Trees, Fulltext, Spatial, etc.


Copy SQL DB files/backups We have a task to copy 2TB of DB files/backups over LAN in a most efficient and speedy way. We are brainstorming amongst our team to come up with a best solution.

These files are multiple backups and compression is already used.

Can someone advise the best way to do this?

I can recommend to use Robocopy, it has the support of multi threaded copy. And it won't take ages to calculate the packages before.

Your syntax would be as follows:

robocopy "C:\SOURCE\Folder\folder" "D:\folder\destination" /mir /MT:XX

I use the mir option, since it will copy everything, meaning doing a perfect mirror. So you don't have to worry if your copy job stucks for any reason. You can easily run the same command again and it will only copy the missing files.
Be careful if you have hidden files in your folder, for example if you have the files placed directly on your Drive root folder. Than you will have problems copying the recycler ;)

MT Parameter
The Command /MT will run your copies with multiple threads. You can choose a number between 1 and 128, default is 8.
Please read : msdn Robocopy

Also one advice is to copy from the machine with more CPU Power since a copy job will alwas run on the CPU where you are currently logged in. In most cases I copy from the destination server, since it is probably not in use so far.


How to restore percona database from kubernetes? I got the backup works by setting these on the helm chart values (it works according to the documentation):
    google-cloud-storage-s3:
      type: s3
      s3:
        bucket: gcp_bucket
        credentialsSecret: google-cloud-storage-s3-backup
        region: us-west2
        endpointUrl: https://storage.googleapis.com/

It's uploaded successfully on the google cloud storage:

Then I tried to restore the backup:
gsutil -m cp -r \
  "gs://gcp_bucket/percona-2021-05-06-00:00:11-full.md5" \
  "gs://gcp_backup/percona-2021-05-06-00:00:11-full.sst_info/" \
  "gs://gcp_backup/percona-2021-05-06-00:00:11-full/" \
  .
| [1.1k/1.1k files][  2.7 GiB/  2.7 GiB] 100% Done 942.3 KiB/s ETA 00:00:00
Operation completed over 1.1k objects/2.7 GiB.

sudo apt install percona-server-server percona-xtrabackup-80

The documentation said:
service mysqld stop
rm -rf /var/lib/mysql/*
cat xtrabackup.stream | xbstream -x -C /var/lib/mysql # --> where is this from?
xtrabackup --prepare --target-dir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
service mysqld start

The problem is, I could not find any file named copy-backup.sh or xtrabackup.stream in the backup directories, is there other way to restore the backup, also all files compressed with lz4?

The copy-backup.sh can be found on the repo
Then following the commands on the documentation will produce xtrabackup.stream file.


Tips for using DML Statements I've recently began learning T-SQL and have found that there are some rules to using single table queries. For instance:

GROUP BY

If aggregate and non-aggregate expressions are used together in a SELECT statement, the GROUP BY clause must be used.

HAVING

The HAVING clause is generally used with GROUP BY

TOP

The TOP statement relies on the ORDER BY clause for logical precedence among rows

ORDER BY

Data returned when using an ORDER BY clause results in a cursor, whereas a table result is returned when not using the ORDER BY clause

I was curious if anyone else had any tips for learning the relationships among the different DML statements.

Thanks in advance!

As @Nick suggested, this question is too broad to have straight forward answer. Are you looking for how to write queries ?? Or you are looking for commonly used best practices for any query ?? Or something else ??
May be for starter you can google around this question, or look at some of the following sites,

http://blog.sqlauthority.com/2009/01/20/sql-server-rules-for-optimizining-any-query-best-practices-for-query-optimization/

http://www.databasejournal.com/features/mssql/article.php/3827091/T-SQL-Best-Practices.htm

http://www.sql-server-performance.com/2001/sql-best-practices/

http://www.codeproject.com/Articles/34372/Top-10-steps-to-optimize-data-access-in-SQL-Server


Mongodb performance: compound query vs moving to other collection My application uses mongodb to store order information. At some point orders are sent after which I need to generate some reporting of all orders in a timerange.

To give an idea of my order document:

{
    status: [type: String, range: 6 different statuses],
    timestamp: [date],
    customer:
        {
            id: [int, range: about 50]
            other non searchable indexed data
        }
    other non searchable indexed data
}

With a single collection I would query:

{ "status": "SENT", "customer.id" : ..., "timestamp" : {$gte: ..., $lt: ...}}

With an indexes like:

{ "status" : 1, "timestamp" : -1, "customer.id" : 1}
{ "status" : 1, "timestamp" : 1, "customer.id" : 1}

I would be able to query these rows. However I found that multiple criteria in a query would slow it down.

So my question is:

Is it better (performance wise, but also best practices) to use compound indexes with compound queries or to have multiple collections for different status (like order_sent, order_new, order_finished) when the database has millions of orders.

Or in other words, how much of a performance impact does extra criteria have on a correctly index query (and are the proposed indexes viable for this situation).

Since 2.6 MongoDB supports index intersection.

https://docs.mongodb.com/manual/core/index-intersection/

To my general experience your queries are not complex enough to be worried.

You should of course create indices for status (especially since it is a string), customer.id, and timestamp but leave the rest to the MongoDB internals. You can of course create a compound index too, and make some benchmarks to see for yourself using .hint() to force MongoDB to use a specific index.

https://docs.mongodb.com/manual/reference/method/cursor.hint/#cursor.hint

You should also know, that the ObjectId contains a timestamp corresponding to the document creation time already which comes in handy as there is always an index on _id.

https://docs.mongodb.com/manual/reference/method/ObjectId/#ObjectId


check constraint that has dynamic list I need to create a check constraint, the problem i face is that the list of values accepted by the constraint changes in the future (ex:now "red,green and blue" in the next month "red,green,blue and white"). How to do this ?

You should do this with a foreign key constraint.

You can create a check constraint with the following definition

CREATE TABLE T
(
Color varchar(10) CHECK (Color in ('red','green','blue'))
)

But there is no way of altering a check constraint definition without dropping it and recreating it (thus requiring all rows to be revalidated against the new definition)

  To modify a CHECK constraint, you must first delete the existing CHECK
  constraint and then re-create it with the new definition.

This is trivial to do with a Foreign Key constraint

CREATE TABLE Colors
  (
     Color VARCHAR(10) PRIMARY KEY
  )

INSERT INTO Colors
VALUES      ('red'),
            ('green'),
            ('blue')

CREATE TABLE T
  (
     Color VARCHAR(10) REFERENCES Colors
  )

Though I'd probably introduce a surrogate key to the Colors table rather than storing the string repeatedly in the main table.

I have come across the argument before that using a check constraint is somehow "more correct" than using foreign keys and a lookup table but the advantages of the lookup table to me are.

Easier and more efficient to add items to the list.
Easier to get a distinct list of allowable colours (e.g. to display in a listbox in your application)
Using a fixed length integer surrogate key can have performance advantages compared to a variable length string both in terms of reducing row size and avoiding fragmentation on updates.

NB: It is possible to have a check constraint reference a scalar UDF that in turn references a table but this approach should be avoided. It does not simulate a foreign key correctly (e.g. does not validate on DELETE FROM Colors)


MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken! So I wanted to test MySQL recovery from Innodb corruption.
file-per-table is enabled.
I have a few test db's on the MySQL instance.
I shutdown mysql.  Manually edited/added random characters to an .idb file within a DB.
... I wanted to test credibility of innodbchecksum, mysqlcheck, etc.

MySQL started but shut down right away with errors.
Only innodb-force-recovery=6 seemed to keep MySQL up.

I couldn't dump the corrupted DB at all.
I couldn't select the table. if I tried MySQL crashed again.
I also couldn't drop the table or database even.
error.log mentioned something about not being able to remove the folder.

I manually did an rm on the entire db folder.  edit: I understand it may be impossible to get the data back after wrecking the .idb file, I am only interested in bringing MySQL back to normal status.

MySQL seemed to have started but with errors everywhere in the log saying:

121122 17:34:01  InnoDB: Error: page 7 log sequence number 1 2040399532
InnoDB: is in the future! Current system log sequence number 0 8204.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files...

So my question is, how can this MySQL instance be recovered?
If an .idb file is corrupted due to disk error or such, is the entire MySQL instance hosed?
is there any recovery from this?

Edit: there must be a way of forcing drop a corrupted DB or even table without breaking the tablespace.

Following these steps probably offers a decent shot at making the MySQL instance happy again...

recreate the database's directory that you deleted
create an InnoDB table with the same exact column names and definitions and the same table name, but in a different schema (or on a different server)
copy only the .frm file for that table into the database's directory, not the .ibd file
be sure the file and directory permissions are correct
start MySQL normally (without innodb-force-recovery)
DROP TABLE database_name.table_name;

The server should then respond by removing the .frm file, removing the table from InnoDB's internal data dictionary, and return a simple warning that the .ibd file was missing.

You'll have to repeat the process for all of the tables, but if you have the schema on another machine (or identical tables in another schema on this one, already) you should be able to copy all the .frm files over in one shot, start up, then drop them all.

But don't try opening them, don't "USE database_name", and don't connect with a graphical client that tries to enumerate the tables.  Start the command line client with the --no-auto-rehash option so it doesn't try to enumerate them in the background, either.

This should let you drop the tables, at which point, you may be stable again.


Errors after installing mysql After installing mysql I get the following errors:

> 120105  6:02:49 [Warning] Setting lower_case_table_names=2 because
> file system for /usr/local/var/mysql/ is case insensitive mysqld:
> Table 'mysql.plugin' doesn't exist
> **120105  6:02:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.** 120105  6:02:49 InnoDB: The InnoDB
> memory heap is disabled 120105  6:02:49 InnoDB: Mutexes and rw_locks
> use GCC atomic builtins 120105  6:02:49 InnoDB: Compressed tables use
> zlib 1.2.5 120105  6:02:49 InnoDB: Initializing buffer pool, size =
> 128.0M 120105  6:02:49 InnoDB: Completed initialization of buffer pool 120105  6:02:49 InnoDB: highest supported file format is Barracuda.
> 120105  6:02:49  InnoDB: Waiting for the background threads to start
> 120105  6:02:50 InnoDB: 1.1.8 started; log sequence number 1595675
> **120105  6:02:50 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist**

I have tried to run mysql_upgrade and I get the following:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
FATAL ERROR: Upgrade failed

I have tried to re-install mysql. I get the same results.

I am wondering if someone can suggest some useful tricks to get this working.  I am using a mac running 10.7.2

Thanks again for your help in advance.

The problem I see is the error message that says

  Table 'mysql.plugin' doesn't exist
  **120105  6:02:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it

MySQL 5.1 introduced plugins. MySQL 5.0 and prior do not have plugins. My guess is that you had MySQL 5.0 running, you uninstalled MySQL 5.0, and tried to install either MySQL 5.1 or 5.5 and it complained about mysql.plugin being missing.

Try cleaning up the 5.0 install as follows

uninstall mysql 5.0
Move /usr/local/var/mysql to /usr/local/var/mysql_old
Install mysql 5.5

Give it a Try !!!


Permission in schema for user I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?

From the documentation (GRANT Schema Permissions):

GRANT SELECT, DELETE -- , etc.
   ON SCHEMA::schema_name
   TO user_name;

For certain permissions they need to be granted at both the database level and the schema level. For example, you can give a user CREATE TABLE permissions at the database level, but that doesn't just allow them to create tables in any schema. Here is an example you can work through:

-- create a server-level SQL auth login
CREATE LOGIN DouglasExample WITH PASSWORD = 'x',
  CHECK_POLICY = OFF;

-- create an empty database and add a schema
CREATE DATABASE Douglas;
GO
USE Douglas;
GO
CREATE SCHEMA foo;
GO

-- create a user linked to the login
CREATE USER DouglasExample FROM LOGIN DouglasExample;
GO

-- grant the user the ability to create tables
GRANT CREATE TABLE TO DouglasExample;
GO

-- grant them explicit permissions on the schema only
GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL
  ON SCHEMA::foo TO DouglasExample;
GO

-- now try to create tables in foo and in dbo
EXECUTE AS USER = N'DouglasExample';
GO
CREATE TABLE foo.what(id INT); -- succeeds
GO
CREATE TABLE dbo.who(id INT); -- fails, not in schema foo
-- CREATE TABLE who(id INT); would also fail with same error
-- unless DouglasExample had foo as their default_schema
GO
REVERT;
GO

The attempt in dbo fails with:

  Msg 2760, Level 16, State 1
  The specified schema name "dbo" either does not exist or you do not have permission to use it.

-- clean up:
USE master;
GO
DROP DATABASE Douglas;
GO
DROP LOGIN DouglasExample;

As a side note, please observe how important it is to always explicitly state the schema name when creating/referencing all objects.


Scheduled stored procedure on all databases in my server On SQL Server 2012 (not express edition) I'd like to schedule the execution of a stored procedure every hour.

On the same server I have lot of databases with the same structure, and I'm constantly adding new db.

How can I schedule the execution of the same stored procedure on all the databases (if this exists in the database)?

Use the better version of the undocumented sp_MSForEachDB to iterate through the databases in a SQL Server Agent job.

Or simply use a WHILE loop yourself on sys.database entries if you have a naming pattern.


In phpMyAdmin, how can I execute mysqldump query? I wanted to execute

mysqldump -u username -p –all-databases > dump.sql

However, I can't see the appropriate field in PHPMYADMIN. Where I can do that?

SQL error

The command you posted does not execute from within PHPMYADMIN

That is supposed to be executed in the OS Shell

There are many web pages that explain running a backup of MySQL with PHP

For example, if you go to How to backup your Mysql database with phpMyAdmin, you will learn how to do the backup starting from the Export tab as well as doing the mysqldump from the OS.


MongoDB - how to reduce numYields I have a MongoDB database with ~100GB data size. I ran a test with 300 threads and queries are all reads, no writes (except writes to db profiler I guess). I enabled database profiler to keep track of slow queries. I noticed that queries with high 'numYields' result in high 'millis'; likewise, queries with low numYields responded very fast in low millis.
90% of the queries ran very fast, in 1 ~ 2 ms, however, around 2% of queries end up in 60,000 ms or higher.
According to MongoDB doc:

numYields is a counter that reports the number of times the operation has yielded to allow other operations to complete.
Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete quickly while MongoDB reads in data for the yielding operation.

I understand a slow query was trying to read data from disk, while yielding to other queries that already have data in memory. However, if this result in 60,000ms for that particular query, it becomes unreasonable.
Perhaps there's a way to limit numYields? Or perhaps try to fit everything to memory? Any suggestions?

This is a common misconception, i.e. that yields are somehow causing the slowness. In fact they are a symptom, not a cause.  Even if there is no lock contention that requires a yield (writes basically), the queries still yield when they have to page from disk.  They then re-acquire the lock when a certain amount of paging is done and look to yield again if more paging is needed (repeat until complete). If there is no lock contention from writes, then this is all pretty much instantaneous and does not add to the overall execution time.

If a query yields a lot, then it was hitting disk a lot, and that is the cause of the slowness - the disk access.  Hence, numYields is just a way to infer that it was indeed paging to disk that caused the query to be slow.  If you want those queries to be fast, then you need to have that data set in memory, and have enough memory for it to stay there long terms and not be evicted.

Note: by default the kernel will use LRU to decide what gets evicted, so the likely candidates for slowness are queries on (large) parts of your data set that are not accessed very often.

There is no way to limit numYields, and it wouldn't really make sense to do so, but yes the remedy is to identify the data being addressed by those slow queries and make it fit into memory (note: the first query on any data will still be slow unless you pre-heat in some way, the second query will be in memory and fast).


The password is expired -- implicitly -- after 360 days -- in MySQL Server 5.7 versions prior to 5.7.11.

  From MySQL 5.7.4 to 5.7.10, the default default_password_lifetime  value is 360 (passwords must be changed approximately once per year). For those versions, be aware that, if you make no changes to the default_password_lifetime variable or to individual user accounts, all user passwords will expire after 360 days, and all user accounts will start running in restricted mode when this happens. Clients (which are effectively users) connecting to the server will then get an error indicating that the password must be changed: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

  https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

Temporary workaround should be this:

mysql> SET GLOBAL DEFAULT_PASSWORD_LIFETIME = 0;

Better, add to the mysqld section of my.cnf:

default_password_lifetime = 0

...or another appropriate value for your site, then restart the server.

Even better still... upgrade.  There were numerous bugs in early GA versions of 5.7, particularly 5.7.9, which was the first GA version of the release series.

Of course, you should be able to modify this account using ALTER USER ... PASSWORD EXPIRE NEVER which is apparently not the same as the NULL value shown in the table.


Having the backup file, how to work out the total disk space required to do the restore? I have a nice query that gives me the date of last full backup, size and location, even when I backup to multiple backup files.

what I don't have is an estimate of the total backup size (for each of the DB files).

Basically, how much disk space in each drive I will need in order to do this restore?

I have been trying with

RESTORE FILELISTONLY FROM DISK=N'C:\SQL Server\BACKUP\my_backup_FULL_20190324_190109.bak'

but I could not work out the total disk space required for the restored-to-be database.

I got the size from the restore filelistonly above however, the size of the original db in disk is the following:

I just found out:

If I think you want this in a SELECT statement for multiple databases, the below code is a good start

CREATE TABLE #FileListHeaders (
     LogicalName    nvarchar(128)
    ,PhysicalName   nvarchar(260)
    ,[Type] char(1)
    ,FileGroupName  nvarchar(128) NULL
    ,Size   numeric(20,0)
    ,MaxSize    numeric(20,0)
    ,FileID bigint
    ,CreateLSN  numeric(25,0)
    ,DropLSN    numeric(25,0) NULL
    ,UniqueID   uniqueidentifier
    ,ReadOnlyLSN    numeric(25,0) NULL
    ,ReadWriteLSN   numeric(25,0) NULL
    ,BackupSizeInBytes  bigint
    ,SourceBlockSize    int
    ,FileGroupID    int
    ,LogGroupGUID   uniqueidentifier NULL
    ,DifferentialBaseLSN    numeric(25,0) NULL
    ,DifferentialBaseGUID   uniqueidentifier NULL
    ,IsReadOnly bit
    ,IsPresent  BIT
    ,TDEThumbprint VARBINARY(32)
    ,SnapshotURL NVARCHAR(360)
)
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = ''C:\Program Files\Microsoft SQL Server\
       MSSQL14.MSSQLSERVER\MSSQL\Backup\test.bak''')
SELECT Size/1024/1024 AS [size in mb],* FROM #FileListHeaders

Got the script from here

One caveat is the created table differs in various versions of sql server in the above link he handles that also. change the script according to you.


Alternative of DBCC CHECKDB I am running DBCC CHECKDB on one of my database but its taking very very long time. I have some questions please help me.

Is there any alternative of it that can check my database integrity?

Should I wait to finish it?

The only alternative to performing consistency checking (DBCC CHECKDB) is to leave your data open to the possibility of corruption and data loss.

There are some ways to reduce the impact that CHECKDB has on your system. The best way to do this by far is to run your checks offline on a copy of your live systems. This should be done by restoring a full backup of your database onto another system and then running DBCC CHECKDB there.
This is good for two reasons:

It off loads the IO and perfomance issues your are experiencing onto another machine
It tests your backups restore successfully.

If you can't afford to have a secondary server for testing backups and running DBCC CHECKDB then you may have to break the check down and run it against a few individual tables each night to spread the load out accross the week. You can do this with the CHECKTABLE statement. Another way to break the job into smaller parts would be to use CHECKFILEGROUP to check each file group, only makes sense if you have multiple file groups.

You can also try trace flags 2549, 2562, and 2566. For more information on the trace flags, the methods mentioned above and more read Aaron Bertrand's post on the subject here


From mysql.com:

  With strict mode
  disabled, invalid dates such as '2004-04-31' are converted to
  '0000-00-00' and a warning is generated. With strict mode enabled,
  invalid dates generate an error. To permit such dates, enable
  ALLOW_INVALID_DATES.

To disable it you have 2 options.

Via SQL: execute SET @@global.sql_mode = ''; query.
Via my.cnf: change sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" to sql-mode = "". Restart MySQL.


Locking on database from SELECT INTO #TempTable We had an issue on our live DB, where a query executed from the test instance, that referenced the live instance caused blocks due to it showing as 'suspended', which caused user requests to back up, all due to this one query.

Looking at the query, there is one request at the start that references the live DB:

SELECT      [Columns]
INTO        #TempTable
FROM        [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Users                      AS  sv
RIGHT JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company                    AS  co
INNER JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company_Marketing          AS  cm  ON co.co_ref        = cm.cmk_co_ref
INNER JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Property                   AS  pr  ON co.co_pr_ref     = pr.pr_ref
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company_Types              AS  ct  ON co.co_cot_id     = ct.cot_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Users                      AS  cb  ON cm.cmk_called_by = cb.us_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Marketing_Disciplines      AS  md  ON cm.cmk_mdi_id    = md.mdi_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].REGIONS                    AS  rg  ON sv.us_reg_id     = rg.reg_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].REGIONS                    AS  rg1 ON CB.us_reg_id     = rg1.reg_id
WHERE       cm.cmk_appointment > '2017-01-01'
AND         md.mdi_mdg_id <> 2
ORDER BY    cm.cmk_appointment

This was being run against a test instance, but referencing live (there is a linked server). Changing it to run on the correct live instance means that it ran in a matter of seconds, and no blocking issues.

We are tidying up the query, as it is far from efficient anyway, but, what I am wanting to understand is, how does SELECT INTO behave in terms of blocks/locks? Would the query above mean that blocks are present on the tables in the query whilst #TempTable is populated?

Thanks for your assistance.

I would expect the same behavior with or without the INTO. Remote queries run with default READ COMMITTED isolation level behavior acquire and release shared locks during execution just like local queries. Those locks can block writers of the remote tables, especially with poorly written queries and if shared table-level locks are held for the duration of the query.

Separately, the local SELECT INTO query consists of two separate transactions when run in autocommit mode. This is done to avoid holding long-term locks on the system catalog objects which could otherwise block object creation by other sessions. Creation of the new table is done in the first transaction and then the table is loaded using the remote query source in the second transaction.


How do graph databases differ from older navigational (hierarchical/network) databases? I'm trying to improve my knowledge of graph databases, in particular DGraph. Are graph DBMSs just a new generation of pre-relational hierarchical/network model DBMSs, or is there something new at a low level in the current generation of graph DBMSs (neo4j, etc.)? For example, the 60's-era CODASYL stored records in linked lists of free-form records. Isn't this just what current graph DB engines are doing?

My question isn't about things external to the fundamental way graph DBMSs function, like query language, replication, etc. I get that those areas have improved significantly.

Graph databases are largely irrelevant. What matters is the graph processing engines that use them. For that a dedicated graph database is not necessary: graphs can be represented in any sorts of stores: relational database, "nosql" databases (HBase, ...). Or even flat file structures. Some graph processing engines include their own dedicated graph store (that is the case for Neo4J and Graph). Others (Oracle Property Graph) let you use any storage you like.

In terms of processing capabilities, graph processing is very different from what was possible with the old Codasyl/Network databases. Graph processing lets you do graph analytics: finding influencers in social networks (page rank, closeness centrality, betweenness centrality ...), community detection, shortest paths, clustering ... They also let you traverse graphs using specialized languages such as PGQL or Cypher (for Neo4J). This is used to detect cycles: very useful for analyzing financial fraud and money laundering. Graph techniques have been used to analyze the Panama Papers for example. Same techniques are commonly used to analyze dependencies in complex processes, like car manufacturing or IT infrastructures. Note that work is under way to extend the SQL language with graph traversal expressions.

I don't know much about DGraph. A look at the documentation lets me believe that it does not provide any of the above: no graph analytics, and no actual graph traversal language.  That may be because it actually implements graphs as RDF (semantic) graphs where data is abstracted as triples. RDF graphs can be constrained using ontologies that allow new triples to be inferred. RDF triple stores are generally queried using the SparQL language, but DGraph does not seem to support that either.

It would be interesting to know what sort of use case you have in mind that you think would be better solved by graph techniques, rather than relational techniques.


Merge Replication: Service Account Password Change Is it possible to change the password of a SQL Server service account that is used for not only on the publisher server but the 6 subscriber servers as well without seriously affecting merge replication? I'd like to avoid (or at least be forewarned of) any potential issues because replication is not my strong suit and I hate troubleshooting it.

If the same username and password is to be used on all servers, you will need to make that change on all machines, or rather the replication agent jobs.

If you change the password but miss one of the subscribers, replication will not break. However, it will fail with login errors when the subscribers attempt to connect to the publisher and the password hasn't been updated on the subscriber. As soon as you update the passwords on all subscribers, they will run again without error.

This is a typical scenario for any password change, regardless of if replication is involved or not.

It may be prudent to consider updating your security policy for replication/service account usage and deploy dedicated accounts for this topology instead of using the service account multiple times. This is a security best practice and would allow you to increase the security of your systems.


Increased RAM, Worse Performance Setup:

Windows Server 2008 R2
SQL Server 2008 R2 SP1
240GB RAM
TempDB is 8x16GB data files w/out auto-grow (128GB total)
Physical/Stand-alone Server

This server is used for ETL processing. We just installed more RAM in this server for a total of 240GB RAM. SQL Server services are the only real things running.

The memory shows up fine in the BIOS, OpenManage and Windows.

If I configure SQL Server to use a Min/Max of 70/100GB memory, we have no issues. However, once I increase that to 120/150GB, I get the following error when I run one of our ETL processes:

  Could not allocate space for object '<temporary system object:
  422234507706368>' in database 'tempdb' because the 'PRIMARY'
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup. (Msg 1105, State 2, Procedure Unknown, Line 1)

We have never run into this issue prior to changing the memory configuration. After re-configuring back to the original 70/100GB, we do not receive this error.

Things I've tried:

Set the TempDB data files to auto-grow. This simply results in the files auto-growing until disk-capacity is reached and then fails.
Add more TempDB data files. Same error as shown.
Increase the TempDB size to 8x32GB (256GB total)

I'm at a loss as to what could be causing this problem.

Thanks to everyone for your help.

After pouring through some execution plans, it turns out there is a JOIN that is being processed differently based on the amount of RAM available. With less RAM it evaluates it with a Hash; with more RAM, it uses a series of Merge Joins.

So basically it came down to poorly written T-SQL, which I am presently refactoring.


what does that mean? COMMAND [PeriodicTaskRunner] task: UnusedLockCleaner took: 273ms With mloginfo I found a strange log entry. Didn't find anything about it in MongoDB docs and other related sites.

$ mloginfo mongodb.log  --distinct
     source: mongodb.log
       host: example
      start: 2016 Aug 25 14:57:57.630
        end: 2016 Aug 26 08:02:05.751
date format: iso8601-local
     length: 572308
     binary: mongod
    version: 3.2.8
    storage: wiredTiger

DISTINCT

   28069    connection accepted from ... # ... ( ... now open)
   27587    end connection ... ( ... now open)
      15    exception: ... on:
      10    serverStatus was very slow:
       4    task: ... took: ... ms
       3    killcursors: found ... of
       3    SocketException handling request, closing client connection:
       1    git version:
       1    waiting for connections on port

what is that exactly? What is a PeriodicTaskRunner and what does UnusedLockCleaner do? Any actions required from DBA?

2016-08-25T21:03:38.449+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 105ms
2016-08-26T00:21:40.285+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 184ms
2016-08-26T01:25:40.940+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 273ms
2016-08-26T01:55:41.357+0200 I COMMAND  [PeriodicTaskRunner] task: UnusedLockCleaner took: 234ms

The nice part about MongoDB is that it is open source, so your answer is in  the code, starting with comments about UnusedLockCleaner:

  Periodically purges unused lock buckets. The first time the lock is
  used again after cleanup it needs to be allocated, and similarly,
  every first use by a client for an intent mode may need to create a
  partitioned lock head. Cleanup is done roughtly once a minute.

Then taking a look at the lock_manager code we get more info about the cleanup function:

  Iterates through all buckets and deletes all locks, which have no requests on them. This
  call is kind of expensive and should only be used for reducing the memory footprint of
  the lock manager.

Basically it looks like some background maintenance being done by the database to clear out unused locks and keep the memory usage of the lock manager under control. Nothing to be concerned about, since if the database is busy and the locks are in use they won't be cleaned up. If you are seeing (reproducible) issues with it, then it would be best to open a ticket to debug with the developers, since this would not be intended.


Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS.

For this, we have taken backup from physical server and restored in RDS and setup replication to sync the data.

But found the time is getting changed to UTC. We found the time format in RDS is UTC, so we changed the time format of time_zone to PST in parameter group. But we are not able to change the default time_zone parameter.

Due to this, the time which is coming from master server is getting updated to UTC and storing in database which is 8hrs more than actual time.

Due to constraints, we are not able to use AWS DMS for replicating data. How can we change the default time_zone or fix the time zone issue in RDS.

I was using MySQL replication with Mixed mode replication which captures the data with timestamp and timezone.

Later, I changed the MySQL replication to Row based replication which captures the data with time. So what ever the time format it is, it just send the insert/update statements with time, which solved my problem.


What is the difference between using varchar(100) and varchar(255)? I want to know difference between varchar(100) and varchar(255) in terms of memory usage and programming problems of using more memory.

The difference is going to be in how much data you are allowed to store there.  The memory and disk space will depend on how much is actually stored, not what limits you put in place.

Most db's (MySQL included) store varchar fields with a length identifier and then a variable length data string.  This means that the size of the field on disk goes up more or less linearly with the actual data stored.  Similarly in memory, you aren't going to be allocating a large amount of memory for a small string unless it is padded (so varchar uses a lot less memory than char does regardless of underlying storage of the latter).  In fact some db's like PostgreSQL store VARCHAR() and TEXT fields the same (and there is a 1GB compressed size limit there) and try to optimize storage based on actual requirements of the data being used.

In general these decisions should be made based on what your data retention and collection needs are, not based on the performance considerations.  The performance can be tuned and that's the lovely thing about an RDBMS.


Does a zero value in innodb_index_stats.rows_per_key mean the index is over-specified? I'm taking care of an elderly 5.5.41 Mysql database and I was taking a look at the indexes with the following query:

select i.table_name, i.index_name, i.rows_per_key,
    coalesce(t.rows_read, 0) as tbl_rows_read,
    coalesce(s.rows_read, 0) as idx_rows_read
from information_schema.innodb_index_stats i
left join information_schema.table_statistics t using (table_schema, table_name)
left join information_schema.index_statistics s using (table_schema, table_name, index_name)
where i.table_schema = 'my_schema'
order by i.table_name, coalesce(s.rows_read, 0) desc, i.index_name;

For the rows_per_key column, some of the tables return values like 9234487, 9234487, 4273, 2, 0, 0. I take this to mean that once a query  specifies the first four columns in a SELECT, whether or not the fifth and sixth columns of the index are specified, the index selectivity does not improve.

The only time it could be useful is if only the fields in the index are sufficient to answer the query, then the planner does not need to consider the table at all.

But apart from that, if I redefine the index to contain only first for fields, I won't lose in performance?

By extension, since the index is then held in fewer pages, it is even more likely to be buffered in its entirety.

Comparing these:

INDEX(a,b,c,d,e)
INDEX(a,b,c,d)

Each can handle the same queries (assuming that e is not in the query).
If exactly those 5 columns exist in the query, then the longer one has an advantage of being "covering".  This might give you a 2x speedup.
Yes the longer one takes more disk space.  This impacts the speed of it only slightly.  (Obviously, the impact depends on the size of e.)
There is no hard-and-fast rule saying you should stop with 4 versus have all 5.
My Rule of Thumb says "don't go past 5".


Drawbacks to add clustered keys I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all.

At a very high level I understand the performance limitation of not having a clustered index. Perhaps more so I understand it isn't best practice, but before I aggressively start converting non-clustered primary keys to clustered ones, are there any caveats I should be aware of when converting these indexes?

The only one I can think of would be the time it would take to update an index on a large table, but again these are relatively small. Perhaps files & partitions, but I only have a single partition.

For reference, this is a 2008-compatible SQL Server database running on SQL Server 2016. This is in our primary OLTP system (these table are not involved in warehousing & ETL).

Back in the old days (as best I recall), tables that small theoretically didn't need indexes at all. However, clustered indexes have a couple of other advantages.

Better space management - tables without a clustered index basically don't release disk space, even when rows are deleted.
Less space needed - Since a clustered index is built into the table's structure, replacing a non-clustered index with a clustered index usually frees up much of the space the non-clustered index took up.

And, of course - with 500 row tables, both of those things should be too little to really notice.

On the con side, it's possible that your non-clustered index managed to be a covering index for some queries, so the actual table didn't need to be touched to get results. So, some queries could actually be slower.

And, of course - with 500 row tables, that should be too little to really notice.

I agree with BradC's comment, however - if the table does start to grow much bigger than expected, I'd rather have a clustered index on it than not. And, if you enforce a standard of putting a clustered index on every table, then you're less likely to wind up with a heap with 400MB of data, and 300MB of unused space....


SSIS PL/SQL task hangs with message "Multiple-step OLE DB operation generated errors." I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database.

The Execute SQL Task (named "SQL Update MY_STATUS flag") is using an OLE DB Connection Manager with a "Native OLE DB\Oracle Provider for OLE DB" Provider. All other task settings are default values.

The query is simple:

UPDATE MY_TABLE
SET MY_STATUS = 1
WHERE MY_STATUS = 0

(Note: This query succeeds when run in SQL Developer with a semicolon terminator. Here the terminator is omitted, because if a semicolon is included, an invalid character error is generated by SSIS.)

When I start debugging, the task begins execution and then hangs indefinitely, the package does not complete execution.

The debug output window shows this message:

  Warning: 0x0 at SQL Update MY_STATUS flag: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if
  available. No work was done.

(The Task behaves the same with the query configured as Direct Input, or as a Variable.)

What does this "Multiple-step" warning mean in this context? What must I do to allow the Execute SQL Task to succeed?

The reason the task was hanging was that I had run a separate update statement against MY_TABLE in a SQL Developer client window, and had not completed the transaction. Because the transaction was still open, the Execute SQL Task could not complete. Committing the transaction in SQL Developer allowed the SSIS package to complete, however, it still generated the same Warning message.

It appears the Warning message is harmless, though I still don't know how to interpret it.

Attention Microsoft SQL Server DBAs! The SQL Developer Oracle query window DOES NOT work like SQL Server Management Studio! DML queries are not implicit transactions, so and INSERT, UPDATE, OR DELETE statement leaves an open transaction unless you click the COMMIT or ROLLBACK button on the toolbar. Learn from my mistake.


What to do if all columns should be queried I'm currently developing an application which saves the progression of a player during an online game. Which means, I want to store for example his score, played time, Flag Captures etc. All in all there are 15-20 columns for different types of data, most of them are integers.

Now I want to sort each column to create statistics for player-ranking, for example:
SELECT players.UserName AS Name, Score FROM player_data JOIN players USING(UserID) ORDER BY Score DESC LIMIT 1, 20.
It should not be limited to query only "Score", every other existing columns should be queried in this way.

Is there any other way except to create indexes on all columns to speed up such queries? Or are there any other ways for optimization?
I'm fairly new to databases but as far as I read, creating many indexes will slow down update and insert-statements, as well as increasing the database size. Obviously I want small database size and fast queries.

If there are only a thousand rows, the query will run fast enough without a suitable index.

If you have a million rows, you really need an index.

Also, this might work faster in some situations:

SELECT u.UserName AS Name,
       d.Score
    FROM ( SELECT UserID, Score
               FROM PlayerData
               ORDER BY Score DESC
               LIMIT 20
         ) AS d
    JOIN Players AS u  USING (UserID);

Or...

SELECT ( SELECT UserName FROM players WHERE UserID = d.UserID ) AS Name,
       Score
    FROM PlayerData
    ORDER BY Score DESC
    LIMIT 20

(I removed the OFFSET 1 in the presumption that you don't want to skip the highest score.)


help understanding galera consistency (certification-based replication) Galera uses a certification-based replication to achieve strong consistency.

While doing a transaction after doing a COMMIT the other nodes will certify it and if is ok it will be successfully committed.

But what happens in the case of losing a node that certified the commit but still fetching the data, for example when writing a big blob?

From my understanding the certification process is synchronous but the data transfer is asynchronous, meaning that the client/user may think his data is stored on all the nodes after running successfully a COMMIT but while in reality is happening in the backend is that data may be still synchronizing among the nodes and in case of a network outage and depending on the type of transaction the node could be in a conflict, correct?

If this is the case, how could the cluster be configured to self-heal from possible conflicts or wondering if there is a way to achieve strong consistency even if it would impact the return time for every COMMIT since it should only be successful after the data been full transmitted on all nodes.

But what happens in the case of losing a node that certified the commit but still fetching the data, for example when writing a big blob?

This is no different to losing any other node. If the node crashes, it will try to re-join the cluster next time it comes up. A donor node will be found, and preferably IST (incremental state transfer) or alternatively SST (state snapshot transfer) will bring the node to the right state.

  From my understanding the certification process is synchronous but the data transfer is asynchronous, meaning that the client/user may think his data is stored on all the nodes after running successfully a COMMIT but while in reality is happening in the backend is that data may be still synchronizing among the nodes and in case of a network outage and depending on the type of transaction the node could be in a conflict, correct?

You are right that the data transfer is asynchronous and there is a short time while the node state is not identical. This isn't exactly a conflict, just that the other nodes are behind. They will catch up soon enough.

  If this is the case, how could the cluster be configured to self-heal from possible conflicts or wondering if there is a way to achieve strong consistency even if it would impact the return time for every COMMIT since it should only be successful after the data been full transmitted on all nodes.

Yes, it does self-heal through ISTs and SSTs.

There are two ways to be sure that you're reading the most up-to-date version of the data: If you always write to only one node (e.g. because of using a DB proxy with a read-write splitter), then you can make sure to also read from that same node. The other option is to set the session variable wsrep_sync_wait=1 - see Achieving Read-After-Write Semantics With Galera.


Limit the number of results but show how much would be returned I have a big problem in T-SQL with large amount of data where I need to limit number of returned rows to 200K but also to show the number of rows that would be returned. It is needed to be done under 10 min.

At the end for the result I am joining 3 tables which has 10'sK, 100'sK, or millions of rows in various combinations, depending on given parameters. I am using just several rows from each table and need the exact number of distinct rows that are returned.

When I place data into a temp table, writing it to disk takes too long (hours), so I am now trying to run the query twice. First time just for counting and second time for returning the result set without writing it to a temp table. This is because if I have more than 200K rows, than it can take too long otherwise.

I am wondering if COUNT ( DISTINCT [field 1] + ... + [field n] ) is going to take too much resources. How can I make this run faster:

DECLARE @count INT = ( SELECT COUNT( DISTINCT [field 1] + ... + [field n] )
    FROM ( #tempDC dc WITH (NOLOCK)
            JOIN #tempUID u WITH (NOLOCK) ON dc.[hashUFI] = u.[hashUFI]
                AND dc.iId IN ( '', u.iId ) )
        LEFT JOIN #rLog ON u.[hashUFI] = #rLog.[hashUFI]
            AND #rlog.iId IN ( '',  u.iId )
            AND #rLog.docId = dc.docId
    WHERE dc.rangeStart <= dc.rangeEnd AND u.rangeStart <= u.rangeEnd
        AND u.rangeStart <= dc.rangeEnd AND u.rangeEnd >= dc.rangeStart
)

I have indexes on all the hashUFI fields and also on all the pairs (rangeStart, rangeEnd).

This is just a problematic snippet of a much larger procedure (around 2000 lines of code), which is taking data from some audit tables.

Are you sure you even need the left join to get a good count?
Are you using values from #rLog in the distinct?
And I would go with , distinct not +

this line is different

ON dc.[hashUFI] = #rLog.[hashUFI]

it might help

SELECT COUNT( DISTINCT [field 1] + [field n] )
  FROM #tempDC dc WITH (NOLOCK)
  JOIN #tempUID u WITH (NOLOCK)
        ON dc.[hashUFI] = u.[hashUFI]
       AND dc.iId IN ( '', u.iId )
       AND dc.rangeStart <= dc.rangeEnd AND u.rangeStart <= u.rangeEnd
       AND u.rangeStart <= dc.rangeEnd AND u.rangeEnd >= dc.rangeStart
  -- try without the left join and see if the number is different
  LEFT JOIN #rLog
        ON dc.[hashUFI] = #rLog.[hashUFI]
       AND dc.docId     = #rLog.docId
       AND #rlog.iId IN ( '',  u.iId )


Performance problem after migration from SQL Server 2005 to 2012 Last Friday, I migrated a SQL Server instance from 2005 Enterprise Edition to a brand new Windows Server 2012 server with SQL Server 2012 Enterprise Edition installed. Since then, users have started to complain about the application performance.

Here is some information about the server:

Virtual server on VMWare 5.5
4 vCPU and 24 Gb RAM. On the previous configuration, 10 Gb was necessary but the tempdb database was smaller than what I set (almost 6Gb)
I now have set the maximum memory target to 22 Gb and tempdb is totally in Buffer Pool
After migration (performed using Database Mirroring), I ran update statistics, index rebuild, and other maintenance commands

I don't know where to find answers. If everything except the SQL Server instance is the same (VM, Disks, etc) then, to me, the problem is in SQL Server configuration, but where? I have tried some changes but none of them seem to have been useful.

Do you have any ideas?

Additional information from comments

I ran sp_Blitz which gave me some information, but nothing new from SQL 2005 (slow storage for instance)
The memory consumption (20Gb + ) is mostly due to tempdb (6Gb) and the remainings are mostly taken by an application database
PLE flows and sometimes goes down to 150. At the beginning of the day, most of the data is out of the cache
The max memory setting was 10Gb on the previous server
We use Idera diagnostic manager
The max server memory is set to twice the value it was on the previous server.
The page life expectancy drops below 150 at least twice a day

sp_BlitzIndex analysis

I started an analysis with sp_BlitzIndex and in addition to poorly written code, it showed this:

Aggressive Indexes: Total lock wait time > 5 minutes (row + page)

dbo.TABLE1.PK_TABLE1 (1):
Row lock waits: 3,591; total duration: 10 minutes; avg duration: 0 seconds;
Page lock waits: 23; total duration: 15 seconds; avg duration: 0 seconds;
Lock escalation attempts: 510,489; Actual Escalations: 1.

dbo.TABLE1.I_TABLE1_CNTTYPE_CATEGORY_IS_CURRENT (85):
Row lock waits: 155; total duration: 48 seconds; avg duration: 0 seconds;
Page lock waits: 129; total duration: 8 minutes; avg duration: 4 seconds;
Lock escalation attempts: 29,423; Actual Escalations: 4,951.

Would just an additional index creation do some changes in that phenomenon?

sp_configure

I've compared the output of sp_configure as requested. Here are the differences:

Config                          Old     New
Blocked process threshold       0       120
Maximum Degree of parallelism   0       4
Maximum Memory (MB)             10240   22000

The power option is already at High Performance. I set the memory back to 10 Gb with this command:

CHECKPOINT ;
DBCC DROPCLEANBUFFERS ;
EXEC sys.sp_configure N'max server memory (MB)', N'10240'
GO
RECONFIGURE WITH OVERRIDE
GO

After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.

Analysis of the Diagnostic Manager CPU statistics Report

The CPU statistics reports :

an average SQL compilation of 500
an average SQL Recompilation of 120
Up to 10 lock waits per minute, 5 on average
And mostly, a Table Lock Escalation which is on average of 40.

I've already set the Optimize for adhoc workload server setting and even "forced parametrization" for the most used user database.

So far, nobody told me there have been performance improvements. As it's a database I got back and was not managed by the DBA team, we don't have the background to check if it came back to a normal situation or not...

I will wait for some time and see whether it's ok for now or not.
Thank you all for your help !

The most likely explanation is that your queries are being executed using different execution plans on the new server, due to changes in statistics, index size and density, and configuration settings.

The configuration settings that have the biggest impact on query optimizer execution plan choices are:

max server memory
max degree of parallelism
cost threshold for parallelism

The question notes that memory has doubled on the new installation, so this is very likely to be the major factor. Intuitively, we might expect more memory to always improve performance, but this is not always the case because the optimizer may start to favour plans with more hashing, sorting, and scans instead of nested loops and index seeks.

For more information, see my previous answer:

difference in execution plans on UAT and PROD server

A setting of 20GB would not normally be large enough to justify setting startup trace flag 2335 (as referenced in that answer), but only testing would prove that one way or the other.

As a potential quick fix, you might consider setting max server memory back to the previous value, while you work on identifying query plans that regressed on the new server, and correcting the underlying causes using normal tuning methodology. I apologise if that sounds generic and hand-wavy, but the reality is that poor performance has very many possible causes, and there are established ways to identify and correct most common ones. The goal of my answer here is to get your system back somewhere close to where you were on 2005.

  After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.

The tempdb database may have grown previously to accommodate sorts and hashes that ended up spilling from memory; it may not need to be so large now. The tempdb database does not use memory any differently from any other database. PLE "hardly at 490" makes no sense as an English sentence.

The important point of setting max server memory back to 10GB was to encourage execution plans similar to the 2005 installation, thereby returning performance to acceptable levels. You don't say if it has helped or not. It is likely you need local professional support at this stage; I think we have reached the limit of what can reasonably be done in Q & A format.


More efficient to do SELECT and compare in Java or DELETE and INSERT I am hitting a REST API to get data from a service. I transform this data and store it in a database. I will have to do this on some interval, 15 minutes, and then make sure this database has latest information.
I am doing this in a Java program. I am wondering if it would be better, after I have queried all data, to do:

SELECT statements and compare vs transformed data and do UPDATEs (DELETE all associated records to what was changed and INSERT new)

OR

DELETE ALL and INSERT ALL every time.

Option 1 has potential to be a lot less transactions, guaranteed SELECT on all records because we are comparing, but potentially not a lot of UPDATEs since I don't expect data to be changing much. But it has downside of doing comparisons on all records to detect a change

I am planning on doing this using Spring Boot, JPA layer and possibly postgres

Neither of your proposals can be considered efficient. MERGE was introduced a long time ago (SQL99 or SQL2003) and will outperform both of your alternatives:

MERGE INTO T as x
USING (
    VALUES (?, ...., ?)
) as y (c1, c2, ...,cn)
    ON x.<key> = y.<key>
WHEN MATCHED AND (x.c1, x.c2, x....) <> (y.c1,y.c2, y...) THEN
    UPDATE (...)
    VALUES (y..)
WHEN NOT MATCHED THEN
    INSERT (...)
    VALUES (...)


Query on Shrinking Table - Oracle I'm trying to shrink a LOB whose owner is TEST and the tablespace it resides is on USERS.

EDIT: Currently, the default tablespace for TEST schema is tablespace called TESTTBL.

I have enabled row movement on the table.

If I try to shrink the particular LOB using:

ALTER TABLE test MODIFY LOB (image) (SHRINK SPACE);

...will it shrink and release space, becuase the schema TEST has default tablespace TESTTBL and data resides in USERS.

I assume you have a Basicfile LOB, because shrink is not supported with Securefile LOBs. The segment is not being moved, so the default tablespace and the tablespace of the segment is not relevant now. So a simple test is:

SQL> create table t1(c1 clob) lob (c1) store as basicfile;

Table created.

SQL> insert into t1 select lpad('X', 4000) from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from user_segments where segment_name =
     (select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');

BYTES/1024/1024
---------------
             80

SQL> alter table t1 enable row movement;

Table altered.

SQL> delete from t1 where rownum <= 5000;

5000 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t1 modify lob (c1) (shrink space);

Table altered.

And the LOB segment became half the size after the shrink operation:

SQL> select bytes/1024/1024 from user_segments where segment_name =
     (select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');

BYTES/1024/1024
---------------
        39.8125


Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario I know it is often asking question. But I can't find answer for my scenario with specific condition.
We use MySQL 5.5

We have always(I am sure)

innodb_file_per_table = ON

option in our mysql config. So I drop big table with confidence that file that represent this table will be deleted and disk space will be freed immediately, seems to be this file was deleted(this file not exist in DB directory in datadir, all other DB tables files exist), but in munin(disk usage) graph I don't see that disk space get free(my bad that I didn't notice disk usage before dropping table). Also ibdata1 seems to be is quite big for this situation(~ 200 GB).

There is way to get some additional info about old table (that was dropped) ?
There is some way to reduce disks usage without dumping and then restoring DB, maybe create new table with old_name(that was dropped) and try to make ALTERING ?

UPDATE:

1) ~100-110GB, or 20% of total disk space(ssd disk)

2) files_  | 124151382016

3) 10 tables, but only 1 is big table

4) Prehistory: we had big table(busy, inserts/updates/reads) that continue growing up, but some data in this table became old, and must be deleted, so we run delete queries in infinity loop( DELETE FROM files WHERE condition LIMIT 1000-100000, I don’t know how much rows were deleted with this method, but Innodb_rows_deleted was approx. ~600kk, and Innodb_rows_inserted nearly 1 billion or so) we deleting at night when load is very low — after we calculate that deleting takes too much time, so we use persona pt-archiver to move(only data that we need) from old table to new table(files_), and then DROP old table.

Yes, old one table had key constraints to separate table — “ON DELETE CASCADE ON UPDATE CASCADE”(in new table we have not constraints), also was in old table(and in current table)UNIQUE KEY on some table field.
I am also now making backup with xtrabackup of current db to check what takes much space in ibdata1 file with innodb_space app. Seems to be only 1 possible way — to make logical backup, and then import, sql file is 40GB, so it would be not fast despite that server pretty powerful(128GB ram).

Thank you!

I would recommend you to run the following

mysqlcheck -u ROOTUSERNAME -p --auto-repair --optimize DBNAME_HERE

This will prompt for MySQL Root Password, type in and it will  reclaim the disk space. Time taken for this process will depend on the size of the database. You may try this first in your test env and then proceed.


Should all queries where you expect a specific order, include an ORDER BY clause? In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results.

It appears SQL Server will provide results in exactly the same order for any given set of data that remains the same and never, ever changes.  Once you introduce any kind of uncertainty such as developers changing something, the order of results can no longer be expected to be the same.

Simply seeing the results in the same order, each time you press F5 does not guarantee the results you expect.

Try this:

USE TempDB;
DROP TABLE TestOrderBy;
DROP TABLE TestOrderBy2;
CREATE TABLE TestOrderBy
(
    ID INT NOT NULL CONSTRAINT PK_TestOrderBy PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData varchar(255)
);
INSERT INTO TestOrderBy (SomeData) VALUES ('E');
INSERT INTO TestOrderBy (SomeData) VALUES ('D');
INSERT INTO TestOrderBy (SomeData) VALUES ('C');
INSERT INTO TestOrderBy (SomeData) VALUES ('B');
INSERT INTO TestOrderBy (SomeData) VALUES ('A');

CREATE TABLE TestOrderBy2
(
    ID INT NOT NULL CONSTRAINT PK_TestOrderBy2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData varchar(255)
);
INSERT INTO TestOrderBy2 (SomeData) VALUES ('E');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('D');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('C');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('B');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('A');

SELECT SomeData
FROM TestOrderBy
UNION ALL
SELECT SomeData
FROM TestOrderBy2;

CREATE INDEX IX_TestOrderBy_SomeData ON TestOrderBy (SomeData);
CREATE INDEX IX_TestOrderBy2_SomeData ON TestOrderBy2 (SomeData);

SELECT SomeData
FROM TestOrderBy
UNION ALL
SELECT SomeData
FROM TestOrderBy2;

The results:

As you see, adding a simple index on the fields selected in the query alters the order of the results.

From here on, I'm adding ORDER BY unless I truly don't care.

I had a simpler repro in mind:
CREATE TABLE #x(z CHAR(1));
CREATE TABLE #y(z CHAR(1));

INSERT #x SELECT 'O';
INSERT #x SELECT 'R';
INSERT #x SELECT 'D';

INSERT #y SELECT 'E';
INSERT #y SELECT 'R';

SELECT z FROM #x
UNION ALL
SELECT z FROM #y;

Results:
O
R
D
E
R

Now add an index:
CREATE CLUSTERED INDEX z ON #x(z);

SELECT z FROM #x
UNION ALL
SELECT z FROM #y;

Results:
D -|
O -|- ordered based on the clustered index, not how originally inserted
R -|
E
R

This still produces the first three rows from #x and the last two rows from #y, so still doesn't prove that SQL Server might return those entire queries in a different order than physically laid out in the query (just that you can't rely on the ordering inside those sets). But clearly this mythical "observe an order once, and it will always be true" statement needs to be nipped.
Bottom line: do not rely on observed order. If you want a certain ordering, say so using ORDER BY.
Also, please read this post by Conor Cunningham, a pretty smart guy on the SQL team.


Update Query With Two Joins I am struggling to get my syntax correct for this update query.  I need to update Sanity.itemname based off employeecodes in SSL12.LBS  I can join to that table like so Sanity.cID = Helper1.cID and Helper1.LBS = SSL12.LBS but the issue I keep running into with my queries is that none of the tables hold true primary keys and my SWITCH() statement seems to be ignored and ALL cID are updated.

Below is sample DDL - can someone help me come to an update query that will only update the values in my SWITCH() statement, but ONLY for LBS that exist in SSL12

Create Table SSL12
(
   LBS int
  ,fname varchar(40)
) ;

Insert Into SSL12 (LBS, fname) VALUES
  (20, 'Blue'),
  (40, 'Red'),
  (60, 'Green') ;

Create Table Helper1
(
   cID int
  ,LBS int
  ,fname varchar(40)
) ;

Insert Into Helper1 (cID, LBS, fname) VALUES
  (2222, 20, 'Blue'),
  (4212, 40, 'Red'),
  (8132, 60, 'Green') ;

Create Table Sanity
(
   cID int
  ,itemname varchar(100)
) ;

Insert Into Sanity (cID, itemname) VALUES
  (2222, 'purple flower'),
  (2222, 'foghorn'),
  (2222, 'socks'),
  (4212, 'iguana'),
  (4212, 'camel'),
  (8132, 'red curtains') ;

And the UPDATE:

UPDATE [SSL12]
    INNER JOIN (Helper1 INNER JOIN Sanity ON Helper1.cID = Sanity.cID)
    ON [SSL12].[LBS] = Helper1.LBS
SET Sanity.itemname = Switch("itemname"="purple flower", "lilac",
                             "itemname"="red flower", "rose",
                             "itemname"="yellow flower", "daisy");

The big problem here is the join between Helper1 and Sanity that causes multiple duplicates, so with larger result sets this is not going to perform well. It is possible to rewrite the update statement so that it does what you need using a CASE expression, rather than a switch.

UPDATE s
SET itemname =
        CASE s.itemname WHEN 'purple flower' THEN 'lilac'
                        WHEN 'red flower' THEN 'rose'
                        WHEN 'yellow flower' THEN 'daisy'
             ELSE s.itemname
        END
FROM dbo.Sanity s
    JOIN dbo.Helper1 h ON h.cID = s.cID
    JOIN dbo.SSL12 ss ON ss.LBS = h.LBS ;

Which leaves the data in the Sanity table as:

cID     itemname
----    --------
2222    lilac
2222    foghorn
2222    socks
4212    iguana
4212    camel
8132    red curtains


Show full detail error message on the console I try to get the error from this call:

$ psql --dbname=synapse -c "SELECT     (json::JSONB->>'content')::JSONB->>'name' AS channel_name,     json::JSONB->>'room_id' AS room_id,     json::JSONB->>'sender' as sender FROM event_json WHERE json::JSONB->>'room_id' LIKE '%matrix.org%';";
Pager usage is off.
FEHLER:  nicht unterstützte Unicode-Escape-Sequenz
DETAIL:  \u0000 kann nicht in »text« umgewandelt werden.
CONTEXT:  JSON-Daten, Zeile 1: ...": {"age_ts": 1535735391873}, "content": {"body":...

but the error message is cut off, how do I get the full error message?

What you see is just the part of the line where PostgreSQL sees a problem, you cannot bet more.

But since you know that the problem is in line 1, you should be able to

SELECT json
FROM event_json
WHERE json::JSONB->>'room_id' LIKE '%matrix.org%'
LIMIT 1;

and search for the offending \u0000.


SQL Server - How to copy database daily I have 2 separate SQL Server instances, for the purpose of the question, we will call them Source and Target.

I have an ever changing database on Source called MyDatabase, this database is populated once a day from a piece of software. When this is dumped, I need to create a copy of the database on Target. It must be an exact replica.

My first method was using a SSIS package, to export and import the data, however will this method work if the database schema is changing?

For example, my package copies:

Target.Table1 (ColA, ColB, ColC)
Target.Table2 (ColA, ColB, ColC)

After my package has been set up, the schema becomes:

Target.Table1 (ColA, ColB, ColC)
Target.Table2 (ColA, ColB, ColD) (ColC has been dropped)
Target.Table3 (ColA, ColB)

How can I insure the changes in Table2 are picked up, and Table3 is exported and imported?

Sorry I can't give much more of an example, however hopefully this is enough detail for the question.

There are various high availability technologies.
See the following and resources below:

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Use replication to distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise. Replication to SQLCE 3.5 and SQLCE 4.0 is supported on both Windows Server 2012 and Windows 8.

Log shipping
Like Always On availability groups and database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases (referred to as secondary databases) for a single production database that is referred to as the primary database. For more information about log shipping, see About Log Shipping (SQL Server).

Database mirroring. Note! This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use Always On availability groups instead.
Database mirroring is a solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database. For more information, see Database Mirroring (SQL Server).

Backup/Restore:

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/high-availability-solutions-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-2017


SSAS : An error occurred when instantiating a metadata object Aside from installing SSAS, I have no experience using it.

Our developers are saying that it is no longer possible to create a new aggregation, whatever the cube.

The error occurs when hitting Start.

Here is what I found in the msmdsrv file

Type: 3, Category: 289, Event ID: 0xC1210012

(7/30/2014 10:55:10 AM) Message: Errors in the metadata manager.
An error occurred when instantiating a metadata object from the file,
'\\?\E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml'.
(Source: \\?\E:\MSAS11.DWH_AS\OLAP\Log\msmdsrv.log,
Type: 3, Category: 289, Event ID: 0xC1210012)

The file E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml has 0kb, so should this file be removed?

Any help on resolving this issue would be appreciated.

The solution was removing the empty files E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml which allowed the aggregation to continue


Log file increases while differential backup is in progress Database server: Microsoft SQL Server 2012 (SP1) - 11.0.3128.0

Database Size: 500+ GB

We have a staging history database which stores what so ever operation(Insert, update, delete) is done on live database is getting transferred to our history server via service broker. And that message is processed and stores data in history database.

We recently found a issue that message queue of our destination database is not clearing processed messages. We started cleaning the queue there were around 320+ M records which were stored in this queue.

We are running one script 24x7 which cleans processed messages of the queue.

Now, the problem which started recently is whenever our scheduled History database's differential backup is progress log file of History database increases to 10+ GB and as soon as it completes everything gets normal.

Is there anything that we are missing or is this is a normal thing?

Transaction backups do run in between differential backups. Usually differential backup takes around 1 hour and in between this one hour time 4 Transaction backups takes place.

Now, the problem which started recently is whenever our scheduled History database's differential backup is progress log file of History database increases to 10+ GB and as soon as it completes everything gets normal.

So was transaction log backup also running before the differential backup job? Starting from SQL Server 2005 both Differential backup and log backup can be started simultaneously on SQL Server but transaction log truncation would be delayed until the diff backup completes that is why till differential backup is running the log file grows to write transaction after it completes the logs are truncated and space is reused. In your case Log backup would have run while differential backup was running and would not have been able to truncate logs but as soon as it finished the logs were truncated

Full backup and differential backup can delay log truncation because both needs to read some part of log to create consistent backup and if log backup was allowed to truncate the log file this would result inconsistent full and differential backup

This is similar SE thread I answered before.