Price Index Error During Order Creation

[jkossoff]

We have recently updated our Magento version from 1.9.1.1 to 1.9.2.2.  Since then, we have intermittently experienced errors during order creation on both the admin and customer facing sides of the site.  The orders do get placed correctly, but customers are understandably concerned and confused that there is something wrong with the order, leading to duplicate orders, phone calls, etc.

On the admin side, the error message we receive is:

Order saving error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '49837-0-1' for key 'PRIMARY', query was: INSERT INTO `catalog_product_index_price_tmp` SELECT `catalog_product_index_price_final_tmp`.`entity_id`, `catalog_product_index_price_final_tmp`.`customer_group_id`, `catalog_product_index_price_final_tmp`.`website_id`, `catalog_product_index_price_final_tmp`.`tax_class_id`, `catalog_product_index_price_final_tmp`.`orig_price` AS `price`, `catalog_product_index_price_final_tmp`.`price` AS `final_price`, `catalog_product_index_price_final_tmp`.`min_price`, `catalog_product_index_price_final_tmp`.`max_price`, `catalog_product_index_price_final_tmp`.`tier_price`, `catalog_product_index_price_final_tmp`.`group_price` FROM `catalog_product_index_price_final_tmp`

Initially, I thought this was due to the very slow processing speed of the tables, as they were using the MySQL memory engine.  I have switched them to InnoDB and while they run much faster, this integrity constraint violation is still a problem.

Does anyone have any ideas about what could be causing this?

Thank you!

[subjectone]

We have the same problem. Did you find a solution?

Thanks!

[jandradewarmon]

Were you able to find a solution for this? We are experiencing the same problem and its creating a lot of issues.

[jkossoff]

I created a module to rewrite the price indexer to a copy from the Magento source.  I then added try/catches with logging to the copy in an attempt to get more information about the problem, but the errors ceased and nothing was ever logged.  I don't know if that really qualifies as a solution--the module definitely deserves its name as a hack--but I have attached the rewritten indexer code below.

Good luck!

<?php
class Soularpanic_IndexerErrorSilencerHack_Model_Resource_Product_Indexer_Price
    extends Mage_Catalog_Model_Resource_Product_Indexer_Price {
    public function reindexProductIds($ids) {
        if (empty($ids)) {
            return $this;
        }
        if (!is_array($ids)) {
            $ids = array($ids);
        }
        $this->clearTemporaryIndexTable();
        $write  = $this->_getWriteAdapter();
        // retrieve products types
        $select = $write->select()
            ->from($this->getTable('catalog/product'), array('entity_id', 'type_id'))
            ->where('entity_id IN(?)', $ids);
        $pairs  = $write->fetchPairs($select);
        $byType = array();
        foreach ($pairs as $productId => $productType) {
            $byType[$productType][$productId] = $productId;
        }
        $compositeIds    = array();
        $notCompositeIds = array();
        foreach ($byType as $productType => $entityIds) {
            $indexer = $this->_getIndexer($productType);
            if ($indexer->getIsComposite()) {
                $compositeIds += $entityIds;
            } else {
                $notCompositeIds += $entityIds;
            }
        }
        if (!empty($notCompositeIds)) {
            $select = $write->select()
                ->from(
                    array('l' => $this->getTable('catalog/product_relation')),
                    'parent_id')
                ->join(
                    array('e' => $this->getTable('catalog/product')),
                    'e.entity_id = l.parent_id',
                    array('type_id'))
                ->where('l.child_id IN(?)', $notCompositeIds);
            $pairs  = $write->fetchPairs($select);
            foreach ($pairs as $productId => $productType) {
                if (!in_array($productId, $ids)) {
                    $ids[] = $productId;
                    $byType[$productType][$productId] = $productId;
                    $compositeIds[$productId] = $productId;
                }
            }
        }
        if (!empty($compositeIds)) {
            try {
                $this->_copyRelationIndexData($compositeIds, $notCompositeIds);
            }
            catch (Exception $e) {
                Mage::log("Error at _copyRelationIndexData: {$e->getMessage()}", null, 'trs_indexer_silencer.log');
            }
        }
        $indexers = $this->getTypeIndexers();
        foreach ($indexers as $indexer) {
            if (!empty($byType[$indexer->getTypeId()])) {
                try {
                    $indexer->reindexEntity($byType[$indexer->getTypeId()]);
                }
                catch (Exception $e) {
                    Mage::log("Error at reindexEntry: {$e->getMessage()}", null, 'trs_indexer_silencer.log');
                }
            }
        }
        try {
            $this->_copyIndexDataToMainTable($ids);
        }
        catch (Exception $e) {
            Mage::log("Error at _copyIndexDataToMainTable: {$e->getMessage()}", null, 'trs_indexer_silencer.log');
        }
        return $this;
    }
}