Over a million developers have joined DZone.

Unexpected problem with triggers and mysqldump

· Performance Zone

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

This post comes from 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'

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.



Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}