4

For some reason Magento indexing is failing, we haven't changed anything in the website.... And that is the weird thing! The error in shell using indexer.php:

$ php 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 (`**********_db`.`#sql-649_47012`, CONSTRAINT  `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES  `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON U)' in  /home/**********/domains/***********nna.nl/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/**********/domains/***********nna.nl/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/**********/domains/***********nna.nl/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/**********/domains/***********nna.nl/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/**********/domains/***********nna.nl/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/**********/domains/***********nna.nl/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ca...', Array)
#5 /home/**********/domains/***********nna.nl/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ca...', Array)
#6 /home/**********/domains/***********nna.nl/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(340): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `ca...')
#7 /home/**********/domains/***********nna.nl/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(2569): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `ca...')
#8 /home/**********/domains/***********nna.nl/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(816): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_PRD_FLAT...', 'catalog_product...', 'entity_id', 'catalog_product...', 'entity_id', 'CASCADE', 'CASCADE')
#9 /home/**********/domains/***********nna.nl/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1390): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(1)
#10 /home/**********/domains/***********nna.nl/public_html/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()
#11 /home/**********/domains/***********nna.nl/public_html/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(336): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()
#12 /home/**********/domains/***********nna.nl/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /home/**********/domains/***********nna.nl/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#14 /home/**********/domains/***********nna.nl/public_html/shell/indexer.php(160): Mage_Index_Model_Process->reindexEverything()
#15 /home/**********/domains/***********nna.nl/public_html/shell/indexer.php(200): Mage_Shell_Compiler->run()
#16 {main}
7

it seams that you have zombie data in the flat table for store with id 1.
The prepareFlatTable method is trying to add a Foreign Key to the catalog_product_entity table and that fails.
Most probably because you already have in the catalog_product_flat_store_1 table a product id that is not in the catalog_product_entity table.

First backup your database (or at least catalog_product_flat_store_1 table), then truncate the table catalog_product_flat_store_1.

Or you can disable the usage of the flat catalog, remove the tables catalog_product_flat_store_*, re run the indexing then enable the usage of flat tables again.

| improve this answer | |
  • i only have a table called catalog_product_flat_1, could that be the same? – xvilo Jan 8 '15 at 12:59
  • yes. I assume you have only one store view in your instance. the 1 at the end is the store view id. Magento creates one flat table for each store view. – Marius Jan 8 '15 at 13:46
  • Yes, Thank you for this answer, couldn't find the right one on the interwebs – xvilo Jan 8 '15 at 16:01
-1
I have 4 languages so i truncated 4 tables.
catalog_product_flat_1, 2,  3, 4
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.