mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table

Written by Claudio Kuenzler - 0 comments

Published on June 6th 2023 - Listed in MySQL Databases MariaDB

When tables are altered or otherwise modified during a mysqldump backup process, the backup process will fail and result in an error such as this:

mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `jobs` at row: 0

In the past couple of days I could see that on a particular customer server. The backup script, launching mysqldump for each database, usually runs with a defined cron job:

root@server:~# crontab -l
#Ansible: Daily MySQL Dump
00 01 * * * /root/scripts/backup-mysql.sh

This means the daily backup script runs at exactly 1 am. What would possibly alter the table structure in the middle of the night?

Looking at the customer's cron jobs I found the following entry:

root@server:~# crontab -l -u customer
0 * * * * /usr/local/bin/wp jobs update_data --path="/var/www/app.example.com/"

Turns out this script runs once an hour during 24h. And of course this also runs at exactly 01:00 when the mysqldump is initiated by the backup script.

To fix this, the script, which modified the table, must not run during the mysqldump time. To avoid running at 01:00 but on every other full hour, the cron job was modified to:

0 00,02-23 * * * /usr/local/bin/wp jobs update_data --path="/var/www/app.example.com/"

This way the table structures remain consistent during the mysqldump process.

Note: During a mysqldump process, the tables being backed up are temporarily locked (LOCK TABLE) to ensure a consistent state. The applications usually continue to work, unless there's heavy I/O on the database during 24/7. In such a case, a replicated database node (or another data node of a Galera cluster) should be used for backups.

Add a comment

Comments (newest first)

No comments yet.