Magento Category Products reindex error - Integrity constraint violation

Question

I recently upgraded a magento from 1.5 to 1.9 and everything is working except for reindexing of Category Products.

On the admin backend, I just get a general error that the process has failed.

so, I did it via command line using the shell/indexer.php and this is the output:

[www-data@DevServer shell]$ php -f indexer.php reindexall
Product Attributes index was rebuilt successfully in 00:00:20
Product Prices index was rebuilt successfully in 00:00:06
Catalog URL Rewrites index was rebuilt successfully in 00:02:59
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 (`dev_salveo`.`catalog_category_product_index`, CONSTRAINT `FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON )' in /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/www-data/dev.my-domain.co.uk/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/www-data/dev.my-domain.co.uk/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#6 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#7 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(49): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_categor...', Array, false)
#8 /home/www-data/dev.my-domain.co.uk/public_html/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_categor...', Array, false)
#9 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#10 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php(935): Mage_Index_Model_Resource_Abstract->syncData()
#11 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Category_Indexer_Product->reindexAll()
#12 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#13 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#14 /home/www-data/dev.my-domain.co.uk/public_html/shell/indexer.php(167): Mage_Index_Model_Process->reindexEverything()
#15 /home/www-data/dev.my-domain.co.uk/public_html/shell/indexer.php(215): 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 (`dev_salveo`.`catalog_category_product_index`, CONSTRAINT `FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON ), 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 /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /home/www-data/dev.my-domain.co.uk/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/www-data/dev.my-domain.co.uk/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /home/www-data/dev.my-domain.co.uk/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#6 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(49): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_categor...', Array, false)
#7 /home/www-data/dev.my-domain.co.uk/public_html/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_categor...', Array, false)
#8 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_categor...', 'catalog_categor...', false)
#9 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php(935): Mage_Index_Model_Resource_Abstract->syncData()
#10 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Category_Indexer_Product->reindexAll()
#11 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#12 /home/www-data/dev.my-domain.co.uk/public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#13 /home/www-data/dev.my-domain.co.uk/public_html/shell/indexer.php(167): Mage_Index_Model_Process->reindexEverything()
#14 /home/www-data/dev.my-domain.co.uk/public_html/shell/indexer.php(215): Mage_Shell_Compiler->run()
#15 {main}
Catalog Search Index index was rebuilt successfully in 00:00:24
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00

Any idea what this error means? how can I fix it?

Answer 1

The meaning of the error is "A value for a foreign key is being added or updated in the index table although the record doesn't exists in the main table".

Make sure to back up the database first and the follow this link for the soul

Answer 2

I've answered this before Checkout there All indexing are stuck at `processing`

Let me know if its worked for you or not.

Answer 3

Refer to this site for the solution: http://www.createhosting.co.nz/support/knowledgebase.php?action=displayarticle&id=70

This error occurs when trying to run the Category Products index, and is due to the indexing process attempting to insert a row into the catalog_category_product_index table, where either the product_id or the category_id don't exist. This causes the "Intergrity Constraint Violation".

This can sometimes happen when a database gets corrupt, often from 3rd party desktop type software or a badly written extension.

To fix this, first check that this is indeed the issue. Backup your database and then run the following SQL to see if any product and/or category associations are incorrect:

Determine incorrect product associations

SELECT * FROM catalog_category_product WHERE product_id not in (select entity_id from catalog_product_entity);

Determine incorrect categories

SELECT * FROM catalog_category_product WHERE category_id not in (select entity_id from catalog_category_entity);

---

If that returns a record, you'll know exactly which product is in a non-existent category or which category has a non-existent product. Run the following SQL to then remove those invalid records:

Delete incorrect product associations

DELETE FROM catalog_category_product WHERE product_id not in (select entity_id from catalog_product_entity);

Delete incorrect categories

DELETE FROM catalog_category_product WHERE category_id not in (select entity_id from catalog_category_entity);

-- After this, Category/Products Reindexing works as expected ;)

Answer 4

Someone at SO had a similar answer and that worked for me: https://stackoverflow.com/a/29676254/2332336

The queries I used: http://pastebin.com/raw.php?i=ZYKzJxnz

Answer 5

The easiest solution for this was to delete those records where causes duplicate from core_url_rewrite table and reindex again