0

I receive an error when trying to reindex Product Prices: "Some problem with reindexing process."

Digging some more, I extracted this error message on exception.log:

2015-10-06T17:17:52+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db1192491-myDB3`.`catalog_product_index_tier_price`, CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_TIER_PRICE_ENTITY` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) O)

Trace: #0 /is/htdocs/wp1192491_HPZFWNW898/www/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /is/htdocs/wp1192491_HPZFWNW898/www/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /is/htdocs/wp1192491_HPZFWNW898/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#3 /is/htdocs/wp1192491_HPZFWNW898/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#4 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price.php(412): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#5 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price.php(353): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price->_prepareTierPriceIndex()
#6 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price->reindexAll()
#7 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#8 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#9 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#10 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#11 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#12 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Core/Controller/Varien/Front.php(177): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#13 /is/htdocs/wp1192491_HPZFWNW898/www/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch()
#14 /is/htdocs/wp1192491_HPZFWNW898/www/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#15 /is/htdocs/wp1192491_HPZFWNW898/www/index.php(78): Mage::run('', 'store')
#16 {main}

I tried this solution on MySQL but didn't work (MySQL says no row was changed):

delete FROM `catalog_product_index_tier_price` where entity_id not in (select entity_id from catalog_product_entity);

Do you have some suggestions about what could I try?

Quite a lot of people have similar issues but couldn't find any of them related to the Product Price index and the tier prices in particular.

Thanks for any ideas,

EDIT: also tried removing the contents on var/locks directory, to no avail. I re-saved as well the products with tier prices, but it made no difference.

  • As a sidenote, in this SO thread they say that it's safe to truncate the Product Prices Index table, and then re-build it, without any consecuences. Do you think I could take that route? (stackoverflow.com/questions/22590843/…) – Peanuts Nov 2 '15 at 19:01
  • I found this link, with a script that may help but I'm not quite sure if it's safe: gist.github.com/cgdangelo/2175558 ALTER TABLE catalog_product_index_tier_price ADD CONSTRAINT FK_CAT_PRD_IDX_TIER_PRICE_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID FOREIGN KEY (customer_group_id) REFERENCES customer_group (customer_group_id) ON DELETE CASCADE ON UPDATE CASCADE; – Peanuts Nov 3 '15 at 19:56
0

Try doing it programmatically. This way:

$indexCollection = Mage::getModel('index/process')->getCollection();
    foreach ($indexCollection as $index) {
      $index->reindexAll();
  }

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.