You are here

MySQL Upgrade Problems

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 from MySQL to Galera
  6. Sidegrade (MySQL derivates)

Reasons to upgrade

Reasons to upgrade your MySQL database are (by priority):

  • Problems/errors
  • New features
  • Performance improvements (response time and scalability)
  • Application requirements
  • End of Life/Support
  • Better manageability
  • Bug fixes
  • Security issues
  • Specific know-How about old releases get lost over time

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 any more (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 in real life

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'

4.1 to 5.6

According to a customer replication from 4.1 to 5.6 does not work at all!

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 behaviour, 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.
  • Datatype timestamp is not allowed as partition key any more in combination with several date functions. See MySQL bug #42849. This is a problem since MySQL 5.1.43 but it will possibly manifest during upgrade.
  • A new ugly bug (#68148) was introduced possibly with the Fast Index Create feature affecting InnoDB tables with Foreign Key Constraints. This bug is fixed in 5.6.12 and 5.7.2
  • DDL commands behave differently due to the Fast-Index-Create feature in MySQL and MariaDB 5.5. This seems to be fixed in MySQL 5.6 and MariaDB 10.0:
    ALTER TABLE active DROP INDEX server, ADD INDEX ( server, callid, stale );
    ERROR 1280 (42000): Incorrect index name 'server'

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!
  • The Query Cache is disabled by default in 5.6. Change to the previous behaviour by setting query_cache_type=1 in my.cnf
  • 8 new reserved key words have been defined. The most important ones are get and partition.
  • A new ugly bug (#68148) was introduced possibly with the Fast Index Create feature affecting InnoDB tables with Foreign Key Constraints. This bug is fixed in 5.6.12 and 5.7.2

5.6 to 5.7

  • None so far.

Migration from MyISAM to InnoDB

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

  • InnoDB does NOT have FULLTEXT search (introduced in MySQL 5.6) and GIS indexes (introduced in MySQL 5.7).
  • In InnoDB rows are sorted by the Primary Key. In MyISAM they are not.
  • InnoDB has a much bigger footprint than MyISAM (50 - 100%!).
  • In InnoDB AUTO_INCREMENT values must be located at the first position of any index (InnoDB AUTO_INCREMENT at 2nd 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.
  • MRG_MyISAM tables/storage engine does not work for InnoDB tables. Use a VIEW or table Partitions instead.
  • A new ugly bug (#68148) was introduced possibly with the Fast Index Create feature affecting InnoDB tables with Foreign Key Constraints. This bug is fixed in 5.6.12 and 5.7.2
  • Copy of a single table on file system is not that easy any more. You have to use the Transportable Tablespace (TTS) mechanism instead (since 5.6).

Switching from statement based replication (SBR) to row based Replication (RBR)

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.
  • pt-table-checksum/mk-table-checksum will complain about RBR.

Sidegrade from MySQL to Galera

Sidegrade

Some MySQL user want to switch from MySQL to Percona Server or MariaDB or the other way around. Up to now we have not seen any specific issues. Both Branches of MySQL claim to be drop-in replacements for MySQL. Percona Server code is genetically closer related to MySQL than MariaDB. So we expect less effects on this switch.

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