query to concatenate columns if record duplicate My table has two column that is id and name. I want to do concatenate the columns if the if more than two names are same like:

id                                                                    name
1                                                                     John
2                                                                     David
3                                                                     Zed
4                                                                     John
5                                                                     David
6                                                                     John
7                                                                     David
8                                                                     John
9                                                                     John

Now I want my output that if name is repeated than the first record will remain as it is and other will be concatenated by id like

Id                                                                     Name
1                                                                      John
2                                                                      David
3                                                                       Zed
4                                                                      4 John
5                                                                     5 David
6                                                                     6 John
7                                                                      7 David
8                                                                      8 John
9                                                                      9 John

Can anyone please help...Thank you

Use partition by on the name column, and count the rows based on the order of id-s. Where the row number of the actual row is greater than 1, concatenate id and name, else just use the name.

with t as (
select 1 as id, 'john' as name from dual union all
select 2, 'david' from dual union all
select 3, 'zed' from dual union all
select 4, 'john' from dual union all
select 5, 'david' from dual union all
select 6, 'john' from dual union all
select 7, 'david' from dual union all
select 8, 'john' from dual union all
select 9, 'john' from dual
)
select
  id,
  case
    when row_number() over (partition by name order by id) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;

        ID NAME
---------- ----------------------------------------------
         1 john
         2 david
         3 zed
         4 4 john
         5 5 david
         6 6 john
         7 7 david
         8 8 john
         9 9 john

9 rows selected.

To treat john, JOHN, joHN, etc. equally, simply change the partition by column, e.g upper(name).

select
  id,
  case
    when row_number() over (partition by upper(name) order by id) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;


AWS DMS - How to select different databases for source and target points for a task Does aws DMS(Data Migration service) allow to select different databases  for source and target points for a task in a replication instance? My source engine is MySQL.
example: I want to move table A from database D1 to D2. but i am not able to do in DMS.

I got the Solution! yes it(AWS DMS) allows to move tables from database D1 to D2. just need to configure transformation rule section properly in JSON section. Plese refer below link within section "Example Rename a Schema"

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.html


Merging Results while avoiding Key Constraint Error? I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table)

In one of the foreign tables two rows are merged and this table is updated to match the changes which can result in two rows being duplicates and cause the query to fail.

There has to be a better way to do the following: (which does work)

SELECT row1, row2
FROM table
WHERE row2 = @ID OR FK_Gmb_row2 = @ID2 GROUP BY row1, row2;

DELETE FROM table WHERE row1 = @ID OR row2 = @ID2;

-- process select in code and loop over results::
INSERT INTO table (row1, row2) VALUES (@val1, @val2)

So if FK_row had the values 2 and 3 merged Table 1 would go from

FK_row1 , FK_row2
-----------------
1       , 1
1       , 2
1       , 3
2       , 3

To

FK_row1 , FK_row2
-----------------
1       , 1
1       , 3
2       , 3

I feel like I am using this solution because I do not properly understand something about the database system.

If I'm following you correctly, you can do what you need in a single MERGE statement, if you want to be cute about it.  I suspect you'd get better performance with separate small INSERT and DELETE operations.

CREATE TABLE #ManyMany
(
FK1 INT NOT NULL,
FK2 INT NOT NULL,
PRIMARY KEY (FK1, FK2),
UNIQUE (FK2, FK1)
)
INSERT INTO #ManyMany (FK1, FK2) VALUES (1, 1), (1, 2), (1, 3), (2, 3)

CREATE TABLE #Merges
(
OldPK   INT NOT NULL PRIMARY KEY,
NewPK   INT NOT NULL
)
INSERT INTO #Merges (OldPK, NewPK) VALUES (2, 3)

SELECT * FROM #ManyMany
MERGE INTO #ManyMany AS Existing
    USING
        (
        SELECT
            MM.FK1, COALESCE(M.NewPK, MM.FK2) AS FK2
        FROM
            #ManyMany AS MM
            LEFT JOIN #Merges AS M ON MM.FK2 = M.OldPK
        WHERE
            NOT EXISTS (SELECT * FROM #ManyMany AS Existing WHERE MM.FK1 = Existing.FK1 AND M.NewPK = Existing.FK2)
        ) AS New ON Existing.FK1 = New.FK1 AND Existing.FK2 = New.FK2
    WHEN MATCHED AND Existing.FK2 <> New.FK2 THEN UPDATE SET FK2 = New.FK2
    WHEN NOT MATCHED THEN INSERT (FK1, FK2) VALUES (FK1, FK2)
    WHEN NOT MATCHED BY SOURCE THEN DELETE;
SELECT * FROM #ManyMany


Maximize redo rate on replica side of an Availability Group My SQL Server databases are hosted on a highly spec'd converged platform with All Flash Storage Arrays, 40G network etc.

However, we're still unable to get more than 50MB/s Redo Rate - I need this to be in the GB/sec range.

The supporting platform can handle this - what is the bottleneck within SQL Server 2012 that stops it from maximizing the potential throughput and how can I overcome it? Even with a single-threaded process 50MB/s is poor - and totally useless given the amount of data we process.

Updates:
When I'm on the server I can copy files between drives and get speeds > 1000MB/s.
After reboots I see Redo Rates spike as high as 300MB/s, so I know its possible in my environment, however it quickly reduces to its norm of 50MB/s even when the Recovery queue is sitting at 400G. That takes 8 hours to drain - leading to very unhappy Clients...

what is the bottleneck within SQL Server 2012 that stops it from maximizing the potential throughput and how can I overcome it?

That was a major work item in SQL 2016.

  The result is a design for SQL Server 2016 that provides high
  availability for the most demanding workloads on the latest hardware
  with minimal impact and scalable for the future. Our design for SQL
  Server 2012 and SQL Server 2014 is still proven and meets the demands
  for many of our customers. However, if you are looking to accelerate
  your hardware, our Always On Availability Group design for SQL Server
  2016 can keep pace.

See SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged


Can upgrade from MariaDB 5.5 to MariaDB 10.3 directly? We have a database running in MariaDB 5.5.60 and we want to upgrade it to the last 10.3.7 version.

However I'm looking at MariaDB knowledge base ( https://mariadb.com/kb/en/library/upgrading/ ) and I'm not sure if we can perform a direct in-place upgrade from 5.5 to 10.3, or we should perform a "step by step" upgrade (5.5 > 10.0 > 10.1 > 10.2 > 10.3 ).

All the documentation I see in the oficial MariaDB knowledge base seems to suggest to do a in-place step by step upgrade (furthermore, looks like a logical upgrade with mysqldump cannot be permormed from 10.2 to 10.3), however I've seen some other examples in blogs where a direct in-place upgrade from 5.5 to 10.2 seems possible ( http://www.linuxtopic.com/2017/10/upgrade-mariadb-5.2-to-10.2.html ).

Any help?
Regards

The whole jump it is possible (depends on your data structure) but is not recommended and based on official documentation is not supported.

The best option is to perform and step-by-step IN-PLACE upgrade so you will only do a mysqldump before start to backup everything then just install every version and run mysql_upgrade then continue with next version and so on.


I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why? I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into.

I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a moderately high volume (300-500 INSERT/s).

I have a PERL script running on a cron job every 5 minutes that aggregates data from the last time it ran. It keeps track of the last record it processed by storing the auto_increment value of the last row in a metadata table.

The aggregates are always off. But not by much. This has been so frustrating because it's just plain simple math (SELECT account_id,sum(sold) GROUP BY account_id). I have a suspicion that it has something to do with the transaction isolation (repeatable-read).

I recently found FlexViews which looks very cool and might address my problem. But I was wondering if anyone could:

Tell me what is going on here. Why is this happening?
What can be done to produce 100% accurate aggregates

I'm like 95% sure the auto_increment thing along with transaction commit ordering is hosing me up, but I don't understand enough about DBs to really ask the right question.

Oh, one thing to note, I've already checked over the field types. This issues isn't the result of rounding.

Using REPEATABLE-READ means that your SELECT transaction gets to view a "snapshot" of the state of data as of the moment the transaction started.  Even if other sessions subsequently insert more data, the SELECT transaction will not see them.  That's the way it's supposed to work.

So your query is returning 100% accurate results, with respect to the data that existed at a specific point in the sequence of INSERTs.  But you are rapidly changing the data, so your SELECT is bound to return an outdated report.

Using another transaction isolation level probably won't help.  InnoDB refreshes a transaction's snapshot only at the beginning of a statement.  In other words, if the SELECT takes 0.2 seconds, and during that time another 100 INSERTs happen, those 100 INSERTs won't be included in the result of the SELECT.

http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html says:

  With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

The only way you can ensure your SELECT is totally in sync with the latest committed data is to prevent any new data from committing while the SELECT is executing.  You can lock the tables temporarily with SELECT ... LOCK IN SHARE MODE.  See http://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

But even if you do this, as soon as you finish that transaction, the lock is released and new INSERTs start committing, making the result of your SELECT out of date almost instantly.


Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR? We use pingdom and a few internal heartbeat mechanisms on our product.

We would like to add a 'are our slaves running and in good shape' check.

What fields in SHOW SLAVE STATUS indicate that the slave where SHOW SLAVE STATUS was executed on is not (or is) replicating correctly?

The 2 we are currently testing are:

Slave_IO_Running (must equal Yes)

Slave_SQL_Running (must equal Yes)

Seconds_Behind_Master (must be integer, and less then 10 seconds behind master)

Any others we should be testing?

Thanks again!

SCENARIO #1
You may want to monitor Relay_Log_Space. It represents the some total of relay logs the Slave has to process. When should you worry about it?
Give this scenario

Slave_IO_Running is Yes
Slave_SQL_Running is No

This indicates that that the IO Thread is still downloading entries from its Master's binary logs. The longer you take to address the SQL error, the more the Relay_Log_Space will pile up. If the Master is in a heavy-write environment, relay logs will pile up quickly and puts the Slave at risk for disk space issues.
If you do not want to monitor Relay_Log_Space, you can always set the relay_log_space_limit. This force a cap on the amount of disk space allow for relay logs. For example, if you set this:
[mysqld]
relay_log_space_limit=8G

and restart mysql, this causes the IO Thread to stop collecting new entries from the Master when the sum of all relay log file sizes >= 8GB.. After the SQL Thread is done processing the oldest relay log, that relay log gets rotated out and the IO Thread starts getting entries from the Master where it left off. This provides a decent throttling mechanism for relay logs.
Another field you may want to look at is Last_SQL_Errno. Once Slave_IO_Running is Yes
and Slave_SQL_Running is No, the Last_SQL_Errno will be set to something nonzero. On very rare occasions, it is possible to have this:

Slave_IO_Running is Yes
Slave_SQL_Running is No
Last_SQL_Errno is 0

When you have this unique phenomenon, mysqld has encountered an undocumented error (Don't laugh, I have seen this).
SCENARIO #2
Something else to keep in mind (not necessarily for monitoring)
Give this scenario

Slave_IO_Running is No
Slave_SQL_Running is No

Under what three circumstances would this occur?

Someone ran STOP SLAVE; by hand or in a backup script
The option skip-slave-start is in /etc/my.cnf and mysql was started
Issuing service mysql start --skip-slave-start or service mysql restart --skip-slave-start

Make sure not to monitor false positives should both replication threads are not running.
SCENARIO #3
Give this scenario

Slave_IO_Running is No
Slave_SQL_Running is Yes

This quickly indicates that there is a communication error because of one of the following

The Master went down
mysqld on the Master went down
Network Intermittency


insert trigger in sql server to linked oracle i have created a linked server to oracle in sql server and i want to write one trigger on table in sql server to insert the data into oracle table. For example the table a is in sql server and table b is on oracle. If i insert record in table a the values needs to be copied or inserted into table b of Oracle.
both the tables have same structure
table a
id varchar2(10);
name varchar2(10);
table b
id varchar2(10);
name varchar2(10);

The answer is a "simple" trigger as I've outlined below for you.  I didn't have enough information to name it properly, but hopefully you will be able to fill in the gaps.  This will fire AFTER the insert operation on the SQL side, then insert the relevant records to the Oracle linked server (assuming it's setup correctly).
However, a few words of advice from someone whose environment does this kind of thing a lot (using triggers to shove data from A to B).
Depending on what kind of client is accessing this, it will escalate to a distributed transaction, which may or may not work for all clients.  If it doesn't work then you will need to change this to using a cursor (or find some way to make distributed transactions work for your environment).  But don't test with just management studio, use the application(s) in your environment as well.
There is no way to catch an error in here (not even in a try/catch block).  If the insert into Oracle fails, then the transaction that wrote the data to TableA will also fail.  This may or may not be what you want to happen, but it is what will happen and there is nothing you can do to stop it.
Finally, if a delay is acceptable and you only need it on INSERTED data, then might I suggest using an agent job to run periodically, query the Oracle side for the highest value id, then send over everything in TableA that is greater than that id.
CREATE OR ALTER TRIGGER trg_TableA ON dbo.TableA
AFTER INSERT
AS
BEGIN

    INSERT INTO OracleLinkedServerName.SchemaName.TableB
        (id, [name])
    SELECT I.id, I.[name]
    FROM INSERTED AS I

END


SQL Server TempDB size issue SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet

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

  Determining the Amount of Free Space in tempdb

  The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

    SELECT SUM(unallocated_extent_page_count) AS [free pages],
    (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
    FROM sys.dm_db_file_space_usage;

This is the result of the query:

free pages           free space in MB
-------------------- ---------------------------------------
1272                 9.937500

The strange thing is that when i go and check it in the ui of the ssms the size and the free space is perfectly fine

So here are my questions:

Why are they reporting two different sizes. Is this due that the script actually counts the page file size rather than "hole size"?
Why is the script dividing the sum by 128?

Any answer is greatly appreciated.

You may want to consider using the query that will show you free space in the log and free space in the data file. If you add them up you'll get the similar value as the SSMS is showing.

This is an excellent query which does just that:

DECLARE @log_used DECIMAL(19,7);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;

DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.[partition_id] = a.container_id;

;WITH x(t,s) AS
(
  SELECT [type] = CASE
    WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END,
    size*8/1024.0 FROM sys.database_files AS f
)
SELECT
  file_type = t,
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END
FROM x;

Query Source and answer to similar question

By the way, it is usually good idea to split data file in your tempDB into number of equally sized chunks (number depends on the number of cores). It tends to help performance a lot. Just don't split the log file (it will do more harm than good).


How to change database encoding in an Amazon RDS Postgresql instance? I need to create a database with SQL_ASCII encoding in Amazon RDS PostgreSql database instance.

Since template0 and template1 are both with utf8 encoding, I get the following error

$ createdb -h endpoint.rds.amazonaws.com -U user -E SQL_ASCII -T template0

createdb: database creation failed: ERROR:  encoding "SQL_ASCII" does not match locale "en_US.UTF-8"

DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

In previous versions of Postgresql, template0 had no predefined encoding, so that command gave no errors.

Also, I don't have permissions to change template1's enconding:

postgres=> update pg_database set datistemplate = FALSE where datname = 'template1';
ERROR:  permission denied for relation pg_database

Although the \l command says my user is the owner of template1

Any ideas?

You must pass a locale that matches your desired encoding and is supported by the host as well. For example, if you don't want localised collations etc, you could pass --locale=C. Otherwise you might want --locale=en_US.ISO-8859-1 (or just en_US).


How to output more than 4000 characters in sqlcmd I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this:

restore database [DB]  from disk='F:\FULL1.bak' with norecovery
restore database [DB]  from disk='F:\DIFF1.bak' with norecovery
restore log [DB]  from disk='F:\log1.bak' with norecovery
restore log [DB]  from disk='F:\log2.bak' with norecovery
...
restore log [DB]  from disk='F:\logN.bak' with norecovery
restore database [DB] with recovery

Througout the SP, the lines get added to a parameter @sql varchar(max) which, at the end, gets printed with print(@sql), and if the user specified the right flag,  gets executed with exec(@sql). Both work perfectly. However, when calling the SP with sqlcmd like this:

set restoresql=exec restoredatabase @DB='Databasename', @Execute='y'
sqlcmd -h-1 -S SRV\T2 -U sa -P sa -d master -Q "%restoresql%" -o output.txt

The output file output.txt will always have a maximum of 4002 characters.

I've tried adding -y 8000 but that doesn't help. How can I get the output file to give me the full generated script?

To be clear, the issue is really with the PRINT command and not the SQLCMD utility.

I am guessing that your variable is actually NVARCHAR(MAX), not VARCHAR(MAX) since the PRINT command is limited to only 4000 characters using NCHAR / NVARCHAR. Otherwise it can output up to 8000 characters using NVARCHAR / CHAR. To see that VARCHAR does go beyond 4000 characters, but not beyond 8000, run the following:

sqlcmd -Q "DECLARE @Yo VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX), '#'), 9000); PRINT @Yo;" -o out.txt

If you need more than 8000 characters printed, then you need to iterate over the variable in chunks of up to 8000 characters (the PRINT command will only display up to 8000 CHAR / VARCHAR characters at a time). But you don't want to simply chop it into even 8000 character chunks as that could be right in the middle of something that should not be split, like a word / object name / number, etc. Since the PRINT command will always include a newline character at the end, it is best to print up to the last newline character of each 8000 character chunk. The result is that the only lines to get split across multiple displayed lines are those that are over 8000 characters before the next newline character, and there is really nothing that can be done about that.

Try the following proc in place of your print(@sql) command (FYI: you don't need parenthesis around @sql for PRINT like you do for EXEC). Please note that finding the last newline character in a string requires creating a LastIndexOf function since one is not built into T-SQL. You can create a T-SQL UDF to do this. You can also use SQLCLR in which case you can code it yourself or simply get the Free version of the SQL# library (which I am the author of).

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

IF (OBJECT_ID(N'dbo.Display') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.Display;
END;

GO
CREATE PROCEDURE dbo.Display
(
  @TextToDisplay VARCHAR(MAX)
)
AS
SET NOCOUNT ON;

  DECLARE @Length INT = LEN(@TextToDisplay),
          @Buffer VARCHAR(8000),
          @BufferLength INT,
          @StartIndex INT = 1,
          @LastNewlineIndex INT;

  SET @TextToDisplay = REPLACE(@TextToDisplay, CHAR(13), ''); -- normalize

  WHILE (1 = 1)
  BEGIN
    SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 8000);
    SET @BufferLength = DATALENGTH(@Buffer);

    IF (@BufferLength < 8000)
    BEGIN
      BREAK;
    END;

    SET @LastNewlineIndex =
                   SQL#.String_LastIndexOf(@Buffer, CHAR(10), @BufferLength, 1);

    IF (@LastNewlineIndex > 0)
    BEGIN
      PRINT SUBSTRING(@Buffer, 1, (@LastNewlineIndex - 1));
      SET @StartIndex += @LastNewlineIndex;
    END;
    ELSE
    BEGIN
      PRINT @Buffer;
      SET @StartIndex += @BufferLength;
    END;
  END; -- WHILE (1 = 1)

  -- Don't print empty line if final chunk was 8000 chars leaving final loop with 0
  IF (DATALENGTH(@Buffer) > 0)
  BEGIN
    PRINT @Buffer;
  END;
GO

Tests:

DECLARE @Test VARCHAR(MAX);
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7998) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1.1' + REPLICATE('a', 7000) + '1.1' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.2' + REPLICATE('a', 400) + '1.2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.3' + REPLICATE('a', 4000) + '1.3' + CHAR(13) + CHAR(10);
SET @Test = @Test + '2' + REPLICATE('b', 1798) + '2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';

NOTES:

You could get away using the built-in CHARINDEX function to look for each next newline, and in this respect it would function almost like using a string splitting function. The difference between what is needed here and doing a simple split on newlines is that any element returned that is over 8000 characters will still need to be split into chunks of no more than 8000 characters.
In order to handle NVARCHAR(MAX) instead of VARCHAR(MAX), use the code that I posted to PasteBin: T-SQL Stored Proc to PRINT NVARCHAR(MAX) values
There is a stored procedure, Util_Print, in the SQL# library that handles NVARCHAR(MAX) and has some functionality beyond the T-SQL code shown above, but that is only available in the Full (i.e. paid for) version, not in the Free version. But the code shown here will handle most cases.


COUNT with set DIFFERENCE and UNION Fairly simple question that I can't seem to find an answer to - I'm working with unions and differences, and I would like to perform a COUNT on the results. Currently I'm having to pipe out to a file and "wc -l" the file (minus 4 for the Postgres printing). There's got to be a way to include COUNT...

SELECT tbl1.id EXCEPT (SELECT tbl2.id UNION tbl3.id);

I would like to know the number of results after the set difference. Thanks for any input!

Here you go:

SELECT COUNT(*) FROM (
    SELECT tbl1.id
    FROM tbl1 EXCEPT
    (
    SELECT tbl2.id
    FROM tbl2

    UNION

    SELECT tbl3.id
    FROM tbl3
    )
) AS t;


Calculating Days Past Due I have a data set similar to the one below. What I'm trying (and failing) to do is calculate the number of days past due each payment is on any given day between the first payment and today.

But here's the catch, each amount paid must be deducted from the earliest amount due first then anything left over will be deducted from the next amount due.

So in the example below the earliest payment overdue would be row 1 until this is fully paid, which is on the 26/08/16 then row 4 would become the earliest payment overdue. This is because the cumulative amounts paid up to an including the 26/08/16 would be £2656.15 which would cover amounts due on rows 1,2 and 3.

RowNumber   AccountNumber   date        AmountDue   AmountPaid
1           1000            05/11/2015  1422.5      0
2           1000            26/11/2015  474.17      0
3           1000            26/12/2015  474.17      0
4           1000            26/01/2016  474.17      0
5           1000            26/02/2016  474.17      400.31
6           1000            26/03/2016  474.17      474.17
7           1000            26/04/2016  474.17      0
8           1000            26/05/2016  474.17      0
9           1000            26/06/2016  474.17      474.17
10          1000            26/07/2016  474.17      0
11          1000            26/08/2016  474.17      1307.5
12          1000            26/09/2016  474.17      0
13          1000            26/10/2016  474.17      0
14          1000            26/11/2016  474.17      434.17
15          1000            26/12/2016  474.17      434.17
16          1000            26/01/2017  474.17      0

Any help would be very much appreciated!

The way I solved this was assembling these pieces:

Using a cte with running totals for AmountDue and AmountPaid
Using outer apply() to get the earliest paid date and final days overdue for each account using the running totals

DaysOverDue is either the final amount of days overdue as of the day it was completley paid off, or if not yet paid off, as of @Date which is set as current date in the query below.

PaidDate is the first date for an account where the RunningAmountPaid >= RunningAmountDue

update from comment based on additional issue not in the original question:

Added an outer apply() in the cte to check if the next row for an account had a negative value for AmountPaid and if so, added that to the RunningAmountPaid.

This cleared up the issue of an amount being marked as paid when the next transaction was a reversal of the previous payment.

rextester link: http://rextester.com/YTHG73937

test setup:

create table t (
    RowNumber int
  , AccountNumber int
  , [date] date
  , AmountDue decimal(9,2)
  , AmountPaid decimal(9,2)
  );

insert into t values
 (1,1000,'2015-11-05',474.17,474.17)
,(2,1000,'2015-11-26',474.17,474.17)
,(3,1000,'2015-12-26',474.17,474.17)
,(4,1000,'2015-12-27',0,-474.17)
,(5,1000,'2016-01-26',474.17,0)
,(6,1000,'2016-02-26',474.17,0)
,(7,1000,'2016-03-26',474.17,474.17)
,(8,1000,'2016-04-26',474.17,0)
,(9,1000,'2016-05-26',474.17,0)
,(10,1000,'2016-06-26',474.17,474.17)
,(11,1000,'2016-07-26',474.17,0)
,(12,1000,'2016-08-26',474.17,1307.5)
,(13,1000,'2016-09-26',474.17,0)
,(14,1000,'2016-10-26',474.17,0)
,(15,1000,'2016-11-26',474.17,434.17)
,(16,1000,'2016-12-26',474.17,-434.17)
,(17,1000,'2017-01-26',474.17,0);

query:

declare @Date date = convert(date,getdate());

with cte as (
    select
        *
      , RunningAmountDue = sum(AmountDue)  over (order by date)
      , RunningAmountPaid= sum(AmountPaid) over (order by date)
                         + isnull(x.Reversal,0)
      , AccountBalance   = sum(AmountDue)  over (order by date)
                         - sum(AmountPaid) over (order by date)
    from t
      outer apply (
          select top 1
              Reversal = i.AmountPaid
            from t as i
              where i.AccountNumber = t.AccountNumber
                and i.RowNumber = t.RowNumber+1
                and i.AmountPaid < 0
        ) as x
    )

    select
        o.RowNumber
      , o.AccountNumber
      , Date                = convert(varchar(10),o.Date,120)
      , AmountDue           = convert(varchar(30),o.AmountDue,1)
      , AmountPaid          = convert(varchar(30),o.AmountPaid,1)
      , RunningAmountDue    = convert(varchar(30),o.RunningAmountDue,1)
      , RunningAmountPaid   = convert(varchar(30),o.RunningAmountPaid,1)
      , AccountBalance      = convert(varchar(30),o.AccountBalance,1)
      , PaidDate            = convert(varchar(10),x.PaidDate,120)
      , DaysOverDue         = datediff(day,o.Date,isnull(x.PaidDate,@Date))
    from cte o
      outer apply (
          select top 1
              PaidDate = i.date
            from cte i
            where i.AccountNumber=o.AccountNumber
              and i.RunningAmountPaid>=o.RunningAmountDue
            order by i.date asc
      ) as x;

results:

+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
| RowNumber | AccountNumber |    Date    | AmountDue | AmountPaid | RunningAmountDue | RunningAmountPaid | AccountBalance |  PaidDate  | DaysOverDue |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
|         1 |          1000 | 2015-11-05 | 474.17    | 474.17     | 474.17           | 474.17            | 0.00           | 2015-11-05 |           0 |
|         2 |          1000 | 2015-11-26 | 474.17    | 474.17     | 948.34           | 948.34            | 0.00           | 2015-11-26 |           0 |
|         3 |          1000 | 2015-12-26 | 474.17    | 474.17     | 1422.51          | 948.34            | 0.00           | 2016-03-26 |          91 |
|         4 |          1000 | 2015-12-27 | 0.00      | -474.17    | 1422.51          | 948.34            | 474.17         | 2016-03-26 |          90 |
|         5 |          1000 | 2016-01-26 | 474.17    | 0.00       | 1896.68          | 948.34            | 948.34         | 2016-06-26 |         152 |
|         6 |          1000 | 2016-02-26 | 474.17    | 0.00       | 2370.85          | 948.34            | 1422.51        | 2016-08-26 |         182 |
|         7 |          1000 | 2016-03-26 | 474.17    | 474.17     | 2845.02          | 1422.51           | 1422.51        | 2016-08-26 |         153 |
|         8 |          1000 | 2016-04-26 | 474.17    | 0.00       | 3319.19          | 1422.51           | 1896.68        | NULL       |         275 |
|         9 |          1000 | 2016-05-26 | 474.17    | 0.00       | 3793.36          | 1422.51           | 2370.85        | NULL       |         245 |
|        10 |          1000 | 2016-06-26 | 474.17    | 474.17     | 4267.53          | 1896.68           | 2370.85        | NULL       |         214 |
|        11 |          1000 | 2016-07-26 | 474.17    | 0.00       | 4741.70          | 1896.68           | 2845.02        | NULL       |         184 |
|        12 |          1000 | 2016-08-26 | 474.17    | 1307.50    | 5215.87          | 3204.18           | 2011.69        | NULL       |         153 |
|        13 |          1000 | 2016-09-26 | 474.17    | 0.00       | 5690.04          | 3204.18           | 2485.86        | NULL       |         122 |
|        14 |          1000 | 2016-10-26 | 474.17    | 0.00       | 6164.21          | 3204.18           | 2960.03        | NULL       |          92 |
|        15 |          1000 | 2016-11-26 | 474.17    | 434.17     | 6638.38          | 3204.18           | 3000.03        | NULL       |          61 |
|        16 |          1000 | 2016-12-26 | 474.17    | -434.17    | 7112.55          | 3204.18           | 3908.37        | NULL       |          31 |
|        17 |          1000 | 2017-01-26 | 474.17    | 0.00       | 7586.72          | 3204.18           | 4382.54        | NULL       |           0 |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+


Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails? On the PostgreSQL Concurrency With MVCC page, it says:

  know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation levels come in. Postgres basically supports two models that allow you to control how this situation should be handled. The default, READ COMMITTED, reads the row after the inital transaction has completed and then executes the statement. It basically starts over if the row changed while it was waiting. For instance, if you issue an UPDATE with a WHERE clause, the WHERE clause will rerun after the initial transaction commits, and the UPDATE takes place if the WHERE clause is still satisfied.

The docs seem to suggest that READ COMMITTED is still subject to failures and should be retried.

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?

No.

READ COMMITTED doesn't retry. Neither does SERIALIZABLE. The application is expected to retry transactions that suffer deadlocks, serialization failures, etc.

That description in docs is very misleading, I'll raise it on the docs list. PostgreSQL doesn't "start over" in READ COMMITTED at all, like some other DBs (e.g. Oracle) do. Instead it waits until the row it's waiting for commits or rolls back. If the other tx commits PostgreSQL reads the updated row, checks that it still matches any WHERE clause or other predicate, and then continues execution. The details are a bit arcane, see EvalPlanQual in the sources.

In either READ COMMITTED or SERIALIZATION the application should be able to re-issue a transaction. READ COMMITTED transactions can fail in ways that will succeed on retry, including:

Deadlock detection due to lock upgrades, lock ordering issues, etc
Administrative query cancel requests
Administrative shutdown/restart of the DB
Unplanned crash/restart of the DB
Connectivity interruption
... etc

SERIALIZABLE just adds some more failure cases.

Well written applications will cope with query failures and re-issue the transaction.


How do I determine if pgAgent is installed on a Postgres instance? I have inherited some problems and I need to determine if pgAgent exists on some number of installations (CentOS) and is running background jobs. These jobs could be run from cron, pgAgent, or an external caller.

If I am looking at a postgres instance and not using pgAdmin, how can I determine if pgAgent is installed?

Can this be done from the command line or from the psql client? I'd prefer that to pgAdmin if possible.

From Database setup:

  pgAgent stores its configuration in the ‘postgres’ database in your
  cluster

And it creates there a schema named pgagent with a handful of tables prefixed by pga_

It's probably good enough to connect to the postgres database and interpret the result of:

select 1  from information_schema.schemata  where schema_name='pgagent';

Due to how information_schema.schemata is restricted, the above should be run as postgres user (otherwise consider querying directly pg_namespace as the non-standard alternative).


Does SQL Server Clustering Enhance Performance? I'm not experienced with SQL Server, I'm a novice.

I'm a developer, and i think the question is more related to a Database Administrator.

I'm working on an old legacy system where over time the Database underneath this system has grown in schema and data over a number of years.

Some tables have around 600,000 records and there are over a hundred tables with a large number of relationships between them.

The systems performance is being severely affected,  as the number of users grows and more tables are added, the queries become larger.

The SQL Server is reporting timeouts daily and the number of timeouts is growing all of the time.

My colleagues have optimized the query plans and indexes in the SQL Server Instance to the best of their ability.

The system runs off one SQL Server Instance, running on a separate server to the where the application is hosted.

Is there any benefit from using a SQL Server Cluster to enhance performance.

I know that its beneficial in terms of maintaining availability if one Instance goes down the other takes over..

But would the workload be shared between SQL Server Instances, i.e queries / writes...

Most importantly would it reduce timeouts?

If there are better , more optimal methods to combat these timeouts, and advice would be great.

SQL Server cluster is a fault tolerance solution it has nothing to do with performance.

If you want your load on sql server to be shared , you should be looking at Replication or maybe log shipping, direct reporting calls to that replicated /log shipped server and keep application calls to the main server.

If your data gets too large that no matter what you do, performance is still a problem, consider archiving data to a data warehouse, in your application database (OLTP database) only keep recent records (last 6 months).  Anything older than 6 months gets shipped to your data warehouse (OLAP database).

The moral of the story is clustering your server isn’t going to give you any performance boost, consider an alternative option.


how to get ALL the objects in a database that are NOT FOR REPLICATION? “Not for Replication” is a property which can be set for different objects like check constraints,
Foreign Key constraints,
Triggers , Identity columns
etc while using SQL Server Replication.

What are ALL object types that can be NOT FOR REPLICATION?
How can I find them?

For those object types I have mentioned above I am putting a script together:

  identity columns

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
, i.name AS [Index]
, p.partition_number
, p.rows AS [Row Count]
, i.type_desc AS [Index Type]
,K.increment_value as IncrementValue
,K.last_value as LastValue
,K.seed_value as SeedValue
,k.is_nullable
,k.is_identity
,k.is_filestream
,k.is_replicated
,k.is_not_for_replication

FROM sys.partitions p

INNER JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id

LEFT OUTER JOIN sys.identity_columns K
ON P.object_id = K.object_id

where  1=1
  AND  i.index_id < 2 -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
ORDER BY [Schema], [Table], [Index]

  triggers

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,t.*

FROM sys.partitions p
INNER JOIN sys.triggers t
ON p.object_id = t.parent_id

  check constraints

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,c.*

FROM sys.partitions p
INNER JOIN sys.tables t
        ON p.object_id = t.object_id

INNER JOIN sys.check_constraints c
        ON t.object_id = c.parent_object_id

  foreign key constraints

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,fk.*

FROM sys.partitions p
INNER JOIN sys.tables t
        ON p.object_id = t.object_id

INNER JOIN sys.foreign_keys fk
        ON t.object_id = fk.parent_object_id

Please note that I have used sys.partitions and that is not a requirement for this question, but I like to have an idea of the space each object occupies and where it is physically located.

Row Level Security Policies have a NOT FOR REPLICATION property too, allowing the replication agents to bypass the policy.  But I think that's it, at least those are the only objects with is_not_for_replciation column in the catalog.

select object_name(object_id) object_name, c.name column_name
from sys.all_columns c
where c.name like '%replication%'
order by column_name


Finding posts with exactly set of tags per user I'm trying to determine the best way to find all posts that have an exact match with a list of user tags. For example, user has tag IDs: 1,5,9, and 11, while post has tag IDs 1, 5 and 9, thus this is selected as a valid post to display to the user.

The only way I could think of to do this is to count the number of unique tags for each post then count the number of tags a user has in common with the post, compare two values, if equal then it is a match (see query below). Is this truly the best way?

Schema:

Tags
Posts
Posts_tags (tag_id, post_id) unique enforced
Users
Users_tags (tag_id, user_id) unique enforced

Current query (broken down so more legible):
Sub-Query #1:

SELECT     COUNT(*) as numrows, pt.post_id AS id
FROM       users_tags AS ut
INNER JOIN posts_tags AS pt
ON         ut.tag_id = pt.tag_id
WHERE      ut.user_id = 1
GROUP BY   pt.post_id

Sub-Query #2:

SELECT   COUNT(*) AS numrows, post_id AS id
FROM     posts_tags
GROUP BY post_id

Master Query:

SELECT     t1.id, t2.id
FROM       (Sub-Query #1) t1
INNER JOIN (Sub-Query #2) t2
ON         (t1.numrows = t2.numrows)
AND        (t1.id = t2.id)

Also, would a graph database like neo4j be better at this type of query?

If you are using MySQL 5.6 or later, then the query will be reasonably efficient.  If using an older version, it is "order N*N", which is very slow for large subqueries.


Generalisation in databases I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c.

Is it better to create another table for doctors,one for nurses,etc or should I connect the appointment table, nurse_schedule, test_table, all directly to the staff table ??

First of all, if this is homework, please tag it as such.

Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand those requirements. If you don't completely and clearly understand the requirements, your schema is going to be miles off the mark.

And, to actually answer the question (at least as best I can with the information given), I'd keep the staff in one table, however you may want to create ancillary tables for each 'type' of staff member (doctor, nurse, janitor, admin, etc) to store data specific to that staff-member-type. To give an answer that's any more specific, the requirements need to be more specific.


Get missed transactions from slave I'm trying to write a disaster recovery plan, I have one way replication with 1 master(A) and 3 slaves(B,C,D)

     A
    /|\
   / | \
  B  C  D

Let's say my master crash, and I loose all data on that server, I still have 3 slaves, and I'll promote the most up-to-date to be my new master, let say slave B is up to date and C and D are not, at the point I promote B to master my slaves have the follow result for SHOW SLAVE STATUS\G:

slaveB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 1476884

slaveC> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 78684

slaveD> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000001
        Exec_Master_Log_Pos: 746848

In order to promote B to master, I want to apply all missing transactions on C and D, so before B become the new master and start to receive queries from applications, I've all slaves on a consistent state, how can I find the missed transactions from C and D on binary logs from B (I've the --log-slave-updates option enabled on all server).

For legacy issues, the envoirement use MySQL 5.0

The thing you need to do to change, what I will call, the reference point for replication.
Since Servers B,C, and D have binlogs enabled, this will be handy.
You gave
slaveB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 1476884

slaveC> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 78684

Let's sync up ServerC to be a Slave to ServerB
STEP 01 : Stop all writes to ServerC
STOP SLAVE;

STEP 02 : On ServerC, run SHOW MASTER STATUS;
Let's say the output was master-bin.000003 position 12345678
STEP 03 : On ServerC, run
mysqlbinlog master-bin.000003 > latest-binlog.sql

STEP 04 : Locate position from STEP 03 and show 3 lines before and after
grep -C 3 12345678 latest-binlog.sql

Let's say the timestamp was "2013-09-03 01:23:45"
STEP 05 : Find the timestamp of that position in the binary logs of ServerB
Let's say the binary log on ServerB is master-bin.000004
STEP 06 : Dump position matching timestamp inside ServerB's binary log master-bin.000004
SQLFILE=dumpstuff.sql
BINLOG="master-bin.000004"
DT2="2013-09-03 01:23:45"
DT1="2013-09-03 01:00:00"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" > ${SQLFILE}

STEP 07 : Locate the timestamp inside SQLFILE and find the position
Let's say the position is 87654321
STEP 08 : Connect SlaveC to SlaveB
On SlaveC, run this
STOP SLAVE;
CHANGE MASTER TO master_log_file='master-bin.000004',master_log_pos=87654321;
START SLAVE;

That's it !!!
CAVEAT #1
Repeats all these same steps to sync ServerD to ServerB
CAVEAT #2
If you have done these steps and still don't trust the data, please download Percona Tools and use pt-table-checksum to verify each Slave against the new Master. If there are differences, you can run pt-table-sync to generate the SQL to repair the differences.
UPDATE 2013-09-05 12:28 EDT
You just made this comment

On Step 2,3 and 4 , I don't have access to master binlog, I guess the way to get the latest applied transaction on slaveC is to extract it (using the Relay_Master_Log_File and Exec_Master_Log_Pos from show slave status) from slaveC relay log. Am I right? Steps 5 to 8 seems to be correct.

You can use mysqlbinlog remotely using --read-from-remote-server. Besides, you do not go back to the old Master, ServerA. You are doing these things among the Slaves, making ServerB the new Master. If you are using some manged hosting company, those admins need to be involved in this process. At the very least, they could provide some SSH tunnel access to permit you to access the Linux box and all necessary folders permissions. No MySQL authentication is needed.


What are the possible performance implications of dropping a not used ‘surrogate key’ column? I have a very large (>10m rows) table in a Postgres 9.5.4 database that collects readings from sensors. This table handles many inserts (~2k/minute) but isn't queried too often--only during a cache miss or when rebuilding the cache. Said table is essentially ‘append-only’--there are no updates. However, the queries that I do run are often fairly complex, and I care about optimizing read performance as cache misses can be very slow to a user.

In the business context of relevance, there are sensors that collect energy and power quality data from buildings; there can be multiple sensors for buildings, and a common query is aggregating facility level data. The table schema looks like so:

                                   Table "public.measurement"
    Column     |            Type             |                        Modifiers
---------------+-----------------------------+----------------------------------------------------------
 id            | integer                     | not null default nextval('measurement_id_seq'::regclass)
 meter_id      | integer                     |
 timestamp     | timestamp without time zone |
 value         | double precision            |
 _reading_type | smallint                    |
 facility_id   | integer                     |
Indexes:
    "measurement_pkey" PRIMARY KEY, btree (id)
    "ix_measurement_timestamp_meter_id__reading_type" btree ("timestamp", meter_id, _reading_type)
    "ix_measurement_timestamp_facility_id__reading_type" btree ("timestamp", facility_id, _reading_type)
Foreign-key constraints:
    "measurement_meter_id_fkey" FOREIGN KEY (meter_id) REFERENCES meter(id)
    "measurement_facility_id_fkey" FOREIGN KEY (facility_id) REFERENCES facility(id)

I am considering dropping the id column, which is a meaningless ‘surrogate key’, as I never, ever query it, but I'm curious what the performance implications would be.

I essentially run two different types of queries on this table:

"Give me all of the measurements between time start and time end of _reading_type 0 for meter_id X", or
"Give me all of the measurements between time start and time end of _reading_type 0 for facility_id X" (I believe the facility_id column denormalizes the table, but saves what was an expensive join).

Questions

What would happen if I do not declare a primary ke?
The combination of (timestamp, meter_id, _reading_type) is guaranteed to be unique, so: what would be the implication of dropping the multicolumn index on these columns and defining a composite primary key?

I have a very large (>10m rows)

Tables with billions of rows in them are common — don't fixate on the size of the table, it is much more important to know how many blocks you scan to query it, which depends on the query and on indexes. You do not want to consider partitioning, or anything exotic, just because you have 10 million rows in a table.

  I essentially run two different types of queries on this table:

  "Give me all of the measurements between time start and time end of _reading_type 0 for meter_id X", or
  "Give me all of the measurements between time start and time end of _reading_type 0 for facility_id X" (I believe the facility_id
  column denormalizes the table, but saves what was an expensive join).

Then your indexes would be better off as:

"ix_measurement_timestamp_meter_id__reading_type" btree (_reading_type, meter_id, "timestamp")
"ix_measurement_timestamp_facility_id__reading_type" btree (_reading_type, facility_id, "timestamp")

… or better still make them partial indexes where _reading_type=0:

create index on measurement(meter_id,"timestamp") where _reading_type=0;

Calling a column "timestamp" is not a good idea, change it if you can.

  I am considering dropping the id column, which is a meaningless ‘surrogate key’, as I never, ever query it, but I'm curious what the performance implications would be.

Any impact on performance is going to be minimal compared to tailoring your indexes to make them more selective.


How to select attribute from two indexes, both pointing at the same table? I have two tables, one with this form:

table teams:

ID, IDPupil1, IDPupil2
1   3000      3001

and the second this form:

table pupils:

ID,   Name,   Forename
3000  Smith   John
3001  Morgan  Lisa

IDPupil1 and IDPupil2 are referencing at pupils.ID.

Now I want to select all this values in one SQL-query.
I tried this:

SELECT teams.ID AS ID, IDPupil1, IDPupil2, pupils.Name AS Name, pupils.Forename AS Forename
FROM teams
LEFT JOIN pupils
ON IDPupil1 = pupils.ID
AND IDPupils2 = pupils.ID

The result is this:

ID,  IDPupil1, IDPupil2, Name, Forename
1    3000      3001      NULL  NULL

When I cancel the last row of the query (AND IDPupil2 = pupils.ID), then the Name and Forename of Pupil1 is shown. But with both indexes it obviously doesn't work.

So how can I get the following result?

ID,  IDPupil1, IDPupil2, Name1, Forename1, Name2, Forename2
1    3000      3001      Smith  John       Morgan Lisa

You need to joins to the pupils table twice: Once for IDPupil1 and once for IDPupil2:

SELECT
    t.ID AS ID, t.IDPupil1, t.IDPupil2, pupils1.Name AS Name1,
    pupils1.Forename AS Forename1, pupils2.Name AS Name2,
    pupils2.Forename2 AS Forename
FROM teams t
LEFT JOIN pupils pupils1
ON t.IDPupil1 = pupils1.ID
LEFT JOIN pupils pupils2
AND t.IDPupils2 = pupils2.ID


Amazon Redshift "cache lookup failed for aggregate" error when using median aggregate I recently ran into this error message and am posting an answer here so that the solution is easily searchable in the future.

Problem: The following query returns an error message when run on Amazon Redshift. What does it mean and does Amazon Redshift support the 'median' aggregate?

dev=# select median(5);
ERROR:  cache lookup failed for aggregate 2762

Amazon recently added support for the 'median' window function. If you forget that it's only a window function and try to use it like an aggregate, you'll get this error:

dev=# select median(5);
ERROR:  cache lookup failed for aggregate 2762

The solution is to rephrase the query to use it as a window function:

dev=# select median(5) over ();
 median
--------
   5

(1 row)


SQL Server Partitioning - Parent / Child tables I'm currently investigating implementing partitioning for a fairly hefty (800GB and growing) SQL Server database. All of the data in the tables is relatable to a date/time, however this date/time is often referenced in another table. For example, I have:

*Instances*
Timestamp (partition key!)
LocationID
...

*Events*
InstanceID
...

*Location*
ID
...

Here, instances contains the partition key. This references a location (LocationID > Location.ID). Also, there is the Events table which references the Instance (InstanceID > Instance.ID).

Is there any way I can apply partitioning to this structure without having to denormalise my tables and have the 'Timestamp' column copied everywhere?

I'm using SQL Server 2008 R2. Thanks in advance.

Well, I'm not saying this is pretty, but it might work.

First, partition the instance table by pseudo-datetime.  Each day/week/month, load the new instance information.  Then assign a new primary key id to this data.  Normally, you would want this to be an identity, but in this case, you have to guarantee uniqueness.  Now, place this into its own partition.

This is almost by date time.  However, other tables that reference instances will be able to join to the correct partition.

Now, if the events and locations are relatively small tables, then perhaps this is enough.

Otherwise, you can repeat for these tables.  However, it gets harder if you add a new event that refers to an older instance.  To handle this, you'll want to assign a new event id range to each partition, but leave a big gap at the end.  Then, when you load event data, you need to determine the correct partition to place it in, based on the partition of the instance id.  You may end up loading events into multiple partitions.

This is an idea.


SQL Native backup MIRROR TO performance We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups (We use Ola Hallengren's scripts).

Right now we are using robocopy to copy files over to a network share just in case our backups stored locally become corrupt, lost, or decide to take a nice long vacation. My question is, when we use the MIRROR TO option, is it any more/less resource intensive than just using robocopy, xcopy or a Powershell solution? What exactly happens when we use MIRROR TO in SQL Server? Looking for a bit of a 'nuts and bolts' answer, if possible.

I'm guessing here based on how I'd implement MIRROR TO:

Probably, SQL Server streams the backup data to two backup devices if you use MIRROR TO. This means that every block they read is written two times. The data stream has a fork.

What they are certainly not doing is write the data once and then copy it over. That would a) incur an additional read pass over the data and b) also open a window for data corruption while only a single copy of the backup exists. From an implementors standpoint it is less work to fork the stream and it has a better result. That's why I think they're doing that way.

This means that MIRROR TO is less resource intensive than copying the data files after the backup has succeeded. It also has less room for data corruption. Data corruption is rare but a practical concern. Memory, disk and network all can have undetected bit flips. (Yes, TCP does not 100% protect from that.)

Advice given the fact that Enterprise Edition will become available to you: Use MIRROR TO. It results in less resource usage and less potential for (undetected) backup corruption. It also automates stuff that you can get wrong when you are doing it manually. SQL Server is certainly tested better than most in-house script development. The only reason not to use it would be if your backup process cannot easily be modified to use it.

One additional point: If the mirror cannot be written the backup will fail. This can be a downside (you probably exceed your RPO objective). It can be an upside because errors are more likely to be detected.

As mentioned by Paul White in the comments: If one of the backup destinations is slow (or all data is flowing over the same saturated network link) the backup might take a lot longer than before. In case of a saturated network link it might be faster to locally duplicate the backup file on the target server.

This theory can be tested: Back up the database to two IO devices that have identical perf characteristics using MIRROR TO. If I'm correct you should observe only a tiny slowdown because the writes are streaming and parallel.

I performed this benchmark myself. Backing up from SSD (very fast) to two (almost) identical magnetic drives. Using data compression (but the CPU was not maxed out).

Mirror: 1:37.
No mirror: 1:10 to drive 1.
No mirror: 1:14 to drive 2.

This slowdown (1:37 vs. 1:14) is well within the tolerance of my theory. There seem to be some overheads but there certainly is no separate mirroring/copy phase. Using Process Explorer I observed IO measured in MB/sec. It was fairly constant. This also hints that there is no separate phase. Writes seem to be parallel to both drives.


What normal forms are these two relations in? I have a set of relations {A, X, Y, Z}:

Given that the functional dependencies:  AX→Y, AX→Z, Y→A, Z→X, I would think that this relation would be in the normal form 1NF, because there is a partial dependency for the composite key, YZ; Y determines A and Z determines X. However, I ran across this example on a university website claiming the relationship was in 3NF. Am I wrong about this relation being in 1NF?

On a similar note, given the same relation but with functional dependencies:  A→XY, Y→AZ, would this be in 3NF or BCNF, I do not see any reason why this would not be in BCNF, but I have seen a similar example where there was a further decomposition, that was also BCNF. So is the decomposition wasteful because the original relation with given dependencies was already in BCNF?

The relation is actually in 3NF (so that it is also in 1NF and 2NF). The reason is that each attribute of the relation is prime, that is, it belongs to a (candidate) key (the are four keys in this relation: (A X), (A Z), (X Y), (Y Z)).

The definition of the 2NF (which has only an historical interest), is the following (Database System Concepts, 6th edition, Korth, Silberschatz, Sudharshan):

  A functional dependency X → Y is called a partial dependency if there is a proper subset Z  of  X such that Z  → Y . We say that Y  is partially dependent on X. A relation schema R is in second normal form (2NF) if each attribute A in R meets one of the following criteria:

  • It appears in a candidate key.

  • It is not partially dependent on a candidate key.

so the relation respects this definition, since each attribute appear in a key.

While the definition of Third Normal Form is (same source as above):

  A relation schema R is in third normal form with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form  X → Y, where  X ⊆ R and Y  ⊆ R, at least one of the following holds:

  • X  →  Y is a trivial functional dependency.

  • X is a superkey for R.

  • Each attribute A in Y − X is contained in a candidate key for R.

so the relations respects this definition too.

For your second question, the relation has two keys, A and Y, and it is already in BCNF as well as in 3NF.

Note that the analysis algorithm to decompose a relation in BCNF should return the original relation, since no dependency violates its definition (each determinant is a superkey).


Create gist index on cube array with 512 dimensions I want to perform nearest neighbors search on db with 5000 entries of cube array with 512 dimensions.

I referred to this answer and used docker image of postgresql with cube array limit set to 2048 which can be found in the comment to the answer. But I'm getting the following error:

ERROR:  failed to add item to index page in "faq_c_idx"
SQL state: XX000

It works with dimensions=~200, but when I set higher dimensions I keep receiving the error above.

Here's the query:

CREATE TABLE mytable
AS
  SELECT i, cube(array_agg(random()::float)) AS c
  FROM generate_series(1,5000) AS i
  CROSS JOIN LATERAL generate_series(1,512)
    AS x
  GROUP BY i;

CREATE INDEX ON mytable USING gist ( c );
ANALYZE mytable;

It can't find a place to store the index tuple, probably because the tuple is larger than a page and so can't fit on any page.  You could recompile with a larger blocksize, e.g. ./configure --with-blocksize=32, but then you will just run into a different limitation, probably INDEX_SIZE_MASK, which is not so easy to work around.

So, you just can't do this.  But if you could, an index into a 512 dimensional cube is not likely to be all that useful anyway.  It will probably be slower than a seq-scan would be, or at least not faster by very much.


Service Broker - Communication not happening between servers despite ssbdiagnose saying all is well I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example:

http://blogs.msdn.com/b/sql_service_broker/archive/2008/08/26/securing-a-dialog-with-certificates.aspx?Redirected=true

A is SQL 2005, B is SQL 2012. It doesn’t work, and I have not been able to track down a good reason why. What I can see is:

The Profiler on the receiving end doesn’t even show any Service Broker events, indicating nothing is getting to it.

But using ssbdiagnose as below, it says congratulations, you have no errors:

C:\Users\me>ssbdiagnose -level info -E configuration from service ServerAServiceTest -S ServerA -d MyDB to Service ServerBServiceTest -S ServerB -d MyDB on Contract myContract

Microsoft SQL Server 11.0.2100.60
Service Broker Diagnostic Utility
0 Errors, 0 Warnings

Also in the transmission queue, the only thing I see that seems particularly off is ‘to_broker_instance’ is null, though I explicitly specified that info when setting up the route.

Further, no errors are showing up in transmission_status.

Also, SQL Server error logs are shedding no light.

As for firewall issues, well, these are test servers not accessible from the outside so I tried turning the firewalls off altogether.

One thing that is bothersome, I will get these:

An exception occurred while enqueueing a message in the target queue. Error: 15581 State: 7. Please create a master key in the database or open the master key in the session before performing this operation.

I do open the key and these go away. But I shouldn't have to repeatedly open the key everytime I want to do something, should I? I suspect this is part of the problem even though as mentioned the errors go away.

Sorry for the somewhat open question - even some help identifying where to get more informative errors or debugging info would be great. This is new territory for me.

Is the database master key encrypted by the service master key? If not, the db engine has no way to automatically open it. To check, take a look in sys.asymmetric_keys, specifically the pvt_key_encryption_type_desc column. If you find that it's not, you can make it so by doing

alter master key add encryption by service master key


Reasonable way to predict query performance over time I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database.

One of the first things we do in our ETL process is to check if the file has already been processed. We simply do a:

SELECT id FROM table1 WHERE basename = <basename>

If there is a result we skip the file, if there is no result we process the file. Right now this query takes ~250ms with ~5m records. We have a nonclustered index on the basename column already.

We will see about 100-200k records added per month. We get the files in batches. So we might see 2k files, and then 2 hours later another 2k files. Some days we will get 10k files, other days we might only get 4k files.

Keeping all other variables the same is there a rule of thumb for projecting when we might run into performance (queries taking longer than 1s) issues with this query other than inserting 15-20 million records into the table and seeing what happens?

Table DDL:

CREATE TABLE [dbo].[raw_records](
[id] [int] IDENTITY(1,1) NOT NULL,
[basename] [varchar](512) NULL,
[filename] [varchar](1024) NULL,
[file_size] [int] NULL,
[machine] [varchar](10) NULL,
[insert_timestamp] [datetime] NULL,
[raw_xml] [xml] NULL,
[process_status] [varchar](2048) NULL,

PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Index:

CREATE NONCLUSTERED INDEX [basename_index] ON [dbo].[raw_records]
(
    [basename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This table was created long before I started working on it, so I'm assuming someone just made filename 1024 maximum length to hold "enough". Certainly changeable.

The files that are produced have a timestamp and unique information within the filename itself (e.g. system1_metadata_timestamp.xml) one "system" couldn't produce (or should never) produce a file with the same timestamp.

select max(len(basename)), max(len(filename)) from dbo.raw_records;

returns: basename - 143, filename - 168. Probably a good thing to change down to a max of 260.

process_status probably doesn't need to be that long either, but I feel that one is reasonable to guess at, because the column is there to hold error messages from the processing phase. I ran a query on it and had a max of 600 characters. We don't typically query on that column though. It is just more informational for debugging.

I'm going through the application cleaning up stuff such as that. In certain places I can't get away from it but in others can't do much about it unfortunately (e.g. need to actually retrieve the XML column to extract data from it). This question simply stemmed from seeing the performance of the query in question and not wanting it to get away from me. It is the first thing that gets done for every file so if that doesn't work, nothing else will either.

With a good index present, the time taken to locate a matching row should scale roughly logarithmically, as long as you have room for the index in memory.
I'd make the index UNIQUE since the basename must be unique otherwise your workflow is invalid, and it makes the index more efficient.
CREATE UNIQUE INDEX IX_raw_records_basename
ON dbo.raw_records (basename);

Check the execution plan for the query to ensure the index is being used.
Make sure you have enough room in memory for the index, and assuming concurrency won't be a massive problem, you should be good for a very large number of rows.
I'd reconsider the length of the basename and filename columns since the query optimizer will use the length when calculating how much memory it will need to allocate to run the query.  If, for instance, the basename column will never hold more than 20 characters, but you have it defined as 512 characters, the memory grant for SELECT basename FROM dbo.raw_records; would be 25.6 times larger than actually required.  Column lengths are actually much more important than most people realize.
You could also change the query to be SELECT 1 FROM table1 WHERE basename = <basename> that way you wouldn't even need the id since all you're trying to do is verify its existence. Only do what you really need.  It looks like the index you show in your question would work fine for that.
In addition, if space is an issue you may want to consider data compression of the indexes and table.  This should allow the index to fit in a smaller memory footprint.  Evaluate DATA_COMPRESSION = ROW versus DATA_COMPRESSION = PAGE to see which is the best compression method for your requirements.


why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist I am trying to run following command:

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db

I get:

failed: FATAL:  database "new_db" does not exist

This is because it is the way pg_restore works.

pg_restore manual reads as follow :

  -C, --create
             Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before
  connecting to it.

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE
  commands. All data is restored into the database name that appears in the archive.

The -d will restore in the given database if and only if -C is not used. If -C is used, the database is used as a "launchpad", not as the destination.


How to enforce that one row in a parent table MUST be associated with at least a row in a child table I have a one to many relationship enforced via FK, classic example order and order_item, whereby a fk on the order_item to the order table enforces that one item belongs to one order, and one order CAN have 1 or more item. I'd need to change the "CAN" to "MUST", basically any order must at least have one item associated with it.
What would be the best way to enforce this.
Many thanks

There is no declarative referential integrity to enforce one or more children in SQL Server.  You have to enforce this with application logic.

Note too that if you are using a foreign key constraint on the child, you can't save the child until the primary key of the parent is known.  This means that you need to have a transactional wrapper (BEGIN TRAN / COMMIT) around the insert of the parent and it's first child, so that the parent is not kept unless the child is also successfully inserted.


Query to insert data into columns and check for pre-existing values for those columns data I have table t1 and t2 with same column names in both tables, column a and b. I want to enter data of column a and b from t2 into t1 at the same time check weather  any data pre exist in t1. If data exist already in a and b column then don't enter that data.
The main challenge in my case is , I want to use combination of (a and b) column data to check for pre existing values.
Thanks for your precious time in advance.

SQL Server:

INSERT INTO t1 (a,b)
SELECT a, b
FROM t2
EXCEPT
SELECT a, b
FROM t1

The EXCEPT operator compares the result sets of two queries and eliminates those rows in the first result set that match in the second result set. It uses an exact match to identify and eliminate rows.

There are other options of course, such as MERGE or using NOT EXISTS in your WHERE clause, however, for a simple query such as this EXCEPT will likely perform better.


What does NVL stand for? What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).

Quite simply: Null VaLue

The function substitutes any NULLs in a given resultset column with the value given as the second parameter.


Entries in suspect_pages but checkdb shows no error I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

Yesterday, I got two entries in "suspect_pages" for the same database. One of event type 1 and one of type 2

  1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).

  2 = Bad checksum.

database_id file_id page_id eventtype   error_count last_update_date
8           1       1482057 1           1           2019-11-14 14:40
8           1       1482057 2           1           2019-11-14 14:40

I found the object related and they both point to the same table on the database.

DBCC TRACEON (3604);
DBCC PAGE (8, 1, 14823057, 0);
DBCC TRACEOFF (3604);

I had a valid backup of before the corruption and couldn't afford a down time so I took a backup of the corrupted database, restored my backup on a new name. I dropped the corrupted table and then recreated it from the valid backup.

Today, I restored the corrupted database backup that I took yesterday on a test server and when I run a full checkdb, it detects no corruption.

  DBCC CheckDB() WITH No_INFOMSGS, ALL_ERRORMSGS

How is it possible that the backup I took from a corrupted database (according to suspect_pages) doesn't have any problems? Can those entries in suspect_pages be a false positive?

Database Compatibility Level is 130 (SQL 2016) Our SQL Server is running on Windows Server 2012.

So I wrote to Brent Ozar and this is the answer I got from him. It's the best answer I've got so far so I'll put it here:

  here's exactly what I do when SQL Server reports corruption:
  https://brentozar.com/go/corruption

  Suspect_pages doesn't do false positives, but the corruption may have been repaired since (or may have only been corrupt temporarily, like if storage had a transient error when you went to go read it the first time.) Definitely follow that checklist and star tthe support call with MS.

  Hope that helps!
  Brent


What are the limits on the amount of slaves per master? I currently have a Master with 2 slaves, all running MySql 5.5.

What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?

There are no limits in terms of settings, but you need to be cognizant of some aspects.

You could use semisynchrnous replication as it uses a nice algorithm for shipping SQL to Slaves by making sure at least one server has all the latest SQL in the Slaves relay logs. (See my August 5, 2011 post Is MySQL Replication Affected by a High-Latency Interconnect?)

However, semisynchronous replication is only good for one out of N Slaves.

Whether you have semisynchronous replication or not, it would suffice to say that the more Slaves you have, the more CPU processing a Master has to do to keep its Slaves up-to-date.

For a Master with N Slaves, that's...

N SQL Statements to send to Slave
N Acknowledgments from Slaves 1-N that SQL was Executed

With semisynchrnous replication, it is not much better because For a Master with N Slaves, that's ...

N SQL Statements to send to Slave
Acknowledgments from Slave

1 Acknowledgment that a Slave 1 received the latest SQL
N-1 Acknowledgments from Slaves 2-N that SQL was Executed

If you have to have many Slaves, think of using a Distribution Master in a Star Topology

Best way to setup master to multi master replication
Is it a good idea to split MySQL DB into two servers
Infrastructure for Highly Concurrent, High Write DB
https://stackoverflow.com/a/5784578/491757

If you cannot setup a Distribution Master in a Star Topology, then here is my simple rule: Use Semisynchrnous Replication but use as few Slaves as possible.


Backup job doesn't write backup when run on schedule We have a database that is backed up to a network drive by a job scheduled to run at 7 AM everyday. The issue is the backup doesn't get created; however, when I execute the job manually, it gets created just fine.

It's not like the scheduled job fails either. If I look at the job history, it indicates that the job ran fine and the backup database operation succeeded.

I don't think it's a permissions issue writing to that folder either since all the other backup jobs also execute using the same user and they run just fine.

Any suggestions?

Does anything show up in the backupset tables for the missing backups?

SELECT  TOP 5
        bs.database_name,
        bmf.physical_device_name,
        bs.backup_finish_date,
        BackupSize = CONVERT( VARCHAR( 16 ),
            CONVERT( DECIMAL( 18, 4 ), bs.backup_size * 1.0 / 1024 ) ) + 'KB',
        AfterCompression = CONVERT( VARCHAR( 16 ),
            CONVERT( DECIMAL( 18, 4 ), bs.compressed_backup_size * 1.0 / 1024 ) ) + 'KB',
        bs.type
FROM    msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
    ON  bmf.media_set_id = bs.media_set_id
WHERE   bs.database_name = '<Your Catalog Here>'
    AND bs.type = 'D'
ORDER BY bs.backup_finish_date DESC;

I would wager you are right that it's not a permissions issue, as if it was, the job history would indicate it.  Similarly, if the network path was simply unavailable at 7:00AM, there'd be something in the job messages about it, even if the jobstep itself was set to always report success.

SELECT  ji.name, ji.step_name, ji.step_id, ji.run_date,
        ji.run_time, ji.run_status, ji.on_success_action, ji.on_fail_action, ji.message
FROM (  SELECT  sj.name, sjs.step_name, sjs.step_id, sjh.run_date, sjh.run_time,
                sjh.run_status, sjh.message, sjs.on_success_action, sjs.on_fail_action,
                Ordinal = ROW_NUMBER() OVER (
                    PARTITION BY sj.name
                    ORDER BY sjh.run_date DESC, sjh.run_time DESC )
        FROM    msdb.dbo.sysjobs sj
        INNER JOIN msdb.dbo.sysjobsteps sjs
            ON  sjs.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysjobhistory sjh
            ON  sjh.job_id = sj.job_id
            AND sjh.step_id = sjs.step_id ) ji
WHERE   ji.Ordinal <= 5
    AND ji.name = '<Your Job Here>'
ORDER BY ji.run_date DESC, ji.run_time DESC,
    ji.name, ji.step_id;

I'm guessing it's some subtle difference between the successful jobs and the one that's behaving oddly, like a conditional that's getting evaluated differently by the agent or similar code issue.


SELECT items that has one or more specific TAGS I have three database tables.

One with items, one with tags and one with items_has_tags.

Tags

CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'tag one'),
(2, 'tag two'),
(3, 'tag three'),
(4, 'tag four'),
(5, 'tag five');

Items

CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `items` (`id`, `name`) VALUES
(1, 'first item'),
(2, 'second item'),
(3, 'third item'),
(4, 'fourth item');

Item has tag

CREATE TABLE `item_has_tag` (
  `item_id` int NOT NULL,
  `tag_id` int NOT NULL,
) COMMENT='';

INSERT INTO `item_has_tag` (`item_id`, `tag_id`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 3),
(2, 4),
(3, 2),
(3, 3),
(3, 4),
(1, 2),
(4, 3),
(4, 4),
(4, 5);

I want to only select items that have all the specific tags, such as the items that item_has_tag.tag_id = 1 AND item_has_tag.tag_id = 2 AND item_has_tag.tag_id = 3 for example.

edit

The amount of specific tags varies from 1 to 4-5.

edit
It is the items list that needs to be returned.

Would you like to help me?

For a specific tags list you can simply calculate the amount of tags from this list attached to each item using

SELECT item_id, SUM(tag_id IN (specific_tags_list))
FROM item_has_tag
GROUP BY item_id

To obtain the list of items which have all of the tags in tags list you must compare calculated SUM() with the amount of tags in the list:

SELECT item_id
FROM item_has_tag
GROUP BY item_id
HAVING amount_of_tags_in_list = SUM(tag_id IN (specific_tags_list))

  items that item_has_tag.tag_id = 1 AND item_has_tag.tag_id = 2 AND item_has_tag.tag_id = 3 for example

For this case the query will be

SELECT item_id
FROM item_has_tag
GROUP BY item_id
HAVING 3 = SUM(tag_id IN (1,2,3))

PS. The fields pair (item_id, tag_id) must be defined as unique by index (in any order, maybe primary) in the table item_has_tag structure.

UPDATE

  Your code works very well but I get into trouble when I try to get the
  result from the items list SELECT items.* FROM items, item_has_tag
  GROUP BY item_has_tag.item_id HAVING 3 = SUM(item_has_tag.tag_id IN
  (1,2,3)) Error "SELECT list is not in GROUP BY clause"

Variant 1:

SELECT items.*
FROM items, ( SELECT item_id
              FROM item_has_tag
              GROUP BY item_id
              HAVING 3 = SUM(tag_id IN (1,2,3))
            ) subquery
WHERE items.id = subquery.item_id

Variant 2:

SELECT items.name /* , another fields */
FROM items, item_has_tag
WHERE items.id = item_has_tag.item_id
GROUP BY items.name /* , another fields */
HAVING 3 = SUM(item_has_tag.item_id IN (1,2,3))

Variant 3: disable session ONLY_FULL_GROUP_BY SQL Mode (not recommended).

PS. Variants 1 and 2 can be freely converted to JOIN form.


Comparison Tool for MySQL data base schemas We'd like to update an old database schema for a new software release that requires changes in database schema (additional colums, column renames, table renames, datatypes, ...). Is there any comparison tool for MySQL data base that can help to adapt the old schema to the new one without the loss of any stored data where possible? (free software preferred)

The changes can be made by hand, yes. But we're looking for a more comfortable way and to catch all the changes made.

This isn't the a very sexy answer but mysqldump & sdiff.

mysqldump -u root -p --no-data -h oldHost databases db1 db2 db3 > /tmp/old.sql
mysqldump -u root -p --no-data -h newHost databases db1 db2 db3 > /tmp/new.sql
sdiff /tmp/old.sql /tmp/new.sql

sdiff will give you a side by side comparison with differences paired w/ a > or < indicator.  I've found this to be useful in the exact scenario you describe.

The example supposes you have the new schema initialized on a different host, but this certainly isn't a requirement.  It could be a different instance (e.g. port) on the same host or the same instance with different database names.


Changing the administrative user on mysql from `root` to something else Just installed mysql on Ubuntu -

sudo apt-get install mysql-server
sudo apt-get install mysql-client

As part of the installation process, I provided a password for the root user.

I can now log in with

mysql -u root -p

But I'd love some other user besides root to own the mysql administration. I'm coming over from the world of Postgres and have never used MySQL before, so I'm wondering how I could change this user.

Thanks!

EDIT

This is based off a recommendation from the Wordpress installation guide:

  It is a safer practice to choose a so-called "mortal" account as your mysql admin, so that you are not entering the command "mysql" as the root user on your system. (Any time you can avoid doing work as root you decrease your chance of being exploited.)

Connect to MySQL as -u root, then

First, see what root has:

SHOW GRANTS;

Then add a new user, similar to the root:

GRANT ALL PRIVILEGES ON *.* TO 'foobar'@'localhost' IDENTIFIED BY ('long secure pwd') WITH GRANT OPTION;

where foobar is the "mortal" mentioned in your quote.

Then re-login as foobar to verify it.  Then get rid of root (or at least emasculate it).  Perhaps this

REVOKE ALL *.* FROM root@localhost;

Then verify with

SHOW GRANTS FOR root@localhost;
SELECT user, host FROM mysql.user WHERE user = 'root';

The first should show only GRANT USAGE, which lets root in, but without any privileges.  The second checks to see of root can get in from any other "host".  If there are any ways in, we can discuss zapping them.


Database structure for a bilingual dictionary I've search all over and can't find advice of a good database structure for a bilingual dictionary, how would you define the structure for an English-Spanish language bilingual dictionary?

In a document store:

{
    {
        "language": "English",
        "word": "cat"
    },
    {
        "language": "Spanish",
        "word": "gato"
    }
}

Of course you'll have to choose a working language for the keys.

In a graph DB, add each word from each language as a node. Label the nodes with the language. Alternatively have a separate node for the language itself and connect the word-node to the language-node. Then connect word-nodes in one language to the corresponding word-node in another language. Note that English has a lot of words imported from other languages so decide whether you have one word-node connected to several languages or separate word-nodes.

In a relational database I would have a table for the languages, one for word lists and a third for equivalences

table: Languages
    Language_name   primary key

table: Words
    language_name
    word

Both columns together make a composite primary key

table: Transaltions
    first_language_name
    first_word
    second_language_name
    second_word

The primary key is all four columns. During lookup should you compare the given word to first_word or second_word? Either maintain a conventions and stick with it (Spanish = first_, English = second_) or query both and deal with the few (but non-zero) collisions).

Some concepts do not translate word-for-word. This could be handled by renaming column "word" to "idea" or "phrase".

For multi-lingual (rather than just bilingual) lists having an over-arching concept which has word-forms in each language would be one way to go:

Table: Concepts
    concept_id

Table: Translations
    concept_id
    language_name
    word

To go from "cat" (English), find the corresponding row in Translations. From that find the concept_id. Then read the concept_id, language_name row in Spanish. Homonyms make this difficult, however.


Identify when Table Schemas were last Modified I'm trying to identify all tables that have had indices added/altered since January 15. Is this possible?

I've run:

select * from INFORMATION_SCHEMA.PARTITIONS where UPDATE_TIME >= '2019-01-15'

but this is returning tables that had their content updated, not structure.

Maybe there's a different INFORMATION_SCHEMA table I should use? I'm running mysql 5.6 if that makes a difference. I'm using myisam and innodb tables (majority are innodb).

When InnoDB add an index it adds a record to I_S.INNODB_SYS_INDEXES. INDEX_ID of a new index will be highest. Analyzing existing records you can get an idea in what order indexes where created, but that's it. INNODB_SYS_INDEXES doesn't contain any time fields.

On other hand, any DML will be logged to a binlog. If it's enabled and it hasn't been rotated yet you will see the ALTER TABLE with a timestamp.


Rank function creating duplicate partitions? (two rank 1's for the same key) The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back.

WITH rank_cte AS (SELECT  ref_number, RANK()
        OVER(
            PARTITION BY
                ref_number
            ORDER BY
                Logged_Date DESC
        ) AS "Rank1"
From my_table )

SELECT * FROM rank_cte
WHERE Rank1= 1 and ref_number = 'abcd'

What is likely to have caused this? SQL Server must think they are similar enough for the where clause to return both rows, but different enough to partition them.

Found the problem, there were ties in the rank ordering, hence why multiple rows are returned with the same rank id.

The solution will be for me to find an ordering key that has unique combinations of partition and order key.


Ensuring relationship between child tables exists prior to SQL insert I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.
I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.
The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I'm wondering if I can accomplish both in one query though. I'm not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.

Recommended scheme:
Existing tables (PK == Primary key):
user (user_id PK, etc.)
assignment (assignment_id PK, etc.)
test (test_id PK, etc.)

Relations tables (PK == Primary key, FK == Foreign key):
user_assignment (user_assignment_id PK, user_id FK, assignment_id FK, etc.)

This table creates the relation "user to assignment" which would check to see if the user and assignment exist.
user_test (user_test_id PK, user_assignment_id FK, test_id FK, etc.)

This table creates the relation "user with existing assignment to test".


Need Help Executing a Remote Task from SQL Server Agent There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues.  We can solve this if we can have SQL Server Agent execute the .exe on the remote server to ensure the schedules never conflict.

Is there any way to do this with SQL Server Agent, perhaps with a Powershell command?  If so, can you please give me the steps?

The .exe on the remote server is a data importer for SQL.  It is a stand alone .exe.  We want it to run on the batch server so it doesn't cause resource contention on the DB server.  We are running SQL 2008 R2 Enterprise.

Please let me know if any additional information is required to answer this question.

I have only done this a couple of times and quite a while ago.  At that time I used PSEXEC.EXE from the Windows Internals.  You can read the instructions here:

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

For Powershell remote execution there was a discussion in StackOverflow that satisfied the questioner.  Powershell is probably the way to go these days.

https://stackoverflow.com/questions/9535515/powershell-execute-remote-exe-with-command-line-arguments-on-remote-computer

However you run the remote job, you can schedule it in the SQL Server Agent process that runs the other jobs.   If you think it would work for you, you could also set the job to run on a defined period of 'idleness'.  (I don't think that is what you want, but you can look into it under the SQL Server Agent discussion on Agent schedules at http://technet.microsoft.com/en-us/library/ms189065.aspx.)


TSQL 2012 deleted table in INSTEAD OF and AFTER triggers? My colleague ask me very simple, but difficult for me question:

If I have deleted table with 10 records in INSTEAD OF trigger and I delete 8 of them how many records will I have in deleted table in AFTER trigger?

I think it wuold be 10, but I am not sure.

Very easy to test.

CREATE TABLE T(X INT);

GO

INSERT INTO T
SELECT TOP 10 1
FROM sys.all_objects;

GO

CREATE TRIGGER TR1 ON T INSTEAD OF DELETE AS DELETE TOP (8) FROM T;

GO

CREATE TRIGGER TR2 ON T AFTER DELETE AS SELECT COUNT(*) FROM DELETED;

GO

DELETE FROM T;

DROP TABLE T;

  Answer 8

This is the correct behaviour. The after trigger should reflect the rows that were actually deleted. If you had an audit trigger recording 10 deletions when only 8 occurred this would be clearly incorrect.


Missing data in specific tables post log backup and shrink We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their .ldf files were growing. We took a log backup and tried to shrink the log file. Not sure if the shrink actually ran, the wizard closed in 2-3 secs and the .ldf file size didn't reduce.

Later that day it has been found that data as far as two years old have been lost in specific tables in that DB. Impacted tables are less than 10, There are around 500 tables in the DB and more than 100 of them involve daily transactions.

Please clarify if the log backup and shrink has any part to play in this?
If yes, how could this have been avoided and what else was supposed to be done to keep the .ldf in check?

We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their ldf files were growing. We took a log backup and tried to shrink the log file. Not sure if the shrink actually ran, the wizard closed in 2-3 secs and the ldf file size didn't reduce.

Did you check that if both of the AlwaysOn servers are in a healthy state? It seems that the log backup is not happening because the principal logs were still holding up, which could be due to the following reasons:

the secondary server is down
OR; the databases are not in a healthy state
the redo logs are applying very slowly on  the secondary server

Look into the SQL error logs as well as sometimes log backup does not work because the disk is full, and it couldn't write to it.

If log usage is still high, you may remove the affected databases from the AlwaysOn availability group first. Wait till the log usage drop, and  then add back the affected databases into the availability group. This shouldn't cause to any impact.

  Please clarify if the log backup and shrink has any part to play in this? If yes, how could this have been avoided and what else was supposed to be done to keep the ldf in check?

No, the log backup nor the shrinking shouldn't cause to the lost of data.

Have you checked if...

any scheduled job to purge away some data?
application team or the DBA who purge the data?


Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE I'm trying to do the following query in mysql 5.5

INSERT INTO countingTable( image_count, article_id )

SELECT COUNT( article_id ) AS sum, article_id
FROM imageTable

ON DUPLICATE  KEY UPDATE image_count = VALUES(sum)

But this gives the error:

#1054 - Unknown column 'sum' in 'field list'

Edit for better explaining what i like to do:

countingTable structure:

CREATE TABLE IF NOT EXISTS `countigTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plakat` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `image_count` bigint(20) DEFAULT NULL,
  `trailer_count` bigint(20) DEFAULT NULL,
  `actor_count` bigint(20) DEFAULT NULL,
   .... (many more counting fields)
  `article_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

imageTable has the structure:

CREATE TABLE IF NOT EXISTS `imageTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `article_id` bigint(20) DEFAULT NULL,
  `image_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

(imageTable is basically a referencing table for many2many relation)

The countingTable already has rows referencing the article_id.
Now i like to count all related images and insert or update that in the
countingTable.

Later i need the same stuff for trailer_count, actor_count and so on.

If this is done the first time, the countingTable will be updated by triggers.

The idea is, to have this table so I dont't need to join all relations just for counting if they exist. (As my project needs the countings all the time)

Use UPDATE image_count = VALUES(image_count).

VALUES() expects a name from the columns you are inserting into, not the alias in the query. The query should be:

INSERT INTO countingTable (image_count, article_id)

SELECT COUNT(article_id) AS sum, article_id
FROM imageTable
GROUP BY article_id                  -- I suppose you skipped that line?

ON DUPLICATE  KEY UPDATE image_count = VALUES(image_count) ;

If you want the new values to be added to the existing ones, use:

...
ON DUPLICATE  KEY UPDATE image_count = image_count + VALUES(image_count) ;