MySQL queries taking very long/not finishing I just started working with databases and have a problem with queries taking very long.

My table has the following form

CREATE TABLE table (column1 INT(10) AUTO_INCREMENT PRIMARY KEY,
column2 VARCHAR(255) NOT NULL,
column3 VARCHAR(255) NOT NULL,
column4 VARCHAR(255) NOT NULL,
column5 VARCHAR(255) NOT NULL,
column6 VARCHAR(255) NOT NULL);

ALTER TABLE table ADD UNIQUE entry (column2, column3, column4, column5);
ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I have inserted about 10 million rows.

Simple queries including a cartesian product like

SELECT a.column1, b.column1
FROM table AS a, table AS b
WHERE a.column2 = ''
AND NOT a.column3 = b.column3

take about five minutes before the results start showing.
A more complex one which includes ordering by multiple columns runs about half an hour before it eventually causes my system to freeze. There are no other concurrent database operations.

I'm running MySQL 5.6.25 on Ubuntu Vivid and didn't change any settings. My machine is a 6x3GHz Phenom II with 8 GB RAM.

Is there a problem with my table/query? Are there any default setting I should change?

edit

The total size of my data is 1.25 GB.

I tested a few more queries.
Before, I set innodb_buffer_pool_size=4G and removed the UNIQUE CONSTRAINT.

SELECT * from table; ~ 8 sec.
SELECT * from table WHERE column2='xyz'; ~ 20 sec.
SELECT * from table WHERE column2='xyz' ORDER BY column1; ~ 50 sec.
SELECT * from table WHERE column2='xyz' ORDER BY column1, column3; > 3 min.

I noticed that a temporary file is created, which is larger than 10 GB for query 4.
When doing queries with cartesian products, my system freezes after a few minutes and this file is never created (as my disk space remains constant).

This is my first real-life database project and I don't know what performance I can expect. Do I simply need different hardware for this amount of data?

Few points here:

"Cartesian product" is not simple :)
In your specific query, and based on the index you have, the time will highly depend on the distribution of the data. Try this query: SELECT COUNT(*) FROM table WHERE column2=''; I guess the result is not a small number.
UNIQUE entry (column2, column3, column4, column5); is not healthy. The index is very big. It affects both, reads and writes. 'Reads' because it has to be loaded into the memory when it is used, and 'Writes' because it has to be checked (and maybe updated) whenever a DML operation is executed. If this uniqueness is a must, I suggest adding an MD5 hash for all the fields, and have that field uniquely indexed.
To get smaller result, avoiding repetition, you may add WHERE... AND a.column1<b.column1. This will avoid you having:

1, 4
4, 1


What do these statements mean in the MS β exam 70-461 "skills measured" list? I have registered for the upcoming beta exam 70-461 entitled Querying Microsoft SQL Server 2012.

I've been reading through the exam overview and found a bunch of descriptions I'm not exactly sure what some of the items mean:

Create and alter DML triggers.

This objective may include but is not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Query data by using SELECT statements.

This objective may include but is not limited to: use the ranking
function to select top(X) rows for multiple categories in a single
query; write and perform queries efficiently using the new code items
such as synonyms and joins (except, intersect); implement logic which
uses dynamic SQL and system metadata; write efficient, technically
complex SQL queries, including all types of joins versus the use of
derived tables; determine what code may or may not execute based on
the tables provided; given a table with constraints, determine which
statement set would load a table; use and understand different data
access technologies; CASE versus ISNULL versus COALESCE

The problem here I think is with the wording, which I think I'm not able to fully understand.

If someone could clarify these I'd be most grateful.

To put it as an actual question:

What is Microsoft talking about on the above highlighted statements?

DML - refers to data manipulation (update, insert, delete). A DML trigger is a trigger created to happen on update, insert or delete of table data.

The update function is the part of a DML trigger where you test if a required update has occured.

For Example:

CREATE TRIGGER myupdate_trigger
ON mytable
FOR UPDATE AS
if UPDATE(column_1)
BEGIN
-- Do something
END

This will trigger a further action every time column column_1 is updated in table mytable. The update function is UPDATE(column_1).


percona mysql crashing at innodb initialization i have a slave percona db setup that also maintains few of it's own tables

it started crashing with this in error.log:

InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 4047010, file name bin-log.000529
InnoDB: and relay log file
InnoDB: position 0 4047154, file name /var/mysql/relay/relay-log.001563
InnoDB: Last MySQL binlog file position 0 71871595, file name /var/mysql/bin/bin-log.001518
120709  9:00:27  InnoDB: Waiting for the background threads to start
120709  9:00:27 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=2147483648
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2427524 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 = (nil) thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x39)[0x8024a9]
/usr/sbin/mysqld(handle_segfault+0x3c2)[0x519df2]
/lib/libpthread.so.0(+0xef60)[0x7f3edfb98f60]
/usr/sbin/mysqld[0x838d8f]
/usr/sbin/mysqld[0x935e67]
/usr/sbin/mysqld[0x936795]
/usr/sbin/mysqld[0x92c2bd]
/usr/sbin/mysqld[0x8579f5]
/usr/sbin/mysqld[0x84ce2c]
/lib/libpthread.so.0(+0x68ba)[0x7f3edfb908ba]
/lib/libc.so.6(clone+0x6d)[0x7f3edee3202d]

after that mysql process dies

there is enough memory on server - that is not the cause

any ideas?

solved by upgrading server from 5.5.13 to 5.5.20


How to use 2 auto increment columns in MySQL phpmyadmin Is it possible to use 2 auto increment values ?

One starting from 0
Another starting from 4000400

Please help me

No, you can't. Not out of the box. Possible workarounds:

Triggers (an AFTER INSERT trigger).

Disadvantages:

Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares.

Advantages:

You can have FOREIGN KEY constraints that reference this column.
You can update the first and the second id columns individually.

Views. If you only a value that is always +4000400 of the first auto incremented value, you can use a view, so basically not store this value at all, just calculate it when you need it:

    CREATE VIEW
        tablex_with_2nd_AI AS
    SELECT
        tablex_id,
        colA,                                 -- other columns
        --                                    -- you need
        tablex_ix + 4000400  AS second_id
    FROM
        tablex ;

Disadvantages:

You can't have a FOREIGN KEY constraint that references this (virtual) column.

Advantages:

Saves some space. Slightly faster inserts.

Don't do that, don't have a second auto incremented column at all. Do you really need a second auto incremented value? What for? A description of the actual problem you are trying to solve would help others help you better. I think you have only told here how you tried to solve a problem and not what the actual problem is.


pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL:

heroku pg:backups:restore 'https://drive.google.com/open?id=dump_id_link_here' DATABASE_URL

I am already logged in into my Heroku app from the terminal on which I run the command.
but I got the same error twice. I have been searching online and found threads such as pg_restore: [archiver] did not find magic string in file header but I could not help link between the two, since I am very new two Postgrel. I Hope you guys point me out towards the issues. Very much appreciated.

Starting restore of https://drive.google.com/open?id=dump_id_link_here to postgresql-symmetrical-52186... done

Stop a running restore with heroku pg:backups:cancel.

Restoring... !
▸    An error occurred and the backup did not finish.
▸
▸    waiting for restore to complete
▸    pg_restore finished with errors
▸    waiting for download to complete
▸    download finished with errors
▸    please check the source URL and ensure it is publicly accessible
▸    Run heroku pg:backups:info r002 for more details.

=== Backup r002
Database:         BACKUP
Started at:       2019-09-14 21:14:26 +0000
Finished at:      2019-09-14 21:14:27 +0000
Status:           Failed
Type:             Manual
Backup Size:      0.00B (0% compression)

=== Backup Logs
2019-09-14 21:14:27 +0000 pg_restore: [archiver] did not find magic string in file header
2019-09-14 21:14:27 +0000 waiting for restore to complete
2019-09-14 21:14:27 +0000 pg_restore finished with errors
2019-09-14 21:14:27 +0000 waiting for download to complete
2019-09-14 21:14:27 +0000 download finished with errors
2019-09-14 21:14:27 +0000 please check the source URL and ensure it is publicly accessible

So I have tried to use Dropbox and other services, but I had the same error until the tenth attempt. Here is what I did:

I logged in to my with my Heroku cli from my terminal on the master branch so I don't have to redefine the app. (specify the path to your damp. I have mine on the same directory) e.g:

[murph@ComputerX languageandcode]$
the I run

[murph@ComputerX languageandcode]$ heroku pg:psql < langandcode.sql

Then I became happy as pushing started and completed successfully.


Percona MySQL 5.5.33 not reading my.cnf? At some point Percona MySQL on CentOS 6.4 seems to have stopped reading my.cnf, possibly after MySQL updated to 5.5.33 a few days ago.

Selection from /etc/my.cnf:

[mysqld]

# GENERAL #
user                    = mysql
default-storage-engine  = InnoDB
socket                  = /var/lib/mysql/mysql.sock
pid_file                = /var/run/mysql.pid
symbolic-links          = 0

# CHARACTER SET AND COLLATION #
collation-server        = utf8_unicode_ci
character-set-server    = utf8
init-connect            = 'SET NAMES utf8'
init-connect            = 'SET collation_connection = utf8_unicode_ci'

# DATA STORAGE AND LOGGING #
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
slow_query_log_file     = mysql-slow.log
slow_query_log          = 1
long_query_time         = 8
log_error               = mysql-error.log

# CACHES AND LIMITS #
max_connections         = 250
max_connect_errors      = 10000
open_files_limit        = 20000
back_log                = 100
wait_timeout            = 10800
interactive_timeout     = 600
max_allowed_packet      = 256M
tmp_table_size          = 512M
max_heap_table_size     = 64M
query_cache_size        = 32M
thread_cache_size       = 64

Some output from 'mysqld --help --verbose'

log-error                                         mysql-error.log
slow-query-log                                    TRUE
slow-query-log-file                               mysql-slow.log
max-connections                                   250
max-heap-table-size                               67108864

Ok that seems good, matches my config. How about logging into mysql and doing some SHOW VARIABLES:

log_error   /var/lib/mysql/hostname.err
log_slow_queries    OFF
slow_query_log_file /var/lib/mysql/hostname-slow.log
max_connections 151
max_heap_table_size 16777216

Huh, well that ain't right. Slow log is off, error log has the wrong name, connection and heap sizes are wrong, and the server collation is wrong as well.

Processes running

root     23533     1  0 12:32 pts/3    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/hostname.pid
mysql    23624 23533 91 12:32 pts/3    01:05:32 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/hostname.err --pid-file=/var/lib/mysql/hostname.pid

I see the error log listed in the process has the wrong name right off the bat. I've tried manually restarting mysql from /etc/init.d, via 'service mysql restart', via restarting the entire server. Nothing seems to get MySQL to read the conf.

Maybe something is breaking when mysqld_safe spins up a mysql instance? No problems reported in the error log when the server starts. Any ideas?

Ultimately the problem was SELinux on CentOS, and the answer was to disable SELinux! I couldn't find any errors in the SELinux log related to MySQL so this took quite some time to track down.


Oracle Upgrade Standard to Enterprise Edition I need to upgrade several Oracle Database 12c Standard Edition to Enterprise Edition. The only upgrade path I found is to reinstall the Oracle software. Is that the only way to upgrade?

You can simply install Enterprise Edition in a different folder, and you can start the databases using that as ORACLE_HOME.

This way you can shutdown the databases running from Standard Edition ORACLE_HOME and startup databases using the Enterprise Edition ORACLE_HOME one at a time, whenever it is feasible.

But you will not be able to skip the install part. There is no official and supported method for converting an existing Oracle software installation from Standard to Enterprise Edition.


What is the benefit of having multiple databases in the same Availability Group vs single AG per database We are planning a migration from current mirroring architecture to Availability Groups for several databases. For every Database we have defined the primary/secondary replica configuration.

The question that I'm unsure is: do we need to group the databases with the same primary/secondary configuration into the same AG, or use one AG per Database. What are pros&cons of having individual AGs vs AG with multiple databases? Is there a best practice to determine if a database needs its own AG or can be combined with others? Any performance or failover advantages?

An Availability Group is a unit of failover. Therefore, when considering how to group databases into an AG, keep in mind that all of these DBs will fail over together.

If there are multiple databases that must failover together, then I would recommend putting those databases into a single AG.

Similarly, you may have databases that are completely independent, where you want to put them in separate AGs so that a failover of one does not affect the other.

In some cases, you may wish to put databases into AGs based on logical groups that fail over together, even if they technically can fail over independently.

Having too many AGs or too many DBs per AG can both be management headaches. For example:

If you have 50 DB, each in it's own AG: you'll have 50 unique connection strings to manage, and 50 failovers to execute for a planned failover.
If you have 50 DBs in a single AG: If there is a problem with one database that necessitates a failover, then the other 49 databases need to be failed over as well, with all 50 databases incurring a short outage during the failover.


Adding slave to existing master-master config throws foreign key constraint failed error We have two MySQL servers running in master - master configuration.
Now we have to add a slave to the existing configuration, but upon adding the third database server and starting slave on it, it throws:

Cannot add or update a child row. foreign key constraint fails

We have tried taking a mysqldump from the master with --master-data and restored it. After that, we started the slave on the slave DB. But even then, it says the same error. We have also tried XtraBackup. But that also throws the same error. Are we missing something?
mysqldump command:
mysqldump --master-data -uroot -p dbname > dbname.sql

We have also tried it this way: percona xtrabackup
In both cases, upon getting the foreign key error, we tried to dump and restore individual tables referenced by the foreign keys manually from the master to the slave. Upon doing this, the replication starts and seems to work normally with 0 seconds behind master for a few minutes, after which another foreign key error shows up, stopping the replication.

auto_increment_increment is set to 2 in both masters.
auto_increment_offset is set to 1 in Master 1 and 2 in Master 2.
We have mostly innoDB tables and some MyISAM tables.
MySQL version 5.5.30.

There is a follow up question: After adding a slave to Master - Master configuration, slave syncs from only one master

Given the Following

+<------+
|       ^
V       |
M1      M2
|       ^
V       |
+-------+

and You Want the Following

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

Given Master IP is 10.1.2.30

Logon to the Slave and run the mysqldump like this

MAS_IP=10.1.2.30
SLV_IP=localhost
MYSQL_MAS_CONN="${MAS_IP} -uroot -ppassword"
MYSQL_SLV_CONN="${SLV_IP} -uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=1 --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database --all-databases"
mysql ${MYSQL_MAS_CONN} -e"FLUSH LOGS;"
echo "STOP SLAVE;" > MySQLData.sql
mysqldump ${MYSQL_MAS_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysql ${MYSQL_MAS_CONN} < MySQLData.sql

If you have already performed this, let me address your comments.

  But upon adding the third DB server and starting slave on it, it throws Cannot add or update a child row. foreign key constraint fails error.

Why would foreign key problems happen if you copied the data from a Master to a Slave? The auto_increment ids on the Slave simply did not match that of the Master. This is true even if you the Slave's Master is where you got the data from. Why?

  auto_increment_increment is set to 2 in both masters. auto_increment_offset is set to 1 in Master 1 and 2 in Master 2. We have mostly innoDB tables and some MyISAM tables.

You have to make sure the Slave has the exact same auto_increment_increment and auto_increment_offset as the Master you are replicating from.

Goto the Master and run

SHOW VARIABLES LIKE 'auto_increment%';

Put those values into the Slave's my.cnf and restart mysql.

REMEMBER : A Slave's auto_increment behavior needs to be identical to that of its master.

Give it a Try !!!

UPDATE 2013-04-06 17:26

Here is the problem: Let's say you have the setup

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

If you did the following:

mysqldump data from M1
load dump it S1
enabled Replication from M1 to S1
M1 and S1 have identical auto_increment_offset
M1 and S1 have identical auto_increment_increment

there should never be foreign key violations.

What you may need to do is cleanup every server's view of the auto increment values

Here is something to try

Set these values on M1's /etc/my.cnf

auto_increment_increment = 10
auto_increment_offset = 1

Set these values on M2's /etc/my.cnf

auto_increment_increment = 10
auto_increment_offset = 1

Set these values on S1's /etc/my.cnf

auto_increment_increment = 10
auto_increment_offset = 1

Restart mysql on all three servers

Load Data like this:

mysqldump from M1 to
Run SET GLOBAL sql_log_bin = 0; on M1
Load the mysqldump into M1
Run SET GLOBAL sql_log_bin = 1; on M1
Run SET GLOBAL sql_log_bin = 0; on M2
Load the mysqldump into M2
Run SET GLOBAL sql_log_bin = 1; on M2
Load the mysqldump into S1
Reset Replication amongst the threee server

If nothing breaks, you can then do this

Set these values on M2's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 2

Restart mysql on M2

Everything should be clean now. From here, INSERTs should be properly handled.

Give it a Try !!1


A lot of ad-hoc queries impact on server We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc Workloads' so the query cache doesn't get bloated.
I've asked the supplier to adjust his queries but he doesn't want to change them.

My question is, what impact can a lot of ad-hoc queries have on my server.
My first thought is a lot of compiles.

It can impact the amount of memory available. Each query plan takes a little memory away from SQL Server.

Also, each time a plan has to be created, SQL Server has to compile it. This means it uses CPU which could be used for other processes. If you have a high CPU contention than that could become a problem.

This query can help you see how large the query plans actually are:

SELECT TOP 1000
    st.text ,
    cp.cacheobjtype ,
    cp.objtype ,
    cp.refcounts ,
    cp.usecounts ,
    cp.size_in_bytes ,
    cp.bucketid ,
    cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE   cp.cacheobjtype = 'Compiled Plan'
    AND cp.objtype = 'Prepared'
ORDER BY cp.usecounts DESC;


How does R-Tree outperform B-Tree for simple check if a point is within a rectangle For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points.

However, for simple check of a point in a rectangle, it's effectively

min

If x and y is indexed by b tree, they too can kick out a lot of false points.

So I did an experiment with spatial index

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

This is pretty fast. .24 seconds

And then I do

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA
WHERE
  -6.2317830813328 < TB.Latitude
AND
  TB.Latitude < -6.1382169186672
AND
  106.72621691867 < TB.Longitude
AND
  TB.Longitude <106.81978308133
AND
   MATCH (TA.FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

This is very slow.

I wonder why. It doesn't make sense.

R-tree structure works in a way that two nearby points are "closer" in the R-tree index - because both coordinates and both with same weight are used to decide where (in the index) a new point is to be placed.

So, it's easy to identify points that are "near" a fixed point - meaning points that have both coordinates near the fixed point coordinates.

B-tree indexes on the other way work differently. Even if you have a compound index, say (Latitude, Longitude), where both coordinates are included the index, they do not have the same weight. In other words, two points with different Latitude and the same exact Longitude will be very far apart (in the index structure). Much more apart than two points with same Latitude and different Longitude.

So, searching for nearby points (with the above (Lat, Long) index) could possibly find fast nearby points with the exact same Latitude. But for all the other points, it will not be very efficient.

As an example, consider you are looking for places nearby a village in Africa - a village that has the same Latitude with London. Your query would spend lots of time calculating the distances between your village and all the (millions) points in UK that have the same (or almost the same) latitude with it. And then reject those points because they are too far apart.

You might think then that two B-tree indices, one on (Latitude) and one on (Longitude) would be fine for such queries. After all, why shouldn't both indices be used in a query that has a:

WHERE Latitude  BETWEEN @LatStart  AND @LatEnd
  AND Longitude BETWEEN @LongStart AND @LongEnd

I think that DBMS cannot use two (B-tree) indices like that in a query (MySQL cannot AFAIK). Even if it could though, it wouldn't be very good, preformance-wise, as both conditions may not narrow the search much. So, after these two index scans, a million rows found that match one condition would have to be merged with a million rows from the other one. And this merge will probably require sorting as well - which will kill performance if the subresults are big.

In general, any query that has more than one range condition on the same table is difficult to be optimized by B-tree indices. (for other DBMS that have other types of indices, like Hash or Bitmap, I can't speak about).

That's why R-trees and other indices that perform well in spatial searches were invented in the first place. They kind of "interleave" both coordinates (or all 3 or 4 in higher dimensional indices) in the index structure and no coordinate has higher weight than the others.


How to export a single huge table from SQL Server? I'm given the task to export the content of a huge table from a production database and to import the data into a database at a remote location.

The Table has about 45,000,000 rows. using about 4 GB space in the database.

Its 10 Columns are of type int, datetime and varchar(n) <= 255, but some of the varchar fields contain newlines and the usual field separators used by bulkcopy.

I guess export to Excel is no option due to the number of rows.
Export with SSIS to Flatfile is possible (took about 30 minutes), but reimport is not automatic, since some fields where spitted.

My current idea is

create a new database on the production server (using same
collations as the production database)
Set its recovery mode to simple
copy the data with
Select * into copy_of_table from prod_db..big_table
Detach mdf file from this new database and ship it to the other
location
Drop adhoc database

I assume that inserting into a database with simple recovery model generates only minimal logging on the new database and this way is one of the fastest possible to export the data and the impact to the production database is the least possible.

I'd of thought BCP, with a format file rather than defaults, would be your best bet.

Creating a format file


SQL Server inexplicably stopped working We had a production emergency yesterday: out of the blue, in the middle of peak usage time, our SQL Server just stopped working.  Never mind no new connections; even existing connections stopped functioning.  I restarted the service and everything came right immediately, but in the interim we lost about 30 minutes of downtime and got serious egg on our faces.

There are no errors or warnings in the event log.  The only clue that something was going wrong is a series of "Information" messages in the event log from the time the outage began, every 5 minutes, saying:

  New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 1500 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%%. System Idle: 98%%.

So it seems like we're running out of worker threads, of which we have 576.  And I have no idea how that can happen.  I know that there are no particularly long running queries, because I am constantly running a Trace to pick up anything longer than 2 seconds, and we're getting no more than one or two of those every 5 minutes.

Also, we have not made any significant changes to the system for the last several weeks, and this kind of thing has never happened before.  Perhaps our usage has been growing, but if it was a resource overload, I would have expected the CPU to be maxed out, rather than at the beach around 0-2%.

What could have happened?  How do we diagnose, and how do we prevent this happening again?

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
    Jun 17 2016 19:14:09
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Based on the comment thread, the first step is to update the system. That is, install Windows updates, patch the Sql Server and update all the device drivers and firmwares. On virtual machines (which you seem to have), the VM guest drivers and the host itself should be updated, but that's usually not DBA's domain.

Lack of patching is often a red flag about more widespread infrastructure problems. Please make sure that the system is fine in other ways. That is, you have a working restore plan [1], service accounts, passwords and user rights are documented and so on.

To illustrate the patching effect, SentryOne has a handy list about the number of fixes included in service packs and cumulative updates. Just SP2 fixes some 133 issues, of which "only" 53 are public ones. Reading through all the release notes about if the problem is mentioned is going to take a while.

As a side note: Microsoft's support policy is restrictive. One needs quite strong a business case (read: lots of money is at stake) if unpatched software problems are looked into. The advice usually is akin to "install all the patches and see if problem occurs again".

[1]: "Working" means you have tested recently enough that the backups can be restored and contain the stuff they should. It's all too common that backup process looks successful, but doesn't quite do what was intended.


How to solve replication in MongoDB only with two machines? I have two machines connected in a ring network, each one running Wildfly in HA mode using JGroups ant MongoDB in HA Mode using Replica Set. The machine 1 is the primary and machine 2 is secondary.

Whenever Wildfly 1 drops, Wildfly 2 raises by reconfiguring MongoDB 2 to be primary by changing the settings of the ReplicaSet.

We know that the Replica Set does not work by itself with two machines, but we have this limitation.

If a split occurs on the network, Wildfly 1 continues to work writing in MongoDB 1, Wildfly 2 is also active, changes the MongoDB 2 configuration to primary, and begins writing to it.

We are using version 3.6.5 of MongoDB.

Questions:

What happens to MongoDB when reestablishing the network and Wildfly 1 take over as principal?
MongoDB 2 will continue as a primary but Wildfly 2 will be stopped?
MongoDB 1 will be able to merge with MongoDB 2?
Considering that MongoDB resolves by doing the merge, what happens if the MongoDB 2 OPLOG passes the size specified in the configuration? Does he go back to the beginning (like a car odometer)?

We know that the Replica Set does not work by itself with two machines, but we have this limitation.

If your goal is high availability with automated failover I would definitely aim for a solution that does not involve reconfiguring the replica set or allow for the possibility of multiple active primaries.

Ideally this does require a third machine so you have a tie-breaking vote to allow  either MongoDB 1 or MongoDB 2 to become primary in the event of a partition between the two. The third machine can either host another secondary (recommended) or a voting-only arbiter (not recommended for HA as it cannot contribute to majority write acknowledgement).

  Whenever Wildfly 1 drops, Wildfly 2 raises by reconfiguring MongoDB 2 to be primary by changing the settings of the ReplicaSet.

If MongoDB 2 is a secondary, this scenario only works if you force reconfigure (since a majority of voting replica set members are not available).

As noted in the documentation, force reconfiguration is a last resort for recovering a replica set rather than a procedure you should frequently use or automate:

Use this procedure only to recover from catastrophic interruptions. Do not use force
every time you reconfigure. Also, do not use the force option in any automatic scripts
and do not use force when there is still a primary.

  If a split occurs on the network, Wildfly 1 continues to work writing in MongoDB 1, Wildfly 2 is also active, changes the MongoDB 2 configuration to primary, and begins writing to it.

A primary can only be elected or sustained in a partition with a majority of voting members. With only two voting members, a network split will result in the current primary stepping down (since it can't see a majority of the voting members) and no primary elected.

  What happens to MongoDB when reestablishing the network and Wildfly 1 take over as principal?

If you forced MongoDB 2 to become primary, it will have a replica set configuration with a significantly higher replica set configuration version. MongoDB 2 may also have writes that haven't replicated to MongoDB 1 yet, so there are two stages of recovery:

If MongoDB 1 accepted any writes that were not replicated to MongoDB 2, MongoDB 1 will enter rollback mode to try to revert write operations to a common point in the oplog. If too many operations have been written on MongoDB 2 or MongoDB 1 no longer has a common point in its oplog, MongoDB 1 will have to be resynced.
Assuming both members now have a consistent oplog history, MongoDB 1 will resume syncing (as a secondary) to try to catch up write operations that have been applied on MongoDB 2

If both of those stages are successful, MongoDB 1 should be eligible to become primary. MongoDB 1 won't automatically become primary unless it has a higher priority than MongoDB 2.


Oracle: Listener alias vs Listener name In sqlplus we can set the local_listener to either the description of the listener or the listener alias. What is the difference between listener alias and listener name. Are these the same ? If not how can I resolve the listener name from the listener alias.

Listener alias and Listener name need not be the same. The database resolves the listener alias to the correct listener description by looking up the tnsnames.ora file.


SELECT Data Set Using Stored Procedure with BCP Error All, I have a BCP export query. It is failing with the useless error mesage on BCP usage:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]
NULL

The query I am using is as follows

DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';"
               queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt"
               -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + '';
EXEC xp_cmdshell @SQL;
GO

where if I replace EXEC ispsSelectEmptyAsNull ''B1A''; with SELECT * FROM B1A; there is not problem. The query EXEC ispsSelectEmptyAsNull ''B1A''; itself runs with no problems and returns the correct result set. I have run a million BCP queries but never using an SP to provide the result set. Am I doing this correct?

The SP is as follows:

IF EXISTS (SELECT name
           FROM sys.procedures
           WHERE name = N'ispsSelectEmptyAsNull')
DROP PROCEDURE ispsSelectEmptyAsNull;
GO
CREATE PROCEDURE ispsSelectEmptyAsNull @TableName NVARCHAR(256)
AS
DECLARE @Columns VARCHAR(MAX);
SELECT @Columns =
    COALESCE(@Columns + N',
    NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '',
  N'NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '')
FROM (SELECT DISTINCT COLUMN_NAME
      FROM [IPACostAdmin].INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = @TableName) AS H
ORDER BY COLUMN_NAME;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
    SELECT ' + @Columns + N'
    FROM ' + @TableName + N';';
-- SELECT CONVERT(XML, @SQL);
EXEC (@SQL);
GO

Thanks very much for your time.

Ps. I do not care about SQL injection etc.

You have CR\LF in shell invocation, a no-no. Make it a single line:

DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" '+
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + '';
EXEC xp_cmdshell @SQL;
GO


How do I determine what MDF files are in use I just right-click deleted all my databases that were not in use. However all the MDF and LDF files are still in the data folder on the machine. What is the best way to determine what MDF and LDF files I can remove since the database no longer exists in SSMS.

Also is this the best way to delete unused databases? (The right-click -> Delete method.)

The  Best way  to delete  databases in sql server is     using    DROP DATABASE  command .
If you right click and delete in SSMS  you will end up with files left in folder .

Then You have to manually remove the files from server folder .

To remove these files from folder , first bring sql server to online  then go the folder and start deleting  the files ,  when deleting mdf or ldf files  you will get error like files in use  means that database you are using and you can remove remaining .


MongoDB disk operations with journaling disabled Assuming MongoDB 3.6, usage of WiredTiger, with journaling disabled:

How does mongod write data to hard drive? It performs write operation per every document? Per every chunk? And similarly: how about reads? Also: are those reads and writes random or sequential?

Going further: are writes made during writing journal file similar to those when creating snapshot?

Further explanation of question: I'm trying to limit usage of hard drive  by Docker containers running MongoDB, so I started by benchmarking my disk following this guide: binarylane.com - How to benchmark disk I/O
. It states that

  Again, databases and many other programs will read very small chunks
  of data - 4 kilobytes is a good working estimate.

I'm wondering if this assumption is true for MongoDB.

There are two primary methods for WiredTiger to ensure the durability of your data: journaling and checkpointing.

Checkpoints store the valid and consistent state of the whole database. Should the server crash for any reason, it can restart from the last checkpoint in a consistent state. WiredTiger by default checkpoints the database every 60 seconds.

Journals store the writes that happened in-between checkpoints. If the database crashed between checkpoints, the journal data will be replayed on top of the last known good checkpoint. The journal is persisted to disk every 50 ms. The journaling process is optimized to be a lot more lightweight compared to checkpoints (and it also does a lot less work), so it's typically much faster than a checkpoint.

If journaling is disabled, the behaviour is slightly different between a mongod running in standalone mode or in replica set mode:

MongoDB 3.6.4 standalone with journaling disabled: only the complete state of the whole database is written to disk on every checkpoint. That is, if your database crashes between checkpoints, you can potentially lose the last 60 seconds of your writes (if using default checkpoint timing values).
MongoDB 3.6.4 replica set with journaling disabled: by design, a replica set requires more durability since it must record every operation in the oplog and ensure that the oplog is safely persisted. Thus, it will perform a checkpoint on every write instead. This will slow down the replica set tremendously.

The tradeoff you make by disabling journaling:

In a standalone deployment, since the main goal of a database is to persist your data, limiting disk usage (space or IOPS) will result in more data being lost between allowed writes. However, it's not recommended to run a standalone MongoDB for production purposes.
In a replica set, the write process is considerably slowed down due to the need to persist the oplog. This will also make the deployment use more disk IOPS. In the future versions of MongoDB, running a replica set with no journal will not be allowed (see SERVER-30347).

For more information regarding journaling, please see the Journaling Process page.


How are these two SQL Server rollbacks different? In SQL Server 2008 R2, how are these two rollbacks different:

Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely.

Run the same ALTER statement, but this make sure that the LDF file is not big enough for it to complete successfully. Once the LDF limit is met and no 'autogrowth' is allowed, query execution stops immediately (or a rollback happens) with this error message:

The statement has been terminated.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SampleDB' is full.
To find out why space in the log cannot be reused, see the
log_reuse_wait_desc column in sys.databases

How are these two different on the following points?

Why is the second 'rollback' instantaneous? I'm not entirely sure if it could be called a rollback. My guess is, the transaction log is written as the execution progresses and once it realizes that there is not enough space to fully complete the task, it just stops with some 'end' message, without commit.

What happens when the first rollback takes so much time (is a rollback single threaded)?
2.1. Does SQL Server go back and undo the entries made in the LDF file?
2.2. The LDF file size gets smaller at the end of the rollback (from DBCC SQLPERF(LOGSPACE))

One additional question: During the second scenario, SQL Server starts consuming LDF file pretty quickly. In my case, it increased from 18% usage to 90% usage in the first few minutes (< 4 mins). But once it reached 99%, it stayed there for another 8 mins, while fluctuating usage between 99.1% to 99.8%. It goes up (99.8%) and down (99.2%) and up again (99.7%) and down (99.5%) a few times before the error is thrown. What is happening behind the scenes?

Any MSDN links that could help explain this more are appreciated.
At Ali Razeghi suggestion, I'm adding perfmon : Disk Bytes/sec
Scenario 1:

Scenario 2:

As indicated above, after running more tests, I arrived at an calculated conclusions. I summarized all of them into a blog post here, but I'll copy some content over to this post for posterity.

Conjecture (based on some tests)

As of now, I do not have a clear explanation as to why this is. But following are my estimations based on the artifacts gathered during the tests.

Rollback happens in both scenarios. One is explicit rollback (user hitting Cancel button), the other is implicit (Sql Server making that decision internally).

In both scenarios, the traffic going to the log file is consistent. See the images below:

Scenario 1:

Scenario 2:

One artifact that reinforced this line of thinking is capturing Sql Trace during both the scenarios.

Scenario 1 is self evident a.k.a. when we hit ‘Cancel’, it rolls back.
In Scenario 2, the error message is displayed after performing ‘rollback’ implicitly. In Sql Trace, we see the error message “The transaction log for database ‘SampleDB’ is full” a long time before the message is displayed on the screen. So, my guess is that rollbacks happens in both scenarios, but the error message is Scenario 2 is displayed after successfully and completely performing the rollback.

Scenario 2 seems to take longer as as it progresses much further along, so the rollback takes longer.

Unexplained behavior:

Why does the log file usage vary so much?

It increases to 90%, then down to 85%, then up to 99% and hovers there for a long time. I see it going up and down like this several times: 99.2 %, 99.8 %, 99.1 %, 99.7 %. Why does this happen?
One possible explanation is that, there might be a background process (something like Log Flush) that cleans up log file every few minutes. And everytime it kicks in, some entries are cleared up, resulting in more free space available.

Any ideas to help explain this behavior in a better way are welcome.


SQL Server - How to solve FIFO Cost of OHB... function, cursor,? SQL Server 2008R2 - Need to solve FIFO cost of items on hand.
To do this I need to add the cost for the most recent receipts quantity up to the current on hand balance(OHB).

So lets say I have
    Item : b1234
    OHB  : 5

Receipts:
RctNumb | Item  | QTYRct | RctCostPerItem | DATERECD
rct05   | b1234 |      1 |           1.00 | 20160822
rct04   | b1234 |      3 |           2.00 | 20160820
rct03   | b1234 |      3 |           1.25 | 20160819
rct02   | b1234 |      2 |           2.50 | 20160818
rct01   | b1234 |      8 |           2.00 | 20160810

Here I would need:
(1*1.00)+(3*2.00)+(1*1.25) = CostofOHB = 8.25

I have no idea how to address this with SQL.

Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/


What is non-CDB @ Oracle ? I'm reading about Oracles PDB and CDB, and their test performances

http://www.oracle.com/technetwork/database/multitenant/learn-more/oraclemultitenantt5-8-final-2185108.pdf

They compare PDB with non-CDB's.
But I dont understand what a non-CDB is. Here is the definition from the document

"multiple stacked single-instance databases (non-CDBs) on the same operating system"

Is non-CDB one single Database process with several databases or
Is non-CDB several Database processes?

A non-CDB is anything that isn't a CDB; that is, any database before 12c, or a 12c database created without the enable pluggable database clause. If you create a non-CDB it isn't Multitentant and is a single-instance standalone like a pre-12c database.

  Is non-CDB one single Database process with several databases or Is non-CDB several Database processes?

Neither, it's a single instance (set of processes) with a single database.

The definition you quoted refers to an aspect of non-Mulitenant architecture:

  stacked single-instance databases

So you have one or more Oracle homes (software installations), each with one or more independent databases running from that home. This was the only way to run multiple databases on the same server before 12c's container/pluggable model.

The problem with doing that is the database instances are completely independent and have no visibility of each other, so they are all competing for resources (CPU, memory, network) at operating system level, and you have to have enough resources for them all to be running at peak to avoid degraded performance, even if that is unlikely to happen often. Or don't have enough resources and one or more end up starved at some point. You don't want that happening in production, so it's generally considered a bad idea to do this in production environments.

This is also touched on in the 12c documentation:

  A typical response to the management problem is to place multiple databases on each server. The problem is that the multiple database instances do not share background processes, system and process memory, or Oracle metadata.

12c's Multitenant model gives you a single container database that manages the resources at operating system level and shares them with the pluggable databases as needed, potentially significantly reducing the total resources the O/S server needs, and hiding/simplifying the management of the plugged-in dtabases.

It's somewhat analogous to running virtual machines on a physical server - the physical server needs fewer actual CPUs and less memory than are allocated in total to all the VMs, and the VM management layer controls the balancing and contention of the resources depending on the actual needs and loads of all the VMs. The VMs themselves aren't really aware of that happening, they just use what they need; similarly pluggable databases aren't really aware that the container is managing their resources rather than the operating system.


Manual recovery of dropped collection in mongodb Some collections were accidentally dropped from mongodb. Although the collections do not exist, in the dbpath there are 6GB of dbname.0, dbname.1,... and dbname.ns

We tried to run mongod --repair but instead of recovering the files it deleted them.

Is there a way to recover these data? maybe partially?

We are using standalone mongodb.

Thanks

We tried to run mongod --repair but instead of recovering the files it deleted them.

Repair and backup processes will (by design) ignore deleted data. Based on the filenames referenced in your question, you are using the MMAP storage engine. When a record is deleted in MMAP the first several bytes of the record are overwritten and the preallocated storage space is marked as available for re-use on the free list. There is a possibility you could write a custom script to try to recover useful bytes from deleted records, but there is no supported "undo" process for doing so.

Hopefully you took a backup of your data files before running --repair. Repairing MMAP data files results in a compete rebuild of your data files and would free up preallocated space used by deleted collections and documents. In any case where you are trying to recover or repair database files from a standalone MongoDB deployment, stopping your MongoDB instance and taking a file copy backup of your data files is a recommended starting point.

  Is there a way to recover these data? maybe partially?

Your best option would be to restore from your most recent MongoDB backup.

In a standalone MMAP deployment without backups, the only other possibility would be writing custom data recovery scripts to try to identify and salvage deleted documents. This involves some significant guesswork and manual effort to clean up data. Deleted records will be missing some essential information such as the size of the associated BSON document, and there may be variations in internal storage depending on the provenance of your data (for example, if all data was created in a specific version of MongoDB versus data files that have been upgraded through successive major releases).

See StackOverflow: Is there any way to recover deleted documents? for some possible approaches.


Can't connect to Database Engine I just built a server (SQL 2017) and I can connect to Analysis Services and Integration Services via SSMS without issue using Windows Authentication.

When I try to connect to the Database Engine I get:

  Cannot connect to myserver01.

  Login failed for use 'me'. (Microsoft SQL Server, Error: 18456)

  A connection was successfully established with the server, but then an
  error occurred during the login process. (provider: Shared Memory
  Provider, error: 0 - No process is on the other end of the pipe.)
  (Microsoft SQL Server, Error: 233)

I checked SQL Configuration Manager and everything seems to be running properly. I restarted everything just to be safe, but no changes. I see some answers on here referring to turning on Mixed Mode, though I think that wouldn't be relevant to me as I'm using Windows Authentication.

Creating SQL Server logins is a bit tricky and you need practice to understand what's going on.

There is an excellent article about it here.

In essence you need to consider the "server credentials" as well as the "database credentials". They are both different things.

I would normally run SSMS and go down to the bottom of my server instance where it's showing the Security folder (as explained in the above link). In there, expand it and right click to create a new Login. Set up your LoginID and Passwords, and default the login to the database you're referring to. Next go to Securables, and choose the correct database, along with the Schema, etc.

Once that's done you'll notice the same login was created at the database's Security folder. Make sure you've given the new Login, all the right permissions to READ and/or WRITE to the database.

The reason you need to do what I've mentioned above, is so you have a correct and valid Login to the database, which is important. Creating a single Login directly inside the database's Security is not enough.

Then you can connect to the database from external sources (Excel, programs, etc) in many ways, using the right connection strings.


pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined below) but still got these errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

Steps to reproduce:

Install a fresh, vanilla Ubuntu 14.04 distro (I'm using Vagrant with this Vagrant box).
Install PostgreSQL 9.3, configure to allow local connections as PostgreSQL user "postgres" from any Linux user.
Create a test database. I'm just doing:

vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres postgres
psql (9.3.5)
Type "help" for help.

postgres=# create database mydb;
CREATE DATABASE
postgres=# \q
vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres mydb
psql (9.3.5)
Type "help" for help.

mydb=# create table data(entry bigint);
CREATE TABLE
mydb=# insert into data values(1);
INSERT 0 1
mydb=# insert into data values(2);
INSERT 0 1
mydb=# insert into data values(3);
INSERT 0 1
mydb=# \q

Create a backup of the database like so:

PGPASSWORD="postgres" pg_dump --dbname=mydb --username=postgres --format=custom > pg_backup.dump
Delete some rows out of the data table in mydb so we will be able to tell if we restored the data successfully.
Restore the database with:

PGPASSWORD="postgres" pg_restore --clean --create --dbname=postgres --username=postgres pg_backup.dump

The data is restored, but the pg_restore command in step 6 exits with status 1 and shows the following output:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

WARNING: errors ignored on restore: 1

I cannot just ignore this because I am running this command programmatically and need to use the exit status to determine if the restore failed or not. Initially, I wondered if this problem was because I put my database in public (the default schema). I reasoned that public would be created as a result of the --create option by pg_restore before the data was restored (which could conceivably try to create that schema as well since that is where my table is), but when I tried the above steps with my table in a different schema, the results were the same and the error messages were identical.

Am I doing something wrong? Why am I seeing this error?

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

  --clean
     Clean (drop) database objects before recreating them

and

  --create
   Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

  --clean
  Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

  --create
  Create the database before restoring into it. If --clean is also specified,  drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema  already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.


How to force/require TLS connections on SQL Azure You can make a secure connection to a SQl Azure instance following these instructions from microsoft. However, this only works when the user specifies it in the connection string. How do I set SQL Azure to reject any insecure connections, should a user forget to set the option.

"Regardless of the client library chosen, data is transferred using tabular data stream (TDS) over a secure sockets layer (SSL). Windows Azure SQL Database doesn’t support unencrypted connections. This will ensure data transmission is secure and can help prevent man-in-the-middle Jump attacks."

I don't know if this is true, I have it from "https://social.technet.microsoft.com/wiki/contents/articles/2763.windows-azure-sql-database-connection-encryption.aspx", but the connection was not secure (!!!!)


How can I exclude rows from two tables whose values are encompassed by each other? I have the following two tables, Table A and Table B.
What I'm trying to do is return a result set where:

If the start position (only) in Table B is between the start and end position of any row in Table A, then I don't want that row from Table B to be in the result set.

If any of the start and end positions in Table A are between any of the start and end positions of any row in Table B, then I don't want that row from Table A.

Here is Table A:

RID
StartPos
EndPos

7
45
77

7
118
130

7
197
212

7
218
235

Here is Table B:

RID
StartPos
EndPos

7
83
87

7
121
132

7
175
179

7
183
191

7
195
214

7
221
237

Here is my desired result set. You can see that 197 and 212 from Table A fall between the row in Table B that has 195 and 214, so that row from Table A isn't in the result set. You can also see that the start position 221 in Table B is between a range in Table A (218, 235), so that row from Table B isn't in the result set either.

RID
StartPos
EndPos

7
45
77

7
83
87

7
118
130

7
175
179

7
183
191

7
195
214

7
218
235

In sum, I want to exclude where:

TableA start pos and end pos are between any of table B's start pos and end pos.
TableB start pos is between any of table A's start pos and end pos.

Also, there are more than one RIDs in the table...
I thought (and still think) I could get this done using a FULL join (or maybe using APPLY), but I'm getting a little stuck, which is why I'm asking you all for some help on it!
Here is the T-SQL to generate the two tables:
CREATE TABLE #TableA(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 45, 77)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 118, 130)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 197, 212)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 218, 235)

CREATE TABLE #TableB(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 83, 87)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 121, 132)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 175, 179)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 183, 191)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 195, 214)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 221, 237)

SELECT *
FROM #TableA

SELECT *
FROM #TableB

According to you question, I think this is what you want:
select * from #TableB b
where not exists (
    select 1 from #TableA a
    where b.StartPos between a.StartPos and a.EndPos
    and A.RID = b.RID
)
union
select * from #TableA a
where not exists (
    select 1 from  #TableB b
    where a.StartPos between b.StartPos and b.EndPos
    and a.EndPos between b.StartPos and b.EndPos
    and A.RID = b.RID
)
;

Fiddle


Can't login as root and can't change root password I have a MySQL 5.5 server on Windows 2012r2.
The database was not performing well, so I generated an ini at Percona and replaced that wit my old ini.

The server is performing good now, but now I cannot login as root, although I am sure I have the right password.
Then I wanted to change the root password. But that did not work either.

Does changing the ini has influence on user passwords?

My old ini:

[client]
port=3306

[mysql]
default-character-set=UTF8

[mysqld]
max-allowed_packet = 64M
wait-timeout = 6000
slow_query_log_file = d:/mysql/slow-queries.log
long_query_time=10
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="D:/mysql/"
character-set-server=UTF8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=150
query_cache_size=0
table_cache=256
tmp_table_size=16M
max_heap_table_size=16M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=69M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=3M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=3076M
innodb_log_file_size=768M
innodb_thread_concurrency=8

My new ini:

[mysql]
# CLIENT #
port = 3306
socket = D:\mysql\mysql.sock

[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = D:\mysql\mysql.sock
pid-file = D:\mysql\mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE

# DATA STORAGE #
datadir = D:\mysql\

# BINARY LOGGING #
log-bin = D:\mysql\mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

# INNODB #
innodb-log-files-in-group = 2
innodb-log-file-size = 768M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 3076M

# LOGGING #
log-error = D:\mysql\mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = D:\mysql\mysql-slow.log

It looks like all localhost-account's don't work anymore.
There is one %-account and that is still working

I hope someone has an explanation why I cannot login as root anymore.
Or even better make the root login work again.

Remove skip-name-resolve in your my.ini, which prevents localhost to be resolved, restart mysql, login and change your root account to 'root'@'127.0.0.1' or 'root'@'::1' then turn on skip-name-resolve again and restart.
See MySQL --skip-name-resolve
Also have a look here How can I run with root@localhost privileges in MySQL?


MySQL 5.6 @ AWS RDS read IOPS plateaus lower than write IOPS I have an RDS MySQL 5.6 instance which plateaus ReadIOPS at around 6.5k. My WriteIOPS is most often lower, but sometimes higher than ReadIOPS, but has never entered such a rigid plateau.  The disk was gp2 750 GiB (2250 IOPS) a couple days ago, then gp2 1000 GiB (3000 IOPS), now io1 1000 GiB / 10000 provisioned IOPS, and the plateau level is the same. Instance type is db.r4.xlarge.

Here is a chart which shows the problem:

As strange as it seems, it seems like something in the MySQL side is capping reads. Have enabled all sorts of monitoring in the instance and nothing obvious jumps out.

Is there anything in MySQL itself which would somehow limit the reads (but not the writes)?

EDIT: table structure:

CREATE TABLE `position` (
  `id` binary(16) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `analyzed` int(11) DEFAULT NULL,
  `version` varchar(255) DEFAULT NULL,
  `machine_serial` varchar(255) DEFAULT NULL,
  `station_id` int(11) DEFAULT NULL,
  `accelerometer` varchar(255) DEFAULT NULL,
  `altitude` float DEFAULT NULL,
  `area_id` int(11) DEFAULT NULL,
  `coordinates` geometry DEFAULT NULL,
  `course` float DEFAULT NULL,
  `gps_status` varchar(255) DEFAULT NULL,
  `gps_date` datetime DEFAULT NULL,
  `original_speed` float DEFAULT NULL,
  `speed` float DEFAULT NULL,
  `analytics_determined_field_1` bit(1) DEFAULT NULL,
  `gps_precision` float DEFAULT NULL,
  `area_uuid` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `positioncreated_at_idx` (`created_at`),
  KEY `positionanalyzed_idx` (`analyzed`),
  KEY `positionstation_idx` (`station_id`),
  KEY `gps_date_idx` (`gps_date`),
  KEY `gps_date_station_id_idx` (`station_id`,`gps_date`),
  KEY `area_id_idx` (`area_id`),
  KEY `area_uuid_idx` (`area_uuid`),
  KEY `created_at_idx` (`created_at`),
  CONSTRAINT `FK_station` FOREIGN KEY (`station_id`) REFERENCES `stations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Reads are cached.  If your data is smaller than the buffer_pool, everything will be read once, then read IOPs will drop to virtually zero.

There are many writes -- write the data, update the index, the double-buffer, the undo log, the binlog, etc.  Still, there is some caching going on there.  See the "change buffer".

So, I am surprised that the Read IOPs are so high.  And I can't explain its flatness.  Please describe what your app does and how big the data is.

UUIDs

So, the problem stems from having the PRIMARY KEY(id) being a 'random' value, while doing reads in somewhat chronological order, or by station_id.

First, let's try to get rid of id.  Is there another combination of columns that is unique?  If so, it might be a better PK.

Probably, this is the best PK:

PRIMARY KEY(station_id, created_at  -- in this order
            , id   -- include only if needed for uniqueness
           ),
INDEX(created_at) -- for finding today's set of rows for analytics?

I assume there are only hundreds, maybe a few thousand 'stations'?

With PK(id), each INSERT is hitting a random spot in the table.  Given that the buffer_pool is only 10% of the size of the table, this means that 90% of the time there will be a cache miss.  Ditto for SELECTing.

With PK(station_id, created_at), there will be hundreds, maybe thousands of "hot spots", so the INSERTs will be more easily cached.  And even the reads, which need INDEX(created_at) will bounce around only those few spots, not bounce around the entire table.

If a block (16KB in InnoDB) contains 100 rows (a convenient Rule of Thumb), then the inserts and selects discussed so far will be inherently 90 times as fast as before.  (OK, it might be only 5-10 times as fast, but that is still  very good, correct?)

(I discuss the UUID problem here, but the cure in it depends on Type 1; you are using Type 4.  Instead, we cna take advantage of created_at.)

Another issue... I see several long strings in that table.  Do those columns contain the same strings over and over?  If so, you should seriously consider normalizing them.  Then you could replace VARCHARs with, say, 2-byte SMALLINT UNSIGNED (for up to 65K distinct values).  Shrinking the table will help performance, especially when it is bigger than the buffer_pool.

I see several redundant indexes; drop the extras.

INDEX(a), INDEX(a)  -- drop one of them
INDEX(a), INDEX(a,b)  -- drop (a), since the other one can handle its needs

Use NOT NULL when appropriate.

INT takes 4 bytes; use smaller numeric types where appropriate.


How to make a test Database grow in size by running transactions to test autogrowth factor? I have created a test Database instance which is currently having zero activity on it. I have set the auto growth parameter for this DB similar to our actual production DB which is 600 MB per day , max size unlimited, initial size 888329 MB. I want to automate or run transactions on this test db instance so that it triggers the auto growth everyday for bench marking purpose before applying the same to our actual PROD DB.
Can anyone suggest how to do it in the best possible way?

In order to grow a DB you do not need to produce "real" data. Anything that uses space on disk will suffice.

SQL Server does IO in 8kB chunks - a page. By making a table with a row size over half this (i.e. > 4kB) we ensure a single INSERT uses 8kB of disk. I'd propose adding a new table to your test instance. Define it thus:

create table dbo.SpaceFiller
(
    spacefiller_id  int  identity(1, 1) primary key clustered,
    data            char(4100) not null
);

Column data is what consumes disk. Note it is char not varchar. This ensures the whole 4100 bytes is allocated. The size of 4100 is somewhat arbitrary. It just needs to be over half a page in size.  The primary key column is there to make post hoc analysis a little easier, and because adding a surrogate key is a hard habit to break!

Now add rows:

insert dbo.SpaceFiller(data)
values ('');

The column data is defined as fixed length. Therefore, no matter what is actually written to it, it will occupy the defined 4100 bytes. So, to save typing, I just write an empty string. It may seem odd but, because it is a fixed length column, the same amount of space will be used. This is just how SQL Server stores fixed length columns.

Wrap this in a WHILE, or whatever your preferred mechanism is. As each iteration will consume 8k you can do the math to find how many cycles to perform. If the DB already has allocated but unused space that space will be used first. You can force it to use new, dedicated space by putting dbo.SpaceFiller in its own filegroup.

You can see how things are going:

exec sp_spaceused 'dbo.SpaceFiller';

If you want to capture the actual moment of growth you could start an Extended Event session, or use Profiler, or monitor the OS action.

To re-test, drop the table and shrink the DB. Better still, restore a backup.


Id column based on other columns I have a simple database with 2000 rows

create table [TableName](
    [variable1] [varchar](50) not null,
    [state] [varchar](50) not null,
    [date] [date] not null
)

That look like this

[variable1] [state] [date]
 A           UT      2015-01-01
 A           UT      2015-01-02
 B           UT      2015-01-02
 B           UT      2015-01-02
 A           TX      2015-01-01
 C           TX      2015-01-01
...

I want to add and identity column that would be based on variable1 and state so the table would look like this

[MyID] [variable1] [state] [date]
 1      A           UT      2015-01-01
 1      A           UT      2015-01-02
 2      B           UT      2015-01-02
 2      B           UT      2015-01-02
 3      A           TX      2015-01-01
 4      C           TX      2015-01-01
 ...

add [MyID] [int] identity(1,1) not null doesn't work, since it just gives each row a unique id.
I want each distinct value in variable1 column to have a unique id, but I want to also use state column incase several states have same value

Possible duplicate https://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio

It sounds like what you are wanting is a composite key.  There is a T-SQL example in the link provided.


Shrinking bigger databases Hello masters of MS SQL

I have a kind of a problem on my hands. We have quite big DB (5TB) and we've just "enabled" some software archiving which means that most of the data will migrate to other DB's. In other words, this big DB is now almost empty with data and will not grow anymore.

The issue here is I have a big file that I need to shrink (space problem). And I wonder how should I approach this problem? Shrinking will probably take quite a while and I am not sure what will happen if something goes wrong and if it's OK to do backups in the middle of shrinking.

Can somebody help me with this issue and shine a little light on this matter?

This is current situation (data is still being deleted, and I assume we will land at 300~500 GB size)

Thank you in advance

Shrinking will probably take quite a while

It will definitely with that size of data, unless by some fluke most of the free space is at the end of the file.

  and I am not sure what will happen if something goes wrong

The operation should be safe even if you experience a complete power-out and the server shuts down uncleanly, pages are copied then relinked as needed, then the old pages unallocated, in an atomic fashion.

  and if it's OK to do backups in the middle of shrinking.

You can take backups during a shrink, though they will take longer as there will be extra IO contention. You could stop the shrink operation and restart it after the backups are complete - it should pick up (more or less) where it left off.

Another option is to shrink in small increments when the server is otherwise quiet so that it doesn't conflict with your users either (i.e. if most of your user activity is between 0800UTC & 1900UTC and your full/diff backups and other maintenance like ETL processes usually happen between 2200 & 2330, perhaps running partial shrinks between 0100 & 0700 would be best. If your app has global reach then this is less useful as you won't have quite the same distinct busy/quiet periods.

Don't shrunk right down to the data size though - leave yourself enough space for some months worth of expected data growth. No point fragmenting the data more than you need to as the space saved will just get reclaimed soon enough.


How can I drop a stored procedure whose name is in a variable? Given a variable that contains a stored procedure name:

DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';

How can I drop the stored procedure?

To do this, you must use dynamic SQL, as the statement DROP PROCEDURE cannot be parameterized.

As a bit of an aside, it's a good practice to always schema-qualify object names. You didn't specify the schema name in the question (or say that the variable contains a complete dot-separated name), so I've gone ahead and extended what you asked for to include it.

DECLARE @stored_procedure_schema sysname = NULL;
DECLARE @stored_procedure_name sysname = N'some_stored_procedure_name';

DECLARE @sql nvarchar(MAX) =
    N'DROP PROCEDURE ' +
        ISNULL(QUOTENAME(@stored_procedure_schema) + N'.', N'') +
        QUOTENAME(@stored_procedure_name) + N';';

EXEC(@sql);

This doesn't do any checking to see if the procedure actually exists or not before it attempts to drop it, so you'll get an error if it doesn't exist. It's trivial to use sys.procedures to check for existence, so I didn't bother with that, and you didn't ask for it either.

To preemptively answer your comment on the other answers, QUOTENAME will correctly escape any object name such that it ends up surrounded by square brackets. Specifically, yes, this does handle single quotes (and square brackets) within the procedure name itself.


Execute query using daily timestamp (mysql event) I was trying to find a way for give users a daily "point" per online day.

So my plan was setup a MySQL event to run this query:

UPDATE users SET points=points+1 WHERE timestamp > "X"

(Where X is the timestamp running since the 12.01AM of the current day).
The problem is that I didn't find any way to change daily timestamp (or the X in query) automatically. There is anyway to do it?
Thank you.


check the progress of table upload-mysql I have a dataset of around 200GB.

The sample is shown below:

Posted a new song: "Ensaio Don`t back anger &amp; imagine- Stone Rocks " http://t.co/i9TqakT38t #music  460523675293196289  1308622848  0   0   Sun Apr 27 20:59:34 +0000 2014  nc  f   nr
Posted a new song: "Led Zeppelin- Black Dog Feat. RockSauro" http://t.co/Scdo6ncIc1 #music  460454112694263809  13086228298 0   0   Sun Apr 27 16:23:09 +0000 2014  nc  f   nr

This data is in a text file. I have created a table and importing this data into mysql by using the command:

mysqlimport --local -u root -pxxx twitter_data '/home/abc/alltweet.txt'

How can I check how much data has been loaded in mysql table. Actually this command is running since 10 hours and count(*) is giving me zero count.

I recommend you to import the text file in batches, that will allow you easier monitoring of the progress by committing from time to time. This can easily be achieved with tools like pt-fifo-split from Percona. The first example you can see on that page is what you should do, changing the cat for the mysqlimport.

The reasons you are seeing 0 rows is because you are importing to an InnoDB table and that means that its insert won't be seen until the process is complete. There are two way in which you can monitor the progress:

If you are using innodb_file_per_table (default on since MySQL 5.5), you should see an increasing in size table called something like alltweet.ibd inside the twitter_data directory on your datadir. You can check the location of your datadir by executing:

SHOW GLOBAL VARIABLES like 'datadir';

By default it is usually on /var/lib/mysql.

The size of that file will be roughly the size of the .txt file, maybe a bit larger (depending on the structure). That, and the progress in which it is growing can be an indicator of the time left.
Monitor the Handler_write status variable:

SHOW GLOBAL STATUS like 'Handler_write';

This will give you the number of rows inserted on your database. Assuming there is no other inserting activity, this can give you the number of inserts/second, which you can compare with the number of lines of the file.

As a reminder, both MariaDB and Percona Server have better defaults to show you the pending work remaining.

If you want to speed up the mass insertion using a .txt file, sometime ago I wrote a guide on how to speed it up. In summary:

Increase your transaction log size
Increase your buffer pool
Disable your double-write buffer
Use a recent version of MySQL
Split the file in several pieces and insert it in parallel


MySQL 8 not using Spatial Index? I have an issue where whatever I do I cannot get MySQL to use a spatial index.

A query like the following then takes 5 seconds!

Any way to force this to use the index at all?

WITH ranked_reports AS (
    SELECT
        station_id,
        raw_text,
        observation_time,
        RANK() OVER ( PARTITION BY station_id ORDER BY observation_time DESC ) order_rank
    FROM
        METAR
    WHERE
        ST_Distance_Sphere (
            geo_point,
        ST_GeomFromText ( 'POINT(51.85 -0.79)', 4326 )) <= 100 * 1609.34
    ) SELECT
    *
FROM
    ranked_reports
WHERE order_rank =1

Explain of query

+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 8       | const |     10 |   100.00 | NULL                        |
|  2 | DERIVED     | METAR      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 585814 |   100.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+

Table Structure

CREATE TABLE `METAR` (
  `station_id` varchar(5) NOT NULL,
  `station_iata` varchar(5) DEFAULT NULL,
  `observation_time` datetime NOT NULL,
  `latitude` float(255,2) DEFAULT NULL,
  `longitude` float(255,2) DEFAULT NULL,
  `raw_text` varchar(255) DEFAULT NULL,
  `temp_c` float(255,1) DEFAULT NULL,
  `dewpoint_c` float(255,1) DEFAULT NULL,
  `wind_dir_degrees` int(255) DEFAULT NULL,
  `wind_speed_kt` int(11) DEFAULT NULL,
  `wind_gust_kt` int(11) DEFAULT NULL,
  `visibility_statute_mi` float(255,2) DEFAULT NULL,
  `altim_in_hg` float(255,2) DEFAULT NULL,
  `altim_in_hPa` float(255,0) DEFAULT NULL,
  `sea_level_pressure_mb` float(255,0) DEFAULT NULL,
  `wx_string` varchar(255) DEFAULT NULL,
  `sky_condition` mediumtext,
  `flight_category` varchar(255) DEFAULT NULL,
  `three_hr_pressure_tendency_mb` float DEFAULT NULL,
  `maxT_c` float(255,0) DEFAULT NULL,
  `minT_c` float(255,0) DEFAULT NULL,
  `maxT24hr_c` float DEFAULT NULL,
  `minT24hr_c` float(255,0) DEFAULT NULL,
  `precip_in` float(255,0) DEFAULT NULL,
  `pcp3hr_in` float(255,0) DEFAULT NULL,
  `pcp6hr_in` float(255,0) DEFAULT NULL,
  `pcp24hr_in` float(255,0) DEFAULT NULL,
  `snow_in` float(255,0) DEFAULT NULL,
  `vert_vis_ft` int(255) DEFAULT NULL,
  `time_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `geo_point` point NOT NULL /*!80003 SRID 4326 */,
  PRIMARY KEY (`station_id`,`observation_time`),
  KEY `IDX_Station` (`station_id`) USING BTREE,
  KEY `IDX_Deletion` (`observation_time`) USING BTREE,
  KEY `IDX_IATA` (`station_iata`,`observation_time`) USING BTREE,
  SPATIAL KEY `IDX_Spatial` (`geo_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Your query has

WHERE
    ST_Distance_Sphere (
        geo_point,
    ST_GeomFromText ( 'POINT(51.85 -0.79)', 4326 )) <= 100 * 1609.34
)

In MySQL you must use the MBR functions to use the index.

So don't use ST_Distance_Sphere instead,

MBRContains( ST_Buffer( ST_SRID(POINT(51.85 -0.79), 4326), 100 * 1609.34), geog )


Optimize Query in MySQL I've following tables with respective fields

1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data
2) wi_individual_p(ind_id,prg_id,...) Around 250K data
3) wi_training(trn_id,trn_start_date,trn_deleted,trn_beneficiary_type,...) - Around 25K data
4) wi_indv_training(ind_id,trn_id,is_deleted) - Around 450K data
5) wi_district(dst_id,dst_name) - Around 75 data

I was required to define a query to report unique individuals who are participated in the training within the given ranges of training dates (dst_name wise). So, I created following QUERY to fetch the records

SELECT
wi_district.dst_name,
COUNT(DISTINCT (CASE
        WHEN wi_training.trn_start_date BETWEEN '2014-07-01' AND '2015-06-30' THEN wi_individual_g.ind_id
    END)) AS y3,
COUNT(DISTINCT (CASE
        WHEN wi_training.trn_start_date BETWEEN '2013-07-01' AND '2014-06-30' THEN wi_individual_g.ind_id
    END)) AS y2,
COUNT(DISTINCT (CASE
        WHEN wi_training.trn_start_date BETWEEN '2013-02-01' AND '2013-06-30' THEN wi_individual_g.ind_id
    END)) AS y1
FROM
wi_individual_g
    INNER JOIN
wi_individual_p ON wi_individual_p.ind_id = wi_individual_g.ind_id
    AND wi_individual_g.ind_is_recepient = 'yes'
    INNER JOIN
wi_district ON wi_district.dst_id = wi_individual_g.ind_district_id
    AND wi_individual_g.ind_deleted = 0
    INNER JOIN
wi_indv_training ON wi_indv_training.ind_id = wi_individual_g.ind_id
    AND wi_indv_training.is_deleted = 0
    INNER JOIN
wi_training ON wi_training.trn_id = wi_indv_training.trn_id
    AND wi_training.trn_deleted = 0
    AND wi_training.trn_beneficiary_type = 2
    AND wi_training.trn_start_date <= '2015-06-30'
GROUP BY wi_district.dst_name

The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE. This query is taking around 4 mins to execute and fetch the records. How can I apply other optimizations to the present query? Please provide me some solutions in MySQL!

The EXPLAIN for the above QUERY is:

The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE.

Please learn about "compound indexes".

Please provide EXPLAIN SELECT ....

AND wi_training.trn_deleted = 0
AND wi_training.trn_beneficiary_type = 2
AND wi_training.trn_start_date <= '2015-06-30'

Begs for the compound INDEX(trn_beneficiary_type, trn_deleted, trn_start_date).

A compound index is almost always better than Using intersect(...).

(Style recommendation:  Don't prefix all columns with the same thing; it unnecessarily clutters the SQL.)


How to remove all hidden parameters from Oracle Database How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended?

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'

Here are parameters:

_fast_full_scan_enabled
_gby_hash_aggregation_enabled
_like_with_bind_as_equality
_optimizer_push_pred_cost_based
_sort_elimination_cost_ratio
_sqlexec_progression_cost
_trace_files_public
_b_tree_bitmap_plans

Regards,

You'd need to add IS_SPECIFIED (or ISSPECIFIED) to the where clause, as some hidden parameters may be set by... other things.

A parameter can be removed from an spfile by issuing:

ALTER SYSTEM RESET "_some_hidden_parameter" scope = spfile;

You will likely have to stop and start the instance(s) to have the changes take effect.


How to enforce full integer (no floating point)? Why is the following allowed

INSERT INTO c VALUES (123.2,'abcdefghijklmnopqrstuvwxyzabcdef', 'abcdefghijklmnop');

When table contains

CustomerID      NUMBER(3,0) -- That's the 123.2 entry

In other words, total of 3 digits with 0 after floating point?

If number is not a way to go, how would you enforce full integer only (no floating point)

When you define a column as number(m,n) where m is larger than n and n is positive, the values you can store in it can have m-n digits to the left from period and n digits to the right after period. For example when you defined your column as number(5,3), these are valid values: 1,234, 12,345. If you attempt to insert a value with a precision higher than n, it is automatically rounded, for example 1,2345 is rounded to 1,235.

When you define a column as number(m,0) you can have m digits to the left before the decimal point, and the numbers you insert are automatically rounded.

When you define a column as number(m,n) where n is larger than m, the values you can store in it can have zero digits to the left from the period and n digits to the right from the period with n-m zeros right after the decimal point. For example, if you defined your column as number(3,5), you can have values like 0.00123 in it, but not 0.01200. If you insert the value with more than n digits like 0.001235 it's automatically rounded to 0.00124.

When you define a column as number(m,n) where n is negative then it can store m-n digits to the left from decimal point, there should be exactly abs(n) zeros to the left from the decimal point, and the number is rounded to the last abs(n) places. For example, if you defined your column as number(3,-1), you can store numbers with 3 - -1 = 4 digits before decimal point which should have zeros in the last abs(n)=1 places. You can have values like 280, 1230, and if you isert values with digits other than zero in the last abs(n) places, the numbers are automatically rounded, for example 536 will eventually be stored as 540, 1245 will be stored as 1250.


Avoiding a sort on an already clustered index for group by On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid:

select count(did), pid
from  T
group by pid

Here is the query plan:

GroupAggregate  (cost=21566127.88..22326004.09 rows=987621 width=8)
->  Sort  (cost=21566127.88..21816127.88 rows=100000000 width=8)
    Sort Key: pid
     ->  Seq Scan on tc  (cost=0.00..1442478.00 rows=100000000 width=8)

However, the table already has clustered index on pid.

Why doesn't Postgres simply scan the table and compute the group by? Why does it need to sort on pid again?

How can I force Postgres to use the clustered index for the group by?

As you have no WHERE clause, you (the query, that is) must scan the whole table to be able to compute the aggregate.  Once it has all the data, it is much easier to sort them directly than to go to an index and match the rows and return them in order.  This latter would involve further I/O which is slow compared to operation in the memory.

If you were on PostgreSQL 9.2 (or above) and you had an index on (pid, did) then the optimizer might have chosen an index scan instead.


Can spatial index be a part of a multiple columns index? This manual over here is extremely unclear and doesn't even provide some sample SQL statements:
http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html

Another way to rephrase the question is the following:

We know we can have an index with multiple columns. What about if the indexes of those columns are of different type? Say the first column is spatial, the other is fulltextsearch, etc. Can we do so in mysql? (Bonus: can we do so in mongodb, if you happen to know)

Say you have a myisam table

It has a LATLONG column that contains points

It has a FULLTEXT column that contains words in the "business"

You want to query by LATLONG first, and then within the matching LATLONG you want to filter based on FULLTEXT column.

I suppose you will need multiple column index.

But what is the SQL command?

As we know, mysql will always use fulltextsearch index first if possible.

This query:

SELECT BusinessID as ID ,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
use index (LatLong_2)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('sea*' IN BOOLEAN MODE)
    ORDER BY
  Distance
LIMIT
  0, 45

Takes a long time, while this query:

SELECT BusinessID as ID ,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
use index (LatLong_2)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('sea*' IN BOOLEAN MODE)
    ORDER BY
  Distance
LIMIT
  0, 45

is faster because I tell mysql to use latlong_2 index instead, which is a spatial query.

Well, say I want to have a multiple column index. Latlong_2 and FULLTEXTSEARCH. They are off different type. LatLong_2 is spatial and FULLTEXTSEARCH is a fulltext search index. What SQL command I should run?

No, you can't.

An index, in MySQL, will either be

a BTREE single or multi-column index.
a HASH index (available only for MEMORY tables), that can span several columns.
a SPATIAL index (available only for MyISAM tables).
a FULLTEXT index (available for MyISAM and 5.6.4+ InnoDB tables), that can span several columns of CHAR, VARCHAR or TEXT type.

You can't combine these types.


Refactoring subquery to JOIN and CROSS APPLY, get only one row for each record in parent table Given the following query:

SELECT
  p.ProductName,
  CASE
    WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
    THEN
      1
    ELSE
      0
    END
  AS HasImage,
  (SELECT Sum(StockBalance) FROM Product WHERE ProductSuperID = p.ProductSuperID) AS StockBalance,
  CASE
    WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND Price IS NULL)
    AND EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND DiscountPrice IS NULL)
    THEN
      0
    ELSE
      1
    END
    AS HasPrice
FROM ProductSuper p

-- SCHEMA
CREATE TABLE ProductSuper
(
  ProductSuperID int,
  ProductName varchar(255)
)
CREATE TABLE Product
(
  ProdID int,
  ProductSuperID int,
  HasImage bit,
  StockBalance int,
  Price decimal(10,2),
  DiscountPrice decimal(10,2)
)
INSERT INTO ProductSuper
  (ProductSuperID, ProductName)
VALUES
  (1, 'Product 1'),
  (2, 'Product 2')
INSERT INTO Product
  (ProductSuperID, HasImage, StockBalance, Price, DiscountPrice)
VALUES
  (1, 0, 10, 10.00, 9.00),
  (1, 0, 0, 10.00, 9.00),
  (2, 0, 10, 10.00, 9.00),
  (2, 0, 2, 10.00, 9.00),
  (2, 1, 5, 10.00, 9.00)

I want to learn how could I best rewrite it to use either JOIN or CROSS APPLY, if anything to avoid some code duplication. I tried writing a JOIN-based version (and one with APPLY) but I'm getting more than one result for each row in the ProductSuper table, whereas I only want one row.

i.e. expected result:

+-------------+-----+-----+-----+
|  Product 1  |  0  |  10 |  1  |
+-------------+-----+-----+-----+
|  Product 2  |  1  |  17 |  1  |
+-------------+-----+-----+-----+

(I'm aware that for this particular piece of code there is little benefit from rewriting, since the subqueries are fast. But still, this is only an example.)

Thanks.

I'd probably structure this query as below

WITH ProductDetails
     AS (SELECT ProductSuperID,
                HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                StockBalance = Sum(StockBalance),
                HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
         FROM   Product
         GROUP  BY ProductSuperID)
SELECT p.ProductName,
       HasImage = ISNULL(pd.HasImage,0),
       pd.StockBalance,
       HasPrice = ISNULL(pd.HasPrice,0)
FROM   ProductSuper p
       LEFT JOIN ProductDetails pd
         ON p.ProductSuperID= pd.ProductSuperID;

As CROSS APPLY it could be written like this

SELECT ps.ProductName,
       pd.HasImage,
       pd.StockBalance,
       pd.HasPrice
FROM   ProductSuper ps
       CROSS APPLY (SELECT HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                           StockBalance = Sum(StockBalance),
                           HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
                    FROM   Product p
                    WHERE  p.ProductSuperID= ps.ProductSuperID) pd


Want to know details and meaning about the trace of SQL Server I referred to this article: sp_trace_setevent (Transact-SQL).

And trace my SQL Server on production machine for months.

EventId of most of the traces were: 10，12，14，17

The MSDN says:

10 RPC:Completed
12 SQL:BatchCompleted
14 Audit Login
17 ExistingConnection

Please tell me what do they mean in the performance perspective. Which ones should I keep an eye on and examine in detail. Or am I missing something. Thanks!

You appear to have setup a trace with no understanding of why you've done so. There is little point in running a trace for 10 minutes, let alone months, without a reason.

  Scenarios for using SQL Server Profiler

  The first step in using SQL Server Profiler is to identify your
  reasons for monitoring an instance of SQL Server.

The most common scenarios (taken from the above link) which may be relevant to you are:

Find the worst-performing queries.
Monitor stored procedure performance.

A trace that captures RPC:Completed and SQL:BatchCompleted events (Stored Procedure and TSQL classes) with Duration, Reads and CPU data columns will provide you with a diagnostic log of how your queries are performing. You can import this trace file to a table and run queries against it to identify the slowest, highest IO or highest CPU consumers for example.

If you have direct access to the server and want to analyse performance, I'm inclined to suggest that you ignore profiler traces and start with the SQL 2005 Performance Dashboard Reports. When you've grasped the basic concepts of Troubleshooting Performance Problems, invest some time understanding Dynamic Management Views (DMVs) and how they can help with performance analysis.