Magento 1.4.0到1.6.2.0升级的唯一约束违规 [英] Unique constraint violation on Magento 1.4.0 to 1.6.2.0 upgrade

问题描述

我正在现有的Magento网站上进行升级.大约10分钟后，Magento报告了一个异常，当我在/var/report中检查错误报告文件时，我看到以下错误消息和堆栈转储:

I'm running an upgrade on an existing Magento site. After about 10 minutes, Magento reports an exception, and when I check the error report file in /var/report I see the following error message and stack dump:

a:5:{i:0;s:223:"Error in file: "/var/www/vhosts/mymagesite/app/code/core/Mage/Customer/sql/customer_setup/mysql4-upgrade-1.5.9.9-1.6.0.0.php" - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-8' for key 'UNQ_BY_CUSTOMER'";i:1;s:952:"#0 /var/www/vhosts/mymagesite/app/code/core/Mage/Core/Model/Resource/Setup.php(645): Mage::exception('Mage_Core', 'Error in file: ...')
#1 /var/www/vhosts/mymagesite/app/code/core/Mage/Core/Model/Resource/Setup.php(437): Mage_Core_Model_Resource_Setup->_modifyResourceDb('upgrade', '1.4.0.0.7', '1.6.1.0')
#2 /var/www/vhosts/mymagesite/app/code/core/Mage/Core/Model/Resource/Setup.php(320): Mage_Core_Model_Resource_Setup->_upgradeResourceDb('1.4.0.0.7', '1.6.1.0')
#3 /var/www/vhosts/mymagesite/app/code/core/Mage/Core/Model/Resource/Setup.php(235): Mage_Core_Model_Resource_Setup->applyUpdates()
#4 /var/www/vhosts/mymagesite/app/code/core/Mage/Core/Model/App.php(412): Mage_Core_Model_Resource_Setup::applyAllUpdates()
#5 /var/www/vhosts/mymagesite/app/code/core/Mage/Core/Model/App.php(338): Mage_Core_Model_App->_initModules()
#6 /var/www/vhosts/mymagesite/app/Mage.php(640): Mage_Core_Model_App->run(Array)
#7 /var/www/vhosts/mymagesite/index.php(80): Mage::run('default', 'store')
#8 {main}";s:3:"url";s:16:"/index.php/admin";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:7:"default";}

Internet上其他地方的一般建议是将app/etc/config.xml中的<initStatements>更改为:

The general advice elsewhere on the Internet is to change <initStatements> in app/etc/config.xml to read:

<initStatements>SET NAMES utf8; SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;</initStatements>

但是，禁用数据库完整性约束系统是以后很难支持和解决问题的有保证的途径.这是一种使升级脚本不会因错误而崩溃的hack，实际上并不能以任何形式解决问题.

However, disabling your databases integrity constraint system is a guaranteed path to incredibly difficult to support and troubleshoot issues later. It is a hack that makes the upgrade script not crash with an error, it does NOT actually fix the problem in any way shape or form.

StackOverflow社区可以提供更好的解决方案，也可以提供很好的解释来说明为什么在MySQL中禁用完整性检查是个好主意吗?

Can the StackOverflow community assist with either a better solution, or a good explanation as to why disabling integrity checking in MySQL is a good idea?

推荐答案

可以将该表截断. http://docs.nexcess.net/magento-database-maintenance

它是收集场所使用信息的少数几张桌子之一，这些信息对magento的操作并不重要. (如果您使用这些报告，则确实会影响客户报告.)

Its one of a handfull of tables gathering site usage information which are not critical to the operation of magento. (It does effect the customer reports, if your using those.)

问题出在以下位置的迁移脚本:

The problem is with the migration script from:

/app/code/core/Mage/Customer/sql/customer_setup/mysql4-upgrade-1.5.9.9-1.6.0.0.php

它会将以前默认为NULL的列更改为默认不为空.

Its altering a column which used to default to NULL to default to not null.

ALTER TABLE `report_compared_product_index` MODIFY COLUMN `customer_id` int UNSIGNED NOT NULL COMMENT ''

该错误来自该列的唯一索引.之前它为null，因此MySQL忽略了唯一索引.一旦将其设置为默认值不为null，则NULL不再是有效值，它会尝试将列的值设置为0.它到达第二行，现在它破坏了唯一索引，并且您会得到一个错误， http://bugs.mysql.com/bug.php?id=8173

The error is from a unique index on that column. It was null before so MySQL was ignoring the unique index. Once it was set to default to not null, NULL is no longer a valid value and it attempts to set the value of the column to 0. It gets to the second row and now its breaking the unique index and you get an error, http://bugs.mysql.com/bug.php?id=8173

1.4x代码在此表中保存了与新架构不兼容的数据. 由于缺少的信息需要满足唯一索引，因此清理起来也非常困难. 最快的选择是截断表.

The 1.4x code saved data in this table which isn't compatible with the new schema. Its also going to be pretty hard to clean up because your missing information needed to satisfy the unique index. The quickest option is to just truncate the table.

这篇关于Magento 1.4.0到1.6.2.0升级的唯一约束违规的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！