10

I get the following error while trying to reindex my magento database..

Product Flat Data index process unknown error: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint >violation: 1452 Cannot add or update a child row: a foreign key constraint fails >(d014505f., CONSTRAINT >FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (entity_id) >REFERENCES catalog_product_entity (`e)' in >/www/htdocs/w00f5624/lib/Zend/Db/Statement/Pdo.php:228

How do i fix this??

28

as stated correctly by Sander, deleted products still present in the product flat table cause this error. Anyway, truncating the whole table will not be necessary. You can find these products by this SQL-query:

SELECT pf1.entity_id
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id ) 

You will then have to delete these items, which can be done using this SQL-query:

DELETE pf1.*
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id ) 

Taken from here (German): http://www.avs-webentwicklung.de/nc/blog/artikel/magento-fehler-beim-index-aufbau-sofortmassnahmen.html

Ask me if u need more advise.

  • Nice query, do you use them for a periodical cleanup task or only when required? – Sander Mangel Mar 22 '13 at 20:20
  • Thanks :) The credits for the first one belong to the link's author, though. I only use them when such an error occurs, which is mostly when data is deleted directly from the database, i.e. with MAGMI. And they are still rare. I did not have to use it periodically yet, though I think it would be possible. When you manage products manually, Magento takes care of the index tables on save / on delete (when indexes are configured properly). – simonthesorcerer Mar 22 '13 at 20:26
  • Some explanations can be found here: catgento.com/… – Dmytro Zavalkin Mar 25 '13 at 23:42
  • @simonthesorcerer Thanks, it helps me to resolve this error :) – Bijal Bhavsar Dec 16 '13 at 5:32
4

i've had this issue before. I solved it by truncating the product_flat_data tables and afterwards reindexing. It's caused by non existing products still present in the table.

  • Hello Sander, I have tried the solution but nothing happened for me i have also truncated the catalog_product_flat_1 but indexer for product attributes is not working. – Sarvagya Nov 12 '13 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.