I have never been able to upgrade Magento, ever, without having to begin with a fresh database and lose all my data. This is surely the most frustrating piece of software ever written, and the upgrade process is riddled with bugs at every corner. Twice now I’ve had to just give up all my data and start fresh. For that reason, I haven’t upgraded in over a year, maybe 2. But I needed to move on upgrade to a more recent version.
I took heart at reading stories of people having spent 3 days, 5 days, a week trying to upgrade their Magento installation, and refused to give in. After a very solid 10 hour battle, I belive I have won the fight (but time will tell if I have won the war).
I did not want to lose my customer data or my order data, and I didn’t want to turn of key constraints and see what problems that would yeild me down the track.
Here is the solution I found through tens of iterations of completely re-starting the upgrade process from fresh files and my database backup, that worked for me. I could never get the command line tool ./mage to do anything useful, and the Magento Connect Manager showed no extensions installed at all after upgrading from 126.96.36.199 to 188.8.131.52 (there are so many threads with people reporting that as a problem with no solutions!).
Here is what I did to upgrade from 184.108.40.206 to 220.127.116.11 (one direct upgrade):
As my solution
1) Back up your database using mysqldump. Do not use mysqlhotcopy, it doesn’t work for InnoDB.
2) Extract 18.104.22.168 to a directory “magento_1620”, and copy app/etc/local.xml file from your old installation into app/etc/ in that directory
Now you have a database ready to be restored, and the 22.214.171.124 files ready to be restored, which you can do with one command.
3) Make sure your PHP max_execution_time is 18000 and max_memory is set to 512MB and verify that with phpinfo();
4) Edit lib/Varien/Db/Adapter/Pdo/Mysql.php and change $_debug from false to true
5) Start the upgrade
6) Open a shell window in var/debug and then “tail -f *” and you will see each query pop up as it runs. Brilliant.
Specifically, I didn’t want to turn off key constraints, and I didn’t want to “keep pressing refresh” which (after 10 tries) would stop erroring, and “hope for the best”.
The first error I ran into was:
SQL: ALTER TABLE `report_viewed_product_index` MODIFY COLUMN `customer_id` int UNSIGNED NOT NULL COMMENT ''
ERROR: #1062 - Duplicate entry '0-6' for key 2
I found that by running this on the database, it solved all the errors I was getting. I was OK with losing these logs.
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
ALTER TABLE `log_summary_type` AUTO_INCREMENT=1;
ALTER TABLE `log_url` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;
ALTER TABLE `report_compared_product_index` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `catalog_compare_item` AUTO_INCREMENT=1;
When it came up, I found that one of my indexes would not turn to green from red, no matter how many times I tried, but the debug window came to the rescue, showing:
"exception 'PDOException' with message 'SQLSTATE: Integrity constraint violation: 1062 Duplicate entry 'category/12-1-2' for key 3' in /www/mysite/store/lib/Zend/Db/Statement/Pdo.php:228".
Some googling showed that this was perhaps related to having multiple stores, and I just happened to be lucky enough to be able to delete one of my stores (I was not going to use it in the future) which make the problem go away. I’m not sure if re-opening more stores in the future will bring this demon back from its grave.
Many thanks to the Magento community (and no thanks to Varien who are entirely absent from most threads) for their earnest attempts to resolve the myriad problems they’ve experienced. Your various posts were absolutely invaluable and I hope that this post saves someone some time in the future.