MySQL to MariaDB migration: handling privilege table differences when using mysqldump

Migrating from MySQL to MariaDB is generally a straightforward procedure: you can shut down MySQL, install MariaDB, and start it up with a very good chance of success. When upgrading/crossgrading/migrating from one release of MySQL or MariaDB to another, it is important to run mysql_upgrade. The same is true when migrating from MySQL to MariaDB, in part because MariaDB has some different definitions for certain privilege tables. This means that the structures of the MySQL tables are not valid for MariaDB, a problem easily fixed by mysql_upgrade, unless you are trying to import an SQL dump from MySQL that creates tables using the old-style structures and tries to interact with them before it is possible to run mysql_upgrade. Let’s take a look at why this is a problem and how to solve it. The two problem tables here are mysql.proc andmysql.event. mysql.proc holds stored procedures and stored functions, collectively known as «Stored Routines». It would probably be better if this table were calledroutines instead of proc, but here we are. The mysql.event table, which is named much more obviously, holds definitions for events that are executed by the Event Scheduler. MariaDB provides an additional SQL mode that is not available in MySQL, specifically IGNORE_BAD_TABLE_OPTIONS, and each of these tables has an sql_modecolumn that influences the environment in which the routine or event executes. There is a surprising amount of odd behavior for mysqldump related to these tables. In MySQL 5.5, mysqldump refuses to dump the mysql.event table unless you gave it the--events option, even if you tried dumping all tables from the mysql database directly. If you try to dump the mysql.event table without the --events option, you’ll get a warning «Skipping the data of table mysql.event. Specify the --events option explicitly.» even if you don’t have any events!

mysql 5.5.35-log (root) [test]> select * from mysql.event;
Empty set (0.00 sec)

$ mysqldump -A >/dev/null
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
$ mysqldump mysql >/dev/null
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
$ mysqldump mysql event >/dev/null
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

In MySQL 5.6, INSERT statements for the mysql.event table are dumped if you dump the mysql database, and redundant CREATE EVENT statements are dumped if you use the --events option to mysqldump and dump any tables from databases where events are defined (events are per-database!). The situation with mysql.proc is a little different. You have always been able to dump that table without needing to use the --routines option, as INSERT statements for it are included if you dump the table normally. However, starting with MySQL 5.6, mysqldump gives warnings if you try to dump data from a server with GTIDs enabled and you leave off certaion options:

$ mysqldump -A > /dev/null
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

The point of mentioning all this is to illustrate that it is probably a good idea to simply specify --routines --events when using mysqldump. But what does this have to do with upgrading or migrating from MySQL to MariaDB? Unfortunately, if you’re using the--events and --routines options to mysqldump, you will have some trouble importing that dump into MariaDB. That’s because mysqldump by default has --add-drop-table enabled, which means that the existing, correct definitions for mysql.procand mysql.event will be obliterated and replaced by the invalid definitions when you load the dump, and MariaDB will then be unable to use those tables when it tries later to execute the CREATE PROCEDURE/FUNCTION and CREATE EVENT statements generated by the --routines and --events] options, respectively.

mariadb-5.5.35-osx10.9-x86_64 $ mysqldump -S ../mysql-5.5.35-osx10.6-x86_64/data/mysql.sock --events --routines -A | mysql
ERROR 1548 (HY000) at line 793: Cannot load from mysql.proc. The table is probably corrupted

We can solve this problem for mysql.proc in one of two ways:

1. Leave out the CREATE PROCEDURE statements and rely on the INSERT statements for the mysql.proc table. This requires simply removing the --events option when executing mysqldump:

   mariadb-5.5.35-osx10.9-x86_64 $ mysqldump -S ../mysql-5.5.35-osx10.6-x86_64/data/mysql.sock  -A | mysql  && echo Success!
   Success!
   

2. The second option is just to force the mysql client to execute all statements in the dump even if some of them fail. So, again we rely on the INSERT statements for the mysql.proc table. We accomplish this using the --force option for themysql clent:

   mariadb-5.5.35-osx10.9-x86_64 $ mysqldump -S ../mysql-5.5.35-osx10.6-x86_64/data/mysql.sock  --routines -A | mysql --force
   ERROR 1548 (HY000) at line 776: Cannot load from mysql.proc. The table is probably corrupted
   ERROR 1548 (HY000) at line 786: Cannot load from mysql.proc. The table is probably corrupted
   
   mariadb-5.5.35-osx10.9-x86_64 $ mysql -e 'select name from mysql.proc'
   +------+
   | name |
   +------+
   | p1   |
   +------+
   

That takes care of mysql.proc but leaves mysql.event still to deal with. As we found above, simply leaving off the --events option is a no-go if you’re using mysqldumpfrom MySQL 5.5, so we have to use the --force option for the command-line client.

mariadb-5.5.35-osx10.9-x86_64 $ ../mysql-5.5.35-osx10.6-x86_64/bin/mysqldump -S ../mysql-5.5.35-osx10.6-x86_64/data/mysql.sock  --routines -A | ./bin/mysql --force
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
ERROR 1548 (HY000) at line 776: Cannot load from mysql.proc. The table is probably corrupted
ERROR 1548 (HY000) at line 786: Cannot load from mysql.proc. The table is probably corrupted
mariadb-5.5.35-osx10.9-x86_64 $ ../mysql-5.5.35-osx10.6-x86_64/bin/mysqldump -S ../mysql-5.5.35-osx10.6-x86_64/data/mysql.sock  --events --routines -A | ./bin/mysql --force
ERROR 1545 (HY000) at line 791: Failed to open mysql.event
ERROR 1545 (HY000) at line 803: Failed to open mysql.event
ERROR 1548 (HY000) at line 815: Cannot load from mysql.proc. The table is probably corrupted
ERROR 1548 (HY000) at line 825: Cannot load from mysql.proc. The table is probably corrupted

And anyway, mysqldump on MySQL 5.6 will complain if you are using GTIDs and leave off --routines, so it’s probably a good practice to leave it in. In any case, we will still need to run mysql_upgrade to change the tables to use the new definition before we can use any of the routines or events.

mariadb-5.5.35-osx10.9-x86_64 $ mysql -e 'select event_name from information_schema.events'
ERROR 1545 (HY000) at line 1: Failed to open mysql.event
mariadb-5.5.35-osx10.9-x86_64 $ mysql -e 'call p1'
ERROR 1548 (HY000) at line 1: Cannot load from mysql.proc. The table is probably corrupted
mariadb-5.5.35-osx10.9-x86_64 $ mysql_upgrade --force
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
...
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK
mariadb-5.5.35-osx10.9-x86_64 $ mysql -e 'call p1'
+------------------------------------+
| message                            |
+------------------------------------+
| Hello, I am Stored Procedure `p1`! |
+------------------------------------+
mariadb-5.5.35-osx10.9-x86_64 $ mysql -e 'select event_name from information_schema.events'
+------------+
| event_name |
+------------+
| ev1        |
+------------+

Note that you may need to restart MariaDB in order to get it to properly initialize the Event Scheduler, if you shut the server down at some point while it had an invalidmysql.event table. If MariaDB encounters the wrong definition on startup, the Event Scheduler is simply disabled (look for «Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.» in the error log to indicate failure or «Event Scheduler: Loaded x events» to indicate success). My final recommendations, then, are these:

1. You should use --events --routines when using mysqldump.
2. You should use the --force option of the mysql client when loadingmysqldump output as part of a migration from MySQL to MariaDB, and in any other circumstance where system tables might vary between the source and destination.
3. You must run mysql_upgrade after loading an SQL dump from any other version and/or any other software.