How can I backup a large SQL Server database without impacting performance? We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job.

The database itself is about 300GB, and the backup job starts at 4:30am and doesn't finish until a little after 7:00am. The current syntax of our backup job is:

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\ is a partition on the server which holds both the databases and the database backups.

It should also probably be noted that this is a virtual server, not a dedicated standalone server. We started getting complaints about slowdowns during the backup process right after we switched to a virtual server, so I think it may be related.

Is there a way to run this backup job so it doesn't affect the query performance while its running?

We are using SQL Server 2005

Isolate backup I/O from the rest of the I/O on your system. While it may take longer and be more prone to hiccups, backing up over the network instead of to a local disk may help alleviate the direct impact on the instance. Even in a virtual machine you should be able to expose other storage so that SQL Server can write to a different I/O subsystem.
It will be unlikely to make any difference whatsoever but, if this is run from a job, why do you need to use the STATS option? Are you sure you need the other options (NOUNLOAD, NOSKIP, NOFORMAT)? I haven't done any extensive performance testing on the entire matrix of options, but IMHO you should only use the options that you know you need.
Run your full backups at a different time that doesn't interfere with regular user activity. This isn't always possible, but even in 24/7 operations there are peak and lull times.
If you move to 2008+ you can take advantage of backup compression (Enterprise on 2008, Standard or Enterprise in 2008 R2+). If you can't move to 2008 or don't have the adequate edition, there are 3rd party backup tools that will do compression for you, and they're all pretty good at it. Since 2008 I've been able to use native compression, and I have been quite satisfied with both speed and compression % there with no need to further invest in 3rd party. But with 2005 I remember having good success with Red-Gate SQL Backup; Quest LiteSpeed is also quite good, but I have no idea what kind of effort is being put into it since the Dell acquisition.
If your databases are in full recovery, you could go longer between full backups, trading that for the knowledge that if you have to recover to a point in time, you will potentially have more logs to restore. You could combine this option with Ali's suggestion below of taking diffs throughout the week - if you had to recover on Thursday, you would need to restore 1 full, 1 diff, and the logs since the diff.
If you have a lot of data that is stale and unchanging, you could consider hosting that in a different database with a different recovery plan, or at least on different filegroups, and split up your backup operations that way. If you have reference or archive data that can be made read-only, and if you are in simple recovery, you can move it to its own read-only filegroup, back it up once, and not have to include it in your nightly backups ever again. See these MSDN topics:

Performing piecemeal restores

Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)


Function which evaluates conditionally? I am using MS Access 2010 and trying to create a parameterized query on one table.

My table has many latitude/longtitude coordinates. I wish to pass one lat long point (two parameters) into a query and return all table records which are within a given distance.

The distance calculation is quite involved when working in spherical space.

I want to avoid executing the full spherical calc if it is obvious that the point being considered is not a suitable candidate (the vast majority of the table's candidates.)

Given that the MS Access function IIF() will evaluate everything despite suitability, as will switch(), etc. I would like guidance which would help me avoid executing the longer calculation. I can write VBA, but I do not know if calling a VBA function every time a record is considered would be very efficient.

Thanks!

Rather than calculating the distance between two points on the fly, you can narrow your selection by approximation.
Since we know that one degree of latitude is a nearly-constant 111km, a search for locations within 100km should rule out points greater than one degree of latitude distant.
Longitude is more complex because of the latitudinal delta (~111km per degree at the equator to 0 at the poles), but there are ways to approximate it. One method would be to simply calculate, in advance, the distance between degrees of longitude for each whole degree of latitude and store the result in a table. That table could be joined on the search target table using a rounded value for the input latitude.
Movable Type has a well-known, informative page dedicated to great circle navigation and related calculations.
Note: I realize this doesn't answer the actual question, but it does address the underlying need for a performant method of lat-long distance calculation, which I hope is helpful.


How secure is mongodb with elastic beanstalk and EC2 I have a questions.

I run MongoDB on one of instances of EC2 and application on Elastic BeansTalk.
I restricted access to DB: only application can access it.
DB instance has Elastic IP because it is replicated in different zones, so private IP is not an option.

Questions:

Can communication between application and DB be eavesdropped?
Should I implement SSL with mongo connection?

Thanks in advance to everyone!

If eavesdropping of traffic intra-cloud is a potential issue for you, you really should implement the connection over SSL.
You should also consider running all of your resources in a VPC (virtual private cloud). This allows you to isolate all of your EC2 resources in a private network that is isolated from the rest of the traffic. It also enables you to control where the points of connection to the external world are and how they behave.


Preventing MySQL index corruption I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen twice. The corruption also only happens on one index and the data itself seems fine.

The composite index in question spans two BigInt foreign key ID Columns on a table with 5M rows. I'm using InnoDB for all tables and running MySQL 5.6.23 on Amazon RDS. I looked to see if I could find anything in mysql-error but haven't found any entries in it.

To verify that the index corrupted, I run the following EXPLAIN:

mysql> explain SELECT * FROM  student INNER JOIN school ON school.id = student.school_id WHERE student.student_status_id IN (3, 4, 7) ORDER BY student.id desc LIMIT 0, 25;

+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys                                                   | key                       | key_len | ref                   | rows | Extra                           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | school     | ALL  | PRIMARY                                                         | NULL                      | NULL    | NULL                  | 3690 | Using temporary; Using filesort |
|  1 | SIMPLE      | student    | ref  | student_status_id_idx,school_id_idx,schoolStatusIndex_idx       | schoolStatusIndex_idx     | 8       | school.id             |  178 | Using index condition           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+

When it’s “fixed” the rows are roughly 48/1 instead of 3690/178

To fix it, it requires some combination of these 3 steps:

alter table student drop KEY schoolStatusIndex_idx; alter table student add key schoolStatusIndex_idx (school_id,student_status_id);
alter table school engine=innodb;  (rebuilds all indexes)
alter table student engine=innodb;

Still, this has me baffled. What could be causing this kind of index corruption and how can I prevent it from happening moving forward?

Here's the trimmed output of SHOW CREATE TABLE for student, student_status, and school:

CREATE TABLE `student_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `student_status_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_status_id_idx` (`student_status_id`),
  KEY `school_id_idx` (`school_id`),
  KEY `schoolStatusIndex_idx` (`school_id`,`student_status_id`),
  CONSTRAINT `student_student_status_id_student_status_id` FOREIGN KEY (`student_status_id`) REFERENCES `student_status` (`id`),
  CONSTRAINT `student_school_id_school_id` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2582686 DEFAULT CHARSET=latin1;

CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4350 DEFAULT CHARSET=latin1;

And some additional output that might be helpful:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+

I suspect that the MySQL query optimizer occasionally choose a sub-optimal path. I'm not certain why it would sometimes use the index and sometimes not, nor why it seemed related to the index in question.

I think the query itself could not be fully covered by the index (it included an ORDER BY that I did not cover). I made a change to the query to ORDER BY student.created_at and added an index on (created_at, school_id, student_status_id) and everything looks good now.


Understanding Oracle "LOG_ARCHIVE_START" parameter I've enabled ARCHIVELOG mode on a Oracle 12c multitenant instance:

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

I understand that REDOLOGS are correctly archived by finding files in the configured path:

[root@LNXORA12 CSCONT01]# find ./ -type f
[...]
./archivelog/2017_03_22/o1_mf_1_5074_df3kfxmb_.arc
./archivelog/2017_03_22/o1_mf_1_5075_df417blj_.arc
./archivelog/2017_03_22/o1_mf_1_5076_df4bqqmf_.arc
./archivelog/2017_03_22/o1_mf_1_5077_df4q73mk_.arc
./archivelog/2017_03_22/o1_mf_1_5078_df4syvms_.arc
./archivelog/2017_03_22/o1_mf_1_5079_df4ttzs7_.arc
./archivelog/2017_03_22/o1_mf_1_5080_df501hrb_.arc
./archivelog/2017_03_22/o1_mf_1_5081_df57w8rj_.arc
./archivelog/2017_03_22/o1_mf_1_5082_df5gx2r5_.arc
./archivelog/2017_03_22/o1_mf_1_5083_df5mhsvj_.arc
./archivelog/2017_03_22/o1_mf_1_5084_df5sj5rx_.arc
./archivelog/2017_03_22/o1_mf_1_5085_df5sk6so_.arc
./archivelog/2017_03_22/o1_mf_1_5086_df5sq6sr_.arc
./archivelog/2017_03_22/o1_mf_1_5087_df5x4mto_.arc
./archivelog/2017_03_23/o1_mf_1_5088_df629bv9_.arc
./archivelog/2017_03_23/o1_mf_1_5089_df6ommvm_.arc
./archivelog/2017_03_23/o1_mf_1_5090_df73j2tt_.arc
[...]

Reading on many docs here and here the automatic archiving process is enabled only if LOG_ARCHIVE_START is set to TRUE but mine is set to FALSE.

SQL> select value from v$system_parameter where name = 'log_archive_start';

VALUE
--------------------------------------------------------------------------------
FALSE

Are my REDOLOGS really get archived?

First of all, the documentation that you have referenced are not from Oracle 12c, one is from Oracle 9i and another one is not the official documentation.

Second of all, the parameter log_archive_start is deprecated in Oracle 10g, meaning no longer available in the next release.

  Starting with Oracle Database 10g Release 1 (10.1), the LOG_ARCHIVE_START initialization parameter has been deprecated. Archiving is now automatically started when the database is placed in ARCHIVELOG mode.

Database Upgrade Guide 10g

Finally, You can use the following query to select all the deprecated parameters.

SQL> SELECT name from v$parameter
     WHERE isdeprecated = 'TRUE' ORDER BY name;

Documentation: Deprecated and Desupported Features


Availability groups taking time to change states I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node, the one in the secondary went into resolving state and it took a long time for it to become a primary AG.

Does it have to do with networking issues? I also want to know if it is a good practice to have a separate network interface for the heartbeat network. When I go to the networks in WFSC I only see "cluster and client" and I assume it also includes the heartbeat.

I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node ...

Just as others have pointed out in the comments section, this isn't a good way of testing as you've nicely and cleanly taken the service down. I mean, it is a great test if your disaster case is "someone shutdown SQL Server nicely", however outside of that it doesn't help much.

Having a two-node cluster also isn't very helpful for testing HA (or production) as there are just two possible nodes. While you can argue the usefulness of this setup one way or another based on certain factors, you won't really get to experience AGs until you have 3 or more nodes. This is mainly to do with 2-node clusters being special cases since there are so little resources.

  ... the one in the secondary went into resolving state and it took a long time for it to become a primary AG.

Resolving, in AG terms, means it is trying to find and connect to the primary. Since you've shut it down nicely, it'll take some time (assuming the cluster has quorum and the failover policy has been met) to fail.

  Does it have to do with networking issues?

Nope.

  I also want to know if it is a good practice to have a separate network interface for the heartbeat network.

No such thing as a "heartbeat" network since Windows Server 2008+. The cluster will decide which route to take.

What do you really want?

If you want to see how long automatic failover takes then you'll first need to figure out what scenarios you'd like to test. Here are some examples:

OS Disk fails
Cluster partitioning
Data disk fails
Out of memory
Bugcheck
log disk fails
Overwhelmed disk
slow networking
windows patching
sql patching
server power failure

You can add more, but for each possible issue there will be a different amount of time it takes.

The amount of time is roughly:

total = Detection + Mitigation + Redo Queue drain + Undo

Each of the above will have different detection and mitigation times, but the redo and undo should be roughly the same for each test. So, at the end of the day, really you're testing detection times.

If you only care about how long it takes to do a failover, then run: ALTER AVAILABILIY GROUP MyAG FAILOVER on the new primary. Time it. That's how long it takes.


How can I find a view column's base table column? I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field in the application. To do this, I have to manually examine the DDL SQL of one or more views in order to understand the table columns, in order to match one view's table columns with another view's table columns. This manual task might require examination of four or five abstraction layers.

Is there a faster way to examine the metadata to find how a view column is calculated from its base table column? Is there a way to examine Oracle's execution plan to see the rewritten query, skipping the manual inspection of the intermediate views?

Starting with 12c, we have DBMS_UTILITY.EXPAND_SQL_TEXT.

You basically pass the query text through the first, input parameter, and receive the rewritten query back through the second, output parameter.

But since you are on 11.2, you can not use this yet. You can however collect the optimizer trace. If you already ran your query, and have its sql_id:

begin
  dbms_sqldiag.dump_trace
  (
    p_sql_id=>'1a2b3c4d5e6f',
    p_child_number=>0,
    p_component=>'optimizer',
    p_file_id=>'optimzer_trace'
  );
end;
/

Or if you do not know the sql_id and did not run the query before:

alter session set events 'trace[rdbms.sql_optimizer.*]';
-- run your query here, no need to wait for results, it just need to be hard parsed
alter session set events 'trace[rdbms.sql_optimizer.*] off';

These will generate a trace file that you can read and find the final, rewritten query in it.

To be honest, the output produced by all methods can be difficult to read once you have some complex queries, and the way to get them is not so trivial, so I usually end up reading and "parsing" the DDL statements manually...


Synchronising batch delete from multiple applications with advisory lock in Postgres We are moving our application from one-instance to multiple instances (all connected to the same Postgres database) in order to increase resiliency of one of our services, and I am now facing a synch issue between multiple instances of the app all trying to delete "old" data once a day (basically, "delete all rows since 30 days ago or more").

I am considering using an advisory lock in Postgres as a synchronisation token so that only one instance actually performs the deletion.

So this is how I envision it (assuming all instances run the exact same code): At, say, 4 AM each morning all 3 instances will try to acquire the advisory lock; only one will succeed initially, and it will perform the deletion of old data, while the other app instances will wait on the lock.

Once the first instance is done, the other instances will, in turn, acquire the lock but will find that there a no more eligible rows and return.

Please let me know if you see any issue with the above approach.

Manual locking is probably unnecessary.  When the second process to get to a row in order to delete it discovers that it is already in the process of being deleted, it will inherently block until that deleting transaction commits. So this should already yield the behavior you want.

It is possible (but I think unlikely) that the processes will choose different execution plans which delete rows in different orders, and deadlock against each other.  In that case, one will get served an ERROR, and the other will proceed.  You will still get an acceptable outcome if you retry the ERRORed process, or if you are sure this is the only source of deadlocks you could just ignore deadlock errors and trust that the winner of the deadlock did the job.  This is somewhat wasteful to have one process do a lot of work which just gets aborted and repeated, so you it might be worthwhile to use advisory locking to avoid that.  But I wouldn't bother until I had a demonstrated problem that needs to be solved.

Depending on the execution plan for the deletions, verifying that no rows need to be deleted might itself be a lot of work.  If you want to reduce that work, you could use "try" advisory locks to skip the work for all but the first session, but then if the first session fails before it finishes but after it has warned off the others, then the job just won't get done.  And if your app uses advisory locks for other purposes, you would want to make sure you don't get collisions on the lock keyspace.


PostgreSQL: Multiple Inheritance tables (one child to two parents) If I have a base table and 2 or more tables that inherit from it, child1 and child2, I can insert a record into a child table and it shows up in base as you would expect.
However what I want to know is, is it possible to have a record that is present in both child1 and child2?

As an example say base is vehicle, child1 is boat and child2 is car, And I want to insert an amphibious car (effectively both a boat and a car).

I know I could do this the normal way, by having relationships but in theory it could be neat if it's possible to do as described.

I advise never using INHERITS, even in simple single-inheritance cases
Not sure why you think this would not be possible.

I would certainly never do this butttt.... you have Multiple Inheritance too if you're really masochistic. From the docs on Inheritance

  A table can inherit from more than one parent table, in which case it has the union of the columns defined by the parent tables. Any columns declared in the child table's definition are added to these. If the same column name appears in multiple parent tables, or in both a parent table and the child's definition, then these columns are "merged" so that there is only one such column in the child table. To be merged, columns must have the same data types, else an error is raised. The merged column will have copies of all the check constraints coming from any one of the column definitions it came from, and will be marked not-null if any of them are.

Example

CREATE TABLE vehicle ( name text );
CREATE TABLE boat () INHERITS (vehicle);
CREATE TABLE car () INHERITS (vehicle);

--ewww,
CREATE TABLE boatcar () INHERITS (boat,car);
NOTICE:  merging multiple inherited definitions of column "name"

INSERT INTO boatcar (name) VALUES ('amphibious car');

TABLE boatcar ;
      name
----------------
 amphibious car
(1 row)

TABLE boat;
      name
----------------
 amphibious car
(1 row)

TABLE car;
      name
----------------
 amphibious car
(1 row)

TABLE vehicle ;
      name
----------------
 amphibious car
(1 row)


How to make slave is in sync with master in tungsten replication? we are using Tungsten master to slave replication in MySQL.
we have configured this setup very long back.
from last week onwards  slave is in lagging with master around 5 to 10 binary files.
we have checked the N/W connectivity it is fine b/w master and slave.

we are not understanding why lagging is keep on increase?

How to  make slave is in sync with master in tungsten replication?

You can try mk-slave-prefatch

Slave prefetch is an increasingly popular technique for speeding up native MySQL replication, with several tools already published to enable it, such as mk-slave-prefetch and Replication Booster.  Tungsten Replicator is now joining the fray.   This article explains how our implementation works, how to install and tune it, and how well it performs compared to unaided MySQL native replication as well as Tungsten parallel replication.

For more information on this please refer:
http://scale-out-blog.blogspot.in/2012/02/solving-replication-lag-with-tungsten.html


SQL Server slows down on one expensive query I have a Sql Server 2012 with lot of CPU and memory.
When I run a query with a bad plan the server slow down and all other query, even of other databases, become very slow.

When I run the query:
- the CPU remains under 14% - so is not a CPU problem
- there are not grant memory pending - so is not a workspace memory
- non problems on disk I/O - so is not a disk I/O problem
- PLE is 8000 - so is not a buffer pool problem

I notice that the query use a lot a workspace memory (probably reaching the 20% limit) and free memory goes near to 0.

I'm stuck.

Could so one help me?

When I run a query with a bad plan the server slow down and all other query,

That says they're contending for the same resource, and

  non problems on disk I/O

doesn't say much about why the disk wouldn't be the resource they're contending for.

Logically we know that a query with a "bad plan" is going to read a lot of data from the disk, perhaps scanning one or more tables, because the definition of a "good plan" is one that uses indexes and minimizes I/O.  So your first suspicion should be that query is bandwidth-limited, and your second guess should be that the I/O cache is being flushed by the bad query.

If you check your I/O queues in SQL Server when the bad query is running, I expect you'll see the other queries have non-zero queue lengths.


How to store static list of hashes in MySQL effectively? Problem: I have a list of pairs (md5_hash, id). I want to store the data in MySQL and I want to make queries like this

SELECT id FROM table WHERE md5_hash = <some_hash>

The number of pairs can be tens or hundred of millions and the number is static - i.e., I do not add new records. It is important for me to save disk space. The time is not so important in this case (if a lookup takes less than say 1 second it is okey).

My thoughts:

I started by creating a table where md5_hash is represented in this way:

CREATE TABLE `myTable` (
   `md5` binary(16) NOT NULL, ' the values are not unique but we can suppose they are.
   `id` int(10) unsigned NOT NULL
) ENGINE=MyISAM

so md5 hashes are represented as binary numbers instead of hexadecimal characters to save space. For the same reasons the columns are defined as "NOT NULL". I chose MyISAM engine because my tests show that MyISAM requires less disk space than InnoDB. In addition MyISAM tables can be compressed by myisampack utility.

Now the hard part comes. When I create an index on md5 column the index requires more disk space than the data itself! I tested it with 17 millions records and my table took around 300 MB of disk space and the index took about 330 MB of disk space. The size of the index is  crazy.

One idea is to provide the md5 hashes presorted to MySQL which should result in smaller index size. But I do not know how to do that.

Another idea is to divide myTable in several smaller tables in order to decrease size of the index. I tried mysql partitioning. However, the purpose of the feature is to improve query time and not disk space usage.

Do you have any idea how to decrease the required disk space usage?

Some options:

Use InnoDB. If not for these reasons, then for compression! With InnoDB you can:

ALTER TABLE my_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Replace 8 with 4 or 2 or 1 to (hopefully) get better compression. Since the table is static, I think this is a great solution for you (with strong compression, writes become slower, but you don't care about that).

Not only data is compressed - indexes also. I would suggest this is the easiest option you have.
Only index part of your column. You agree that the column is not UNIQUE but can be assumed to be. OK; is your index a UNIQUE index? If not, try to:

ALTER TABLE my_table ADD INDEX `md5_prefix_idx` (md5(8))

to only index first 8 bytes of the columns.
Try TokuDB, which is an alternative storage engine to InnoDB, and which has an amazing compression (I've seen data get 20 times smaller than InnoDB with TokuDB aggressive compression).


Is there a way to clone a row in a "maintainable" way (with PostgreSQL)? I often have to clone rows in my applications so I do it the long way :

INSERT INTO mytable (a, b, c)
SELECT a,b,c FROM mytable WHERE id=12;

(id is declared as a SERIAL PRIMARY KEY, 12 being the row to clone).

The thing is, when I add new fields to mytable I must remember to update the clone functions to also clone those new fields.

I would like to clone rows in a more maintainable way. Something like :

INSERT INTO mytable
SELECT *, id:DEFAULT FROM mytable WHERE id=12;

As I write it, I also think it would be nice to clone detail rows : if I have a detail/child table declared with referential integrity like this :

CREATE TABLE mychild (
  id serial PRIMARY KEY,
  parentid int REFERENCES mytable(id),
  a text
);

I could clone mytable and all childs in one command rather than :

clone mytable (the long way)
get the new id
clone all mychild, setting parentid to the new id

Is there anything in PostgreSQL (or extensions) that could help dealing with these cases ?

With the additional module hstore installed, there is a very simple way to replace the value(s) of individual field(s) without knowing anything about other columns:
INSERT INTO mytable
SELECT (t1).*  -- note the parentheses
FROM  (
   SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mytable', 'id'))::text) AS t1
   FROM   mytable t
   WHERE  id = 12
   ) sub;

All columns are cast to text and back in the process.
Closely related answer with detailed explanation and more (possibly a bit faster) alternatives:

Duplicate row with Primary Key in PostgreSQL

Clone parent row and all its children
That's more tricky. But based on the above, plus using a data-modifying CTE, there is a very elegant solution:
WITH ins_parent AS (
   INSERT INTO mytable
   SELECT (p).*  -- note the parentheses
   FROM  (
      SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mytable', 'id'))::text) AS p
      FROM   mytable t
      WHERE  id = 2
      ) sub
   RETURNING id
   )
INSERT INTO mychild
SELECT (c).*
FROM  (
   SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mychild', 'id'))::text)
            #= hstore('parentid', (SELECT id::text FROM ins_parent)) AS c
   FROM   mychild t
   WHERE  parentid = 2
   ) sub;

This uses the new parentid for all copies in the child table and assigns new serial values to the id column.


MySQL - Persistent values in my.cnf I have a small doubt regarding making variables persistent in Percona Server 5.6 in Linux.

I need to make log-bin-trust-function-creators persistent so that I need not make it to true everytime on instance restart.

Doubt is that in my.cnf, should I give

  log-bin-trust-function-creators=1

or

  log_bin_trust_function_creators=1

Currently I have set log_bin_trust_function_creators=1, and it doesn't take in effect after restart.

InnoDB buffer pool cache should be in bytes only or it can be in G (currently I have set 12G and it is not working). Should I change to bytes?
In my.cnf, does the order in which the variables are given matters? or it takes randomly? Say, I can give buffer pool in the last in [mysqld] and InnoDB buffer size as first - does it matter?

1) The format with dashes are for command line and with underscore in my.cnf and as global variable. Any of the following has to work (tested on Percona Server 5.6.16):

log-bin-trust-function-creators=1
log-bin-trust-function-creators
log_bin_trust_function_creators=1
log_bin_trust_function_creators

(since it's a boolean you don't need to specify =1)

2) You can use G, M, k just fine. MySQL will pick that up. If you have a wrong config it will complain won't just ignore it silently.

3) Variable order doesn't matter with the obvious exception if you specify something twice under the same section. In that case the one coming later will override the previous value:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_size = 2G

With this the buffer pool size will be 2G.

From what you described it seems your server is not picking up your my.cnf at all. Check if you have the files at the right place:

[root@server ~]# $(which mysqld) --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Or another alternative:

[root@server ~]# timeout 3 sudo -u mysql strace $(which mysqld) 2>&1 | grep stat|grep cnf
stat("/etc/my.cnf", 0x7fff9e681280)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=5454, ...}) = 0
stat("/usr/etc/my.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
stat("/var/lib/mysql/.my.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
stat("/var/lib/mysql/.mylogin.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)


Poor SQL Server performance when management studio closed I've noticed a strange behaviour of SQL Server 2008 r2 express:

usually my query execution time is ~650ms, but if I open Management Studio and run any simple query (for example SELECT * FROM Something), the execution time becomes ~40ms. If I close management studio this value returns to normal ~650ms

And no matter where the query was executed: from Management Studio or my application (it uses ADO.Net)

ARITHABORT setting has no effect

Why is this happening?

The database you are querying probably has "auto close" enabled.

The connection from SSMS stops the database closing. When you close SSMS, the database closes. A subsequent call requires it to be opened.

Auto close is the default for SQL Server Express and is not a good idea:

http://blogs.msdn.com/b/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-auto-close
https://stackoverflow.com/questions/706732/what-is-the-auto-close-option-when-creating-a-new-database-in-sql-server-2008


Service Broker Msg 33305 I restored a database from SQL Server 2014 Enterprise to SQL Server 2017 Standard.

The restore went through fine without any issues/errors.

I then run DBCC CHECKDB on the database and I get the following messages related to the service broker. Any help in understanding this is appreciated.

  Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 33305, State 1:
  The multicast message body row with reference 28671 should have
  reference count value of 0.

This may have been an existing problem with the database you backed up.

Do you need to preserve the existing Service Broker conversations?  Messages?  Broker Identifier?

If you don't need the Service Broker messages, or to preserve any of its conversations or place in a distributed broker topology, you might end all the conversations WITH CLEANUP, or ALTER DATABASE ... SET NEW_BROKER.

If you do need to fix this while preserving the other conversations and messages, you would probably need to open a support case, as there are really only a handful of people who can advise you on this.


Transaction Log is big! But simple recovery mode is chosen! I am wondering: We have a Database on our MS SQL Server 2012. The recovery mode is "simple".

The DB itself is about 2.7GB big. I have an MAX Size for Trn-Log configured (3GB).

Now i made a Maintenance Plan with "reorganize Index Task" --> "Update Statistics".

The strange thing ist that when i run the Maintenance Plan the Log of the DB in "simple" Mode grows and grows since the 3GB are filed and i get an error...

Why the Trn-Log is used even in simple mode? And why the 3 GB seems to be not enough?

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log.

There is no way to prevent SQL Server from writing to the transaction log.

This means that a large transaction can still expand the log, if the current size of the log is too small. The tasks in your maintenance plans are performing operations in the database which are being written to the log, which is causing the log to expand, hit the 3GB limit you have set and that generates the error.

Remove the 3GB limit on the size of the log and make sure that the drive your log is on has enough adequate free space for the log to expand, or (and this is what I recommend) expand your log file manually to a size that will avoid auto-growth.

Finally, even though your database is small, I would not recommend using maintenance plans. The tasks in them are not "smart" and will perform operations even if they are not needed.

For example, the REBUILD INDEX task will perform a index rebuild even if it is not required (the index's fragmentation is low). This will cause a lot of information to be written to the database's log file.

Have a look at other maintenance solutions, I use Ola Hallengren's scripts.
https://ola.hallengren.com/


PITR restore in postgresql I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows.

wal_level = archive
archive_mode = on
archive_command = 'copy "%p" "C:\\Users\\Suniel\\Desktop\\archive\\%f"'
archive_timeout = 3600 //for every hour
max_wal_senders = 1

Then, I took the base backup.

pg_basebackup -U postgres -D C:\Users\Suniel\Desktop\Postgresql_backup\pg_basebackup -F t -x

which gives the replication of existing data directory in the given location.

Then i created the recovery.conf file with following parameters.

recovery_target_time = '2016-07-05 04:00:00'
recovery_target_timeline = 'latest'
restore_command = 'copy "C:\\Users\\Suniel\\Desktop\\archive\\%f" "%p"'

I stopped the postgresql service and placed the recovery file under data directory of installed postgres location.

Then I started the postgres service which changes recovery.conf to recovery.done but when i view the tables records, i saw no changes.

But my log files seems like this with some error(i think).

2016-07-06 12:15:49 NPT LOG:  database system was shut down at 2016-07-06 12:15:08 NPT
2016-07-06 12:15:49 NPT LOG:  starting point-in-time recovery to 2016-07-05 04:00:00+05:45
2016-07-06 12:15:49 NPT LOG:  consistent recovery state reached at 0/19000098
2016-07-06 12:15:49 NPT LOG:  invalid record length at 0/19000098
2016-07-06 12:15:49 NPT LOG:  redo is not required
2016-07-06 12:15:49 NPT LOG:  selected new timeline ID: 19
2016-07-06 12:15:50 NPT LOG:  archive recovery complete
2016-07-06 12:15:50 NPT LOG:  MultiXact member wraparound protections are now enabled
2016-07-06 12:15:50 NPT LOG:  database system is ready to accept connections
2016-07-06 12:15:50 NPT LOG:  autovacuum launcher started

I must be wrong somewhere in these steps. What did i miss? Am i wrong in these procedures?

Thanks in advance.

PITR restores forward, not backward.  You must use a base backup which was taken before the point in time to which you want to recover.


SSIS 2008 Package Configurations being ignored With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time values instead of using the settings from the config file.

I'm not quite sure I understand HOW to get the external config file to be used at all.  I've read articles that say that only design-time configurations that are set will overwrite the load of the external file.  Does this mean I can change the variables to blank strings and then they will get overwritten?  I can't delete the variable completely!  What about integers?

I've seen articles that mention turning OFF using package configurations in the package.

I can use the SSIS Package Editor or an XML editor to change the configuration file path in the package, and then it will use that file's settings "last" (regardless of the external /ConfigFile option), but I don't want to be changing the package.  I want one package with Test.dtsConfig and Production.dtsConfig and be able to swap back and forth without changing the package.

What is the recommended way to do this now?

You must take into account that when running by BIDS the package will take first the variable value from the config file, and only if the config file doesn't exist, it will throw a warning and the value will be taken from the package.

Now, the situation in command line is a bit different. You can have the following situations:

run the package in cmd line without any config file chosen:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx"

if the original config file (let's name it Prod) doesn't exist in the same path defined in the metadata of the package, values from inside the package are used and you'll just receive a warning that
config file is missing;
if the original config file exists and is valid, then values from the config file will be used (inner values will be bypassed);

run the package in cmd line without any config file chosen, but with variable set in the call:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /SET \Package.Variables[checkMe];"outside the package in cmd line"

if the original config file doesn't exist, then the value is taken from the /SET package call;
if the original config file does exist, then the value is taken from the config file and even the /SET is ignored (this is used only in the case above);

run the package in cmd line with a new config file (let's say DEV instead Prod):

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig"

if the new config (Dev) file exists, and old (Prod) doesn't, then values from it are used;
if both the Dev and Prod config file exist, then only values from Prod is used (DEV is bypassed even if specified in command line call);

run the package in cmd line with a new config file and a SET statement in the call:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig" /SET \Package.Variables[checkMe];"outside the package in cmd line - DEV config"

if both config files exist, Prod will be used, all others ignored, even the SET;
if no config file exists, SET value will be used;

So, in short, if you want to use a new config file you'll have to rename/move the old one and call the package with /configFile.
If that's not enough and want to override even new config file, then use the /SET variable.
Or you can bypass any config file and just use /SET statements in the batch call.

Hopefully that will shed some light in your possibilities.


Do MySQL optimizations hold for MariaDB also? I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in smaller projects, but the field of bloody optimization is quite new to me, admittedly.

To prepare for the project and not drive it into the ground, I'd like to learn about database performance optimization first. I'd like to know if I (or my employer) bought a book about optimizing MySQL performance, would it apply to MariaDB similarly?

My intuition tells me, that most of the MySQL optimizations would apply to MariaDB too, for they change quite a huge portion of their code base, but to avoid wasting money by buying books which we don't need, I'd like to clarify this. Vice versa a book only applicable to MariaDB would be useless to us if we tried optimizing old products. Unfortunately - as far as I know - there are no books dedicated to MariaDB performance yet. Does this hold?

Yes, all the basic optimizations in MySQL 5.5 apply to MariaDB 5.5.

However, starting with MariaDB 10, a real fork, not all improvements in 5.6 are in MariaDB codebase, and alternatively, MariaDB has some exclusive features (Hash JOINS). For now, those are not too different, that may change in the future. For a book, where most of the optimizations are regarding basic indexing, it may not be very relevant.

And you can always check the latest improvements online -blogs, wikis. This is a presentation I created for the newest MySQL 5.6 query optimizations techniques.

Have a look at this image for a visual representation (white zones are MariaDB-only). 5.6- and 5.7-only are not represented:


Replication - Initialize with Snapshot when using filtered rows We have a large database that is being Transactionally replicated (one way) between sites.

The Publication has a date based filter so that it doesn't replicate old data.

I need to add a new subscription to this publication, but don't have the capacity in the transaction log at the new Subscription server to Snapshot all the data and it will take so long to copy the full snapshot over the network that the Subscriber will be millions of transactions behind after initialization.

My question is: does the filter apply to the snapshot?

For instance, the database and all data is on the new subscription server (via Backup/Restore). The filter on the publication (Filter rows) is set to > 2011 on a date field. If I set snapshot going to initialize the subscription is it going to:

Create a snapshot of all data in the Publication articles that is greater than 2011

OR

Create a snapshot of all data in the Publication

Further, if 1 is true, then when it applies the snapshot at the subscriber will it remove all data already in the table (so data added via the initial Backup/Restore operation will be gone. Leaving me with ONLY data > 2011?)

If you can't tell I need all the data in there from the Backup (the pre 2011) but need the snapshot to be small enough that it can initialize the sub within a sensible timeframe (therefore hopefully only the > 2011 data in the snapshot).

Thanks for any assistance.

A lot of testing later, I have the answers.

Does the filter apply to the snapshot?

Yes.

So out of my numbered list, 1 is correct. The reason I didn't really believe this to be true is because when quickly testing this I forgot about the option in Article Properties that controls how the Snapshot is applied. This also made me ask the question after the numbered list beginning 'Further'. The answer to that paragraph is: it depend how it is set in Article Properties.
To explain this more fully:

Right click your Publication in SSMS > Properties > click Articles in the left tab > Article Properties button (pick either option that is relevant to you).
Look at the Destination Object section > Action if name is in use

This option controls (for this or ALL articles) what the Agent does when applying the Snapshot to the Destination database. As Default it Drops and recreates the object (this is why it seemed to me to be replacing all the data - essentially it was because it was dropping the object, creating the object, BCP'ing the data in (so anything previously in the table is gone)).

I needed "Delete data. If article has a row filter, delete only data that matches the filter" as this therefore kept the data already in the table that was not > 2011.

Hopefully someone in the future can save some time testing by finding this answer! I'm slightly surprised that the default option is to Drop and replace the object to be honest, but I suppose that fits most people's purposes for Snapshot.
So someone can find this answer if they are searching google I shall reproduce the question as I would google it:

When initializing replication from Snapshot on a publication with Filtered rows, does it apply the filter to the snapshot it takes?


SQL query optmization This is one thing I have been trying to read from few days. I am not getting any satisfactory explanation anywhere. Tried surfing some blogs and videos in youtube.

Appreciate if anyone one can throw some light on this.

What are the query optimization techniques in SQL Server or how to optimize a SQL query in SQL Server?
If we have a query which is 2-3 pages long; what are the first steps to take for optimizing the query?

Faced this in one of the interviews and couldn't give a proper answer other than indexing.

Indexing is always the first port of call. Ensure that you have the proper indexes in place before anything else.

Next run the query and study the actual execution plan which will give some much needed pointers.

http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

Finally these tricks can give a well needed performance boost and most if not all are good practice anyway:

Ensure All tables have a primary Key.
Ensure All tables have a relevant clustered Index.
Ensure All Tables have the appropriate non clustered index. Specifically all columns referenced in the Where / Order By and Join clauses should be covered.
Avoid Views where possible.
Do not use SELECT *
Ensure all of your query is relevant especially where it joins to other tables.
Avoid Cursors unless absolutely necessary.


SSIS Transaction Level for Single Task I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback.

Do I just set that task to REQUIRED? Or leave it as SUPPORTED?

Thanks!

SSIS Transaction Level

The default transaction level for all tasks and containers is Supported which means they will enlist in an transaction if available but they will not create one..

A Required settings will start a transaction if one does not exist and enlist in an existing transaction.

NotSupported indicates that the Executable/Container will ignore any existing transactions which may result in deadlocks depending on your design.

Your case

You have 3 Tasks and the desire is to have the third item behave in an atomic way. The answer is, partially, that you'll want to set the Transaction option to Required. Partially because you can commit throughout your data load. In an OLE DB Destination, if you have a non-zero commit size then the committed data is fully committed. A rollback isn't going to pull that data back out.


Is there a way to synchronize mysql databases that are missing a few days worth of data I have a primary-primary MySQL server setup that is connected to a load balancer. At one point, a failure occurred to where the replication wasn't happening and data was being written to one server and not the other. We have since fixed the replication issue. However, both servers are missing data that the other server has due to the load balance switching.

Is there a way to sync the missing data between both devices?

This may be your best bet:
https://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

However, if you have inserted the same unique key (or AUTO_INCREMENT value) on both masters, there will be a mess.


How should I select a primary key in my use case I have an app wherein I want to give offers to certain devices which fall in range of IMEI numbers (I have a separate algo to calculate this range).
So I want to create a structure in which I have a column as IMEI and next column as status, which can be true or false.
Now for those devices which have more than one IMEI I am pretty confused how I should structure my database?
I am expecting transactions which can rise up to a million inserts per 5 hours once the API is released and in the above table my use case is that of transactional database, which helps me generate analytics.
Two solutions come to my mind:

Make a mix of IMEI numbers seperated by a hash and set it as primary key.
Flaw: The IMEI numbers I receive from the client side are not always guaranteed to be in the same order, so maybe I will have to use a like query on the primary key thus created, slowing my transactional database yet again.

I create a new record for each IMEI and set it as true. Which means upon selecting I have to run queries on multiple primary keys with a where clause.

Please advise

Since a device can have more than one IMEI number, that is obviously not the primary key of the devices. Is there no existing attribute, such as serial number, that can serve as unique identifier? If not, then you probably need a devices master table with one entry per device and a generated ID for the primary key. This value would then be used in the transaction table along with the IMEI number. Something like
Table: Device
   DeviceID  int serial primary key

Table: DeviceIMEI
   DeviceID  int FK references Device.DeviceID
   IMEI      character(16)  primary key,
   Status    Boolean

The IMEI is defined as globally unique so can be the PK of DeviceIMEI, even though it may not be the only IMEI for its DeviceID.
One million transactions over 5 hours is about 55 per second. A modern relational DBMS can handle that.


PostgreSQL difference between VACUUM FULL and CLUSTER I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx index.

So to reclaim the space do I need to use cluster command or vacuum full command?

What is the difference between this two command ?
Is vacuum full order by some column same as cluster command?
Is index recreated in both the commands?
In my case which one will be faster?

The version of PostgreSQL database is 9.1

To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers.  I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting.

First, having run VACUUM FULL on the table fka, I took its size:

\dt+ fka
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description
--------+------+-------+----------+--------+-------------
 public | fka  | table | test     | 338 MB |

Then let's see the physical order of the data from the very beginning of the table:

SELECT *, ctid FROM fka ORDER BY ctid LIMIT 5;

 id  | col1 |  ctid
-----+------+---------
   2 | 2    | (0,1)
   3 | 3    | (0,2)
   4 | 4    | (0,3)
   5 | 5    | (0,4)
   6 | 6    | (0,5)

Now let's delete some rows:

DELETE FROM fka WHERE id % 10 = 5;
--DELETE 1000000

After this, the reported table size did not change.  So let's see now what CLUSTER does:

CLUSTER fka USING fka_pkey;

SELECT *, ctid FROM fka ORDER BY ctid LIMIT 5;

 id  | col1 |  ctid
-----+------+---------
   2 | 2    | (0,1)
   3 | 3    | (0,2)
   4 | 4    | (0,3)
   6 | 6    | (0,4)
   7 | 7    | (0,5)

After the operation the table size changed from 338 to 296 MB.  From the ctid column, which describes the physical place of the tuple in the page, you also see that there is no gap where the row matching id = 5 used to be.

As the tuples were reordered, indexes should have been recreated so that they point to the correct places.

So the difference looks to be that VACUUM FULL does not order the rows.  As far as I know, there is some difference in the mechanism the two commands use but from a practical point of view this seems to be the main (only?) difference.


Views for XML with attributes using XMlTable I created an XMLType table

CREATE TABLE PERSON OF XMLType
  XMLTYPE store AS OBJECT RELATIONAL
  XMLSCHEMA "PERSON.XSD" ELEMENT "root";

The XML is -

<root>
<Person empid="1" empname="Joe" empproj="IT" emporg="Consulting" />
</root>

I created view for the XMLType table. But I could not see the values inserted in table in the view. I want to have the XML attributes (empid, empname, empproj, emporg) as columns of VIEW person_master_view for table PERSON.

CREATE OR REPLACE VIEW
  person_master_view(empid, empname, empproj, emporg)
AS SELECT xt.*
     FROM PERSON,
                XMLTable ('/root/Person' passing PERSON.object_value
                                 columns
                                     empid        number(10)   path '/Person',
                                     empname      varchar2(30) path '/Person',
                                     empproj      varchar2(30) path '/Person',
                                     emporg       varchar2(30) path '/Person'
                             ) as xt

The following worked -

CREATE OR REPLACE VIEW
      person_master_view(empid, empname, empproj, emporg)
    AS SELECT xt.*
         FROM PERSON P,
                    XMLTable ('//Person' passing P.object_value
                                     columns
                                         empid        number(10)   path '@empid',
                                         empname      varchar2(30) path '@empname',
                                         empproj      varchar2(30) path '@empproj',
                                         emporg       varchar2(30) path '@emporg'
                                 ) as xt


Can I configure MySQL's typecasting to consider 0 != 'foo'? This article points out the following security problem with MySQL:

USERS
id | email            | password_reset_token
1  | one@example.com  | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx
2  | two@example.com  | CZor5t7WbX#LTeqiG3v@6f3@z#)BfK*n

Here we have nice, random tokens that a user must prove they have in order to reset their account. But the user manages to submit a reset token of 0, so we run this query:

SELECT * FROM `users` WHERE `email` = 'one@example.com' AND `password_reset_token` = 0

MySQL converts the token VARCHAR to an INT in order to make the comparison. It considers a VARCHAR that does not start with a number to equal 0. Therefore, an attacker can match any non-numeric string and take over the account.

Even if the reset token starts some numbers, the situation is not much better. MySQL ignores all characters but the initial numbers when making this comparison, so it considers 12blahblah3blah4 to be equal to 12, which makes guessing exponentially easier.

Can I configure MySQL not to do this kind of typecasting? If, for example, it cast INT to VARCHAR instead of vice versa, this attack would not work.

Note

If the query is run with '0' instead of 0, this doesn't work. The article discusses this vulnerability in terms of Ruby on Rails' acceptance of XML, where a type=integer property convinced Rails to send an actual integer in the query.

That bug has been patched in Rails; it now converts all request parameters to strings, so it would never build a query with an integer. But I still think MySQL should be configurable to prevent this.

The problem, though, is that you are supplying a numeric value, unquoted, so MySQL tries to cast the other side of the comparison as a DOUBLE, to compare it with the numeric argument you've supplied... which leaves you with a 0 that's equivalent to 0.
mysql> select 'foo' = 0;
+-----------+
| 'foo' = 0 |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

If you supply the 0 in a string context, this evaluates to false.
mysql> select 'foo' = '0';
+-------------+
| 'foo' = '0' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

So one option you could use to avoid this behavior would be by casting your input value as a CHAR... which, most of the time, it already is... but if not, it evaluates as you would expect.
mysql> select 'foo' = cast(0 as char);
+-------------------------+
| 'foo' = cast(0 as char) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

update, to more fully answer the question:

Can I configure MySQL not to do this kind of typecasting?

No, there does not appear to be any way to avoid this through configuration -- only through an explicit cast of your potentially-numeric-looking string -- to a string, which you've said can't be accomplished since you're using an ORM.  The most likely place, if something like this existed, would be SQL Server Mode, and none of those options modify this behavior when it comes to comparisons.
There's a little bit of discussion of implicit conversions like this in Bug #63112:

SELECT orange > banana;
Is this true or false? Well .. most often a banana is longer than an orange.  So if lenght is the criteria the above statement is false.  But most often an orange is heavier than a banana, so if weight is the criteria the above statement is true.
When comparing different types (orange versus banana, string versus integer) some 'criteria' or 'rule' will have to be used.  When MySQL compares different datatypes they are [both] cast to DOUBLE internally before comparison. The integer "1" casts to the double "1" - the string 'a' or 'UUID()' casts to the double "0".  [...]

Note that the scope of this bug report is not strictly limited to implicit casting in out context, and the mention of UUID() here is a bit of a distraction, since UUID() can return strings beginning with digits and cast to other numbers... but I think it does serve to confirm that no alternative exits, since nothing is presented other than "don't use different types for comparison if you want to avoid a cast," which we already know.  It continues:

To avoid casting to DOUBLE internally ensure that the values compared are same datatype. In your case the id columns are strings and the value you compare with should also be strings (not 1 but '1' - not 2 but '2' etc.) or use cast()/convert() in your statement [...]

Regarding your suggestion that if implicit casting is to be done, then the number should become a string instead of the string becoming a number... I can see your point, to a point, for strict equality comparisons... but there are many other operators besides = and using that logic, a whole new series of problems arises...
mysql> SELECT CAST(100 AS CHAR) < '2';
+-------------------------+
| CAST(100 AS CHAR) < '2' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

Correct!  The string '100' is "less than" the string '2' in an alphabetical sense... in the same way the string 'AXX' is "less than" the string 'B'.  So this alternative certainly has its down-sides.
There is, however, a third alternative, which seems like the most accurate logic (though I welcome a constructive objection): If a string that begins with a non-digit character is cast to a DOUBLE (or any other numeric type, for that matter), it seems like the most correct result of that cast would be NULL.
Unfortunately, this isn't the MySQL approach.  It seems like this could be fixed in the source, possibly in sql/field.cc where the '0' returned by my_strntod() appears to be simply returned to the caller after throwing the warning if an error occurred, but attempting such a hack this deep in the source is beyond my expertise.


Linux Volumes for SQL Server + Docker on Linux I am testing out a POC project for SQL on Docker containers on Linux and wanted some background info on how to request the storage on the Linux VM. The plan is to spin up 10 containers with SQL 2019 on storage of at least 10 TB
My question is specific to where the sql containers are stored within the Linux VM. This will help me to request storage capacity for that specific mount point accordingly
Thank you

When running SQL Server in a container you'll want to use either bind mounts or named volumes to persist the databases.
By using either bind mounts or named volumes what you're doing is separating out the data from the SQL instance so that if a container has an issue and needs to be removed, you can spin up another one and mount the databases into it.
Bind mounts are mounting volumes from the host into the container and named volumes are volumes created within docker that can be mapped from one container to another.
Both are specified by using the --volume flag in the docker container run statement.
By using these you can determine where on the host the SQL Server databases will persist and request storage accordingly.
An example of bind mounts: -
mkdir /mnt/mssqlsystem
mkdir /mnt/mssqluser

docker container run -d \
--publish 1433:1433 \
--volume /mnt/mssqlsystem:/var/opt/mssql/data \
--volume /mnt/mssqluser:/var/opt/sqlserver \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--env MSSQL_DATA_DIR=/var/opt/sqlserver \
--env MSSQL_LOG_DIR=/var/opt/sqlserver \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2019-CU6-ubuntu-18.04

What's happening here is that two directories have been created on the host and mapped to /var/opt/mssql/data (the location of the system databases) and /var/opt/sqlserver within the container. /var/opt/sqlserver is also the default location for the user data and log directories set by ENV variables.
An example of named volumes: -
docker volume create mssqlsystem
docker volume create mssqluser

docker container run -d \
--publish 1433:1433 \
--volume mssqlsystem:/var/opt/mssql \
--volume mssqluser:/var/opt/sqlserver \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--env MSSQL_DATA_DIR=/var/opt/sqlserver \
--env MSSQL_LOG_DIR=/var/opt/sqlserver \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2019-CU6-ubuntu-18.04

Here two named volumes are created and mapped to /var/opt/mssql/data and /var/opt/sqlserver within the container. You don't actually need to individually create the volumes, if they're specified in the docker container run statement and don't already exist, docker will create them.
You can see the location of the named volume on the host by running
docker volume inspect mssqlsystem
docker volume inspect mssqluser

The location where named volumes are created can be altered by using a docker volume plugin. More details are here: -
https://dbafromthecold.com/2018/05/02/changing-the-location-of-docker-named-volumes/
Full run throughs of using bind mounts and named volumes are detailed in The SQL Server and Containers guide here: -
https://github.com/dbafromthecold/SqlServerAndContainersGuide/wiki


Oracle Equivalent of SQL Server's Bitwise Operators I am trying to figure out all of the common bit-wise operations in Oracle.

In SQL Server we have some very simple bit-wise operators to use against a bit-wise value:

& - Evaluates if bit exists select 10 & 2   /* result=2 */
| - Add Bit (if doesn't exist) select 10 | 2   /* result=10 */
&~ - Remove Bit (if exists) select 10 &~ 2  /* result=8 */
^ - Toggle Bit (remove if exists, adds if doesn't) select 10 ^ 2  /* result = 8 */

In Oracle, I know we have a built-in bitand() function which is more or less equivalent to SQL Server &, and I have built an Oracle bit-wise OR function to mimic SQL server's bit-wise OR operation (|) as follows:

CREATE OR REPLACE FUNCTION BITOR (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
    BEGIN
       RETURN x + y - BITAND(x,y);
    END;

My question is: how can I achieve the SQL Server &~ and the ^ operations in Oracle?

Have you checked the manual?, it should help with your question to a large extent.


A date column in a table won't save the value after a correct date was entered I have a table with a column named Shipped Date. It is declared as a column of type DATE and retains NULL marks by default.

When I "edited" other columns in the table it worked, but when I entered in a standard date value such as '9/14/2017' into the Shipped Date column, the DBMS will let me enter it but it won't save it after I press TAB or ENTER.

I edited Shipped Date directly in the table. I also tried to enter "2017-14-09" and "September 09, 2017" into the column, but it still won't save it.

I put in the date with a format of "07-APR-9"8, and then I click the Commit button, and an error message popped up that said:

  Could not save file table ORDERS.

Tools/Preferences/Database/NLS/Date Format has DD-MON-RR.

Is RR supposed to be year? If I wanted it in month-Date-Year then I suppose that it will be MON-DD-RR. Is that correct? I did put it in the format of DD-MON-RR (15-09-17) but it still does not work.

I am using Oracle SQL Developer version 17.2.0.188.

Community wiki answer:

There are loads of options/possibilities. See Datetime Format Models in the Database SQL Language Reference.

For example, if you have it set to DD-MON-RR, you should use 15-SEP-17.

RR lets you store 20th century dates in the 21st century using only two digits, as described in The RR Datetime Format Element:

  The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

  If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

  That is:

  If the specified two-digit year is 00 to 49, then

  If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

  If the specified two-digit year is 50 to 99, then

  If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
  If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.


T-SQL query must include and exclude child rows I have simple parent -> child (one to many) relationship. Need to return children that have all given attributes, in this case red hair and brown eyes.
Table:
CREATE TABLE [dbo].[children](
    [child_id] [int] IDENTITY(1,1) NOT NULL,
    [parent_id] [int] NOT NULL,
    [attribute] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_children] PRIMARY KEY CLUSTERED
(
    [child_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Data:
insert into children (parent_id, attribute) values (1, 'red hair');
insert into children (parent_id, attribute) values (1, 'blue eyes');
insert into children (parent_id, attribute) values (2, 'red hair');
insert into children (parent_id, attribute) values (2, 'brown eyes');
insert into children (parent_id, attribute) values (3, 'purple hair');
insert into children (parent_id, attribute) values (3, 'brown eyes');

Desired Result:
parent_id
2

Semi Working Query:
select parent_id

from children

where attribute in ('red hair','brown eyes')

group by parent_id

having count(child_id) = 2;

That query works, however I would think there is a better way, (maybe not?) where I don't have to provide a count of the given arguments.
Update: Previous question and example were overly complicated and not communicated well. Hopefully this simplifies the issue.

Alright, round three!
So, it looks like you're trying to find any parents that match all given criteria, correct? I'm assuming you're looking to use this in some real world scenario (a report or application). If that's the case, you'll need to pass in the criteria using a parameter.
The code here is going on that assumption.For this to work correctly, you'll need a function to split the parameter string. STRING_SPLIT function is not supported until SQL 2012, so if you're working in 2008, you'll need this function.
SET QUOTED_IDENTIFIER ON;

SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.SplitString
(
    -- Add the parameters for the function here
    @myString     VARCHAR(500)
    ,@deliminator VARCHAR(10)
)
RETURNS @ReturnTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    id     INT         IDENTITY(1, 1) NOT NULL
    ,value VARCHAR(50) NULL
)
AS
BEGIN
    DECLARE @iSpaces INT;
    DECLARE @part VARCHAR(50);

    --initialize spaces
    SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0);

    WHILE @iSpaces > 0
    BEGIN
        SELECT  @part = SUBSTRING(@myString, 0, CHARINDEX(@deliminator, @myString, 0));

        INSERT INTO @ReturnTable
        (
            value
        )
        SELECT @part ;

        SELECT  @myString = SUBSTRING(
                                         @myString, CHARINDEX(@deliminator, @myString, 0) + LEN(@deliminator)
                                         ,LEN(@myString) - CHARINDEX(' ', @myString, 0)
                                     );

        SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0);
    END;

    IF LEN(@myString) > 0 INSERT INTO @ReturnTable   SELECT @myString;

    RETURN;
END;
GO

Then, this query should give you the results you're looking for.
If you're using SQL 2012 or higher, you can replace the function (dbo.SplitString) with the built in windowed function STRING_SPLIT and achieve the same results.
DECLARE @ParmList VARCHAR(MAX) = 'red hair,blue eyes';

SELECT  c.parent_id
FROM    dbo.children AS c
WHERE   EXISTS (
                   SELECT   1
                   FROM     dbo.SplitString(@ParmList, ',') AS at
                   WHERE    at.value = c.attribute
               )
GROUP BY
        c.parent_id;


Why does REGEXP_REPLACE concatenate with a previous result? This looks like strange behaviour, and I get the same on the MySQL command line as via db<>fiddle:

create table t(foo varchar(255));

insert into t (foo) values
('the quick brown fox'),
('jumped over the lazy dog');

select * from t;

| foo                      |
| :----------------------- |
| the quick brown fox      |
| jumped over the lazy dog |

select regexp_replace(foo, '[^a-z]+', '-') from t;

| regexp_replace(foo, '[^a-z]+', '-')         |
| :------------------------------------------ |
| the-quick-brown-fox                         |
| the-quick-brown-foxjumped-over-the-lazy-dog |

db<>fiddle here

I'd expect to see:

select REGEXP_REPLACE(foo, '[^a-z]+', '-') from t;

| REGEXP_REPLACE(foo, '[^a-z]+', '-') |
| :---------------------------------- |
| the-quick-brown-fox                 |
| jumped-over-the-lazy-dog            |

Am I doing something wrong or is this a MySQL bug?

It's a known bug, fixed in 8.0.12.

  REGEXP_RELACE() results from one result set row could carry forward to
  the next row, resulting in accumulation of previous results in the
  current row.


Cannot Decrypt the encrypted columns from the database backup on local machine I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

use master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'

CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Certificate';

-- Create a symmetric key
CREATE SYMMETRIC KEY FileName
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE BackupCert
GO

OPEN SYMMETRIC KEY FileName
DECRYPTION BY CERTIFICATE BackupCert
GO

UPDATE [TestDB].[dbo].[Order_Customer_tab]
SET [Process_File_Name] = ENCRYPTBYKEY(key_guid('FileName'), [Process_File_Name])
GO

OPEN Master Key decryption by PASSWORD = 'StrongPassword'

select Process_File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(Process_File_Name))
 from [TestDB].[dbo].[Order_Customer_tab]

I tried doing above still no luck.

Can anybody point me in the right direction? Any help is greatly appreciated.

Thanks

Looks like you did not encrypt the encryption key with the database master key. There has to be a complete chain from the service master key to the encryption key, usually is like this:

service master key -> database master key -> [optional certificate private key] -> encryption symmetric key

You are going to have to open the encryption key using the original password and then add the missing master key encryption.


Server has antivirus. Do I need to enable external locking on MySQL? From MySQL docs:

  If you only read tables with other programs, external locking is not required

Assuming the bytes of MySQL files does not match viruses, the antivirus will not change the contents of the file.

However, the antivirus may change file attributes and "do some other things".

Do I need to enable external locking on MySQL to prevent (possible) data corruption?

(I'm using both InnoDB and MyISAM.)

No, external locking is (was) only needed for MyISAM when you tried to execute several processes on the same data directory, such as two server instances, or an instance and a file repair.

Sincerely, I would be surprised if that page manual was accurate and was still fully supported. In any case, it is mostly impossible to do that now, as InnoDB (default engine) disallows 2 instances on the same server at the same time.

In summary, an antivirus scan is not a problem for MySQL, unless it changes the files or moves them away; and if the resources it uses do not interfere with the database execution. I wouldn't run, however, an antivirus software on a production server, unless it was for scanning the network (emails or desktop clients).


Check ruby gem and SQLite3 for FTS3 or FTS4 support I have installed SQLite3 on Mountain Lion via MacPorts and Ruby along with sqlite3 gem using RVM.

I'd like to know if there's any way for me to understand if my SQLite3 installation supports FTS4.

i.e. The only way I found online was issuing FTS3 commands and not getting errors, but there must be a more 'clean' way... Also the mentioned way doesn't show if you have FTS3 or 4 working.

The documentation states:

  Note that enabling FTS3 also makes FTS4 available. There is not a
  separate SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite
  either supports both FTS3 and FTS4 or it supports neither.

So if FTS3 works, so does FTS4.

Doesn't look like it's possible to tell what options sqlite was compiled with according to this SO answer.


how to make a particular field into seperate columns? Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

I have this table in the below format:

No | User | Value
01 | Port | Funds
01 | Vip1 | Systems
02 | Port | Bank
02 | Vip1 | Authority

This is how I want it:

No | Port  | Vip1
01 | Funds | Systems
02 | Bank  | Authority

Now the problem is, in this table, the User column has 6 other entries besides Port and Vip1. So I would want 6 columns and their respective values. I also want a query that I can use in other similar tables that have different User column entries. This is what I was trying to do without any success:

SELECT
   No,
   CASE user WHEN 'Port' THEN Value ELSE NULL END AS Port,
   CASE user WHEN 'Vip1' THEN Value ELSE NULL END AS Vip1
FROM table1

Please let me know what you think.

This query gives you the answer you want including "NULL" values if for a given No one of the Usernames is null.

SELECT  t1.NO No,
        t1.VALUE AS PORT,
        t2.VALUE AS Vip1
FROM table1 t1 FULL OUTER JOIN table1 t2
     ON t1.NO = t2.NO
WHERE t1.USERNAME = 'Port' AND t2.USERNAME = 'Vip1';

On the table:

SQL> select * from table1 where username = 'Port';

NO USERNAME             VALUE
-- -------------------- ----------------------------------------
01 Port                 Funds
02 Port                 Bank

I changed the column "USER" to "USERNAME", because "USER" is a reserved word in oracle.

This gives the output (in oracle 11g):

No PORT VIP1
-----------------
01 Funds Systems
02 Bank Authority

Another way to achieve this is a PIVOT table. A caveat is, that PIVOT tables only work with aggregate functions. For example, if you had two different entries for "Port" for No = 01, this would only return one of them.

SELECT * FROM
    (SELECT No, Username, VALUE from table1)
    PIVOT (MAX(Value)
           FOR (Username)
           IN ('Vip1', 'Port'))
ORDER BY No;

A slight variation of this gives you a pivot table for unspecified Usernames.
Unfortunately, oracle11g only provides XML output for this.

 SET LONG 10000 <-- to see the output on the terminal
 SELECT * FROM (
    SELECT No, Username, VALUE FROM table1)
    PIVOT XML (MAX(Value)
               FOR (Username)
               IN (SELECT DISTINCT Username FROM table1));

For more information on oracle pivot tables see http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php


Can I alter data within a mariadb db using mysql-workbench? I am on Ubuntu, remote connecting to a point of sale. I want to update costs of ingredients on our menus.
Can I do this in mysql-workbench?

In principle, yes.

Though it depends on the GRANTs for your particular database user. If you can't change the values, then try another database user or create a new user with the correct GRANTs.

You should be able to click on a table to list its records (it's the right-most icon when you hover over the table in my MySQL Workbench version), then double-click on the value you want to change, change the value, then hit the 'enter' key on your keyboard, then click the 'Apply' button below the table listing.


List expiration dates of Oracle backups Is it too much to ask Oracle to provide expiration dates with a list of backups?  I am not looking for expired backups.  I am looking for a list of expiration dates for each backup.

Oracle, in contrast with the name, can not predict the future.

If you have a retention policy configured as recovery window of 14 days or redundancy 2, the database does not know when your next full backup will succeed. Just because you have a full backup scheduled on every Saturady, that does not necessarily mean that backup will succeed and old backups can be deleted after that point of time.

Backups do not have an expiration date, unless you mean backups created with the KEEP option. For a backup created with the KEEP option, the database does list the time until it will be kept:

list backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7       Full    211.59M    DISK        00:00:18     2016-04-04 21:50:15
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20160404T214957
        Piece Name: /nasdata/exdb/0dr27io5_1_1
        Keep: BACKUP_LOGS        Until: 2017-04-04 21:49:57
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/system01.dbf
  2       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/myxml01.dbf
  6       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/users01.dbf
  9       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/ut2.dbf

...

Notice the line:

        Keep: BACKUP_LOGS        Until: 2017-04-04 21:49:57

I created this backup with the keep until time 'sysdate+365' option.


Database Tuning Advisor can't find tables across two databases I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the Database Tuning Advisor it has the error message "invalid object name" about many of the tables in one of the databases.

I have selected both databases on the first tab of the tuning advisor. It seems that the trace doesn't capture which database the SQL has been run on, and I can't find a place to say that it should.

How can I analyse a trace from this script without these errors?

With Martin Smith's help I found the answer.

On the dialog where you specify which events to include click Show All Columns. Tick the Database name and Id columns. Then redo the trace. It should import into the Tuning Advisor OK.


SQL Server database is too large because available space allocated 70% mdf space My database is 825 GB on disk, but unallocated space is about 500 GB (825GB * 55%).

After I shrank the database and files in SQL Server Management Studio, it had no improvement to reclaim the total .mdf file size. But I observed the unavailable space increased to 600 GB, total size of the .mdf file still was 825 GB (before shrink, I rebuilt the the index of tables which used to full text index search).

Do I have approach or configuration to reduce or reclaim the unallocated space (available space)?
Some detail:

Recovery mode: Simple
SQL Server 2016 CU15

The "unused space" is reserved by extents of pages which are maintained to be allocated to your data as your Tables and Indexes grow. The reason it's as big as it is currently is because you had a growth event at some point in the database that caused the MDF to grow.
General recommendation is to not shrink the file back down because it's likely that unallocated (and even unused) space will become allocated and used, and growth operations are heavy and affect performance. (There's also potential risk of data corruption as a result, when doing it to the MDF.)
It is not uncommon to have about 50% of your drive space in Unallocated. You can read more about Unallocated Space here. When SSMS calls it Space Available, it's already reserved by SQL Server but available to become allocated as needed.


Trigger for update column i´m trying to create trigger for update column, resulting of the min value of other columns. My sql code it´s the following:

DELIMITER //
CREATE TRIGGER t__price_final AFTER UPDATE ON market__combination_price
FOR EACH ROW
BEGIN
CASE
WHEN market__combination_price.price <= market__combination_price.offer
THEN UPDATE market__combination_price SET price_final =
market__combination_price.price;
WHEN market__combination_price.price > market__combination_price.offer
THEN UPDATE market__combination_price SET price_final =
market__combination_price.offer;
END CASE;
END;
//

But it doesn´t work. What could be the problem?

Several things are wrong:

While the UPDATE statement can be inside a CASE statement, you don't really need it. You only want to update a single row. And you have access to that row with the OLD and NEW keywords (values before and after the update. You can use SET NEW.price = ...
To modify a value in an update trigger, it has to be an BEFORE trigger, not AFTER.
The CASE statement (that has a SET) can be converted to a CASE expression (inside a SET)
Finally, if the columns are not nullable, it would be simpler to use the function LEAST() instead of a CASE expression.

The trigger code:

DELIMITER //
CREATE TRIGGER t__price_final
  BEFORE UPDATE ON market__combination_price
  FOR EACH ROW
  BEGIN
    SET NEW.price_final = LEAST(NEW.price, NEW.offer) ;
                     -- if price or offer are nullable:
                     -- = CASE WHEN offer < price OR price IS NULL
                     --       THEN offer
                     --       ELSE price
                     --   END ;
  END ;
//


Can a clustered index with low cardinality cause deadlocks? I'm looking at a deadlock, which happens when two processes execute an UPDATE statement on the same table. Both transactions are implicit, meaning that they span only the current statement only, which in turn means that locks I'm seeing are not a carry over from a prior statement.

There are two locks and two processes involved in the deadlock. One of the two locks is a page lock in the table (One process has update lock the other one wants Intent Exclusive lock) and the second one is the key lock on the table clustered index (one process has Exclusive lock and the other wants the update lock).

Now, the fields being updated in the both UPDATE statements are not a part of the clustered index.

Still, I'm wondering can the fact that this clustered index has tonnes of duplicate values contribute to the deadlock?

I'm sorry for not posting the deadlock trace, the update statements, the definition of the table and of the index, there is too much to obfuscate.

My question is not how to solve my deadlock, my question is about effect of low cardinality clustered index on probability of deadlocks in principle.

One of the two locks is a page lock in the table (One process has
  update lock the other one wants Intent Exclusive lock)

This mean that the table is big enough so that the engine has decided to use page granularity locks in order to discover the rows to be updated. Then as it 'drills' in' to actually apply the update to the rows, the deadlock occurs because the two updates both located the row-to-update on the same page. You could force rowlock, or you could disable page locks on the table, but the real problem is that it looks like the WHERE clause of the UPDATE is non-sARG-able.


SQL Server disk layout For a new SQL Server installation, I plan a disk layout.

My new server has a 10 disk bay, I also plan to use 256GB SSD as 5x Raid1.

My idea is:

OS install W2k19
SQL Server 2019 standard + USER DB data
USER DB logs
tempdb
Backups

I checked some discussion, but just don't know which one will be better solution, also my questions are:

What's better, OS separately and SQL Server + user DB data together or OS+SQL Server on one disk and USER DB data separately
For tempdb need mirrored disk??
Tempdb logs with tempdb data on same disk or tempdb logs together with USER DB logs?

In my experience, separating data and log files from the O/S is a frequently found default setup used by experienced DBAs.  Use Windows Backup (or some other full-disk backup system) to backup the O/S drives.  Use SQL Server backup to backup the data and log files.  Backing up the O/S allows you to easily restore the entire thing to a different machine as part of disaster recovery.

There are two schools of thought for the location of SQL Server data files and log files.

Brent Ozar thinks keeping both data and log on the same volume is a good idea since it reduces the chances that a disk problem will result in the database going offline.  As long as you have good backups, this is a sound strategy.
Other people1 recommend having the data separately from the log files which allows you to provide better performance for both since you'll have doubled the I/O capacity.  This can be an important factor if you have dedicated local devices for each volume, and they're not actually on the same SAN or RAID set.

It makes a lot of sense to have tempdb on it's own volume, and best to keep that local if you can.  Throw as many resources as you can at it.  As in putting it on a fast local SSD can have real performance benefits, especially if you have queries that spill to tempdb.

I tend to put the SQL Server installation files on their own volume, but this is not strictly necessary.

If the data you'll be storing in SQL Server is important, and honestly what data isn't, then you must ensure the location where you store backups is wholly unrelated to the location where you store the data and log files.  In other words, don't store backups and data on the same volume, SAN, or RAID set.  Also, no backup should be considered "good" unless and until you successfully restore that backup and validate its integrity via DBCC CHECKDB, or some other method.

1 - myself included


MySQL Replication Lag Check we are about to replicate our database servers into several slaves in different physical locations.

The company which is doing replication for us, has asked to give them a sql select statement on one of our tables to check lag and latency like

SELECT id FROM table1 ORDER BY id DESC LIMIT 1

Is it a common practice to check the lag between several instances?

Thank you in advance.

It's unclear what they want to measure.  To see the network lag, all they need is ping in the OS.  That should be much less than a second, even to the other side of the world.  (Unless they have a sloppy network.)

Seconds_behind_master is problematical.  It is a handy tool for the DBA, but it can be "wrong" for many reasons.

When running a long query, it shows how long ago the query started on the Master.  So, Seconds_behind_master will rise as long as that query is running on the Slave.
If you flood the Master with lots of writes from lots of connections, the Slave can get "behind".  This is congestion because of single-threaded Slave, not because of network lag.

One thing it can tell (I think) -- If the timezones are inconsistent, or they don't have a good clock sync (NTP).

A SELECT will not propagate through replication, only writes.  So, I see no use for your suggested SELECT.

If you are running at least 5.6.4:  CREATE a DATABASE and a login limited to that database (GRANT ALL ON db.* ...).  Give them the user name an password.  Let them INSERT into the Master and see what pops up in the Slave.  I think this is what is needed (but I have not tried it):

-- Create on Master and propagate to Slave:
CREATE TABLE Timer (
    id INT NOT NULL,
    raw_time TIMESTAMP(6) NOT NULL,
    sys_time TIMESTAMP(6) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
-- Run on Master:
REPLACE INTO Timer (id, raw_time, sys_time) VALUES (1, NOW(6), SYSDATE(6));
-- Run on Slave:
SELECT * FROM Timer;

The difference of the two columns will say how much delay where was -- Including

A tiny delay between NOW and SYSDATE on the Master
Network delay
Slave backlog (if something was running on the Slave when the REPLACE was being replicated; this can be minimized by using multi-threaded replication)

Note:  The (6) everywhere gives you microsecond precision.  This is more than sufficient to measure the milliseconds for replicating cross-country.

Note:  That technique won't work if you run with --sysdate-is-now.

Point the Company at this Answer; challenge them to top it.


use of synonyms in oracle I want to know the usage of Synonyms in oracle.I have searched in internet there i have learned like synonyms will be created in one schema and we can use that in another schema. But still am not clear with synonyms.Can anyone clear my doubt about Synonyms

In my experience, synonyms are generally used for the sake of convenience.  As an example, in my environment, we employ synonyms so that the developers don't have to worry about properly writing up cross-schema joins.  By utilizing synonyms, our developers don't need to qualify any object with a schema in their SQL statements, because everything they need is either in their schema, or looks to be (because of the synonym).  While some would argue this is poor practice, we're just tired of educating round after round of developers about proper database scripting and security standards.

As Travis alluded to below, creating a synonym for an object in a different schema doesn't mean you don't have to worry about security.  The proper GRANTs to the base objects are still required and using good judgement on the level of permissions granted is always encouraged.

For me, synonyms reduce my total cost of ownership on administrative tasks.  I'm sure there are other uses for Synonyms I've not touched on, but hopefully that helps by giving you a practical situation where synonyms are in fact very useful.


Updating large table (~6.5 million rows) & indexes I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all rows based on the data in a column in another table.

UPDATE  TOP (20000) c
SET     c.NewColumn = ISNULL(p.Col1, p.Col2)
FROM    dbo.Child c
    INNER JOIN dbo.Parent p on c.FKId = p.Id
WHERE   c.NewColumn IS NULL

in a loop, like this article.  The update was still running after 2.5 hours.  I'm wondering if disabling indexes on dbo.Child would make an impact.  NewColumn has no indexes, nor will it, but there are other indexes (about 5) on dbo.Child

Is SQL Server smart enough to see that it doesn't need to update the other indexes (as they are not part of the UPDATE), or would we benefit from temporarily disabling the indexes while we do the update statements?

This is SQL Server 2012 but the DB in question is in 2008 compatibility mode.

You say that the UPDATE is running in a loop and was still running after 2.5 hours. Did that loop update any rows at all? Was it the loop itself that was taking a long time or a single UPDATE statement? That is a somewhat important distinction and is currently ambiguous in the given information. However, here are some things to note based on things said in the question:

No, SQL Server should not be updating indexes that do not use the new column
Disabling indexes is not simply a matter of turning them off. It actually drops all of the index pages and keeps only the structure so that you don't need to run the full CREATE statement again. But re-enabling an index will have to rebuild it.

And here are some things to look for:

Are there any UPDATE triggers on the Child table?
What is the auto-growth setting on the transaction log? If set to a very low number it could be that the UPDATEs are impeded by a large number of auto-growth operations.
Of those 5 indexes on the Child table, do any of them have FKId as the leading column?
Have you tried lower batch sizes, or just 20k? I have seen several people randomly recommend large (10k - 50k) batch sizes for these types of operations without considering that not only does it limit the operation to that number, but it also hints to the Query Optimizer that the number is possible to hit in the first place. Hence, if there are only 5000 rows to find then it might keep looking past those to see if any others meet the c.NewColumn IS NULL criteria. And considering that the field is not indexed, all the Q.O. has to go on are the auto-generated statistics which don't tell it where to find the remaining NULL rows. Assuming that the first several iterations of the loop did succeed, SQL Server still has to find 20,000 rows that are NULL, out of the 6.5 million, by scanning as many as it takes, in no particular order, before it finds them. Hence the first few iterations probably happened quickly, but this type of operation slows down quickly as each successive pass needs to scan more and more of the records before it can find the 20,000 that meet the IS NULL criteria.

So, two things to consider:

Start out with a small batch size, maybe 10 rows, and run that statement by itself (i.e. not in the loop). Does it come back? If not then you need to answer @Aaron's question about what the WAIT_RESOURCE is. If it does come back, increase the batch size to 100, then 500, 1000, 2000, 5000, and then 10000. It will take more loops to complete, but finding the required number of records to UPDATE will be easier to accomplish. Once you find a size that seems responsive, then you can run the loop again (I am guessing that somewhere in the 2000 - 5000 range is gonna work :)
Rather than get rid of indexes, you might actually want to create an index, temporarily, to support this particular UPDATE operation. A filtered index (which was introduced in SQL Server 2008 so the compatibility mode shouldn't pose a problem) will allow you to target the remaining rows to update. Something like:

CREATE NONCLUSTERED INDEX [IX_Child_FKId_temp]
  ON dbo.Child ([FKId] ASC) -- you might need to include [NewColumn] ASC after [FKId]
  WHERE [NewColumn] IS NULL
  WITH ( FILLFACTOR = 100 );