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. Switching to partitioned tables
  6. Sidegrade from MySQL to Galera
  7. Sidegrade (MySQL - MariaDB - Percona Server)

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'

  • MySQL 5.5 introduces metadata locking which opens up a new possibility of deadlocks. Especially if an application contains transactions which might run somewhat longer, it is essential to test not only the functionality with 5.5 but also do some load tests.

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
  • Implicit GROUP BY sorting in MySQL 5.6 is deprecated.
  • When upgrading a master-master setup from 5.5 to 5.6 without downtime, take care: MySQL 5.6 (by default) writes checksums into the binary log which MySQL 5.5 does not understand, so the 5.5 IO slave reports an error and replication stops. A Google search returns this:
    "It's because of binlog_checksum = crc32 setted default at 5.6.5. If you use 5.6's master and 5.5 (or earlier)'s slave, you need to set binlog_checksum = none on 5.6."
    If you forgot this, upgrade the second node also, then replication will resume.
  • After upgrading to MySQL 5.6 a performance decrease is expected, especially, for the single-threaded applications. So, it is highly recommended to test not only the application code with MySQL 5.6 but also the application performance under a high load before doing the upgrade on production. Otherwise, you might need to downgrade to MySQL 5.5 because of the performance issues.

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).
  • Badly designed tables can lead to the following error when the default InnoDB file format Antelope is used. Redesigning the table or switching to Barracuda file format will solve the problem:
    ERROR 1118 (42000) at line 101: Row size too large (> 8126).
    Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.
    In current row format, BLOB prefix of 768 bytes is stored inline.

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

Switching from non-partitioned to partitioned tables

Since version 5.1 MySQL is aware of table partitioning. Switching from non-partitioned tables to partitioned tables has some impacts:

  • The partition key must always be part of the Primary Key. In most cases this does not cause problems but we have seen the following scenario on a table with the PK on the first column:
    REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
    REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');

    This query will not work as expect any more in a partitioned table when the last column is used as partition key!
  • Query Cache is no supported for partitioned tables (since MySQL 5.1.63, Bug #53775, MySQL Docu). MariaDB 5.5 claims to have fixed this problem (July 26 2014).

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.

  • Some customers experienced performance slow-down of some specific queries. This has to do with differences in the optimizers. Also faster response time has to be expected but you will not complain about it... This can happen in both directions.
  • MySQL to MariaDB migration: handling privilege table differences when using mysqldump
  • Sidegrade from MariaDB 10.0 to MySQL/Percona 5.6: MariaDB binary logs cause error messages in MySQL error logs. Purge of the old (MariaDB) binary logs helps to get rid of the messages. The utility mysql_upgrade does not solve all problems. Needs some manual fixes on the tables: mysql.event, mysql.innodb_table_stats and mysql.innodb_index_stats.
  • Sidegrade from MySQL/Percona 5.6 to MariaDB 10.0: InnoDB: in InnoDB data dictionary has unknown flags 40/50/52. The command OPTIMZE TABLE solves these issues. The utility mysql_upgrade does not solve all problems. Needs some manual fixes on the tables: mysql.innodb_table_stats and mysql.innodb_index_stats.
  • You get rid of these error messages. If it causes any technical harm we cannot say so yet. To be on the safe side use a dump/restore to migrate the data (careful with huge databases!!!)

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