Erdem Ece
Reputation: 1775
Magento SQLSTATE error if there is one item left in product inventory
In Magento 1.8 when a customer place an order and if that order contains a product that has 1 item left in product inventory the customers receives friendly message saying "there was an order with your order" but the order get through and payment would be taken. If there is two Item with the same product this does not happen. also it becomes out of stock but the inventory stays 1.
So, the error I get with Payment transaction failed e-mail is:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1499-0-1' for key 'PRIMARY'
1499 is product ID.
I enabled debug in mysql. and this is 'var/debug/pdo_mysql.log';
EXCEPTION
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4368-0-1' for key 'PRIMARY'' in F:\Projects\html2\lib\Zend\Db\Statement\Pdo.php:228
Stack trace:
#0 F:\Projects\html2\lib\Zend\Db\Statement\Pdo.php(228): PDOStatement->execute(Array)
#1 F:\Projects\html2\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 F:\Projects\html2\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 F:\Projects\html2\lib\Zend\Db\Adapter\Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 F:\Projects\html2\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#5 F:\Projects\html2\lib\Varien\Db\Adapter\Pdo\Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#6 F:\Projects\html2\app\code\core\Mage\Catalog\Model\Resource\Product\Indexer\Price\Default.php(564): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#7 F:\Projects\html2\app\code\core\Mage\Catalog\Model\Resource\Product\Indexer\Price\Default.php(142): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->_movePriceDataToIndexTable()
#8 F:\Projects\html2\app\code\core\Mage\Catalog\Model\Resource\Product\Indexer\Price.php(315): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->reindexEntity(Array)
#9 F:\Projects\html2\app\code\core\Mage\Catalog\Model\Resource\Product\Indexer\Price.php(246): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexProductIds(Array)
#10 F:\Projects\html2\app\code\core\Mage\Index\Model\Indexer\Abstract.php(163): Mage_Catalog_Model_Resource_Product_Indexer_Price->catalogProductMassAction(Object(Mage_Index_Model_Event))
#11 F:\Projects\html2\app\code\core\Mage\Catalog\Model\Product\Indexer\Price.php(300): Mage_Index_Model_Indexer_Abstract->callEventHandler(Object(Mage_Index_Model_Event))
#12 F:\Projects\html2\app\code\core\Mage\Index\Model\Indexer\Abstract.php(103): Mage_Catalog_Model_Product_Indexer_Price->_processEvent(Object(Mage_Index_Model_Event))
#13 F:\Projects\html2\app\code\core\Mage\Index\Model\Process.php(282): Mage_Index_Model_Indexer_Abstract->processEvent(Object(Mage_Index_Model_Event))
#14 F:\Projects\html2\app\code\core\Mage\Index\Model\Process.php(384): Mage_Index_Model_Process->processEvent(Object(Mage_Index_Model_Event))
#15 F:\Projects\html2\app\code\core\Mage\Index\Model\Process.php(360): Mage_Index_Model_Process->_processEventsCollection(Object(Mage_Index_Model_Resource_Event_Collection))
#16 [internal function]: Mage_Index_Model_Process->indexEvents('catalog_product', 'mass_action')
#17 F:\Projects\html2\app\code\core\Mage\Index\Model\Indexer.php(378): call_user_func_array(Array, Array)
#18 F:\Projects\html2\app\code\core\Mage\Index\Model\Indexer.php(223): Mage_Index_Model_Indexer->_runAll('indexEvents', Array)
#19 F:\Projects\html2\app\code\core\Mage\CatalogInventory\Model\Observer.php(983): Mage_Index_Model_Indexer->indexEvents('catalog_product', 'mass_action')
#20 F:\Projects\html2\app\code\core\Mage\Core\Model\App.php(1338): Mage_CatalogInventory_Model_Observer->reindexProductsMassAction(Object(Varien_Event_Observer))
#21 F:\Projects\html2\app\code\core\Mage\Core\Model\App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogInventory_Model_Observer), 'reindexProducts...', Object(Varien_Event_Observer))
#22 F:\Projects\html2\app\Mage.php(448): Mage_Core_Model_App->dispatchEvent('end_process_eve...', Array)
#23 F:\Projects\html2\app\code\core\Mage\Index\Model\Indexer.php(334): Mage::dispatchEvent('end_process_eve...')
#24 F:\Projects\html2\app\code\core\Mage\CatalogInventory\Model\Stock\Item.php(792): Mage_Index_Model_Indexer->processEntityAction(Object(Mage_CatalogInventory_Model_Stock_Item), 'cataloginventor...', 'save')
#25 F:\Projects\html2\app\code\core\Mage\Core\Model\Abstract.php(319): Mage_CatalogInventory_Model_Stock_Item->_afterSave()
#26 F:\Projects\html2\app\code\core\Mage\CatalogInventory\Model\Observer.php(746): Mage_Core_Model_Abstract->save()
#27 F:\Projects\html2\app\code\core\Mage\Core\Model\App.php(1338): Mage_CatalogInventory_Model_Observer->reindexQuoteInventory(Object(Varien_Event_Observer))
#28 F:\Projects\html2\app\code\core\Mage\Core\Model\App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogInventory_Model_Observer), 'reindexQuoteInv...', Object(Varien_Event_Observer))
#29 F:\Projects\html2\app\Mage.php(448): Mage_Core_Model_App->dispatchEvent('sales_model_ser...', Array)
#30 F:\Projects\html2\app\code\core\Mage\Sales\Model\Service\Quote.php(191): Mage::dispatchEvent('sales_model_ser...', Array)
#31 F:\Projects\html2\app\code\core\Mage\Sales\Model\Service\Quote.php(249): Mage_Sales_Model_Service_Quote->submitOrder()
#32 F:\Projects\html2\app\code\core\Mage\Checkout\Model\Type\Onepage.php(785): Mage_Sales_Model_Service_Quote->submitAll()
#33 F:\Projects\html2\app\code\core\Mage\Checkout\controllers\OnepageController.php(579): Mage_Checkout_Model_Type_Onepage->saveOrder()
#34 F:\Projects\html2\app\code\core\Mage\Core\Controller\Varien\Action.php(418): Mage_Checkout_OnepageController->saveOrderAction()
#35 F:\Projects\html2\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('saveOrder')
#36 F:\Projects\html2\app\code\core\Mage\Core\Controller\Varien\Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#37 F:\Projects\html2\app\code\core\Mage\Core\Model\App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#38 F:\Projects\html2\app\Mage.php(684): Mage_Core_Model_App->run(Array)
#39 F:\Projects\html2\index.php(87): Mage::run('', 'store')
#40 {main}
things that I have done; clean all the log files remove tax classes clean cahches
edit: after the comment bellow the system log is this:
2014-06-07T12:14:50+00:00 ERR (3): Warning: include(Mage\Realex\Model\Remote.php): failed to open stream: No such file or directory in F:\Projects\html2\lib\Varien\Autoload.php on line 93
2014-06-07T12:14:50+00:00 ERR (3): Warning: include(): Failed opening 'Mage\Realex\Model\Remote.php' for inclusion (include_path='F:\Projects\html2\app\code\local;F:\Projects\html2\app\code\community;F:\Projects\html2\app\code\core;F:\Projects\html2\lib;.;F:\Projects\xampp\php\PEAR') in F:\Projects\html2\lib\Varien\Autoload.php on line 93
2014-06-07T12:14:52+00:00 ERR (3): Strict Notice: Only variables should be passed by reference in F:\Projects\html2\app\design\frontend\magma\default\template\directory\currency-top.phtml on line 5
2014-06-07T12:15:04+00:00 ERR (3): Warning: include(Mage\Realex\Model\Remote.php): failed to open stream: No such file or directory in F:\Projects\html2\lib\Varien\Autoload.php on line 93
2014-06-07T12:15:04+00:00 ERR (3): Warning: include(): Failed opening 'Mage\Realex\Model\Remote.php' for inclusion (include_path='F:\Projects\html2\app\code\local;F:\Projects\html2\app\code\community;F:\Projects\html2\app\code\core;F:\Projects\html2\lib;.;F:\Projects\xampp\php\PEAR') in F:\Projects\html2\lib\Varien\Autoload.php on line 93
2014-06-07T12:15:04+00:00 ERR (3): Strict Notice: Only variables should be passed by reference in F:\Projects\html2\app\design\frontend\magma\default\template\directory\currency-top.phtml on line 5
2014-06-07T12:15:14+00:00 ERR (3): Warning: include(Mage\Realex\Model\Remote.php): failed to open stream: No such file or directory in F:\Projects\html2\lib\Varien\Autoload.php on line 93
2014-06-07T12:15:14+00:00 ERR (3): Warning: include(): Failed opening 'Mage\Realex\Model\Remote.php' for inclusion (include_path='F:\Projects\html2\app\code\local;F:\Projects\html2\app\code\community;F:\Projects\html2\app\code\core;F:\Projects\html2\lib;.;F:\Projects\xampp\php\PEAR') in F:\Projects\html2\lib\Varien\Autoload.php on line 93
EDIT: error starts after this query in pdo_mysql.log:
## 2014-06-08 19:10:41
## 1716 ## QUERY
SQL: 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`
Upvotes: 0
Views: 1396
Answers (1)
jahnni
Reputation: 21
We used to have a similar problem on magento 1.7, it happened because we introduced a "new" type of product that is a bundle of configurable products. In this case we had some products that were at the same time parent of some products and children of some other (the bundle ones) that way when _copyRelationIndexData was called by reindexProductIds catalog_product_index_price_tmp table was filled with some rows belonging to "composite products" causing the "Integrity constraint violation" when reindexEntity is called. Long story short we overrode _copyRelationIndexData method this way :
protected function _copyRelationIndexData($parentIds, $excludeIds = null)
{
if(!is_null($excludeIds) && is_array($excludeIds)){
$excludeIds = array_merge($excludeIds,$parentIds);
}
return parent::_copyRelationIndexData($parentIds, $excludeIds);
}
Upvotes: 1
Related Questions
- IF ROW_COUNT() return error
- Getting error while adding product to cart
- Inventory count of my database keeps starting at 28?
- Field with value not exists (MySQL)
- php cart out of stock mysql
- MySQL count issue
- MySQL COUNT items in stock
- The stock was out of limit, still insert into order table
- total inventory stock mysql
- Stock system - SQL not showing out of stock items