Permissions required to alter a database file Totally at the beginning in administering a database.

Using EMS SQL Manager ... latest version.  How do I over come below

ALTER DATABASE joneslocker2
MODIFY FILE (
    NAME = joneslocker2,
    MAXSIZE = UNLIMITED )
GO

which results in an error:

  User does not have permission to alter database 'joneslocker2', the
  database does not exist, or the database is not in a state that allows
  access checks.

I don't have a clue what to set next.

You need to GRANT ALTER DATABASE permission to the user.

GRANT ALTER ON DATABASE:: joneslocker2 TO username

Below script will help you to find what permissions are assigned at the database level :

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

Check my answer here for an example on why you are getting the error.

Refer to : Database Engine Permission Basics


How can I find all rows that have at least one element in an array matching a pattern? Consider the following example:

CREATE TEMPORARY TABLE testing (
    names TEXT[]
);

INSERT INTO testing VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}')
;

SELECT * FROM testing WHERE 'toplevel1.%' ILIKE ANY(names);

I want to write a select where "any" element of an array matches a pattern. But I can't figure out a way to do it. In the example I've given, using the ILIKE operator can't work because it checks if the RHS matches the LHS. For this case I would need a similar operator but which checks if the LHS matches the RHS.

update: I'm currently fiddling around with something like this without much success:

SELECT * FROM testing WHERE names ILIKE ANY (ARRAY(SELECT unnest(names) || '%'));

You can use the official ltree extension.

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TEMP TABLE testing (
    names ltree[]
);

INSERT INTO testing (names)
VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}');

SELECT * FROM testing WHERE names ~ 'toplevel1.*';

It is also doable in "plain" postgresql:

SELECT * FROM testing AS t
WHERE (
    SELECT true
    FROM unnest(t.names) AS n
    WHERE n ILIKE 'toplevel1.%'
    LIMIT 1
);

The ltree extension is not very widely used, and obviously it has some limitations (including label length & character set), but it's stable and can achieve better performance than unnest-based version. I would really give it a try.


SQL Server 2008 collation arabic We are currently having a problem loading arabic into a SQL Server 2008 database.

We want to load
بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ

but in the database it changes to

بِسْمِ اللَّهِ الرَّحْمَ?نِ الرَّحِيمِ

with a ? added,

We have set the collation Arabic_CI_AI_KS_WS and SQL_Latin1_General_CP1256_CS_AS

and neither of these work..... any suggestions?

From a quick test this end none of the built in collations can represent all characters in that string in a single byte code page so you would need to use a unicode datatype.

SET NOCOUNT ON;

DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ'

--The below without the problematic character returns 7 results
--DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَنِ الرَّحِيمِ'

CREATE TABLE #Results
(
name sysname
)

CREATE TABLE #Chars(
C NCHAR(1)
)
INSERT INTO #Chars
SELECT DISTINCT SUBSTRING(@StringToTest,number,1)
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND LEN(@StringToTest)

CREATE TABLE #Collations
(
     code TINYINT PRIMARY KEY
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM E08

DECLARE @AlterScript NVARCHAR(MAX) = ''

SELECT @AlterScript = @AlterScript + '
RAISERROR(''Processing' + name + ''',0,1) WITH NOWAIT;
ALTER TABLE #Collations ADD ' + name + ' CHAR(1) COLLATE ' + name + ';
EXEC(''UPDATE #Collations SET ' + name + '=CAST(code AS BINARY(1))'');
EXEC(''UPDATE #Collations SET ' + name + '=NULL WHERE ASCII(' + name + ') <> code'');
EXEC(''
IF NOT EXISTS(
SELECT *
FROM #Chars
WHERE NOT EXISTS(SELECT * FROM #Collations WHERE ' + name + ' = C COLLATE ' + name + '))
INSERT INTO #Results
SELECT ''''' + name + '''''
'');
'
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%CS_AS'
       AND name NOT IN    /*Unicode Only Collations*/
                        ( 'Assamese_100_CS_AS', 'Bengali_100_CS_AS',
                         'Divehi_90_CS_AS', 'Divehi_100_CS_AS' ,
                         'Indic_General_90_CS_AS', 'Indic_General_100_CS_AS',
                             'Khmer_100_CS_AS', 'Lao_100_CS_AS',
                         'Maltese_100_CS_AS', 'Maori_100_CS_AS',
                         'Nepali_100_CS_AS', 'Pashto_100_CS_AS',
                         'Syriac_90_CS_AS', 'Syriac_100_CS_AS',
                         'Tibetan_100_CS_AS' )

EXEC (@AlterScript)

SELECT * FROM #Results

DROP TABLE #Collations
DROP TABLE #Results
DROP TABLE #Chars


What are database statistics, and how can I benefit from them? I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?

Statistics simply are a form of dynamic metadata that assists the query optimizer in making better decisions. For example, if there are only a dozen rows in a table, then there's no point going to an index to do a lookup; you will always be better off doing a full table scan. But if that same table grows to a million rows, then you will probably be better off using the index. But if you query that table on a column that only has very few unique values (e.g. it might be a "sex" column containing only "M" or "F"), well actually a FTS might be better that that case because you would need to retrieve the block anyway to build the result set. Now say your table is 99% "M" and only 1% "F", we should FTS in one case or use an index in the other.  Same table, same query, potentially four different query plans depending on the contents of the table. These kinds of things are the "statistics" and they are individual to each database - even two databases with identical table and index structure will have different statistics.

In summary, in a modern database engine there are two kinds of query optimization: rewriting the SQL (rule-based optimization, like a compiler rewrites your C to make it more efficient) and choosing the right path the the data (cost-based optimization, like a JIT compiler identifying hotspots at runtime). You only need to worry about this if you spot the query optimizer doing something obviously wrong (e.g. choosing FTS when you know an index would be better).


Why does INSERT SELECT into an unrelated table block when SELECT doesn't? Mysql 5.6 Simplified, I have a table 'app' with a primary auto increment field app_id, and one field created_at datetime not null.

I have another table, 'app_subset', with a single key 'app_id'.

If I run a query like this:

SELECT app_id FROM app;

then while that is running, I execute:

INSERT INTO app (created_at) VALUES (NOW());

The insert executes immediately, not waiting for the select to finish.

If, however, I run a query like this:

INSERT IGNORE INTO app_subset SELECT app_id FROM app;

and then run the same insert into app:

INSERT INTO app (created_at) VALUES (NOW());

now the INSERT INTO app query blocks, waiting for the INSERT IGNORE to finish.

I'm trying to understand why this is the case.  I understand that the results of my select will be changed by the INSERT INTO app, but it seems like if the original SELECT doesn't block, then an INSERT using the results of that select shouldn't block either.  I'd like to be able to set things up so this is the case in some fashion, or at least fully understand why it is happening so I can anticipate similar issues in the future.

from Rolando's answer below, it sounds like READ UNCOMMITED should work, and indeed when I execute:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The INSERT IGNORE .. SELECT no longer causes my INSERT INTO app to block.

I am curious if there is a way to do it as a single line (something like:)

INSERT IGNORE INTO app_subset SELECT app_id FROM app LOCK IN NONSHARING MODE;

Actually, INSERT INTO .. SELECT does lock the SELECT table

I wrote about this before

Apr 25, 2016 : MySQL INSERT ... SELECT Performance
Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ... SELECT (More Details)


MySQL 8 roles - I'm probably missing something obvious I'm trying to use roles in MySQL 8, and failing. I have read the documentation and as far as I can tell, I'm doing it right. Since this is a 'hello world' level attempt, I assume I'm missing something very obvious, but I can't figure it out. Any help will be highly appreciated.

Here is what I'm trying to do.

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'reader_password';

CREATE ROLE 'read_all';

GRANT SELECT ON MySchema.* TO 'read_all';

GRANT 'read_all' TO 'reader'@'localhost';

The above script is executed with root user, and all statements succeed.

Then, I open a new connection using the 'reader' user credentials, and connect successfully. However, any attempt to SELECT from any table in MySchema is denied with the error message:

SELECT * FROM MySchema.SomeTable;

SQL Error [1142] [42000]: SELECT command denied to user 'reader'@'localhost' for table 'SomeTable'

I expect this SELECT to succeed as the role has SELECT permissions, and the reader user has been granted the role. What am I missing?

Have a wonderful weekend!

UPDATE: Following Rick James' comment, here are the relevant SHOWs:

On the root connection (with active schema context 'MySchema'):

SHOW CREATE USER 'reader'@'localhost';
--------------------------------------
CREATE USER 'reader'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*96B291A4F8FC2B6C453E4C54AD2080751B0D4712' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT

SHOW CREATE USER 'read_all';
----------------------------
CREATE USER 'read_all'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

SHOW GRANTS FOR 'reader'@'localhost';
-------------------------------------
GRANT USAGE ON *.* TO `reader`@`localhost`
GRANT `read_all`@`%` TO `reader`@`localhost`

SHOW GRANTS FOR 'read_all';
----------------------------
GRANT USAGE ON *.* TO `read_all`@`%`
GRANT SELECT ON `MySchema`.* TO `read_all`@`%

And on the reader connection:

SHOW GRANTS;
------------
GRANT USAGE ON *.* TO `reader`@`localhost`
GRANT `read_all`@`%` TO `reader`@`localhost`

So after diving deeper into the documentation, and specifically the "Using Roles" page, I have found that:
"By default, granting a role to an account or naming it in the mandatory_roles system variable value does not automatically cause the role to become active within account sessions... To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE."


Should developers be able to query production databases? Should developers be given permission to query (SELECT / read only) production databases?  The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production instance.

One of the test instances is a copy of production restored from a production backup once a week, so there aren't any problems with developers actually seeing the data.

What good reasons are there for not allowing developers to query production (except for simply not wanting them to have access to read sensitive data)?

It really depends on whether the developer has any support responsibilities.  If they are on the hook for third line support then they will probably need to look at the production database to do this.

Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there.

For most development purposes, mirrors or snapshots of the production database will be adequate, and probably better than the live production database.  If you are doing anything involving integration then you will want stable database environments where you can control what's in them.  Anything involving reconciliation will also need the ability to look at a controlled point in time.

If the problem is that you don't have production mirror environments or any means to put a copy of production data somewhere for your developers then this is a somewhat different question.  In that case your developers really need at least one mirror environment.  If you can't see what the problem is in the data then it's kind of hard to troubleshoot it.


How to get a group where the count is zero? I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero.
For this I've tried this query:
Create table Streets(
  ID int IDENTITY  primary key,
  Name varchar(100)
);

create table users(
  ID int IDENTITY  primary key,
  Username varchar(100),
  StreetID int references Streets(id)
);

insert into streets values ('1st street'), ('2nd street'), ('3rd street'),
                           ('4th street'), ('5th street');
insert into users values ('Pol', 1), ('Doortje', 1), ('Marc', 2), ('Bieke', 2),
                         ('Paulien', 2), ('Fernand', 2), ('Pascal', 2), ('Boma', 3),
                         ('Goedele', 3), ('Xavier', 4);

select s.name as street, count(s.name) as count
from users u inner join streets s on u.streetid = s.id
group by s.name

And it gives me this output:

ID
street
count

1
1st street
2

2
2nd street
5

3
3rd street
2

4
4th street
1

The problem is that the 5th street, where no user lives, doesn't appear on the result. Could I do this with SQL server? Here you've got a fiddle
Update: If I do right join, I've got this result:

ID
street
count

1
1st street
2

2
2nd street
5

3
3rd street
2

4
4th street
1

5
5th street
1

See this fiddle.

The reason your query did not work as intended:

Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not produce any entry for that.

Outer join (right or left) will give the result of inner join and in addition all non-qualifying records from the left or right table depending on the type (left or right) of  outer join.

In this case, I put Street on the left of the join and used left outer join as you wanted all streets (even count is zero) in your result set.

Change your select query to this.

SELECT S.Name AS Street,
       Count(U.Username) AS COUNT
FROM Streets S
LEFT OUTER JOIN Users U ON U.Streetid = S.Id
GROUP BY S.Name

Result


Improve query performance with index I am looking to create an index for a database to acheive better performance.
I am using this query:

SELECT DISTINCT t1.* FROM current_order
AS t1 LEFT JOIN
receipt AS t2 USING (paper_id,subscriber_id)
WHERE t2.id NOT IN (SELECT id
FROM receipt WHERE paid_till_date > Now())
UNION
SELECT current_order.* FROM receipt RIGHT JOIN
current_order USING (paper_id, subscriber_id)
Where receipt.id IS NULL ORDER BY
subscriber_id, paper_id

Tables are:

 PAPER
    id
    name

 SUBSCRIBER
    id
    name
    address
    suburb
    state
    postcode
    round_id

 CURRENT ORDER
    paper_id
    subscriber_id

 ROUND
    id
    name
    paperboy

 RECEIPT
    id
    receipt_date
    paid_till_date
    paper_id
    subscriber_id

I understand that primary keys are already indexed, and that i should use an index where WHERE clauses and ORDER BY clauses are used frequently. So i'm assuming that the subscribers Name would be a good one to index?
And also the receipt date?

Any tips appreciated

The original query

SELECT DISTINCT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE t2.id NOT IN (
    SELECT id
      FROM receipt
      WHERE paid_till_date > Now()
    )
UNION
  SELECT current_order.*
    FROM receipt
      RIGHT JOIN current_order USING (paper_id, subscriber_id)
    WHERE receipt.id IS NULL
    ORDER BY subscriber_id, paper_id

is more complex than necessary.

The base tables are

CURRENT ORDER
  paper_id
  subscriber_id

and

RECEIPT
  id
  receipt_date
  paid_till_date
  paper_id
  subscriber_id

The OP stated that receipt.id is a primary key and I think that (paper_id,subscriber_id) is the primary key of current order.

Both queries

SELECT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE t2.id NOT IN (
    SELECT id
      FROM receipt
      WHERE paid_till_date > Now()
    )

and

SELECT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE paid_till_date <= Now()
    or paid_till_date  is NULL

are equivalent: The resultset f both queries contains exactly the following tuples:

all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date <= Now()
all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date is NULL
all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore paid_till_date  is NULL)

Therefore the queries are equivalent. And they are also equivalent if an DISTINCT is added to the select clause.

The resultset of the query

  SELECT current_order.*
    FROM receipt
      RIGHT JOIN current_order USING (paper_id, subscriber_id)
    WHERE receipt.id IS NULL
    ORDER BY subscriber_id, paper_id

contains the following tuples

all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore receipt.id is NULL)

So the original query can be changed to the simpler query

SELECT DISTINCT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE paid_till_date <= Now()
    or paid_till_date  is NULL

Perhaps an index on (paper_id,subscriber_id,paid_till_date) will be usefull. The query uses only columns found in the index (receipt.id is not used anymore). An Index on (paper_id,subscriber_id) of "current order" already exists because this is the primary key.


Can't import .sql file I have a 1.4GB .gz backup file(which is 8GB unzipped) I have to import to the server but it isnt working whatever method I have tried.

1st I tried Workbench, after 2 days and still no progress I canceled, and after some research I found out console was the better option with big files.

On Console I tried importing directly from compressed .gz however that gave me ascii\0 error so I unzipped and tried uploading the file directly. This time it started very fast with some occasional spikes (like some rows gets 0.8 secs but the next row 11.76 secs). But this time it got stuck at the last second. I tried this step with 4 different OS and hardware setups.(Vista,7 server 08 and ubuntu also I tried with dedicated hardware also)

Since the file is big I cant just open it from notepad++ and I read that might corrupt the file also.

I cant get a new backup as the data came from outside source.

The database has 15 coloumns, myISAM engine.

As I can't seem to find anything else I would like some help as to what might be causing this issue.

Thank you very much.

As I said in my comments above your issue comes from a small tmp dir. There are many answers on stackoverflow concerning this. Here is 1

https://stackoverflow.com/a/1067521/716691

And here's how to change mysql tmp dir temporarily without editing my.cnf. Make sure you create a tmp dir somewhere with enough available space.

https://stackoverflow.com/a/21853110/716691


Pivoting some row data into columns (SQL Server 2008 R2) Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another.

Full-Disclosure: I did not design this disaster of an application

We have an application that stores email addresses and contacts on different rows in the same table. The problem is that the primary email for that user is simply the one that comes back first when the rows are sorted by a unique id field.

Dumb_ID   Col1        Col2
1A!       James Q.
2B$       1A!         JamesQ@Contoso.com
CD#       1A!         JamesQ@OldButRelevantContoso.Ru

I need to select the data like so:

Dumb_ID    Col1        Col2                  Col3
1A!        James Q.    JamesQ@Contoso.com    JamesQ@OldButRelevantContoso.Ru

There are a number of other columns being selected from 1A! but I cant think of an effective way to select these into a column based on their row order when sorted by Dumb_ID

Please help DBA's, you're my only hope to escape the madness of this application.

Thanks!

EDIT:
After @Daniel gave me an elegant method using a subquery with a partitioned row count this was my end result. Posted for posterity.

LEFT OUTER JOIN
  (SELECT LINKACCT
        , ADDRESS2
        ,MAX(CASE WHEN subQ.ORDINAL = 1 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL1
        ,MAX(CASE WHEN subQ.ORDINAL = 2 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL2
        ,MAX(CASE WHEN subQ.ORDINAL = 3 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL3
  FROM
  (SELECT recid
    , CONTACT
    , CONTSUPREF
    , LINKACCT
    , ADDRESS1
    , ADDRESS2
    ,ROW_NUMBER() OVER (PARTITION BY LINKACCT ORDER BY recid) AS ORDINAL
    FROM CONTSUPP CSEI WHERE CSEI.RECTYPE = 'P') subQ GROUP BY LINKACCT, ADDRESS2) CSEI ON CS.recid = CSEI.LINKACCT AND CS.CONTACT = CSEI.ADDRESS2

Ok, here's my two pence:

SELECT header.Col1,
       MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1,
       MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2,
    -- ... and so on..
       MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99

FROM (
      --- For each Col1, enumerate all the rows and return Col2 as well:
      SELECT Col1, Col2,
             ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Dumb_ID) AS ordinal
      FROM someTable
      ) AS details

--- join this to the header:
INNER JOIN someTable AS header ON details.Col1=header.Dumb_ID

--- One row per header:
GROUP BY header.Col1;

Admittedly, not the prettiest code I've ever written. How it works:

The "header" in this case is the row where Dumb_ID='1A!'.
"details" contains one row for each record that can be joined to the header ON header.Dumb_ID=details.Col1, in this example rows where Col1='1A!'.
The "details" subquery is given an ordinal, a ROW_NUMBER(), that enumerates each row (partitioned by Col1).
"header" and "details" are joined and everything is output by aggregating the result, so we get a single record for each "header"
Each "detail" is assigned to its own column using a CASE and the ordinal number. We need to use MAX() (or any other aggregate function, really) here so it'll work with the aggregate.

I hope I've understood your question correctly. Also, note that this is ad-hoc coded, without testing. Let me know how it works out.


Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock? Can two or more instances of the following deadlock with one another?

START TRANSACTION
SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE
:
COMMIT

Points to keep in mind:

mycolumn is indexed
This is mariaDB/innoDB
There is only one table involved (mytable)

Example A:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Thread 2: (note that IN-list is in opposite order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (200,100) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT in mariaDB/innoDB?

Example B:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Thread 2: (note that IN-list is in same order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT  in mariaDB/innoDB?

UPDATE: A traditional way of avoiding deadlocks is to be sure to acquire locks in the same order, so that you can't have Thread 1 owning lock A and waiting on lock B while Thread 2 owns lock B and is waiting on lock A. However, I've seen several posts suggesting that even getting a single lock can't be considered atomic, because there are asynchronous processes going on in the engine that could still result in deadlock (and as a corollary to this, advising developers to always expect deadlocks and recover from them by retrying the transaction). I'm really asking whether deadlock can be eliminated by using proper coding practices in innoDB.)

This thread is discussing one example of deadlocks.  There are dozens, maybe hundreds, of ways to get a deadlock.

Sure, sorting the ids might prevent the IN(100,200) case, but what about all the rest?  Sure, go ahead and sort the ids.  And be sure to have suitable indexes.  And so on.  These might at least diminish the frequency of deadlocks.

Another example of a deadlock cause:  Lack of an index that leads to a table scan instead of an index lookup.  I think this can lead to locking all rows in the table, thereby both slowing things down and by having more locks, thereby begging for a deadlock.

I'll tell you what I have said many a time:  Deadlocks happen; live with it.  Be ready to restart the transaction from the BEGIN.

If you ever get a second deadlock, you should probably abort the program and spend some quality time figuring out how to speed up the entire transaction.  That is the best way to minimize deadlocks.

(OK, I'll admit that I don't know the definitive answer about IN().  Long ago (ver 4.1?), I thought I had proof that sorting was beneficial.  Later I heard that MySQL sorted the IN list, thereby killing my theory.  Or is all this hearsay evidence?)


Deadlock between two non-related tables I am facing with a deadlock between two tables Table1 and Table2.

The deadlock occurs when an sp (sp2) delete from table2 and other sp (sp1) try to update table1.

In table2 the column by which it is deleted is the primary key of table1, however due to a bad design the Foreign Key was never added, that is, that table2 has no references to table1, however when deleting from the table2 generates an exclusive lock of table1. So how the engine knows the reference without FK?

CREATE TABLE [dbo].[Table2] (
    [IdTable1]   INT          NOT NULL,
    [IdTable3] INT          NOT NULL,
    [Sent]         BIT          CONSTRAINT [DF_Table2_Sent] DEFAULT ((0)) NOT NULL,
    [Order]        VARCHAR (50) CONSTRAINT [DF_Table2_NewID] DEFAULT (newid()) NOT NULL,
    [ToExclude]    BIT          CONSTRAINT [DF_Table2_ToExclude] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ([IdTable1] ASC, [IdTable3] ASC) WITH (FILLFACTOR = 100) ON [FG2]
);

GO
CREATE NONCLUSTERED INDEX [IX_Table2_IdTable1_Sent]
    ON [dbo].[Table2]([IdTable1] ASC, [Sent] ASC, [Order] ASC)
    INCLUDE([IdTable3]) WITH (FILLFACTOR = 100)
    ON [FG2];

Here is the deadlock detail. https://justpaste.it/700ik

Also the tables are on different filegroups (different files)

Why is this?

Also, if I had the FK, why should need check the PK in table1 if I am just deleting from table2?

EDIT
Thanks Josh for the answer.

Now I have SP2 as

CREATE PROCEDURE SP2 @value
AS
IF @value = 1
    UPDATE dbo.Table1.....
else
BEGIN
    EXEC SP3 --This SP update Table1
    DELETE FROM Table2...
END

Now to avoid keep the locking on Table 1 could I add a transaction as follow to release the lock on table 1 on SP3 completition?

CREATE PROCEDURE SP2 @value
AS
IF @value = 1
    UPDATE dbo.Table1.....
else
BEGIN
    BEGIN TRAN T1
       EXEC SP3 --This SP update Table1
    COMMIT TRAN T1
    DELETE FROM Table2...
END

Thanks.

Looking at a subset of the deadlock information:

This shows that session 342 is currently executing line 47 of the stored procedure dbo.SP2, which is a DELETE statement on dbo.Table2.

Somewhere prior to that in the procedure, this session has already acquired an eXclusive (key) lock on dbo.Table1.  Based on the non-zero "log used" value, it seems like this procedure does some writes to dbo.Table1, thus acquiring the X lock and creating the potential for deadlocks.

The takeaway here is that the deadlock XML is only showing you the currently executing statement (Erik Darling has some great demos of this and other confusing things about deadlock graphs on his blog).  It appears that there are multiple statements being executed in a transaction in this stored procedure.

Another possibility is that there is a trigger on one of more of these tables that's causing more locks to be taken than what is obvious from the T-SQL statements themselves.

  Also, if I had the FK, why should need check the PK in table1 if I am just deleting from table2?

You're right, even if the foreign key existed on Table2, locks on Table1 would not be required to delete rows from Table2.  Locks would be required if the FK was defined "backwards" and cascading deletes were enabled.


Concerns regarding changing virtual NIC on SQL Server 2008 R2 / Windows Server 2008 R2 I have SQL Server 2008 R2 running on Windows Server 2008 R2 in a virtual environment (ESXI 5.5). Client has been advised to switch the existing E1000 adapter with a VMXNET 3 adapter because they were told it would perform better.

Anyhow, I've done this several times on member servers as well as domain controllers. As I understand it, SQL Server is more concerned with the network layer so I don't believe this should be an issue. I am not a SQL guru so I figured I would ask here to be safe.

Assuming SQL Server is configured to use a dynamic TCP port via SQL Server Configuration Manager, and the SQL Server Browser service is running and not blocked by a firewall, replacing the network adapter should not change connectivity significantly, within the bounds of the network adapter operational characteristics (i.e. its speed, etc).

If, however, you're using a specific TCP port for SQL Server, you'll want to ensure you document that port number before replacing the network adapter.  Once you install the replacement adapter, and remove the existing adapter, set the new adapter to use the same port via SQL Server Configuration Manager.

This is a snip of the configuration screen:


ssis ado.net destination has failed to acquire the connection I am Transferring data from sql server source db to sql server destination db (cross db).
but some times getting error "ssis ado.net destination has failed to acquire the connection.The connection may have been corrupted"

configure your connection as xml
set variable in connection
set computer name & logic [Admin]


Does SQL Server upgrade to table locks at total locks or session locks? I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so the read-only queries shouldn’t affect the lock count (?) in relation to the sequential insert.
My concern is that, even when limiting the maximum number of locks taken during the insert (eg. inserting in batches), it is possible for one (or more) of the OLTP updates to bypass this limit. If the lock count heuristic is per-session then it is less of potential issue.
Given the answer to the question in the title, then, what is the “best” way to prevent table lock escalation here?
My current approach/thought is to select a row count (eg. arbitrary of 1-4k) during the mass inserts to allow “some slack”, although this feels overall imprecise. While batches are essential a way to deal with replication and such, it would be nice to specify a batch size of 5k rows and move on. (To be fair, quick table locks aren’t really the issue: the intent of the question is more about finding the edge such that table lock escalation doesn’t happen.)
There has been DBA pushback on both 1) disabling row locks (to ensure page locks and thus reduce lock counts) and 2) disabling table lock escalation (with forced page locks to minimize worst-case). Are there any other relevant database properties to consider with respect to lock escalation? (Increasing the lock limit to say, 10k would then allow a much larger “slack” batch size.)
Using with PAGLOCK in the batch inserts results in page-row deadlocks with the current instead-of triggers and generally appears to be a pain to get correct. Although the instead-of triggers themselves are currently on the axing block for various technical complications. This hint also only increases the “slack”. I don’t suppose there is a NOINCRLOCKCOUNT hint that has been overlooked..

If the lock count heuristic is per-session then it is less of potential issue.

It is per-session.

Are there any other relevant database properties to consider with respect to lock escalation?

On the database, no, but you can disable lock escalation on the whole instance by enabling trace flag 1211.

what is the “best” way to prevent table lock escalation here?

Why would you want to?

Lock escalation serves a useful purpose in SQL Server by maximizing
the efficiency of queries that are otherwise slowed down by the
overhead of acquiring and releasing several thousands of locks. Lock
escalation also helps to minimize the required memory to keep track of
locks. The memory that SQL Server can dynamically allocate for lock
structures is finite, so if you disable lock escalation and the lock
memory grows large enough, attempts to allocate additional locks for
any query may fail

https://docs.microsoft.com/en-us/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation
That article also outlines an additional way to prevent lock escalation for a time on a table by running (from a seperate session) a query like:
BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN


Rank variable value in MySQL is always being set to null In a sample procedure I am trying to find the rank the of the cities according to the
    number of offices. I have a Location Table and an Office Table and a location can have
    multiple offices ( In my case I am assuming Boston has 3 offices and Bangalore has only
    2) offices and I want them to rank accordingly . I am using the below SP but it returns
    me the rank as 0 and 0 and also MySQL query browser is being crashed . I guess I am
    making some error here .

   BEGIN

   DECLARE rank INT DEFAULT 0;

   SELECT rank =rank+1 AS Rank,l.Location_Name,COUNT(*)
   AS Number_Of_Offices FROM employee_leaves_management_system.location l
   INNER JOIN
   employee_leaves_management_system.office o
   ON l.Location_ID = o.Office_Location_ID
   GROUP BY l.Location_Name
   ORDER BY Number_Of_Offices;

   END $$

A quick help will be much appreciated !!

= is the assignement operator only for SET statements,in your case is a comparison operator,for SELECT use rank := rank+1

Example

SET @var=1;
SELECT @var = 1 + 1; -> This compares @var which is one to 2 so it returns 0.

Result 0

SET @var=1;
SELECT @var := 1 + 1; -> This actually adds up.

Result 2


How to structure a model to properly and efficiently represent tree-like data on relational databases? Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical implications?

I'm assuming that the RDBMS has not special features to handling that other than regular SQL ANSI or common available features.

In doubt I'm always interested on MySQL and PostgreSQL and eventually SQLite.

I believe he is going for something like a binary tree.  I would just include three keys that are tied to the unique id of the same table, one for the left, one for the right child, and one for the parent.

i.e.- (very much pseudocode)

TABLE tree
int         id                  autoinc
varchar(16) data_you_care_about
int         parent_id
int         left_child_id
int         right_child_id

FOREIGN KEY parent_id = tree.id
FOREIGN KEY left_child_id = tree.id
FOREIGN KEY right_child_id = tree.id


Thanks @Akina for pointing me in the right direction: https://stackoverflow.com/questions/16732980/why-cardinality-value-in-mysql-indexes-dont-equal-distinct-count-for-column-val
Running ANALYZE TABLE my_table updated the cardinality to match index size.


Numerous failed authentication attempts in error log I'm having a hellacious time trying to figure out what is attempting to authenticate against a particular database server, running SQL Server 2008 R2 Standard Edition. The following is the error littered in my logs:

  Login failed for user 'Master80'. Reason: Password did not match that for the login provided.
  [CLIENT: Server IP Address]

The interesting thing is that this account is nowhere to be found in Active Directory or within the database server, not even specific databases. I've gone through every security folder and searched AD high and low, nothing, no remnants of this account.

As far as the "Server IP Address" part of the error, that changes between 3 or 4 CITRIX servers. So, the only thing we know for sure is that it coming from a specific handful of servers and maybe some application, but no one knows which application. Perusing the server and opening apps from the program menu or checking Add/Remove Programs netted nothing.

What is it that I can do to track this down? It was suggested to start a trace, but I'm not sure what to configure as tracing is kind of nebulous to me.

My initial thought was a logon trigger but, sadly, those only fire after a successful login.

The documentation for the Audit Login Failed Event Class says that failed login audits will include the application name, but I haven't observed that so far (I only tested failed logins from changing connections within Management Studio, so maybe it is exposed in other scenarios). Anyway, create this audit and audit specification:

USE master;
GO

CREATE SERVER AUDIT failed_logins
  TO FILE (FILEPATH = 'C:\temp')
GO

ALTER SERVER AUDIT failed_logins
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION failed_logins_spec
  FOR SERVER AUDIT failed_logins
  ADD (FAILED_LOGIN_GROUP)
  WITH (STATE=ON);
GO

Now you should be able to right-click the audit (in Object Explorer under Security > Audits)  and choose View Audit Logs. There may be more information there for you, that might help you narrow this down better, but I am not 100% certain. The application trying to connect would have to pass a valid application name, Citrix can't be blocking any of that information from passing through (or changing it on the way), etc.

Note that this may be a no-op depending on your version and edition, too. If so, your next option may be Extended Events, as Max pointed out below.

CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14
      --AND error_number = 18456 -- login failed - 2012+ only!
      AND state > 1 -- seems to always be a redundant state 1 event
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedLogins.xel',
    METADATAFILE = N'C:\temp\FailedLogins.xem'
  );
GO

ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO

You can write really ugly XQuery to parse the results, or get Jonathan Kehayias' SSMS add-in.

In 2012 you can just open a Watch Live Data window by right-clicking the session in Object Explorer: Management > Extended Events > Sessions and wait for it to happen again. You might get noise from other "real" failed logins, password typos, other severity 14 errors, etc. In 2012+ you will be able to bind error_number instead, but it is not valid on 2008. Anyway, here is what I saw in 2014 with the 18456 error filter:

And while I have no interest in going the extra length to craft the XQuery you need to see query results in a nice tabular format, I did verify that in 2008 the above session does record the application name in the .xel file target by opening it in Notepad (it ain't pretty).

If that doesn't work (e.g. the app name does not get populated for some reason), next would be some kind of network/packet sniffer, like Wireshark. You could also:

Look at Control Panel > Administrative Tools > Services to see if there are any services running that are set to start up using that account.
Look at the Event Logs on both the SQL Server machine and an offending Citrix machine for any other clues around the time of a login failure.
Change the hosts file on that machine to override the SQL Server DNS name to point elsewhere (e.g. to an IP that is unreachable), and see if anything breaks in a different way if the app or service can't connect to SQL Server at all. Who knows, it may actually log something or pop up a dialog in that case. This will only work, of course, if the app currently reaches the SQL Server by name resolution and not by IP - you may need to try both the Windows name and the FQDN. This should work to stop the login attempts at least, even if it doesn't yield any more clues about the source; if so, then at the very least this could be a permanent way to stop filling the SQL Server's logs with failed login attempts. If other apps on that machine do need to connect to SQL Server, you could have them connect by IP instead, or (probably more future-proof) you could set up a different alias and change their connection strings to use the new name.

I also have a pretty elaborate blog post detailing what the state means for all the 18456 errors.


Postgresql trigger to update a column in a table when another table gets inserted or updated Basically I have table_a and table_b. table_b is made with features from table_a and in common they share section_id column and status.

table_a's section_id is primary key so it's unique, but table_b can have multiple section_ids, but they all share same status

Users insert and update table_b and I want to capture the status changes back in table_a

CREATE TRIGGER table_b_aiu
AFTER INSERT OR UPDATE
ON table_b
FOR EACH ROW
WHEN (((new.status = 100) OR (new.status = 200)))
EXECUTE PROCEDURE table_b_aiu();

CREATE OR REPLACE FUNCTION table_b_aiu()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE table_a a
SET status = 100
FROM table_b b
WHERE (b.status = 100 or b.status = 200)
AND a.section_id = b.section_id;
    RETURN new;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;

The problem with this, is that instead of updating just the newly updated or inserted row, it updates the ENTIRE dataset, which is not intended. How to update only new rows? I've tried putting arguments on the triger but it doesn't accept new.section_id

By default, UPDATE table_a ... FROM table_b builds CROSS JOIN of all rows from both tables. So such UPDATE applies to all rows of table_a.

Solution is simple: add to WHERE condition

AND a.section_id = NEW.section_id

to limit rows of CROSS JOIN result to only just-now-updated rows.

NEW and OLD are a system record which keep values of all columns of row for which trigger function has been called.


Use of Trigger on table which can have 100 hits / second. Will it hamper performance? Currently in one of our projects we have the following situation.

The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. We are running SQL Server 2008 R2.

We need to implement a database trigger on a table which could have around 100 hits (for fetch and DML) per second.

The trigger will work on INSERT, UPDATE and DELETE.

I wanted to know if the system performance will suffer considerably due these triggers?

The trigger will perform addition of 2 numbers and store the result in a separate table.

Unless you've badly coded it, then no. It is probably less then 10 writes per second. Even very write heavy databases are over 90% reads and under 10% writes.

Consider though that such things can be replaced by, say, an indexed view on this table that looks like the separate table. Or the DML can be done in a stored procedure so it's all in one place for clarity.

If you do hit issues, consider snapshot isolation so that the writes do not block reads.

Summary: should be OK, but there are things to mitigate if not.

Edit, read:write ratio

SELECT  SUM(ddius.user_updates) AS user_writes,
        SUM(ddius.user_lookups + ddius.user_scans + ddius.user_seeks) AS user_reads,
        OBJECT_NAME(ddius.object_id),
        1.0*SUM(ddius.user_lookups + ddius.user_scans + ddius.user_seeks)/NULLIF(SUM(ddius.user_updates), 0)
FROM    sys.dm_db_index_usage_stats AS ddius
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY
 1.0*SUM(ddius.user_lookups + ddius.user_scans + ddius.user_seeks)/NULLIF(SUM(ddius.user_updates), 0)

Of 227 objects with indexes in my main OLTP system

only 24 have more writes then reads and about half are log or history tables.
101 have zero writes (mostly lookup or metadata tables)
Of the remainder, some of our heaviest use table have x2 or x10 or x25 and higher more reads than writes


Behavior of Select expressions containing select Consider the following setup:

CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text);

INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');

CREATE TABLE fields(fid integer, fname text);
INSERT into fields values(1, "Id");
insert into fields values(2, "Name");

SELECT name, (SELECT "Name") FROM NAMES;

The last statement will output

Tom|Tom
Lucy|Lucy
Frank|Frank

However, although select fname from fields where fid=2 yields ("Name") (as a single-cell table), replacing the last statement in the above query by

SELECT name, (select fname from fields where fid=2) FROM NAMES;

gives

Tom|Name
Lucy|Name
Frank|Name

Why? Is this intended?

Note: This has been tested using SQLite v3.20

In SQL, 'single quotes' are used for string values, while "double quotes" are used for table/column names.

SQLite allows all types of quotes for compatibility with other databases, but it uses the correct interpretation first, if possible.

So in the subquery (SELECT "Name"), it will try to use a column named Name, and there is one. In other words, the first query is interpreted as:

SELECT NAMES.Name, (SELECT NAMES.Name) FROM NAMES;

(The subquery just refers to the value in the current row of the outer query; this is a rather pointless instance of a correlated subquery.)

SQLite is designed as an embedded database that is accessed from a 'real'
 programming language, so it has no built-in mechanism for dynamic SQL. You have to do the column name lookup separately:

cursor = db.execute("select fname from fields where fid = ?", [2])
fname = cursor.fetchone()[0]
cursor = db.execute("SELECT name, {} FROM NAMES".format(fname))
for name, othercolumn in cursor:
    ...


MySQL Replication and High Availability I know this might not be a very good question to be asked in this site, but I really need some helps/hints to be directed to the right path.

I want to have failover strategy for 2 MySQL servers which are in Master-Master Replication mode. I have done the replication, and it's working just fine. Yet, when in comes to the failover strategy, I'm kind of clueless...

I've been reading on the internet over the past couple of days (I know this is a very complex procedure, and I could not master it in a week or so), but the more I read the more confuse I get. Basically, as I understood, I need to have a software to monitor the network and report any failure and another one to switch between two systems. Heartbeat apparently is the best in doing so, but in all tutorials I read, they are using many other software ( like MON for monitoring, or other software for dumping or getting backups) as well. Is it necessary to have them? Can one have fail-over strategy only using MySQL and HA tool like Heartbeat?

Each website is introducing different tool and ways, which they have their own pros and cons, and of course it depends on how I want my "cluster" to work, and how much high availability is important to my system. I am just considering pointing it out to my customers, and it's not going to the production phase any soon, since my customers running small businesses and don't care about loosing data that much, however, I wanted to dig into the concept by setting up a simple master-master replication where in case of failure of any servers, clients automatically commit changes to the standby server.

Given the fact that you mentioned you have Master-Master replication mode, I would not recommend any automatic failover unless you properly account for Replication Lag. After all, MySQL Replication is asynchronous.

It is theoretically possible to have the following:

DBServer1 as Master to DBServer2
DBServer2 as Master to DBServer1
DBVIP pointing at DBServer1
DBServer2 is 180 seconds behind
DBServer1 goes down
Automatic Failover moves DBVIP to DBServer2

With this scenario, DBServer2 could have auto increment keys that do not exist yet. Upon failover, the DBVIP will allow WebServers to connect to DBServer2 and ask for data that does not exist yet.

This would therefore require background processes running on each DBServer.

For the above scenario:

DBVIP is on DBServer1
DBServer1 runs HeartBeat
DBServer2 runs HeartBeat
Background Process on DBServer1 to monitor

a) Data Mount Availability
b) Data Mount Writeability
c) MySQL Connectivity
Once a,b, or c fail, kill HeartBeat

Background Process on DBServer2 to make sure DBVIP is pingable

What should killing HeartBeat do? Trigger the startup script defined for it.

What should the startup script on DBServer2 look for?

Loop until DBVIP is unreachable via ping
Connect to MySQL and

Run SHOW SLAVE STATUS\G in a Loop until Seconds_Behind_Master is NULL
RUn SHOW SLAVE STATUS\G in a Loop until `Exec_Master_Log_Pos stops changing

Assign DBVIP to DBServer2 via ip addr add

This is essentailly the algorithm for failing over safely to a Passive Master in a Master/Master Replication Cluster.

ALTERNATIVE

If ALL your data is InnoDB, I recommend something with less rigor. Perhaps you should look into using DRBD and HeartBeat. Here is why:

DRBD provides network RAID-1 for a Block Device on two servers.

You would essentially do this:

Have DBServer1's DRBD Block Device as Primary
Have DBServer2's DRBD Block Device as Secondary
Mount DBServer1's DRBD Device on /var/lib/mysql
Startup MySQL on DBServer1
Have HeartBeat Monitor Ping Activity Between Servers

What would startup script look like in a DRBD scenario?

Loop until DBVIP is unreachable via ping
Kill HeartBeart
Disconnect DRBD
Promote DRBD to Primary
Mount DRBD on /var/lib/mysql
Start MySQL (InnoDB Crash Recovery Fills in Missing Data)
Assign DBVIP via ip addr add

This is a lot more straightforward because only one side is Active. The Passive side (DRBD Secondary) is a Synchronous Disk Copy of the Active Side (DRBD Primary).

CAVEAT

If all or most of the working set data is MyISAM, do not touch DRBD. Crash scnearios quickly result in MyISAM tables being marked crashed and need auto-repair (which can be paintfully slow to wait for).

UPDATE 2012-12-29 08:00 EDT

Here are my past posts on using DRBD with MySQL

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Aug 29, 2011 : MySQL Replication : 1 Slave / Multiple Masters
Dec 19, 2011 : Best way to setup master to multi master replication
Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site (I stirred a pretty big pot on this one in someone else's blog)


How to find a query that is use deprecated option? Mongodb writes logs

  2018-04-05T11:38:28.178+0000 W COMMAND  [conn107] Use of the aggregate
  command without the 'cursor' option is deprecated. See
  http://dochub.mongodb.org/core/aggregate-without-cursor-deprecation.

How to find related query?

You need go thru your source code! Search all aggregate commands and add needed ,{cursor:{}} section. Check here!


MySQL - The DBMS must maintain a separate execution domain for each executing process I'm trying to get a government system hardened and during that process I have to answer certain criteria. One of them is the below:

  Rule Title: The DBMS must maintain a separate execution domain for
  each executing process.

  Discussion: Database management systems can maintain separate
  execution domains for each executing process by assigning each process
  a separate address space. Each process has a distinct address space so
  that communication between processes is controlled through the
  security functions, and one process cannot modify the executing code
  of another process. Maintaining separate execution domains for
  executing processes can be achieved, for example, by implementing
  separate address spaces.

  Check Text: Review the DBMS architecture to find out if and how it
  protects the private resources of one process (such as working memory,
  temporary tables, uncommitted data and, especially, executable code)
  from unauthorized access or modification by another user or process.

  If it is not capable of maintaining a separate execution domain for
  each executing process, this is a finding.

  If the DBMS is capable of maintaining a separate execution domain for
  each executing process, but is configured not to do so, this is a
  finding.

  Fix Text: Deploy a DBMS capable of maintaining a separate execution
  domain for each executing process.

  If this is a configurable feature, configure the DBMS to implement it.

Anyone have any information or documentation on this?

I guess the answer depends on how you define the term "process".

MySQL is a single-process DBMS; each connection runs as a separate thread, but they share the same process address space. Subsequently, there's nothing that prevents one thread from accessing another thread stack, for example.

In the spirit of the rule text you quote MySQL is "not capable of maintaining a separate execution domain for each executing process" -- if by "process" you understand a client connection.

However, if you stick to the letter of the rule, since there's only one DBMS process at the OS level, no isolation is necessary apart from the process isolation provided by the underlying operating system.


Error replicating password change in MySQL slave So I built a master-slave Mysql replication architecture in production because of a database FOO that I wanted to query (slave node) without affecting the performance of the main site (master node).

The thing is, I was afraid of someone breaking replication by inserting data in the slave which would conflict with data coming from master, so as soon as I created the slave node, I removed all privileged users (e.g. dba1, dba2) in the slave node and added a single readonly one (with the SELECT grant): readonlyUser.

The problem I have now is that suddenly dba1 has changed his password in the master node, and the result is this replication error:

Replication reported error 1396:
Error 'Operation ALTER USER failed for 'dba1'@'localhost'' on query.
Default database: 'mysql'.
Query: 'ALTER USER 'dba1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DAB3A57F75AD41285552E115420DA2EFD756CD19''

Should I prevent the replication of the mysql DB then? I'm afraid that, if I do this, any structure changes for my FOO database will not be replicated, because the structure of the tables is stored in the mysql database, right?

Or should I just ignore errors of type 1396? Is error1396 always related to users or is it more broad?

Mysql Replication is pretty hard to manage, if anyone executed wrong query it'll affect the slave too. Use the below command to fix.

  Stop slave;

  SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

If you want to exclude this error all time, add the below line in my.cnf file.

If the error code is 1396 then

  slave-skip-errors = 1396


Are the switches for the SQLCMD command case sensitive? I have MS SQL Server 2014 Management Studio installed on two computers, both of which run on Windows 10. The switches for SQLCMD command are case-sensitive on one computer, but it is not on the other one. If I type the command with lower case switches (like the below examples), it works on one but doesn't work on the other.

sqlcmd -s .\<_servername_>
sqlcmd -l

For the second computer, I have to type with upper case as shown below.

sqlcmd -S .\<servername>
sqlcmd -L

What is the issue/setting that decides this?

Yes, SQLCMD switches are case sensitive.

 -l login_timeout
 -L[c] (list servers, optional clean output)
 -s col_separator
 -S [protocol:]server[instance_name][,port]

When you connect using

sqlcmd -s .\<servername>

is connects to the default instance and uses ".\<servername>" as column separator

While on the second machine there may not be any default instances so you need to enter -S .\ explicitly to connect to a named instance


What problems would using the reserved keywords DATE and TIME as column names cause in Oracle? DATE is an SQL Reserved Word and TIME is a PL/SQL Reserved Word in Oracle (listed in SQL> HELP RESERVED WORDS). What problems would using them as column names cause in Oracle?

The database server runs:

The answer to your question is that it won't let you.

Oracle is stricter than other RDBMSes and you'll get an ORA-00904: invalid identifier if you try a CREATE TABLE with a reserved word.

However, you can force it to do so by surrounding the name in quotes, for example:

CREATE TABLE a
(
  "date" date
);

... But in doing so, you're essentially admitting you're doing something wrong (plus it makes the name case-sensitive).

Best practice would be to avoid case-sensitive object names, and avoid using reserved words.

Documentation link here, with a list of reserved words, which backs up what I've stated:

  You cannot use Oracle SQL reserved words as nonquoted identifiers.
  Quoted identifiers can be reserved words, although this is not
  recommended

In summary: Just don't.


Update Null Values With Other Values I am new to the SQL Server world. In my example below, I have two companies:

Building Blocks
Red Cement

Only one entry for each company has a value for the columns power1, power2, power3.

I need to update the table so that each company has a value for all columns. In other words, I need to update all blank (null) values.

Example with made-up data:

Create Table #Rentarious
(
  c1 varchar(200)
  ,yryryryr datetime
  ,power1 varchar(200)
  ,power2 varchar(200)
  ,power3 varchar(200)
)

Insert Into #Rentarious VALUES
('Building Blocks','2016','Red','Blue','Green')
,('Red Cement', '2012', 'Pink','Purple','Orange')

Insert Into #Rentarious(c1, yryryryr) VALUES
('Building Blocks', '2012')
,('Building Blocks', '2013')
,('Building Blocks', '2014')
,('Red Cement', '2016')
,('Red Cement', '2011')

Question

What would the syntax be to write an update statement to update the null values for columns power1, power2, power3 to the values already listed in the table?

What would the syntax be to write an update statement to update the null values for fields power1, power2, power3 with the values already listed in the table?

I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, your UPDATE statement would need to look something like this:

UPDATE
  #Rentarious
SET
  power1 = power1 from the same company’s populated row,
  power2 = power2 from the same company’s populated row,
  power3 = power3 from the same company’s populated row
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

One way to implement the above pattern could be to use correlated subqueries:

UPDATE
  #Rentarious
SET
  power1 = (SELECT power1 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power1 IS NOT NULL),
  power2 = (SELECT power2 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power2 IS NOT NULL),
  power3 = (SELECT power3 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power3 IS NOT NULL)
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

While that would work, such an update might not be very efficient, because the same table would be touched three extra times to obtain the source value. Since you know that the non-null values are stored in one row for each company, you could obtain them all in just one extra pass over the table by using a derived table and the proprietary "update with a join" syntax:

UPDATE
  #Rentarious
SET
  power1 = sub.power1,
  power2 = sub.power2,
  power3 = sub.power3
FROM
  (
    SELECT
      c1,
      power1,
      power2,
      power3
    FROM
      #Rentarious
    WHERE power1 IS NOT NULL
      AND power2 IS NOT NULL
      AND power3 IS NOT NULL
  ) AS sub
WHERE #Rentarious.c1 = sub.c1
  AND #Rentarious.power1 IS NULL
  AND #Rentarious.power2 IS NULL
  AND #Rentarious.power3 IS NULL
;

You can also rewrite it to use the explicit JOIN syntax:

UPDATE
  tgt
SET
  power1 = src.power1,
  power2 = src.power2,
  power3 = src.power3
FROM
  #Rentarious AS tgt
  INNER JOIN
  (
    SELECT
      c1,
      power1,
      power2,
      power3
    FROM
      #Rentarious
    WHERE power1 IS NOT NULL
      AND power2 IS NOT NULL
      AND power3 IS NOT NULL
  ) AS sub
  ON tgt.c1 = sub.c1
WHERE tgt.power1 IS NULL
  AND tgt.power2 IS NULL
  AND tgt.power3 IS NULL
;

As you can see, there is only one subquery in both variations and it provides all three values to populate the other rows.

Note, though, that it is also possible to solve this problem without any extra scans. First of all, if this were a SELECT statement, you could return the populated row's values in every row by using a window aggregate function, like this:

SELECT
  c1,
  yryryryr,
  power1,
  power2,
  power3,
  populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
  populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
  populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
  #Rentarious
;

The MAX function works in this situation because it returns the maximum value only across the non-null values in the specified set. In your case, there would be only one non-null value per partition of c1 in each of the three cases, so the function would return that one value. This would be the result of the query for the example in your question:

c1               yryryryr    power1  power2  power3  populatedPower1  populatedPower2  populatedPower3
---------------  ----------  ------  ------  ------  ---------------  ---------------  ---------------
Building Blocks  2012-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2013-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2014-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2016-01-01  Red     Blue    Green   Red              Blue             Green
Red Cement       2012-01-01  Pink    Purple  Orange  Pink             Purple           Orange
Red Cement       2016-01-01  NULL    NULL    NULL    Pink             Purple           Orange
Red Cement       2011-01-01  NULL    NULL    NULL    Pink             Purple           Orange

So the only thing remaining would be just to

SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3

And it is indeed possible to do so in SQL Server, because the result of the above SELECT query can be used as the target of your UPDATE statement. You can use it as a derived table:

UPDATE
  tgt
SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3
FROM
  (
    SELECT
      c1,
      yryryryr,
      power1,
      power2,
      power3,
      populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
      populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
      populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
    FROM
      #Rentarious
  ) AS tgt
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

or implement it as a CTE (Common Table Expression) and use the CTE's alias as the target:

WITH tgt AS
  (
    SELECT
      c1,
      yryryryr,
      power1,
      power2,
      power3,
      populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
      populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
      populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
    FROM
      #Rentarious
  )
UPDATE
  tgt
SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

Both would work equally well and result in all nulls replaced with corresponding values, i.e. from this:

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  NULL    NULL    NULL
Building Blocks  2013-01-01  NULL    NULL    NULL
Building Blocks  2014-01-01  NULL    NULL    NULL
Red Cement       2016-01-01  NULL    NULL    NULL
Red Cement       2011-01-01  NULL    NULL    NULL

to this:

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  Red     Blue    Green
Building Blocks  2013-01-01  Red     Blue    Green
Building Blocks  2014-01-01  Red     Blue    Green
Red Cement       2016-01-01  Pink    Purple  Orange
Red Cement       2011-01-01  Pink    Purple  Orange


Pre-formated varchar variable does no work in select query I am trying to run a select query that receives in the where clause the @Sites variable. The query at the moment is returning empty because the following line

AND S.[Name] IN (@Sites)

is not being processed as expected. Below an extract of the query:

SET QUOTED_IDENTIFIER OFF

DECLARE @Month int
, @Sites varchar(MAX) = null

SET @Month = 8
SET @Sites = 'Bayside;Collaborative'
SET @Sites = "'" + REPLACE(@Sites,';',"','") + "'"

SELECT
    CV.[Id] AS Id
    , ...
    , S.[Name] AS [Site]
    , ...
  FROM
    [CalendarViews] CV
    INNER JOIN [Sites] S ON S.[Id] = CV.[SiteId]
  WHERE
    MONTH(CV.[DateAllocation]) = @Month
    AND S.[Name] IN (@Sites)
  ORDER BY
     S.[Name]

When I run the query replacing

AND S.[Name] IN (@Sites)

for

AND S.[Name] IN ('Bayside','Collaborative')

the query works as expected. Can anybody tell me what I am missing please? Thank you.

@Sites is a string, not an array. So you are literally searching for:

S.[Name] IN ('''Bayside'',''Collaborative''')
-------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- this is one single string

I highly recommend, instead of passing in a comma- or semi-colon-separated string, you pass in a table-valued parameter. First, create this in your database:

CREATE TYPE dbo.Sites AS TABLE(Site NVARCHAR(255));

Then your procedure would say (leaving out the less relevant bits):

ALTER PROCEDURE dbo.ProcedureName
  @Sites dbo.Sites READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ...
  FROM dbo.Sites AS s
  INNER JOIN @Sites AS tvp
    ON s.[Name] = tvp.Site;
END
GO

Then your code would pass a structured parameter (I assume the set of sites originally comes from a DataTable or some other collection):

SqlCommand cmd = new SqlCommand("dbo.ProcedureName", conn);
c2.CommandType = CommandType.StoredProcedure;
SqlParameter tvp = cmd.Parameters.AddWithValue("@Sites", DataTableName);
tvp.SqlDbType = SqlDbType.Structured;
...

If you really want to do this the slow, old-fashioned way, you can create a UDF that splits the string:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM
      (
        SELECT x = CONVERT(XML, ''
          + REPLACE(@List, @Delimiter, '')
          + '').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

Then your query is (again leaving out the far less relevant bits):

  INNER JOIN dbo.SplitStrings_XML(@Sites, N';') AS f
    ON s.[Name] = f.Item;

Now, XML might not be the safe approach here, but it's the simplest to repro without offering even slower methods or auxiliary objects such as a numbers table. For other alternatives, and more on why you don't want to do it this way, see:

Split Strings The Right Way - Or The Next Best Way
Splitting Strings : A Follow-Up
Splitting Strings : Now with less T-SQL


Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value I am new to triggers and SQL in general. Sorry in advance if this forum is only for experts and this is a newbie question...

I need to create a trigger that fires when someone tries to submit a grade(evaluation) of "5" in bookinglog_table for grade column. This will then grab the values of 5 columns that gave that grade of 5, and put them in a new table (so a fourth table) that I already created.

But the 5 columns are from 3 different tables (for example, fname=si_table, lname=si_table, phone=contact_table, email=contact_table, and grade=bookinglog_table.)

So tables are si_table si, contact_table c, and bookinglog_table b

What they have in common is si.student_id=c.student_id, c.event_id=b.event_id, and si.test=b.test

I have a trigger that works only if I bring in columns from table 3 which is where the INSERT or UPDATE grade originally comes from.

So here is the new table, we'll call it grade_table in which I tested the trigger that WORKS.

CREATE TABLE grade_table (
grade NUMBER (1),
test1 NUMBER(5),
test2 NUMBER(8));

And the trigger that DOES work with the above:

CREATE OR REPLACE TRIGGER b_trigger1
AFTER INSERT OR UPDATE ON bookinglog_table
FOR EACH ROW
WHEN (NEW.eval = 5)
DECLARE
BEGIN
INSERT INTO grade_table
    VALUES (:NEW.grade, :NEW.test1, :NEW.test2);
    DBMS_OUTPUT.PUT_LINE('Recorded student information into grade_table');
END;
/

But I don't want test1 and test2 values from the bookinglog_table. I want fname, lname, phone, email from the other two tables, as well as the grade from the bookinglog_table.

Any tips, hints or suggestions? I've tried creating a SELECT statement within the trigger, but I'm not sure when I need aliases, if I need to put :NEW somewhere, or if I need an IF statement instead, etc..

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger.

You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

In the body of your trigger you first need to get the additional information like fname, lname, etc. With a query like this one:

SELECT si.fname into l_fname, si.lname into l_lname, c.phone into l_phone, c.email into l_email
  FROM si_table si, contact_table c, bookinglog_table b
 WHERE si.student_id=c.student_id AND c.event_id=b.event_id, AND si.test=b.test
   AND <add a clause to uniquely identify a single record>;

Remember to declare the cariables l_fname, l_lname, etc. in the DECLARE block of the trigger:

DECLARE
  l_fname varchar2;
  l_lname varchar2;
  l_phone varchar2;
  l_email varchar2;
BEGIN
...

Even though this solution might work, it is actually not correct. You better create the table grade_table as follows. According to the database normalization form 4 you should not store detailed data multiple times.

CREATE TABLE grade_table (
  student_id number,
  event_id number,
  test number,
  grade NUMBER
);

When you query the table grade_test and need the details on the student, etc. you simply join the detail tables.


How to take Oracle backup and restore? I am coming from SQL Server background where SQL Server studio has a very easy way of taking database backup and then doing a restore.

I have been using Oracle 11G express edition for past couple of months and I am not sure of doing the backup and restore in oracle.

I can see the option to backup database and restore databse in oracle server menu but I am not sure how does it work, where does it creates the backup file etc.

Please help

rman is the way to go for backup and recovery for protection against physical corruption. For documentation check Oracle® Database 2 Day DBA
11g Release 2 (11.2)
In the current release of SQLDeveloper backup and recovery is integrated in the GUI. For download of SQLDeveloper see Oracle SQL Developer It is a free product, with active development.

In SQLdeveloper in the View menu select DBA to get access to the dba tools like RMAN and DATAPUMP. Very nice tool. Don't forget that the backup files are written by the Oracle database server processes. This means that the backups are written on the database server, not on your client. Also, currently it just generates a backup script. You should run the generated script using RMAN.

An other way to run and configure the backup is by using dbconsole or grid control.

Using datapump is an other way to protect against data loss. Using this enables you to read back the data as it was during the export. Might be usefull for test sets that have to be refreshed with a specific version of the data. Not really usefull if you want to recover production transactions to the last moment before disaster stroke.


Dropping Hypothetical Indexes In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.

I have a database that is full of DTA remnants that I would like to cleanup; however, when I try to drop the object I always receive an error telling me that I cannot drop the object "because it does not exist or you do not have permission". I am a full sysadmin on the server so would expect to have rights to do anything.

I've tried this with both DROP STATS and DROP INDEX statements but both give me the same error.

Has anyone deleted these before and is there a trick I'm missing?

Addendum

Poking around in this, I just noticed that if I R-Click on the object, both the 'Script As' and 'DELETE' options are greyed out.

The hypothetical indexes created by the Index Tuning Wizard start with a name of "hind_%" and should not exist after the tuning has finished; they should all be removed. You can run the following script from the SQL Server Query Analyzer to remove any such indexes that may exist. You must log in by using an account that has either sysadmin or db_owner permissions, or is the owner of the object on which these statistics were created.

For example

DECLARE @strSQL nvarchar(1024)
DECLARE @objid int
DECLARE @indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
WHILE (@@FETCH_STATUS  -1)
BEGIN
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @objid and i.indid = @indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats

Above script is provided by Microsoft for more detail you can visit following link.
http://support.microsoft.com/kb/q293177


Changing POSTGRES column datatype - should I drop indexes and then re-create? I have a number of postgres tables with a column of type INT which I need to change to BIGINT. Each partition of the table contains about 100 million rows, my intention is to detach the individual partitions ALTER the column type, and then re-attach.

My question is this, the column that needs updating is indexed. Is likely to be quicker to drop the index, alter the column type and then re-create the index? Or should I leave the index in place and just change the column type?

note that the column values aren't going to change, just the datatype (not sure if that's relevant or not!)

thanks

The ALTER will automatically drop and rebuild the indexes (all indexes, not just on the column being altered).  There is no point in micromanaging the process yourself, unless you want to build the indexes CONCURRENTLY or something like that, so that that part can be done outside the maintenance window.


Should I split this large table into three smaller tables? I'm designing a PostgreSQL database that will contain information about photos uploaded by my users.  All users will have at least one main photo and optionally one or more public and/or private photos.  My first schema is as follows:

user
----
id (PK)

photo
-----
id (PK)
user_id   (FK to user)
photo_id  (unique identifier such as "aK1q9")
type      ("main" or "other")
access    ("public" or "private")

The query that will get run the most is:

SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'main' AND u.id = (some user id);

The next most popular query will be:

SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'other' AND p.access = 'public' AND u.id = (some user id);

The problem I forsee is that the Photo table will become extremely large over time as it will contain all public and private photos uploaded by all users.  Since my most popular query will only be looking for the main photo IDs, would it make more sense to break my photo table up into three tables?

main_photo
----------
id (PK)
user_id   (FK to user but in a one-to-one relationship to user)
photo_id

other_public_photo
------------------
id (PK)
user_id   (FK to user)
photo_id

other_private_photo
-------------------
id (PK)
user_id   (FK to user)
photo_id

I would think this latter schema would be preferable because 1) each photo's type and access information is made explicit by where it's stored thus eliminating additional ANDs in my queries; and 2) my queries would run faster since they would be run against one of the three smaller tables rather than one huge table.  Which is the optimal approach from a performance standpoint?

Thanks.

@CoderAbsolute's answer gives you a good design for your tables. Since he or she did not go into detail about why this approach is better, I thought it was worth adding another answer.

First of all, design your table structure in accordance with how your data fits together. Don't try to smoosh different types of things into one table, don't add several tables for the same kind of records. Try to 'normalize' - if a table will have the same info repeated many times, then move that info into a different table, and link it from the first table using a foreign key. You should be aware of what first normal form, second normal form and third normal form are. Many real-world databases do not match these standards completely, but being aware of what you should aim for will help you make a much cleaner design.

I would say, don't worry about optimization until you've already got a correct design. First of all, you don't yet know how many entries you will have in your tables. Secondly, database engines are designed to make queries as fast as possible, even if you have a lot of entries. Don't second guess the developers of your database software.

When you've figured out that you really do have a bottleneck, you should look first at indexing. We think of a database table as being something like an array. In reality, it could be stored as a bunch of 'blobs', one for each row, which might all have different locations on a disk, not necessarily in order. (This is not a super accurate technical description of DB internals. But it should give you a clearer picture of how the pieces fit together.) How does the database find all the blobs? It has a somewhere, a list of 'pointers' which tell it where to find each blob. So typically, finding every single row of a table is an efficient process. It goes through the list and records each one.

Now, suppose that you most commonly retrieve all the photos for a given user. This might be a slow process, since it has to go through every single row of the Photo table, and look at the UserId field. In this case, you should add an Index on that field. An index is something like a lookup table, which allows the software to quickly find the location of all the rows with a given UserId. So it doesn't have to check every row in turn.

There are different kinds of indexes. Some are optimized for matching a particular value. This is probably the type you want for the UserId column. Some are optimized for finding things greater or smaller than some value. This might make sense for timestamps. (Give me all the photos from the last month.) You can have an index on multiple columns if this is what you regularly query on, or even on some function of one or more columns. Some indexes mean that similar items are stored close to each other on disk, to take advantage of caching to retrieve them more quickly.

You should familiarize yourself with the possibilities for your DBMS. Experimentation can also be very valuable here, since the exact speedups depend on your settings and also your hardware configuration.


PostgreSQL: Foreign key on string column I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it.

So for example:

create table "Item"
(
    "Name" VARCHAR(254) PRIMARY KEY
);

CREATE TABLE "Uses" --new table
(
    id SERIAL PRIMARY KEY NOT NULL,
    "itemName" VARCHAR(254) NOT NULL,
    FOREIGN KEY ("itemName") REFERENCES "Item" ("Name")
);

I was wondering, if each row of Uses will have a reference(memory address) to the Item tables column - that way not copying the value around.

Or will it indeed copy that value, causing superfluous space loss.

Also would like a general opinion on using a VARCHAR as a primary and foreign key. Should maybe better alter the old database to give it a proper id?

Thank you very much.

varchar keys are okay, but they do have some issues:

varchars use more space than ints
you are more likely to have to update a varchar value than an int value, causing cascading updates
might not be appropriate in internationalized applications (i.e. different values for different languages)

To answer your particular question, no there won't be a pointer to the value, the actual value will be stored.

I rarely use varchars in key columns. Sometimes I'll use short char columns like ISO country codes, for convenience.

Also, you can just use the text type in PostgreSQL, as varchar uses text internally. Also, I'd recommend using unquoted snake_case column names.


Show multiple joined rows I have a simple relational MySQL database with users, cars an a log table. Everything works fine. In the log table I want to 'register' the users of a car. But a car can have two users at the same time.

My question: How can I show multiple users in the log table, using the relational database?

My database structure:

Table users

user_id  name   phone
-------  -----  -----
1        john   123
2        kevin  456

Table cars

car_id  nr   brand
------  ---  -----
1       666  Ford
2       999  Dodge

Table log

log_id  car_id  user_id1  user_id2
------  ------  --------  --------
1       1       1         2

The car.car_id is related to log.car_id and users.user_id is related to log.user_id1 and log.user_id2.

Supposed SQL:

SELECT log.*, cars.*, users.*
FROM log
LEFT JOIN cars ON log.car_id=car.car_id
LEFT JOIN users ON log.user_id1=users.user_id
LEFT JOIN users ON log.user_id2=users.user_id

PHP (in loop of course):

echo $row["brand"];
echo $row["name"]; // user_id1
echo $row["name"]; // user_id2

It's working for only one name. How to show the names of user_id1 and user_id2 in the same "log" row?

Try using aliases like this:

SELECT l.*, c.*, u1.*, u2.*
FROM log l
LEFT JOIN cars c ON l.car_id=c.car_id
LEFT JOIN users u1 ON l.user_id1=u1.user_id
LEFT JOIN users u2 ON l.user_id2=u2.user_id

(Note that it's bad practice to use "SELECT *" except for ad-hoc queries. So you should instead enumerate the exact columns you want to select.)


MySQL : DATE_FORMAT() vs. SUBSTR() I'm currently working on MYSQL DB optimizations. I'm looking around some slow queries and I try to optimize some of them.

In my app, for statistics purposes, I'm querying and -very- big table (millions of records) which contains visitors log entries (clicks, opens, impressions,...).
That table contains :

ID (INT, Primary Key)
OpenDate (DATETIME)
ListID (INT)
UserID (INT)
...and some other fields

I need to retrieve number of rows per Day. So I need to convert DATETIME into DATE to be able to GROUP BY day value.

DATETIME is on format yyyy-mm-dd hh:ii:ss
In the facts, what I need is the only 10 first chars : yyyy-mm-dd

Currently, the query is :

SELECT COUNT(*) AS TotalOpens, DATE_FORMAT(OpenDate,  '%Y-%m-%d') AS DayOfWeek
FROM `stats_open` WHERE ListID='38' AND UserID='4'
GROUP BY crc32(DayOfWeek) ORDER BY TotalOpens DESC

But I wonder if I should change the DATE_FORMAT function to SUBSTR() function, so MySQL would work a little bit less ?
Isnt easier for MySQL to cut a string instead of reformat a date ?

Thanks for your help.

Joffrey

Since OpenDate is a datetime column, you don't need to use DATE_FORMAT(), you can simply GROUP BY DATE(OpenDate). And the extra CRC32() call seem completely redundant.

The query can be rewritten:

SELECT COUNT(*) AS TotalOpens,
       DATE(OpenDate) AS DayOfWeek
FROM stats_open
WHERE ListID = 38
  AND UserID = 4
GROUP BY DATE(OpenDate)
ORDER BY TotalOpens DESC ;

Add an index on (ListID, UserID, OpenDate), to make it more efficient.


Where can I find documentation on SQL Server internals? I know, well I've been told, Microsoft has a per-dev doc system under MSDN. Specifically, I'm looking for docs on the internal format of the WAL log. Is this documented anywhere? Certainly third parties must have access to this?

And, moreover, is there anyway to know what exactly is documented with MSDN without first paying for it?

What other source of documentation is there on the SQL Server internals?

I'm looking for docs on the internal format of the WAL log

The SQL Server Log File is not publicly documented, but there is a partner program for 3rd parties that want to build solutions that use it directly.  EG Attunity, Quest, etc.

    And, moreover, is there anyway to know what exactly is documented with MSDN without first paying for it?

There is ZERO additional documentation available to MSDN subscribers.  All published documentation and support material is published to the web.

The best sources for SQL Server Internals are the work of Kalen Delaney https://www.red-gate.com/simple-talk/author/kalen-delaney/ and Paul Randal https://www.sqlskills.com/blogs/paul/.

In particular Paul has some posts on reading the SQL Log. eg
https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/


As a SQL Server DBA, what do I need to know about the meltdown/spectre vulnerabilities? If you didn't hear, a set of related vulnerabilities were recently discovered that impact virtually all processors sold over the last decade. You can find more technical detail about the meltdown/spectre vulnerabilities on InfoSec.SE.

As a SQL Server DBA, what do I need to understand about this?

If we don't share our SQL Servers (or our vm farms) with other companies, is this still a risk?

Will this be simply an OS patch? Or are there patches/hotfixes available for SQL Server that are required to address this vulnerability? Which SQL Server versions will be patched?

Some articles are predicting a 5-30% performance impact, especially in highly virtualized environments. Is there any way to predict what the performance impact on my SQL Servers might be?

Here is Microsoft's Security Advisory on the vulnerabilities, which have been assigned three "CVE" numbers:

CVE-2017-5715 - Branch target injection ("Spectre")
CVE-2017-5753 - Bounds check bypass ("Spectre")
CVE-2017-5754 - Rogue data cache load ("Meltdown")

The Microsoft KB for how these vulnerabilities impact SQL server is being actively updated as new information becomes available:
KB 4073225: SQL Server Guidance to protect against speculative execution side-channel vulnerabilities.
Microsoft's exact recommendation will depend on your configuration and business scenario, please refer to the KB for details. If you are hosting on Azure, for example, no action is required (the environment is already patched). If, however, you are hosting applications in shared virtual or physical environments with potentially untrusted code, other mitigations may be required.
SQL patches are currently available for the following impacted SQL versions:

SQL Server 2008: 2008 SP4 GDR
SQL Server 2008R2: 2008R2 SP3 GDR
SQL Server 2012: 2012 SP3 CU, 2012 SP3 GDR, 2012 SP4 GDR
SQL Server 2014: 2014 SP2 GDR, 2014 SP2 CU
SQL Server 2016: 2016 CU7 SP1, 2016 GRD SP1, 2016 CU, 2016 GDR, CU7 for 2016 SP1
SQL Server 2017: 2017 GRD, 2017 CU3 RTM, CU3 for 2017

These SQL server patches protect against CVE 2017-5753 (Spectre: Bounds check bypass).
To protect against CVE 2017-5754 (Meltdown: Rogue data cache load), you can enable Kernel Virtual Address Shadowing (KVAS) on Windows (via registry change) or Linux Kernel Page Table Isolation (KPTI) on Linux (via a patch from your Linux distributor).
To protect against CVE 2017-5715 (Spectre: Branch target injection), you can enable Branch Target Injection mitigation hardware support (IBC) via registry change plus a firmware update from your hardware manufacturer.
Note that KVAS, KPTI and IBC may not be required for your environment, and these are the changes with the most significant performance impact (emphasis mine):

Microsoft advises all customers to install updated versions of SQL Server and Windows. This should have negligible to minimal performance impact to existing applications based on Microsoft testing of SQL workloads, however, we recommend that you validate before deploying to a production environment.
Microsoft has measured the impact of Kernel Virtual Address Shadowing (KVAS), Kernel Page Table Indirection (KPTI) and Branch Target Injection Mitigation (IBC) on various SQL workloads in various environments and found some workloads with significant degradation. We recommend that you validate the performance impact of enabling these features before deploying into a production environment. If the performance impact of enabling these features is too high for an existing application, customers can consider whether isolating SQL Server from untrusted code running on the same machine is a better mitigation for their application.

Microsoft System Center Configuration Manager (SCCM) specific guidance:
Additional guidance to mitigate speculative execution side channel vulnerabilities as of Jan 08 2018.

Related blog posts:

Brent Ozar: SQL Server Patches for Meltdown and Spectre Attacks
SQLHA: The No Good, Terrible Processor Flaw and SQL Server Deployments – Nearly Everything You Need To Know
Thomas LaRock: SQL Server Guidance to Protect Against Meltdown and Spectre Attacks
Glenn Berry: Microsoft SQL Server Updates for Meltdown and Spectre Exploits
Glenn Berry: Checking your Meltdown and Spectre Mitigation Status in Windows
Aaron Bertrand: Latest Builds of SQL Server 2017 (collected info and links for CU3)
Joey D'Antoni: Spectre and Meltdown: How Do They Impact SQL Server?


Can sqlite dump database schema in a fixed order? I am using SQLiteStudio 3.0.7 to dump the database schema regularly for versioning purposes and it seems to change the order of the table definitions at random. This wreaks havoc on the diffs as even a small change like adding one or two additional columns seems to result in wild diffs all over the place. A side by side comparision can result in a whole table being list out separately when it is just the line difference that should show.

Can SQLiteStudio be configured to do that correctly., or can sqlite itself do the same schema export in a fixed formatted manner?

SQLiteStudio sorts the database objects by type (table, index, trigger, view), but inside each group, sorts them randomly.
(If you consider this a bug, report it.)

The sqlite3 shell dumps tables in the order in which they are stored in the sqlite_master table, which is typically the same order in which they were (re)created.


Why is Postgres on RDS maxing out on CPU every few hours? Using Amazon RDS, we are running ETL scripts to migrate our data. However, every few hours there is a huge CPU spike.

Here are the ETL specs (per ETL):

50 records inserted / second
pool of 1000 connections

Here are the server's specs:

Amazon R3.8XL
244 GB RAM
32 vCPU
3TB SSD
10 GiB Network Performance
No Multi-AZ (yet)

Here are the main & modified PG parameter group settings:

checkpoint_completion_target = 0.9
checkpoint_segments          = 16
effective_cache_size         = {DBInstanceClassMemory/10923} (8kb)
maintenance_work_mem         = {DBInstanceClassMemory/16384} (kb)
max_connections              = {DBInstanceClassMemory/12582880}
max_locks_per_transaction    = 64
shared_buffers               = {DBInstanceClassMemory/32768} (8kb)
work_mem                     = {DBInstanceClassMemory/20480000} (kb)

In this case, DBInstanceClassMemory is approximately 244,000,000,000 bytes. The (8kb) means the value is taken as blocks of 8kb, so shared_buffers = 244000000000/32768*8000 = 60 gb. All of the changes made were based on this article, and I set the effective_cache_size to 75% because (as you will see below) the memory doesn't seem to be getting fully utilized.

Here is a screenshot of our database server's stats over a 6 hour time span:

The graph on the top left shows the CPU spikes, and you can see the correlated drop in Write IOPS (the graph below it).

What can be the reason for these CPU spikes? They almost completely freeze queries by the ETL (taking upwards of 3 minutes for queries that would usually take less than a second).

It was a combination of making too many transactions and too many functions. After combining statements into the same transactions and removing the functions, the CPU hasn't risen past an average of 5%.


Reasons against using local indexes For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?

There is a point where the queries using the index are accessing enough partitions that a local index will be slower than a global index.

For example, if your table is partitioned by date, but you are querying only on a status column.


Lack of cached execution plans when using `IN` clause Microsoft's documentation on Caching Mechanisms contains a description of Autoparameterization. As I understand it, Autoparameterization allows SQL Server (under certain circumstances) to use a previously cached execution plan for a query even if the specific parameters for that instance of the query are unique.

However, the documentation on that page states that "there are many query constructs that normally disallow autoparameterization". One of these constructs is the IN clause.

I have several queries in my application that use the IN clause. Each of these queries could have an arbitrary number of values in the IN clause. If I understand the documentation referenced above, then the execution plans for these queries will not be cached.

Questions:

1) Is there a recommended way to write these sorts of queries in order to bypass this limitation and take advantage of cached execution plans (other than using forced parameterization at a database level)?

2) How can I evaluate the impact that these queries are having on the overall performance of the database (e.g., how can I answer quantitatively whether or not it is worth my time to worry about the lack of cached execution plans)?

Thank you in advance for any insight you can provide.

1) Is there a recommended way to write these sorts of queries in order
  to bypass this limitation and take advantage of cached execution plans
  (other than using forced parameterization at a database level)?

Paul White explained it here with examples.

  2) How can I evaluate the impact that these queries are having on the
  overall performance of the database (e.g., how can I answer
  quantitatively whether or not it is worth my time to worry about the
  lack of cached execution plans)?

You need do some collection and aggregation for this one.

Once you compare the performance of your rewrite as explained in Paul's article you know the difference (in io, cpu) for one call + time for recompilation.
Multiply that by number of calls (of the changed query) in x period.
Add those numbers for all the query you rewrite.
Another approach is to benchmark you overall workload, make change to small number of queries and compare with baseline.
If moving in the right direction make more changes (provided workload did not change),otherwise go back to starting point. Evaluate one by one.