2

I'm getting the following error when I try to re-index Category Products. Any way I can repair the database? I tried the answers from this question but neither of the queries suggested worked.

I wonder if I truncated the table catalog_category_product_index if that would fix things by causing the table data to be re-generate or would just break things further.

php indexer.php --reindex catalog_category_product
Category Products 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 (`local_080316`.`catalog_category_product_index`, CONSTRAINT `FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity`)' in /var/www/magento-root/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/magento-root/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/magento-root/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/magento-root/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/magento-root/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /var/www/magento-root/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `rr...', Array)
#5 /var/www/magento-root/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `rr...', Array)
#6 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `rr...')
#7 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(49): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_cat...', Array, false)
#8 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Category_Indexer_Product), Object(Varien_Db_Select), 'catalog_cat...', Array, false)
#9 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_cat...', 'catalog_cat...', false)
#10 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php(935): Mage_Index_Model_Resource_Abstract->syncData()
#11 /var/www/magento-root/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Category_Indexer_Product->reindexAll()
#12 /var/www/magento-root/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#13 /var/www/magento-root/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#14 /var/www/magento-root/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#15 /var/www/magento-root/shell/indexer.php(216): Mage_Shell_Compiler->run()
#16 {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 (`local_080316`.`catalog_category_product_index`, CONSTRAINT `FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity`), query was: INSERT INTO `catalog_category_product_index` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `catalog_category_product_index_idx`.`category_id`, `catalog_category_product_index_idx`.`product_id`, `catalog_category_product_index_idx`.`position`, `catalog_category_product_index_idx`.`is_parent`, `catalog_category_product_index_idx`.`store_id`, `catalog_category_product_index_idx`.`visibility` FROM `catalog_category_product_index_idx` ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)' in /var/www/magento-root/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /var/www/magento-root/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/magento-root/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/magento-root/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/magento-root/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `rr...', Array)
#4 /var/www/magento-root/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `rr...', Array)
#5 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `rr...')
#6 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(49): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_cat...', Array, false)
#7 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Category_Indexer_Product), Object(Varien_Db_Select), 'catalog_cat...', Array, false)
#8 /var/www/magento-root/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_cat...', 'catalog_cat...', false)
#9 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php(935): Mage_Index_Model_Resource_Abstract->syncData()
#10 /var/www/magento-root/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Category_Indexer_Product->reindexAll()
#11 /var/www/magento-root/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#12 /var/www/magento-root/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#13 /var/www/magento-root/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#14 /var/www/magento-root/shell/indexer.php(216): Mage_Shell_Compiler->run()
#15 {main}
11
DELETE FROM `catalog_product_entity_datetime` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_decimal` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_gallery` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_group_price` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_int` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_media_gallery` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_text` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_tier_price` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_product_entity_varchar` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_category_product` WHERE product_id NOT IN(SELECT entity_id FROM `catalog_product_entity`);
DELETE FROM `catalog_category_product` WHERE category_id NOT IN(SELECT entity_id FROM catalog_category_entity); 
DELETE FROM `catalog_product_website` WHERE product_id NOT IN(SELECT entity_id FROM `catalog_product_entity`); 
DELETE FROM `catalog_product_index_eav_idx` WHERE entity_id NOT IN(SELECT entity_id FROM `catalog_product_entity`); 
DELETE FROM `catalog_product_index_eav` WHERE entity_id NOT IN(SELECT entity_id FROM `catalog_product_entity`); 
DELETE FROM `catalog_product_link` WHERE product_id NOT IN(SELECT entity_id FROM `catalog_product_entity`); 
DELETE FROM `catalog_product_relation` WHERE parent_id NOT IN(SELECT entity_id FROM `catalog_product_entity`);

Run this query and then try to reindex

  • this worked perfectly for me. Thank you so much for your help :) – Holly Mar 9 '16 at 16:43
  • Cheers my friend :) – Asish Hira Mar 10 '16 at 9:31
  • It worked for me :) nice post, thanks for help – Jitendra Mandloi Nov 18 '17 at 7:33

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.