You are here

Home

MySQL - MariaDB migration

Table of Contents


More and more MySQL users want to switch from MySQL/Percona Server to MariaDB over time or, more rarely, the other way around. This is mostly caused by the change of the default in the Linux Distributions (RHEL/CentOS 7, SLES 12, Debian 8) to MariaDB.
Up to MySQL/MariaDB 5.5 everything was quite easy, both Branches claim to be a drop-in replacement of each other. But after the separation into different forks (MariaDB 10.0 ff. vs. MySQL 5.6 ff.) we expect more and more problems migrating from one branch to the other what we call sidegrade.
Percona Server code is genetically closer to MySQL than MariaDB. So we expect to see less problems on this sidegrade.

See also our article about Migration between MySQL/Percona Server and MariaDB

General findings


Migration from MySQL 5.x to MariaDB 5.5

  • Significant slow-down experienced upgrading from MySQL 5.0 to MariaDB 5.5 on CentOS 7 due to wrong MariaDB optimizer decision for sub-query optimization.

Migration from MySQL 5.0 to MariaDB 10.3

  • We tried to upgrade from MySQL 5.0 to MariaDB 10.3 via direct Replication. This did not work because of an error in the IO thread of the replication: 
    Last_IO_Errno: 1595, Relay log write failure: could not queue event from master

    We will try with an MariaDB 5.5 intermediate Slave...

Migration from MariaDB 10.0 to Percona Server 5.6

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

Migration from MySQL/Percona 5.6 to MariaDB 10.0

  • 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!!!)

Migration from MySQL 5.7 to MariaDB 10.2

MySQL 5.7 handles JSON data types like LONGBLOBs. MariaDB 10.2 stores JSON data types in LONGTEXT fields. This means that with JSON data types we have an incompatibility issue. These data types need some kind of conversion. Since MariaDB 10.5 there is the MYSQL_JSON plugin to cover this issue.

Migration from MySQL 5.7 to MariaDB 10.3

Some table creation statements seems to be not compatible any more. Nasty but easy to fix:

$ zcat zabbix.sql.gz | mysql -u root zabbix	
ERROR 1005 (HY000) at line 1028: Can't create table `zabbix`.`history_str` (errno: 140 "Wrong create options")

CREATE TABLE `history_str` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) unsigned NOT NULL DEFAULT '0',
  `value` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `ns` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Migration from MySQL 5.7 to MariaDB 10.4

More detail can be found in the following article: Migration from MySQL 5.7 to MariaDB 10.4

Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4

The sst user additionally needs the PROCESS privilege.

More detail can be found in the Migration Workshop - PXC 5.5 to MariaDB 10.4 Slides.

Migration from MySQL 5.7 to MariaDB 10.5

MySQL 5.7 handles JSON data types like LONGBLOBs. MariaDB 10.5 stores JSON data types in LONGTEXT fields. This means that with JSON data types we have an incompatibility issue. These data types need some kind of conversion. Since MariaDB 10.5 there is the MYSQL_JSON plugin to cover this issue.
See also: JSON Data Type and Making MariaDB understand MySQL JSON.

Migration from MySQL 5.7 to MariaDB 10.6

Partitioned tables containing JSON columns seems NOT to be supported by the MySQL JSON plug-in. We opened a bug report at MariaDB for this: MDEV-32277: Migration from MySQL 5.7 to MariaDB 10.6 with partitioned tables fails. A workaround could be to alter the JSON column to TEXT already on MySQL and after the upgrade convert it back to JSON on MariaDB. Has to be verified yet...

Migration from MySQL 5.7 to MariaDB 10.6 on Windows

  • Attempt 1: Dumping with mysqldump and restoring with mariadb CLI failed. Attempt 2: Dumping with mariadb-dump and restoring with mariadb CLI failed. Following errors on Windows: 
    ERROR 1064 (42000) at line 1 in file: 'C:\backup_mysql.sql': You have an error in
your SQL syntax; check the manual that corresponds to your MariaDB server version
for the right syntax to use near '' at line 1

ERROR at line 1 in file: 'C:\backup_mysql.sql': No query specified

    We did NOT investigate any further and did NOT find the reason.
  • Verfication tests on Linux did NOT show the same symptoms but the following error: 
    SQL> mariadb --user=root < /tmp/database_dump.sql
ERROR 1118 (42000) at line 1240: Row size too large (> 8126). Changing some columns
to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored
inline.

    Fixing the table columns solved the problem...

  • Migration to MariaDB 10.6 on Windows miserably failed because of frequent nested Stored Procedure calls in combination with many JSON_EXTRACT() function calls and JSON documents causing very high CPU load. Rolling back...

tags: 
migration
upgrade
sidegrade
downgrade
mariadb
mysql
pxc
percona xtradb cluster
galera cluster
galera