Migration from MySQL 5.7 to MariaDB 10.4

Sat, 2019-11-30 14:17 — Shinguz

Up to version 5.5 MariaDB and MySQL can be considered as "the same" databases. The official wording at those times was "drop-in-replacement". But now we are a few years later and times and features changed. Also the official wording has slightly changed to just "compatible".
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root
MariaDB upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv OK
...
mysql.user OK
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views from mysql
sys.host_summary
Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist
status : Operation failed
sys.host_summary_by_file_io
Error : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50723, now running 100407. Please use mysql_upgrade to fix this error
error : Corrupt
...
sys.x$host_summary
Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist
Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
error : Corrupt
...
sys.x$waits_global_by_latency OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
staging
staging.sales OK
staging.sugarcrm_contact_export
Warning : 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.
status : OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

If you run the mysql_upgrade utility a 2^nd time all issues are gone...

# mysql_upgrade --user=root --force

Some hints for upgrading

Make a backup first before you start!
Dropping MySQL sys Schema before the upgrade and installing MariaDB sys Schema again afterwards reduces noise a bit and lets you having a working sys Schema again.
The MariaDB sys Schema you can find at GitHub: FromDual / mariadb-sys .
It makes sense to read this document before you begin with the upgrade: MariaDB versus MySQL: Compatibility.