2

We're unable to add configurable products to our website anymore, when we try to we get the following error in our Magento exception.log.

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000] [1062] Duplicate entry '2231-1' for key 'UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID', query was: INSERT INTO `cataloginventory_stock_item` (`product_id`, `stock_id`, `qty`, `is_in_stock`, `use_config_manage_stock`, `use_config_qty_increments`, `use_config_enable_qty_inc`, `is_decimal_divided`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' in /var/www/magento-root/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /var/www/magento-root/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute()
#1 /var/www/magento-root/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute()
#2 /var/www/magento-root/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute()
#3 /var/www/magento-root/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query()
#4 /var/www/magento-root/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query()
#5 /var/www/magento-root/lib/Zend/Db/Adapter/Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query()
#6 /var/www/magento-root/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(453): Zend_Db_Adapter_Abstract->insert()
#7 /var/www/magento-root/app/code/core/Mage/Core/Model/Abstract.php(318): Mage_Core_Model_Resource_Db_Abstract->save()
#8 /var/www/magento-root/app/code/core/Mage/CatalogInventory/Model/Observer.php(159): Mage_Core_Model_Abstract->save()
#9 /var/www/magento-root/app/code/core/Mage/Core/Model/App.php(1358): Mage_CatalogInventory_Model_Observer->saveInventoryData()
#10 /var/www/magento-root/app/code/core/Mage/Core/Model/App.php(1337): Mage_Core_Model_App->_callObserverMethod()
#11 /var/www/magento-root/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent()
#12 /var/www/magento-root/app/code/core/Mage/Core/Model/Abstract.php(466): Mage::dispatchEvent()
#13 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Product.php(549): Mage_Core_Model_Abstract->_afterSave()
#14 /var/www/magento-root/app/code/core/Mage/Core/Model/Abstract.php(319): Mage_Catalog_Model_Product->_afterSave()
#15 /var/www/magento-root/app/code/core/Mage/Adminhtml/controllers/Catalog/ProductController.php(728): Mage_Core_Model_Abstract->save()
#16 /var/www/magento-root/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Catalog_ProductController->saveAction()
#17 /var/www/magento-root/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch()
#18 /var/www/magento-root/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match()
#19 /var/www/magento-root/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#20 /var/www/magento-root/app/Mage.php(684): Mage_Core_Model_App->run()
#21 /var/www/magento-root/index.php(89): Mage::run()
#22 {main}

Also we are unable to re-index our Product Flat Data and we just get the message...

There was a problem with reindexing process.

From the command line I get more detail. . .

# php indexer.php --reindex catalog_product_flat
Product Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento_root`.`#sql-921_2c6865`, CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON D)' in /var/www/magento-root/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/magento-root/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/magento-root/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/magento-root/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/magento-root/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /var/www/magento-root/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `rr...', Array)
#5 /var/www/magento-root/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `rr...', Array)
#6 /var/www/magento-root/lib/Varien/Db/Adapter/Pdo/Mysql.php(423): Varien_Db_Adapter_Pdo_Mysql->query('ALTER TABLE `rr...')
#7 /var/www/magento-root/lib/Varien/Db/Adapter/Pdo/Mysql.php(2833): Varien_Db_Adapter_Pdo_Mysql->raw_query('ALTER TABLE `rr...')
#8 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(815): Varien_Db_Adapter_Pdo_Mysql->addForeignKey('FK_CAT_...', 'catalog_pro...', 'entity_id', 'catalog_pro...', 'entity_id', 'CASCADE', 'CASCADE')
#9 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php(1389): Mage_Catalog_Model_Resource_Product_Flat_Indexer->prepareFlatTable(1)
#10 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Product/Flat/Indexer.php(296): Mage_Catalog_Model_Resource_Product_Flat_Indexer->reindexAll()
#11 /var/www/magento-root/app/code/core/Mage/Catalog/Model/Product/Indexer/Flat.php(350): Mage_Catalog_Model_Product_Flat_Indexer->reindexAll()
#12 /var/www/magento-root/app/code/core/Mage/Index/Model/Process.php(212): Mage_Catalog_Model_Product_Indexer_Flat->reindexAll()
#13 /var/www/magento-root/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#14 /var/www/magento-root/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#15 /var/www/magento-root/shell/indexer.php(216): Mage_Shell_Compiler->run()
#16 {main}

I was exporting the db yesturday and by accident I believe started to to execute a sql db dumb on the database rather than dump the database

2

It just says, that you have a duplicate key.

Maybe it's enough to just disable flats, reindex all, enable flats, reindex all. That should be enough after i've seen your edited post

| improve this answer | |
  • still get the same error after disabling flats. Any idea how I could find and remove the duplicate key from the db – Holly Mar 8 '16 at 12:21
  • what if I tried to truncate catalog_product_flat? – Holly Mar 8 '16 at 12:27
  • Use PhpMyAdmin or MysqlWorkbench to find the product with product_id 2231-1in the table cataloginventory_stock_item. If it apears twice, you should delete the older ones. Attention: You should always do things like this on a testing/development environment first, not on live! It may cause damage that can't be repaired. – Michael Mar 8 '16 at 12:32
  • I can't find any entry for 2231-1, there is just an entry for 2231 – Holly Mar 8 '16 at 12:39
2

I checked our list of products in Catalog > Manage Products and saw that the highest ID was 2227 so I removed all records from our cataloginventory_stock_item & catalog_product_flat that had a product_id > 2227.

After that it began working.

| improve this answer | |
  • This would mean, that your autoincrement IDs where gone wrong. That seems really strange. – Michael Mar 8 '16 at 15:56

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.