mysqldump --single-transaction fails with "Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied"

[halfgaar]

Ubuntu has included mysql-client tools from upstream 8.0.32, including mysqldump. This includes https://github.com/mysql/mysql-server/commit/022e73ba6976b984658a1c2652178cd4b81aec28: "mysqldump make a non-consistent backup with --single-transaction".

This breaks dumping with 'mysqldump --single-transaction'. To repeat my comment on the git commit:

=====
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, if possible. 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'. In that case, the flush is not required, I'd say?
=====

Before the update, we had version 8.0.31-0ubuntu0.20.04.2 which worked fine.

I suppose the correct solution would be that upstream turns off the table flush when '--set-gtid-purged=OFF' is given, and then that Ubuntu takes that version.

In the mean time, I black-listed 8.0.32-0ubuntu0.20.04.2. But my options run short here, because the only other version is 8.0.19-0ubuntu5 from security, and that will change at some point.

Ubuntu version Ubuntu 20.04.5 LTS and more I suppose.

[Paride Legovini (paride)]

Thanks for this bug report. Looks like this is a duplicate LP: #2003866.