none
Bug on rebuilding a partition on a table partitioned on peristed column with LOB data gives a duplicate key violation RRS feed

  • Question

  • SQL server version

    Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)   Mar 12 2019 19:29:19   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

    Repo script


    CREATE PARTITION SCHEME [PS_RingBufferByMonthOfYear] AS PARTITION [PF_RingBufferByMonthOfYear] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
    GO
    CREATE PARTITION FUNCTION [PF_RingBufferByMonthOfYear](tinyint) AS RANGE LEFT FOR VALUES (0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09, 0x0A, 0x0B, 0x0C)
    GO

    CREATE TABLE TableA
    (
    [ExecutionId] [int] NOT NULL IDENTITY(1, 1),
    [StartDateTime] [datetime2] (3) NOT NULL CONSTRAINT [DF_TaskExecution_StartDateTime2] DEFAULT (getutcdate()),
    [LOB] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MonthOfYear] AS (CONVERT([tinyint],datepart(month,[StartDateTime]))) PERSISTED NOT NULL
    ) ON [PS_RingBufferByMonthOfYear] ([MonthOfYear])
    WITH
    (
    DATA_COMPRESSION = PAGE
    )
    GO
    ALTER TABLE TableA ADD CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ([MonthOfYear], [ExecutionId]) WITH (DATA_COMPRESSION = PAGE) ON [PS_RingBufferByMonthOfYear] ([MonthOfYear])
    GO

    insert into TableA
    (
      
        StartDateTime,
        Lob
    )
    values
    (  
        sysdatetime(), -- StartDateTime - datetime2(3)
        N''            -- ResultMessage - nvarchar(max)
        )
     go 10
     alter index PK_TableA on TableA rebuild partition = 1 with (online=on)



    Error

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TableA' and the index name 'PK_TableA'. The duplicate key value is (6, 1).


    • Edited by dehsroM Wednesday, June 12, 2019 3:18 PM
    Wednesday, June 12, 2019 3:12 PM

Answers

  • I was able to repro this on SQL 2017 CU14. Interesting enough, it did not repro whan I tested on SQL 2019 CTP 3.0. So it may that it is an issue that has been fixed. (It could also be that I was lucky on SQL 2019.) CU15 for SQL 2017 is out as I recall. It could be worth trying.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Lokesh Vij Friday, June 14, 2019 6:55 PM
    • Marked as answer by dehsroM Monday, June 17, 2019 5:14 PM
    Friday, June 14, 2019 8:15 AM

All replies

  • That sounds bad. If you have not already reported it on https://feedback.azure.com/forums/908035-sql-server you should do so.

    If this is a blocking issue for you, so that you need a fix, you will have to open a case with Microsoft.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 12, 2019 10:05 PM
  • I was able to repro this on SQL 2017 CU14. Interesting enough, it did not repro whan I tested on SQL 2019 CTP 3.0. So it may that it is an issue that has been fixed. (It could also be that I was lucky on SQL 2019.) CU15 for SQL 2017 is out as I recall. It could be worth trying.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Lokesh Vij Friday, June 14, 2019 6:55 PM
    • Marked as answer by dehsroM Monday, June 17, 2019 5:14 PM
    Friday, June 14, 2019 8:15 AM
  • That's amazing, thankyou.

    I think we are holding off cu15 for this reason

    https://twitter.com/codykonior/status/1132850174491971584?s=21

    I had logged the bug on the portal you suggested anyhow

    Friday, June 14, 2019 3:07 PM