Magento and error 1452 (integrity constraint violation) in reindex directory
Magento refuses to re-index my product catalog. It logs 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`))
What I think it means there is an invalid row in catalog_category_product_index with a missing value in 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 it doesn't. What can I do to fix this?
source to share
I thought about this after thinking about it a little more - the problem is with the indexing process that wants to insert a row into the catalog_category_product_index table where either product_id or category_id does not exist. What causes the violation.
It would be great if the index processing in this step did not execute a query to determine which rows are causing the problem, but until that happens, you can run these two queries that will tell you which product_id or category_id is causing the problem. Both of these queries should return 0 records. If they return a record, you will 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 remove the offending rows, or be more careful, edit the product or category with the problem and keep your categories or products to get good data in the problem table. Then indexing will work.
source to share
Make a backup first .
The radical fix is to remove all these false links:
Delete FROM `catalog_category_product`
WHERE product_id not in (select entity_id from catalog_product_entity)
OR category_id not in (select entity_id from catalog_category_entity);
Worked for me, but could rip some products for you.
source to share