Unique constraint violation on Magento 1.4.0 to 1.6.2.0 upgrade

Question

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";}

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>

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.

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?

Answer 1

This table is ok to truncate. http://docs.nexcess.net/magento-database-maintenance

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

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 ''

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

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.