3

I am running MS SQL Server for linux from a Docker image (https://hub.docker.com/r/microsoft/mssql-server-linux/)

I've discovered in my log files, that there are many PRIMARY KEY violations on my log table, which has ID uniqueidentifier DEFAULT NEWSEQUENTIALID() column.

Exception is:

Exception: System.Data.SqlClient.SqlException: 
Violation of PRIMARY KEY constraint 'PK_Logs'. 
Cannot insert duplicate key in object 'dbo.Logs'. 
The duplicate key value is (20c0423e-f36b-1410-8020-800000000000).

As stated in documentation

NEWSEQUENTIALID is a wrapper over the Windows UuidCreateSequential function.

(source: https://learn.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql)

How does it work on linux then? Is the behaviour broken, since generated GUIDs should be unique, and they are clearly not.

Reproduction steps

  1. start mssql-server-linux docker image docker run mssql-server-linux (refer to https://hub.docker.com/r/microsoft/mssql-server-linux/ for details)

  2. Create table CREATE TABLE SequentialIdTest(ID uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(), ColA int );

  3. Insert new row INSERT INTO SequentialIdTest(ColA) VALUES (0);

  4. restart docker image docker restart {CONTAINER_NAME}

  5. Try to insert new row again INSERT INTO SequentialIdTest(ColA) VALUES (0);

wojciech_rak
  • 2,276
  • 2
  • 21
  • 30
  • You could consider NEWID() instead, but the limitation is the GUID values will not be sequential, which may result in index fragmentation (if one exists on the GUID column) and some performance issues. Do you mind sending me a small repro script I could try out on Linux? My email is andrela@microsoft.com – andrea-lam-MSFT Jun 21 '17 at 20:05
  • Yes, using `NEWID()` works fine, but I want to optimize insert performance, therefore `NEWSEQUENTIALID()` would suite me better. I will prepare some more detailed repro steps. – wojciech_rak Jun 22 '17 at 13:44
  • 1
    Hi @andrea-lam, I've added reproduction steps to my issue – wojciech_rak Jun 23 '17 at 06:20
  • 1
    Was able to successfully reproduce the issue on my end as well. I've filed a bug against our team to follow up. Thanks for identifying this issue. – andrea-lam-MSFT Jun 28 '17 at 16:53
  • Hi! any development on this? @andrea-lam did you get any feedback on the bug you opened. – vtortola Sep 16 '17 at 23:10
  • 2
    @vtortola as I have not found a related bug in the official github repo, I have created one here: https://github.com/Microsoft/mssql-docker/issues/222. Meanswhile, if you/anyone have/has found a workaround, please share. – Sayan Pal Dec 19 '17 at 10:18
  • Be aware that `NEWSEQUENTIALID()` resets along with your server. So if you expect to avoid index fragmentation throughout your server being reset, you will be disappointed. – Brian Stork May 07 '18 at 15:20

0 Answers0