MySQL Upgrade Problems

Several of our customers are currently (2011-09-08) doing the upgrade from MySQL 5.0 to a higher release. Our recommendation actually is to go to MySQL 5.5.

The main reasons are a better performance with MySQL 5.5, more on-line changeable parameters and features like Fast Index create/drop.

How to upgrade

How to upgrade from different releases you can find here:

It is advisable to go through the Change Lists as well:

The recommended way to do an upgrade is to dump (mysqldump) and reimport (mysql) the data. This is often not possible especially when you have a huge amount of data.
Binary upgrade is done as well and with 5.0 and higher we have not seen any issues with binary upgrades anymore (but there might be some!).

When you have Master/Slave set-ups keep in mind, that Slave should always have a newer version than Masters. So upgrade Slaves first. We have not heard any problems replicating from MySQL 5.0 to 5.1 or even 5.5. But test this carefully on your own.

Problems we hit up to now in real life

Because Change Lists are huge we collected here the problems we or our customers were running into:

5.0 to 5.1

Optimizer did some wrong Query Execution Plans. It was in one case so bad, that we have to rollback the upgrade.
There are some new reserved Keywords in MySQL 5.1. Especially the following: RANGE, ...
Test binary upgrade carefully (Canias 6.0.2, MySQL 5.0.28). mysql_upgrade complains for the majority of tables.
MySQL introduced the Index Merge Optimization with 5.1. This leads sometimes to non-optimal Query Execution Plans. With the optimizer_switch variable you can revert to the old behavior, either globally or per session if you do not want to use hints.

5.1 to 5.5

Optimizer did some wrong Query Execution Plans.
Little performance slow down. When Buffer Pool Instances was set higher (# of CPU) the slow down went away.
There are some new reserved Keywords in MySQL 5.5. Especially the following: GENERAL, MAXVALUE, RESIGNAL, SIGNAL, SLOW, ...
mk-table-checksum reports differences but there are none.
Keep in mind, that from MySQL 5.5 on InnoDB is the default Storage Engine.

5.5 to 5.6

Migration from MyISAM to InnoDB

Since MySQL 5.5 InnoDB is the default Storage Engine. This has some impacts:

InnoDB does NOT have GIS Indexes and FULLTEXT Search (introduced in MySQL 5.6).
In InnoDB rows are sorted by the Primary Key. In MyISAM they are not.
InnoDB has a bigger footprint than MyISAM.
In InnoDB AUTO_INCREMENT VALUES must be located at the first position of any index (InnoDB AUTO_INCREMENT at 2^nd position)
SELECT COUNT(*) FROM table; in InnoDB is much slower than with MyISAM.

Sidegrade

Some customers want to upgrade to newer Releases of Percona Server or MariaDB. Up to now we have not seen any specific issues. Both Branches of MySQL are drop-in replacements for MySQL.

If you are aware of any problematic situations upgrading MySQL we would like to hear about...

Languages

FromDual