MySQL - MariaDB sidegrade

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.

General findings

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

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.

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.

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

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;

tags: