Virtual Happy Hour this month, Jun 28, we'll be getting a sneak preview at our soon to launch SaaS CMS!

Try our conversational search powered by Generative AI!

Violation of PRIMARY KEY constraint 'PK_CatalogItemChange'

Vote:
 

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.

https://world.optimizely.com/forum/developer-forum/Commerce/Thread-Container/2018/7/import-products-over-service-api-fail-in-production-environment-only/
https://world.optimizely.com/forum/developer-forum/Commerce/Thread-Container/2019/5/primary-key-violation-error-with-sp-catalogitemchange_insert/

#291285
Edited, Nov 08, 2022 15:22
Vote:
 

can you post the full stacktrace ?

#291287
Nov 08, 2022 16:17
Vote:
 

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

System.AggregateException:
   at Mediachase.Data.Provider.TransactionScope.ExecuteActions (Mediachase.DataProvider, Version=14.6.0.0, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Data.Provider.TransactionScope+ThreadStaticTransactionScope.Commit (Mediachase.DataProvider, Version=14.6.0.0, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Data.Provider.TransactionScope.Complete (Mediachase.DataProvider, Version=14.6.0.0, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Commerce.Catalog.Managers.CatalogAssociationManager.SaveCatalogAssociation (Mediachase.Commerce, Version=14.6.0.0, Culture=neutral, PublicKeyToken=6e58b501b34abce3)
   at EPiServer.Commerce.Catalog.Linking.AssociationCommitter.Update (EPiServer.Business.Commerce, Version=14.6.0.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7)
   at EPiServer.Commerce.Catalog.Linking.DefaultAssociationRepository.UpdateAssociations (EPiServer.Business.Commerce, Version=14.6.0.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7)
   at Project.PIM.DataImport.AssociationManager.UpdateContentAssociations (Project, Version=2.1.0.116, Culture=neutral, PublicKeyToken=null: D:\a\1\s\Project\PIM\DataImport\AssociationManager.cs:38)
   at Project.ScheduledJobs.ImportScheduledJob.RunImport (Project, Version=2.1.0.116, 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=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.TdsParser.TryRun (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery (Microsoft.Data.SqlClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute (EPiServer.Data, Version=12.9.3.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec (Mediachase.SqlDataProvider, Version=14.6.0.0, Culture=neutral, PublicKeyToken=41d2e7a615ba286c)
   at Mediachase.Commerce.CatalogItemChangeManager.InsertChanges (Mediachase.Commerce, Version=14.6.0.0, Culture=neutral, PublicKeyToken=6e58b501b34abce3)
   at Mediachase.Data.Provider.TransactionScope.ExecuteActions (Mediachase.DataProvider, Version=14.6.0.0, 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


#291322
Nov 09, 2022 8:01
Vote:
 

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;

#291325
Nov 09, 2022 9:46
Vote:
 

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.

 

#291327
Nov 09, 2022 10:00
Vote:
 

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

#291385
Nov 10, 2022 12:06
Vote:
 

Hi, are there any updates for this issue?

#292116
Nov 23, 2022 7:59
Vote:
 

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 

#292137
Nov 23, 2022 15:14
Vote:
 

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).

#292138
Edited, Nov 23, 2022 15:26
Vote:
 

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

#292139
Nov 23, 2022 19:02
Vote:
 

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?

#292140
Edited, Nov 23, 2022 19:06
Vote:
 

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? 

#292175
Nov 24, 2022 7:11
Vote:
 

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
AS
BEGIN
	MERGE INTO CatalogItemChange AS TARGET  
	USING (SELECT
		C.ID, CE.CatalogId
	FROM @EntryIds C
	INNER JOIN CatalogEntry CE ON c.ID = CE.CatalogEntryId)
		   AS SOURCE (CatalogEntryId, CatalogId)  
	ON 
		TARGET.CatalogEntryId = SOURCE.CatalogEntryId 
		AND TARGET.CatalogId = SOURCE.CatalogId
		AND TARGET.IsBeingIndexed = 0
	WHEN NOT MATCHED BY TARGET THEN  
		INSERT (CatalogEntryId, CatalogId, IsBeingIndexed) VALUES (CatalogEntryId, CatalogId, 0);
END
GO
#292176
Nov 24, 2022 8:34
Vote:
 

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 ... 

#292178
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.