Since a few weeks our reindex will not work anymore and crash with the following error:
Product Attributes 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 (`hcomputers_mag`.`catalog_product_index_eav`, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE)' in /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php:228 Stack trace: #0 /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) #1 /var/www/hcomputers/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #2 /var/www/hcomputers/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #3 /var/www/hcomputers/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #4 /var/www/hcomputers/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array) #5 /var/www/hcomputers/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array) #6 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...') #7 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(48): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_product...', Array, false) #8 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source), Object(Varien_Db_Select), 'catalog_product...', Array, false) #9 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_product...', 'catalog_product...', false) #10 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): Mage_Index_Model_Resource_Abstract->syncData() #11 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll() #12 /var/www/hcomputers/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll() #13 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll() #14 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll() #15 /var/www/hcomputers/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything() #16 /var/www/hcomputers/shell/indexer.php(212): Mage_Shell_Compiler->run() #17 {main} Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`hcomputers_mag`.`catalog_product_index_eav`, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE)' in /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php:234 Stack trace: #0 /var/www/hcomputers/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #1 /var/www/hcomputers/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #2 /var/www/hcomputers/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #3 /var/www/hcomputers/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array) #4 /var/www/hcomputers/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array) #5 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...') #6 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(48): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_product...', Array, false) #7 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source), Object(Varien_Db_Select), 'catalog_product...', Array, false) #8 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_product...', 'catalog_product...', false) #9 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): Mage_Index_Model_Resource_Abstract->syncData() #10 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll() #11 /var/www/hcomputers/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll() #12 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll() #13 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll() #14 /var/www/hcomputers/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything() #15 /var/www/hcomputers/shell/indexer.php(212): Mage_Shell_Compiler->run()
Things I've tried:
- Truncate all the catalog_category_flat_store_# (1 – 40) tables
- Truncate all the catalog_product_flat_# (1 – 40) tables
- Tried, as suggested somewhere else to delete the constraints.
- Ran the Reindexer while apache was turned off.
The weird thing is, that the "Product Prices" index's status is still Processing while there is no indexer running. When I try to remove the lock files in var/locks they are immediately generated again like there is still something trying to index, which isn't.
Any help'd be appreciated, since we're running a blank here!
Best Answer
The
catalog_product_index_eav
table has a foreign key contraint namedLooking at this table's definition
we can see the foreign key definition is
This means for every
entity_id
row incatalog_product_index_eav
, there needs to be an identical, correspondingentity_id
value incatalog_product_entity
.The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in
catalog_product_index_eav
that violates this rule. The next step is identifying what Magento's doing so you can fix the data.If we look at your call stack, this looks like a good place to start debugging
Jumping to that source file, we see the following bit of code
As part of its indexing process, Magento tries to sync data from an "index table" (
getIdxTable
), to a "source" (getMainTable
) table.For this particular index, the index table is
catalog_product_index_eav_idx
, and the source table iscatalog_product_index_eav
.Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)
So, Magento is trying to sync a row from
catalog_product_index_eav_idx
to the tablecatalog_product_index_eav
. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusionsThe
catalog_product_index_eav_idx
hasentity_id
rows that do not exist incatalog_product_entity
.The
catalog_product_index_eav
table has (through previous manipulation with index checks turned off)entity_id
rows that do not exist incatalog_product_entity
.So, your mission here is to figure out which entity_id rows in
catalog_product_index_eav
andcatalog_product_index_eav_idx
don't exist incatalog_product_entity
, and manually delete said rows (fromcatalog_product_index_eav
andcatalog_product_index_eav_idx
).If it were me, and my
catalog_product_entity
table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)Good luck!