Episerver Commerce 10.2.2.
We use Catalog Content API to keep our product catalog in sync with PIM.
When we manually delete nodes from the UI (Commerce/Catalog), Episerver keeps the old data in CatalogItemAsset and Variation tables.
So next time we run "EPiServer Find Content Index Job", we get lots of errors like this:
" An exception occurred while indexing content 18229: Exception in ecf_CatalogEntry: ForeignKeyConstraint FK_Variation_CatalogEntry requires the child key values (26218) to exist in the parent table. (see log for more information)"
Is this a known issue?
It sounds like this problem was fixed in 10.4 (and it might have something to do with me!). But why are you using 10.2.2 Dejan - it was like aeon ago.
Thanks Quan. We will try with an upgrade to 10.8 first.
Please note that it will not fix the problem with already orphan keys - you will need to do some magic (read, SQL) to clean them up. The fix is to make sure deleted entries are cleanly deleted
For future reference, we used the following queries to clean up the commerce database.
DELETE FROM CatalogItemAsset WHERE CatalogEntryId = 0 AND CatalogNodeId NOT IN (SELECT CatalogNodeId FROM CatalogNode)
DELETE FROM CatalogItemAsset WHERE CatalogNodeId = 0 AND CatalogEntryId NOT IN (SELECT CatalogEntryId FROM CatalogEntry)
DELETE FROM Variation WHERE CatalogEntryId NOT IN (SELECT CatalogEntryId FROM CatalogEntry)