Solving reindex issues with Product Flat Data in Magento

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';
    • yoko on November 21, 2012 at 11:04 am

    Reply

    Hi! Please check the StackOverflow link!

  1. Reply

    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.

  2. Reply

    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 on January 12, 2016 at 7:10 am

    Reply

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

Leave a Reply

Your email address will not be published.