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';
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 […]