在Magento中编辑产品时出现MySQL错误 [英] MySQL error when editing product in Magento
问题描述
它是av 1.4.1.1 Magento，从av 1.3.2.4升级
当我尝试编辑产品时，会出现一条错误消息：
it's a v 1.4.1.1 Magento, upgraded from a v 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''
如果我尝试重新索引价格，我在exception.log中有相同的消息
我认为问题在于catalog_product_website表，因为如果我删除该行，我可以运行价格索引，但是当我尝试编辑产品为其分配网站时，我得到相同的消息。
奇怪的是，catalog_product_website table只有2列，并且有关3主键表的异常（右？）
这是整个堆栈跟踪：
And if I try to reindex 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 run the price indexing, but as I try to edit the product to assign it a website, I get the same message.
A weird thing is that catalog_product_website table has only 2 columns, and the exception talks about a 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}
我希望有人可以帮忙e，我现在真的没想到:(
I hope someone can help me, I really out of idea right now :(
编辑：我已经增强了异常日志的消息，所以我可以看到整个查询，这是：
edit: I've enhanced the exception log's message so I can 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(
),
)
整个excpetion.log文件在这里： http://www.dowhiletrue.net/exception.txt.tar.gz
the whole excpetion.log file is here: http://www.dowhiletrue.net/exception.txt.tar.gz
推荐答案
似乎问题不在 catalog_product_website 表中。价格索引表中的问题，可能有一些旧记录从以前的版本，应删除。我建议你重新索引所有的索引。完整的reindex进程首先清除索引表，然后它用数据完成表。它应该有帮助。
It seems the problem is not in catalog_product_website table. The problem in price index table, maybe there some old records from previous version, that should be deleted. I would recommend you to reindex all the indexes. The full reindex process clears index table first and then it fulfill the table with data. It should help.
如果完全重新索引没有帮助，那么你的分期付款可能包含观察 prepare_catalog_product_price_index_table 事件的模块，它会以额外的方式修改重新索引选择连接乘以行数w ith相同的主键。它也可能导致这样的错误。
If full reindex is not helping, then it is possible that your installment has modules that observe prepare_catalog_product_price_index_table event, which modifies re-index select in a way of additional join that multiplies number of rows with the same primary key. It also may cause such an error.
干杯，
伊万
这篇关于在Magento中编辑产品时出现MySQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
