I was getting below error today:
There was a problem with reindexing process.SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails ('database'.'#sql-70c_45b87', CONSTRAINT 'FK_CAT_PRD_FLAT_2_ENTT_ID_CAT_PRD_ENTT_ENTT_ID' FOREIGN KEY ('entity_id') REFERENCES 'catalog_product_entity' ('entity_id') ON DELETE CASCADE ON UPDAT)
I tried: Magento Index Issue, which didn't work in this case. So after some google around found below solutions to solve this issue. First solution solved my issue, so I didn't have to try other ones.
Option 1: Check to see if you have any deleted product not removed from flat table as below:
SELECT f1.entity_id FROM catalog_product_flat_1 f1 LEFT JOIN catalog_product_entity p ON f1.entity_id = p.entity_id WHERE ISNULL( p.entity_id );
I didn't get any row from flat table 1, so tried with 2 and I got 1 result. So I deleted that row as:
DELETE f2.* FROM catalog_product_flat_2 f2 LEFT JOIN catalog_product_entity p ON f2.entity_id = p.entity_id WHERE ISNULL( p.entity_id );
Then I re-indexed all again from command line. This time I was able to reindex without any issue.
Option 2:
Truncate flat tables and reindex all again.
Option 2:
Drop flat tables and reindex all again which recreates flat tables again.
Francisco posted on - Monday 11th of November 2013 12:12:37 PM