Unexpected problem with triggers and mysqldump

Some time ago, I had to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a logical dump on the master, exporting separately the schema and the data, then edit the CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new server.

Quite easy, isn’t it? Of course I wanted to run a test first before performing the actions on a live system.

So let’s play with the sakila database.

mysqldump has options to export schema and data separately, let’s use them:

# Export schema
$ mysqldump --no-data sakila > schema.sql

# Export data
$ mysqldump --no-create-info sakila > data.sql

Just to check that everything is fine, let’s reimport the data in a new database:

mysql> CREATE DATABASE sakila2;
Query OK, 1 row affected (0.00 sec)

$ mysql sakila2 < schema.sql 
$ mysql sakila2 < data.sql 
ERROR 1235 (42000) at line 86: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

What????

Let's look around line 86:

$ head -90 data.sql | tail -5
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER customer_create_date BEFORE INSERT ON customer
	FOR EACH ROW SET NEW.create_date = NOW() */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;

Ok, so we're trying to create a trigger and it fails. The error message suggests that there is already a trigger on BEFORE INSERT for this table.

That's correct: if we look at the schema.sql file, we can see the same trigger definition.

This means that the --no-create-info option doesn't include the CREATE TABLE statements in the output, but includes CREATE TRIGGER statements. Is it documented? Well, sort of...

If you look at the mysqldump documentation, you will see:

--triggers

Include triggers for each dumped table in the output. This option is enabled by
default; disable it with --skip-triggers.

So the conclusion is that if you are using triggers and if you want to dump data only, you have to use --skip-triggers along with --no-create-info.

The correct way to export data is therefore:

# Export schema
$ mysqldump --no-data sakila > schema.sql

# Export data
$ mysqldump --no-create-info --skip-triggers sakila > data.sql

I'm quite surprised that such an issue never came up before, it may be an indication that using triggers is far from being a common practice with MySQL.