You are here

MariaDB and MySQL Upgrade Problems

Contents


Reasons to Upgrade MariaDB or MySQL

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 MariaDB or MySQL

How to upgrade from different releases you can find here:

Upgrading from MariaDB 5.3 to MariaDB 5.5 Upgrade to MySQL to 5.0
Upgrading from MariaDB 5.5 to MariaDB 10.0 Upgrade to MySQL to 5.1
Upgrading from MariaDB 10.0 to MariaDB 10.1Upgrade to MySQL to 5.5
Upgrading from MariaDB 10.1 to MariaDB 10.2Upgrade to MySQL to 5.6
Upgrading from MariaDB 10.2 to MariaDB 10.3Upgrade to MySQL to 5.7
Upgrading from MariaDB 10.3 to MariaDB 10.4Upgrade to MySQL to 8.0

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

Changes & Improvements in MariaDB 5.5 What is new in MySQL 5.0
Changes & Improvements in MariaDB 10.0What is new in MySQL 5.1
Changes & Improvements in MariaDB 10.1What is new in MySQL 5.5
Changes & Improvements in MariaDB 10.2What is new in MySQL 5.6
Changes & Improvements in MariaDB 10.3What is new in MySQL 5.7
Changes & Improvements in MariaDB 10.4What is new in MySQL 8.0

The recommended way to do an upgrade is to dump (mysqldump) and re-import (mysql) the data. This is often not possible especially when you have a huge amount of data.

Binary in-place 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!). Since 5.5 binary in-place upgrade is even officially supported.

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. Very often Replication from a newer release to an older release is also possible (but neither supported nor recommended). So you have the possibly to fallback to the older Release again or even to have a Master/Master Replication with different Version.

MariaDB and MySQL Upgrade Problems we hit in real life

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

Upgrade MySQL 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'


Upgrade MySQL 4.1 to 5.6

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

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

Upgrade MySQL 5.0 to 5.6

  • This upgrade path is NOT recommended. You will try on your own risk!
  • MySQL 5.6 server will crash when started on MySQL 5.0 database files.
  • Correct upgrade path is: 5.0 -> 5.1 -> 5.5 -> 5.6
  • Workaround: Copy over mysql schema tables (the non InnoDB ones) from other MySQL 5.6 server, then start the 5.6 server on the MySQL 5.0 database files and run mysql_upgrade. This is NOT a recommended procedure!!!

Upgrade MySQL 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.
  • mysqldump and MySQL Enterprise Backup (mysqlbackup) will break when concurrently DDL statements are issued: Do not run the DDL operations ALTER TABLE, TRUN-CATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, or RESTORE TABLE while a backup operation is going on. The resulting backup might be corrupted or fail.
  • RESET SLAVE followed by a database restart behaves different in 5.5 compared to 5.1. Possibly use RESET SLAVE ALL instead.

Upgrade MySQL 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 behavior 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.
  • mysqldump and MySQL Enterprise Backup (mysqlbackup) will break when concurrently DDL statements are issued: Do not run the DDL operations ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, or RESTORE TABLE while a backup operation is going on. The resulting backup might be corrupted or fail.
  • RESET SLAVE followed by a database restart behaves different in 5.6 compared to 5.1. Possibly use RESET SLAVE ALL instead.
  • The mysql.host table was removed. If you rely on those privilege rules you should change this before upgrading to MySQL 5.6

Upgrade MySQL 5.6 to 5.7

  • In MySQL 5.7.6 and 5.7.7 the password column was removed from the `mysql`.`user` table. This might cause some old legacy admin tools to fail (Bug #76655). Possible evil workaround: ALTER TABLE `mysql`.`user` ADD COLUMN `Password` CHAR(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '' AFTER `User`; (has to be tested! SET PASSWORD = 'secret'; seems to NOT work any more afterwards...).
  • Various options, parameters and some MySQL syntax is removed an thus does not work any more and can cause errors in old legacy applications.
  • New range optimizer setting might cause unexpected table scans if not set properly (brought up by Simon Mudd).
  • New MySQL accounts expire by default after 360 days. The default_password_lifetime is 360. (brought up by Simon Mudd).
    SELECT user, host, password_expired, password_last_changed, password_lifetime, account_locked
      FROM `mysql`.`user`;
    SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';
    

Upgrade MySQL 5.7.12 to 5.7.21

  • Upgrade from 5.7.12 (or earlier?) to 5.7.21 failed.
    Upgrade from 5.7.12 to 5.7.16 looked better.
    bash> mysql_upgrade --user=root
    Checking if update is needed.
    Checking server version.
    Running queries to upgrade MySQL server.
    mysql_upgrade: [ERROR] 1072: Key column 'Id' doesn't exist in table
    

    In addition we got some messages in the MySQL error log:
    [ERROR] Info table has a problem with its key field(s). Table 'mysql.slave_master_info' expected field #23 to be 'Channel_name' but found 'Enabled_auto_position' instead.
    [ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.
    [ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.
    

    Fixed some tables manually (DROP/CREATE) from a recent MySQL 5.7 server.
    The table causing the error we found with strace:
    bash> strace mysql_upgrade --user=root
    ...
    sendto(3, "\27\1\0\0\3ALTER TABLE slave_worker_in"..., 283, 0, NULL, 0) = 283
    recvfrom(3, "/\0\0\1\3770\4#42000Key column 'Id' doe"..., 16384, 0, NULL, NULL) = 51
    
    write(2, "mysql_upgrade", 13mysql_upgrade)           = 13
    write(2, ": [", 3: [)                      = 3
    write(2, "ERROR", 5ERROR)                    = 5
    write(2, "] ", 2] )                       = 2
    write(2, "1072", 41072)                     = 4
    write(2, ": ", 2: )                       = 2
    write(2, "Key column 'Id' doesn't exist in"..., 38Key column 'Id' doesn't exist in table) = 38
    write(2, "\n", 1
    )                       = 1
    sendto(3, "\1\0\0\0\1", 5, 0, NULL, 0)  = 5
    shutdown(3, SHUT_RDWR)                  = 0
    close(3)                                = 0
    exit_group(5)                           = ?
    +++ exited with 5 +++
    

Upgrade MySQL 5.7 to 8.0

  • To prepare for migration to MySQL 8.0, any table with nonnative partitioning should be changed to use an engine that provides native partitioning, or be made non-partitioned. For example, to change a table to InnoDB, execute this statement: ALTER TABLE <table_name> ENGINE = INNODB;
  • Upgrade MySQL 5.7 to MySQL 8.0

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

Switching from MariaDB or MySQL to Galera


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