Mysqldump: Error 1412: Table definition has changed

[Hossain_Mahmud]

Hi,

I’m running Percona Server 8.0 on Kubernetes with StatefulSets. When I try to back up a database using mysqldump 8.0.21:

mysqldump --set-gtid-purged=OFF --single-transaction <db_name>

I get this error:

mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table <table_name> at row: 0

Any advice on how to fix this?

Thanks!

[matthewb]

Hello @Hossain_Mahmud,
Yes, the advice is don’t do any ALTER TABLE while a backup is in progress. You need to increase the locking level of mysqldump to prevent any ALTER TABLE from happening while the backup is running.

[Hossain_Mahmud]

@matthewb

Yes, the advice is don’t do any ALTER TABLE while a backup is in progress. You need to increase the locking level of mysqldump to prevent any ALTER TABLE from happening while the backup is running.

Thanks. Just to clarify, when you mention increasing the locking level, are you suggesting using flags like --lock-tables or --lock-all-tables with the mysqldump command?

[matthewb]

@Hossain_Mahmud,
Yes, try with those increased locking levels. If it works with those, then you know things are still modifying while the backup is happening when using less levels.