Geek Answers Handbook

MySQL error while editing a product in Magento

it av 1.4.1.1 Magento, updated from version 1.3.2.4
When I try to edit a product, an error message appears:

exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6028-0-2' for key 'PRIMARY''

And if I try to override prices, I have the same message in exception.log
I think the problem is in the catalog_product_website table, because if I delete the row, I can start price indexing, but when I try to edit the product to assign it a site, I get the same message.
It is strange that the catalog_product_website table has only 2 columns, and the exception says 3 primary keys table (right?)
Here is the whole stack trace:

 2010-12-07T12:12:41+00:00 ERR (3): exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6028-0-2' for key 'PRIMARY'' in /var/www/ortoweb1314/lib/Zend/Db/Statement/Pdo.php:234 Stack trace: #0 /var/www/ortoweb1314/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #1 /var/www/ortoweb1314/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array) #2 /var/www/ortoweb1314/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array) #3 /var/www/ortoweb1314/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array) #4 /var/www/ortoweb1314/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Default.php(260): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...') #5 /var/www/ortoweb1314/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Default.php(133): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Default->_prepareFinalPriceData('6028') #6 /var/www/ortoweb1314/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price.php(192): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Default->reindexEntity('6028') #7 /var/www/ortoweb1314/app/code/core/Mage/Index/Model/Indexer/Abstract.php(144): Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price->catalogProductSave(Object(Mage_Index_Model_Event)) #8 /var/www/ortoweb1314/app/code/core/Mage/Catalog/Model/Product/Indexer/Price.php(255): Mage_Index_Model_Indexer_Abstract->callEventHandler(Object(Mage_Index_Model_Event)) #9 /var/www/ortoweb1314/app/code/core/Mage/Index/Model/Indexer/Abstract.php(85): Mage_Catalog_Model_Product_Indexer_Price->_processEvent(Object(Mage_Index_Model_Event)) #10 /var/www/ortoweb1314/app/code/core/Mage/Index/Model/Process.php(185): Mage_Index_Model_Indexer_Abstract->processEvent(Object(Mage_Index_Model_Event)) #11 [internal function]: Mage_Index_Model_Process->processEvent(Object(Mage_Index_Model_Event)) #12 /var/www/ortoweb1314/app/code/core/Mage/Index/Model/Indexer.php(252): call_user_func_array(Array, Array) #13 /var/www/ortoweb1314/app/code/core/Mage/Index/Model/Indexer.php(154): Mage_Index_Model_Indexer->_runAll('processEvent', Array) #14 /var/www/ortoweb1314/app/code/core/Mage/Index/Model/Indexer.php(219): Mage_Index_Model_Indexer->indexEvent(Object(Mage_Index_Model_Event)) #15 /var/www/ortoweb1314/app/code/core/Mage/Catalog/Model/Product.php(525): Mage_Index_Model_Indexer->processEntityAction(Object(Mage_Catalog_Model_Product), 'catalog_product', 'save') #16 [internal function]: Mage_Catalog_Model_Product->afterCommitCallback() #17 /var/www/ortoweb1314/app/code/core/Mage/Core/Model/Resource/Abstract.php(104): call_user_func(Array) #18 /var/www/ortoweb1314/app/code/core/Mage/Core/Model/Abstract.php(310): Mage_Core_Model_Resource_Abstract->commit() #19 /var/www/ortoweb1314/app/code/core/Mage/Adminhtml/controllers/Catalog/ProductController.php(663): Mage_Core_Model_Abstract->save() #20 /var/www/ortoweb1314/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Catalog_ProductController->saveAction() #21 /var/www/ortoweb1314/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('save') #22 /var/www/ortoweb1314/app/code/core/Mage/Core/Controller/Varien/Front.php(177): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http)) #23 /var/www/ortoweb1314/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch() #24 /var/www/ortoweb1314/app/Mage.php(596): Mage_Core_Model_App->run(Array) #25 /var/www/ortoweb1314/index.php(80): Mage::run('', 'store') #26 {main}

Hope someone can help me, I'm really off topic :(

to change . I improved the exception log message to see the whole query, and here it is:

 #2 /var/www/ortoweb1314/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query( "INSERT INTO `catalog_product_index_price_final_tmp` SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `cw`.`website_id`, ta_tax_class_id.value AS `tax_class_id`, ta_price.value AS `orig_price`, IF(IF(ta_special_from_date.value IS NULL, 1, IF(DATE(ta_special_from_date.value) <= cwd.date, 1, 0)) > 0 AND IF(ta_special_to_date.value IS NULL, 1, IF(DATE(ta_special_to_date.value) >= cwd.date, 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value) AS `price`, IF(IF(ta_special_from_date.value IS NULL, 1, IF(DATE(ta_special_from_date.value) <= cwd.date, 1, 0)) > 0 AND IF(ta_special_to_date.value IS NULL, 1, IF(DATE(ta_special_to_date.value) >= cwd.date, 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value) AS `min_price`, IF(IF(ta_special_from_date.value IS NULL, 1, IF(DATE(ta_special_from_date.value) <= cwd.date, 1, 0)) > 0 AND IF(ta_special_to_date.value IS NULL, 1, IF(DATE(ta_special_to_date.value) >= cwd.date, 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value) AS `max_price`, tp.min_price AS `tier_price`, tp.min_price AS `base_tier` FROM `catalog_product_entity` AS `e` INNER JOIN `customer_group` AS `cg` INNER JOIN `core_website` AS `cw` INNER JOIN `catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id INNER JOIN `core_store_group` AS `csg` ON csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id INNER JOIN `core_store` AS `cs` ON csg.default_store_id = cs.store_id AND cs.store_id != 0 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id AND pw.website_id = cw.website_id LEFT JOIN `catalog_product_index_tier_price` AS `tp` ON tp.entity_id = e.entity_id AND tp.website_id = cw.website_id AND tp.customer_group_id = cg.customer_group_id INNER JOIN `catalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 80 AND tad_status.store_id = 0 LEFT JOIN `catalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 80 AND tas_status.store_id = cs.store_id LEFT JOIN `catalog_product_entity_int` AS `ta_tax_class_id` ON ta_tax_class_id.entity_id = e.entity_id AND ta_tax_class_id.attribute_id = 81 AND ta_tax_class_id.store_id = 0 LEFT JOIN `catalog_product_entity_decimal` AS `ta_price` ON ta_price.entity_id = e.entity_id AND ta_price.attribute_id = 60 AND ta_price.store_id = 0 LEFT JOIN `catalog_product_entity_decimal` AS `ta_special_price` ON ta_special_price.entity_id = e.entity_id AND ta_special_price.attribute_id = 61 AND ta_special_price.store_id = 0 LEFT JOIN `catalog_product_entity_datetime` AS `ta_special_from_date` ON ta_special_from_date.entity_id = e.entity_id AND ta_special_from_date.attribute_id = 62 AND ta_special_from_date.store_id = 0 LEFT JOIN `catalog_product_entity_datetime` AS `ta_special_to_date` ON ta_special_to_date.entity_id = e.entity_id AND ta_special_to_date.attribute_id = 63 AND ta_special_to_date.store_id = 0 INNER JOIN `cataloginventory_stock_status` AS `ciss` ON ciss.product_id = e.entity_id AND ciss.website_id = cw.website_id WHERE (e.type_id='simple') AND (IF(tas_status.value_id > 0, tas_status.value, tad_status.value)=1) AND (e.entity_id IN('6028')) AND (ciss.stock_status=1)", array( ), )

the entire excpetion.log file is here: http://www.dowhiletrue.net/exception.txt.tar.gz

+4
source share
2 answers

It seems the problem is not in the catalog_product_website table. The problem is in the price index table, perhaps there are old entries from the previous version that should be deleted. I would recommend that you reindex all indexes. The complete reindex process first clears the index table and then executes the data table. This should help.

If full reindex does not help, it is possible that your package has modules that watch the prepare_catalog_product_price_index_table event, which changes the selection of the re-index as an additional join that multiplies the number of rows with the same primary key. It may also cause such an error.

Cheers, Ivan

+1
source

How silly how that sounds, since you get duplicate - auto_incremented - keys, have you tried to increase the auto_increment value for this table? Assuming this is a conflicting database state in some way, this will fix the problem. If you skip PK to 100, and you still get PK violations, then the code will probably try to insert the same record twice.

This will be the corresponding request:

 ALTER TABLE catalog_product_website AUTO_INCREMENT=1234
0
source

More articles:


All Articles