Unexpected problem with triggers and mysqldump
Join the DZone community and get the full member experience.Join For Free
This post comes from Stephane Combaudon at the MySQL Performance Blog.
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'
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
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.
Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Application Architecture Design Principles
Integrating AWS With Salesforce Using Terraform
The Ultimate API Development Guide: Strategy, Tools, Best Practices
Automating the Migration From JS to TS for the ZK Framework