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.

ToC

1. How to upgrade to 5.x
2. Problems we hit in real life: 5.x to 5.y
3. Migration from MyISAM to InnoDB
4. Switching from SBR to RBR
5. Sidegrade (MySQL derivates)

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 from different releases you can find here:

• to 5.0
• to 5.1
• to 5.5
• to 5.6

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

• Changes in MySQL 5.0
• Changes in MySQL 5.1
• Changes in MySQL 5.5
• Changes in MySQL 5.6

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.

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

4.1 to 5.1

• MySQL is more strict in data type checking and throws warnings. This caused us some troubles with the application (Canias ERP): Data were written in some cases correctly in some cases not written at all. Some results were retrieved wrongly from the database (rounding problems). We considered to roll back the whole upgrade. Dumping the database with mysqldump --compatible=mysql40 and restoring back into 4.1 gave some errors about too long indexes. So it seems to be a bug in mysqldump/mysql.
  Cases were:

  DECIMAL(6,2) 123.345
  DATE  '2012-10-09 12:12:12'

  
  

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

• Import of a MySQL 5.5 dump followed by the mysql_upgrade command while GTIDs are enabled causes troubles with some MySQL 5.6 releases. Do the upgrade with --gtid-mode=0 and enable it later on.
• Some default values have changed (18!): The most important one is innodb_file_per_table = 1. Some old variables and some deprecated commands have been removed. So test carefully!
• 8 new reserved key words have been defined. The most important ones are get and partition.

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. More details you can find here: Change MyISAM tables to InnoDB and handle SELECT COUNT(*) situation
• InnoDB seems to cope better with write load than MyISAM (Slave lag disappeared).
• InnoDB mandatorily needs a Primary Key. If you do not provide one, InnoDB creates one itself. See also discussion Disadvantages of explicitly NOT using InnoDB Primary Keys and row based replication (RBR)...
• Isolation level READ-COMMITTED is not allowed any more with Statement Based Replication S(BR): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'.
• Traditional MyISAM Locking with LOCK TABLE causes meta data locks (Waiting for table metadata lock) when mysqldump backup is started with --single-transaction.

In version 5.1 MySQL introduced row based replication (RBR). RBR is much more reliable than SBR when it comes to data consistency (non-deterministic queries, etc.). So one should consider to leave SBR and start using RBR!

• One customer had the situation, that binary log grew significantly after switching from SBR to RBR. This was caused because he just updated a timestamp on a row which contained a BLOB. With RBR replication the whole row (including the BLOB) is transfered. This is optimized and configurable in MySQL 5.6.
• InnoDB mandatorily needs a Primary Key. If you do not provide one, InnoDB creates one itself. See also discussion Disadvantages of explicitly NOT using InnoDB Primary Keys and row based replication (RBR)...
• We have seen some strange errors on Windows replicating from 5.5.11 to 5.5.24:

  Last_Errno: 1677
  Last_Error: Column 15 of table 'test.users' cannot be converted
  from type 'decimal(0,?)' to type 'decimal(3,2)'

  
  We have no solution yet. But it is reproducible.

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...