SaaS CMS has officially launched! Learn more now.

Primary key violation error with SP CatalogItemChange_Insert


I'm not sure if this is the right place to report this, but we encountered the following issue

After an incident where the indexer had been stopped prematurely in production, we noticed that the commerce importer was throwing ‘primary key violation’ exceptions every now and then. The reason for the above, was found to be that the SP does not take into consideration that an entry in the CatalogItemChange table can already exist with the values for the given catalog entry and catalog, with IsBeingIndexed = 0.
Since the table has a combined primary key consisting of all 3 values, it then threw an exception, when trying to create the new record.

For us, In order to get around the ‘primary key violation’ exception, the where-clause of the SP was changed from just, naively, looking at whether or not a record with IsBeingIndexed = 1 exists, to check if a record with IsBeingIndexed = 0, for that catalog entry and catalog, exists. The change is not simply swapping a 1 for a 0, as can be seen later.

I would however like an oppinion on this issue and our solution :)
In my oppinion it should not try to insert an entry if the same entry is already queued and pending indexation

Thanks in advance

May 01, 2019 15:21

What version are you using? We fixed this issue (assuming I understand you correctly) in a later version (12.0.0)

May 01, 2019 21:56

Hi Quan

We are using 11.8.3 - If you have already fixed this issue, then my job is done :)

Now at least we know it will still be fixed if/when we update

Thanks a lot for your response

May 02, 2019 9:32
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* 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.