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:
1 2 3 4 5 | # 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:
1 2 3 4 5 6 | 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:
1 2 3 4 5 6 | $ 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:
1 2 3 4 | --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:
1 2 3 4 5 | # 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.
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),
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.
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.
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.
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.
Oh, I also realized there is –add-drop-trigger for mysqldump. So, you just need the right options.
@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!
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
Data dumping may be performed by
mysqldumpbut also byselect into outfile.I agree that the
mysqldumpcan 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.
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