DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • How to Save a Million Dollars on Databases
  • Schema Change Management Tools: A Practical Overview
  • SQL Commands: A Brief Guide
  • Projections/DTOs in Spring Data R2DBC

Trending

  • Automated Testing Lifecycle
  • Docker and Kubernetes Transforming Modern Deployment
  • Selecting the Right Automated Tests
  • Demystifying Enterprise Integration Patterns: Bridging the Gap Between Systems
  1. DZone
  2. Data Engineering
  3. Databases
  4. Unexpected problem with triggers and mysqldump

Unexpected problem with triggers and mysqldump

Peter Zaitsev user avatar by
Peter Zaitsev
·
Feb. 18, 13 · Interview
Like (0)
Save
Tweet
Share
6.13K Views

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'

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.



Database Data (computing) Error message Schema InnoDB MySQL Dump (program) Testing Documentation

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

Opinions expressed by DZone contributors are their own.

Related

  • How to Save a Million Dollars on Databases
  • Schema Change Management Tools: A Practical Overview
  • SQL Commands: A Brief Guide
  • Projections/DTOs in Spring Data R2DBC

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: