MySQL Bugs: #105761: mysqldump make a non-consistent backup with --single-transaction option

Bug #105761	mysqldump make a non-consistent backup with --single-transaction option
Submitted:	1 Dec 2021 8:49	Modified:	23 Jan 2023 16:04
Reporter:	Jinghua Lin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.7.36	OS:	Any (7)
Assigned to:		CPU Architecture:	Any

Description:
I used mysqldump to backup data with the following command:

mysqldump -u -p -h -P --single-transaction --set-gtid-purged=on -ER --databases db_name > 1.sql

After importing data into an empty instance and configuring replication, the sql thread got an error:

"Could not execute Update_rows event on table xxx.xxx; Can't find record in 'xxx.xxx
', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000705, end_log_pos 174057460, Error_code: 1032"

I checked all tables is InnoDB engine, so i thought --single-transaction should work. But the fact is that the backup data has gtid and no corresponding data.

I noticed a change in mysqldump in 5.7.36: "The dump file sequence has now been changed"(Bug #32843447). Maybe it caused this problem

I tested mysqldump 5.7.33 is ok. Perhaps mysqldump 8.0.27 has the same problem.

How to repeat:
1.use mysqldump backup data with slow speed.
2.insert data to tables during the backup process.
3.when bakcup completed, the dump file has all gtid but no the new data.

Using --master-data option still have this problem.

Because mysqldump get the @@GLOBAL.GTID_EXECUTED values in the end of backup process

Hi Mr. Lin,

Thank you for your bug report.

We have tested your options on both 5.7 and 8.0 and got the same output.

Verified as reported for both, latest 5.7 and 8.0.

Hi,

But i can't get the same output in 8.0.27.

I searched the code of mysqldump 8.0.27, it still contains this note "GTID state at the beginning of the backup" instead of "GTID state at the end of the backup".

Hi Mr. Lin,

This bug is verified for both 5.7 and 8.0. 

Your note has been copied to our internal database of the verified bugs.

Hence, it will be analysed when the bug gets scheduled. Scheduling of the bugs is unknown even for our Verification team. We will both be informed, with a comment in this report, once the bug is fixed, not sooner.

The issue is caused by https://github.com/mysql/mysql-server/commit/3bc436203a600129fa41159df03b4c92dc3bff59 resulting in GTID_EXECUTED been gathered at the end of the dump, instead of right after the START TRANSACTION.

Patch generated based on tag mysql-5.7.36 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 105761.patch (application/octet-stream, text), 6.31 KiB.

Thank you Mr. Altmann, for your significant contribution.

Adjusted return codes - Patch generated based on tag mysql-5.7.36

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 105761_5_7.patch (application/octet-stream, text), 6.32 KiB.

Thanks again,  Mr. Altmann, for your significant contribution.

We are also affected by this issue after upgrading MySQL to 5.7.36.

A workaround is to use mysqldump from 5.7.34.

Not sure why this is set a non critical, as it's no longer possible to get replication working on 5.7.36 which is rather important.

Thank you , Mr. Bowden ....

This is a very important news ...... which makes this bug a regression bug.

We shall copy your comments to our internal bugs database ........

Posted by developer:
 

This bug will be fixed by making --single-transaction and set-gtid-purged as incompatible options, else it will lead to a backup which when restored will result in inconsistent data.

I've updated the mysqldump documentation to reflect the incompatibility.

This bug cause a non-consistent issue. It's still there!（5.7.38).

Solution :Excute "SELECT @@GLOBAL.GTID_EXCUTED" before unlock tables and print "SET @@GLOBAL.GTID_PURGED" later.

I have pulled a request on github:
https://github.com/mysql/mysql-server/pull/405#issue-1252227773

Thank you, Jon.

Contribution submitted via Github - Bug #105761:mysqldump make a non-consistent backup with --single-tran… (*) Contribution by LeYuan Zhong (Github dgzhongly, mysql-server/pull/405#issuecomment-1141605394): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_950545006.txt (text/plain), 7.23 KiB.

When is this going to be fixed? 5.7.40?

Hi,

We truly do not have the answer to your question.

Scheduling of the bug fixing and fixing belongs to totally different department ......

Hence, both you and we will know when this bug is closed with a comment of where it is fixed.

Is this a duplicate of bug #33630199?

If so, was it addressed in 8.0.32:
https://github.com/mysql/mysql-server/commit/022e73ba6976b984658a1c2652178cd4b81aec28

Posted by developer:
 
Added the following entries to the changelogs:

For 8.0.32:
"The data and the GTIDs backed up by mysqldump were inconsistent when the options --single-transaction and --set-gtid-purged=ON were both used. It was because in between the transaction started by mysqldump and the fetching of GTID_EXECUTED, GTIDs on the server could have increased already. With this fixed, a FLUSH TABLES WITH READ LOCK is performed before the fetching of GTID_EXECUTED to ensure its value is consistent with the snapshot taken by mysqldump.

Our thanks to Marcelo Altmann for contributing to the fix."

For 5.7.36:
"The data and the GTIDs backed up by mysqldump were inconsistent when the options --single-transaction and --set-gtid-purged=ON were both used. It was because GTID_EXECUTED was fetched at the end of the dump, at which point the GTIDs on the server could have increased already. With this fixed, a FLUSH TABLES WITH READ LOCK is performed at the beginning of the dump and GTID_EXECUTED was fetched right after, to ensure its value is consistent with the snapshot taken by mysqldump.

Our thanks to Marcelo Altmann for contributing to the fix."

It sounds like this doesn't affect users that use --single-transaction --set-gtid-purged=OFF, correct? 

Could we not perform the 'FLUSH TABLES WITH READ LOCK' in that case, if it is not necessary? Because this is causing us not to be able to back up from our AWS RDS MySQL server with the following error:

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'web'@'%' (using password: YES) (1045)

AWS RDS doesn't allow this command: https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/

I second  Mohamed Hafez comment. On AWS RDS you can't have FLUSH TABLES privilege, even as master user. This means you can't dump a large database, because without --single-transaction the whole application (b)locks. Part of our backup solution is now broken.

There is another problem. We allow our devs to clone live databases. This doesn't work anymore and we are required to either: 

1) Give the users in question the FLUSH TABLES privilege. This is a dangerous privilege to give because you can't give the permission only on 'WITH READ LOCK'. Or, 

2) Have the live application block while the dump is running.

Our dumps are all made with '--set-gtid-purged=OFF'.

whoever over at oracle is freaking out and hiding comments on this bug from multiple users that show workarounds, that's bad optics for ya as a company, might want to undo that

can anyone explain that why this bug must be fixed by running flush tables command? this will impact a lot even when taking backup of just one table. could be be fixed by go backup to old version like 5.7.34?

Note: a related 8.0.33 fix is described here:

  https://bugs.mysql.com/bug.php?id=109685

In short, using --single-transaction will no longer require a RELOAD or FLUSH_TABLES privilege unless gtid_mode=ON (default: OFF) and --set-gtid=purged=ON|AUTO (default: AUTO).

Thank you, Philip.