Solving reindex issues with Product Flat Data in Magento

Check your exception.log file And find :

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`., 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)

That error for

1 The key is here:
2 ?
4 That is telling you which table is failing. This exception tells that
5 the referential integrity between:
6 catalog_product_flat_2.entity_id and catalog_product_entity.entity_id is corrupted. This usually means some record(s)
7 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.

1 +———–+
2 | entity_id |
3 +———–+
4 | 35427 |
5 | 35428 |
6 +———–+
7 2 rows in set (0.04 sec)
As example:
1DELETE FROM catalog_product_flat_2 where entity_id = ‘35427’;

Leave a Reply