Why MS SQL bigint type is implicitly mapped to float64 python type, and what is the best way to handle it? Python integer type has unlimited precision so it is more than capable to hold a bigint value of MS SQL (64 bit). Still it is implicitly mapped to float64 python type, when passed to an external script.
This can cause serious calculation errors for large integers.
So why is it mapped to float64?
My guess is:
R was added before Python via the Extensibility architecture and it has fixed precision integers (32 bit). So it can't hold bigints. So perhaps this is a compatibility issue.
What is the best practice to ensure precise calculations?
Simple but working idea: pass bigints as string then parse them as int.
I know it has a slim chance to cause problem in practice, but still good to know.
How can it be a problem:
I wrote a simple example to demonstrate how can it be a problem:
CREATE TABLE #test (
    big_integer BIGINT
);

INSERT INTO #test
    (big_integer)
VALUES
    (36028797018963968),
    (36028797018963968 + 1);

EXECUTE sp_execute_external_script
    @language = N'Python',
    @input_data_1 = N'SELECT big_integer FROM #test',
    @script = N'
print(InputDataSet.dtypes)
OutputDataSet = InputDataSet
'

Executing this code on SQL Server 2019 will give you the result of:
| | (No column name)  |
|---------------------|
|1| 36028797018963970 |
|2| 36028797018963970 |

and because of the print(InputDataSet.dtypes) statement we can see the following message:
...
STDOUT message(s) from external script:
big_integer    float64
dtype: object
...

So we got a floating point rounding error. The value of this error for big enough integers is greater than 1, which is the root of this problem.
It is out of the scope of this question to teach floating point arithmetics, but I link some good materials if you don't understand what did happen:
Simple example - Stack Overflow.
Floating Point Numbers - Computerphile
The IEEE 754 Format - Oxford
I also share a small ipython sample if you want to experiment with this (which isn't a substitute of learning the theory behind this):
In [16]: import numpy as np

In [17]: a = 2**55

In [18]: a
Out[18]: 36028797018963968

In [19]: float(a) == float(a + 1)
Out[19]: True

In [20]: float(a)
Out[20]: 3.602879701896397e+16

In [21]: float(a + 1)
Out[21]: 3.602879701896397e+16

In [22]: np.nextafter(float(a), np.inf)
Out[22]: 3.6028797018963976e+16

Note
To run my example T-SQL some conditions must be met:

Machine Learning Services must be installed
external scripts must be enabled
Privilege to execute external scripts must be granted
You must have SQL Server 2017 CTP 2.0 or later

I write down the best solution what I found:
CREATE TABLE #test (
    big_integer BIGINT
);

INSERT INTO #test
    (big_integer)
VALUES
    (36028797018963968),
    (36028797018963968 + 1);

CREATE TABLE #out (
    big_integer BIGINT
);

INSERT INTO #out
    EXECUTE sp_execute_external_script
        @language = N'Python',
        @input_data_1 = N'SELECT CAST(big_integer AS VARCHAR(20)) AS big_integer FROM #test',
        @script = N'
import numpy as np
print(InputDataSet)
InputDataSet["big_integer"] = InputDataSet["big_integer"].astype(np.int64)
InputDataSet["big_integer"] = InputDataSet["big_integer"] + 1
InputDataSet["big_integer"] = InputDataSet["big_integer"].astype(str)
OutputDataSet = InputDataSet
';

SELECT big_integer FROM #out;

I did what I supposed in the question:

cast the big_integer column to VARCHAR(20) the maximum length of a string representation of 64 bit signed integer:

In [34]: len(str(-2**63))
Out[34]: 20

In [35]: len(str(2**63-1))
Out[35]: 19

cast it back to numpy.int64 type in the external script.

made a simple calculation: incremented all values in the column

cast it back to string still in python. This step also necessary, because the implicit type conversion works both ways.

Inserted the values into the big_integer column of the #out table. Which also has a BIGINT type and the returned strings were implictly casted to BIGINT

Note
It would be a rare event where you need to handle this problem. The integer value must be bigger than 2^52 so a distance between two float64 will be greater than 1.
In [50]: def float_distance(x):
    ...:     x_float = float(x)
    ...:     x_next_float = np.nextafter(x_float, np.inf)
    ...:     x_float_diff = x_next_float - x_float
    ...:     return(x, x_float, x_next_float, x_float_diff)

In [51]: float_distance(2**52)
Out[51]: (4503599627370496, 4503599627370496.0, 4503599627370497.0, 1.0)

In [52]: float_distance(2**53)
Out[52]: (9007199254740992, 9007199254740992.0, 9007199254740994.0, 2.0)

I guess this may occur if your store the results of high throughput scientific research in physics or in bioinformatics.


How do you properly remove "REQUIRE SSL" from a single user? I successfully granted REQUIRE SSL to a single user by doing...

mysql -u"${targetMySqlUser}" -p"${targetMySqlPass}" -e "GRANT USAGE ON dbname.* TO 'dbusername'@'%' REQUIRE SSL;"

but im failing on REMOVING or REVOKING this flag from the user, using revoke. i guess im fighting with the syntax. Is there a proper way to remove it with the "revoke" command, without revoking the whole permission ?

MySQL 5.5 manual , this site and the interwebs didn't helped me yet finding a proper counter-way.

This SQL statement will work.

UPDATE mysql.user SET ssl_type = '' WHERE ssl_type = 'any' ; FLUSH PRIVILEGES;

but I believe where is a GRANT REQUIRE SSL there must me a REVOKE REQUIRE SSL, isn't there ?

What you are looking for does not exist in MySQL 5.5

Unfortunately, the ALTER USER command for MySQL 5.6 is limited. All you can do is

ALTER USER user@host PASSWORD EXPIRE;

In MySQL 5.7, you could run the ALTER USER command as follows

ALTER USER user@host REQUIRE NONE;

When it comes to MySQL 5.5, you did the most expedient way possible. Great !!!

A more politically correct way would have been to do the following:

DROP USER user@host;
CREATE USER user@host;
GRANT ... on ... TO user@host IDENTIFIED BY '...';

Otherwise, I commend you for doing what was needed.


Tricky selection of grouped rows, selecting based on values of two distinct but related rows Say I have a table with the following information (sorted here for display purposes)
taskid   |  reference   |  centreid  |  date_out
---------------------------------------------------
10001    | 'IX#001323'  |         1  | '2012-09-28'
10022    | 'IX#001323'  |         2  | '2012-10-04'
10032    | 'IX#001544'  |         1  | '2012-10-09'
10046    | 'IX#001666'  |         1  | '2012-10-10'
10056    | 'IX#001666'  |         3  | '2012-10-13'
10078    | 'IX#002100'  |         2  | '2012-10-23'
10098    | 'IX#002100'  |         1  | '2012-11-01'

I want to select the groups of rows that have the same reference where the first occurrence belonged to a particular centreid, but where the second occurrence was within a particular date range. For example, I want to find all taskids that have the same reference where the second date falls in October but the first centreid is 1, the results should be:
taskid   |  reference   |  centreid  |  date_out
---------------------------------------------------
10001    | 'IX#001323'  |         1  | '2012-09-28'
10022    | 'IX#001323'  |         2  | '2012-10-04'
10046    | 'IX#001666'  |         1  | '2012-10-10'
10056    | 'IX#001666'  |         3  | '2012-10-13'

It's possible that there are more than two taskids with the same reference, in which case,  I am always interested in the last date and the second to last centreid (when ordered by date_out). Each time I write a query it ends up being very complex and still not returning exactly the rows I want.
I have:
SELECT taskid
FROM tasks t1
WHERE (EXISTS (SELECT 1
               FROM tasks t2
               WHERE t1.taskid <> t2.taskd
                 AND t1.reference = t2.reference))
 AND
    -- STUCK
    -- Unsure how to select amongst remaining rows where max(date) between two dates

Thanks in advance for any help. I'm using PostgreSQL 9.1 if that makes any difference...
Update (2012-11-10)
I have used the answer provided by Erwin, but the self join in CTE d took a long time to execute, so I split it up into two different CTEs. This provided an overwhelming performance improvement. Erwin's original answer executed in around 120 seconds (choking on CTE d) whereas the below executes in just under 2 seconds.
... snip ...
,d AS (
   SELECT reference, grp
   FROM   c
   WHERE  c.rn = 1 AND c.date_out BETWEEN '2012-10-01' AND '2012-10-31'
   )
,e AS (
   SELECT reference, grp
   FROM   c
   WHERE  c.rn = 2 AND centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
JOIN   e USING (reference, grp)
ORDER  BY reference, date_out

Get qualifying rows only
One way ..
WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
, y AS (
   SELECT *
   FROM   x
   WHERE  x.rn = 1
   AND    date_out >= '2012-10-01'
   AND    date_out <  '2012-11-01'
   )
, z AS (
   SELECT x.*
   FROM   x
   JOIN   y USING (reference)
   WHERE  x.rn = 2
   AND    x.centreid = 1
   )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y
JOIN   z USING (reference)

UNION  ALL
SELECT taskid, reference, centreid, date_out
FROM   z
ORDER  BY reference, date_out;

Another way:
WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT x.*, y.taskid AS taskid2, y.centreid AS centreid2, y.date_out AS date_out2
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y

UNION  ALL
SELECT y.taskid2, y.reference, y.centreid2, y.date_out2
FROM   y
ORDER  BY reference, date_out;

I'd expect the second one to be faster. Depends on your data distribution. Test with EXPLAIN ANALYZE.
Get all rows for qualifying references
WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT reference
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT *
FROM   tbl
JOIN   y USING (reference)
ORDER  BY reference, date_out;

-> sqlfiddle
Answer to follow-up in comment
Separate groups if more than 30 days between entries.
WITH a AS (
   SELECT *
         ,lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) AS last_date
         ,CASE WHEN date_out >
                   (lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) - 30)
               THEN 0 ELSE 1
          END AS step
   FROM   tbl
   )
,b AS (
   SELECT *
         ,sum(step) OVER (PARTITION BY reference ORDER BY date_out DESC) AS grp
   FROM a
   )
 ,c AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference, grp ORDER BY date_out DESC) AS rn
   FROM b
   )
,d AS (
   SELECT reference, grp
   FROM   c
   JOIN   c d USING (reference, grp)
   WHERE  c.rn = 1
   AND    c.date_out >= '2012-10-01'
   AND    c.date_out <  '2012-11-01'
   AND    d.rn = 2
   AND    d.centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
ORDER  BY reference, date_out

-> sqlfiddle
But while pure SQL is a beaut .. I would solve this procedurally in a plpgsql function. Very similar to this recent answer on SO. Would be faster, because it can be done in a single table scan.


How to change initial extent of an existing partition I'm having trouble with something that should be rather simple ... I'm sure I'm missing something obvious.

I'm trying to change the initial extent of an existing table partition.

For some reason, I have an existing table with initial extents of various sizes, and I'm trying to realign them as I'm just doing some reorg and moving them about (DEV environment).

I'm working on Oracle 11.2.0.3.0

I have a table, range partitioned. Residing in a locally managed tablespace.
It was originally system managed, however, I'm trying to create a new tablespace using uniform extents. I've done that, and am trying to MOVE the objects over, however, I have a couple (empty) partitions which seem to want to have multiple extents (initial 8M, despite uniform of 1M). (oddly some partitions are fine with an initial of 1M ..)

I tried:

alter table my_table modify default attributes storage ( initial 1M next 1M );

that ran, but didn't seem to do anything. So I tried this:

alter table my_table modify default attributes for partition part101 storage ( initial 1M );

but that throws an exception:

ORA-14264: table is not partitioned by Composite Range method

How do I alter the INITIAL extent of a (range) partition ?

Even if its an empty partition, MOVE is the way to do it:

create table t1 (c1 number, c2 varchar2(100))
segment creation immediate
partition by range (c1)
(
  partition p1 values less than (10),
  partition p2 values less than (20)
);

SQL> select partition_name, initial_extent, next_extent
     from user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                          8388608     1048576
P2                          8388608     1048576

Default 8M/1M. Now move the partition:

SQL> alter table t1 move partition p1 storage (initial 65536 next 65536);

Table altered.

SQL> select partition_name, initial_extent, next_extent from
     user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                            65536       65536
P2                          8388608     1048576

Modifying default attributes will have its effect only on partitions created after setting it:

SQL> alter table t1 modify default attributes storage ( initial 1M next 1M );

Table altered.

SQL> alter table t1 add partition p3 values less than (30);

Table altered.

SQL> select partition_name, initial_extent, next_extent from
     user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                            65536       65536
P2                          8388608     1048576
P3                          1048576     1048576


selective undoing of commited transactions in PostgreSQL I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables.  Updates are very light, reads not so heavy, but the data is extremely valuable.

These rules are updated by a variety of complex, legacy, overlapping systems, all of which are non-trivial to modify.  There are even some updates by humans using SQL templates.

And we really have had "panic why did you delete that customer's route?  how will we undo this?" moments.

So is there an easy way to version these tables?  To be able to manually review and decide to undo a particular transaction, and review which subsequent transactions rely upon it and so on?

I believe MSSQL and Oracle has versioning built-in.  Does PostgreSQL support any similar 'change data capture' system?

I hesitate to roll my own trigger-based scripts.  I can see that I can put the txid_current() into a 'shadow' table, but not how to record if a transaction commits or rolls back, for example.  TableAudit/TableLog, for example, seems to be on the table-only level.  If I had to roll my own, how would I do that and what aspects would I need to remember?

Are there tools or scripts for doing multi-table transaction tracking in PostgreSQL, and tools for recovery and undoing them?

You have a few options here.  If it is just committed transactions you need to worry about I would use a trigger-based solution. There are already extensions to help with this.  See, for example, e-maj and there is good documentation in how to roll your own, and 2ndQuadrant has released a version of an audit trigger on GitHub.

One of the key issues you may have is in determining how to handle rolling back of selective updates.  Do you need to restore a table to a specific point in time?  Or just a row?  With any solution here you are likely to have to roll your own solution to this part.  Tablelog does indeed appear to be dead, unfortunately.


How to optimise multiple joins with OR? I need to extract some statistical information form drupal database tables. The problem with drupal is that it splits it's data for entities across multiple tables and getting simple information from such database usually requires multiple joins.

In this case I have structure similar to this (I'm simplifying things for the purpose of this question as the whole structure is way more complicated):

node table

nid
title
uid (user id from users table)
type (can be: flight or airport)
[others]

departure_field table

id
flight_node_id (node id from nodes table with type 'flight')
departure_node_id (node id from nodes table with type 'airport')

arrival_field table

id
flight_node_id (node id from nodes table with type 'flight')
arrival_node_id (node id from nodes table with type 'airport')

The query

Now let's say I want to know top airports visited by the given user. The query could go like this:

SELECT
  COUNT(a.nid) AS cnt, a.nid, a.title
FROM
  node a, /* Airport */
  departure_field dep,
  arrival_field arr,
  node f /* Flight */
WHERE
  /* All flights of user with id 100 */
  f.type = 'flight' AND f.uid = 100
  AND /* Include data for departure and arrival airports */
  f.nid = dep.flight_node_id AND f.nid = arr.flight_node_id
  /* Combine that with airport data */
  AND (a.nid = dep.departure_node_id OR a.nid = arr.arrival_node_id)
GROUP BY
  a.nid
ORDER BY
  a.cnt DESC

The problem

This query, although looks quite simple, uses temp table and file sort. And of course runs for ages. How can I optimise it for MySQL to run fast enough to be usable during webpage rendering? For now the query executes few minutes with database having data for 50k+ flights.

Note that this is not the actual query and table structure. The field naming in the drupal database is insane, so I tried do simplify things for the purpose of this question.

As jkavalik suggested in his comment, I tried UNION and it worked great. The performance improvement was astronomical.

SELECT
  COUNT(a.nid) AS cnt, a.nid, a.title
FROM
  node a /* Airport */
JOIN
  (
    (SELECT
      dep.nid
    FROM
      departure_field dep
    JOIN
      node f
    ON
      f.nid = dep.flight_node_id
    WHERE
      f.type = 'flight' AND f.uid = 100
    )
  UNION ALL
    (SELECT
      arr.nid
    FROM
      arrival_field are
    JOIN
      node f
    ON
      f.nid = arr.arrival_node_id
    WHERE
      f.type = 'flight' AND f.uid = 100
    )
  ) airport
ON
  airport.id = a.nid
GROUP BY
  a.nid
ORDER BY
  a.cnt DESC

Note that in my case UNION ALL was the key, because it includes duplicates from both arrivals and departures. And this was important because I wanted to count how much times each airport was visited. Without ALL each airport would show up only once as simple UNION removes duplicates.


Inserting Missing day of data into table - Need to increment primary key by 618 for each key So I recently switched servers, but there is a day inbetween the switch of servers where the data didn't get carried over. I have exported the missing data in a CSV.

I know there is 617 rows to be inserted into the "primary" database, problem is there is entries which have already taken the "primary key" (in this case, it is called "id"). This ID auto increments by 1.

What I would like to do, is the following:

starting at id = 552611 ADD $ID + 618 & repeat till end of table.

This would allow me to insert the 617 keys starting at:

id = 552611

Ending at:

id = 553228

My SQL-Foo is not that strong! Can anybody point me in the right direction?

Since you're changing the identity of the row, can we assume that it's not used as a foreign key of another table? If it's only purpose is to uniquely identify the row of the table it's on, then don't bother attempting to assign it a new identity because it's already auto incremented by one, upload your CSV without specifying the id column and it will take care of the id.

You'd have to worry about key collision and identity_insert etc, it's just a mess, but if your heart is set on it, there are ways it could be done.


Fast alter column NVARCHAR(4000) to NVARCHAR(260) I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).

Using

ALTER TABLE [table] ALTER COLUMN [col] NVARCHAR(260) NULL

results in SQL Server rewriting the entire table (and using 2x table size in log space), which is billions of rows, only to change nothing, isn't an option. Increasing the column width doesn't have this problem, but decreasing it does.

I have tried creating a constraint CHECK (DATALENGTH([col]) <= 520) or CHECK (LEN([col]) <= 260) and SQL Server still decides to re-write the entire table.

Is there any way to alter the column data type as a metadata-only operation? Without the expense of rewriting the entire table?  I'm using SQL Server 2017 (14.0.2027.2 and 14.0.3192.2).

Here is a sample DDL table to use to reproduce:

CREATE TABLE [table](
    id INT IDENTITY(1,1) NOT NULL,
    [col] NVARCHAR(4000) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

And then run the ALTER.

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data:

CAST the column as NVARCHAR(260) in all codes that uses it. The query optimizer will calculate the memory grant using the casted data type instead of the raw one.
Rename the table and create a view that does the cast instead. This accomplishes the same thing as option 1 but may limit the amount of code you need to update.
Create a non-persisted computed column with the right data type and have all of your queries select from that column instead of the original one.
Rename existing column and add computed column with the original name. Then adjust all of your queries making updates or inserts to the original column to use new column name instead.


hierarchical query in oracle Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume the number of tables is huge in a schema. What would be the query that gives me the list of tables in that order which I can perform migration.

To do that you could try:

select
   level,
   owner, name,
   referenced_owner, referenced_name
from
   dba_dependencies
start with
    owner = '${YOUR_TABLE_OWNER}'
and name = '${YOUR_TABLE_NAME}'
connect by prior referenced_owner = owner
       and prior referenced_name = name
       and prior referenced_type = type
and type = 'TABLE';

Datapump uses a similar strategy to prevent constraint violations.
I hope this helps.


Where does SQL_Variant datatype come from in select statement using LOGINPROPERTY In the below code I do not understand where SQL_Variant is coming from - The column it is complaining about is LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS LastChangeTime According to MSFT documents this is a DATETIME column.

Where does the SQL_Variant datatype come from?

DROP TABLE #PasswordChange;
CREATE TABLE #PasswordChange (name VARCHAR(50),
lastPWChangeTS DATETIME,
PWhash VARBINARY(26));

INSERT INTO #PasswordChange (name,
lastPWChangeTS,
PWhash)
SELECT [name],
LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS LastChangeTime,
LOGINPROPERTY([name], 'PASSWORDHASH') AS PWhash
FROM sys.syslogins
WHERE loginproperty([name], 'PASSWORDLASTSETTIME') > DATEADD(HOUR, -24,
GETDATE());

Msg 257, Level 16, State 3, Line 6
Implicit conversion from data type sql_variant to datetime is not
allowed. Use the CONVERT function to run this query.

The return value depends on the input value, but the result set returns sql_variant to accommodate all possible return types.

You'll have to make some adjustments to your code.

INSERT INTO #PasswordChange
     ( name, lastPWChangeTS, PWhash )
SELECT  name,
        CONVERT(DATETIME, ISNULL(loginproperty([name], 'PASSWORDLASTSETTIME'), 0)) AS LastChangeTime,
        CONVERT(VARBINARY(26), LOGINPROPERTY(name, 'PASSWORDHASH')) AS PWhash
FROM    sys.syslogins
WHERE   CONVERT(DATETIME, loginproperty([name], 'PASSWORDLASTSETTIME')) > DATEADD(HOUR, -24, GETDATE());


Odd Stream Aggregate behaviour Query:

declare @X xml = '
<item ID = "0"/>
<item ID = "1"/>
<item/>
<item/>';

select I.X.value('@ID', 'int')
from @X.nodes('/item') as I(X);

Result:

-----------
0
1
NULL
NULL

Execution plan:

The top branch shreds the XML to four rows and the bottom branch fetches the value for the attribute ID.

What strikes me as odd is the number of rows returned from the Stream Aggregate operator. The 2 rows that comes from the Filter is the ID attribute from the first and second item nodes in the XML. The Stream Aggregate returns four rows, one for each input row, effectively turning the Inner Join to an Outer Join.

Is this something that Stream Aggregate does in other circumstances as well or is it just something odd going on when doing XML queries?

I can not see any hints in the XML version of the query plan that this Stream Aggregate should behave any differently than any other Stream Aggregate I have noticed before.

The aggregate is a scalar aggregate (no group by clause). These are defined in SQL Server to always produce a row, even if the input is empty.

For a scalar aggregate, MAX of no rows is NULL, COUNT of no rows is zero, for example. The optimizer knows all about this, and can transform an outer join into an inner join in suitable circumstances.

-- NULL for a scalar aggregate
SELECT MAX(V.v) FROM (VALUES(1)) AS V (v) WHERE V.v = 2;

-- No row for a vector aggregate
SELECT MAX(V.v) FROM (VALUES(1)) AS V (v) WHERE V.v = 2 GROUP BY ();

For more about aggregates, see my article Fun With Scalar and Vector Aggregates.


Quick and easy ways to check SQL/DB performance We host a web application for customers, sometimes they call in and complain of slowness. I was asked to have a list of things to check from network down to the application.

For SQL, can any suggest any quick queries that can be run or any good commercial tools that could be purchased that would quickly determine if SQL is the root cause or is otherwise healthy?

The Brent Ozar Unlimited First Responder Kit is a good and free place to start. It will walk you through looking for slow queries and comes with a handy worksheet of what to do.


PostgreSQL. Shared_memory and sessions I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with application Jira then there is 16GB left for other usage (including postgres, httpd).
According to PostgreSQL documentation size of the shared_buffers should be 25% of RAM. It could be 16GB * 0,25 = 6,5GB.
Unfortunately I have doubts because application jira is using 20 connection sessions to PostgreSQL server. So I afraid that when I increase shared_buffers it will multiply it by 20 usage of entire memory.

Is it true:

approx. all memory consumed by postgres = shared_buffers*sessions

What will you suggest to do with shared_buffers parameter?

Yes, shared_buffer is not allocated per-session or per-user. It is shared amoung all sessions, and it is allocated only once.
When parameter is not shared, it is clearly stated, i.e., on page https://www.postgresql.org/docs/9.6/runtime-config-resource.html you may see that on parameter temp_buffers description, it is stated that they are "temporary buffers used by each database session".


Statistics for AlwaysOn Availability DBs Can you please help me understand why there is a need for SQL Server to maintain statistics in tempdb for DBs that are part of an Always On Availability Group?

Isn't "everything" that happens in the Primary replica, replicated to the Secondaries? If so, statistics changes that happen in Primary would be replicated into secondary isn't it?

Isn't "everything" that happens in Primary replica, replicated to the Secondaries? If so, statistics changes that happen in Primary would be replicated into secondary isn't it?

You are correct.

  Can you please help me understand why there is a need for SQL Server to maintain statistics for DBs that are part of Always On availability group in tempdb?

This really comes into play when there are secondary replicas which are readable. A readable secondary replica may have a workload on it that is different than the primary and could benefit from different statistics on the columns and indexes. This is where the temporary statistics in TempDB come into play (because the database is [for all outside purposes] readonly).

These statistics are really just to support differentiating workloads on a readable secondary without having to explicitly create them on the primary and let them flow through.

If readable secondary replicas aren't used, these will be of no use to you and won't even be created.


Convert a date range to an interval description A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, something like "1 year, 3 months to go."

The built-in DATEDIFF function

  Returns the count ... of the specified datepart boundaries crossed between the specified startdate and enddate.

If used as-is this could produce misleading or confusing results. For example, using an interval of YEAR would show 1999-12-31 (YYYY-MM-DD) and 2000-01-01 to be one year apart whereas common sense would say these dates are separated by only 1 day. Conversely using an interval of DAY 1999-12-31 and 2010-12-31 are separated by 4,018 days while most people would see "11 years" as a better description.

Starting from the number of days and calculating months and years from there would be prone to leap year and size-of-month errors.

I got to wondering how this could be implemented in the various SQL dialects? Example output includes:

create table TestData(
    FromDate date not null,
    ToDate date not null,
    ExpectedResult varchar(100) not null); -- exact formatting is unimportant

insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
       ('1999-12-31', '2000-01-01', '1 day'),
       ('2000-01-01', '2000-02-01', '1 month'),
       ('2000-02-01', '2000-03-01', '1 month'),              -- month length not important
       ('2000-01-28', '2000-02-29', '1 month, 1 day'),       -- leap years to be accounted for
       ('2000-01-01', '2000-12-31', '11 months, 30 days'),
       ('2000-02-28', '2000-03-01', '2 days'),
       ('2001-02-28', '2001-03-01', '1 day'),                -- not a leap year
       ('2000-01-01', '2001-01-01', '1 year'),
       ('2000-01-01', '2011-01-01', '11 years'),
       ('9999-12-30', '9999-12-31', '1 day'),                -- catch overflow in date calculations
       ('1900-01-01', '9999-12-31', '8099 years 11 months 30 days');  -- min(date) to max(date)

I happen to be using SQL Server 2008R2 but I am interested to learn how other dialects would handle this.

This answer shows an implementation using a SQL Server (2005+) CLR function.

-- Enable CLR (if necessary)
EXECUTE sys.sp_configure
    @configname = 'clr enabled',
    @configvalue = 1;

RECONFIGURE;

Assembly and function

CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM [encoded data, 10242 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.IntervalDescription
(
    @From date,
    @To date
)
RETURNS nvarchar(100)
AS EXTERNAL NAME
    DBA.UserDefinedFunctions.IntervalDescription;

Usage

SELECT
    TD.FromDate,
    TD.ToDate,
    TD.ExpectedResult,
    IntervalDescription = dbo.IntervalDescription(TD.FromDate, TD.ToDate)
FROM dbo.TestData AS TD;

Result

Source

I am not a C# programmer!

using Microsoft.SqlServer.Server;
using System;
using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess = DataAccessKind.None,
        SystemDataAccess = SystemDataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true,
        Name = "IntervalDescription"
        )
    ]
    [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 100)]
    public static string IntervalDescription(DateTime From, DateTime To)
    {
        var workDate = From;
        int years = To.Year - From.Year;
        int months = 0;
        int days = 0;

        if (years != 0)
        {
            if (From.Month > To.Month || (From.Month == To.Month && From.Day > To.Day))
            {
                years--;
            }
            workDate = workDate.AddYears(years);
        }

        while (workDate < To && (workDate.Year != DateTime.MaxValue.Year || workDate.Month != DateTime.MaxValue.Month))
        {
            if (workDate.AddMonths(1) <= To)
            {
                months++;
                workDate = workDate.AddMonths(1);
            }
            else
            {
                break;
            }
        }

        while (workDate < To)
        {
            days++;
            workDate = workDate.AddDays(1);
        }

        StringBuilder sb = new StringBuilder(100);

        if (years > 0)
        {
            sb.Append(years);
            sb.Append(years == 1 ? " year" : " years");
            sb.Append((months > 0 || days > 0) ? ", " : string.Empty);
        }

        if (months > 0)
        {
            sb.Append(months);
            sb.Append(months == 1 ? " month" : " months");
            sb.Append(days > 0 ? ", " : string.Empty);
        }

        if (days > 0 || (years == 0 && months == 0))
        {
            sb.Append(days);
            sb.Append(days == 1 ? " day" : " days");
        }

        return
            sb.ToString();

    }
}


How improve the performance of the query I think the correct answer is B and C. My reasoning:

The returned row is 1, but the Disk IO and Query is very high. So optimizer use a full table scan not index scan. B and C can make query choose index scan.

But the provided answer is A and E. Why, is there valid reasoning for A and E? Are my answers and understanding correct? Or something else?

A - Makes no sense. employee_id is unique, the query retrieves at most 1 row based on an equality filter, this should be a dead simple INDEX UNIQUE SCAN.

B - I don't think so. employee_id is unique, the number of distinct values equals to the number of rows. With huge gaps in a unique column, a height balanced histogram can still provide extra information for better estimates for a full/range scan, but this is an index unique scan.

C - Maybe, but very unlikely in a real scenario. The above query should return its result after a few block reads and gets (lets say, <10, index height + 1 table access). Instead, it took 453 disk reads and 797 gets. Maybe the query read the whole table instead of an index unique scan. In that case, decreasing MBRC would increase the cost of a FTS maybe up to the point, where the index scan becomes favored. Still, in a real scenario, with proper statistics, I doubt this would happen.

D - Maybe, but again, unlikely in a real scenario. "Decreasing" the index may reduce the cost of it. The clustering factor and number of leaf blocks do not affect the cost of an index unique scan. This leaves us with the index height, and yes, that may decrease, which means the cost decreases as well, but this difference is so small, I find it difficult to believe the database would choose a FTS with 453 reads over an index unique scan with an index that has a much lower BLEVEL (typically between 0..3, it is rare to have indexes with higher BLEVEL).

E - Makes no sense. Same as A.


Generating an expiry date based on fields in different tables I want a field that gives the date a certificate expires.

I think i would create a query and add a field using something like this..

dateadd("d", [DaysToExpire], [DateCompleted])

My trouble is, [DaysToExpire] is on a different table to [DateCompleted]. how do i resolve that?

Is there a way to get the query field to refer to both tables?

Can I create a lookup field on one of the tables that auto populates once the 2 tables a linked by a record?

I am all pretty new to this so sorry if my language is a bit off. Any help would be much appreciated.

The code generated by access:

SELECT tbl_Courses.CourseName
     , tbl_Courses.CourseDescription
     , tbl_Training.DateCompleted
  FROM tbl_Courses
 INNER JOIN tbl_Training
    ON tbl_Courses.CourseID = tbl_Training.CourseID;

Error after entering Mr.Brownstone's code:

You can create a view using a JOIN statement to combine the results of two related tables. In your design, the CourseID field appears on both the tbl_courses and tbl_training tables, using this field to perform the join you can easily derive the value that you require:

CREATE VIEW [CourseDetails]
(
    [CourseName],
    [CourseDescription],
    [DateCompleted],
    [ExpiryDate]
)
AS
SELECT tbl_Courses.[CourseName]
     , tbl_Courses.[CourseDescription]
     , tbl_Training.[DateCompleted]
     , DATEADD("d", tbl_Training.[DaysToExpire], tbl_Courses.[DateCompleted]) [ExpiryDate]
  FROM tbl_Courses
 INNER JOIN tbl_Training
    ON tbl_Courses.[CourseID] = tbl_Training.[CourseID];

There is a full tutorial on how to perform this that you might find helpful.


List the CMS SQL Server Instances using Powershell I need to run a set of Powershell commands from my Central Management Server onto all of the SQL Server Instances that it contains. Can someone let me know how this could be achieved?

I need to loop each server instance from the central management and calculate/get the free space. I am using SQL Server 2012. I need to try out the following things:

Search particular extension files on Server Instances
Check the space occupied by them
Check Free Space
Store the above info. in a file on the current system

I am simply aiming for getting the size of backup files (.bak files) only. I have a solution already where we query Invoke-Command to find the files for us on remote machine. I wanted to know whether there exist a better solution to get the task done.

First, get your server list query returning what you want, bellow is an example query to run against your CMS.

SELECT DISTINCT [server_name]
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]

Next the PowerShell, we use a loop that pings the server first to verify it is available then checks the SQL connection. If everything is good it will execute the requested code. The below executes a SQL query but anything could be put in that block. This script runs under the current windows user when connecting to the SQL Instances.

#Import the SQLPS module to use the Invoke-SQLCmd
Import-Module -Name SQLPS -DisableNameChecking -Force

#Create a variable to hold your SQL query for the server list
$ServerListQuery = @"
SELECT DISTINCT [server_name]
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]
"@

#Create a variable to hold the query or queries you want to execute against each SQL Server
$SQLServerVersionQuery=@"
SELECT GetDate() AS DateTimeStamp,
CAST(SERVERPROPERTY('ServerName')AS sysname)  AS ServerName,
@@version AS SQLServerVersion
"@

#Create your list and place it in a variable using Invoke-Sqlcmd
#Invoke-Sqlcmd -ServerInstance <TheNameOfYourCMSServer> -Database msdb (because that is where CMS is stored) -Query $ServerListQuery
$list = Invoke-Sqlcmd -ServerInstance MyCMSServer -Database msdb -Query $ServerListQuery

#Loop through the list and run your query
ForEach ($databaseinstance in $list){

#remove named instance if it exists for the ping test
$servername = $databaseinstance[0].split("\") | Select-Object -Index 0
IF(Test-Connection -ComputerName $servername -quiet) {
TRY{

#Run the query variable created at the top $SQLServerVersionQuery
Invoke-Sqlcmd -ServerInstance $databaseinstance[0] -Query $SQLServerVersionQuery -QueryTimeout 300 |
Select-Object -Property DateTimeStamp ,ServerName, SQLServerVersion

}
#Catch the error if SQL connectino doesn't work
CATCH {Write-Output "SQL Connect Error on: $databaseinstance[0] >> $Error[0]"}
}
#Catch the error if the ping test fails
ELSE {Write-Output "Server Ping Error: $servername >> $Error[0]"}
}


Is it needed to add additional column to make this clustered index unique? I have a table as listed below in SQL Server 2012. There is a clustered index on RequisitionID – but this column is not unique. There can be many ProductID for one RequisitionID.

CREATE TABLE [dbo].[RequisitionProducts](
    [RequisitionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [Qty] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [UnitAmount] [decimal](10, 2) NOT NULL,
 CONSTRAINT [pk_RequisitionProducts] PRIMARY KEY NONCLUSTERED
    (
        [RequisitionID] ASC,
        [ProductID] ASC
    )
)

CREATE CLUSTERED INDEX [cidx_RequistionProducts] ON [dbo].[RequisitionProducts]
(
    [RequisitionID] ASC
)
GO

I searched a lot and found that Clustered Index can be non-unique  - but only on limited scenario. Only scenario mentioned appropriate is when there is a Range Search. In my case almost all queries will be based on RequisitionID only – and there is no range search required.

Should I add ProductID also to make the clustered index unique? What are the pros and cons?

Should I add ProductID also to make the clustered index unique?

You should delete cidx_RequistionProducts and make the primary key clustered instead. The index that is created to support the primary key will do the job for you when filtering on RequisitionID.

  What are the pros and cons?

I see no benefit of having two indexes when one is enough to get the job done. Removing the extra index will save you disk space and IO cost when updating the table.


Efficient database design for multiple price based on quantity and attribute I have a data sample for products with different prices depending on quantity and attributes. The price might be updated occasionally (not too frequently).

PID | Name                | Type  | Colour  | Colour Print | Quantity: 1 to 149 |   150 to 199| 200 to 249 |250 to 499
1   |White ABC Product    |ABC    |White    |1CP           |2.34|2.07   |1.82|1.51
2   |White ABC Product    |ABC    |White    |2CP           |2.6 |2.31   |1.97|1.62
3   |White ABC Product    |ABC    |White    |3CP          |2.86|2.55|2.14|1.77
7   |Red ABC Product      |ABC    |Red      |1CP           |2.39|2.12|1.87|1.56
8   |Red ABC Product      |ABC    |Red      |2CP           |2.65|2.36|2.02|1.67
9   |Red ABC Product      |ABC    |Red      |3CP           |2.91|2.6|2.19|1.82
12  |White XYZ Product    |XYZ    |White    |1CP           |2.69|2.38|2.09|1.74
13  |White XYZ Product    |XYZ    |White    |2CP           |2.69|2.38|2.09|1.74
14  |White XYZ Product    |XYZ    |White    |3CP           |3.29|2.93|2.46|2.04
18  |Emerald  XYZ Product |XYZ    |Emerald  |1CP           |2.74|2.43|2.14  |1.79
19  |Emerald  XYZ Product |XYZ    |Emerald  |2CP           |3.04|2.71|2.32  |1.91
20  |Emerald  XYZ Product |XYZ    |Emerald  |3CP           |3.34|2.98|2.51  |2.09

So basically, I can design the database as the same structure as the above data sample and dump all data into the table (a lot of repetition) or I can do something like this:

Product Table:
---------
--Product id
--Product name {ABC, XYZ, PQR...so on}

Colour Table:
-------
--Colour id
--Colour value {white, red, emerald, and so on}

Colour Print Table
--------
--Colour print id
--Colour print value {1CP, 2CP, 3CP}

Quantity Table
--------
--Quantity id
--Quantity value {1-149, 150-199, 200-249,250-499}

Price Table
-------
--Price id
--Product id
--Colour id
--Colour Print id
--Quantity id
--Price

So the price table will look something like this:

Price table
price id |  product id |colour id | quantity id | colourprint id |  price
1        | 1           |1         | 1           |1               |2.34
2        | 1           |1         | 2           |1               |2.07
3        | 1           |1         | 3           |1               |1.82
4        | 1           |1         | 4           |1               |1.51
5        | 1           |1         | 1           |2               |2.6
6        | 1           |1         | 2           |2               |2.31
7        | 1           |1         | 3           |2               |1.97
8        | 1           |1         | 4           |2               |1.62
9        | 1           |1         | 1           |3               |2.86

Price is displayed to the user when they choose the options based on type of product, colour, colour print and quantity.

New products might also be added in the future. I'm looking to design the database in the most efficient way.

I find the first option easier to implement but not sure if it's the most efficient or if it's even correct.

Your input would be appreciated. Thank you!

First of all you must define your entities and these form your tables.  From what you provide above I can see two tables based on two entities:  Product and price.

Then you will add your attributes to each entity such as colour and colour print.

Then you should decide on the nature of your relationships - product has a 1:M relationship with price.

To avoid anomalies in your database you will have to normalise your tables - see https://beginnersbook.com/2015/05/normalization-in-dbms/

Hope this helps :-)


SQL Merge Replication: Cannot resolve conflict I have a SQL Merge Replication over HTTP (Replisapi.dll). I'm not very familiar with the internas of the replication conflict solving because it was always working so far.

Now I have two conflicts in the replication which I can resolve but after a few seconds the same two conflicts appear again.

The looser is the subscriber DB which wants to insert a row which already exists on Publisher. Therefore I get a Primary key Violation error.

Has anybody ever discovered such a conflict which can be resolved in the Conflict Viewer but always appears again?
How can I resolve this issue?

Primary key conflicts/violations will not be resolved automatically.  You will need to manually delete one of the offending rows to get the primary key conflicts from reoccurring on every synchronization.

If you expect primary key conflicts to occur regularly then you may want to consider doing one of the following:

Horizontally partition your data by adding a location specific identifier column and extend the primary key(s) to include this column.  Then have subscribers only insert rows belonging to their partition.
Set the Merge article property @compensate_for_errors to true which sends a compensating change to the source replica to undo the failed change when a primary key conflict occurs.


invalid memory alloc request size on simple query with array_agg on Postgres 9.3 Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine)

I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is:

select array_agg(st.search_token) from search_token st limit 200

This column search_token is a varchar(25) and I'm getting this error:

ERROR:  invalid memory alloc request size 1073741824

If I remove the array_agg from the query, it runs smoothly in 1ms.

It used to work fine and started to fail on last days. Nothing was changed in the config file or on OS config.

Another funny fact. If I write the query as:

select array_agg(x.search_token) from (select st.search_token from search_token st limit 100000) x;

It runs in 64ms. (Note that I also raised the limit from 200 to 100000). Why is it trying to allocate 1GB ram to process this query with limit 200 and isn't for the subquery form?

In your first query, you do the aggregate on the whole table, then limit the single row result to 200 rows.  This is because the LIMIT is applied on the result set of its containing query.

If you did an EXPLAIN select array_agg(st.search_token) from search_token st limit 200;, it would return something like

Limit [...]
   -> [...]Aggregate [...]

Basically, the closer one node in the plan is to the bottom, the earlier it is executed (this is more complicated than that, but in this context it is true).

The good news is that you found the solution, too :)


Set "Encryption Enabled" to FALSE...what can go wrong? I'm migrating a database from SQL Server 2012 to SQL Server 2014 on the same server.

I'm configuring LOG SHIPPING on a database that is apparently encrypted:

When setting the log shipping I got this message (when it started to restore the database from a backup):

I'm the new DBA here and there's no report of master keys, encrypted keys etc.

I'm setting log shipping from SQL2012 to SQL2014 (with norecovery). What could go wrong if I set this database to Encryption enabled - false and try to restore the database with the log shipping wizard?

There are some questions about this problem , like This one, but all of them say I need the password.

I Just found this certificate on master > security > certificates on the primary server (2012):

And there are some code samples over the Interet but I really can't trust them (I don't think it's too simple):

`USE MASTER
GO
ALTER DATABASE DatabaseName
SET ENCRYPTION OFF
GO
USE DatabaseName
GO
DROP DATABASE ENCRYPTION KEY
GO`

Edit1:

In this question Dylon says:

  If you are receiving the thumbprint error the certificate was not created properly from the Source Server's Cert/Key backups.

I'm confused about this topic and all I want is to disable encryption, restore the database on the other server to log shipping, and some day, activate encryption.

Edit2:

On Database > tasks > manage database encryption there are some options too:

Disabling this, I would be able to backup the database again, and restore it on the new instance?

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE.

Rather set up log shipping properly in the presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the TDE certificate from the original server and then import it on the standby server, making sure it is encrypted with the master database master key and the database master master key is in turn encrypted with the service master key. After that, if done correctly, restore operations should succeed as the required TDE certificate is present.

To copy the certificate: The certificate is in the master database of the primary instance. You need to run:

BACKUP CERTIFICATE TDE_CERT
 TO FILE <where to save cert>
 WITH PRIVATE KEY (FILE = <where to save private key>,
  ENCRYPTION BY PASSWORD = <password for the private key file>);

Then copy both cert and private key files to the secondary and restore it in master using:

 CREATE CERTIFICATE TDE_CERT
  FROM FILE = <your copied cert file>
  WITH PRIVATE KEY (FILE = <your copied private key file>,
   DECRYPTION BY PASSWORD = <password that protects private key file>);

TDE_CERT in master on the secondary needs to be encrypted with master database master key, which will likely need to be created.


SQL Server Linked Servers not connecting I have configured linked servers with "connections will be made using the login's current security context", but can only make use of them when logged on to the server, not from another machine...

Works fine:

SSMS --> SQL_SERVER_A --linked server--> SQL_SERVER_B
The test connection to the linked server succeeded

Does not work:

SSMS --> ADMIN_PC --> SQL_SERVER_A ---linked server--> SQL_SERVER_B
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

I've ran the 'Kerberos Configuration Manager for SQL Server' and everything checks OK and user I'm using is the same.

Does someone have an idea what could be the problem?

This is a common error in delegation permission for the linked server connection however you can fix it checking the Kerberos Connectivity or adding but none recommendable SQL Login ID in your Linked Server Connection.

You can find more details on how to use the Kerberos Connection Manager and add the delegation permissions on your AD based on this article.
https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/

!


MySQL Crash After Starting Binary Logs Recently I have added binary logs to mysql to start replication but after that setting, mysql process failed to start and keep crashing constantly. In mysql error logs some messages are there but not guiding in any direction towards resolution.

replication setting on master

server-id              = 1
log_bin                = /home/ebsbkdrive/mysql_repl_data/mysql-bin.log
expire_logs_days       = 10
max_binlog_size         = 100M
binlog_format           = ROW

mysql version

mysql  Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.1

Platform

Distributor ID: Ubuntu
Description:    Ubuntu natty (development branch)
Release:    11.04
Codename:   natty

error that reported in error logs,

150401  9:28:22 [Note] Plugin 'FEDERATED' is disabled.
150401  9:28:22 InnoDB: The InnoDB memory heap is disabled
150401  9:28:22 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150401  9:28:22 InnoDB: Compressed tables use zlib 1.2.3.4
150401  9:28:22 InnoDB: Initializing buffer pool, size = 10.0G
150401  9:28:23 InnoDB: Completed initialization of buffer pool
150401  9:28:23 InnoDB: highest supported file format is Barracuda.
150401  9:28:25  InnoDB: Waiting for the background threads to start
150401  9:28:26 InnoDB: 5.5.32 started; log sequence number 269850279391
09:28:26 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 456057 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7f9e7008f349]
/usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7f9e6ff54f33]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f9e6ec9fcb0]
/usr/sbin/mysqld(_ZN9MYSQL_LOG17generate_new_nameEPcPKc+0x9b)[0x7f9e6ffe87ab]
/usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG4openEPKc13enum_log_typeS1_10cache_typebmbb+0x204)[0x7f9e6ffec5a4]
/usr/sbin/mysqld(+0x284e21)[0x7f9e6fdbde21]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x59b)[0x7f9e6fdc0f3b]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xed)[0x7f9e6e2f676d]
/usr/sbin/mysqld(+0x27e2c5)[0x7f9e6fdb72c5]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

please check provided information and help me to resolve the issue.

I have resolved the issue, just thought to sharing in case it helps other in future in case face similar issue.

This is something have to do with apparmor and you don't qualify proper permission,

I have only qualify following in /etc/apparmor.d/usr.sbin.mysqld

/home/ebsbkdrive/mysql_repl_data/* rw

but was lacking read permission on folder itself

/home/ebsbkdrive/mysql_repl_data/ r

more precise first folder read itself followed by read-write within,

/home/ebsbkdrive/mysql_repl_data/ r
/home/ebsbkdrive/mysql_repl_data/* rw

I came to know this when I redirect logs to default location then it worked and also by closely looking how permission sets in armor file for default /var/log/mysql path.


What's the most unsafe MySQL configuration? I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance.

The box has 32GB of RAM, but most of those are used for running tests. I can probably spend another 64M for innodb_buffer_pool_size

The box has 8 cores with 24% utilization as well as SSD drive for MySQL.

Here's what I have so far for my.cnf:

[mysqld]
innodb_buffer_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_io_capacity = 4000
innodb_doublewrite = 'OFF'
innodb_adaptive_flushing_method = keep_average

Anyone have other ideas what else can I turn off to gain performance?

At first glance, I would look over

innodb_buffer_pool_size
innodb_log_file_size
innodb_log_buffer_size

To properly judge the first two values, you should run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This will tell you the ideal buffer pool size for the give dataset in MB. The innodb_buffer_pool_size should never be larger that 75% of installed RAM.
As for the log file size, I have two suggestions (pick one)
Suggestion 1: Set the innodb_log_file_size to be 25% of innodb_buffer_pool_size
Suggestion 2: If you want to set the log file size based on that actual amount of log file writes per hour, see my Aug 27, 2012 post Proper tuning for 30GB InnoDB table on server with 48GB RAM and my Feb 16, 2011 post How to safely change MySQL innodb variable 'innodb_log_file_size'?
As for innodb_log_buffer_size, you should max it out to 8M. The larger the innodb_log_buffer_size, the less flushing of transactions will occur.
EPILOGUE
Your original question was What's the most unsafe MySQL configuration?. The real answer to that is : a MySQL instance with no config file whatsoever !!! ;-)


Why does SQL Browser service get disabled? I have an intermittent problem with a software installation package that installs our product (written using InstallShield/InstallScript).  During the process of the installation, we restart the SQL Browser Service.  Most of the time this works fine.  But occasionally - and I have not worked out how to reproduce this predictably - the service fails to restart, and I find in my "Services" manager that the service status is set to "Disabled".

Any ideas what would be causing the service to be disabled, and how to prevent it happening?

The SQL Server Browser Service is installed often in Disabled state (and this is what my SQL Slammer link is about, it gives the history why the Browser service is preffered to be disabled unless required). It is only required for named instances or instances listening on non-default ports. Your application installer should only restart the Browser service if needed, and certainly should check for a disabled service and ask an administrator permission to enable the service (ie. display a confirmation dialog) before messing with it.


How to update MariaDB if the current version is not in default directory? I will try to update my MariaDB from 5.5.56-MariaDB to 10.3.9-MariaDB. However my current version was configured or located into another directory. How should i do it?

take a backup. either a) a copy of the datadir, or b) a sql dump (preferably both)
install the mariadb-10.3 packages

Normally only a single major upgrade is supported like 5.5 -> 10.0 -> 10.1 -> 10.2 -> 10.3 is supported however you may as well try and having your backup ready, you can install the datadir back.

Run mysql_upgrade after the upgrade.

Or, clear the datadir, run mysql_install_db -u mysql and then load the new version with the mysqldump.

Test on a test server, if downtime or potentially incompatibly is an issue.


Efficiently storing query-affecting settings in a database I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).

My example

I've got a massive table of sales orders. Let's simplify it to two columns:

SalesOrder
 OrderID      int      Primary Key
 OrderDate    date

I need to retrieve the list of orders for the last X days and this X should be configurable. That's why I introduce a table of settings where I define it:

Admin_Configuration
 SettingName   (PK)|      SettingValue
 ------------------|------------------
 DaysBack          |          30

It allows me not to hardcode this "30" in query definitions.
Then I would run queries like:

SELECT OrderId
  FROM SalesOrder as S
  JOIN Admin_Configuration as A
    ON A.SettingName = 'DaysBack'
  WHERE [OrderDate] > DATEADD(DAY, -A.SettingValue, GETDATE());

Performance

It avoids hardcoding, but leads to a performance problem. This query runs much slower than it would've, had I simply hard-coded 30 in the WHERE clause.

My understanding is that optimizer doesn't know how large my SettingValue is and assumes the worst - that all rows would be retrieved from the SalesOrders table, thus leading to a suboptimal query plan.

Trying a scalar function yielded even worse results.
I'm desperate enough to have a separate view which stores these hardcoded settings but I've been refraining from it with hopes for a better solution.

Here is the original query plan (with a join):
4 seconds elapsed

Here is the plan with subquery, as gbn suggested:

SELECT orderid
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY,
  (SELECT -NumValue
   FROM Admin_Configuration where SettingName = 'DaysBack'
  )
  , getdate())

7 seconds elapsed, slightly slower

As for the ideal performance: I just hardcoded 20, the query took 2 seconds.
It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance.

SELECT OrderId
  FROM SalesOrder as S
  JOIN Admin_Configuration as A
    ON A.SettingName = 'DaysBack'
  WHERE [OrderDate] > DATEADD(DAY, -20, GETDATE());

Using parameters would mean that I'd have to replace views (that I use now) with stored procedures/table-valued functions, which is unacceptable in my case

It needs mentioning that the Admin_Configuration table contains only
18 rows, hence it shouldn't affect performance.

Why shouldn't an 18 row table affect performance? Take a look at the estimated query plan for the original query that takes 4 seconds. The query optimizer is saying that the loop join has roughly the same cost as the scan. The scan is only 59% of the total cost. The query with just the scan finishes in two seconds. It just so happens that the costs line up well here, but SQL Server is telling you that there is some overhead to sending all of those rows through the join. It would be more typical to write the query like this:
SELECT orderid
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY,
  (SELECT -1 * MAX(NumValue)
   FROM Admin_Configuration where SettingName = 'DaysBack'
  )
  , getdate());

However, that won't help you. You'll still have the overhead of the join. You're also unlikely to get good cardinality estimates. I assume that some of your views have more complex logic than just joining to a single table. It could be important to get cardinality estimates based on the actual setting value instead of a magic number guess or an average of all of the values in the table. By that I mean that if you change your setting value from 30 to 3000 you probably want some of the query plans to change. With your current strategy that may not happen, unless you have a filtered statistics on the table for each row.
Use a view or table-valued function
If you would need to create an object per row to get good estimates, you might as well put your data into views or table-valued functions. They seem to have mostly the same performance characteristics for this problem so I'll just cover views. Let's say that you create a view that holds your settings instead of your table:
CREATE OR ALTER VIEW SETTINGS_VIEW AS
SELECT 'DaysBack' SettingName , 85 SettingValue
UNION ALL
SELECT 'Setting2', 0;

SQL Server won't create statistics on the view. Instead it'll look directly at the values. However, your cardinality estimates can still be off. For the data that I mocked up:

However, if I create a view for each setting:
CREATE OR ALTER VIEW DAYSBACK_SETTING_VIEW AS
SELECT 85 SettingValue;

And run this query:
SELECT OrderId
FROM SalesOrder as S
WHERE S.[OrderDate] > DATEADD(DAY
, (
    SELECT -1 * MAX(SettingValue)
    FROM DAYSBACK_SETTING_VIEW
)
, GETDATE());

I get a better estimate that's directly based on the value in the view:

The overhead of the join, which you think will be a problem, is still present.
Use a one-row view
Or, write a one row view with one column per setting as described in SQL Server: Variables, Parameters or Literals? Or… Constants? by Jared Ko:
CREATE OR ALTER VIEW dbo.Constants AS
SELECT
    [DaysBack] = CONVERT(integer, 85),
    [Setting2] = CONVERT(integer, 0),
    [Setting3] = CONVERT(char(6), 'Banana')
    ...

The query becomes:
SELECT
    SO.OrderId
FROM dbo.SalesOrder AS SO
JOIN dbo.Constants AS C
    ON SO.[OrderDate] > DATEADD(DAY, -C.DaysBack, GETDATE());

This gives a good estimate using the value in the view, without a join.
Use a scalar function
I almost never recommend this as an option, but you could consider using a scalar UDF in the query. Scalar UDFs are bad because they will force any query using the view to be fully run in serial, they can be executed per row instead of per query which can lead to poor performance, and the cardinality estimates from filtering against them are often based on magic number guesses and don't change with the input variables. However, your current query already runs serially and your current approach doesn't give good cardinality estimates. Consider the following function:
CREATE OR ALTER FUNCTION SETTINGS_UDF (@SettingName VARCHAR(100)) RETURNS INTEGER
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE @SettingName
        WHEN 'DaysBack' THEN 85
        WHEN 'Setting2' THEN 0
        ELSE NULL
    END;
END;

If I use that in the query there's no longer a join in the plan:

The brief testing that I did suggests that a simple function like this can be executed once per query instead of once per row, if you're careful. The numbers vary a bit, but here's what I see when I execute the three queries:
╔═══════════╦═══════════════╦═══════════════════╗
║   Test    ║ CPU Time (ms) ║ Elapsed Time (ms) ║
╠═══════════╬═══════════════╬═══════════════════╣
║ Hardcoded ║           422 ║               502 ║
║ UDF       ║           422 ║               476 ║
║ View      ║           906 ║               990 ║
╚═══════════╩═══════════════╩═══════════════════╝

In my experience, if your development framework is causing scalar UDFs to appear to be a good option then you probably need to change your framework if possible. I would start by validating that the two second performance degradation that you observed will actually be a problem. If it is, consider other solutions that don't require you to hardcode values into a set of views. That isn't really a typical thing to do.


Need help on best database design for quick retrieval of data I'm in the process of designing one database that will replace lots of CSV files that I currently use for data storage, which are starting to get messy and inconsistent. I am using C# in Visual Studio. It is for Bricklink/Lego data, and I'll I'll just explain the section that I need help with below, simplifying the actual figures:

The part of the database I'll focus on has 3 tables: -Parts contains PartID and about 30 other fields (e.g. mass, averageSalePrice). There are 50,000 Parts. -Store contains StoreID and about 10 other fields. There are 1000 Stores. -StoreParts links the two in a many-many relationship. It contains PartID, StoreID, Date, Price and Notes.

Now here is my issue: Each store has 10,000 parts. So there would be about 10 million records in StoreParts (more if I record multiple dates). One query that I am likely to run would need to retrieve all of the parts for a given store and compare their Price to the averageSalePrice in Parts. I feel this may run very slow as it would be going through 10 million records of StoreParts to find the 10 thousand parts.

When I was using CSV files to store the data, I had one file for each store/date, so it only had to open that file with the 10,000 parts. I feel this would be more efficient than having to find one store's parts in the list of 10 million or more.

Is there a way I can set up my database so there is a separate table for each store? I feel that this would be more efficient to search, but from my experience does not fit with best practice for database design, as I would have 1000 store tables. If I consider recording store data on different dates (e.g. 1 store has the price of all its parts on 100 different dates), then things could get way too big and slow.

I would welcome any advice on this, as I would love to do this properly and not have to have CSV files sitting around all over the place as I currently have. Thank you.

A modern relational database should be able to handle 10M records in a table with no particular problem. You may have to spec the hardware memory up if your queries aren't performant. I think the most expensive part of this would be the initial ingest, where you have to parse, organize, and import the CSVs. If you get new data regularly in the form of CSVs, that may be a concern, but that can usually be addressed by writing the ETL code in something efficient like Go.

My next suggestion would be using something non-relational. I see where you do joins in these datasets, but a non-relational dataset that just had records of a specific part in a specific store might not be unreasonable. I know MongoDB or other NoSQL options would likely work for this use case. NoSQL databases work very well with large amounts of data, but don't let you do much in terms of joins, but if this is the extent of your dataset, that very well might work for you.

I tend to lean toward Elasticsearch for datasets like these. Calling it a database might start a fight in certain circles, and this isn't the primary intended use for Elasticsearch, but I've found it work well as a sort of NoSQL database. It's search API is easy to integrate with applications and it's very easy to scale up to the performance you need.

tl;dr:
Don't knock a relational database with three tables like you describe--I think it'd work fine. But if that's the only join you'll ever need, flatten it and put it in a NoSQL database like Mongo, which should make it easier to achieve high performance. Depending on what you do with the data, Elasticsearch might be worth looking into.

I realize this is a rather general answer, but it's a rather high-level question, so I apologize.


Is command BACKUP asynchronous operation I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location.

Is it possible that multiple backup operations are running at the same time? I have not found any "WAIT" operation in a BACKUP clause, but this option is present in SMO command (Wait) of Backup class.

Here is my partial code (full code works well, so I want to describe it as shown below):

WHILE 1 = 1
BEGIN
   SET @currentDbName = ...

   SET @sql = 'BACKUP DATABASE ' + @currentDbName + '...'

   EXEC(@sql)
   -- maybe it is a good place to wait?
   -- WAITFOR
END

If you are running your backups as per the posted example code, they will not overlap.

An  EXEC() statement will complete before the next statement is executed, which you can demonstrate easily:

DECLARE @SQL VARCHAR(8000);

SET @SQL = 'WAITFOR DELAY ''00:00:10:00''';

SELECT GETDATE() AS [Start]
EXEC (@SQL);
SELECT GETDATE() AS [End]

BACKUP, as with all TSQL commands, is executed synchronously. Each statement in a SQL batch will complete in turn before executing the next, whether its executed standalone or via EXEC() as in your example.

Backup using SMO is different and unrelated to the TSQL BACKUP command. With SMO you have the option of executing a synchronous backup with Backup.SqlBackup or an asynchronous backup with Backup.SqlBackupAsync.


Why do I receive the error message "'' must appear in the GROUP BY clause or be used in an aggregate function"? Where am I going wrong?  Why am I getting that message when I add the MAX function?

The query involved:

WITH
   references_cve AS (
      SELECT vulnerability_id, reference
      FROM dim_vulnerability_reference
      WHERE dim_vulnerability_reference.source = 'NVD'
   )
SELECT DISTINCT ON (
      da.ip_address,
      favi.port,
      dv.severity,dp.name, dv.title,
      dv.description,
      fix,
      dv.nexpose_id,
      cve.reference,
      da.host_name,
      dos.description,
      da.last_assessed_for_vulnerabilities,
      davbs.solution_id
   )
   da.ip_address AS IPAddress,
   CASE WHEN favi.port = -1 THEN NULL ELSE favi.port END AS Port,
   dv.severity AS Severity,
   dp.name AS Protocol,
   dv.title AS VulnerabilityName,
   htmlToText(dv.description) AS Summary,
   htmlToText(dv.description) AS Description,
   htmlToText(fix) as Solution,
   'https://www.rapid7.com/db/vulnerabilities/' || dv.nexpose_id as SeeAlso,
   dv.nexpose_id AS NexposeID,
   ''||null as PluginOutput,
   cve.reference as CVE,
   ''||null as APPorOS,
   ''||null as BU,
   da.host_name as DEVICENAME,
   date(da.last_assessed_for_vulnerabilities) as LastScanDate,
   dos.description as DeviceOperatingSystem,
   MAX(sr.solution_id),
   davbs.vulnerability_id,
   dv.vulnerability_id

FROM fact_asset_vulnerability_instance favi
   LEFT JOIN dim_asset da USING (asset_id)
   LEFT JOIN dim_vulnerability dv USING (vulnerability_id)
   LEFT JOIN dim_site_asset dsa USING (asset_id)
   LEFT JOIN dim_site ds USING (site_id)
   LEFT JOIN dim_vulnerability_status dvs USING (status_id)
   LEFT JOIN dim_protocol dp USING (protocol_id)
   LEFT JOIN dim_service dsvc USING (service_id)
   LEFT JOIN dim_operating_system dos USING (operating_system_id)
   LEFT JOIN dim_asset_vulnerability_best_solution davbs ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
   LEFT JOIN dim_solution sr on davbs.solution_id = sr.solution_id
   LEFT JOIN references_cve cve on favi.vulnerability_id = cve.vulnerability_id

ORDER BY da.ip_address

The resulting message:

  Error: column "da.ip_address" must appear in the GROUP BY clause or be used in an aggregate function
  Character:456

I assume you are using PostgreSQL.

I suppose -I can't reproduce your whole query-, you are getting this error because you added an aggregate to it:

MAX(sr.solution_id)

I've set up a minimal example to reproduce this error:

CREATE TABLE t(id INT, foo int, bar int);
INSERT INTO t VALUES
(1, 10, 20),
(1, 11, 21),
(1, 12, 22),
(2, 30, 40),
(2, 31, 41),
(2, 32, 42);

Using a similar syntax:

SELECT
    DISTINCT ON (id) id,
    foo,
    MAX(bar) bar
FROM
    t
ORDER BY
    id, foo;

It returns the next error:

  ERROR:  column "t.id" must appear in the GROUP BY clause or be used in
  an aggregate function LINE 2:     DISTINCT ON (id) id,

One idea to solve it is by using a sub-query to calculate the aggregated value (but this doesn't work as one would expect):

SELECT
    DISTINCT ON (id) id,
    foo,
    (SELECT
        MAX(bar)
     FROM
       t t1
     WHERE
       t1.id = t.id) max_bar
FROM
    t
ORDER BY
    id, max_bar;

id | foo | max_bar
-: | --: | ------:
 1 |  10 |      22
 2 |  30 |      42

Assuming that you want one row per id, th eone that has the maximum bar, then this is how to use DISTINCT ON:

SELECT
    DISTINCT ON (id) id,
    foo,
    bar
FROM
    t
ORDER BY
    id, bar DESC;

id | foo | max_bar
-: | --: | ------:
 1 |  12 |      22
 2 |  32 |      42

db<>fiddle here

Using DISTINCT ON and after the comments that clarified a bit the requirements, that you want a row for each vulnerability - identified by dv.nexpose_id:

  The query has a row per vulnerability, however, some vulnerabilities have more than 1 solution which causes that row to be duplicated but with a different solution ID. I just need 1 solution ID per row.

  Nexpose ID is what identifies the vulnerability.

your query should be rewritten:

WITH
   references_cve AS (
      SELECT vulnerability_id, reference
      FROM dim_vulnerability_reference
      WHERE dim_vulnerability_reference.source = 'NVD'
   )
SELECT DISTINCT ON (
      dv.nexpose_id    -- one result for each vulnearibility
   )
   da.ip_address AS IPAddress,
      -- ...
   dos.description as DeviceOperatingSystem,

   sr.solution_id,               --<-- This is changed

   davbs.vulnerability_id,
   dv.vulnerability_id

FROM fact_asset_vulnerability_instance favi
   LEFT JOIN dim_asset da USING (asset_id)

      -- ...

ORDER BY
      dv.nexpose_id,        -- same as the DISTNCT ON
      sr.solution_id DESC   -- plus the ordering we want
  ;


Permissions for mysql 'SHOW CREATE PROCEDURE'? I'm working on creating a 'read-only' mysql database. I want the user to be able to see everything, but not be able to modify anything. My user currently has the following grants:

GRANT USAGE ON . TO 'sqlprostudio-ro'@'%' IDENTIFIED BY PASSWORD '*x'
GRANT SELECT, EXECUTE, SHOW VIEW ON northwind.* TO 'sqlprostudio-ro'@'%'

I can see stored procedures, but if I run:

SHOW CREATE PROCEDURE x

The 'Create procedure' column returns null. Is there a way I can allow a read-only user to see the creation procedure without being able to modify it?

the second line in man says:

To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

The other option (just a suggestion) you may design a procedure or function (preferable)  to show the info in question.

Regards,


Performance gain with MultiSubnetFailover for single subnet On BOL I read following about MultiSubnetFailover=True:

  The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to preconfigure new clients to support future spanning of subnets without any need for future client connection string changes and also optimizes failover performance for single subnet failovers.

As I understand the working of MultiSubnetFailover, with this option set the client driver sets up a socket for each IP address associated with the listener. They are all checked in parallel to speed up the process to find the online IP and the first one that response will be the one used for the connection. Here I see a performance gain.

But where is the performance gain in a single subnet? There is only IP associated with the listener.

When MultiSubnetFailover=True is set, SQL Server Native Client will aggressively retry the TCP connection.

So in

a multi-subnet scenario, it will attempt connections in parallel, which you already know.
a single subnet scenario, it will aggressively retry the TCP connection faster than the OS default TCP retransmit interval, so your reconnect time after an AG failover will be improved.


How to get MIN and MAX date from table data We have a nightly job that uses sql loader to insert data from a csv file. Here is a small sample of the data:

select * from ktimov.press_schd order by job;

PLANT JOB EST_COMPLETION INSERT_DATE
10    111 6/26/2017      5/10/2017
10    111 7/1/2017       5/11/2017
10    111 7/5/2017       5/12/2017
10    222 6/20/2017      5/10/2017
10    222 6/26/2017      5/11/2017
10    333 7/26/2017      5/10/2017
10    333 6/30/2017      5/11/2017
10    333 8/10/2017      5/12/2017
10    333 8/6/2017       5/13/2017
10    333 8/7/2017       5/14/2017

INSERT_DATE is default sysdate of when the record was inserted.
EST_COMPLETION is an estimate of when the JOB will be completed. So for example:

On 5/10/17, JOB# 111 was estimated to be completed by 6/26. Then on 5/11 the estimated completion was changed to 7/1.

I'm looking to query this data to show the following:

JOB#, First EST Date, Last Est date, Count of how many times it was changed.

The end result should be like this:

JOB FIRST_DATE LAST DATE Count_Changes
111 6/26/2017  7/5/2017  3
222 6/20/2017  6/26/2017 2
333 7/26/2017  8/7/2017  5

Any ideas how I can accomplish this?

Here's a simple method. We use a CTE to get the first and last INSERT_DATEs for each job; then, join to the main table to retrieve the estimated completions dates from those first and last loads:

WITH JobFirstLast (job, first_load, last_load, load_count) AS
     (SELECT job,
             MIN(INSERT_DATE) as first_load,
             MAX(INSERT_DATE) as last_load,
             COUNT(job) as load_count
        FROM ktimov.press_schd
       GROUP BY job
     )
SELECT j.job
      ,f.EST_COMPLETION as first_est_completion
      ,l.EST_COMPLETION as last_est_completion
      ,j.load_count - 1 as times_changed
  FROM JobFirstLast j
         LEFT  JOIN ktimov.press_schd f ON (j.job = f.job AND j.first_load = f.INSERT_DATE)
         LEFT  JOIN ktimov.press_schd l ON (j.job = l.job AND j.last_load = l.INSERT_DATE);

NOTE: I'm assuming that the first load is simply establishing the initial values, and that only subsequent loads are counted as changes. If you really want the total number of loads, change j.load_count - 1 to j.load_count.


Is COALESCE sargable now? One of my developers is arguing that COALESCE(column, default value) = default value is now sargable.  Is that right?

I ran the following test, and think it implies that COALESCE is non-sargable.

USE tempdb;

SELECT @@VERSION;
-- Microsoft SQL Server 2016 (RTM-CU3-GDR) (KB3194717) - 13.0.2186.6 (X64)   Oct 31 2016 18:27:32   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor)

CREATE TABLE Test
(
    ID int primary key clustered,
    Mod6 int null,
    INDEX IX_Mod6 NONCLUSTERED (Mod6)
);

INSERT INTO Test (ID, Mod6)
SELECT object_id as ID, case when name like '%k%' then null else object_id % 6 end as Mod6
FROM sys.objects;

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
where Mod6 is null or Mod6 = 0;
-- Plan shows expected seek

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;
-- Error:
-- Msg 8622, Level 16, State 1, Line 20
-- Query processor could not produce a query plan because of the hints
-- defined in this query. Resubmit the query without specifying any hints
-- and without using SET FORCEPLAN.

No COALESCE is not sargable.

Your own test demonstrates this well.

An exception would be if you created a computed column with the COALESCE expression and indexed that.

CREATE TABLE Test
(
    ID int primary key clustered,
    Mod6 int null,
    Foo AS COALESCE(Mod6, 0),
    INDEX IX_Mod6 NONCLUSTERED (Mod6),
    INDEX IX2_Mod6 NONCLUSTERED (Foo),
);

You could end up with a seek on that

SELECT Mod6
FROM Test WITH (INDEX = IX2_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;

ISNULL is marginally more sargable in that if it is totally redundant it can be optimised out and not prevent a seek.

i.e. if the column Mod6 is defined as NOT NULL then the following can produce a seek.

SELECT Mod6
FROM Test
WHERE ISNULL(Mod6, 0) = 0;

But this of course doesn't supply any benefit over just doing

WHERE Mod6 = 0


Return all possible combinations for each group I have got a table looks like this one here shows the student ID and all classes this student took:

+-------------+
| ID | Class  |
+-------------+
| 01 |   A    |
| 01 |   B    |
| 01 |   C    |
| 02 |   A    |
| 02 |   C    |
| 02 |   D    |
| 03 |   A    |
| 03 |   B    |
+-------------+

And I would like to convert this table to the one below in order to see all possible combinations of classes for each student.

+----+---------+---------+
| ID | Class 1 | Class 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | A       | C       |
| 01 | B       | C       |
| 02 | A       | C       |
| 02 | A       | D       |
| 02 | C       | D       |
| 03 | A       | B       |
+----+---------+---------+

Does anyone know how to get this?

if your dataset is small, something like this could be suitable:

select class1.id ,class1.class class1,class2.class class2 from
t1 class1,
t1 class2
where
class1.id = class2.id and
class1.class < class2.class
order by id,class1,class2


Two rows having null values merge into one row without null values I have fetched the record from table like this:

Name                 Opp                     Bid                  Pro
----------------------------------------------------------------------
Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      NULL                 NULL
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL
Pragnya Sonal         NULL                   2                    NULL
Pragnya Sonal         NULL                   NULL                 1

But I want to reorder this table in this format:

Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      2                    1
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL

Can any one help me to solve this, thank you very much in advance

You can use grouping to get only one row per Name. If more than one row for the same Name and column have values you have to decide what to do with those values: do you want to see minimum, maximum, sum, average etc? So for example:

SELECT Name, min(Opp), min(Bid), min(Pro) FROM your_table GROUP BY Name


How does the MongoDB query optimizer evaluate candidate plans? The MongoDB query optimizer processes queries and chooses the most
  efficient query plan for a query given the available indexes

This is an excerpt from MongoDB documentation.
I am looking for more information about how the query optimizer evaluates candidate plans.

Does the query optimizer use collections statistics ? Indeed, we can
think that a COLSCAN has not the same cost for a collection with
1000 docs or a collection with 100 millions docs.
When we run explain("allPlansExecution"), we get a lot of information about each evaluated plans. But, among following, what are the selection criteria in order of importance to determine the winning plan ? totalKeysExamined,   totalDocsExamined,   executionTimeMillis,   nReturned,   memUsage,  other... ?

Does the query optimizer use collections statistics ?

The query optimizer does not (as at MongoDB 4.0) use collection statistics.

Plan evaluation is based on comparing candidate plans for a given query shape to see which returns the first batch of results (by default 101 documents) with the least amount of overall "work". The works score is a proxy for different effort involved in query stages (index key comparisons, fetching documents, etc). If multiple plans perform identical work during evaluation, there are some small tie-breaking bonuses that can help choose a plan to cache (for example, if a plan is a covered query or does not require an in-memory sort).

The winning plan will be cached for future queries matching the same query shape, but will be re-evaluated if performance declines over time or there are other relevant changes (for example, indexes are added/removed). See Query Plans in the MongoDB documentation for a general overview.

For best outcomes on common queries you generally want to have an ideal index for your query shape rather than several competing candidate indexes. An ideal index would include the fields in your query and support using the index to sort query results (if you have sort criteria). For a worked example, the Optimizing MongoDB Compound Indexes blog post is a helpful read (note: this uses explain output from an older version of MongoDB, but the general concepts are still applicable).

If adding an ideal index isn't possible and the query optimizer isn't able to deterministically chose the best index given your data, you could consider using an index hint to override the query optimization process and force a specific index to be used. Index hints should be used sparingly as they can force suboptimal choices and will ignore any future indexes added that might be more suitable.

  When we run explain("allPlansExecution"), we get a lot of information about each evaluated plans. But, among following, what are the selection criteria in order of importance to determine the winning plan ? totalKeysExamined, totalDocsExamined, executionTimeMillis, nReturned, memUsage, other... ?

From a query scoring perspective, the  works value for each query stage is the key indicator. Metrics like totalKeysExamined and totalDocsExamined are useful for a quick comparison of efficiency for the number of results returned (nReturned). Ideally the ratio of totalKeysExamined to nReturned would be 1:1 (a single index lookup for every result), but some complex queries will necessarily have to examine more values. Other execution metrics will vary based on the query stage, but these can provide insight into calculation of the works value for that stage. For a more detailed walkthrough, I recommend the Reading the .explain() Output presentation from Charlie Swanson.

If you want to better understand the execution stats for a specific query, I suggest posting a new question on DBA StackExchange with your specific MongoDB server version and output of explain("allPlansExecution"). You may also find it helpful to investigate using a UI tool like MongoDB Compass, which includes a Visual Tree of explain output which is easier to explore than raw JSON.


Is a query inside mysql IF clause allowed? DELIMITER $$

CREATE PROCEDURE `InsertUser`(IN passwd VARCHAR(100), IN userType VARCHAR(20), IN mobileNo VARCHAR(20), IN refId BIGINT(20), IN companyName VARCHAR(100), IN companyMail VARCHAR(200), IN status CHAR(1)  )
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE c VARCHAR(20);
Select COUNT(*) from `user` into c where `mobile_no` = mobileNo;

IF c > 0
THEN

    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Mobile Number Exists';
ELSE

    INSERT INTO `user`
        (
        `password` ,
    `user_type` ,
        `mobile_no` ,
        `reference_id` ,
        `company_name` ,
        `company_mail_id` ,
        `status`
        )

    VALUES
        (
        pass ,
        usertype ,
        mobileno ,
        refid ,
        companyName ,
        companyMail ,
        status
        );
END;
END$$

DELIMITER ;

Can anyone point out the error? It might be something very small, but I've tried a lot, and I'm just not able to make this run.
Documentation
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where mobile_no = mobileNo;
IF c > 0
THEN

    SIGNAL SQLSTATE '' at line 7

You have a syntax error

This is not the right SQL for getting the count

Select COUNT(*) from `user` into c where `mobile_no` = mobileNo;

You do not put into c after the from clause. It goes before it.

Therefore, this is correct SQL syntax

Select COUNT(*) into c from `user` where `mobile_no` = mobileNo;

Give it a Try !!!


mongo2.6 local file size on secondary is much larger than primary I set up a new standalone mongo2.6 and copy one folder(database) from another machine to its data path. (cause I enabled directoryperdb)

Then I set up another machine to make them as a replica set.
After rs.initiate and data sync finished, I found the local file size on primary and secondary are:

primary: 232G
secondary: 316G

I found similar question here
https://stackoverflow.com/questions/14843229/mongodb-replica-set-disk-size-difference-in-primary-and-secondary-nodes

But as above answer, the primary should use more disk space than the secondary?

I need to migrate about 25T data from the old mongo 2.6 cluster.
If every database local file size take so more disk space in the secondary, then I need to take this as a spec consideration in the new bought machine :(

Your secondary node have more (free) disk space than primary! If you don't specify your oplog size in config file (or command line parameter), mongod will take 5% of free disk space (at dbpath) for opLog.


Cannot run Sql Server Agent jobs with PowerShell script The problem:
I have a customer that cannot run Sql Server Agent jobs with PowerShell script. It fails with this message:

Message
Executed as user: xxNC\SQLService. Unhandled Exception:
System.NullReferenceException: Object reference not set to an instance of an object.
at System.Environment.GetEnvironmentVariable(String variable, EnvironmentVariableTarget target)     at
System.Management.Automation.ModuleIntrinsics.GetExpandedEnvironmentVariable(String name, EnvironmentVariableTarget target)     at
System.Management.Automation.ModuleIntrinsics.SetModulePath()     at
System.Management.Automation.ExecutionContext.InitializeCommon(AutomationEngine engine, PSHost hostInterface)     at
System.Management.Automation.AutomationEngine..ctor(PSHost hostInterface, RunspaceConfiguration runspaceConfiguration, InitialSessionState iss)     at
System.Management.Automation.Runspaces.LocalRunspace.DoOpenHelper()     at
System.Management.Automation.Runspaces.RunspaceBase.CoreOpen(Boolean syncCall)
at Microsoft.SqlServer.Management.PowerShell .CustomRunspaceConfiguration.HandleAgentJob(RunspaceConfiguration config)     at
Microsoft.SqlServer.Management.PowerShell .CustomRunspaceConfiguration.Main(String[] args).  Process Exit Code -1073741819.  The step failed.

As I see it, it not about the powershell script itself. The problem is to execute a PowerShell script at all.

To test I have created a new job with some simple PowerShell, that I have testet on another Windows Server 2008 (Windows Server 2008 R2 Enterprise, with powershell 3.0), where it works. I can also run it from a PowerShell command on the problematic server. But when I create a Sql Server Agent job and run it, it is fails with the same message. So to me it seems that there is a problem with how PowerShell is configured compared to SQL Server…

Systeminfo:

Sql Server 2008 (SQL Server 10.50.2500)
Windows Server 2008 R2 Standard

I have checked the following:

1)  From PowerShell command line (and SQL Server PowerShell command, launched from sql management studio):
$PSVersionTable:

PS C:\Users\xxx> $PSVersionTable
Name                            Value
----                            -----
CLRVersion                      2.0.50727.5485
BuildVersion                    6.1.7601.17514
PSVersion                       2.0
WSManStackVersion               2.0
PSCompatibleVersions            {1.0, 2.0}
SerializationVersion            1.1.0.1
PSRemotingProtocolVersion   2.1

2)  Also this seems fine:
$Get-ExecutionPolicy  RemoteSigned

3)  If I run directly in SSMS a command like this:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

I also get an error:

“The shell cannot be started. A failure occurred during initialization:
Object reference not set to an instance of an object.”
There is an error in the Event log, with this message: ‘ErrorMessage=Object reference not set to an instance of an object.’

So it must be the same error – I just do not have stacktrace from event log.

Now I am running out of ideas of where to look for the error. So any suggestions on where to look are very welcomed.
The only thing should be that PowerShell is only version 2.0. But I assume this is not that problem?
Maybe Sql Server Agent is not allowed to run powershell scripts? How do I check for that?
Or it could be a security issue? The owner of the job is 'sa'. While for the jobstep Run as entry it is "SQL Server Agent Service Account".

I have found very few posts on similar issue. Fx. Execute Remote Powershell script from SQL Agent but it does not solve my issue.

(I am mainly a .NET Developer so Sql Server and Powershell is not my strongest side).

It sounds like you have a good way to reproduce the problem:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

The next step I'd take is to do some tracing to determine where the error is happening. Since you aren't getting a stack trace in your repro, I'd start by running Process Monitor.

I'd want to start by filtering on processes named Powershell.exe, and then adjust the filters based on the data captured. My suspicion is that this will turn out to be a problem with the PowerShell profile for the Windows service account, but hopefully with Process Monitor you'll get an idea of what error is leading up to the generic -1073741819, which is defined as being a default or unknown error in various Windows libraries.

Significant edit: According to the Visual Studio ERRLOOK feature, the error message of -1073741819 shown in the lower-right corner of the stack trace in your post translates to 0xC0000005. This is defined as STATUS_ACCESS_VIOLATION in ntstatus.h. It could be that some component is not correctly installed, or something is interfering with it (e.g. antivirus). If an access violation is occurring, there might also be further details in Windows' application log.

I'd suggest testing running Process Monitor in a non-production environment first until you feel familiar with it, and selecting the 'Drop Filtered Events' option to avoid consuming large amounts of memory from buffering all events.