You are here

Home

MariaDB and MySQL Upgrade Problems

Table of Contents


Downgrading between Major Versions of MariaDB and Downgrading MySQL (for 5.7 and older).

See also: MariaDB Galera Cluster Upgrade Path

See also: MySQL - MariaDB migration

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
Upgrading from MariaDB 5.5 to MariaDB 10.0
Upgrading from MariaDB 10.0 to MariaDB 10.1
Upgrading from MariaDB 10.1 to MariaDB 10.2
Upgrading from MariaDB 10.2 to MariaDB 10.3
Upgrading from MariaDB 10.3 to MariaDB 10.4
Upgrading from MariaDB 10.4 to MariaDB 10.5
Upgrading from MariaDB 10.5 to MariaDB 10.6
Upgrading from MariaDB 10.6 to MariaDB 10.7 Upgrade to MySQL to 5.6
Upgrading from MariaDB 10.7 to MariaDB 10.8 Upgrade to MySQL to 5.7
Upgrading from MariaDB 10.6 to MariaDB 10.11 Upgrade to MySQL to 8.0

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

Changes & Improvements in MariaDB 5.5
Changes & Improvements in MariaDB 10.0
Changes & Improvements in MariaDB 10.1
Changes & Improvements in MariaDB 10.2
Changes & Improvements in MariaDB 10.3
Changes & Improvements in MariaDB 10.4
Changes & Improvements in MariaDB 10.5
Changes & Improvements in MariaDB 10.6
Changes & Improvements in MariaDB 10.7
Changes & Improvements in MariaDB 10.8
Changes & Improvements in MariaDB 10.9 What is new in MySQL 5.6
Changes & Improvements in MariaDB 10.10 What is new in MySQL 5.7
Changes & Improvements in MariaDB 10.11 What is new in MySQL 8.0
Changes & Improvements in MariaDB 11.0 What is new in MySQL 8.4
Changes & Improvements in MariaDB 11.1
Changes & Improvements in MariaDB 11.2
Changes & Improvements in MariaDB 11.3
Changes & Improvements in MariaDB 11.4
Changes & Improvements in MariaDB 11.5
Changes & Improvements in MariaDB 11.6
Changes & Improvements in MariaDB 11.7

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 Reserved Keywords

MariaDB Reserved Words

Keywords and Reserved Words

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 MySQL 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 MySQL 5.6

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

Upgrade MySQL 5.0 to MySQL 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 MySQL 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.0 to MariaDB 10.3

  • We did this upgrade with an intermediate MariaDB 5.5 first. With later tests we managed to do it in one step.
  • Recent MariaDB server releases are controlled by SystemD. The SystemD unit file prevents with the sandboxing option ProtectHome that one can write to /home with SELECT ... INTO OUTFILE. Thus after the upgrade batch jobs did not work any more [ Lit 1, Lit 2 ]:
    mariadb> SELECT 1 INTO OUTFILE '/home/oli/test.txt';
ERROR 1 (HY000): Can't create/write to file '/home/oli/test.txt' (Errcode: 13 "Permission denied")

Upgrade MySQL 5.1 to MySQL 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 MySQL 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 MySQL 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 MySQL 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 MySQL 8.0


Upgrade MariaDB 5.5 to MariaDB 10.5

In-place upgrade from MariaDB 5.5 to MariaDB 10.5.11 was successful and system was stable for 69 days. But then after one huge DELETE statement (5 to 6 Gibyte transaction size) the database crashed and was only recoverable with innodb_force_recovery=2. We can not exclude the theory that this problem was already inherited by a physical in-place upgrade from 5.1 to 5.5. Dump/Restore with mariadb-dump/mariadb solved the problem.:

2021-09-11 19:33:17 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=3] type 0 to 6.
2021-09-11 19:33:17 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=5] type 0 to 7.
2021-09-11 19:33:17 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=6] type 0 to 6.
2021-09-11 19:33:17 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=7] type 0 to 6.

2021-09-11 19:34:48 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=0] type 0 to 8 when flushing.
2021-09-11 19:34:48 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=16384] type 17855 to 9 when flushing.

2021-09-11 19:52:53 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=1] type 0 to 5 when flushing.
2021-09-11 19:52:57 0 [Note] InnoDB: Resetting invalid page [page id: space=0, page number=16385] type 17855 to 5 when flushing.

210911 20:20:15 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

Server version: 10.5.12-MariaDB-log

Thread pointer: 0x7f7a9c000a98
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f7a57ffebb8 thread_stack 0x49000
??:0(my_print_stacktrace)[0x55bb6449840e]
??:0(handle_fatal_signal)[0x55bb63e9b547]
sigaction.c:0(__restore_rt)[0x7f7da9698630]
??:0(void std::vector >::_M_emplace_back_aux(unsigned long&&))[0x55bb643aed22]
??:0(void std::vector >::_M_emplace_back_aux(unsigned long&&))[0x55bb643af9e4]
??:0(std::pair<:_rb_tree_iterator int="">, bool> std::_Rb_tree, std::less, std::allocator >::_M_insert_unique(unsigned int const&))[0x55bb642e1a7a]
??:0(std::pair<:_rb_tree_iterator int="">, bool> std::_Rb_tree, std::less, std::allocator >::_M_insert_unique(unsigned int const&))[0x55bb642e3f6f]
??:0(std::pair<:_rb_tree_iterator int="">, bool> std::_Rb_tree, std::less, std::allocator >::_M_insert_unique(unsigned int const&))[0x55bb642b1283]
??:0(std::pair<:_rb_tree_iterator int="">, bool> std::_Rb_tree, std::less, std::allocator >::_M_insert_unique(unsigned int const&))[0x55bb642b437f]
??:0(void std::__introsort_loop(unsigned char**, unsigned char**, long))[0x55bb6429a529]
??:0(tpool::task_group::execute(tpool::task*))[0x55bb64421bc6]
??:0(tpool::thread_pool_generic::worker_main(tpool::worker_data*))[0x55bb64420351]
??:0(std::this_thread::__sleep_for(std::chrono::duration >, std::chrono::duration >))[0x7f7da9232330]
pthread_create.c:0(start_thread)[0x7f7da9690ea5]
??:0(__clone)[0x7f7da8bab9fd]

Replication from MariaDB 10.5.12 back to 5.5.68 (for fallback reasons) did not work an broke with:

Last_Errno: 1677
Last_Error: Column 4 of table 'erp.orders' cannot be converted from type '' to type 'datetime'

Upgrade MariaDB 10.2 to MariaDB 10.3

  • Various query runtime problems due to Optimizer changes and conversion to Aria and back to MyISAM. MyISAM was 2 times slower as before. Can be fixed by dump/restore. See: MDEV-25308

Upgrade MariaDB 10.2 to MariaDB 10.4

  • Various query runtime problems due to Optimizer changes and conversion to Aria and back to MyISAM. MyISAM was 2 times slower as before. Can be fixed by dump/restore. See: MDEV-25308

Upgrade MariaDB 10.2 to MariaDB 10.5

  • Various query runtime problems due to Optimizer changes and conversion to Aria and back to MyISAM. MyISAM was 2 times slower as before. Can be fixed by dump/restore. See: MDEV-25308

Upgrade MariaDB 10.3 to MariaDB 10.5

  • When we used MariaDB repository installations and upgraded from 10.3 to 10.5 the installation script silently removed our my.cnf file (no backup!). After the upgrade connection with clients was not possible any more (because bind_address was set back to 127.0.0.1) and replication was broken. After restoring my.cnf configuration file everything worked fine again. So keep a backup of your my.cnf!

Upgrade MariaDB 10.3 to MariaDB 10.6

  • After Upgrading from MariaDB 10.3 to MariaDB 10.6 (from Ubuntu repository) a hot-spot query became very slow (800 ms instead of 2 ms). The result was massive deadlocks and InnoDB row lock timeouts. The system freaked out...
    This query was called via a (nested) Stored Procedure. The problem was a mix of character sets leading the optimizer to a casting on the indexed columns which led to a full table scan on the table. The table had a character set of utf8mb3, the Stored Procedure was stored as utf8mb4. Converting the table character set to utf8mb4 solved the problem immediately.

Upgrade MariaDB 10.4 to MariaDB 10.5

  • Trying a downgrade from MariaDB 10.5.8 to MariaDB 10.4.13 we were running into the following problem:
    [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.5.8.
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[Note] InnoDB: Starting shutdown...

    Possibly deleting the InnoDB log files can help... We did not try.


Upgrade MariaDB 10.5 to MariaDB 10.6

After upgrade from MariaDB 10.5 to 10.6 we got some errors in the application error log:

[Z3005] query failed: [4047] InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
[insert into history_str (itemid,clock,ns,value) values (54833,1640605503,3,'');]

The table looks as follows:

CREATE TABLE `history_str` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) unsigned NOT NULL DEFAULT 0,
  `value` varchar(255) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `ns` int(11) NOT NULL DEFAULT 0,
  KEY `itemid` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
 PARTITION BY RANGE (`clock`)
(PARTITION `p2021_kw25` VALUES LESS THAN (1624226400) ENGINE = InnoDB,
...
 PARTITION `p2022_kw02` VALUES LESS THAN (1641769200) ENGINE = InnoDB,
 PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

After some research we found, that this is a new "feature" introduced in MariaDB 10.6 to deprecate this type of page compression (MDEV-23497). The variable innodb_read_only_compressed by default is set to "on". Setting it to "off" helps to work around the problem.

SQL> SHOW GLOBAL VARIABLES LIKE '%innodb_read_only_compressed%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| innodb_read_only_compressed       | ON    |
+-----------------------------------+-------+

But the tables must be changed permanently to fix this also in the future. Find all those tables like this:

SQL> SELECT table_schema, table_name, create_options FROM information_schema.tables WHERE create_options LIKE '%row_format=COMPRESSED%';
+--------------+-------------+----------------------------------------------------+
| table_schema | table_name  | create_options                                     |
+--------------+-------------+----------------------------------------------------+
| zabbix       | history_str | row_format=COMPRESSED key_block_size=4 partitioned |
+--------------+-------------+----------------------------------------------------+

Altering the table as suggested led to an other error:

SQL> ALTER TABLE history_str ROW_FORMAT=DYNAMIC page_compressed=1;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSED'

SQL> show warnings;
+---------+------+------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                            |
+---------+------+------------------------------------------------------------------------------------+
| Warning | 1478 | InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.                   |
| Warning |  140 | InnoDB: PAGE_COMPRESSED table can't have key_block_size                            |
| Error   | 1478 | Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSED' |
+---------+------+------------------------------------------------------------------------------------+

This DDL command seems to solve the problem:

SQL> ALTER TABLE history_str ROW_FORMAT=DYNAMIC page_compressed=1 key_block_size=0;

Caution: Disk footprint of this tables increased by a factor of 2 to 3 by altering it...!

Upgrade MariaDB 10.11 to MariaDB 11.4

Before upgrading the old database should be stopped with:

SQL> SET GLOBAL innodb_fast_shutdown = 0;
SQL> SHUTDOWN;

Otherwise some new features cannot be activated:

[Warning] InnoDB: Cannot change innodb_undo_tablespaces=3 because previous shutdown was not with innodb_fast_shutdown=0

During mariadb-upgrade --user=root --force we got some notes such as follows:

tellmatic.blacklist
note     : Auto_increment will be checked on each open until CHECK TABLE tellmatic.blacklist FOR UPGRADE is executed
status   : OK

They also did not go away after calling mariadb-upgrade twice (several times). We did not see anything suspicious in the CREATE TABLE statement. Also, as suggested, a CHECK TABLE ... FOR UPGRADE did not help: 

SQL> CHECK TABLE tellmatic.blacklist FOR UPGRADE;
+---------------------+-------+----------+---------------------------------------------------------------------------------------+
| Table               | Op    | Msg_type | Msg_text                                                                              |
+---------------------+-------+----------+---------------------------------------------------------------------------------------+
| tellmatic.blacklist | check | note     | Auto_increment will be checked on each open until CHECK TABLE FOR UPGRADE is executed |
| tellmatic.blacklist | check | status   | OK                                                                                    |
+---------------------+-------+----------+---------------------------------------------------------------------------------------+

Finally the OPTIMIZE TABLE made the problem go away. I assume the tables were created in an old format which was fixed when rebuilding the table with the OPTIMIZE TABLE command.

To make the whole fixing easier (for the lazy ones) we were running the following command:

shell> mariadb-check --optimize --databases erp dwh mantis tellmatic

I could imagine that this operation causes some production issues when upgrading a huge database! So test the upgrade carefully...

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

Migration from MyISAM to Aria

  • Various query runtime problems due to Optimizer changes and conversion to Aria and back to MyISAM. MyISAM was 2 times slower as before. Can be fixed by dump/restore. See: MDEV-25308. Affects MariaDB 10.3, 10.4 and 10.5.
  • Significant slow down of various data warehouse (DWH)/BI queries migrating from MyISAM to Aria. Factor 3.8 in MariaDB 10.3 and 10.4 and about factor 2.5 in MariaDB 10.5.

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

tags: 
migration
upgrade
innodb
sidegrade
GTID
downgrade
rbr
sbr
tbr
partition