August 1, 2014

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:

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

What????

Let’s look around line 86:

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:

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:

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.

About Stephane Combaudon

Stéphane joined Percona in July 2012, after working as a MySQL DBA for leading French companies such as Dailymotion and France Telecom.

In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.

Comments

  1. Sheeri says:

    I’ve seen companies use triggers plenty, but they don’t tend to separate out schema and data in mysqldump. They might use only physical backups, or they use mysqldump to export all the schema and data together.

    In the wild, I’ve only seen people export the data and schema separately if they specifically want to change something in the schema (e.g. change myisam tables to innodb tables upon reload),

  2. Maetthu says:

    I’ve also seen this same error when importing a database where the triggers has been created using a fully qualified “schema.triggername” as their name. Re-importing into another schema still creates the triggers within the old schema. To fix it, we had to strip the schema from the trigger names manually (well, sed-ually ;) before being able to import it to another schema. I didn’t find an option for mysqldump to fix the names – it just uses the same name like it was used when creating the trigger. Since then, I always check the naming format before importing a schema with triggers… it avoids confusion later on.

  3. Sheeri says:

    I’m surprised mysqldump doesn’t have an –add-drop-triggers flag. That would help, to drop the trigger beforehand. DROP TRIGGER IF EXISTS has been around since 5.0, so compatibility isn’t an issue.

  4. Triggers are trouble on MySQL. I hit a somewhat different issue with a customer implementation. As I recall the problem was that the triggers defined in a mysqldump output file were trying to fire as we reloaded it. We had to edit to remove them. MySQL needs a command like Oracle’s ALTER TABLE DISABLE ALL TRIGGERS. This help on replication as well.

  5. Ike Walker says:

    I agree with Sheeri that plenty of people use triggers in MySQL, but like some other features introduced in 5.0 there are some funny quirks like the one you encountered in mysqldump. Views are basically treated like tables, but stored routines and triggers and handled separately. And although DROP TRIGGER IF EXISTS is supported, I’ve always been irked by the fact that CREATE TRIGGER IF NOT EXISTS is not supported.

  6. Sheeri says:

    Oh, I also realized there is –add-drop-trigger for mysqldump. So, you just need the right options.

  7. Stephane Combaudon says:

    @Sheeri,

    Unfortunately the –add-drop-trigger option is only supported when using MySQL Cluster:

    root@wheezy:~# mysqldump –add-drop-trigger –no-create-info sakila
    mysqldump: unknown option ‘–add-drop-trigger’

    It’s stated in the documentation, but like you, I first forgot to read the note!

  8. Another approach would be to use “–compatible=no_table_options” while dumping data. Make sure that InnoDB is the default engine. The catch is that it will also remove the character set declaration from create table statement. Since you are converting MyISAM table to InnoDB I guess this will not be an issue unless you are using UTF-8 data or expect foreign keys.

    MySQL has a great feature called “A”. That is dependent upon feature “B” which is not available till version X.Y

    A lot of my clients are not using MySQL because of the peculiar way MySQL works (as mentioned above)

    There is no way to disable triggers nor any way to disable foreign key checks completely.

    http://bugs.mysql.com/bug.php?id=62602

  9. Marius Garbea says:

    Data dumping may be performed by mysqldump but also by select into outfile.
    I agree that the mysqldump can perform the dump for all tables. But the loading speed is terrible. On top, one also avoids the triggers problem.
    Anyway, I do agree with you: by default, triggers should *not* be present in the dump – and this *may* be a consequence of late addition of triggers in MySQL.

Speak Your Mind

*