Magento and error 1452 (Integrity constraint violation) on reindex catalog

Question

Magento is refusing to reindex my product catalog. It's logging this error:

2013-01-29T23:24:51+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: 
Integrity constraint violation: 1452 Cannot add or update a child row: 
a foreign key constraint fails (`cjsquash_mgnt1`.`catalog_category_product_index`, 
CONSTRAINT `FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`))

Which I would think means there's an invalid row in catalog_category_product_index with a missing value in the catalog_category_entity table, but I ran this query:

SELECT *
FROM catalog_category_product_index
where category_id not in (select entity_id from 
catalog_category_entity)

and it returns 0 rows so that doesn't appear to be the case. What can I do to fix this?

Answer 1

I figured this out myself after thinking about it some more - the problem comes from the indexing process wanting to insert a row into the catalog_category_product_index table where either the product_id or the category_id don't exist. That's what causes the violation.

It would be great if the index processing failing on this step would subsequently run a query to identify which rows are causing the problem, but until that happens, you can run these two queries which will tell you which product_id or category_id is causing the issue. Both of these queries should return 0 records. If they return a record, you'll know exactly which product is in a non-existent category or which category has a non-existent product. You can

SELECT * FROM `catalog_category_product` WHERE 
product_id not in (select entity_id from catalog_product_entity)

SELECT * FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity)

You can then either delete the offending rows, or to be more cautious, edit the product or category with the problem and resave its categories or products to get good data into the problem table. Then indexing will work.