Violation of PRIMARY KEY constraint 'PK_CatalogItemChange'


In our project we have an automated process importing products with relationship between them.
As one of the last steps we're creating bulk associations and saving them via IAssociationRepository.UpdateAssociations.

However, it seems to be randomly throwing exceptions:

Violation of PRIMARY KEY constraint 'PK_CatalogItemChange'. Cannot insert duplicate key in object 'dbo.CatalogItemChange'. The duplicate key value is (449935, 16, 0).
The statement has been terminated.

It doesn't seem to follow any pattern and when the process is reinitated, it pops-up for different catalogs or products.

I've seen simillar threads from the past, but it doesn't seem the solutions (upgrade to later version) are applicable to Commerce 14.

Edited, Nov 08, 2022 15:22

can you post the full stacktrace ?

Nov 08, 2022 16:17

Hi Quan, here's the stack trace (I've obfuscated details related to the project iself):

   at Mediachase.Data.Provider.TransactionScope.ExecuteActions (Mediachase.DataProvider, Version=, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Data.Provider.TransactionScope+ThreadStaticTransactionScope.Commit (Mediachase.DataProvider, Version=, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Data.Provider.TransactionScope.Complete (Mediachase.DataProvider, Version=, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Commerce.Catalog.Managers.CatalogAssociationManager.SaveCatalogAssociation (Mediachase.Commerce, Version=, Culture=neutral, PublicKeyToken=6e58b501b34abce3)
   at EPiServer.Commerce.Catalog.Linking.AssociationCommitter.Update (EPiServer.Business.Commerce, Version=, Culture=neutral, PublicKeyToken=8fe83dea738b45b7)
   at EPiServer.Commerce.Catalog.Linking.DefaultAssociationRepository.UpdateAssociations (EPiServer.Business.Commerce, Version=, Culture=neutral, PublicKeyToken=8fe83dea738b45b7)
   at Project.PIM.DataImport.AssociationManager.UpdateContentAssociations (Project, Version=, Culture=neutral, PublicKeyToken=null: D:\a\1\s\Project\PIM\DataImport\AssociationManager.cs:38)
   at Project.ScheduledJobs.ImportScheduledJob.RunImport (Project, Version=, Culture=neutral, PublicKeyToken=null: D:\a\1\s\Project\ScheduledJobs\ImportScheduledJob.cs:205)
Inner exception Microsoft.Data.SqlClient.SqlException handled at Mediachase.Data.Provider.TransactionScope.ExecuteActions:
   at Microsoft.Data.SqlClient.SqlConnection.OnError (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.TdsParser.TryRun (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery (Microsoft.Data.SqlClient, Version=, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute (EPiServer.Data, Version=, Culture=neutral, PublicKeyToken=8fe83dea738b45b7)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec (Mediachase.SqlDataProvider, Version=, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Commerce.CatalogItemChangeManager.InsertChanges (Mediachase.Commerce, Version=, Culture=neutral, PublicKeyToken=6e58b501b34abce3)
   at Mediachase.Data.Provider.TransactionScope.ExecuteActions (Mediachase.DataProvider, Version=, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)

BTW - I'm wondering if I could temporary disable content of CatalogItemChange_Insert stored procedure as a workaround?
Would you be able to desribe for what the CatalogItemChange table is used? I cannot seem to find any relevant information, especially in context of Commerce 14

Nov 09, 2022 8:01

If you are not using the Search Provider (lucene etc.), and if you are not even using the event-driven indexing, you can disable it entirely by setting DisableCatalogEventDrivenIndexing to true inside your ConfigureContainer(ServiceConfigurationContext context)

context.Services.BuildServiceProvider().GetInstance<CatalogOptions>().DisableCatalogEventDrivenIndexing = true;

Nov 09, 2022 9:46

We are using the LuceneSearchProvider, however the flag DisableCatalogEventDrivenIndexing doesn't seem to affect the code that we're discussing as it only affects CatalogItemChangeForEventDrivenIndexing but not CatalogItemChangeManager.


Nov 09, 2022 10:00

That's true. It was changed since I wrote the code. Let me take a look

Nov 10, 2022 12:06

Hi, are there any updates for this issue?

Nov 23, 2022 7:59

I created bug COM-16517, but it will take a while to be prioritized. Looking at the code, i think you can empty out the SP as a temporary measure, IF you are not using Personalization 

Nov 23, 2022 15:14

Hi Quan, thank you for the response and the ticket to track.
Would you able to tell how disabling the SP affect the personalization?
I've did in fact disable the SP, because I had no other choice in that matter, I had to make the import of products reliable again.
We're using the personalization, but not in for Commerce product (i.e. only for CMS blocks and only on CMS pages, not Commerce).

Edited, Nov 23, 2022 15:26

I think you can just make it empty. I.e. Comment everything between begin and end

Nov 23, 2022 19:02

I know how to disable the stored procedure :)
But my previous questions still stands - you've metioned that it has some impact on personalization - how does the disabling of stored procedure will affect personalization?

Edited, Nov 23, 2022 19:06

No I mixed things up - my bad. CatalogItemChangeManager is not used for recommendation, that's its sister. CatalogItemChangeManager is used so you can build the index incrementally, as you are using LuceneSearchProvider you'll be needing it (unless you rebuild index all the time)

What does your CatalogItemChange_Insert look like before your modification? 

Nov 24, 2022 7:11

Okey, that's fine - as long it only affects the Lucene Search, then it's fine.

The content of unmodified CatalogItemChange_Insert:

CREATE PROCEDURE [dbo].[CatalogItemChange_Insert]
@EntryIds [udttIdTable]  READONLY
	MERGE INTO CatalogItemChange AS TARGET  
		C.ID, CE.CatalogId
	FROM @EntryIds C
	INNER JOIN CatalogEntry CE ON c.ID = CE.CatalogEntryId)
		   AS SOURCE (CatalogEntryId, CatalogId)  
		TARGET.CatalogEntryId = SOURCE.CatalogEntryId 
		AND TARGET.CatalogId = SOURCE.CatalogId
		AND TARGET.IsBeingIndexed = 0
		INSERT (CatalogEntryId, CatalogId, IsBeingIndexed) VALUES (CatalogEntryId, CatalogId, 0);
Nov 24, 2022 8:34

So that's updated and correct. I was worried that you might have older version (i.e. the upgrade script did not complete successfully). Not quite sure why the issue still happens though ... 

Nov 24, 2022 15:15
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.