Bug #109701 Fix for #33630199 in 8.0.32 introduces regression when --set-gtid-purged=OFF
Submitted: 19 Jan 11:11 Modified: 21 Feb 22:39
Reporter: Przemyslaw Malkowski Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[19 Jan 11:11] Przemyslaw Malkowski
As a result of:

the mysqldump behavior with options --single-transaction & --set-gtid-purged=OFF, changed from (8.0.31):

2023-01-19T11:05:25.538346Z        29 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2023-01-19T11:05:25.538420Z        29 Query     UNLOCK TABLES

to (8.0.32):

2023-01-19T11:06:52.507656Z        30 Query     FLUSH TABLES
2023-01-19T11:06:52.587387Z        30 Query     FLUSH TABLES WITH READ LOCK
2023-01-19T11:06:52.587599Z        30 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2023-01-19T11:06:52.587672Z        30 Query     UNLOCK TABLES

There is hardly a reason for using FTWRL here when no GTID information is being taken in that scenario.

There is also no reflection of this behavior change in the documentation.

Related bug #109700

How to repeat:
Compare the behavior of mysqldump versions:

8.0.31/bin/mysqldump -umsandbox -pmsandbox -S /tmp/mysql_sandbox22233.sock --single-transaction --set-gtid-purged=OFF --databases db1 > db1.dump


8.0.32/bin/mysqldump -umsandbox -pmsandbox -S /tmp/mysql_sandbox22233.sock --single-transaction --set-gtid-purged=OFF --databases db1 > db1.dump

Suggested fix:
FTWRL should not be used without any purpose.
[20 Jan 7:20] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report and test case.

[20 Jan 11:36] Anner Visser
Another effect of this change is that mysqldump with the --single-transaction argument now requires the `FLUSH_TABLES` or `RELOAD` privilege, where it didn't before.
This has caused errors in our application. I would call it a BC break, which is unfortunate for a patch version.
[26 Jan 21:54] Patricia Gagnon-Renaud
Same issue here. GTID are turned off and --set-gtid-purged=OFF is explicitly set when calling mysqldump, and FTWRL is still being used.

We should not have to give a server wide privilege to a user who only have read-write access to a single database.

Please revert or correct the patch that introduced this regression.
[27 Jan 9:54] Lorenzo Milesi
Possibly related to https://bugs.mysql.com/bug.php?id=109685
[27 Jan 16:16] Joe Creighton
Also seems to be in 5.7.41: after patching from 5.7.39 our scripts using mysqldump with --single-transaction --force --set-gtid-purged=off --no-tablespaces now throws an error requiring the RELOAD grant.
[31 Jan 10:40] Matt Jones
I left this comment on the Github commit but leaving it here too for better visibility.

I hope this change get's reverted, adding global perms to many isolated server users does not seem like a good work-around just to allow use of `--single-transaction`.

FYI this breaks all `--single-transaction` dumps from Virtualmin and Plesk created users created with default permissions. I haven't checked CPanel but I imagine that will have similar defaults.

Expect a lot of complaints once this rolls out widely!
[2 Feb 19:26] Brian Morton
Flushing the tables has undesired behavior on a busy system. The previous behavior with --single-transaction avoided blocking writes.
[7 Feb 1:41] Brett M
Can I confirm that this bug is going to be fixed soon in the next release? This has broken our backups and regular exports completely.

We run on AWS RDS so can't grant the extra permissions, and this update was a Security Patch so the previous isn't available on any repos to downgrade too.

This is a huge, breaking change and needs to revert. Can we have an update on this please?

I am using MySQL 5.7, but can't find a ticket for the same bug there.
[7 Feb 18:25] Dan Baggott
+1 for this being a serious PITA for anyone using mysqldump along with AWS RDS in any capacity.

For those of you where using docker is feasible, a workaround is to use something like:

`docker run mysql:5.7.40 mysqldump ...`


`docker run mysql:8.0.31 mysqldump ...`
[8 Feb 15:08] NOT_FOUND NULL !
+1 for this being a serious PITA for anyone using mysqldump along with AWS RDS in any capacity. 

This has broken all our backup scripts.
[8 Feb 20:44] Mike Lee
Can someone from the MySQL team respond when a fix will be available?
[9 Feb 8:03] Tue Korsgaard
+1 for a response from the team.
[17 Feb 11:12] Colin Dean
This has broken backups on our cPanel hosting at Zen Internet, which has been updated to MySQL 5.7.41. We are not in a position to grant the additional permissions, or to block users from accessing the databases while backing up.

Our emergency fix, as the cPanel server is running a derivative of RHEL7, has been to grab a copy of the 5.7.24 binary RPM from CentOS 7, extract the contents using rpm2cpio | cpio -idmv and use the extracted opt/rh/rh-mysql57/root/usr/bin/mysqldump instead of /usr/bin/mysqldump.

This sort of change is not something that should have been made at a point-point release, given the impact on so many servers, including those running on AWS, Plex, cPanel, etc.
[17 Feb 11:31] Wiebe Cazemier
To confirm: you can't dump a database on Amazon RDS anymore now without blocking it (because you can't do --single-transaction). If you have huge tables, the live service will block during the dump.

I installed the old mysqldump in /usr/local/bin everywhere.
[20 Feb 8:07] Brett M
Thanks for the suggestion Colin; I found a .deb for 5.7.40 in the MySQL Community Downloads section, extracted the old mysqldump from it and am temporarily using in place of the broken 5.7.41 version; it works ok.

Hoping for a proper resolution from the devs.

Thank you.
[21 Feb 22:39] Philip Olson
This bug is considered a duplicate of the following bug:


Copying the latest #109685 entry here too, which is:

Fixed as of the upcoming MySQL Server 5.7.42 / 8.0.33 releases, and here's the 
proposed changelog entry from the documentation team:

With mysqldump, using --single-transaction required either the RELOAD or
FLUSH_TABLES privilege with mysqldump v5.7.41. This requirement now only
applies when gtid_mode=ON (defaults to OFF) and with --set-gtid-purged =
ON|AUTO (defaults to AUTO).

With mysqldump, using --single-transaction required either the RELOAD or
FLUSH_TABLES privilege with mysqldump v8.0.32. This requirement now only
applies when gtid_mode=ON (defaults to OFF) and with --set-gtid-purged =
ON|AUTO (defaults to AUTO).

Thank you for the bug report.