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??

Improve this question

2 Answers 2

Reset to default
29

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.

Improve this answer
4
  • Nice query, do you use them for a periodical cleanup task or only when required?
    – Sander Mangel
    Commented Mar 22, 2013 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
    Commented Mar 22, 2013 at 20:26
  • Some explanations can be found here: catgento.com/…
    – Dmytro Zavalkin
    Commented Mar 25, 2013 at 23:42
  • @simonthesorcerer Thanks, it helps me to resolve this error :)
    – Bijal Bhavsar
    Commented Dec 16, 2013 at 5:32
4

I've had this issue before, the issue is caused by non-existing (deleted) products still present in the flat tables.

I solved it by truncating the product_flat_data* tables and afterwards reindexing.

Improve this answer
1
  • 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
    Commented Nov 12, 2013 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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