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

Written by - 0 comments

Published on - 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.


More recent articles:

Blog Tags:

AWS Android Ansible Apache Apple Atlassian BSD Backup Bash Bluecoat CMS Chef Cloud Coding Consul Containers CouchDB DB DNS Database Databases Docker ELK Elasticsearch Filebeat FreeBSD Galera GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Icingaweb Icingaweb2 Influx Internet Java KVM Kibana Kodi Kubernetes LXC Linux Logstash Mac Macintosh Mail MariaDB Minio MongoDB Monitoring Multimedia MySQL NFS Nagios Network Nginx OSSEC OTRS Office PGSQL PHP Perl Personal PostgreSQL Postgres PowerDNS Proxmox Proxy Python Rancher Rant Redis Roundcube SSL Samba Seafile Security Shell SmartOS Solaris Surveillance Systemd TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder