Magento Reindex Issue / Error on Product Flat Data

Posted by Damodar Bashyal on September 17, 2013

 

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)

Index Management   System   Magento Admin

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

Great! the Option 2 works with me! Thanks
 
not published on website


QR Code: Magento Reindex Issue / Error on Product Flat Data