Last few weeks I was searching for solution that allows me to reduce time of Magento database upgrade. In my case I had to upgrade very old Magento version to Magento CE 220.127.116.11 During my tests I found that database upgrade took around 6 hours. The reason was that I had very large database and of course there were a lot of database schema changes (e.g. removing a column of large table was taking ages). This meant that we could have at least 6 hours of down time during upgrade. This was a bit risky because we had limited time span and wanted to go live as soon as possible.
The the good news is that because of my research I managed to reduce database upgrade time from 6 hours to 2 hours! If you are impatient to try the solution just checkout Magento-Upgrade-Replay. I recommend also to read Troubleshooting section of this post.
How my research went?
I had some ideas but they seemed a bit crazy and risky. I decided to chwck for second opinion and asked Magento community: An idea for faster DB upgrade when upgrade Magento. Phillip Jackson gave me a great idea about upgrade_replay.php shells script.
How upgrade_replay.php works?
- We have to make test upgrade. During this upgrade Magento function for logging all sql must be enabled.
- The outcome of of step (1) is a log file (var/debug/pdo_mysql.log) with all sql queries that were performed during test upgrade.
- When you are ready for real upgrade copy at your live environment following files: upgrade_replay.php and pdo_mysql.log in Magento shell folder.
- Change the number of used parallel threads in upgrade_replay.php to number that suits you. I think that 4 threads should be enough e.g. $track->run(4, true); (The second argument should be true because this means that the you are going to perform real upgrade).
- Run command “php upgrade_replay.php” and wait. The shell script will show you a progress bar so you would have a clue if the upgrade is finished.
How upgrade_replay.php works?
- The scrip analyses pdo_mysql.log and finds duplicate queries or queries that are executed on same table columns. For example if we change 5 times type of a column then upgrade_replay.php will execute only the last type change because this is the the change that really matters.
- The scrip also runs queries in parallel. If we have upgrade queries on catalog_product_entity_int and sales_flat_order_item then upgrade_replay.php would run those queries in different threads.
I noticed 3 serious issues that I managed to fix in order to make upgrade possible with upgrade_replay.php.
When I run upgrade_replay.php for first time the script existed with message “UNKNOWN ALTER” and of course no sql queries were executed. I got this error because some third party modules didn’t use “COLUMN” in alter table statements. For example “ALTER TABLE aw_blog DROP `cat_id`” had to be changed to “ALTER TABLE aw_blog DROP COLUMN `cat_id” otherwise upgrade_replay.php couldn’t recognise this statement. I han manually to modify pdo_mysql.log in order to make upgrade_replay.php works.
I did couple of test upgrades using standard Magento way and upgrade_replay.php. I noticed that with upgrade_replay.php some indexes were missing so I had to add them manually. So far the reason for this is not clear.
In upgrade-18.104.22.168.1-22.214.171.124.2.php of Customer module Magento Core team modifies all duplicate customer email addresses (I also asked interesting question about this operation: Why Magento Core team doesn’t notify us when modifies duplicate customer email addresses in upgrade script?). All those modifications are stored in pdo_mysql.log and there is a possibility that if you created pdo_mysql.log in past moment e.g. a week before you execute upgrade_replay.php on live database then you may have recently created duplicate email addresses and this may break upgrade. In order to spot all duplicated email addresses you could use this query:
I think that upgrade_replay.php is a good solution but as you see it may not work at first try so you may need extra effort. For sure this method requires extra research and double check if upgrade was successful but really reduces upgrade time.