3

I have been straggiling with this over a month and I'm going to kill myself or close the business. Please help me.

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

Thank you

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`

edit2: there is a problem with duplicated entity.

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 NULL DEFAULT '0' COMMENT 'Tax Class ID',
    `price` DECIMAL(12,4) NULL DEFAULT NULL COMMENT 'Price',
    `final_price` DECIMAL(12,4) NULL DEFAULT NULL COMMENT 'Final Price',
    `min_price` DECIMAL(12,4) NULL DEFAULT NULL COMMENT 'Min Price',
    `max_price` DECIMAL(12,4) NULL DEFAULT NULL COMMENT 'Max Price',
    `tier_price` DECIMAL(12,4) NULL DEFAULT NULL COMMENT 'Tier Price',
    `group_price` DECIMAL(12,4) NULL DEFAULT NULL COMMENT 'Group price',
    PRIMARY KEY (`entity_id`, `customer_group_id`, `website_id`),
    INDEX `IDX_CATALOG_PRODUCT_INDEX_PRICE_TMP_CUSTOMER_GROUP_ID` (`customer_group_id`),
    INDEX `IDX_CATALOG_PRODUCT_INDEX_PRICE_TMP_WEBSITE_ID` (`website_id`),
    INDEX `IDX_CATALOG_PRODUCT_INDEX_PRICE_TMP_MIN_PRICE` (`min_price`)
)

as you see entity_id, customer_group_id and website_id are primary keys. one of them is being duplicated somehow and I don't know how to find that dublicated data. is it entity_id? or customer_group_id? and where is it coming from? What I did was for now, I dropped this table and created without making entity_id, customer_group_id, website_id PRIMARY KEY. Now it's working but I don't think it is a good idea at all. Thank you.

  • Within 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() you should do a Mage::log() on $select->getSelect() after it is set and let us know what the query is having an issue with. Look in var/log/system.log to see what it says. – user2045 Jun 6 '14 at 22:29
  • @user2045 thank you so much. I edited my question as the error very long. I could not write here. thanks. the error at the end. – Erdem Ece Jun 7 '14 at 12:18
  • I need you to add a Mage::log() line to the file i told you to please to see the full sql statement. – user2045 Jun 8 '14 at 19:14
  • see my question. I edited I added it there is nothing displaying... only those codes. i don't even know if they are right.. @user2045 – Erdem Ece Jun 8 '14 at 19:26
  • Please do 2 queries and add them to your answer: 1) SELECT entity_id FROM catalog_product_index_price_final_tmp WHERE entity_id LIKE '1499%' 2) SELECT entity_id FROM catalog_product_index_price_tmp WHERE entity_id LIKE '1499%' – user2045 Jun 9 '14 at 23:58
1

It could be a guess, and can be an alternate to your problem too. What i think is Your settings:

Admin: Catalog > Manage products Choose a product: inventory

  • Qty = Your quantity

  • Qty for Item's Status to Become Out of Stock = 0

  • Stock availability: in stock

May not be proper:

There is a similar setting in admin also:

Admin: System > Configuration Inventory: Qty for Item's Status to

Become Out of Stock = 0

Try checking these two configurations, this may be the cause.

Alternate:Change your settings to 1, for out of stock

Admin: Catalog > Manage products Choose a product: inventory

  • Qty = Your quantity

  • Qty for Item's Status to Become Out of Stock = 1//Update this

  • Stock availability: in stock

This way whenever you have 1 product in inventory it will not be able to checkout, and you can resolve your issue.

0

You need to find out what the SQL query is so you can locate the table with the duplicate keys.

You might try truncating sales_flat_quote and sales_flat_quote_item tables, as well as incrementing all the increment_last_id values in eav_entity_store by 10,000 or some large number.

  • it did not work.. do you think is there anything related to catalog_product_index_price_tmp table? – Erdem Ece Jun 8 '14 at 22:17
  • Why guess? Follow the suggestions and get the full SQL query that's causing the error. This will tell you which table is the culprit. – Steve Robbins Jun 9 '14 at 17:25
  • please see my question. I edited it. – Erdem Ece Jun 9 '14 at 19:29
  • Try truncating catalog_product_index_price_tmp and then reindexing prices. – Steve Robbins Jun 9 '14 at 19:52
  • I did that too.. – Erdem Ece Jun 9 '14 at 23:17

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.