MySQL Bugs: #109685: mysqldump has incompatibile change in MySQL 8.0.32

Bug #109685	mysqldump has incompatibile change in MySQL 8.0.32
Submitted:	18 Jan 2023 15:18	Modified:	21 Feb 2023 22:20
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	8.0.32	OS:	CentOS (7.9)
Assigned to:		CPU Architecture:	x86

Description:
There's incompatibility between 8.0.31's mysqldump and 8.0.32's mysqldump.

https://github.com/mysql/mysql-server/commit/022e73ba6976b984658a1c2652178cd4b81aec28

8.0.32's `mysqldump --single-transaction` use `FLUSH TABLES WITH READ LOCK` , without `--flush-logs` or `--source-data`.

That's not described in Refman and Relnotes.

How to repeat:
mysql> SET GLOBAL general_log = 1;

$ mysqldump --single-transaction --all-databases > /dev/null
$ grep FLUSH /path/to/general_log

Suggested fix:
Add this behavior into Refman and Relnotes.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

For what it's worth, upgrading mysqldump from 8.0.31 to 8.0.32 is preventing us from running mysqldump against AWS Aurora MySQL, getting this error:

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user

We also had this issue and had to revert on AWS Linux 2 using yum history undo to get it to the previous 8.0.31

Hello tanaka-San,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
This is a docs bugs.

Based on the commit message, it seems like the inconsistency is specific to servers using GTID.

Why is mysqldump still blocked on servers that have gtid_mode off, or when using the `--set-gtid-purged=OFF` CLI argument?

As an end user, I would expect the mysqldump to try to work with the level of privileges an ordinary mysql user would have, especially in circumstances where it does not seem necessary to use the more restricted permissions.

I see the logic in this being a docs bug, and it should have been documented and/or added to the changelog. 

However, this is a major change and should only be done in a major update (unless it's security related, which I don't believe is).  

Developers should not be removing a major feature in a minor update. If it worked in 8.0.31 and not in 8.0.32, then that's a defect.  If the intention is to address a major defect, then it should only be done in a major release such as from 8.0 to 8.1 (or 8.0 to 9.0 for example).  Making a change like this in a minor version bump is unwise.

I don't see the doc tag / category. Is this been verified as a doc bug?

This issue is originated from bug https://bugs.mysql.com/bug.php?id=105761 .
We have provided a less invasive patch for the original issue at #105761

Please at least provide an option to disable the `FLUSH TABLES WITH READ LOCK` for mysqldump. Otherwise it breaks backing up from AWS, and this is just a point release.

I get that in a race condition it can give erroneous results, but it's much better than nothing, and some of us depend on it for backups and didn't expect it to break in a point release.

Yes, this is a real problem for AWS users. Also Ubuntu has removed 8.0.31 (as this is a security update), so it is not easy to downgrade.

For me it is a bit odd that a user now needs a "Server administration" user role to create a mysqldump of it's database? Is this a bug which will be changed again or do i need to remove --single-transaction from my mysqldump command and need to risk inconsistent dumps?

Huge +1 to Hans Borresen's comment: it looks like this patch is totally unnecessary when --single-transaction --set-gtid-purged=OFF is set. Since this fix breaks backups from AWS RDS, could we please detect when that is the case and not perform the 'FLUSH TABLES WITH READ LOCK' when it is not necessary?

Because it breaks backups from AWS RDS, they don't allow 'FLUSH TABLES WITH READ LOCK' (see https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/), and it fails there with the following message:

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

Marcelo Altmann does your less-invasive patch take this into account hopefully?

This bug is causing all our backups to fail. I fully agree that mysqldump should not require flushing tables. When can we expect this to be fixed?

+1  Many of our database backups are broken because of this so we are at real risk of losing client data and clients.

It seems by introducing a breaking change like this in a minor point release you could be at risk of losing the trust of your users. Personally, I am much more reluctant to install minor point releases, including critical security updates, because of this new reckless disregard for the stability of the software.

+1. This is definitely an issue for any AWS RDS users.  It has caused our (non-critical) backups to fail.  I'd wager this is an issue for many users who don't know it's an issue yet, and could probably be a catastrophic issue if mysqldump is being used in any critical workflows.

Related to https://bugs.mysql.com/bug.php?id=109701

This is a severe issue that will affect, and put at risk, many existing systems, which in turn will severely damage Oracle's reputation.

This also happens on Ubuntu 20.04.3 LTS and Ubuntu 22.04.1 LTS
I can imagine that it happens on nearly any system that connects to AWS RDS.

This really need to be fixed. It is not a documentation issue, it is a breaking change done in a minor version and it affects backups greatly.

This breaks our production backup scripts for our RDS instance. It affects a lot of people running MySQL on AWS RDS and there doesn't seem to be any viable workaround for it. This needs to be fixed asap.

I fixed this issue on Amazon Linux 2 by downgrading the package.
I removed mysql-community-client-5.7.41-1.el7.x86_64 and installed mysql-community-client-5.7.33-1.el7.x86_64

yum remove mysql
yum erase mysql
yum install mysql-community-client-5.7.33-1.el7.x86_64

only workaround for me was to downgrade to 8.0.19. I don't think MySQL is going to fix this anytime soon, as it is a security fix for them..

mysqldump v8.0.32 is broken for us now as well with AWS Aurora MySQL...

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user

Posted by developer:
 
This is a verified issue, in the meantime I added the following to the associated 8.0.32 release note for Bug #105761:

Limitation: this adds a RELOAD privilege requirement when using --single-transaction to
execute FLUSH TABLES WITH READ LOCK; the MySQL team is investigating a solution.

FLUSH TABLES WITH READ LOCK statement locks write access to tables on our production environment. This renders our application unusable while using mysqldump --single-transaction.

This affects both 8.0.32 / 5.7.41 releases.

Posted by developer:
 
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.