Violation of PRIMARY KEY constraint 'blb_pk'

Vragen
,
1

Wij gebruiken op dit moment Invantive Data Hub (22.0.191-PROD+3392)
Bij het draaien van het onderstaande script ontstaat de hieronder getoonde fout (deel uit logfile):
Hoe is dit te voorkomen/op te lossen?

Alvast dank!

2022-05-23 08:31:17.655 Information itgendhb216: 23-05-2022 08:31:17.65013 Select document attachment for division 1085344 from beyond timestamp 6194871520.
2022-05-23 08:31:17.655 Information itgendhb216: 23-05-2022 08:31:17.65013 Select document attachment for division 1085344 from beyond timestamp 6194871520.
2022-05-23 08:31:18.719 Information itgendhb216: 23-05-2022 08:31:18.71905 Select document attachment for division 1085344 to at most 6245217963.
…
2022-05-23 08:31:20.238 Information itgendhb216: 23-05-2022 08:31:20.23725 Register attachment #1 with ID 173fe071-59dd-43ce-b647-c05d5d276533 for document ID c39c29a0-161e-4ff6-bd43-4efe2e979848.
2022-05-23 08:31:20.527 Information itgendhb216: 23-05-2022 08:31:20.52604 Error loading #2 with ID 6f267c36-44cb-42a5-9c28-7364d08a3bef for document ID 6a3a19c2-3784-40d2-80cb-df0911756222: Violation of PRIMARY KEY constraint ‘blb_pk’. Cannot insert duplicate key in object ‘dbo.blobs’. The duplicate key value is (6f267c36-44cb-42a5-9c28-7364d08a3bef).
The statement has been terminated.
2022-05-23 08:31:20.527 Information itgendhb216: 23-05-2022 08:31:20.52604 Error loading #2 with ID 6f267c36-44cb-42a5-9c28-7364d08a3bef for document ID 6a3a19c2-3784-40d2-80cb-df0911756222: Violation of PRIMARY KEY constraint ‘blb_pk’. Cannot insert duplicate key in object ‘dbo.blobs’. The duplicate key value is (6f267c36-44cb-42a5-9c28-7364d08a3bef).
The statement has been terminated.
2022-05-23 08:31:20.527 Information itgendhb216: 23-05-2022 08:31:20.52704 Register attachment #2 with ID 6f267c36-44cb-42a5-9c28-7364d08a3bef for document ID 6a3a19c2-3784-40d2-80cb-df0911756222.
2022-05-23 08:31:20.527 Information itgendhb216: 23-05-2022 08:31:20.52704 Register attachment #2 with ID 6f267c36-44cb-42a5-9c28-7364d08a3bef for document ID 6a3a19c2-3784-40d2-80cb-df0911756222.
2022-05-23 08:31:20.846 Information itgendhb216: 23-05-2022 08:31:20.84506 Error loading #3 with ID 8c4720b0-06e1-4ef7-9a6a-35097449f546 for document ID c56d7328-35f0-4fce-9779-262da2a0d7da: Violation of PRIMARY KEY constraint ‘blb_pk’. Cannot insert duplicate key in object ‘dbo.blobs’. The duplicate key value is (8c4720b0-06e1-4ef7-9a6a-35097449f546).
The statement has been terminated.
2022-05-23 08:31:20.846 Information itgendhb216: 23-05-2022 08:31:20.84506 Error loading #3 with ID 8c4720b0-06e1-4ef7-9a6a-35097449f546 for document ID c56d7328-35f0-4fce-9779-262da2a0d7da: Violation of PRIMARY KEY constraint ‘blb_pk’. Cannot insert duplicate key in object ‘dbo.blobs’. The duplicate key value is (8c4720b0-06e1-4ef7-9a6a-35097449f546).
The statement has been terminated.

declare
  l_timestamp_start      int64;
  l_timestamp_start_incl int64;
  l_timestamp_next_min   int64;
  l_cnt_dct_loaded       int64;
  l_cnt_dct_ptn_loaded   int64;
  --
  -- Maximum number of documents to load per run across all companies.
  --
  g_max_dct_load       pls_integer := 200000;
  --
  -- Some maximum step size for timestamp of Exact Online.
  --
  g_step_size          pls_integer := 50000000;
begin
  l_cnt_dct_loaded := 0;
  --
  use [INVANTIVE-EOL]@sqlserver;
  use all@eol;
  --
  for r_ptn in
  ( select to_number(spn.code) divisioncode
    from   SYSTEMPARTITIONS@DataDictionary spn
    where  spn.is_selected = true
    and    spn.provider_name = 'ExactOnlineAll'
    order
    by     spn.code
  )
  loop
    if l_cnt_dct_loaded < g_max_dct_load
    then
      begin
        --
        -- Retrieve timestamp number of the last document.
        --
        select /*+ ods(false) */        
               timestampmax
        into   l_timestamp_start
        from   AttachmentSettings@sqlserver
        where  division = r_ptn.divisioncode
       ;
      exception
        when no_data_found
        then
          insert into AttachmentSettings@sqlserver
          ( division
          , timestampmax
          )
          values 
          ( r_ptn.divisioncode
          , 0
          )
          ;
          l_timestamp_start := 0;
      end;
      --
      use select r_ptn.divisioncode, 'eol';
      --
      -- Find first document along increasing value of timestamp.
      --
      dbms_output.put_line('Select document attachment for division ' || to_char(r_ptn.divisioncode) || ' from beyond timestamp ' || to_char(l_timestamp_start) || '.');
      select min(timestamp)
      into   l_timestamp_start_incl
      from   ( select timestamp
               from   syncdocumentattachments@eol
               where  division = r_ptn.divisioncode
               and    timestamp > l_timestamp_start
               limit 1
             )
      ;
      --
      -- Load when there are any documents.
      --
      if l_timestamp_start_incl is not null
      then
        l_timestamp_next_min := l_timestamp_start_incl + g_step_size;
        l_cnt_dct_ptn_loaded := 0;
        --
        dbms_output.put_line('Select document attachment for division ' || to_char(r_ptn.divisioncode) || ' to at most ' || to_char(l_timestamp_next_min) || '.');
        for r_dct in
        ( select /*+ ods(false) http_disk_cache(false) http_memory_cache(false) */ 
                 sdt.document documentid
          ,      sdt.url
          ,      sdt.timestamp
          ,      atl.DocumentContents
          ,      sdt.ID attachmentid
          from   SyncDocumentAttachments@eol sdt
          join   AttachmentByUrl@eol(sdt.url) atl
          where  sdt.division = r_ptn.divisioncode
          and    sdt.timestamp >= l_timestamp_start_incl
          and    sdt.timestamp <= l_timestamp_next_min
        )
        loop
          if r_dct.timestamp > l_timestamp_start_incl
          then 
            l_timestamp_start_incl := r_dct.timestamp;
          end if;
          --
          -- Save to table.
          --
          begin
            insert into Blobs@sqlserver
            ( timestamp_
            , division
            , id
            , documentid
            , attachment
            )
            values
            ( r_dct.timestamp
            , r_ptn.divisioncode
            , r_dct.attachmentID
            , r_dct.Documentid
            , r_dct.DocumentContents      
            );
          exception
            when others
            then
              dbms_output.put_line('Error loading #' || to_char(l_cnt_dct_ptn_loaded+1) || ' with ID ' || r_dct.attachmentid || ' for document ID ' || r_dct.documentid || ': ' || sqlerrm);
              null;
          end;
          --
          l_cnt_dct_loaded := l_cnt_dct_loaded + 1;
          l_cnt_dct_ptn_loaded := l_cnt_dct_ptn_loaded + 1;
          dbms_output.put_line('Register attachment #' || to_char(l_cnt_dct_ptn_loaded) || ' with ID ' || r_dct.attachmentid || ' for document ID ' || r_dct.documentid || '.');
          --
          if l_cnt_dct_loaded >= g_max_dct_load
          then
            dbms_output.put_line('Loaded ' || to_char(l_cnt_dct_loaded) || ' documents exceeds limit of ' || to_char(g_max_dct_load) || '.');
            exit;
          end if;
        end loop;
        --
        -- Move forward to end of range when no documents were found.
        --
        if l_cnt_dct_ptn_loaded = 0 and l_timestamp_start_incl < l_timestamp_next_min
        then
          l_timestamp_start_incl := l_timestamp_next_min;
        end if;
        --
        update AttachmentSettings@sqlserver
        set    timestampmax = l_timestamp_start_incl
        where  division = r_ptn.divisioncode
        ;
        dbms_output.put_line('Finished company ' || r_ptn.divisioncode || ' at timestamp ' || l_timestamp_start_incl || ' (including).');
      else
        dbms_output.put_line('Skip company ' || r_ptn.divisioncode || ', no new documents beyond timestamp ' || to_char(l_timestamp_start) || '.');
      end if;
    else
      dbms_output.put_line('Skip company ' || r_ptn.divisioncode || ', maximum document load exceeded.');
    end if;
  end loop;
end;
2

De melding:

Violation of PRIMARY KEY constraint ‘blb_pk’. Cannot insert duplicate key in object ‘dbo.blobs’.

is een SQL Server foutmelding op basis van dubbele waardes voor de primary key van de tabel blobs en buiten het bereik van Invantive SQL. Advies is om het bestaan van de dubbele waarde te onderzoeken en correctieve acties hiervan te ondernemen.

3

Dank voor je reactie.

Dit was voor mij echter ook al duidelijk, het script is echter iemand van Invantive geleverd en ik dacht dat daarin voorzien was.

Het kan ook zo zijn dat ik deze fout gewoon moet accepteren, dat is hem nou juist de vraag. Het betreft het constraint ‘blb_pk’ ingesteld is op de kolom id, die op zijn beurt weer een Guid is.
Of te wel: hoe moet ik hier mee om gaan?

4

Of er sprake is van een technische melding, functioneel probleem in script of issue met documenten in Exact Online is niet zo te zeggen. Advies is om anders een consult in te plannen.

5

Alhoewel documentbijlages niet bijgewerkt zouden moeten worden, lijkt dat wel te gebeuren in Exact Online. De code voor het repliceren van grote hoeveelheden documentbijlages uit Exact Online in SQL Server is hierop in onderlinge samenwerking aangepast tot:

declare
  l_timestamp_start      int64;
  l_timestamp_start_incl int64;
  l_timestamp_next_min   int64;
  l_cnt_dct_loaded       int64;
  l_cnt_dct_ptn_loaded   int64;
  l_cnt_load_errors      int64;
  l_cnt_ignore_errors    int64;
  --
  -- Maximum number of documents to load per run across all companies.
  --
  g_max_dct_load       pls_integer := 200000;
  --
  -- Some maximum step size for timestamp of Exact Online.
  --
  g_step_size          pls_integer := 50000000;
begin
  l_cnt_dct_loaded := 0;
  --
  use [INVANTIVE-EOL]@sqlserver;
  use all@eol;
  --
  for r_ptn in
  ( select to_number(spn.code) divisioncode
    from   SYSTEMPARTITIONS@DataDictionary spn
    where  spn.is_selected = true
    and    spn.provider_name = 'ExactOnlineAll'
    order
    by     spn.code
  )
  loop
    if l_cnt_dct_loaded < g_max_dct_load
    then
      begin
        --
        -- Retrieve timestamp number of the last document.
        --
        select /*+ ods(false) */        
               timestampmax
        into   l_timestamp_start
        from   AttachmentSettings@sqlserver
        where  division = r_ptn.divisioncode
       ;
      exception
        when no_data_found
        then
          insert into AttachmentSettings@sqlserver
          ( division
          , timestampmax
          )
          values 
          ( r_ptn.divisioncode
          , 0
          )
          ;
          l_timestamp_start := 0;
      end;
      --
      use select r_ptn.divisioncode, 'eol';
      --
      -- Find first document along increasing value of timestamp.
      --
      dbms_output.put_line
	  ( 'Select document attachment for division ' 
	    || to_char(r_ptn.divisioncode)
        || ' from beyond timestamp ' 
        || to_char(l_timestamp_start) 
        || '.'
      );
      select min(timestamp)
      into   l_timestamp_start_incl
      from   ( select timestamp
               from   syncdocumentattachments@eol
               where  division = r_ptn.divisioncode
               and    timestamp > l_timestamp_start
               limit 1
             )
      ;
      --
      -- Load when there are any documents.
      --
      if l_timestamp_start_incl is not null
      then
        l_timestamp_next_min := l_timestamp_start_incl + g_step_size;
        l_cnt_dct_ptn_loaded := 0;
        l_cnt_load_errors := 0;
        l_cnt_ignore_errors := 0;
        --
        dbms_output.put_line
        ( 'Select document attachment for division ' 
          || to_char(r_ptn.divisioncode) 
          || ' to at most ' 
          || to_char(l_timestamp_next_min) 
          || '.'
        );
        for r_dct in
        ( select /*+ ods(false) http_disk_cache(false) http_memory_cache(false) */ 
                 sdt.document documentid
          ,      sdt.url
          ,      sdt.timestamp
          ,      atl.DocumentContents
          ,      sdt.ID attachmentid
          ,      atl.DocumentSize
          ,      sdt.FileSize
          from   SyncDocumentAttachments@eol sdt
          join   AttachmentByUrl@eol(sdt.url) atl
          where  sdt.division = r_ptn.divisioncode
          and    sdt.timestamp >= l_timestamp_start_incl
          and    sdt.timestamp <= l_timestamp_next_min
        )
        loop
          if r_dct.timestamp > l_timestamp_start_incl
          then 
            l_timestamp_start_incl := r_dct.timestamp;
          end if;
          --
          -- Save to table.
          --
          begin
            insert into Blobs@sqlserver
            ( timestamp_
            , division
            , id
            , documentid
            , attachment
            )
            values
            ( r_dct.timestamp
            , r_ptn.divisioncode
            , r_dct.attachmentID
            , r_dct.Documentid
            , r_dct.DocumentContents      
            );
          exception
            when others
            then
              --
              -- In general, a document attachment should not be updated at all.
              -- However, as described on
              -- https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver16,
              -- even an update of any column to an equal value will change the rowversion of a row.
              --
              -- One specific case has been studied and no changes were found. Please ignore
              -- errors containing:
              --
              -- Violation of PRIMARY KEY constraint ‘blb_pk’
              --
              if sqlerrm like '%Violation of PRIMARY KEY constraint %blb_pk%'
              then
                l_cnt_ignore_errors := l_cnt_ignore_errors + 1;
              else
                l_cnt_load_errors := l_cnt_load_errors + 1;
                dbms_output.put_line
                ( 'Error #'
                  || to_char(l_cnt_load_errors)
                  || ' loading row #' 
                  || to_char(l_cnt_dct_ptn_loaded + 1) 
                  || ' with ID ' 
                  || r_dct.attachmentid 
                  || ' for document ID ' 
                  || r_dct.documentid 
                  || ', document attachment timestamp ' 
                  || r_dct.timestamp 
                  || ', file size ' 
                  || r_dct.FileSize
                  || ', document size ' 
                  || r_dct.DocumentSize
                  || ': ' 
                  || sqlerrm
                );
              end if;
          end;
          --
          l_cnt_dct_loaded := l_cnt_dct_loaded + 1;
          l_cnt_dct_ptn_loaded := l_cnt_dct_ptn_loaded + 1;
          dbms_output.put_line
          ( 'Register attachment #' 
            || to_char(l_cnt_dct_ptn_loaded) 
            || ' with ID ' 
            || r_dct.attachmentid 
            || ' for document ID ' 
            || r_dct.documentid 
            || '.'
          );
          --
          if l_cnt_dct_loaded >= g_max_dct_load
          then
            dbms_output.put_line
            ( 'Loaded ' 
              || to_char(l_cnt_dct_loaded) 
              || ' documents exceeds limit of ' 
              || to_char(g_max_dct_load) 
              || '.'
            );
            exit;
          end if;
        end loop;
        --
        -- Move forward to end of range when no documents were found.
        --
        if l_cnt_dct_ptn_loaded = 0 and l_timestamp_start_incl < l_timestamp_next_min
        then
          l_timestamp_start_incl := l_timestamp_next_min;
        end if;
        --
        update AttachmentSettings@sqlserver
        set    timestampmax = l_timestamp_start_incl
        where  division = r_ptn.divisioncode
        ;
        dbms_output.put_line
        ( 'Finished company ' 
          || r_ptn.divisioncode 
          || ' at timestamp ' 
          || l_timestamp_start_incl 
          || ' (including). '
          || to_char(l_cnt_dct_loaded)
          || ' documents processed. '
          || to_char(l_cnt_load_errors)
          || ' load errors. '
          || to_char(l_cnt_ignore_errors)
          || ' ignored errors.'
        );
      else
        dbms_output.put_line
        ( 'Skip company ' 
          || r_ptn.divisioncode 
          || ', no new documents beyond timestamp ' 
          || to_char(l_timestamp_start) 
          || '.'
        );
      end if;
    else
      dbms_output.put_line
      ( 'Skip company ' 
        || r_ptn.divisioncode 
        || ', maximum document load exceeded.'
      );
    end if;
  end loop;
end;