Magento: Problem with reindexing process for “Product flat data” indexes

Problem with reindexing process for "Product flat data" indexes
Problem with reindexing process for "Product flat data" indexes

Symptoms

Reindexing of Product flat data index gives unknown reindexing error or “Some problem with reindexing process” error message or reindexing of Product flat data index is never completed.

Attempt to reindex it via command line gives the following error message:

$ php -f shell/indexer.php -- --reindex catalog_product_flat
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 (<result 2 when explaining filename '#sql-'>, CONSTRAINT ´FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID´ FOREIGN KEY (´entity_id´) REFERENCES ´catalog_product_entity´ (´entity_id)' in lib/Zend/Db/Statement/Pdo.php:228


Reason

There are references to inexisting/deleted products in catalog_product_flat_% tables.

Solution

Try to truncate ´catalog_product_flat_%´ tables (catalog_product_flat_1, catalog_product_flat_2, catalog_product_flat_3 etc.) in MySQL console or via phpMyAdmin:

mysql > truncate table ´catalog_product_flat_1´;
mysql > truncate table ´catalog_product_flat_2´;
mysql > truncate table ´catalog_product_flat_3´;