Magento – Keep getting Integrity Constraint Violation on checkout/place order/complete order

checkoutindexingmagento-1.9

Here's the problem. I have a constantly running script that adds/changes products' quantities and prices, based on an API from a foreign 3rd party CRM. (Required by the client).

The synchronization is going great, and the products appear and show correctly, as well the quantity and out of stock.

However, the big problem becomes in the checkout! Every now and then (not always) there happens an integrity constraint violation when the user tries to check out.

I have set the index on 'Update on Save', since we're using the Community Edition. Is that the cause of the problem? Will removing the automatic indexing for the prices (which rarely change) and changing to manual mode fix this issue? Or will other indexes also cause this problem?

An example of the stacktrace I'm getting in the log:

    Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4418-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`' in /mnt/webdrive/testing.example.com/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /mnt/webdrive/testing.example.com/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /mnt/webdrive/testing.example.com/app/code/core/Zend/Db/Statement.php(311): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /mnt/webdrive/testing.example.com/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /mnt/webdrive/testing.example.com/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /mnt/webdrive/testing.example.com/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /mnt/webdrive/testing.example.com/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Default.php(564): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#6 /mnt/webdrive/testing.example.com/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Configurable.php(71): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->_movePriceDataToIndexTable()
#7 /mnt/webdrive/testing.example.com/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(315): Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable->reindexEntity(Array)
#8 /mnt/webdrive/testing.example.com/app/code/core/Mage/CatalogInventory/Model/Observer.php(749): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexProductIds(Array)
#9 /mnt/webdrive/testing.example.com/app/code/core/Mage/Core/Model/App.php(1358): Mage_CatalogInventory_Model_Observer->reindexQuoteInventory(Object(Varien_Event_Observer))
#10 /mnt/webdrive/testing.example.com/app/code/core/Mage/Core/Model/App.php(1337): Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogInventory_Model_Observer), 'reindexQuoteInv...', Object(Varien_Event_Observer))
#11 /mnt/webdrive/testing.example.com/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('sales_model_ser...', Array)
#12 /mnt/webdrive/testing.example.com/app/code/core/Mage/Sales/Model/Service/Quote.php(191): Mage::dispatchEvent('sales_model_ser...', Array)
#13 /mnt/webdrive/testing.example.com/app/code/core/Mage/Sales/Model/Service/Quote.php(249): Mage_Sales_Model_Service_Quote->submitOrder()
#14 /mnt/webdrive/testing.example.com/app/code/core/Mage/Checkout/Model/Type/Onepage.php(812): Mage_Sales_Model_Service_Quote->submitAll()
#15 /mnt/webdrive/testing.example.com/app/code/core/Mage/Checkout/controllers/OnepageController.php(579): Mage_Checkout_Model_Type_Onepage->saveOrder()
#16 /mnt/webdrive/testing.example.com/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Checkout_OnepageController->saveOrderAction()
#17 /mnt/webdrive/testing.example.com/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('saveOrder')
#18 /mnt/webdrive/testing.example.com/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#19 /mnt/webdrive/testing.example.com/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#20 /mnt/webdrive/testing.example.com/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#21 /mnt/webdrive/testing.example.com/index.php(83): Mage::run('', 'store')

Best Answer

I was facing the same issue and i get it resolved by firing following query which will remove primary key from table.

--
-- Table structure for table `catalog_product_index_price_tmp`
--
DROP TABLE IF EXISTS `catalog_product_index_price_tmp`;
CREATE TABLE `catalog_product_index_price_tmp` (
 `entity_id` int(10) UNSIGNED NOT NULL COMMENT 'Entity ID',
 `customer_group_id` smallint(5) UNSIGNED NOT NULL COMMENT 'Customer     Group ID',
 `website_id` smallint(5) UNSIGNED NOT NULL COMMENT 'Website ID',
 `tax_class_id` smallint(5) UNSIGNED DEFAULT '0' COMMENT 'Tax Class ID',
 `price` decimal(12,4) DEFAULT NULL COMMENT 'Price',
 `final_price` decimal(12,4) DEFAULT NULL COMMENT 'Final Price',
 `min_price` decimal(12,4) DEFAULT NULL COMMENT 'Min Price',
 `max_price` decimal(12,4) DEFAULT NULL COMMENT 'Max Price',
 `tier_price` decimal(12,4) DEFAULT NULL COMMENT 'Tier Price',
 `group_price` decimal(12,4) DEFAULT NULL COMMENT 'Group price'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='Catalog Product Price     Indexer Temp Table';

--
-- Indexes for dumped tables
--

--
-- Indexes for table `catalog_product_index_price_tmp`
--
ALTER TABLE `catalog_product_index_price_tmp`
 ADD KEY `IDX_CATALOG_PRODUCT_INDEX_PRICE_TMP_CUSTOMER_GROUP_ID` (`customer_group_id`),
 ADD KEY `IDX_CATALOG_PRODUCT_INDEX_PRICE_TMP_WEBSITE_ID` (`website_id`),
 ADD KEY `IDX_CATALOG_PRODUCT_INDEX_PRICE_TMP_MIN_PRICE` (`min_price`);

I have been searching all over the world, but i didn't found any solution for the same. The issue happen mostly when a simple product of configurable having last quantity.

Tables starting with _tmp are the temporary tables, Updating such table will not create any issue in future.

Related Solutions

Magento – Magento 1.8 reindex all

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.
The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!

Magento – Custom cron causing one page checkout deadlocks

Adding a sleep(3) to allow the db to settle in between cancel transactions could be a good workaround.

Best Answer

Related Solutions

Magento – Magento 1.8 reindex all

Magento – Custom cron causing one page checkout deadlocks

Related Topic