Wow! What a day I had yesterday. Upgrading Magento from 126.96.36.199 to 188.8.131.52 was an absolute nightmare! I managed to get a clients installation up and running in around 5 hours but it wasn't easy. So to help out my fellow Magento developers here is how I upgraded the software and consequently fixed a lot of MySQL bugs at the same time.
InnoDB vs. MyISAM
First of all, the Magento installation I was upgrading had begun it's life on a very early release of Magento that was developed on a server in the U.S. before being ported to a server in the UK. I'm not sure how relevance that had to the database stability but something happened along the way and the client began experiencing some strange bugs such as being kicked out of the checkout and returned to the cart, customers not being able to add new addresses, etc.
I began the upgrade process using a copy of the live Magento installation and proceeded to upgrade the software using a combination of Magento Connect Manager and an FTP client. The software installed fine and I thought to myself 'that was too easy', and it was. On logging into the newly upgraded 1.4 Magento installation, I ran through the basic steps or setting up the cache and then run the index manager to get the product indexes refreshed. That's when the thing started to unravel and I was presented with the rather unhelpful message: "Cant initialize indexer process". So, I began digging around the admin interface for other problems and soon came across a bug in the manage products section. Essentially I couldn't save products due to an SQL error.
After logging into the PHPMyAdmin I noticed that there were lots of MyISAM tables for the old database content and new database tables had been generated using InnoDB - weird! So, I tried converting a couple of tables to InnoDB to see if it fixed the issue. To my surprise the Magento admin interface began responding again and I could save products after adjusting a few table to InnoDB. This made me aware that the database must have been corrupted in some way during either the port between servers and databases or during the Magento upgrade process.
The Magento Upgrade Solution
To fix the database corruption issue I used these simple steps:
- Create a new empty database and upload Magento 184.108.40.206.
- Install Magento 220.127.116.11 using the new database.
- Download the Magento Database Repair Tool.
- Run the repair tool on a copy of the existing 18.104.22.168 database and new Magento 22.214.171.124 database. This will use the new database a reference point to fix the corrupt 126.96.36.199 database. Note: This may take a long time so don't close the browser window and make sure your server doesn't time out for at least 5 minutes.
- After the process has finished (it may take a couple of attempts), log in to the Magento Admin panel and go to catalog > manage products.
- Click the select all button.
- Use the drop down 'Actions' menu and select 'Update Attributes'.
- Set the visibility settings for all products to catalog, search (if that is what you normally use).
- Assign all products to the appropriate websites by clicking the 'websites' tab.
- Click save.
- Next clear refresh the cache and then go to the configuration > index management.
- Finally, click each 'Reindex data' link to make sure that each related database table is functioning as it should.
Upgrading Magento is a real pain sometimes so hopefully this little tutorial will help you get your site launched without to much stress!