MySQL Bugs: #109701: Fix for #33630199 in 8.0.32 introduces regression when --set-gtid-purged=OFF

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

Description:
As a result of:
https://github.com/mysql/mysql-server/commit/022e73ba6976b984658a1c2652178cd4b81aec28

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

2023-01-19T11:05:25.538286Z        29 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
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.587497Z        30 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
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

vs

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.

Hello Przemyslaw,

Thank you for the report and test case.

Thanks,
Umesh

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.

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.

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

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.

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!

Flushing the tables has undesired behavior on a busy system. The previous behavior with --single-transaction avoided blocking writes.

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.

+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 ...`

or 

`docker run mysql:8.0.31 mysqldump ...`

+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.

Can someone from the MySQL team respond when a fix will be available?

+1 for a response from the team.

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.

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.

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.

This bug is considered a duplicate of the following bug:

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

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:

5.7.42:
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).

8.0.33:
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.
---