The complete Magento upgrade guide

Posted: November 9th, 2012 | Author: | Filed under: Magento, MySQL, PHP | Comments Off on The complete Magento upgrade guide

After having upgraded several Magento sites and having read alot of online upgrade guides, I decided to write my own manual. Many people search for how-to’s for specific Magento versions, but it actually doesn’t really matter what version of Magento you are upgrading. The only difference is that you need to upgrade to 1.4.2 if you are running a version below that. Magento stopped using PEAR as of version 1.4.2.


First of all, backup your production site and create a staging environment. After setting the new database connection in app/etc/local.xml, manually change the website urls in the database:

   `value` = REPLACE(`value`, "//", "//");

Now login to the new staging Admin panel and disable all cache under Cache Management and delete the var/cache and var/session folders in your root directory. It is important to clean up and reduce the size of the database before we start the upgrade process, since large databases take forever to upgrade and corrupt databases can cause headaches when debugging.

If you have the Magento Compiler enabled, run the following commands to remove all compiled files and to disable the include path.

$ php shell/compiler.php disable
$ php shell/compiler.php clear

Make sure that the staging website maintains all proper file and folder permissions. If this is not the case you can reset them by following this guide.

Cleanup the database

Since Magento logs alot, let’s clean up a bit first. Enter the following command to view how much data Magento has been logging until now.

$ shell/log.php status

This displays the size of the `log_` and `report_` tables. If you haven’t enabled Log Cleaning in the Magento Admin (System > Configuration > System > Log Cleaning), this can quickly grow to a couple of hundred MB or maybe some GB’s!
You can manually cleanup these tables by running this command:

$ shell/log.php clean

However there are more tables that can be emptied. You can safely run this query:

TRUNCATE `catalog_compare_item`;
TRUNCATE `dataflow_batch_export`;
TRUNCATE `dataflow_batch_import`;
TRUNCATE `log_customer`;
TRUNCATE `log_quote`;
TRUNCATE `log_summary`;
TRUNCATE `log_summary_type`;
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `log_visitor_online`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `report_compared_product_index`;
TRUNCATE `report_event`;
TRUNCATE `sales_flat_quote_item`;

You could also truncate the `core_url_rewrite` table, which can grow pretty large too if you have alot of products and websites/stores-views. Attention: When emptying this you will lose all old url redirects for products, categories, cms pages, etc, but it greatly increases the speed of the upgrade process. Of course it is safe to do this when the website is not yet in production in the first place.

This should leave us with a pretty slimmed down version of the database.

Now reindex the database to be sure

$ php shell/indexer.php reindexall

Clear cache again if Magento somehow managed to create some (you can’t do this enough! :))

$ rm -rf var/cache var/session

Important reminder: do not access the staging website in your browser! As this will initiate the upgrade process too early

Fix corrupt database (don’t skip this step!!)

Check for corrupt structure

It’s possible that the database structure has been changed after the moment is was installed, due to plugins or custom code. Magento has released a tool that can fix corrupted databases: The Magento Database repair tool. It adds missing table columns, foreign keys and indexes. Download the php script and upload it to the root of your staging website. Now create a fresh install of Magento of the same version as your current shop, so we can compare it’s database-structure with our staging database.

Run the tool and see if it makes some changes. If an error occurs, just run the tool again until it says there is nothing more to change.

Check for corrupt data

Besides the structure of tables, it’s also possible that some data is corrupted. I’ve had cases that duplicate order records existed that messed up the upgrade process. Run this query to find out whether there are duplicate records and delete them.

SELECT `increment_id` FROM `sales_flat_order` GROUP BY `increment_id` HAVING COUNT(*)>1;
SELECT `increment_id` FROM `sales_flat_order_grid` GROUP BY `increment_id` HAVING COUNT(*)>1;

More info on this can be found in this thread.

Personally I’ve never had other tables that contained duplicate records.

Upgrade the Magento core files

As mentioned earlier, if you are running Magento 1.4.1.x or lower you need to upgrade to 1.4.2 first.

$ chmod -R 777 lib/PEAR
$ ./pear mage-setup
$ ./pear upgrade -f magento-core/Mage_All_latest-stable
$ chmod 550 ./mage
$ ./mage mage-setup
$ ./mage sync

If pear displays errors, if may have to be upgraded.

$ ./pear channel-update
$ ./pear upgrade --force PEAR

Repeat this step if it shows any errors

The following steps apply to all versions of Magento (1.4.2 and higher) that want to upgrade to the latest stable version (currently Reminder: unfortunately it’s not possible to upgrade to a version other than the latest release. If you really need this, you need to manually download the specific version of Magento and connect the older database to initiate the upgrade process.

View which packages can be upgraded

$ ./mage list-upgrades

Configure Magento to upgrade to the latest stable version, as we do not want a bèta version. (Bèta state is default!)

$ ./mage config-set preferred_state stable

Upgrade all packages! This will also upgrade any plugin installed via SSH if possible. Magento will replace all php files in the app/code/core and in the app/design/base/default map
Custom theme files and php files from app/code/local remain untouched!

$ ./mage upgrade-all --force

The filesystem has now been fully upgraded.

Upgrade the database

Since all package files on the server have been upgraded. Magento will check for version changes when a page is loaded in the browser. All packages will execute the upgrade php scripts which change the database structure. Now open the staging website in your browser and Magento will start the upgrade. You can monitor this progress via SSH:


Depending on your database size, this process can be ready in a few minutes or can take up a few hours. I usually like to refresh the processlist every 10 minutes to see what Magento is up to. If anything goes wrong, you’ll at least know where to look :). It is important not to open the site in another browser during the process, so be sure that no-one else does too.

When the process is finished (no more activity in the mysql processlist), close the browser and clear cache.

$ rm -rf var/cache var/session

Finishing up

To be sure the database upgrade was succesful, run the Magento Database Repair tool again. Now you’ll need a fresh database of the latest Magento version to compare against, so create this first.
If the upgrade went ok, the tool shouldn’t find any differences.

The last step is to reindex everything again!

$ php shell/indexer.php reindexall

The upgrade is now complete! You can safely open the website in the browser and see if everything still works. Remember: All errors are almost always related to corrupt database structure, custom theme files, incompatible plugins or custom code (app/code/community and app/code/local).

Character encoding in MySQL

Posted: February 27th, 2010 | Author: | Filed under: MySQL | Comments Off on Character encoding in MySQL

Just a quick note on character encoding in MySQL, use the following to force MySQL into using UTF-8 for ever!


In your PHP, always use the following immediately after you set up your connection.

  • mysql_set_charset(‘utf8’, $oDB);
  • mysql_query(‘SET NAMES \’utf8\”);

The tricky bit lies in your MySQL configuration file:


Add the following lines:

default-collation = utf8_general_ci

This tells MySQL that the client (which is either PHP or the system) expects UTF-8. If you don’t tell MySQL this, a mysqldump will produce ANSI and an import from the command line will result in double encodings.

And don’t forget to use utf8_general_ci for all databases, tables and fields!

MySQL Substr count

Posted: July 17th, 2009 | Author: | Filed under: MySQL | 1 Comment »

Recently I needed to create an SQL statement which was to count the occurences of a character inside a particular database field. For example, you could store the tags connected to a single db-record in a semi-colon (;) separated column in the record. To get the number of tags for this record straight from the database, we would thus like the number of semi-colons in the field (+1) as an indicator for this count. Since MySQL5 does not have a standard function for this (similar to PHP’s substr_count()), I had to find something of my own. Read the rest of this entry »

MySQL and Integers

Posted: June 26th, 2009 | Author: | Filed under: MySQL | Tags: | Comments Off on MySQL and Integers

Also tired of always looking up the minimum and/or maximum value of a MySQL integer field? For once and for all a cheatsheet:

Field type Bytes Attribute Min. value Max. value
TINYINT 1 -128 (-2^7) 127 (2^7 – 1)
TINYINT 1 UNSIGNED 0 255 (2^8 – 1)
SMALLINT 2 -32768 (-2^15) 32767 (2^15 – 1)
SMALLINT 2 UNSIGNED 0 65535 (2^16 – 1)
MEDIUMINT 3 -8388608 (-2^23) 8388607 (2^23 – 1)
MEDIUMINT 3 UNSIGNED 0 16777215 (2^24 – 1)
INT 4 -2147483648 (-2^31) 2147483647 (2^31 – 1)
INT 4 UNSIGNED 0 4294967295 (2^32 – 1)
BIGINT 8 -9223372036854775808 (-2^63) 9223372036854775807 (2^63 – 1)
BIGINT 8 UNSIGNED 0 18446744073709551615 (2^64 – 1)