For the past 2 years we’ve run into a reindex issue when reindexing product flat data in Magento more than once. Basically, we wet something like:

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 (`DATABASE_NAME`.<result 2 when explaining filename '#sql-1803_1aa01'>, CONSTRAINT `FK_CAT_PRD_FLAT_2_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` ()' in /app/magento_oneline/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /app/magento_oneline/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /app/magento_oneline/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /app/magento_oneline/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /app/magento_oneline/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

The key is here:

FK_CAT_PRD_FLAT_2_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

That is telling you which table is failing. This exception tells that the referential integrity between: catalog_product_flat_2.entity_id and catalog_product_entity.entity_id is corrupted. This usually means some record(s) contain(s) null in the field catalog_product_entity.entity_id, where they are expected to contain existing entity_ids.

Fixing this

In order to fix this issue, you need to find what the corrupted entries are. This is easy. In this case, you’ll need to create a query to get the empty registers:

SELECT a.entity_id FROM catalog_product_flat_2 AS a LEFT JOIN catalog_product_entity AS b ON a.entity_id = b.entity_id WHERE ISNULL(b.entity_id);

This will display the corrupted entities. You only need to delete them and that’s all.

+-----------+
| entity_id |
+-----------+
|     35427 |
|     35428 |
+-----------+
2 rows in set (0.04 sec)

As example:

DELETE FROM catalog_product_flat_2 where entity_id = '35427';
Categories: Errors

6 Comments

yoko · November 21, 2012 at 11:04 am

Hi! Please check the StackOverflow link!

Ricardo Martins · June 18, 2013 at 1:48 pm

It’s a great and useful article. Im not sure if I can delete every result in the first select. If so, In my case I have a lot of them, so I build this sql to allow deleting everything directly from the select…

CREATE TEMPORARY TABLE IF NOT EXISTS tempidxx (entity_id INT);

— insert your first select into the temp table
INSERT INTO tempidxx (entity_id)
SELECT a.entity_id FROM catalog_product_flat_2 AS a
LEFT JOIN catalog_product_entity AS b ON a.entity_id = b.entity_id
WHERE ISNULL(b.entity_id);

— uses the temp table in the where clause for your delete, deleting every register in the first select
DELETE FROM catalog_product_flat_2 where entity_id IN(
SELECT entity_id FROM tempidxx);

— just ensure that everything was deleted
SELECT a.entity_id FROM catalog_product_flat_2 AS a
LEFT JOIN catalog_product_entity AS b ON a.entity_id = b.entity_id
WHERE ISNULL(b.entity_id);

I hope you like it.

paulbill · June 25, 2014 at 9:56 am

Thanks for this, very helpful.

Instead of deleting all the records individually via id you can just delete them all like this:

DELETE a.*
FROM catalog_product_flat_2 AS a
LEFT JOIN catalog_product_entity AS b ON a.entity_id = b.entity_id
WHERE ISNULL(b.entity_id)

pradeep · January 12, 2016 at 7:10 am

nope didn’t work for me i have done same things, the document said to do but still no luck

Milo · April 6, 2017 at 6:31 am

Thanks a lot.
Worked for me, but I had to do the same solution for catalog_product_flat_1 and catalog_product_flat_3 as well before it worked.

Logando as queries do Magento e rastreando seus problemas | Blog do Desenvolvedor <? · June 18, 2013 at 2:01 pm

[…] Referência e links úteis Stack Overflow Apresentação do Erick Hansen (Classy Llama) –  (Link direto do Download) Resolvendo problemas de relacionamento durante o Reindex no Magento […]

Leave a Reply

Your email address will not be published. Required fields are marked *