cancel
Showing results for 
Search instead for 
Did you mean: 

Category Products Reindex Unkown Error

   Did you know you can see the translated content as per your choice?

Translation is in progress. Please check again after few minutes.

Category Products Reindex Unkown Error

I am having a reindex issue with Category Products.

 

Details:

  • Sites been up and running for a month.
  • I have been using magmi without errors to import new products in bulk from a CSV files from the beginning.
  • error only started 2 days ago.
  • yes i have reindexed multiple times over the month and not had problems prior to 2 days ago.
  • i have checked all other forum threads with no solution relevant or found that affects the issue.

error code output:

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 (`blackrav_mage`.`mg_catalog_category_product_index`, CONSTRAINT `FK_OZSS_CAT_CTGR_PRD_IDX_PRD_ID_OZSS_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `mg_catalog_product_entity` (`)' in /home/blackrav/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/blackrav/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/blackrav/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/blackrav/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/blackrav/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /home/blackrav/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `mg...', Array)
#5 /home/blackrav/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `mg...', Array)
#6 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `mg...')
#7 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(49): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'mg_catalog_cate...', Array, false)
#8 /home/blackrav/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), 'mg_catalog_cate...', Array, false)
#9 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('mg_catalog_cate...', 'mg_catalog_cate...', false)
#10 /home/blackrav/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php(935): Mage_Index_Model_Resource_Abstract->syncData()
#11 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Category_Indexer_Product->reindexAll()
#12 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#13 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#14 /home/blackrav/public_html/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#15 /home/blackrav/public_html/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 (`blackrav_mage`.`mg_catalog_category_product_index`, CONSTRAINT `FK_OZSS_CAT_CTGR_PRD_IDX_PRD_ID_OZSS_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `mg_catalog_product_entity` (`), query was: INSERT INTO `mg_catalog_category_product_index` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `mg_catalog_category_product_index_idx`.`category_id`, `mg_catalog_category_product_index_idx`.`product_id`, `mg_catalog_category_product_index_idx`.`position`, `mg_catalog_category_product_index_idx`.`is_parent`, `mg_catalog_category_product_index_idx`.`store_id`, `mg_catalog_category_product_index_idx`.`visibility` FROM `mg_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/blackrav/public_html/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /home/blackrav/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/blackrav/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/blackrav/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/blackrav/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `mg...', Array)
#4 /home/blackrav/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `mg...', Array)
#5 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `mg...')
#6 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(49): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'mg_catalog_cate...', Array, false)
#7 /home/blackrav/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), 'mg_catalog_cate...', Array, false)
#8 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('mg_catalog_cate...', 'mg_catalog_cate...', false)
#9 /home/blackrav/public_html/app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php(935): Mage_Index_Model_Resource_Abstract->syncData()
#10 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Category_Indexer_Product->reindexAll()
#11 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#12 /home/blackrav/public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#13 /home/blackrav/public_html/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#14 /home/blackrav/public_html/shell/indexer.php(216): Mage_Shell_Compiler->run()
#15 {main}

what i have tried:

  • i have tried truncating the category tables and recreating them using SQL from a fresh install on local machine. this broke the categories completely, reverted back to backup.
  • i have tried removing all products from categories and deleting/recreating categories. Reindexed with only default empty category still errors.

any help is appreciated hopefully a fresh install won't be neccessary, however if its needed i have a means of easily and quickly recreating my products using a webapp magmi csv compiler ive built. i just want to avoiding doing a fresh install as i don't wish to interrupt my M2E plugin.