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:


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.

Share this post

Comments (10)

  • Sheeri

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

    February 11, 2013 at 11:37 am
  • Maetthu

    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.

    February 11, 2013 at 5:01 pm
  • Sheeri

    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.

    February 11, 2013 at 5:05 pm
  • Robert Hodges

    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.

    February 11, 2013 at 10:00 pm
  • Ike Walker

    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.

    February 12, 2013 at 5:45 pm
  • Sheeri

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

    February 12, 2013 at 5:48 pm
  • Stephane Combaudon


    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!

    February 13, 2013 at 5:23 am
  • Shantanu Oak

    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.


    February 25, 2013 at 10:30 am
  • Marius Garbea

    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.

    April 24, 2014 at 5:47 am
  • Rares P

    A danger with the approach in the article is that if the triggers are exported with the table schema rather than with the data, “after insert” triggers will fire during the data import. This is the behaviour in MySQL 5.6 at least.
    So, to prevent the triggers from firing during import, one needs to either:
    1. run mysqldump –no-data –skip-triggers dbname > schema.sql and then run mysqldump –no-create-info dbname > schema.sql
    or 2. run mysqldump –no-data dbname > schema.sql followed by mysqldump –no-create-info –add-drop-triggers dbname > schema.sql

    November 2, 2015 at 9:14 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.