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}
Improve this question
1

2 Answers 2

Reset to default
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
3
  • i only have a table called catalog_product_flat_1, could that be the same?
    – xvilo
    Jan 8, 2015 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, 2015 at 13:46
  • Yes, Thank you for this answer, couldn't find the right one on the interwebs
    – xvilo
    Jan 8, 2015 at 16:01
-1 
I have 4 languages so i truncated 4 tables.
catalog_product_flat_1, 2,  3, 4

EDIT : It seems wrong, do not proceed please.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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