How significant is performance difference between "DATE(mydate) = CURDATE()" vs "mydate = CURDATE()"? DATE(mydate) = CURDATE()

vs

mydate = CURDATE()

I have a pretty performance sensitive situation with tens of millions of rows, and I'm wondering if storing only date in datetime field and time in a separate columns, just to have a simple mydate = CURDATE() comparison when looking up records by days, would be significantly faster than merging both information in datetime field and using DATE(mydate) = CURDATE().

(yes, I cannot use "date" data type, because of the abstraction layer between me and the database)

Would there be a significant difference or is there a better way to get all records from the given day when using datetime field?

You just asked

  How significant is performance difference between “DATE(mydate) = CURDATE()” vs “mydate = CURDATE()”?

My Answer

  Very Gigantic Difference

When you use

DATE(mydate) = CURDATE()

The Query Optimizer will suggest a full table scan even if the column is indexed. Why ? The Query Optimizer is obligated to see the need to call the DATE() function against mydate in every row, thus eliminating the idea of using the index. Additionally, using FORCE INDEX will simply get you nowhere.

If mydate is datetime and is indexed, you could then change the query to

mydate >= (CURDATE() + INTERVAL 0 SECOND) AND
mydate <  (CURDATE() + INTERVAL 86400 SECOND)

or

mydate >= (CURDATE() + INTERVAL 0 SECOND) AND
mydate <  (CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND)

so you are comparing datetime to datetime and isolating just today. In this instance, an index on mydate will be used.

You do not want to split the date and time into spearate columns as that will cause comparisons of datetime ranges across multiple dates and times very difficult to express in SQL.


Error: change in schema version between compile time and run time - SQLNCLI11 This is a head scratcher. I've searched the forums and no one has been able to answer this. Not even GilaMonster on SQLServerCentral has chimed in on this one! One user suggested that it may be referring to a schema version number however it seems to be a bit high of a number for that. I looked for a query that could verify this but wasn't able to find a query to return version number of a schema, only the id of a schema.

The error below (message_id 7359) showed up recently and briefly on our servers that are linked. I'm not looking for a why so much as a what. What datatype is used for the compile time and run time values in this error message and is there a function already for formatting that into something human readable, assuming it is a datetime value? I've got a hunch its relatively new since there's nothing out there, that I could find anyway, that explains what those digits are.

Here's an example of the message we get:

  The OLE DB provider "SQLNCLI11" for linked server "SSQLS01" reported a change in schema version
  between compile time ("186839690094804") and run time ("186852574981615") for table ""SS017"."dbo"."TVHA"".

Thank you !

As described at https://stackoverflow.com/questions/1143259/what-is-the-internal-representation-of-datetime-in-sql-server, SQL stores datetime values as an 8-byte field.

The compile time in your sample error, 186839690094804, when converted to hex, is 0x0000A9EE015BA8D4. The date part is 0x0000A9EE, and the time part is 0x015BA8D4. 0x0000A9EE, when converted back to decimal, is 43502, and using an epoch of 01 January 1900, that comes out to 06 February 2019.

The date part of the run time in your sample comes out to 09 February 2019. Those pass the sanity check.


FORMAT returns large row size and data size I am surprise with one of my findings that using a FORMAT () does have very big impact on the row size and data size. It is almost 250x more of the size of not applying FORMAT ().

My question is:
1) Why does using FORMAT() have such big impact on the size? To me, is just 1.23 vs $1.23 difference, which is probably 1 character difference. And does it matter with such huge size?

2) Why do we still be encouraged to use FORMAT() in SQL server instead of using string concatenation as below. Since below is only 2X data size, and using format returns 250x data size. OR is that data size is not a critical measurement?

SELECT '$' + CONVERT(varchar(10), UnitPrice) FROM Sales.SalesOrderDetail;

3) Does having data size of 464MB means i will be returning 464MB of data to client?

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

Below is my findings with AdventureWorks2012 database.

SELECT UnitPrice FROM Sales.SalesOrderDetail;

Actual Number of Rows: 121317
Estimated Number of Rows: 121317
Estimated Row Size: 15B
Estimated Data Size: 1777KB

SELECT '$' + CONVERT(varchar (10), UnitPrice) FROM Sales.SalesOrderDetail;

Actual Number of Rows: 121317
Estimated Row Size: 26B
Estimated Data Size: 3060KB

SELECT FORMAT(UnitPrice, 'c') FROM Sales.SalesOrderDetail;

Actual Number of Rows: 121317
Estimated Row Size: 4011B
Estimated Data Size: 464MB

FORMAT() has an (admittedly undocumented) output of nvarchar(4000), at least in the cases of converting ints and dates to strings. The documentation simply says...

  The length of the return value is determined by the format.

But then doesn't explain or provide any examples. You can see what I'm describing, though, with:

SELECT TOP (1) object_id, x = FORMAT(object_id, 'en-us')
  INTO #blat FROM sys.all_objects;

EXEC tempdb.sys.sp_help N'#blat';

Result is that x is an nvarchar with a length of 8,000 (this is the number of bytes, not the number of characters).

Estimated row size is based on an assumption that variable width values will be half-populated. So, it expects 2,000 characters (4,000 bytes) on each row (even if the particular parameters you supply can't possibly result in that many characters). I demonstrate this (but not with FORMAT() specifically) in another answer, Would using varchar(5000) be bad compared to varchar(255)?

This is one reason I prefer to use CONVERT() and TRY_CONVERT() equivalents instead of FORMAT(), in spite of its syntactic sugar. At least with those you can convert to a defined width instead of relying on it "being determined by the format." Which may or may help estimated size, depending on the query. Another example that demonstrates the benefit here (even though it requires uglier code):

DECLARE @m float = 32.74532323;

SELECT
    a = @m,
    b = FORMAT(@m, 'c'),
    c = '$' + CONVERT(varchar(12), CONVERT(decimal(8,2),@m))
 INTO #splunge
 FROM sys.all_objects;

EXEC tempdb.sys.sp_help N'#splunge';

Results:

a    float
b    nvarchar(4000)
c    varchar(13)

Another reason I prefer to use CONVERT() and TRY_CONVERT() is that FORMAT() sucks from a performance perspective (see FORMAT() is nice and all, but…).

Also please don't ever use variable-width types like varchar without also specifying a length.


SQL server agent unable to open step output file the step failed - mapped drives I have used Ola Hallengren's maintenance script to create backup scripts and schedules, originally both to a local folder and this worked fine. Then I set it up to send the backup files to a mapped Azure file share as a mapped network drive with no log file output, this worked.

I have now re ran the script so that it backups and outputs to the same drive (one folder called Backup and one called LogBackup), SQL Server agent will now not run if the output file location is declared, I have tried using a local drive but it still does not work. Any suggestions?

This is the error displayed in the job history:

Message
Executed as user: NT Service\SQLSERVERAGENT. Unable to open Step output file.  The step failed.

Once you get the code straightened out for the local drive, you'll need to set permissions on the network share for the server's computer account since you are using the default NT Service account for the services. The computer account is the server's name and then a dollar sign.

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window.

You would set the permissions on the remote server. RDP to it, navigate to the share and grant the access there. I am not sure for Azure how it works. But if this were a hosted server instead of a cloud server, it would be on the actual folder and not on the mapped network drive.


Should I change my old datetime columns from SQL Server 2005 to just date datatype for SQL Server 2012 My database was developed in 2005 for SQL Server 2005 version.

In that time for columns which store pure date without time we used  datetime data type.
Our database has survived up today and now we are using SQL Server 2012.

Should I consider to change my columns from datetime to just date in places where I do not need to store time?

I am afraid what will happen with indexes on columns which changing type from datetime to date, what will happen whit statistic. Why I am thinking about changing datatype is because I think that I can gain more performance with just date type, also I want to save some space ? Does exist anywhere some general guidelines about switching from datetime to date. Is there really huge differences in performances between these two types.

In SQL Server 2012, date takes 3 bytes while datetime takes 8 bytes.
If your table has millions of millions of rows, then yes, then the IO and logical page size should be reduced and your performance will be quicker.

First though i'd suggest to try dumping the table into a testing table, convert the column, apply your indexes and benchmark using real life workloads against this table using the original as a baseline.


Storing multiple tags on analytics database I would like to store user purchase custom tags on each transaction, example if user bought  shoes then tags are "SPORTS", "NIKE", SHOES, COLOUR_BLACK, SIZE_12,..

These tags are that seller interested in querying back to understand the sales.

My idea is when ever new tag comes in create new code(something like hashcode but sequential) for that tag, and code starts from "a-z" 26 letters then "aa, ab, ac...zz" goes on. Now keep all the tags given for in one transaction in the one column called tag (varchar) by separating with "|".

Let us assume mapping is (at application level)

"SPORTS" = a
"TENNIS" = b
"CRICKET" = c
...
...
"NIKE"  = z        //Brands company
"ADIDAS" = aa
"WOODLAND" = ab
...
...
SHOES   = ay
...
...
COLOUR_BLACK = bc
COLOUR_RED = bd
COLOUR_BLUE = be
...
SIZE_12 = cq
...

So storing the above purchase transaction, tag will be like tag="|a|z|ay|bc|cq|" And now allowing seller to search number of SHOES sold by adding WHERE condition tag LIKE %|ay|%. Now the problem is i cannot use index (sort key in redshift db) for "LIKE starts with %". So how to solve this issue, since i might have 100 millions of records? dont want full table scan..

any solution to fix this?

Update_1:
I have not followed bridge table concept (cross-reference table) since I want to perform group by on the results after searching the specified tags. My solution will give only one row when two tags matched in a single transaction, but bridge table will give me two rows? then my sum() will be doubled.

I got suggestion like below

  EXISTS (SELECT 1 FROM transaction_tag WHERE tag_id = 'zz' and trans_id
  = tr.trans_id) in the WHERE clause once for each tag (note: assumes tr is an alias to the transaction table in the surrounding query)

I have not followed this; since i have to perform AND and OR condition on the tags, example ("SPORTS" AND "ADIDAS") ----  "SHOE" AND ("NIKE"  OR  "ADIDAS")

Update_2:
I have not followed bitfield, since dont know redshift has this support also  I assuming if my system will be going to have minimum of 3500 tags, and allocating one bit for each; which results in 437 bytes for each transaction, though there will be only max of 5 tags can be given for a transaction. Any optimisation here?

Solution_1:

I have thought of adding min (SMALL_INT) and max value (SMALL_INT) along with tags column, and apply index on that.

so something like this

"SPORTS" = a = 1
"TENNIS" = b = 2
"CRICKET" = c = 3
...
...
"NIKE"  = z  = 26
"ADIDAS" = aa = 27

So my column values are

`tag="|a|z|ay|bc|cq|"` //sorted?
`minTag=1`
`maxTag=95` //for cq

And query for searching shoe(ay=51) is

maxTag <= 51 AND tag LIKE %|ay|%

And query for searching shoe(ay=51) AND SIZE_12 (cq=95)  is

minTag >= 51 AND maxTag <= 95 AND tag LIKE %|ay|%|cq|%

Will this give any benefit? Kindly suggest any alternatives.

I'm still convinced that using a many-to-many lookup table (a bridge table) is still your best option here. Your concern about matching multiple rows can be fixed by proper query design. Let's say your tables are:

CREATE TABLE purchases(PurchaseID,CustomerID,PurchaseDate,...)
CREATE TABLE tags(TagID,TagType,TagName)
CREATE TABLE purchasetags(PurchaseID,TagID)

So each purchase can have multiple tags set (no limit), and just for fun, I added the ability to categorized the tags by TagType, maybe that contains things like "ProductType", "Brand", "Color", "Sport", so you have a way to tell that "shoes" is a "ProductType" tag, "Nike" is a brand tag, and "soccer" is a sport tag.

Then if you want to query (and return just single rows), just do:

SELECT *
FROM purchases
WHERE PurchaseID IN (SELECT pt.PurchaseID
                     FROM purchasetag pt
                     INNER JOIN tags t ON pt.TagID=t.TagID
                     WHERE t.TagName IN ('Adidas','Nike'))
GROUP BY whatever...

If you need to do fancier combo-searches (find purchases of Nike shoes or Adidas shoes, your query will have to also be fancier:

SELECT *
FROM purchases
WHERE PurchaseID IN (SELECT pt.PurchaseID
                     FROM purchasetag pt
                     INNER JOIN tags t ON pt.TagID=t.TagID
                     WHERE t.TagName = 'Shoes')
AND   PurchaseID IN (SELECT pt.PurchaseID
                     FROM purchasetag pt
                     INNER JOIN tags t ON pt.TagID=t.TagID
                     WHERE t.TagName IN ('Adidas','Nike'))

Again, that's still returning a single row for each purchase that matches your desired tag combination.


Ordering by field, constructed by subquery is slow My tables (oversimplified for clarity):

documents:  registrations:
|id|        |id|document_id|date|

My simplified query:

SELECT *
FROM (SELECT documents.*,
             (SELECT max(date)
              FROM registrations
              WHERE registrations.document_id = documents.id) AS register_date
      FROM documents) AS my_documents_view -- it's supposed to be a view
ORDER BY register_date desc NULLS LAST
LIMIT 20;

When I try to order by register_date field I get a potato response of ~80 seconds execution.

EXPLAIN ANALYSE:

Limit  (cost=27237727.87..27237727.92 rows=20 width=192) (actual time=85124.599..85124.613 rows=20 loops=1)
  ->  Sort  (cost=27237727.87..27265594.16 rows=11146516 width=192) (actual time=85124.597..85124.600 rows=20 loops=1)
        Sort Key: ((SubPlan 2)) DESC NULLS LAST
        Sort Method: top-N heapsort  Memory: 33kB
        ->  Seq Scan on documents  (cost=0.00..26941123.09 rows=11146516 width=192) (actual time=0.074..77874.947 rows=11153930 loops=1)
              SubPlan 2
                ->  Result  (cost=2.19..2.29 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=11153930)
                      InitPlan 1 (returns $1)
                        ->  Limit  (cost=0.43..2.19 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=11153930)
                              ->  Index Only Scan Backward using registrations_document_id_date_idx on registrations  (cost=0.43..3.95 rows=2 width=4) (actual time=0.004..0.004 rows=1 loops=11153930)
                                    Index Cond: ((document_id = documents.id) AND (date IS NOT NULL))
                                    Heap Fetches: 10337268
Planning Time: 0.381 ms
Execution Time: 85124.722 ms

Complexity and cost are absurd, there are lots of rows in these two tables (millions, actually), but is it really so hard for engine to order it? Are there any workarounds or suggestions to optimize it?

In full query, I have some additional filters so it runs a bit faster, but it's still unacceptable for the project.

I tried playing with joins and indexes without any success.

Try to flatten the subquery into a join:

SELECT *
FROM (SELECT d.*,
             max(r.date) AS register_date
      FROM documents AS d
      LEFT JOIN registrations AS r
          ON r.document_id = d.id
      GROUP BY d.id) AS my_documents_view
ORDER BY register_date desc NULLS LAST
LIMIT 20;


MySQL - LOCK IN SHARE MODE for EXISTS query I am starting a transaction and I need to check that a record exists, I don't need any values, but I need it to exist for the duration of the transaction for everything to remain valid.

It's VERY unlikely of course that someone will delete at more or less the same time someone else tries to do what this transaction happens, but as with all things concurrent 'very unlikely' isn't good enough.

I don't want to experiment either - I want to know for certain it'll work. Will something like:

SELECT EXISTS(SELECT * FROM tbl1 WHERE ... LOCK IN SHARE MODE);

Work as I expect it to?

For

I would imagine that LOCK IN SHARE MODE is simply implemented as a flag at the parsing stage, telling MySQL to acquire certain locks, thus this isn't a special case.

Against

MySQL treats EXISTS differently to a normal SELECT for obvious reasons. As per it's query optimiser it often reads constants before it starts running the query. An over-eager optimisation could defeat this.

I'm leaning towards 'for' but I want to be certain.

There are multiple type of locks in Mysql.
LOCK IN SHARE MODE in a shared mode lock on any rows that are read. So you need to pay attention to your query, how many rows are read (look also at examined column when doing explain).

The isolation level will also affect behaviors of certain locks.
I strongly advise to let Mysql take care of the locks.
When an update needs to occur it is usually the first come first serve.
make sure your Exist query is fast (best is to query on the PK).

If exists (select 1 from tbl1 WHERE ) Then
 perform your update
Else
Select "record does not exists"
End if;


Mysql import table warning Hello i have a small database i'm working on and when i import on another server it tells me you have 6 warnings. when i show warnings it says 1031 This table doesn't have this option. I've searched on google about this problem but i didn't find something that relates my problem and the warning level is on note.

Localhost Version

# mysql --version
mysql  Ver 14.14 Distrib 5.5.34, for Win32 (x86)

Server Version

-- --------------------------------------------------------
-- Host:
-- Server version:               5.5.35-cll - MySQL Community Server (GPL)
-- Server OS:                    Linux
-- HeidiSQL Version:             8.3.0.4694
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for AquaMercury
DROP DATABASE IF EXISTS `AquaMercury`;
CREATE DATABASE IF NOT EXISTS `AquaMercury` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `AquaMercury`;

-- Dumping structure for table AquaMercury.bex_categories
DROP TABLE IF EXISTS `bex_categories`;
CREATE TABLE IF NOT EXISTS `bex_categories` (
  `ProductCategoryId` int(11) NOT NULL AUTO_INCREMENT,
  `ProductCategoryName` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ProductCategoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 COMMENT='Stores product categories';

-- Data exporting was unselected.

-- Dumping structure for table AquaMercury.bex_products
DROP TABLE IF EXISTS `bex_products`;
CREATE TABLE IF NOT EXISTS `bex_products` (
  `ProductId` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` mediumtext NOT NULL,
  `ProductPrice` varchar(100) NOT NULL,
  `ProductManufacturerCode` varchar(100) NOT NULL,
  `BexProductCode` varchar(100) NOT NULL,
  `ProductDetails` mediumtext,
  `ProductPicture` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ProductId`,`BexProductCode`,`ProductManufacturerCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 COMMENT='Place of storing products';

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

CHECKSUM=1 is not an option that is supported by InnoDB.  When used with MyISAM, it causes the CHECKSUM TABLE command to run faster because the storage engine maintains the calculated checksum whenever the table data is changed, instead of having to calculate it on demand... but otherwise it doesn't actually have a purpose (such as safety, data integrity, or error detection or correction, even though it sounds like it might).

How the option originally got set is difficult to guess... either these tables were converted from MyISAM to InnoDB at some point, or perhaps you created them with some kind of graphical MySQL client that added the option to be "helpful."

These warnings should be safe to disregard, but it is easy enough to just edit the file with a quality text editor (such as Notepad++) and remove CHECKSUM=1 from each place where it appears.  If the warnings go away, that was the issue.  Removing the option should have no negative consequence, since it isn't doing anything anyway.

http://dev.mysql.com/doc/refman/5.6/en/create-table.html


Invoice days and amount with Distinct Value I am having a billing table like as above.

I need to get a billing age wise report by using distinct of Branch, UserId, Customer, BillType.

I need to wirte a Stored procedure for getting the following result.

How to write a query for this

You'll want to check out the datediff function, see below example.

select branch,
       userid,
       customer,
       billtype,
       case when datediff(d,billdate,getdate()) >= 0 and datediff(d,billdate,getdate()) <= 30 then billingamount else 0 end [0 - 30 days],
       case when datediff(d,billdate,getdate()) >= 31 and datediff(d,billdate,getdate()) <= 60 then billingamount else 0 end [31 - 60 days],
       case when datediff(d,billdate,getdate()) >= 61 and datediff(d,billdate,getdate()) <= 90 then billingamount else 0 end [61 - 90 days],
       case when datediff(d,billdate,getdate()) >= 91 and datediff(d,billdate,getdate()) <= 120 then billingamount else 0 end [91 - 120 days],
       case when datediff(d,billdate,getdate()) >= 121 then billingamount else 0 end [121+ days],
       billingamount as total
  from #test


MySQL similar name for status or type column As status and type is reserved key in MySQL, I need a column name similar to status to identify the status of a row.

I know there is way to ignore this problem but in different language and different framework it suddenly arise this problem in different way, so I need to ignore this word and need a similar word that sound almost same and can be understandable from the name.

The potential values for my use case are true/false. What do you suggest to name a Boolean column that defines the status of that row?

Here status is defining that row if it is active or inactive. And not every where I'm using status column for this purpose, Somewhere status column is defining if the status is pending, current, confirmed, applied, rejected, etc. Like this shorts of key.

As a_horse_with_no_name commented:

  I typically include the entities name with that, e.g. person_status or element_type. That also makes reading queries easier (at least in my eyes)

...it is better to include more information with those column names in order to make them more easily understandable.

For example you in a user table you could have a user_status and user_type column and those names would clearly define exactly what you are referring to with status and type. It would make it even more useful if those columns where using reference tables such as user_status and user_type.

Those are just examples. The table name could be modified to indicate that it is a reference table such as r_user_status or user_status_ri. Or one can work on making it more generic (but still more descriptive than status), so that it can be used for more tables. This would also allow you to remove repeated data that might need updating, and move the update to a single table for when you might need to change the text of an existing status.

In the end avoiding generic names such as status and type will make it much easier for developers who join the project later on to easily understand the data and its relationships.


LEFT JOIN conversion to INNER JOIN, can't change FROM clause I have 2 tables:
BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation.
These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date.
However the history in BASE is independent from the history in CHILD. E.g.:

BASE
1   1/1/2000    1/1/2010
1   1/1/2010    31/12/2012

CHILD
1   1/1/2000    31/12/2005
1   31/12/2005  1/1/2010

The goal:
For a given date, select the active record in both table and show them in 1 result-record.

Problem:
If there is no match in CHILD (e.g. 1/1/2011), I get “no rows” whereas it should show the data from BASE (and Null-values for the CHILD-fields)

This is my query:

SELECT
  BASE.*, CHILD.*
FROM
  BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
WHERE
  BASE.ID  = '1'
  AND BASE.START_DATE  <=  TO_DATE('1/1/2011', 'DD/MM/YYYY')
  AND BASE.END_DATE  >  TO_DATE('1/1/2011', 'DD/MM/YYYY')
  AND CHILD.START_DATE  <=  TO_DATE('1/1/2011', 'DD/MM/YYYY')
  AND CHILD.END_DATE  >  TO_DATE('1/1/2011', 'DD/MM/YYYY')

I know that my LEFT JOIN will be converted to an INNER JOIN because of CHILD - columns in the WHERE clause. I also tried the IS NULL-trick that I found on this forum, but I’m still struggling with it.

Important remark:
Because of the technical setup, it is not possible to change the FROM clause, so I’m forced to find a solution via the WHERE clause …

Can you help?

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

  the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range.
Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.


Drop table without its dependencies and solve block corruption I have found alert about corrupted block in alert log. Corrupted block belonged to the table named DOCS. Because of I don't have any backup of this database, decided to recreate DOCS table to solve the corruption. And here is a problem.

Generated all scripts:

1. DDL for table.
2. DDL for constraints,triggers,indexes on the table.
3. DDL for the constraints that references this table.
4. Saved grants on this table.

Now I want to drop table , but left its dependencies , and then recreate table with its constraints,triggers,indexes..

How can I do that?

------------------------Edit

It seems to be very simple.

Save data of this table

  create table new_table as select * from my_table;
  Save DDL for table and its comments
  DDL for indexes on this table
  DDL for constraints on this table
  DDL for Foreign key-s that references on this table
  Save grants on this table
  Drop table purge
  Recreate table, comments, constraints, indexes, foreign keys that references to this table
  Give grants
  insert data

  insert into my_table as select * from new_table

This solution works for re-creation the table(it is a correct way to recreate a table). But the corruption did not solved, during the insertion into the table in Alert.log raised the same error about block corruption.

In comments @Mindaugas Riauba wrote a solution and it worked. I just want to know why my solution about re-creation table did not work and @Mindaugas Riauba's solution work?

Thank you @Mindaugas Riauba again!

Apparently you dropped the object already. Now to calm you down - corrupted blocks now most probably are in free space and do no harm. They just annoy you during backups. To check that:

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) corr_start_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
  , 'Free Block' description
from dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1
  and f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

If it really shows here you have two options. You can simply ignore it. Once the block will be assigned to some object it will be reformated and corruption will go away. If you want to fix corrupted blocks then you will have to create the object which would occupy your corrupted blocks.

Let's say your corrupted block exists in tablespace USERS and datafile /ora/users01.dbf. First you have to make datafiles of tablespace not autoextendable. That is not to inflate datafile size. And then you'll create filler table.

create table filler (
  padding varchar2(1000)
) tablespace users nologging pctfree 90 pctused 10;

Then you have to extend table's segment so that it would incorporate corrupt blocks:

alter table filler allocate extent
  (datafile '/ora/users01.dbf' size 100m);

In such way you can add several extents adjusting their sizes. After you'll check that corrupt blocks now longer belong to free space you can fill the table:

insert /*+append */ into filler  select rpad('x',1000) from
dba_objects
--  filler
where rownum <= 50000;

First insert/select goes from dba_objects then it is faster to insert/select from filler table itself.

Now you just have to check datafile using RMAN:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 333;

And drop filler table:

DROP TABLE filler PURGE;

Easy. :)


Can I select keys stored in a postgres jsonb column as values dynamically I have a JSONB column in a Postgres DB that contains some dynamic data about a row. Think of the table as a single table with an id column and a "dynamicData" JSONB column for simplicity for this example. And suppose in a row, I store the following in the "dynamicData" column
{
  dynamicField1: 'value1',
  dynamicField2: 'value2',
}

I understand I can select those as columns in a select such as
Select
  dynamicData->dynamicField1 as dynamicField1,
  dynamicData->dynamicField2 as dynamicField2,
From
  some_table

But what I'm curious is if I can somehow tell PG I want all keys in the JSONB column to be selected as columns.
This is because I have database views that sit on top of the tables, and if I add a new JSONB key in the dynamic data column, I would then need to somehow update these views if I cannot select the keys as columns automatically.

Like @a_horse told you, SQL is strictly typed and demands to know the resulting row type at call time at the latest.
If key names follow a predictable pattern, and there can only be a trivial number, you might "overprovision" in your views:
SELECT dynamic_data->'dynamicField1' AS dynamicField1
     , dynamic_data->'dynamicField2' AS dynamicField2
     , dynamic_data->'dynamicField3' AS dynamicField3  -- overprovisioned columns
     , ...
     , dynamic_data->'dynamicField3' AS dynamicField9
FROM  (SELECT json '{"dynamicField1": "value1"
                   , "dynamicField2": "value2"}') tbl(dynamic_data);

Missing keys get a NULL value. dynamicField3 - dynamicField9 are NULL in the example. The workaround has obvious weaknesses.


Restoring SQL Server database on another box I have a SQL Server database that is running these scripts to create backups. It's running full backups once a week, daily differentials, and hourly logs. These are being FTPd to another box where I would like to be able to restore them for verification purposes. Right now I'm doing this simply hand picking the appropriate backups using the names of the files, which have the date/time encoded into them. That seems pretty hacky to me.

On the main SQL Server box where the backups are being created, I can open SMS, right click on the database, and select

    Tasks -> Restore -> Database

I get a nice grid listing of options including the full, diffs, and logs that I can restore.

I can't seem to recreate this on the remote box though. I figure that the other SQL Server probably doesn't have the metadata required to create this list since the backups didn't originate from that box. Is there anyway to restore that meta data as well? I tried restoring msdb but it doesn't seem to help.

Here are the steps I'm taking:

(1) Restore the master database on the new server.
(2) Restore the msdb database on the new server.
(3) Restore the full backup of the database on the new server with NORECOVERY

RESTORE DATABASE AwesomeDatabase99
FROM DISK = N'C:\Path\To\FULL_20150226_111927.bak'
WITH
    MOVE N'AwesomeDatabase99' TO N'C:\Path\To\AwesomeDatabase99.mdf',
    MOVE N'AwesomeDatabase99_log' TO N'C:\Path\To\AwesomeDatabase99_1.ldf',
    NORECOVERY
GO

I was hoping at this point I would be able to get the full grid of restore options containing the diffs and logs inside MSM but it still doesn't want to show up. The paths to the backup file are the exact same on the new server as they were on the server where they were created.

It seems like the system should be able to take all the backup files and tell me which ones go together. Is that possible?

What Bob said.

Couple of ways to get round this.

Firstly, you could copy the files from the FTP server to your test server and then use xp_cmdshell and the DIR command to read in the contents of that folder (full, diff and log backups) and order them accordingly (you need the date/time in the filename to achieve this).  Once you have this information, you can dynamically build your restore statements.

I did this once, but, I have xp_cmdshell disabled in my environment here, so it's not an option.

The way I currently do it.  I build the restore statements based on the metadata in msdb and export that out to a text file which is stored on the FTP server.  I simply read that text file on along with my backup files on my test server and away I go.

My solution performs a point in time on a daily basis to my test box.  What it can't guarantee is if the subsequent log backups are valid and I can restore from those.

I also perform manual restores to my test box (another instance) just to keep myself fresh when it comes to a disaster - I vary the restores too.


Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6 I have the following demo data (taken from Adventureworks)

SalesOrderNumber    SalesOrderDetailID
SO43659             12
SO43659             11
SO43659             8
SO43659             9
SO43660             14
SO43660             13
SO43661             27

Is it possible to Group per SalesOrderNumber, and show the Detail IDs per SalesOrderNumber?

SalesOrderNumber    SalesOrderDetailID
SO43659             12, 11, 8, 9
SO43660             14, 13
SO43661             27

http://www.sqlfiddle.com/#!3/7708b/1

What would be the best option for this please?
Pivots?
Cursors?

Cursor which calls stored proc for each row , e.g. here ?
Or this option from 4guysfromrolla which uses user-defined function?

This is something that will be run once on only - at night -  I dont need efficiency - just any help please.

This is standard sql server emulation of GROUP_CONCAT in mysql

select  SalesOrderNumber,Ids=Stuff((SELECT ',' + CAST(SalesOrderDetailID as VARCHAR(255)) FROM t t1 WHERE t1.SalesOrderNumber=t.SalesOrderNumber
 FOR XML PATH (''))
             , 1, 1, '' )
from t
GROUP BY SalesOrderNumber

Fiddle

Also,you might be intrested in http://groupconcat.codeplex.com/


SQL Server 2005/8 Query Optimisation Hints I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance.

For instance I once had a DBA who insisted that count(*) would perform worse than count(1) (I have no idea whether she was right or whether it's still valid against the latest query optimisers).

What simple things should I be telling the team to try and always use or avoid?  I'm ideally looking for things which (a) might make a reasonable difference and (b) are straight forward, 1 - 2 lines to state.

Query tuning 101

There is no magic silver bullet to query tuning, although I can give you some hints and tips.  The first thing to do is to understand what's actually going on behind the scenes.  Get a good internals book like the third Guru's Guide book.

Poorly performing queries tend to come in two basic flavours: Transactional queries that take too long, and grinding batch jobs (or reports) that take too long.  One good sign of a query with something wrong with it is a single item in the query plan taking 99% of the time.

Transactional queries

On most occasions a poorly performing transactional query is one of a few things:

A missing index.  You can see this in the query plan - table scans of large tables on a join that should be very selective (i.e. return few rows).
Query unable to use an index.  If you have OR conditions in the where clause, joins on a calculated value or some other item in the query that is not sarg-able then you may need to re-write the query.  Briefly, sargs are query predicates that can use indexes to eliminate rows.  Logical AND, equality and inequality (>, >=, <, <= and !=) are all sarg-able.  OR is traditionally not sarg-able.  However, you can often translate OR's into sarg-able predicates by inverting the sense from OR to NOT (foo and not bar) type constructs.
Inefficient predicates.  For example, if you have a where in referencing a nested subquery see if it can be re-written as where exists or as a join.  This may result in more efficient query plans and here are other standard re-writes you can try also.  Again, the Guru's guide books and others on the subject are a good starting point.

Batch queries

Batch queries are more complicated and have different tuning issues.  Some tips are:

Indexing.  This can make a large difference for the same reason it does with transactional queries.  Often a good sign of a missing index is a long, grinding operation (99% of the query plan) that doesn't seem to be thrashing the machine.
Temporary tables.  You may find it better to break down a query into several queries populating temporary tables.  Larger queries give the optimiser more room to screw up, although this is less of an issue that it used to be.  Make the temp tables with select into as this operation is minimally logged (much less log activity), which reduces the I/O load.  Note that temporary tables in tempdb are the same data structure that the optimiser uses to store intermediate join results, so there is no performance penalty for doing this.  You can also create an index (including clustered and covering indexes) on a temp table, which may improve performance of the queries reading it for the same reasons that they improve queries on static tables.Don't overdo temp tables though, as they can make things harder to trace back through the query.  For smaller tables within a stored procedure, test to see if table variables help.  These are an in-memory data structure, so they can be a performance win.
Clustered and covering indexes.  These can improve the performance of a query as they force locality of reference on the disk based on some grouping column.  A clustered index can make a large difference to the performance of a batch job.
Inefficient predicates.  These can cause problems with sargs and other sub-optimisation isses in much the same way as they do with transactional queries.
Table scan is your friend.  Contrary to popular belief, table scans are not inherently evil.  Generally they are a sign of something wrong in a transactional query, but they are often the most efficient way to do a large batch operation.  If you are doing something with more than a few percent of rows in a table, a table scan is often the most
efficient way to cover the table.
Nested loops joins.  Take a look at what the optimiser is doing on both sides of the join.  These can be inefficient if you are (for example table scanning two large tables on both sides of a nested loops join.  Consider using clustered indexes or order by and trying to change the operation to a merge join or hinting to promote a hash join if one side is small enough to do this with.

Locking

Locking can also cause performance issues.  If your system is performing badly under load, look at the profiler and perfmon counters related to locks and check whether there is any significant contention.  sp_who2 has a 'BlkBy' column in the result set that will show if a query is blocked and what is blocking it.  Also, profiles with 'deadlock graph' events (if you have queries deadlocking) and lock related events can be useful to troubleshoot lock issues.


getting error while installing memcached_function now i am working with memcached with mysql cluster 7.2
for that i am installed

libevent-1.4.14b-stable.tar.gz
memcached-1.4.5.tar.gz
libmemcached-0.8.tar.gz

then

memcached_functions_mysql-0.8.tar.gz

i have installed first 3 successfully
but while installing memcached_functions with

./configure  --prefix=/usr/local/ --with-mysql=/usr/local/mysql/bin/mysql_config

i am getting error like

  checking for DEPS... configure: error: Package requirements (libmemcached >= 0.17) were not met:

can any one help me out in finding the solution...

thanks in adv

Deepak M

i found the solution for the previous case

the DEPS ERROR mainly because of not setting the path

while installing the libevent,memcached,libmemcached i gave the prefix with /usr/local path....in that path all the library files are created
and while installing memcached function we need to set the environment variable to the path as shown below

export PKG_CONFIG_PATH=/usr/local
export DEPS_CFLAGS=/usr/local
export DEPS_LIBS=/usr/local/lib

later its succesfully configured and working

regards
Deepak M


Efficient query for an advanced EAV model I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure.

Today, a product table already exists, containing 4 fixed fields. We want to upgrade the system, allowing an infinite amount of additional product fields, defined by the manufacturer of each product. We call these additional fields 'parameters'.

A parameter can be of the following data type:

Text
Date (range)
Boolean
Dropdownvalue (single)
Dropdownvalues (multiple)

Based on that, I created the following database model:

:

Product: the original product table, containing the fixed fields.
Parameter: this table defines all types of parameters. Currently 5: Text, Date, Boolean, Dropdown value (single), Dropdown value (multiple)
ManufacturerParameter: this table stores which parameters are defined by the product manufacturer (Manufacturer table not included in the picture) for all of his products.
ProductParameter: this is where the actual product data of all the defined parameters is being stored. A parameter of the type 'Text', stores its information in the field 'Text', 'Date' in 'DatumBegin' and 'DatumEnd', Bit/Boolean in 'Boolean'.
ParameterValueListItem: this table contains the pre-defined values of the Dropdownlist values for both single and muliple coupling. These are the values you get to see in the Dropdownlist when creating or altering a product.
ProductParameterValueListItem: this is where the actual product data is stored for the Dropdownlist values.

I need to build a webpage in ASP.NET C# which shows a list of all product data, including each defined parameter with it's value for that product. I need to be able to filter the list by searching on any field/parameter. This list also needs to be sortable on any field/parameter.

I tried to filter the list by outer joining Product - ProductParameter - ProductParametervalueListItem - ParameterValueListItem to obtain a table result containing all data, and then filtering that list by using a dynamic where clause, built and passed through by the web app. This works when you search on 1 parameter but when you start searching on multiple parameters, you get incorrect results because each parameter is a different line in the table result and there simply is no 'AND'-match between parameter A and B, because they don't exist in the same record (line).

Can anyone advice me how this can be achieved? The most perfect solution would be 1 table result, containing all data and each parameters being shown as a column in that same product row. Is this too complex to handle at database level?

Thanks for reading this far. Any advice is welcome.

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is nothing wrong with applications running CREATE TABLE now and again.

Second, I am not sure I understand why you have normalised Parameter into its own table? Why not put that directly into the ManufacturerParameter table.

Third, if you insist on proceeding with your current model, there are ways to achieve what you want (at least if I am interpreting your requirement correctly). What you can do is to write your query in such a way that it is a sums up the search argument when there is a match and then use HAVING to filter out the values that match. I am assuming that only one of the fields Text, Boolean, Datum etc are populated per ProductParameter record (you probably want to enforce this with a constraint)

For example, to search for all products that have a bolean = true for one parameter AND text = 'abc' for some other parameter you can do:

SELECT P.Name
FROM Product P
JOIN ProductParameter PP
WHERE P.ID = Foo
  AND PP.Boolean = 1 OR PP.Text = 'abc'  ... /* For each filter */
GROUP BY P.Name /* And any other things you want out of product */
HAVING COUNT(*) >= [Number of where clauses]

If you need to list all the parameters of this product, you can use the above query template as a nested query and join back to ProductParameter.

The above query CAN be optimised by maintaining a computed column in ProductParameter that has a string representation of the different data types in that table. That way, the above OR statements can be rewritten as an IN list (which you will want to pass as a table valued parameter).

I would like to repeat that what you are doing is probably very wrong. If you do it, you will most likely need to hand tune most of your query plans - the optimiser will not help you anymore. And that is assuming you don't have too many query variants, which will run your plan cache full.


SQL select statements not matching on certain rows I am running a Postgresql database with docker-compose, using the image postgres:10-alpine. For some reason, the database is unable to select certain rows based on string comparison. For example, this query works as expected:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='user1';
  id   | preferredUsername
-------+-------------------
 38793 | user1
(1 row)

However, the same query is not working for another user:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='user2';
 id | preferredUsername
----+-------------------
(0 rows)

The user definitely exists, and the query works if I use ILIKE instead of =:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 'user2';
  id   | preferredUsername
-------+-------------------
 41576 | user2
(1 row)

What could be the reason for this wrong behaviour? I suspected it might have something to do with the encoding, but SHOW SERVER_ENCODING and SHOW CLIENT_ENCODING both show UTF8. I also tried to export the data and import it into a fresh database. That fixed the problem for a few days, but it came back after that.

I'm happy about any possible solutions or debugging ideas.

Edit: Some more queries, it works with trim() in the WHERE clause:

peertube=# SELECT "preferredUsername", "preferredUsername" FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername | btrim  |               md5
-------------------+--------+----------------------------------
 mailab            | mailab | 3d83ba6a9e5391c0c4d0253fbb2b01aa

However, this still seems weird because if there is whitespace, the hash of the field should be different. In fact, the md5 is the same:

peertube=# SELECT "preferredUsername", md5("preferredUsername"), md5('mailab') FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername |               md5                |               md5
-------------------+----------------------------------+----------------------------------
 mailab            | 3d83ba6a9e5391c0c4d0253fbb2b01aa | 3d83ba6a9e5391c0c4d0253fbb2b01aa

Edit 2:  Length is the same with or without trim, so that seems to rule out the whitespace theory:

peertube=# SELECT "preferredUsername", length("preferredUsername"), length(trim("preferredUsername")) FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername | length | length
-------------------+--------+--------
 mailab            |      6 |      6

Edit 3: Details of the affected table: https://gitlab.com/snippets/1840320

Edit 4: Someone suggested the get_byte() function to check that there are no unexpected utf8 characters. And in fact, every single character corresponds to an ascii value in the range 97-122.

I would check and see if there are any white spaces after the Value 'User2' in the database.

I would also look at the application inserting the data and if possible profile the data stream before insertion to determine if the application is incorrectly adding white spaces.

Alternatively I would look at the source of the 'User2' and ensure that the source does not contain white spaces.


Percona's xtrabackup --apply-log fails with "all log files must be created at the same time" I'm trying to figure out why applying logs on my xtrabackup files isn't working.
I created a VM running MySQL 5.5.24. Then I created the sakila test database and backed it up with the following command:

innobackupex --user=bckuser --password=XXXX --no-timestamp --compress /var/lib/mysql/dumps/backup/;

This completed successfully. After that I wanted to apply the logs with this command:

innobackupex --user=bckuser --password=XXXX --apply-log /var/lib/mysql/dumps/backup/;

But I get the following error:

140912 21:46:19  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/var/lib/mysql/dumps/backup/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/var/lib/mysql/dumps/backup --tmpdir=/tmp

xtrabackup_55 version 2.1.9 for Percona Server 5.5.35 Linux (x86_64) (revision id: 746)
xtrabackup: cd to /var/lib/mysql/dumps/backup
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(8564748)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
140912 21:46:19 InnoDB: The InnoDB memory heap is disabled
140912 21:46:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140912 21:46:19 InnoDB: Compressed tables use zlib 1.2.3
140912 21:46:19 InnoDB: Initializing buffer pool, size = 100.0M
140912 21:46:19 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
140912 21:46:19  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
140912 21:46:20 InnoDB: Error: all log files must be created at the same time.
140912 21:46:20 InnoDB: All log files must be created also in database creation.
140912 21:46:20 InnoDB: If you want bigger or smaller log files, shut down the
140912 21:46:20 InnoDB: database and make sure there were no errors in shutdown.
140912 21:46:20 InnoDB: Then delete the existing log files. Edit the .cnf file
140912 21:46:20 InnoDB: and start the database again.
xtrabackup: innodb_init(): Error occured.
innobackupex: Error:
innobackupex: ibbackup failed at /usr/bin/innobackupex line 2560.

I have googled a lot, but I can't find out what's wrong. Do you guys have any idea?

Check the log, a bit above the error:

InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!

It is trying to create new innodb files because they are not found.

You need to decompress the files before applying the log:

innobackupex --decompress /...

Then, apply the log.

BTW, you do not need to specify the user and password once the first backup process was done.


Checkpoints are occurring too frequently during pg_restore Under PostgreSQL 9.2.2 (Windows 32 bits), I have a pg_restore command that systematically results in log warnings about the checkpoint frequency, for example:

LOG:  checkpoints are occurring too frequently (17 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

The database is about 3.3 Gb in size with 112 tables/160 views and restores in about 14 minutes.

Is it normal for that to occur during a pg_restore?

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just like the error message hints.

You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even what the manual suggests (including an explanation):

  Temporarily increasing the checkpoint_segments configuration variable
  can also make large data loads faster. This is because loading a large
  amount of data into PostgreSQL will cause checkpoints to occur more
  often than the normal checkpoint frequency (specified by the
  checkpoint_timeout configuration variable). Whenever a checkpoint
  occurs, all dirty pages must be flushed to disk. By increasing
  checkpoint_segments temporarily during bulk data loads, the number of
  checkpoints that are required can be reduced.

Related answer with more details:

Configuring PostgreSQL for write performance

Postgres 9.5

The upcoming new release has a smarter approach. Quoting the beta release notes:

  Replace configuration parameter checkpoint_segments with min_wal_size
  and max_wal_size (Heikki Linnakangas)

  This allows the allocation of a large number of WAL files without
  keeping them if they are not needed. Thus the default for max_wal_size
  has been increased to 1GB.

Aside: the number of views is barely relevant, those do not contain any data, just the "recipe", i.e.: the query and some attributes of the view. For the question at hand, basically only the total size of the backup file matters.


Fire trigger after the complete transaction commit I try to call an external script with a trigger when I INSERT on my table.

This script have to get some data, especially the last row added (which one fired the trigger)

According to the documentation, CONSTRAINT TRIGGER and DEFERRABLE are executed inside the transaction so my external script can't get the last row added.

How can I fire my trigger after the transaction completely commited ?

You can get a behaviour similar to what you're asking by using the LISTEN / NOTIFY mechanism of PostgreSQL. You can use the payload of the NOTIFY to pass values to your external process.
From the documentation:

The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.
[...]
NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.

(emphasis mine)
The difference between this and a trigger is: the external script will not be started by the database. It needs to be a process that is programmed using a language that allows for connections to the database and some kind of callback system, and that can be started, and be listening to all notifications from the database. When a notification (message) is received, it needs to act accordingly. Your process will behave somehow like a daemon or service. I know you can do this using a program written in C, C++ or C#, but I'm not sure it can be done with a shell script.
See also Asynchronous Notification.


Copying mysql data from one db to another with a where clause for a subset of tables I'd like to be able to move mysql data from one db to another. I only want to move records where account_id=1234 except for tables like account which does not have an account_id column.

Is there any possible way to do this?

By "move" do you mean "copy"?  That is, are you leaving the rows behind?

Two different machines

mysqldump has options to specify particular a database and list of tables.  It also has an optional WHERE clause.  So...

mysqldump -h src -w "account_id=1234" -d db tbl1 tbl2 > some_tables.sql
mysqldump -h src -d db tbl3 tbl4 > other_tables.sql

(Please verify the syntax.)

Then load those two files into some other place (after doing CREATE DATABASE newdb):

mysql -h dest newdb <some_tables.sql
mysql -h dest newdb <other_tables.sql

Same machine

CREATE DATABASE newdb;
CREATE TABLE newdb.tbl1 LIKE db.tbl1;
INSERT INTO  newdb.tbl1 SELECT * FROM db.tbl1 WHERE account_id=1234;
 ditto for tbl2, etc
CREATE TABLE newdb.tbl3 LIKE db.tbl3;
INSERT INTO  newdb.tbl3 SELECT * FROM db.tbl3;
 ditto for tbl4, etc


Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH? I have the following table:
CREATE TABLE [dbo].[MP_Notification_Audit](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [type] [int] NOT NULL,
    [source_user_id] [bigint] NOT NULL,
    [target_user_id] [bigint] NOT NULL,
    [discussion_id] [bigint] NULL,
    [discussion_comment_id] [bigint] NULL,
    [discussion_media_id] [bigint] NULL,
    [patient_id] [bigint] NULL,
    [task_id] [bigint] NULL,
    [date_created] [datetimeoffset](7) NOT NULL,
    [clicked] [bit] NULL,
    [date_clicked] [datetimeoffset](7) NULL,
    [title] [nvarchar](max) NULL,
    [body] [nvarchar](max) NULL,
 CONSTRAINT [PK_MP_Notification_Audit] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MP_Notification_Audit] ADD  CONSTRAINT [DF_MP_Notification_Audit_date_created]  DEFAULT (sysdatetimeoffset()) FOR [date_created]
GO

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUserDateCreated] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC,
    [date_created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

There are over 10000 rows in the table with a [target_user_id] of 100017.
When I execute the following query:
SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 9200 ROWS
FETCH NEXT 10 ROWS ONLY

... I get the following actual execution plan:

Why did SQL Server need to do 9210 instead of 10 clustered key lookups?  The index [IX_MP_Notification_Audit_TargetUserDateCreated] should've allowed it to figure out the 10 RIDs it needed to retrieve to get [patient_id], and only do 10 clustered key lookups, right?
I've also discovered some more odd behaviour - it looks like SQL Server 'punishes' you for not selecting unindexable columns.  If I instead OFFSET 10000 rows, I get the following execution plan:
SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

... with the recommendation of creating an index that includes [patient_id], and an inefficient clustered index scan for the whole table.  The time taken was 0.126s, however this could apparently have been a lot better because when I add the unindexable column [title] to the query, I get this:
SELECT
    [target_user_id], [patient_id], [title]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

... and the nonclustered index is still used, the time taken only being 0.032s.  Does SQL Server basically say "you could've created an index to do this more efficiently, so we're not even going to use the index you do have and we're going to do the lookup inefficiently to punish you", or am I missing something?

It's because the order of operations that the clauses of your query are executed in. The WHERE clause occurs before your OFFSET and FETCH clauses. That's why not only are there about 10,000 (9,000 in your first example) key lookups, but there's just as many index seeks. This is the filtering that occurs as a result of your predicate on target_user_id in your WHERE clause. Execution Plans read from right to left for order of events, so if you follow the plan starting from the index seeks and work your way left you'll see the Top operator which is what represents your OFFSET / FETCH clauses, and thus comes after your WHERE clause.
So simply put, SQL Server first needs to locate the rows based on the filters you apply via your predicates (in the WHERE, HAVING, and JOIN clauses). Then once it finds the correct rows it can apply your OFFSET / FETCH clauses. If it didn't get all the rows based on your filters first, it wouldn't know what rows it'll need to OFFSET / FETCH.
Could have the SQL Server Engine been programmed a little bit logically better in such a case? Possibly. They could've designed the Engine in such a way that realizes a Top operation is going to eventually occur on that index seeked data and apply it before the key lookups occur to at least minimize the amount of key lookups but I would imagine it would only help performance in certain cases, and make things programmatically a lot more complicated that it may not be worth it. For example, the key lookups are happening in parallel to the index seek operation (hence them being equally to the right in the plan steps). If the Top operation were to occur before the key lookups to reduce the data, then the remaining rows key lookups would have to happen serially after that Top operation, resultantly serially after the index seek operation as well, which would be worse performance-wise in some cases.

In regards to your second question on what you've experienced with the execution plan changing from index seeks to clustered index scans back to index seeks again, this is known as the Tipping Point. Basically the Query Optimizer analyzes your query and quickly calculates the cost of each operation among a series of different execution plans it can use to fetch your data, partly based on cached statistics of your data that SQL Server maintains. The sum total of those costs allows the Query Optimizer to choose the execution plan with the lowest cost (ideally the fastest execution plan).
Those cached statistics are based on how many rows exist for each value in each column out of the total rows in the table and are used to do cardinality estimates on each operation that needs to occur. In short, cardinality estimation is the number of rows SQL Server thinks a particular operation is going to return. For example, your WHERE clause in your first example returns approximately 9,000 rows and the SQL Server Engine probably estimated the cardinality to be close to that number, which resulted in the cost of an index seek operation being sufficient enough that an index seek was chosen.
When you explicitly told SQL Server to return 10,000 rows instead, it likely tripped the Tipping Point, which made it think the amount of rows it needs to return will be more efficient with an index scan operation instead, because the cardinality of rows is high enough that the SQL Engine thinks a scan will likely encounter more contiguous rows efficiently as opposed to doing 10,000 index seeks. (Long story short, the Optimizer isn't perfect, especially when dancing so close to the tipping point, and an index seek is probably still more efficient here.)
As to why when you added another unindexed column to your SELECT list causes the Query Optimizer to choose a plan not over the tipping point, I couldn't precisely tell you for sure (especially without the actual execution plan in front of me) because it's pretty complicated what's going on under the hood, but generally speaking the estimations it did, based on the cached statistics of your data at that point in time, resulted in it calculating costs that were not over the tipping point any more, and resulted in it choosing an execution plan that leverages index seeks instead.


Remote RESTORE of database through SSMS How can I RESTORE a database through SQL Server Management Studio by connecting "remotely" to it?

If I know the "IP address" of the target machine what else do I need to know?

If the port number is a requirement, how can I find it?

I only know the IP address of the target machine. I want to know on which port the SQL Server runs. I am given the task to connect to the SQL Server instance and take a backup.

What TCP Port is SQL Server running under? by Colin Stasiuk details several ways to determine the port number SQL is running on

go right to the registry and look at the TCP settings

SQL 2005

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
  MSSQL.<InstanceNumber>\MSSQLServer\SuperSocketNetLib\TCP\

SQL 2008

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
  MSSQL10.<InstanceName>\MSSQLServer\SuperSocketNetLib\TCP\

xp_readerrorlog.  This will show the current SQL Server log and you
can find the row "Server is listening on".
use the SQL Server Configuration Manager
if you are on the machine, look at Task Manager

Then you should be able to use 'IPAddress, Port' to connect.  Or you can create a client side Alias using CliConfg on the client that allows you to connect to an alias.


Automatically give grants to particular users PostgreSQL Every time when someone creates a table I have to give grants on select, insert, and so on to particular users. Are there any solutions to make it automatically?
Thanks!

There is alter default privileges, but this does not accept a wild card for the users it will apply to (i.e. the creating roles).  You have to list every possible creating user, which is quite a drag.


When should you split a database into 2-3 databases for performance reasons? I'm not an expert on database performance. A colleague has suggested splitting a BI data warehouse database into several databases based on functional business unit - I believe still on one machine though I'm not sure - for performance reasons - there are many processes, procedures, logging, and monitoring happening. This has nothing to do with security or permissions - in fact those will be made more complicated potentially.

Again I can't say I'm the expert but for some reason, it doesn't seem logical to me. DB in total is about 500 GB and we have about 200 tables.
Naturally it seems like most solutions from my cursory research recommend improving the disc drive speed (like SSD) or RAM (currently an anemic 4 GB) for performance reasons. Or possibly keeping one logical database, but fragmenting the schemas into 2-3 different machines.

But I don't know. Is there a significant use case or performance reason to split one database into 2 logical databases - other than user permissions/ security which aren't a factor here. There will be many views that span across these 3-4 databases.

Do read operations, normalization, tuning options, or database/table locks come into play here? My instincts tell me no - those are entirely separate issues, but I can certainly be wrong. I'm just concerned that we are going to trade minimal, if not zero, performance improvements for a whole lot of added complexity and maintenance.

I obviously am coming in here already leaning/ biased at one side, but wondering your thoughts.

Size is not really a reason to do a database split.  If there is slow query performance or other factors like that, then maybe.  But keep in mind, splitting a database using the same hardware and storage will provide very few gains.  There is also the possibility of partitioning the data which will accomplish something similar.

The problem is that this was devised as a solution that it may not fit.  I have multi-terabyte databases that respond faster than 100 GB databases because it is tuned and indexed within an inch of it's life.  Replacing hardware will often cover up poor database and query design.  An immediate fix would be to add more RAM.  8 GB would be the minimum I would want to see in a SQL server, but given the size of the database I would suggest a minimum of 128 GB.

Ultimately we need more information on what performance issues you are experiencing in order to guide you to a more complete solution.


What is exadata database? I need to know Is oracle exadata database is same as the oracle database which is installed in my laptop. What is the main difference.
Can I simply take backup from exadata database and restore it in my laptop?

Oracle Exadata database machine is a hardware solution from Oracle which runs the usual Oracle Database Software. However it has an extension: the disks are accessed by special storage nodes which are smart: when the DB tells them to read blocks from a database table they can also tell them filter conditions: the result is that IO bandwidth and caches are distributed to multiple machines and table scans are parallelized (offloading from the main database machine). Besides that there can be additional Flash Cache and the interconnect use fast Infiniband network connections.

This makes it not only faster for typical OLAP queries, but also more robust against wild ad-hoc queries. The disadvantage is that you need to have a big workload so it is worth it.

Exadata uses Linux or Solaris as the Operating System.

It must not be confused with ODA, which is the database appliance without the special storage nodes.

Exadata is for the purpose of applications interacting with it invisible. This also means you can import backups from there and run the same queries (sans management queries for the distributed storage of course).


Mysql trigger not working, syntax issues I have this trigger

CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN
    SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y;
END;

mysql 5.1.72-0ubuntu0.10.04.1 (Ubuntu)

But I am getting a syntax error, and I don't see where...

EDIT:

DELIMITER //
CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END//
DELIMITER ;

this still got a syntax error

EDIT2:

I think I need the old syntax from here:
https://dev.mysql.com/doc/refman/5.0/en/commit.html

I don't know how to complete the code though...

START TRANSACTION;
SELECT p.* FROM players p WHERE EXISTS (SELECT w.* FROM walls w WHERE w.x=p.x AND w.y=p.y);
COMMIT;
ROLLBACK;

For 5.5 and later it is possible to use signals:

delimiter @

create trigger checkcollision
after update on players
for each row
begin
    declare dummy int default 0;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    if (dummy = 1) then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message';
    end if;
end @

delimiter ;

For 5.1 and earlier version there is no support for signal. You could try to mimic it with an forced exception, like division by zero or by referencing something that does not exist. You don't get a nice error message though:

delimiter @

create trigger checkcollision
after update on players
for each row  begin
    declare dummy int default 0;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    if (dummy = 1) then
        select 1/0 into dummy;
    end if;
end @

delimiter ;

A slightly more elegant way is to use an exit handler which is supported in 5.1, still no error message though:

create trigger checkcollision
after update on players
for each row
begin
    declare dummy int default 0;
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    select 1/0 into dummy;
end @

If no wall is found an empty exit handler is invoked, otherwise the trigger continues and an deliberate error is made.

By adding a dummy table like:

create table dummy (msg varchar(100) primary key);

We can force a primary key violation by inserting the same value twice from the trigger:

delimiter @
create trigger checkcollision
after update on players
for each row  begin
    declare dummy int default 0;
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    insert into dummy (msg) values ('ERROR: Collision')
                                 , ('ERROR: Collision');
end @
delimiter ;

We will get an error message like (tested in 10.0.20-MariaDB):

ERROR 1062 (23000): Duplicate entry 'ERROR: Collision' for key 'PRIMARY'

You might want to encapsulate this into a stored procedure:

create procedure my_signal (msg varchar(100))
begin
    insert into dummy (msg)
    values ('ERROR: Collision')
         , ('ERROR: Collision');
end @

which can be called from the trigger:

create trigger checkcollision
after update on players
for each row
begin
    declare dummy int default 0;
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    call my_signal('ERROR: Collision');
end @


Is a @CATALOG argument required in linked server to connect to READ-ONLY secondary? I have 1 primary and two secondaries.  Node 3 is READ-ONLY.  I created a linked server as follows (as described in Microsoft Docs):

EXEC sp_addlinkedserver
@server=N'Node3',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr = N'ApplicationIntent=ReadOnly',
@datasrc= 'AGListenerName';

When using this linked server it will always connect to Node 1 (primary).  I verify my connection as follows:

SELECT *
FROM OPENQUERY([Node3],
SELECT DATABASEPROPERTYEX (''AnyDatabase'', ''Updateability'' ) ')

This returns:  READ_WRITE

If I specify a database (catalog) in the linked server as follows:

EXEC sp_addlinkedserver
@server=N'Node3',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr = N'ApplicationIntent=ReadOnly',
@datasrc= 'AGListenerName',
@catalog= 'database';

The connection is made to the correct READ-ONLY secondary (Node 3).  I verify in the same manner as above.

It returns:  READ_ONLY

This is the same behavior in find in SSMS when creating a connection to a READ-ONLY instance.

Am I configuring the linked server incorrectly or is the @CATALOG argument required?  Thanks.

Am I configuring the linked server incorrectly or is the @CATALOG argument required?

Regardless of the style in which you connect (SSMS, Linked Server, SQLCMD, JDBC Client, Whatever), there are a few things required in the connection string in order for Read Only Routing to properly work:

TCP/IP must be the protocol used
The listener name must be used (depending on version)
A database belonging to the AG must be in the connection string
Application Intent must be set to ReadOnly

Any deviation from the above will immediately result in read only routing not working. Thus, to answer your question, you need to somehow specify the default database as part of the connection string.


SQL Server replication methodology I have a relatively large production environment (150GB) in SQL Server, and I need to duplicate it somewhere else for querying (specifically: I need to create an environment identical to the production so that queries are not hitting the live production environment - I used the word "duplicate" because I do not want to suggest a specific method).
Requirements:
1- the server has only 2 cores with 32GB RAM, so it's not very advanced hardware wise. Production environment is Standard SQL 2014, and receiving environment is Enterprise SQL Server 2017.
2- I need good latency (1 minute): this means that the delay cannot be more than a minute.
3- This is just a one-way duplication. I will not be making changes in the replica and expect it to end up in the production. This is just one-way duplication.
4- "live" connection (meaning changes made to the production must be sent back to the duplicate within 1 minute or so)
What I have done:
I thought of doing replication. But this is such a bad idea for a few reasons:
a) most tables (there are hundreds of them) do not have PKs. I understand that for replication you need PK.
b) there are so many tables, so it's hard to handpick them all for replication.
c) I understand that replication puts a lot of load on the production server. So a lighter version is better.
And then I looked at Snapshot. I was wondering if it's possible to use both, and if not, can snapshot alone be enough for above scenario? Any help/direction would be much appreciated.
NOTE: thank you for the recommendation that tables need PKs. We all know this, however, this is a vendor environment. Adding PKs is out of question, please consider that as a requirement as mentioned.

Transactional Replication is the best fit for this scenario.
Your tables should have primary keys, which is a pre-requisite for Transactional Replication.  The load on the publisher is minimal.  The bulk of the work is done by the Distributor, which can be on the same instance as the Publisher, or the Subscriber, or a completely separate SQL instance.
If you are willing to upgrade the production database to SQL Server 2017 Enterprise Edition you can create a readable replica on the secondary with AlwaysOn Availibility Group, with or without joining the servers in a Windows Server Failover Cluster.


Why didn't primary file size change after moving large table to new file group? After looking and finding the close answer, I'd like to post a follow-up, but separate, question to this one (split db to fit on ssds)

I have a 60 gig database that is growing quickly, due to one table that holds scanned documents. We are pushing to move old paper to scanned docs, and I have no choice that this is how we handle saving scanned documents.
I was thinking about adding a new file group, and move that table to the new FG by recreating the unique clustered index for that table to the new FG.

I did that on a test box and surprisingly, the OS file size of the original MDF did not significantly change, nor did the new secondary FG size up to the corresponding ratio of that one table's size to all the tables.

I was thinking the OS file sizes should change on the order of the size of the table that was moved.  What am I missing?

Thanks,
Bill

Once you move data out of a filegroup on SQL Server, you must run a DBCC SHRINKFILE command to reclaim that physical space.

The official documentation has all you need to know about this, but the general technique is as follows (this example is from the documentation, unedited).

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

Note that this example assumes your database is called AdventureWorks2012. You'll have to substitute in the logical name for the file and database.

The recovery model change in the above example must be preceded and followed by appropriate backups. Make sure your backups are well-tested, and stored in a secure off-site location as well. While this particular command shouldn't cause any corruption, backups are critical.


Should we still be using QUOTENAME to protect from injection attacks? I was looking at an old stored procedure today and noticed it was using quotename on the input parameters. After doing some digging to figure out what that does exactly I came across this site. I now understand what it does and how to use it but the site says it is used as a mitigation from SQL Injection attacks. When I used to develop apps that directly queried a database, using asp.net, I would use ADO.Net parameters to pass user input in as a literal value and never really worried about protecting it in my stored procedures.

I am now writing a stored procedure that will be used by applications that I do not write so I do need to try and protect from injection attacks at the procedure level, is the quotename the best way to do this or is there a newer function/better method?

Code that got me on this thought pattern (@parm1 is a user input parameter):

'SELECT project [Project], project_desc [Description],
        customer [Customer], cpnyid [Company]
FROM PJPROJ (nolock)
where project like ' + quotename(@parm1,'''') + '

Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As well as SQL injection mitigation this also means your code will work correctly for non standard identifier names.

The function is only appropriate for object names (e.g. table, column, database names) though.

You should try and parameterise everything else and use sp_executesql, passing in parameters rather than concatenating those into the query string.

The definitive article on this topic is still The Curse and Blessings of Dynamic SQL

Edit. Now you have supplied the code I see it is passing the second parameter of ' to add the outer quotes and escape any single quotes by doubling them up before injecting them into the string. This is not a good use of quotename. It will fail (return null) if the string is greater than 128 characters.

Additionally It may still leave SQL injection possibilities if the string contains U+02BC instead of the standard apostrophe and then the string is assigned to a varchar after the sanitation (where it can silently get converted to a regular apostrophe )

The correct way to do this is to leave the query parameterised. And then pass the @parm1 value in to sys.sp_executesql

DECLARE @Sql NVARCHAR(MAX);

SET @Sql = '
SELECT project      [Project],
       project_desc [Description],
       customer     [Customer],
       cpnyid       [Company]
FROM   PJPROJ (nolock)
WHERE  project LIKE @parm1
';

EXEC sys.sp_executesql
  @Sql,
  N'@parm1 varchar(100)',
  @parm1 = 'foobar%';


MySQL dynamically optimize innodb tables without "file per table" setting We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking websites offline.  I am thinking because tables are locked connections get backed up, or something like that. So I am thinking it’s the procedure that is the cause of this error and am looking at ways to refactor it to be smarter as to which tables it optimizes and/or chop up task to be run over many days.

This procedure only filters which tables and data bases to run the optimize command on by using LIKE against the table or database name. So, for every table match, “optimize table {tablename}” is run. These tables are both of MyISAM and InnoDB engine types.

One refactor approach would be to only optimize if the table really needs it.  If a fragmented table signifies it needs optimizing then finding out if is fragmented is not too difficult if,  if the table is a MyISAM, or InnoDB using innodb_file_per_table, where you can do the math on the information_schema fields “Data_free”, data_length and index_length.

Unfortunately the MySQL server is not using the “file per table” setting. This makes the “data_free” field seemly useless for this task because every InnoDB table with have the same value i.e. the free space in the ibdata file.

Maybe if I can understand answers to these questions I will better understand the solution i need.

Q 1: How much fragmentation is allowed before it affects performance?
Q 2: Should InnoDB tables even be optimized (some say yes others say no)?
Q 3: How do you test for InnoDB fragmentation if the server does not use the “file per table” option?
Q 4: Is fragmentation the only reason to run “optimize table”?
Q 5: If I do need to run “optimize table” on an InnoDB table should I run ALTER TABLE mydb.mytable ENGINE=InnoDB; and not ANALYZE TABLE
Q 6: Can you selectively tell which innodb tables needs optimizing if the server does not use the "file per table" option?

Before answering the question, please click here to see the InnoDB Infrastructure Map

Based on innodb_file_per_table being disabled, let's go through your questions:

  Q 1: How much fragmentation is allowed before it affects performance?

The system tablespace can grow to the limit of the disk volume.

EXAMPLE: I just answered a question about what do to when the system tablespace reaches the limit of an ext3 disk : How to solve "The table ... is full" with "innodb_file_per_table"?

There may still be some wiggle room inside the system tablespace. However, when the wiggle room dwindles to the point that all 1023 undo logs inside the system tablespace are completely filled and can no longer extend, then you must add a new system tablespace file.

Please note that when I say wiggle room, I am referring to the free space within the system tablespace that must accommodate the following:

Data Dictionary
Double Write Buffer (can be disabled but not recommended)
Insert Buffer (Cached Index Changes in System Tablespace instead of the OS)
Rollback Segments (1023 slots)
Undo Logs (referenced from the Rollback Segments)
Please refer Back to the InnoDB Infrastructure Map

  Q 2: Should InnoDB tables even be optimized (some say yes others say no)?

If you run OPTIMIZE TABLE, you basically make the data and index pages contiguous in the system tablespace. This defragments the table and accesses all data and indexes quicker until the fragmentation reappears over time in production use. This can introduce new areas of fragmentation. Again, all that fragmentation can fill up with data and indexes. This will endanger the wiggle room I mentioned before.

  Q 3: How do you test for InnoDB fragmentation if the server does not use the “file per table” option?

Back on Aug 27, 2012, I answered this post : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I explained there how to get the fragmentation. In essence, you do this:

Goto the OS and run

cd /var/lib/mysql
ls -l ibdata1 | awk '{print $5}'

This gets you the size of ibdata1 in bytes

SELECT (data_length+index_length) InnoDBDataIndexBytes
FROM information_schema.tables WHERE engine='InnoDB';

This gets you the sum total of data and index pages in bytes

Subtract the sum total from ibdata1's total bytes. The difference represents the wiggle room. This space causes fragmentation, but is constantly in use until ibdata1 gets filled.

CAVEAT : When innodb_file_per_table is enabled, I explain how to get the fragmentation of an individual table: Innodb table with many deletes and inserts - is there any disk space wasted?

  Q 4: Is fragmentation the only reason to run “optimize table”?

Yes. It is far more beneficial for MyISAM and for InnoDB tables (innodb_file_per_table being enabled). Do this with innodb_file_per_table off and you will just make the system tablespace grow faster. See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

  Q 5: If I do need to run “optimize table” on an InnoDB table should I run ALTER TABLE mydb.mytable ENGINE=InnoDB; and not ANALYZE TABLE

Running ALTER TABLE mydb.mytable ENGINE=InnoDB; would indeed shrink when innodb_file_per_table is enabled. Again, it is not worth when innodb_file_per_table is disabled.

  Q 6: Can you selectively tell which innodb tables needs optimizing if the server does not use the "file per table" option?

No you cannot. Why? The INFORMATION_SCHEMA becomes totally useless because all the tables are inside one file. I wrote a script to find to uptime_time (last time an InnoDB table was written) of all InnoDB tables. Is there a way to find the least recently used tables in a schema? script only works for innodb_file_per_table. This shows that you cannot ascentain that fragmentation with ease. You could resort to more aggressive techniques like dumping the tablespace map and located segments with unused space : See this blog post : http://www.markleith.co.uk/2009/01/19/innodb-table-and-tablespace-monitors/. This is way too much firepower to deal with. You could just run the OPTIMIZE TABLE to eliminate segment fragmentation, but this brings us back full circle to getting everything out of ibdata1.

SUGGESTION

If you want to remove all data and index pages from ibdata1 and shrink ibdata1 permanently, please read my Oct 29, 2010 StackOverflow post Howto: Clean a mysql InnoDB storage engine?

As you can see, this subject is not new to me

Jan 13, 2013 : How to check growth of database in mysql?
Sep 23, 2012 : MySQL / InnoDB: Does daily optimization improve performance?
Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

EPILOGUE

Running OPTIMIZE TABLE is not the biggest reason that ibdata1 grows quickly. Please see this post from mysqlperformanceblog and learn about the other contributing factors.

Please remember that most who run OPTIMIZE TABLE do so sequentially. You could probably script many of them in parallel. Of course, you need to convert to innodb_file_per_table like I mentioned before.


How do you combine multiple update statements for the same row using MySQL trigger Background:
Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger.
Code:
Here's a simplified example of what I'm trying to do, which does its job fine, but inefficiently:
DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
  BEFORE UPDATE ON first_table
  FOR EACH ROW
BEGIN
  /* putting IF statements on one line so it's easier to see what's happening */
  IF NOT(OLD.firstname <=> NEW.firstname)   THEN UPDATE second_table SET firstname  = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.middlename <=> NEW.middlename) THEN UPDATE second_table SET middlename = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.lastname <=> NEW.lastname)     THEN UPDATE second_table SET lastname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.nickname <=> NEW.nickname)     THEN UPDATE second_table SET nickname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.dob <=> NEW.dob)               THEN UPDATE second_table SET dob        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.email <=> NEW.email)           THEN UPDATE second_table SET email      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.address <=> NEW.address)       THEN UPDATE second_table SET address    = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.city <=> NEW.city)             THEN UPDATE second_table SET city       = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.state <=> NEW.state)           THEN UPDATE second_table SET state      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.zip <=> NEW.zip)               THEN UPDATE second_table SET zip        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.phone <=> NEW.phone)           THEN UPDATE second_table SET phone      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
END;
//
DELIMITER;

The problem:
As you can see, depending on how many columns are updated in `first_table`, there can be as many as 11 update statements on the same row in `second_table`.
The question:
Is there any way to combine the update statements into one?

You can use prepared statemnts for this purpose to have only one UPDATE command.
A danger from SQL injection isn't possible, because there is no user input.
DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
  BEFORE UPDATE ON first_table
  FOR EACH ROW
BEGIN
    SET @switch = 0;
    SET @sql = 'UPDATE second_table SET ';
    IF NOT(OLD.firstname <=> NEW.firstname) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'firstname = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.middlename <=> NEW.middlename) THEN
            if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'middlename = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.lastname <=> NEW.lastname) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'lastname = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.nickname <=> NEW.nickname) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'nickname = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.dob <=> NEW.dob) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'dob = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.email <=> NEW.email) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'email = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.address <=> NEW.address) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'address = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.city <=> NEW.city) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'city = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.state <=> NEW.state) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'state = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.zip <=> NEW.zip) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'zip = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.phone <=> NEW.phone) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'phone = CURRENT_TIMESTAMP');
    END IF;
    SET @sql = CONCAT ( @sql,' WHERE id = OLD.id;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

That said, a GMP audit requieres also to have the old value saved.