What does this deadlock report from MySQL mean? Here is the output:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-12-23 05:57:47 7f23d73c6700
*** (1) TRANSACTION:
TRANSACTION 32990178, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 314344, OS thread handle 0x7f23d6480700, query id 99645000 104.197.97.45 dsprod update
insert into pings (lastProcessTime, id, type) values (1450845, '52c7e28eebb26a86fb1505b5', 'INCIDENT')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 4 n bits 376 index `PRIMARY` of table `alerts`.`pings` trx id 32990178 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 24; hex 353363336635646430636632336133633062623934613866; asc 53c3f5dd0cf23a3c0bb94a8f;;
 1: len 3; hex 415050; asc APP;;
 2: len 6; hex 000001f76076; asc     `v;;
 3: len 7; hex 5a000001e10b80; asc Z      ;;
 4: len 4; hex d67a36f8; asc  z6 ;;

*** (2) TRANSACTION:
TRANSACTION 32990184, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 314341, OS thread handle 0x7f23d73c6700, query id 99645014 104.197.97.45 dsprod update
insert into pings (lastProcessTime, id, type) values (1450848, '52c7e28eebb26a86fb1505b5', 'INCIDENT')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 17 page no 4 n bits 376 index `PRIMARY` of table `alerts`.`pings` trx id 32990184 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 24; hex 353363336635646430636632336133633062623934613866; asc 53c3f5dd0cf23a3c0bb94a8f;;
 1: len 3; hex 415050; asc APP;;
 2: len 6; hex 000001f76076; asc     `v;;
 3: len 7; hex 5a000001e10b80; asc Z      ;;
 4: len 4; hex d67a36f8; asc  z6 ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 4 n bits 376 index `PRIMARY` of table `alerts`.`pings` trx id 32990184 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 24; hex 353363336635646430636632336133633062623934613866; asc 53c3f5dd0cf23a3c0bb94a8f;;
 1: len 3; hex 415050; asc APP;;
 2: len 6; hex 000001f76076; asc     `v;;
 3: len 7; hex 5a000001e10b80; asc Z      ;;
 4: len 4; hex d67a36f8; asc  z6 ;;

*** WE ROLL BACK TRANSACTION (2)

And here is the code that produces this:

@Autowired
private PingRepository repository;

@Transactional
public void processPing(Ping ping)
{
    Ping storedPing = repository.findById(ping.id);

    if (storedPing == null)
    {
        repository.save(ping);
    } else
    {
        if (ping.lastProcessTime > storedPing.lastProcessTime)
        {
            storedPing.setLastProcessTime(ping.lastProcessTime);
        }
    }
}

public interface PingRepository extends CrudRepository<Ping, PingKey>
{
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Ping findById(PingKey id);
}

Some questions:

What exactly is going on in the deadlock report?
it feels like txn 2 is trying to acquire a lock it already has. why?
this is the only txn in my code that accesses the pings table. why does this code deadlock at all?

Update:

Adding table schema:

CREATE TABLE `pings` (
  `id` varchar(255) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT '',
  `lastProcessTime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

You didn't specify what language your code was in, but it appears that the PESSIMISTIC_WRITE is causing a FOR UPDATE type of select.

As such, the following is happening:

Transaction A

SELECT * FROM pings where id = '52c7e28eebb26a86fb1505b5' FOR UPDATE;

Transaction B

SELECT * FROM pings where id = '52c7e28eebb26a86fb1505b5' FOR UPDATE;

At this point, both transactions are holding a lock on the space you want to insert into.

Transaction A (continuing)

insert into pings (lastProcessTime, id, type) values (1450845, '52c7e28eebb26a86fb1505b5', 'INCIDENT')

Transaction A at this point will wait, as it wants B to release its lock before it continues.

Transaction B (continuing)

insert into pings (lastProcessTime, id, type) values (1450848, '52c7e28eebb26a86fb1505b5', 'INCIDENT')

However, B not only didn't release its lock, but it wants to insert the same record. The database says no way, and forces one (B, in this case) to deadlock and be undone.

This is actually extremely easy to replicate yourself. Just start 2 transactions (I used two Terminal windows to connect to my MySQL instance), one for each transaction, A and B. Start each with START TRANSACTION, and then just enter the commands in the same order as above, in each respective window. Any table with any primary key will work, just adjust the statements to each try to insert the same row.

Once you submit the second insert, you'll see the deadlock (generally applied to the transaction that tried to insert second.)

So, your issue is fully expected, as the FOR UPDATE on the SELECT statement is doing exactly what it is intended to do (which is to keep other transactions from changing the result of the SELECT until the transaction ends.)

Edit: Well, fully expected except for the fact that one would expect the SELECT ... FOR UPDATE to wait, regardless of whether there are any rows returned for the SELECT. However, if there are no rows, the behavior is as described above, but if there are rows returned, Transaction B will wait before returning any results from its SELECT. See this the bug report on this issue.

I would suggest changing your code to check for a duplicate key after inserting (either catching the exception or using ON DUPLICATE KEY ...), rather than trying to do a SELECT ... FOR UPDATE and then an insert.


How to select distinct for one column and any in another column? I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column.  For example, consider the following table with two columns, key and value:

key     value
===     =====
one     test
one     another
one     value
two     goes
two     here
two     also
three   example

I wish to get back one sample row, chosen arbitrarily, from each distinct key, perhaps getting these three rows:

key     value
===     =====
one     test
two     goes
three   example

How can I formulate such a query in SQL?

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:

SELECT key, value
FROM tableX
GROUP BY key ;

In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:

SELECT key, ANY_VALUE(value) AS value
FROM tableX
GROUP BY key ;

For other DBMSs, that have window functions (like Postgres, SQL-Server, Oracle, DB2), you can use them like this. The advantage is that you can select other columns in the result as well (besides the key and value) :

SELECT key, value
FROM tableX
    ( SELECT key, value,
             ROW_NUMBER() OVER (PARTITION BY key
                                ORDER BY whatever)     --- ORDER BY NULL
               AS rn                                   --- for example
      FROM tableX
    ) tmp
WHERE rn = 1 ;

For older versions of the above and for any other DBMS, a general way that works almost everywhere. One disadvantage is that you cannot select other columns with this approach. Another is that aggregate functions like MIN() and MAX() do not work with some datatypes in some DBMSs (like bit, text, blobs):

SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;

PostgreSQL has a special non-standard DISTINCT ON operator that can also be used. The optional ORDER BY is for selecting which row from every group should be selected:

SELECT DISTINCT ON (key) key, value
FROM tableX
-- ORDER BY key, <some_other_expressions> ;


Postgres nested hstore select query I have a carts table with items hstore column. An example entry in this column is:

carts.items row#1 = {
                     "1614" => {:quantity=>"100", :price_cents=>1655},
                     "1938" => {:quantity=>"50", :price_cents=>1955},
                     "1983" => {:quantity=>"100", :price_cents=>2255},
                     "1322" => {:quantity=>"10", :price_cents=>4455},
                     "1691" => {:quantity=>"25", :price_cents=>1055},
                     "1734" => {:quantity=>"20", :price_cents=>1255}
                    }

carts.items row#2 = {"1614"=>{:quantity=>"50", :price_cents=>1655}}

So my carts table would look like this:

    id   | items
---------+-------
    1    |   {"1614"=>{:quantity=>"100", :price_cents=>1655}, "1938" => {:quantity=>"50", :price_cents=>1955},"1983"=>{:quantity=>"100", :price_cents=>2255},"1322"=>{:quantity=>"10", :price_cents=>4455},"1691"=>{:quantity=>"25", :price_cents=>1055},"1734"=>{:quantity=>"20", :price_cents=>1255}}
    2    |   {"1614"=>{:quantity=>"50", :price_cents=>1655}}

You will notice that there is one duplicate id (1614) in the hash, but its quantity is different.

I want to write a query that will return a table with the item id counts and the total quantity. It should look like this:

 item_id | count | total
---------+-------+------
   1614  |   2   |  150
   1938  |   1   |  50
   1983  |   1   |  50
   1322  |   1   |  100

Here is the query that I am working with:

SELECT
skeys(carts.items) as item_ids,
COUNT(*) AS count,
svals(carts.items) as items
FROM carts
GROUP BY
skeys(carts.items),
svals(carts.items)

It returns:

 item_id | count | total
---------+-------+------
   1614  |   1   |  {:quantity=>100}
   1614  |   1   |  {:quantity=>50}
   1938  |   1   |  {:quantity=>50}
   1983  |   1   |  {:quantity=>50}
   1322  |   1   |  {:quantity=>100}

I aslo have tried:

SELECT key, count(*) FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY count DESC, key;

Which gives me this:

item_id | count
---------+-------
   1614  |   2
   1938  |   1
   1983  |   1
   1322  |   1

Original question (dupes within hstore value)

I think the root cause of your problems is explained in this quote from the manual:

  Each key in an hstore is unique. If you declare an hstore with
  duplicate keys, only one will be stored in the hstore and there is no
  guarantee as to which will be kept.

Bold emphasis mine.

You are mistaken in assuming that you could have the same key twice in a single hstore value.

I can also not reproduce your count of 2. I get a count of 1 for the key 1614. Start by not using count as column name. It is a reserved word (but allowed in Postgres).

I get (tested with Postgres 9.1.9):

WITH carts AS (
   SELECT '"1614"=>{:quantity=>"100"}, "1938"=>{:quantity=>"50"}, "1614"=>{:quantity=>"50"}, "1983"=>{:quantity=>"100"}, "1322"=>{:quantity=>"10"}, "1691"=>{:quantity=>"25"}, "1614"=>{:quantity=>"77"}, "1734"=>{:quantity=>"20"}'::hstore items
    )
SELECT key, count(*) AS ct FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY ct DESC, key;

Result:

 key  | ct
------+----
 1322 |  1
 1614 |  1
 1691 |  1
 1734 |  1
 1938 |  1
 1983 |  1

Updated question (dupes across multiple rows)

To aggregate values you need a subquery (or CTE).

Simplified test case:

CREATE TEMP TABLE carts(c_id serial, items hstore);
INSERT INTO carts(items) VALUES
   ('"1614"=>"100", "1938"=>"50", "1983"=>"100", "1322"=>"10", "1691"=>"25", "1734"=>"20"')
  ,('"1614"=>"50"');

Query:

SELECT item_ids, count(*) AS ct, sum(items) AS sum_items
FROM (
   SELECT (each(items)).key AS item_ids
         ,(each(items)).value::int AS items  -- assuming values can be cast to int
   FROM   carts
   ) sub
GROUP  BY 1           -- positional parameter is just a notational convenience
ORDER  BY 2 DESC;

Result:

 item_ids | ct | sum_items
----------+----+-----------
 1614     |  2 |       150
 1734     |  1 |        20
 1691     |  1 |        25
 1983     |  1 |       100
 1938     |  1 |        50
 1322     |  1 |        10


More efficient SQL for different databases with same schema I have data stored in different monthly databases that have the exact same name schema for the tables and columns. Each monthly database has ~10 million rows. I'm looking for a more efficient way to select the rows I want from each database based on the same variable. There are only 5 to 10 rows from each month for that variable.

I also need to JOIN this different monthly data to the same separate database. I am currently using UNION to combine the data. So:

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   db_JAN.tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

    UNION

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   db_FEB.tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

    UNION

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   db_MARCH.tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

    .
    .
    .

If the SELECT, JOIN and WHERE clauses are the same across different databases with the same naming schema, is there a more efficient way of writing this query? Something like:

    SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
    FROM   (db_JAN,db_FEB,db_MARCH...).tab1 a
    INNER JOIN db_OTHER.tabX b
    ON (a.DATE = b.DATE AND a.someID = b.someID)
    WHERE a.col1='variable1'

This question might be applicable but I'm not sure as I'm relatively new to SQL and am not sure about sequential table scans or indexing:

"How to make a union view execute more efficiently?"

I don't believe it is a hierarchical query and this question doesn't help me either.

NOTE: I am using SAS to run the query connecting to Teradata SQL database and have used a MACRO VARIABLE for 'variable1' in my PROC SQL statement. I am not sure if I can use a MACRO VARIABLE for the databases but that could be an option if I can't do it in SQL itself.

There are various ways to write the query in shorter form. I'm not sure if they could improve performance (or make it worse!)

The best lines of attack if the query isn't efficient is to change it into UNION ALL (provided that the results are equivalent) and examine/optimize the parts separately, make sure indexes are used, etc.

One way to rewrite is to move the UNION or UNION ALL inside a derived table and then join:

SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
FROM
    ( SELECT col1, col2, col3, DATE, someID
      FROM db_JAN.tab1    WHERE col1 = 'variable1'
      UNION ALL
      SELECT col1, col2, col3, DATE, someID
      FROM db_FEB.tab1    WHERE col1 = 'variable1'
      UNION ALL
      ---
    ) a
INNER JOIN db_OTHER.tabX b
ON (a.DATE = b.DATE AND a.someID = b.someID) ;


How can I design a table with greater than in where statement and ordering to avoid filesort? I have a list of documents that I store along with creation date and rank:
CREATE TABLE `document` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `rank` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_combo` (`category_id` ASC, `created_at` ASC, `rank` ASC)
) ENGINE=InnoDB;

I would like to get a list of 10 documents that belong to category 30, were created_at is after certain point in time and order it by rank:
EXPLAIN
SELECT
    id
FROM
    docs.document
WHERE
    category_id = 30
    AND created_at > 1592862179
ORDER BY
    rank
LIMIT 10

It's not surprising that this query has to use filesort:
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+
| id   | select_type | table    | type | possible_keys | key       | key_len | ref   | rows  | Extra                                    |
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+
|    1 | SIMPLE      | document | ref  | idx_combo     | idx_combo | 4       | const | 19596 | Using where; Using index; Using filesort |
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+

If the table is large and I have many records for many years and want to retrieve data for specific date range, filesort becomes performance issue. I would like to be able to retrieve results directly from the index. Is there a way to redesign the table, index or the select query for mysql to only use the index for retrieval and avoid temp tables or filesort?

Can't be done.
To achieve the goal, you would need a 2-dimensional index.  Such does not exist.
WHERE
    category_id = 30
    AND created_at > 1592862179

That much is handled nicely with INDEX(category_id, created_at).  But adding anything onto the end of the index definition will not benefit you
The only way to get the index to be also useful for the ORDER BY is to have ORDER BY created_at (either ASC or DESC).  At that point, it can stop after 10 rows (because of LIMIT 10).
More on indexing:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


MongoDB Benchmark with write-intensive on geo-indexed DB I am using PostgreSQL 9.3 on AWS RDS to store geo information into an indexed table of points.

My data is write-oriented, meaning that I write nearly 1,000 points per each geo search query.

I am considering some scaling solutions, and MongoDB seems like a natural choice because it's mature, scales up well, and supports geo indexes out of the box.

Is there any reliable and recent benchmark that compares MongoDB performance to other SQL and NoSQL solutions, preferably under write-intensive scenarios?

i have used PostGIS for over a decade now and I can tell you for sure that there is no match for it in the NoSQL world.

how many rows do you have? how large is the thing? Mongo is definitely not going to make you happy. I am pretty sure you did something fishy on the PostgreSQL side to even consider using Mongo. Let us fix it ...


Hide columns that are constant across a small number of rows I have a query that returns a small number of rows (usually 2-5) but a huge number of columns (several hundred).  The rows are mostly identical, save for a few places.  I would like to quickly identify them by hiding columns which are constant (e.g., I have 5 products, all of which cost $3, so the cost column should be suppressed). Is there an elegant way to do this using MySQL?  If it helps, most of the columns are numerical.

Here's a tiny example: Suppose my initial query returns

col1|col2|col3|col4
 1  | x  | a  | 3.14
 1  | x  | b  | 3.14

Columns 1, 2 and 4 are "constant", while column 3 is not. Therefore I would like a secondary query to return only column 3:

|col3|
| a  |
| b  |

The problem is that manually finding a small number of non-constant columns is quite difficult with a large number of constant columns.

Columns are not dynamic. They can't be suppressed on the result because at that point it's too late. You have two options

Unpivot

id|ident|x|y
1 | foo |1|2

Becomes

id|ident|key|val
1 | foo | x |2
1 | foo | y |2

The schema is fixed from that point and you can return just what you need.
Use dynamic SQL. The server returns sql that you issue against the server.

You could also fix the schema, if most of those columns are redundant modify the schema.

Update

  I think perhaps you misunderstood my goal. I'm working with a query that returns something like: [...] I want to turn hide/supress the columns which are "constant," as in they don't change from row to row. That would turn the above table into...

I'm very sure I don't misunderstand your goal. We have at least one question a week that is along the same lines we have to turn around for the very same reason. In fact, a year ago yesterday I asked a similar question out of desperation thinking there may be a better way, and was turned around for the same reason.. I can still quote Erwin's relevant parts here,

  Column names and types cannot be dynamic. SQL demands to know number, names and types of resulting columns at call time. Either by explicit declaration or from information in the system catalogs (That's what happens with SELECT * FROM tbl: Postgres looks up the registered table definition.) ... You want Postgres to derive resulting columns from data in a user table. Not going to happen. [...] One way or the other, you need two round trips to the server. Either you create a cursor and then walk through it. Or you create a temp table and then select from it. Or you register a type and use it in the call.

Your use case here is slightly different, but you should still read the above bolded parts at least 15 times, because it has to sink through.

Then you've got to either,

Change the format you're will to accept from the server.
Process the result set on the server that generates the right query, and then issue that query back to the server.

For your example

CREATE TABLE tinyexample(id,ident,key,val)
AS VALUES
  ( 1, 'foo', 'x', 2),
  ( 1, 'foo', 'y', 2);

What you want is something like this. Note this is not the best way to do this, you could save a round trip by running this in a function.

SELECT FORMAT (
  $$
    SELECT FORMAT(
      'SELECT %%s FROM %%I.%%I.%%I;',
      array_to_string(array_remove(ARRAY[%s]::text[],null), ', '),
      %s,
      %s,
      %s
    ) AS inner
    FROM %I.%I.%I;
  $$,
  colexpr,
  quote_literal(table_catalog),
  quote_literal(table_schema),
  quote_literal(table_name),
  table_catalog,
  table_schema,
  table_name
) AS outer
FROM (
  SELECT
    table_catalog,
    table_schema,
    table_name,
    string_agg(  format($$CASE WHEN count(DISTINCT %I) > 1 THEN %s END$$, column_name, quote_literal(column_name)), ', '  ) AS colexpr
  FROM information_schema.columns
  WHERE table_catalog = 'test'
    AND table_schema = 'public'
    AND table_name = 'tinyexample'
  GROUP BY table_catalog, table_schema, table_name
) AS t;

This seems like a mess, most of that's not me though. We have

Issue our first query to the information_schema to get the column listing. Without this you'd have to statically type the columns. That's no fun. This returns query "outer".
Issue our second query ("outer") to find out which columns are not distinct based on their values. This returns query "inner".
Issue our third query ("inner") which returns the resultset we want.


Understanding SQL Server statistics better I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have more buckets given the quantity of records and selectiveness of the field.

Can anyone explain to me why SQL Server is only using two RANGE_HI_KEYs?

NOTE: I get the same stats breakdown on 2014 and 2016

Table

CREATE TABLE [dbo].[Person](
    [BusinessEntityID] [INT] NOT NULL,
    [PersonType] [NCHAR](2) NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [NVARCHAR](8) NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [NVARCHAR](10) NULL,
    [EmailPromotion] [INT] NOT NULL,
    [rowguid] [UNIQUEIDENTIFIER] NOT NULL,
    [ModifiedDate] [DATETIME] NOT NULL,
    [DateOfBirth] [DATE] NOT NULL
)

Index

CREATE NONCLUSTERED INDEX [IX_dbo_Person_DateOfBirth]
ON [dbo].[Person] ( [DateOfBirth] ASC )

Histogram

Statistics for INDEX 'IX_dbo_Person_DateOfBirth'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IX_dbo_Person_DateOfBirth       Jun 24 2016  7:16PM             5000000                         5000000                         2                               0.004236792                     3                               NO                                                              5000000

All Density                     Average Length                  Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4.720544E-05                    3                               DateOfBirth

Histogram Steps
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1/1/1950 12:00:00 AM            0                               255                             0                               1
12/31/2007 12:00:00 AM          4999537                         208                             21182                           236.0276

Very easy. Look for the result of:

select datediff(day,'1/1/1950','12/31/2007');

It will give you your DISTINCT_RANGE_ROWS + 1
That means that SQL Server thinks that all your DOBs are approximately equally distributed across the range with average 236 items per day.
Query your data and see if you have any outlines like: missing day ranges or >1000 DOBs per day.
If you do, than that would be a question to the engine.


Health checking via Database Mail in SQL Server I have a few SQL scripts that I would like to run every day on my SQL Server(s). How can I query multiple times and have the output similar to the following:

Health check for <server>

Query Title 1:

<query results 1>

Query Title 2:

<query results 2>

I am checking things like last good back up, uptime etc so they are not related to each other. I am planning to use sp_send_db_mail inside of a SQL Server Job. Is this the best way of accomplishing this task?

Ideally, I would only like one email.

Update

After battling for ages!!! The solution I have found so far is to put it into a stored procedure and then execute the stored procedure like so:

EXEC msdb.dbo.sp_send_dbmail
        @recipients = N'<email>',
        @body = @body,
        @subject = @subjectt,
        @profile_name = @ProfileName,
        @query ='exec test..sp_checks'

Is this ok for multiple select statements?

This is more just a note on the philosophy rather than the technique.

If you did this, I would make sure to set some limits on it so that it only sent out emails when you were outside those limits.

For example, if you're CPU is spiking to 100% frequently, I would send out the email.  However, if there's no problems with the CPU, I would explicitly, intentionally not send the email.

If you get a daily email from your database, you're going to start ignoring them after a while.  But if you only get them when there's something that you think you should check for, you're more likely to look at the emails.

Also, I'd make those limits configurable (and possibly even make flags so that specific queries can be turned on or off).

Regarding your question of multiple selects inside a single email, it sounds like there are two options:

Get the results for each query and format them into a string that contains everything you want.
Merge all the different results into a single query and then set your @query to that. (Alternatively, sticking all the results into a temporary table and querying the temp table.)

It is possible, I should note, to stick a query in the @query as well as sticking the results of a second query in the @body (per here and here), but that only allows you two statements.

I would try option one inside of a stored procedure.  The trick would be formatting the table into the string.  However, it's the most likely to work correctly.


Read-only user query in Oracle I have been asked to create a read only user in a Oracle database 12c. The read only user must have read only grants only to the objects of select schemas, which I created and handed over.

But now the request from the team is that the newly created read only user must have read only grants to even to all those new objects created by those select schemas.

How is this possible? Inputs please!

No, no, no, you should definitely not do this. It is the responsibility of the object creator to grant the necessary privileges. (Although this is a common request, and it would be nice to finally have built-in support for this.)

Since you are on 12c, forget SELECT, and grant READ. SELECT is not a read-only privilege. With SELECT granted, the grantee can run SELECT ... FOR UPDATE, lock rows, and interfere. READ does not allow that.

What you asked is however still possible with some dirty tricks, using a DDL trigger, for example:

How do I create a Oracle trigger that grants permissions


Temp tables not being created in SQL Server Azure I have created a stored procedure that creates multiple temp tables for use in the sproc and then drops them at the end of the sproc. The problem I am encountering is I can execute the stored procedure just fine in management studio, but when I attempt to create an SSIS data flow that retrieves the rows returned by the sproc, it give me the following error:

  Invalid object name '#tempRuns'

Is there something special I need to do in order for temp tables to work with SSIS in Azure?

To do this you will need to use global temp tables, i.e. ##TempTable.  Additionally, the TransactionOption property will need to be set to Required for the the parent container of all the objects involved.  This will keep all of the SSIS components in the same transaction.  Within the container, the tasks can use use the same Required level or Supported, which will inherit this property from the parent container.  All the components involved can either be placed within a Sequence container with TransactionOption set accordingly to isolate them, or this can be applied at the package level.


Multiple disparate functions within stored procedure Developers at my company have this habit of putting multiple semi-related functions within a stored procedure, calling the appropriate one based on a passed-in parameter, e.g.:

if @function = 1

begin
    truncate table holiday_list
end

if @function = 2

begin
    -- huge SQL statement populating the holiday_list table
end

Is this a bad practice? It seems to me that SQL Server would not create a good execution plan, as the code branches into two disparate paths (this is a simple example, though many times there will be 4 or 5 such branches, doing unrelated tasks except for sharing the same base table).

In summary, this is poor practice. While not "wrong" in an absolute sense it introduces avoidable risks.
SQL Server optimizes each statement separately. The execution plan for the "everything" stored procedure will not be noticeably different to the combined plans of having each bit in its own stored procedure (SP). There will be a very tiny overhead of evaluating the IFs, but you would never measure it, much less notice it.
It seems they're treating the SP as a plug-in library. SPs are not libraries. They should be treated as methods. Each will have its own clear, distinct purpose and implementation.
How would code review treat application code that had a class with a single mega-method? Would the devs accept this style in the UI or business tier? Likely not. Don't do it in the DB tier either.
There are some run-time implications. The mega-SP has a single plan. Changing any part of the code will cause the whole plan to be re-compiled, stalling all clients wishing to run any part of that plan, even if they're executing a function whose code has not changed. The time to compile a large SP is longer than the time to compile a small one. Plans have to be loaded in toto to be executed, even if only a small fraction of the code path is traversed. So fewer plans can be in memory at any one time, meaning plans get evicted from cache more often, causing re-comiles and client slowness. If the server has more memory than it needs and is never under memory pressure this may never manifest as an actual issue.
Project management becomes more complicated, too. With a single SP lots of changes will affect that one module risking code merge fails. (Your DB code is in source control, right?)
The code is less self-documenting. What does function "1" mean in this SP? Is it these same meaning in that SP? Should every SP have a function "1"?
The parameters must be a superset of all the functions. Now you need even more documentation to say "function 1 uses @a, @c and @f; function 2 uses @a and @g but don't populate @c or year-end crashes." Likely every parameter will be optional losing further built-in documentation and validation.


MySQL FULLTEXT search not returning any results I'm wondering why my fulltext search isn't returning any results.  To me this seems pretty straightforward, but alas it's not working.  I'm far from a DBA, but looking at the EXPLAINed query, I see key_len is zero, which seems odd.

I'm running MySQL 5.5.41

CREATE TABLE

CREATE TABLE `searchable_content` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `page_id` int(11) DEFAULT NULL,
  `menu_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `menu_title` (`menu_title`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table content

+----+---------+------------+-------+------------------------------------------------+
| id | page_id | menu_title | title | content                                        |
+----+---------+------------+-------+------------------------------------------------+
|  8 |      42 | Apps       | Apps  |
blah blah blah blah blabbity blah blah
  |
+----+---------+------------+-------+------------------------------------------------+

Query

SELECT
    page_id,
    MATCH(`content`) AGAINST ('blabbity') AS 'relevance'
FROM
    `searchable_content`
WHERE
    MATCH(`content`) AGAINST ('blabbity');

That query EXPLAINed

+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table              | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | searchable_content | fulltext | content       | content | 0       |      |    1 | Using where |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+

First, ignore the key_len of explain, that value only has a meaning in the context of a BTREE index, so it will always be 0 for a FULLTEXT scan, regardless of the actual size of the index/column.

Second, by default, the FULLTEXT index is used in "natural language mode", which, as it is documented, will ignore results that happen in 50% or more of the rows ("words that are present in 50% or more of the rows are considered common and do not match."). As you only have 1 row, you will always get 0 results. Note that if you add 2 more non-matching records, you will get the desired results:

mysql> insert into searchable_content () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into searchable_content () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
       page_id,
       MATCH(`content`) AGAINST ('blabbity') AS 'relevance'
       FROM
       `searchable_content`  WHERE
       MATCH(`content`) AGAINST ('blabbity');
+---------+--------------------+
| page_id | relevance          |
+---------+--------------------+
|      42 | 0.3573872447013855 |
+---------+--------------------+
1 row in set (0.00 sec)

If you may want to get all results, regardless of the percentage of matches, I recommend you using the boolean mode:

mysql> SELECT
       page_id,
       MATCH(`content`) AGAINST ('+blabbity' IN BOOLEAN MODE) AS 'relevance'
       FROM
       `searchable_content`
       WHERE MATCH(`content`) AGAINST ('+blabbity' IN BOOLEAN MODE);
+---------+-----------+
| page_id | relevance |
+---------+-----------+
|      42 |         1 |
+---------+-----------+
1 row in set (0.00 sec)

Although you lose the ranking. Be careful, as you will never match words shorter than ft_min_word_len (innodb_ft_min_token_size for InnoDB). Changing this variable may require recreate the index.

Also be aware that you could use a single index to search on the three columns at the same time.

Last but not least, fulltext search is available for InnoDB starting with MySQL 5.6, and both 5.6 and 5.7 bring some interesting improvements in terms of performance. If you need a more flexible, feature-complete or performant search solution, you have to go to an external tool like Sphinx or Lucene.


Recommended Auto Grow settings suitable for database whose size is 867666.25 MB I need recommendations for setting/modifying the auto grow options for our production database.

size of FUNDSDB  867666.25 MB

Also,
I have attached the current autogrowth statistics , looks like it is set to 200 MB which I think is considerably less considering the database size and also it should be modified to some realistic value else these auto-growth events can cause performance degradation for our current db transactions.

Can anyone suggest the auto growth settings best suitable for this heavy OLTP production database?
We are using SQL Server 2012 standard RTM version currently.
The main reason we are planning to change the autogrowth factor is that we have observed, we are experiencing frequent up to 11 times auto grow a day and also, many frequent CPU spikes.

Set it to 1024MB.  If it is still happening too often, then 4096MB.  Until you measure your actual growth needs, it guesswork.

Or, manually grow it to 950GB, or an even 1TB and let Autogrow not be a factor, which is what you should be doing anyway.

Is Instant File Initialization turned on?  You didn't mention the version of SQL Server.

And change the Log file from % to MB.

And just for bonus - please get them onto separate drives.

Kevin3NF


how to convert a date string like this MM/YYYY to 01-MM-YY I have a column and its type is string and stores date something like this 03/1996
I want to convert that to 01-MAR-96?

how can I achieve that in ORACLE

SQL> select to_char(to_date('03/1996', 'MM/YYYY'), 'DD-MON-YY') from dual;

TO_CHAR(T
---------
01-MAR-96

Based on that (if by convert you meant changing the data in the table):

update table tableX set columnX = to_char(to_date(columnX, 'MM/YYYY'), 'DD-MON-YY');


SQL Server Management Studio highlights a word that does not appear to be a reserved word I don't like to name objects with reserved word (i.e. Set) for all the obvious reasons.

So when SSMS highlights a word in blue, I rethink my naming of the object.

Every once and a while I see a word highlighted that does not appear to be in the list of reserved words. In this case the word pool.

Does SSMS use a more strict convention for highlighting reserved words?

SSMS uses color syntax highlighting for keywords that are not necessarily reserved keywords, but are keywords in the context of certain statements.  For example POOL is a keyword as in CREATE RESOURCE POOL.  SSMS will show the word in a different color even when not in the context of a CREATE or ALTER resource pool.


How does mysql(innoDB) handle same foreign key for join I have a table called X and another called Y. There is a primary key on Y that is a foreign key on X. Now, my data is such that there will be many rows in X that will have the exact same foreign key value.

I want to know if MySQL will look up the Y table every time in select using a join even when the value is the same, or does it not have to search the new table a second time when I do the select?

According to MySQL 5.7 documentation (current as of now):

  8.8.2 EXPLAIN Output Format

  The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

  EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

Emphasis mine. That is, I guess that MySQL isn't intelligent enough to remember that "it already looked some key".

MariaDB uses several Block-Based Join Algorithms in current versions:

  Block Nested Loop (BNL) join
  Block Nested Loop Hash (BNLH) join
  Block Index join known as Batch Key Access (BKA) join
  Block Index Hash join known as Batch Key Access Hash (BKAH) join

The "Batch Key Access" methods seems to take some advantage ... the key part of the docs is:

  Yet BKA join allows to optimize the order in which the records are fetched.


How to connect to a named SQL Server instance without specifying its name? Is there anyway I can connect to a SQL server that has no default instance, using just the server name? I've tried using SQL CMD, but it seems that the named instance name is required to login, regardless? Does anyone know how this could be acheived and what language?

If there is no default instance, then you could force the named instance to listen on port 1433.  SQL Server Configuration Manager, then navigate to the networking bit and turn off dynamic ports.

This article goes into details on how you can do that for multiple instances running on the same server, but it gets complicated fast.  How to Add a Hostname alias for a SQL Server Instance.

So there are the methods, but why do you want to do it that way?


Agent shows disabled(although it is not) after migration I am migrating our old server to a more reliable and powerful new server. SQL Server edition and build number stays the same (SQL Server 2008 R2 Standard). For this I followed the steps given in this article. This is what I have done so far in summary:

Backed up the master db of the old machine,
Noted down the system db's data and log files' paths on the old server and created them on the new one,
Stopped the SQL services on the old machine and copied the data and log files of msdb and model db's of the old machine over to the new one and placed them exactly on their original paths (drive letters are same as well),
Started SQL Server on the new machine in single user mode and restored the backup of old machine's master db.
Backed up the service master key file on the old machine and restored on the new one in order for the linked servers to work,
Changed the server name of the new machine in system tables to the old one.

Everything looks like fine except a glitch. SQL Server Agent in the SSMS shows as "Agent XPs disabled". However, I can confirm that it is not. Agent service can successfully be started and run. It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.

What might be causing this and does it pose a risk?
Thank you

It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.

This is expected behaviour. When you STOP sql server agent, the congiguration option Agent XPs changes the config_value and run_value to 0. When you start sql agent, then the config_value and run_value for Agent XPs changes back to 1.

It is important to run reconfigure with override when you enable the configuration option for the first time using sp_configure 'Agent XPs', 1


sys.dm_tran_version_store_space_usage reports always zero space used I was learning about RCSI when I discovered something strange on Azure SQL Database.
The DMV sys.dm_tran_version_store_space_usage always reports 0 as the space used by the version store, even if I run a CRUD workload just before.
To demonstrate this behavior, I created a little test.
-- Server info
select @@version as sql_version;

-- Database info
select
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
from sys.databases
where database_id = db_id();

-- Just to be sure the current database has its version store empty
select reserved_page_count as pre_workload_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

-- Test workload
drop table if exists RCSI_TEST;

create table RCSI_TEST (
    id uniqueidentifier default newid()
);
go

insert into RCSI_TEST default values;
go 100

update RCSI_TEST
set id = newid();

delete from RCSI_TEST;

-- Metrics
select reserved_page_count as post_workload_page_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

waitfor delay '00:01:30'; -- Just to be sure! ;)

select reserved_page_count as post_cleaning_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

I ran this script both on SQL Server 2019 Developer Edition (on Docker) and on an Azure SQL Database (tier S0, 10DTU) and here are the results.
SQL Server 2019
sql_version
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
8

post_cleaning_space_count
0

Azure SQL Database
sql_version
Microsoft SQL Azure (RTM) - 12.0.2000.8     Oct  1 2020 18:48:35    Copyright (C) 2019 Microsoft Corporation

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
0

post_cleaning_space_count
0

SQL Server 2019 appears to behave correctly, reporting 8 pages worth of data in the version store right after the workload and then be cleaned up after a minute or so.
On Azure SQL Database, though, the space used is always zero! Is that a correct behavior? What does it means?
Books Online reports this DMV to be compatible both with SQL Server and Azure SQL Database but also says "The following query can be used to determine space consumed in tempdb, by version store of each database in a SQL Server instance.". As far as I know Azure SQL Databases do have a limited scope to their parent instance by design. Can that be the root cause?
https://docs.microsoft.com/it-it/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15
I know that Azure SQL Databases run under RCSI by default and also that they have a fixed amount of tempdb space based on the tier you get, so I was concerned about filling that up with long running transaction or other processes keeping lots of records in the version store for too long. But I cannot manage something I can't measure, right?

Azure SQL Database uses Accelerated Database Recovery, and so RCSI doesn't use TempDb for the version store.  Instead the version store is inside the database to enable "Instantaneous transaction rollback", which is especially important during a failover:

The persisted version store is a database engine mechanism for persisting the row versions generated in the database itself instead of the traditional tempdb version store. PVS enables resource isolation and improves availability of readable secondaries.

Accelerated Database Recovery
So you should look in sys.dm_tran_persistent_version_store_stats, or see generally Manage accelerated database recovery


SSD used for tablespace died. How can I recover data? I have a postgres cluster with two databases

Database A uses the default tablespace. It holds important information but has very few writes per day (< 20) and only has a few tables with a few thousand rows of data.
Database B is on its own tablespace on a separate SSD. It has hundreds of GBs of data and adds millions of rows per day.  The data is for analytics and is not important.

Recently the SSD holding Database B's tablespace died.  Postgres will no longer start up.  My priority is dumping the data from Database A.

I was thinking that because Database A has few writes or deletes per day, it would be fairly safe to run pg_resetwal, then dump Database A.  After dumping Database A I would re-install postgres and load the dumped data from Database A.

Is there an alternative method to recover my data.  Are there any obvious problems with my plan?

(I know that pg_barman should be used to prevent these problems, but my client has refused when I suggested pg_barman after a similar failure in the past. And, yes, RAID would obviously be better than a single SSD, but I don't get to make the hardware decisions)

Error log when attempting to start postgres:

2020-03-13 16:20:16.678 PDT [55834] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-03-13 16:20:16.686 PDT [55834] LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:16.688 PDT [57190] LOG:  database system was interrupted while in recovery at 2020-03-13 16:19:29 PDT
2020-03-13 16:20:16.688 PDT [57190] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2020-03-13 16:20:17.016 PDT [57190] LOG:  could not stat file "pg_tblspc/16394": No such file or directory
2020-03-13 16:20:17.134 PDT [57190] LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.135 PDT [57190] LOG:  database system was not properly shut down; automatic recovery in progress
2020-03-13 16:20:17.135 PDT [57190] LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.136 PDT [57190] LOG:  redo starts at 7C1/93EB0EA0
2020-03-13 16:20:17.136 PDT [57190] FATAL:  could not create directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.136 PDT [57190] CONTEXT:  WAL redo at 7C1/93EB0EA0 for Sequence/LOG: rel 16394/26819/26877
2020-03-13 16:20:17.136 PDT [55834] LOG:  startup process (PID 57190) exited with exit code 1
2020-03-13 16:20:17.136 PDT [55834] LOG:  aborting startup due to startup process failure
2020-03-13 16:20:17.139 PDT [55834] LOG:  database system is shut down

Posgresql version is 11.2

In my hands, just creating an empty tablespace directory allows recovery to proceed and the database to open.  Either change your mounts so that the existing path of the tablespace now maps to a valid directory again, or delete and re-create the symlink  "PGDATA/pg_tblspc/16394" so that it points to a valid empty directory on some other mountpoint.

I think that this should much safer than monkeying around with pg_resetwal.


count distinct column without null value in MySQL 5.7 how to distinct not contain null in MySQL, if I have a dataset like this:
a b
1 null
2 1

I want to using sql like this:
select count(distinct b)
fromt table

the expect result may output 2, but I want the count distinct result not contain null. I could not using filter in the where clause because I should using other column to sum as result, if I filtered result first, the result is not correct. My real sql like this:
select

        sum(transin_num) as transinNum,
        case when count(distinct transin_user_num) -1 < 0 then 0 else count(distinct transin_user_num) -1 end as
        transinUserNum,
        sum(transin_count) as transinCount
        from (

        select
        case when consume_item in('PLATFORM_IN') then ABS(consume_num) else 0 end as transin_num,
        case when consume_item in('PLATFORM_IN') then user_id else -1 end as transin_user_num,
        case when consume_item in('PLATFORM_IN') then 1 else 0 end as transin_count

        from wallet_consume_record
        where created_time < 1595951999999
        and created_time > 1595865600000
        and tenant_id = 84
) a

I want to calculate transin_user_num without the -1 value.Is it possible to do like this in MySQL 5.7?

MySQL works like you expected and it should return 1.
create table t (a int, b int);
insert into t (a, b) values (1, null), (2, 1);
select * from t;
select count(distinct b) from t;

dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4fd6a3744a042c376880f030f4d12101


MySQL General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it' I have a table in MySQL with ~2,000,000 rows.

When I try to select all records, it exhausts the system's memory and I get an error about memory exhaustion back.

When I try to use the MySQL LIMIT and select 1,000 records, the error in the title is returned:

General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it'

What's the best course of action to export all of the data in this table?  Why is it returning an error when only trying to pick 1,000 records?

My query:

select * from notes order by id asc limit 1000

You can remove any /tmp/#sql* files -- they are tmp tables left over after a crash.  Or they are part of something (ALTER, CREATE, etc) that is going on, but stuck waiting for disk space.

It is often a mistake to have a separate filesystem for tmp -- you run into problems like this:  Plenty of space to finish a task, but not in /tmp.  Consider changing mysql's tmpdir setting.


Sum(Case When) Question I'm trying to get the syntax correct on my query but I'm having issues.

Currently my query will show the Household Total Value (HHValue) and then the NationalValue on one row, then the second row shows the HHValue again and the DirectValue. It's splitting the results between two rows and I want it all on one row.

However, when I try to change my CASE statement, I am getting errors. I think I'm just confusing myself but I need one row for each household with HHValue, NationalValue and DirectValue.

This is what I have in my query so far:

SELECT Household.Name
     , FinancialPlanner.LastName
     , PlanFirmSpecCode.SpecialCode
     , SUM(Asset.Value) AS HHValue
     , SUM(CASE WHEN financialinst.name = 'National Financial Services'
                THEN Value ELSE 0
           END) AS NationalValue
     , SUM(CASE WHEN financialinst.name <> 'National Financial Services'
                THEN Value ELSE 0
           END) AS DirectValue
FROM PlanFirmSpecCode
JOIN FinPlanSpecCode
    ON PlanFirmSpecCode.PlanFirmSpecCodeID
     = inPlanSpecCode.PlanFirmSpecCodeID
RIGHT JOIN Household
      JOIN Account
          ON Household.HouseholdID = Account.HouseholdID
      JOIN Asset
          ON Account.AccountID = Asset.AccountID
      JOIN FinancialPlanner
          ON Household.FinancialPlannerID
           = FinancialPlanner.FinancialPlannerID
      LEFT JOIN FinancialInst
          ON Account.FinancialInstID = FinancialInst.FinancialInstID
    ON FinPlanSpecCode.FinancialPlannerID
     = FinancialPlanner.FinancialPlannerID
WHERE Account.Closed <> 'Y'
  AND Asset.Value > '0'
  and Account.Managed <> 'N'
GROUP BY Household.Name
       , FinancialPlanner.LastName
       , PlanFirmSpecCode.SpecialCode
       , CASE WHEN FinancialInst.Name = 'National Financial Services'
              THEN 'NFS' ELSE 'Direct Business'
         END
Order by household.Name

I think you do not need the last column in your GROUP BY clause:

SELECT     Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode,
           SUM(Asset.Value) AS HHValue,
           SUM(CASE WHEN financialinst.name = 'National Financial Services' THEN Asset.Value ELSE 0 END) AS NationalValue,
           SUM(CASE WHEN financialinst.name <> 'National Financial Services' THEN Asset.Value ELSE 0 END) AS DirectValue
FROM       PlanFirmSpecCode INNER JOIN
           FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
           Household INNER JOIN
           Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
           Asset ON Account.AccountID = Asset.AccountID INNER JOIN
           FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER JOIN
           FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE      (Account.Closed <> 'Y') AND (Asset.Value > '0') and (Account.Managed <> 'N')
GROUP BY   Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode
Order by   household.Name


meaning of ## in SQL Server Profiler trace data I'm running a trace using the SQL Server Profiler.  The trace data contains the following lines:

WHERE CAST(LEFT(pc.Time_stamp,11) AS DATETIME >= ##
AND CAST(LEFT(pc.Time_stamp, 11) AS DATETIME) <= ##

What does the ## mean

First, if you're using SQL Server 2008 or higher, the Profiler is deprecated - look into using Extended Events instead.

The ## marks serve as a placeholder for the value of the variables being used in the query.


Lock wait timeout exceeded on insert despite low lock_time in the slow query log Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs.

# Query_time: 51.472333  Lock_time: 0.000090 Rows_sent: 0  Rows_examined: 0
SET timestamp=1340049365;
insert into Foo ( ...

# Query_time: 51.045967  Lock_time: 0.000131 Rows_sent: 0  Rows_examined: 0
SET timestamp=1340049476;
insert into Foo ( ...

# Query_time: 50.693427  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 0
SET timestamp=1340049561;
insert into Foo ( ...

Clearly the queries are hanging for a long time and seem to be hitting the exception right at around 50s, but the short lock time does not make sense to me. What can the queries be waiting for if not some lock? Would someone please shed some light onto this?

The query times are wall time from start to finish.  The lock times are times spent actually locking affected tables.  If you run

select sleep(60);

You'll see a slow query entry with a query time of 60 seconds and no lock time.

50 seconds is a standard lock wait time out setting. Run

show variables like 'innodb_lock_wait_timeout';

I'll bet it's 50.  The queries in your OP are likely showing a query time of this b/c of the time out setting.  They're probably sitting there not really doing anything waiting for some other query to finish until they're effectively killed.  If they're hitting that limit it should be easy to just run show processlist; to see what's going on.  You'll likely see many threads in a Locked state (i.e. doing nothing but waiting) where there's one thread "doing something".

Are your truncated "insert" statement ultimately doing some kind of expensive subselect from other tables?

Is your application explicitly locking tables and not letting go?

Edit: Queries hitting a lock wait timeout does not mean they are holding on to a lock for that limit but rather they got tired of waiting for someone else to give it up and let them have a go.

show engine innodb status\G

Can also give you insight to what is locked at the moment and who is holding them.  Although the output can be a bit cryptic at first glance it can give some pointers.


List parent then nested child items in a single table With the following SCHEMA

CREATE TABLE Software (
    ID INT,
    name VARCHAR(150),
    ParentID INT );

INSERT INTO Software VALUES (1,'Main Product',NULL);
INSERT INTO Software VALUES (2,'Next Main Product',NULL);
INSERT INTO Software VALUES (3,'Addon Product',1);
INSERT INTO Software VALUES (4,'Addon Product',1);
INSERT INTO Software VALUES (5,'Sub-Addon Product',3);

I need to create a query that will sort the data by picking the first Main Product, then finding any addon products where the parent ID matches the ID of the parent, then seeing if that addon-product has any sub-addon products.

The Query I've tried based on one of the recommendations is

WITH x AS (
    SELECT ID
    FROM Software
    WHERE ParentID IS NULL)
SELECT
    Software.ID,
    Software.name
FROM Software
INNER JOIN x ON x.ID=Software.ParentID OR x.ID = Software.ID

The output however misses the sub-addon product shown below:

/---------------------
| ID  | Name
| 1   | Main Product
| 3   |   Addon Product
| 4   |   Addon Product
| 2   | Main Product
\----------------------

What I need is this where the sub-addon is added

/---------------------
| ID  | Name
| 1   | Main Product
| 3   |   Addon Product
| 5   |     Sub-Addon Product
| 4   |   Addon Product
| 2   | Next Main Product
\----------------------

As far as you want to obtain all records where parent_id is null, I'd suggest to use an inline udf function.

-- Inline UDF function
CREATE FUNCTION fnTree(@ID int)
RETURNS table AS
RETURN
(
    WITH tree AS
    (
      SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast(c1.id as varchar(100))
      FROM dbo.[software] c1
      WHERE c1.id = @ID
      UNION ALL
      SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1, Path=Cast(tree.path+'/'+cast(c2.id as varchar(10)) as varchar(100))
      FROM dbo.[software] c2 INNER JOIN tree ON tree.id = c2.parent_id
    )
    SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name as name
    FROM tree
);
GO

Then you can CROSS APPLY this function with all records where parent_id is null
Take care when using a recursive function, you should set OPTION (MAXRECURSION XXX) just to ensure it can hold all returned records.

SELECT      f.id, f.name, f.parent_id, f.path
FROM        software
CROSS APPLY fnTree(software.id) f
WHERE       software.parent_id IS NULL
ORDER BY    f.path
OPTION (MAXRECURSION 0);
GO

id | name                  | parent_id | path
-: | :-------------------- | --------: | :----
 1 | Main Product          |      null | 1
 3 |   Addon Product       |         1 | 1/3
 5 |     Sub-Addon Product |         3 | 1/3/5
 4 |   Addon Product       |         1 | 1/4
 2 | Next Main Product     |      null | 2

dbfiddle here


SQL Server 2012 performance degradation We are running SQL Server 2012 SP1 on Windows Server 2012. The issue we have is that after some time the server automatically reboots.

Checking the logs did not show any errors. When looking into the SQL Server log we find these logsDate

  24-5-2014 13:42:47
  SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.

  24-5-2014 10:01:18
  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14401 seconds. Working set (KB): 23684, committed (KB): 99988, memory utilization: 23%.

  24-5-2014 08:01:16
  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7200 seconds. Working set (KB): 29868, committed (KB): 99972, memory utilization: 29%.

  24-5-2014 07:01:48
  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3632 seconds. Working set (KB): 34376, committed (KB): 100004, memory utilization: 34%.

Searching the internet did not help very much. Most written about this is about server 2003.

The problem occurs frequently depending on the usage.

What steps do I need to take to fix this?

The first message regarding the shutdown means that someone or something has initiated a shutdown of the machine. Take a look at the Windows system logs in Event Viewer for more information. You should look at log events leading up to and just after the shutdown time to look for messages indicated why Windows shutdown.

Is this a relatively newly installed server? If so, have you activated Windows? If not and you've passed the activation grace period this is one potential reason for Windows Server automatically rebooting.


Sqlserver 2019 Partitioning based on Data Classification? Now that sqlserver 2019 has been around for a while, this is a re-ask of a prior question that was not really answered in the way I had hoped:

Is it possible to partition a database based on the Sqlserver 2019 data classification feature?

Explanation/Detail:
I would like to column partition the entire database schema so that our "high sensitivity" data is processed and stored separately, perhaps in another instance in a different location.  This to improve security of our data, understanding that there would be a performance cost.

I would like to maintain the current schema for application compatibility purposes, just store the data differently for security reasons.

No. And it isn't just that this feature is not yet implemented, it is literally not possible to combine these two features in the way you're thinking.

Table partitions are row (record) based. You define a partitioning rule that determines which partition each record is stored in, based on a value stored in that record (for whichever column you've defined the rule).

Data classification labels are column-based. They are simply meta-data (like extended properties) attached to columns in a table that categorize the sensitivity (or lack thereof) of the data stored in the entire column. These categorizations are used by some pre-defined reports, but otherwise don't impact the behavior of the database.

Some further thoughts:

SQL partitioning is used to break a table into separate chunks, but to still appear as the same logical table. You can put those chunks in different filegroups (and put the different filegroups on different physical disks), but they all still have to reside in the same SQL instance and database.
If you are using the term "partition" less formally, sure, you could manually split a table into two tables based on sensitivity (say EmployeePublic and EmployeePrivate) with a 1-to-1 relationship, with different permissions or even encryption on each. You'd have to change your application queries, though, or create an Employees view that combines them back together in the way your application expects. This would be an entirely manual process, though, and has nothing to do with either the 2019 sensitivity flag or SQL table partitioning.


How to sort categorical values by an arbitrary order? I have a table called ANIMALS which looks like this

| name      | category |
------------------------
| fluffer   | CAT      |
| tooter    | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer    | DOG      |

If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG

How can I achieve this result?

This can be done using a CASE statement in the ORDER BY clause:

WITH
Animals (name, category) AS (
 SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
 SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
  name, category
FROM Animals
ORDER BY
  CASE category
  WHEN 'CAT' THEN 1
  WHEN 'ELEPHANT' THEN 2
  WHEN 'DOG' THEN 3
  END
;

Please comment, if and as this requires adjustment / further detail.


Establishing a 1 to 1 relationship between tables I have a table Nurse:

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [WardId]     [int]               NOT NULL,
 CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and a table Employee:

CREATE TABLE [dbo].[Employee](
    [Id]            [int] IDENTITY(1,1) NOT NULL,
    [Name]          [nvarchar](100)     NOT NULL,
    [StaffNumber]   [nvarchar](50)      NOT NULL,
    [Salary]        [money]             NOT NULL,
    [Address]       [nvarchar](max)     NOT NULL,
    [GenderId]      [int]               NOT NULL,
    [ContactNumber] [nvarchar](20)      NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How do I establish a 1:1 relationship between the two tables?

There is one nurse for one employee, only the Nurse table is separated from the Employee table as it has to be part of relationships, such as, just any employee, e.g. a Nurse belongs to a Ward, while a Doctor, also an employee, belongs to a Department.

I guess that you mean: "every Nurse is an Employee".

This can be done with (keeping the Employee table as it is):

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,        -- why not name this EmployeeId ?
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
    ( [Id] )
 ) ;

and changing the Nurse table so the primary key is not an IDENTITY and it has a foreign key that references the Employee (Id):

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] NOT NULL,                 -- not IDENTITY !
    [WardId] [int] NOT NULL,
  CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
    ( EmployeeId ),
  CONSTRAINT [FK_Employee_Nurse] FOREIGN KEY
    ( EmployeeId )
    REFERENCES dbo.Employee ( Id )
) ;


Parameter for Sybase Sql Anywhere 12 Serverport If SQL Server Anywhere is started via "dbsrv12.exe -z -x tcpip " it opens a Serverport.

Default is 2638.

If more then one database is opened, the other ones are getting "some" serverports.

How could I say: open the server on "this" port

In help of SQL anywhere I find:
TCP/IP: Broadcast, BroadcastListener, DoBroadcast, Host, LDAP, LocalOnly, MyIP, ReceiveBufferSize, SendBufferSize, ServerPort, TDS, Timeout

Using dbsrv12.exe -z -x tcpip ServerPort 54382 gives 2 errors, that ServerPort and 54382 are not a file.

http://dcx.sybase.com/1200/en/dbadmin/serverport-network-conparm.html states that:

  You can specify a single port number, or a combination of individual
  port numbers and     ranges of port numbers. When you specify a list
  and/or range of port numbers, the database server attempts to bind to
  all specified port numbers.  For example: (port=1234)

  Default  TCP/IP   2638

Therfore i tried dbsrv12.exe -z -x tcpip (port=54382) and dbsrv12.exe -z -x tcpip 54382, both giving the same error as above.

In which way do I misunderstand the help?

The proper syntax is:

dbsrv12.exe -z -x tcpip(ServerPort=54382)

No space between tcpip and the (. You can use "Port=" instead of "ServerPort=" if you want.


How to cache select with variables? (MySQL) Context:

I have complicated very often executed query:

CREATE OR REPLACE VIEW ordered_words AS SELECT word.id
  FROM category JOIN frequency ON category.id = frequency.category_id
    JOIN word ON frequency.word_id = word.id
    JOIN language ON word.language_id = language.id
  WHERE
    category.name='Subtitles' AND iso_639_1='en'
  ORDER BY frequency.value DESC;

I enabled cache query_cache_type=1 and I obtain great performance improvement.

First time (4.5 s)

sql> SELECT * FROM ordered_words
500 rows retrieved starting from 1 in 4s 585ms (execution: 4s 576ms, fetching: 9ms)

Second time (5 ms)

sql> SELECT * FROM ordered_words
500 rows retrieved starting from 1 in 11ms (execution: 5ms, fetching: 6ms)

It is ok.

Problem

But now I want to add to this result one column with identifiers 1,2,3... because I would like to be able to get selected row from result of this query. I created the following query:

SELECT @ROW := @ROW + 1 AS ROW
 FROM ordered_words t
 join (SELECT @ROW := 0) t2;

But this query is not cached. Any time execution gets about 4.8 sec. I want to save to cache result of this query.

Alternative solutions:

Because of my experience in databases is small. I do not know if my approach is optimal so I describe wider context. In my application inserts are very rarely used.

My real target is execute very often query that get one random element from ordered_words with dynamically assigned distribution of probability. I do not want to create new table for this, because category and language are free parameters. I will use function rather. But I do not want execute all query any time.

Logic of randomization will be executed out of MySQL. MySQL will obtain only number of word in temporal order saved in result of ordered_words. I am planning treat column row as temporary identifier that allow select word_id without calculating all joins and ordering from first mentioned query.

Maybe is there something like temporary table created in memory, or should I use other database system?

Update

As @Rick-James suggest. Timing was wrong. Cache was created by my IDE - DataGrip not by MySQL. Finally I decided to save result of my complicated query to table in memory by command:

  CREATE TABLE IF NOT EXISTS words_memory_subtitles_en (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, word_id INTEGER)
  ENGINE=MEMORY
  AS
  SELECT word.id as word_id
    FROM category JOIN frequency ON category.id = frequency.category_id
      JOIN word ON frequency.word_id = word.id
      JOIN language ON word.language_id = language.id
    WHERE
      category.name='Subtitles' AND iso_639_1='en'
    ORDER BY frequency.value DESC;

I will mange creating and removing tables like this in PHP.

I decided to remove id from frequency table.

For efficient fetch of a random row, here is a blog on such.

Do you really select all columns of all rows, as indicated in the timings you mention?

Keep in mind that all entires for a given table in the "Query cache" are deleted whenever any write operation happens on that table.  (This makes the QC rarely of any use in busy production servers.)

A common mistake in many-to-many table is to have an AUTO_INCREMENT id.  Check out that and other many:many tips here.

VIEWs are a convenience, not a way to get efficiency.  Pretend you don't have the VIEW and write the real query you need.  If it is 'fast enough', then I have given you the answer.  If not, let's see it and look for ways to optimize it.  Please provide SHOW CREATE TABLE for each relevant table.


Postgres - Selectively either null or delete foreign key rows (without cascade) I understand this can easily be achived with cascade behaviour, but cascade isn't desirable in this case.

Is there a way of automatically either nulling the column or deleting the row referencing a foreign key depending on whether or not the column is nullable in the referencing table?

Say we have the following tables:

              Table "public.a"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 pk                         | integer | not null

              Table "public.b"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 fk                         | integer | not null
"b_a_fk_fkey" FOREIGN KEY (fk) REFERENCES a(pk)

              Table "public.c"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 fk                         | integer |
"c_a_fk_fkey" FOREIGN KEY (fk) REFERENCES a(pk)

We can get tables referencing a table with queries such as this. Does anyone have an example of how to generically delete rows in table 'b' and null columns in table 'c' referencing a given value for a.pk?

EDIT: Recursive version. Lots of debug but peace of mind is nice when you're running queries like this. I'm sure this is by no means perfect, it assumes the PKs are ints for one, but it does the job for me:

create or replace function delete_cascade(table_name text, column_name text, column_value int) returns void
language plpgsql
as
$func$
  declare
    sql_temp text := '';

    rec record;
    recur record;
    recur2 record;

  begin
    raise notice 'enter';
    sql_temp := format('' ||
                       'with fkey as ' ||
                       '( ' ||
                       '  select pg_class.oid, pg_attribute.attnum ' ||
                       '  from pg_attribute ' ||
                       '  inner join pg_class on (pg_class.oid = pg_attribute.attrelid) ' ||
                       '  where pg_class.relname = ''%s'' and pg_attribute.attname = ''%s'' ' ||
                       ') ' ||
                       'select pg_class.relname, pg_attribute.attname, pg_attribute.attnotnull ' ||
                       'from pg_constraint ' ||
                       'inner join pg_attribute on (pg_attribute.attrelid = pg_constraint.conrelid) ' ||
                       'inner join pg_class on (pg_class.oid = pg_attribute.attrelid) ' ||
                       'inner join fkey on (fkey.oid = pg_constraint.confrelid and (array_position(pg_constraint.confkey, fkey.attnum) > 0)) ' ||
                       'where array_position(pg_constraint.conkey, pg_attribute.attnum) > 0',
                        table_name, column_name);
    for rec in execute sql_temp
    loop
      sql_temp := format('select pg_attribute.attname from pg_index inner join pg_attribute on ((pg_attribute.attrelid = pg_index.indrelid) and (pg_attribute.attnum = any(pg_index.indkey))) where (pg_index.indrelid = ''%s''::regclass) and pg_index.indisprimary', rec.relname);
      for recur in execute sql_temp
      loop
        sql_temp := format('select %s as col_val from %s where %s = $1', recur.attname, rec.relname, rec.attname);
        raise notice '%', sql_temp;
        for recur2 in execute sql_temp using column_value
        loop
          sql_temp := format('select delete_cascade(''%s'', ''%s'', %s)', rec.relname, recur.attname, recur2.col_val);
          raise notice '%', sql_temp;
          execute sql_temp;
        end loop;
      end loop;

      if rec.attnotnull = true then
        sql_temp := format('delete from %s where %s = %s', rec.relname, rec.attname, column_value);
        raise notice '%', sql_temp;
        execute sql_temp;
      else
        sql_temp := format('update %s set %s = null where %s = %s', rec.relname, rec.attname, rec.attname, column_value);
        raise notice '%', sql_temp;
        execute sql_temp;
      end if;
    end loop;

    sql_temp := format('delete from %s where %s = %s', table_name, column_name, column_value);
    raise notice '%', sql_temp;
    execute sql_temp;
    raise notice 'leave';
  end
$func$;

Original, more readable but "depth = 1" answer:

In the unlikely event anyone wants to follow in my footsteps here, this is what I ended up with. It only "cascades" one level, but as it happens that's good enough for me for the task in hand. I'll leave recursive cascades to the interested reader ;-)

create or replace function delete_cascade(table_name text, column_name text, column_value int) returns void
language plpgsql
as
$func$
  declare
    cur cursor for
      with fkey as
      (
        select pg_class.oid, pg_attribute.attnum
        from pg_attribute
        inner join pg_class on (pg_class.oid = pg_attribute.attrelid)
        where pg_class.relname = table_name and pg_attribute.attname = column_name
      )
      select pg_class.relname, pg_attribute.attname, pg_attribute.attnotnull
      from pg_constraint
      inner join pg_attribute on (pg_attribute.attrelid = pg_constraint.conrelid)
      inner join pg_class on (pg_class.oid = pg_attribute.attrelid)
      inner join fkey on (fkey.oid = pg_constraint.confrelid and (array_position(pg_constraint.confkey, fkey.attnum) > 0))
      where array_position(pg_constraint.conkey, pg_attribute.attnum) > 0;

    rec record;

  begin
    for rec in cur loop
      if rec.attnotnull = true then
        execute format('delete from %s where %s = %s', rec.relname, rec.attname, column_value);
      else
        execute format('update %s set %s = null where %s = %s', rec.relname, rec.attname, rec.attname, column_value);
      end if;
    end loop;

    execute format('delete from %s where %s = %s', table_name, column_name, column_value);
  end
$func$;


MongoDB - Stand Alone Server Security SSL or SSH We've outgrown our current server and decided to move to two servers, one to host our apps, and one to hold our database. We looked into clustering and we don't feel we need to scale horizontally just yet.

What's the best way to connect to and secure communication between the database and app server. Mongo suggests SSL in their docs but Digital Ocean now allows private networking between servers so I'm thinking maybe setup an SSH connection between the two servers? I'm not sure how reliable that would be. Also, I don't want to pay for the data transfer if I don't have to, so I'd like to keep it on private networking. What's the best way to get this done.

x.509 is, of course, best choice. You can get keys (for free) from let's encrypt organization if you "own" domain address.

Then there is keyfile what you can use.

Other choices are SSL tunnel or OpenVPN, latter preferred.


SQL Server: deadlocked on lock communication buffer resources What could be possible reason for this deadlock type? (not deadlock in general)

  Lock communication buffer resources

Is this indicated system is low in memory and buffers count ran out of limit?

Detailed Error:

  Transaction (Process ID 59) was deadlocked on lock communication
  buffer resources with another process and has been chosen as the
  deadlock victim. Rerun the transaction

The full message that is commonly seen:

  Transaction (Process ID 53) was deadlocked on lock | communication
  buffer resources with another process and has been chosen as the
  deadlock victim. Rerun the transaction.

This lock type is commonly seen with deadlock queries that SQL Server has executed as parallel, sometimes referred to as "intra-query parallel deadlocks". I have seen a few statements that this also points out system resources are low, which I guess could be involved to a small degree.

A general guideline that I have noticed to determine if it is parallel deadlock is when you pull the XML deadlock graph (which can be done with the system_health session in 2008 and higher) you will notice different process IDs showing the same bit of code within the execution stack.

As well, looking at the resource list of the deadlock graph and noting the type of waiter event. They will most commonly show "e_xxxxxx", or something like this maybe:

<waiter-list>
 <waiter event="e_waitPipeGetRow" type="consumer" id="process821d828" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process8209198" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process3827c18" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process3809eb8" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process8226b08" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process9acb6d8" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process6188d7828" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process381cef8" />
</waiter-list>

To try and resolve the issue various paths to take are offered online and in books. I generally start by looking at the execution plan of the query/procedure and focus on the areas that are showing parallel execution. Then from there go through trying to tune the query first and then as last resort may start using query hints.

The most common query hint you will see mentioned to resolve these deadlocks is implementing MAXDOP 1. However, before doing that you might check to see what the server level MAXDOP and Cost Threshold are set to. Cost Threshold is generally set to 5 by default and I like to raise that to 35 or 40 to start out with, if the query in question has a low cost for that section of code it may not need to run in parallel at all. I'm not all that fond of using MAXDOP query hints but that does not mean they do not have their place and purpose. just my opinion.


Pass information to another table with the same inherit Suppose the following scenario ... I have two tables (A, B) that inherit from a Z table, the SGBD is postgresql, these tables do not have an additional column, my question is how to pass a row from table A to table B that have the same inheritance.

The Table Z has a state column, with that column i decide which is the destiny of the data that has been inserted, this with a trigge, when execute a insert query the triger is activate and with condition is redirecte to child tables.

Now, if the state change, how can i pass to table A to table B.??

supose state is a varchar data type and the value is 'A' go to table A and if the value is 'B' go to table B, the constranint in the tables is something like that...
table A constraint.. CONSTRAINT only_A CHECK (state = 'A')
table B constraint.. CONSTRAINT only_A CHECK (state = 'B')

____________
|___Z_TABLE_|
| pk        |
| col1      |
| col2      |
| col3      |
| state     |
|___________|

_______________    ________________
|___A_TABLE____|   |____B_TABLE____|
| INHERITS (Z) |   | INHERITS (Z)  |
|______________|   |_______________|

i need to delete and reinsert in the other table or only with chage the value of state column ???

something more hard is... the table Z has a many relation with another tables...

You can create trigger after update like this

CREATE TRIGGER trigger_name
    AFTER UPDATE ON Z
    FOR EACH ROW
    ... delete/update/insert to table A;
    ... delete/update/insert to table B;

I hope this helps

Reference
https://www.postgresql.org/docs/10/sql-createtrigger.html


Managing Security for a Log Shipped db across two (unconnected) domains SQL Server 2008 (soon to be 2012).

We have a vendor that is providing us with log shipped backups of our production database.  We use this log shipped database for BI staging, reporting, etc. I've seen several blogs and articles discussing pass through authentication and trusted domains, but we have no access at all to their domain.  Is is possible to create an AD user or group on their side and have it function on our side?

As a workaround - probably unsupported, we take the log shipped db offline, copy the datafiles to a shell db and bring both dbs back online.  This gives us a read/write copy of the database and allows us to configure security; and make any other changes we might need.

I'm wondering if there is any way to have the vendor just create Windows or SQL Server logins (say with specific SIDs to match our AD account SIDs) and therefore keep us from having to make copies of the db in order to manage security.  I'm hoping the question is clear enough, I'll try to rephase it here:

Is it possible to create a login on one server, move the db to another server, and have an AD login already there that keeps the user from being orphaned?

but we have no access at all to their domain.

Your Windows user won't work - since you don't have access to the domain or the domains are not trusted.

What you can do?

Ask the vendor to create a SQL Server user with enough rights. Assign appropriate roles, then take that SID and create a login on the secondary server. Remember that log shipping works on the database level!

e.g. Below is pseudocode that will help you:

use LS_PRIMARY
go
create user LS_User without LOGIN;
go
--- give permissions e.g. data reader ,etc

-- get the SID
SELECT sid from sys.database_principals
where type ='S' and name = 'LS_User'

--- on the secondary server (your side)

use master
go
create login LS_User with PASSWORD = 'yourSecurePassword',
SID = 0x125896541236980KIN -- This is the SID from above query


Why does my parallel query sometimes process billions of extra rows? I was running some data prep queries when I noticed that one of them sometimes ran much longer than expected. The query should have finished in seconds but sometimes would take minutes to run. Here is the query:

CREATE TABLE dbo.X_HEAP (ID INT NOT NULL, ID2 INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (20000000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 100000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3;

Here's an actual execution plan for when the query runs quickly. I can't find anything out of the ordinary in the plan.

However, there is something wrong in the query plan when the query takes longer than expected. The final (reading from right to left) nested loop operator generates around 13.3 billion rows. I would expect only expect it to generate around 20 million rows because of the row goal introduced by TOP. I've observed between one to three threads spin out of control and it seems like this is directly responsible for the long runtime.

Why does my parallel query sometimes process billions of extra rows? What can I do about it?

I am on SQL Server 2016 SP1. I've been unable to reproduce the issue with a compatibility level of 120.

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 6.3  (Build 14393: )

Let's start by discussing why the fast query processes a little more than exactly 20 million rows. Here's a screenshot:

In my testing, the final nested loop operator running with MAXDOP 4 always generates 20008861 rows before the rows are reduced to exactly 20 million by the TOP operator. This is because parallel queries in SQL Server work with packets of data (it's the "PACKET" in the common CXPACKET wait event). We can see evidence of this by running a similar query that returns just 100 rows and with trace flag 8649 to force a parallel plan:

SELECT TOP (100)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 100000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (QUERYTRACEON 8649);

Most of the time I see around 9000 total rows processed:

The rows processed by each thread are usually close to multiples of 1023. So perhaps the packet size for this query is 1023? If threads are working with packets of data and the other threads are already in the process of getting more data when we finally get the last row, it seems reasonable that we could see more rows processed than strictly necessary due to parallelism.

For another way to analyze the packet size we can reduce the MAXDOP of the query. With a row goal of 100000 I consistently see 108442 rows processed by the nested loop join. The number of rows processed by each thread can change but the overall total was always the same in my tests. Changing MAXDOP to 3 reduces the number of rows processed to 106395. Change MAXDOP to 2 reduces the number of rows processed to 104348. For this query and a few other similar ones, I always observed a reduction of 2047 rows per DOP. Suspiciously this is nearly double the value from before, 1023. I'm not sure why it's not consistent, but at the very least we can conclude that we process more extra rows with more DOP.

That was somewhat educational but it doesn't explain why the query sometimes processes billions of rows. To get insight into that we can use the sys.dm_exec_query_profiles dmv introduced in SQL Server 2014. Here's a snapshot of some of the rows from dmv during one of the slow runs:

Here's the query plan with the relevant nodes labeled to give some context to the data:

There are a few interesting things to note here. We've inserted 19997604 rows into the table, so the query is very close to done. Threads 1-3 for the nested loop join at node_id 8 have finished all of their work. The TOP operator at node 4 has already met its row goal of 20000000 rows and has closed. However, thread 4 for the nested loop join continues to fetch rows. Those rows aren't being sent anywhere according to the last_row_time column. Also, the parallelism node at node 3 is still open. If I let the query run nothing changes except the row count for thread 4:

Eventually it will run out of packets to send and the query will finish.

It seems as if the problem is directly related to parallelism. The query gets "stuck" at node 3 in the plan, which is the distribute steams operator for the parallel insert. SQL Server 2016 supports parallel inserts into existing heap tables. SQL Server 2014 does not, so this could explain why I couldn't reproduce the issue with a lower compatibility level. So perhaps the problem is related to the parallel insert.

The most obvious workaround is to make the entire query run serially. However, suppose I still need part of the query to run in parallel for performance reasons. Parallel insert requires a TABLOCK hint. If I remove the TABLOCK hint I'm unable to reproduce the issue, but I also lose minimal logging. If I don't want to lose minimal logging I can set trace flag 9495 at the global level. It's important to also include a NORECOMPILE hint or to wipe the plan from the cache because cached plans can cause parallel inserts even with trace flag 9495 enabled. An alternative to the trace flag is to something else which prevents parallel inserts, such as adding a nonclustered index to the table. Any index will do, including a filtered one that doesn't actually contain any rows:

CREATE INDEX X_DUMMY_INDEX ON dbo.X_HEAP (ID) WHERE (ID = -1 AND ID = -2);

In summary, I can't say for sure why the query sometimes processes billions of rows but it definitely seems like unintended behavior. Perhaps it is some kind of parallelism-related race condition. For this query disabling parallel insert seems sufficient to prevent the bug from reappearing.


Multiple JOIN of 6 tables via 5 database instances I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the roles of a user which he has of all instances.

I already used UNION ALL, but the output was unstructured and you couldn't tell which role on which instance. So I tried the following for only 3 tables:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')
WHERE U.USERNAME = 'USER'
ORDER BY U.USERNAME ASC;

It actually worked, but the output wasn't satisfying:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1
ROLE_2
ROLE_3
ROLE_4
                            ROLE_1
                            ROLE_2
                            ROLE_4
                            ROLE_5
                            ROLE_6

Is there any way to make an output like this:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1                      ROLE_1
ROLE_2                      ROLE_2
ROLE_3
ROLE_4                      ROLE_4
                            ROLE_5
                            ROLE_6

I tried ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2') but the output was even worse.

You guys have any suggestions or helpful thoughts?

The issue is you're restricting on the instance field, which is resulting in the roles only appearing in one column or the other.

To get around this, remove the restrictions on db_instance and full outer join the roles from the second database to the roles in the first, like so:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE)
FULL OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND V.GRANTED_ROLE = W.GRANTED_ROLE)
WHERE nvl(U.USERNAME, v.username) = 'USER'
ORDER BY nvl(W.GRANTED_ROLE, V.GRANTED_ROLE) ASC;

This SQLFiddle has a working example. Updated fiddle following comments from Andriy.


creating a read only user in Oracle I have an Oracle database and a user that has all privileges to access that database. Now I want to create a new read only user who can only get the DDL of that database and can only read the data. What are the least privileges or roles that are needed to be granted to the new user? I don't want to perform any kind of alter, update or delete on that database.

You need to grant the create session privilege in order for the user to be able to connect. Then grant the select privilege on every table the user needs to access. To grant the user the privilege to read all tables in the entire database you can use the select any table privilege.

I think the PUBLIC role has the execute privilege for the DBMS_METADATA package so the user should be able to extract the DDL of the tables automatically.