MySQL mysqldump –skip-triggers

In the event of exporting data from one database to another, even if you only export select tables using the –tables option, be sure to add –skip-triggers as well if you don’t need them.

We use triggers to duplicate data between our demo databases but one of the triggers broke, breaking all the databases. After fixing the issue and re-importing all of the data from the main demo database we thought it had been fixed. I had forgotten to put in the –skip-triggers option though, so the triggers in the main demo database copied to all the sub-demo databases, creating a recursive loop. Lesson learned.

The error we were getting, just to note, was “Can’t update table ‘<table>’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.” and caused a whole chain of databases to fail.

Advertisements

Published by:

Skyrail

Production Facilitator at Audacious Church Previously Software Developer at UKFast Ltd. and Web Applications Developer at SenseLogix Ltd. Graduate of Renewable Energy BSc from the University of Exeter. Experience with PHP, MySQL, Javascript, CSS and HTML.

Categories SQLTags Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s