mysqldump 8.0.32 no longer works for AWS RDS with --single-transaction

[Mohamed Hafez]

When you use mysqldump --single-transaction in 8.0.32, it issues a 'FLUSH TABLES WITH READ LOCK'. AWS RDS doesn't allow this call (see https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/), so in 8.032 this will fail with an error like this:

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

You can see the upstream bug report at: https://bugs.mysql.com/bug.php?id=109685

As a fix, I would suggest not performing 'FLUSH TABLES WITH READ LOCK' when the --single-transaction --set-gtid-purged=OFF options are set, since it isn't necessary in that case and would fix the problem at least for me and I imagine most others.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Gonçalo (goncaloss)]

I would add that this error occurs when dumping data from multiple hosting providers and MySQL server versions. So far I noticed it on AWS RDS, HostGator and DreamHost.

[Mohamed Hafez (mohamed-m-m-hafez)]

Perhaps a better upstream bug report: https://bugs.mysql.com/bug.php?id=109701

[Marc Deslauriers (mdeslaur)]

This is probably a dupe of bug 2003866

[Mohamed Hafez (mohamed-m-m-hafez)]

This bug is mostly a duplicate of https://bugs.launchpad.net/bugs/2003866, and is caused by the same underlying issue. The difference though is that they were able to solve the issue by granting the RELOAD privilege to the backup user, as recommended by the error message they reported.

What I'm adding here is that when trying to back up from an AWS RDS MySQL database, even after granting that permission as suggested by that initial error message, it *still* doesn't work: you get the new error message that I reported above, because AWS RDS doesn't allow the 'FLUSH TABLES WITH READ LOCK' no matter what privileges have been granted, even for root/admin accounts.

[Athos Ribeiro (athos-ribeiro)]

Hi Mohamed,

Thanks for reporting this issue.

Regarding the proposed fix

> As a fix, I would suggest not performing 'FLUSH TABLES WITH READ LOCK' when the --single-transaction --set-gtid-purged=OFF options are set, since it isn't necessary in that case and would fix the problem at least for me and I imagine most others.

I'd say we'd hold back any downstream solutions before hearing what upstream has to say on the upstream bugs linked. I would also suggest to report the bug in AWS channels.

Moreover, it would be nice to also understand if the issue also affects Ubuntu mysql servers. Would you be willing to come up with a small reproducer using Ubuntu's mysql?

[Mohamed Hafez (mohamed-m-m-hafez)]

@Athos Ribeiro if we are using Ubuntu's 8.0.32 mysqldump against an Ubuntu 8.0.32 MySQL server, that case seems to be covered by https://bugs.launchpad.net/bugs/2003866, and from reading around on that bug report and the upstream bug reports, mysqldump --single-transaction --set-gtid-purged=OFF will work fine as long as the RELOAD permission is granted, so I don't think there's anything to reproduce in that case. Or are you looking for a reproducer that it won't work without the RELOAD privilege? I think that is also covered in https://bugs.launchpad.net/bugs/2003866 though

[David L (dlevesque)]

We have some MySQL servers running directly in Ubuntu and also a separate MySQL instance running in RDS. For the servers running directly in Ubuntu, granting the RELOAD privilege did work, for the issue described in https://bugs.launchpad.net/bugs/2003866.

However, as other people have mentioned, it doesn't work with an RDS instance. After granting the RELOAD privilege, we get the error "Couldn't execute 'FLUSH TABLES WITH READ LOCK'".

This change in behavior breaks some backup scripts for our production servers. We are surprised to see such a change introduced in a minor version update.

[Nevin Lyne (nevin-f)]

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

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

[Niall Fleming (wa-erador)]

Is there any timeline for when the 8.0.33 package might be available in Ubuntu 20?

[Sergio Durigan Junior (sergiodj)]

Hello,

We are still waiting on MySQL 8.0.33 to be released. When it is, we can assess the fix and, if everything is OK, backport it to the affected Ubuntu releases.

[Patricia Gagnon-Renaud (pgrenaud-exolnet)]

MySQL 8.0.33 was release two weeks ago (2023-04-18) and it does fix this bug. It is possible to distribute this newer version?

> Using --single-transaction with mysqldump version 8.0.32 required either the RELOAD or FLUSH_TABLES privilege. This requirement now applies only when both gtid_mode=ON (default OFF) and with --set-gtid-purged = ON|AUTO (default AUTO). (Bug #109685, Bug #109701, Bug #34993824, Bug #34998910, Bug #35020512)
>
> References: This issue is a regression of: Bug #105761, Bug #33630199.

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