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

1 Answer 1

Reset to default
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

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

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.