Couldn't execute 'FLUSH TABLES': Access denied since 5.7.41/8.0.32

[Maxxer]

As of today, our dump script stopped working. During the night, unattended-upgrades upgraded from 5.7.40 to 5.7.41.

Same happened on 20.04, upgraded from 8.0.31 to 8.0.32.

The changelog doesn't mention any change in permissions handling.
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-41.html

Relevant discussion:
https://stackoverflow.com/a/75202033/738852

The error reported is:

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

The command used for dump:

mysqldump --defaults-extra-file=/dati/mysql_backup_credentials.cnf -h 127.0.0.1 --quick --skip-lock-tables --single-transaction DBNAME > FILENAME

User created with:

grant select, lock tables, show view on *.* to 'backup-user'@'localhost' identified by 'pwd';

ProblemType: Bug
DistroRelease: Ubuntu 18.04
Package: mysql-server-5.7 5.7.41-0ubuntu0.18.04.1
ProcVersionSignature: Ubuntu 4.15.0-200.211-generic 4.15.18
Uname: Linux 4.15.0-200-generic x86_64
ApportVersion: 2.20.9-0ubuntu7.28
Architecture: amd64
Date: Wed Jan 25 13:25:03 2023
InstallationDate: Installed on 2020-04-20 (1009 days ago)
InstallationMedia: Ubuntu-Server 18.04.4 LTS "Bionic Beaver" - Release amd64 (20200203.1)
Logs.var.log.daemon.log:

MySQLConf.etc.mysql.conf.d.mysql.cnf: [mysql]
MySQLConf.etc.mysql.conf.d.mysqldump.cnf:
 [mysqldump]
 quick
 quote-names
 max_allowed_packet = 16M
MySQLConf.etc.mysql.my.cnf: Error: [Errno 40] Too many levels of symbolic links: '/etc/mysql/my.cnf'
MySQLConf.etc.mysql.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
MySQLVarLibDirListing: ['ibdata1', 'ib_buffer_pool', 'mysql', 'nextcloud', 'mysql_upgrade_info', 'ibtmp1', 'ca.pem', 'public_key.pem', 'debian-5.7.flag', 'client-key.pem', 'sys', 'ca-key.pem', 'c1alloggyo', 'auto.cnf', 'ib_logfile0', 'private_key.pem', 'server-key.pem', 'client-cert.pem', 'server-cert.pem', 'ib_logfile1', 'performance_schema']
ProcEnviron:
 TERM=xterm-256color
 PATH=(custom, no user)
 XDG_RUNTIME_DIR=<set>
 LANG=en_US.UTF-8
 SHELL=/bin/bash
SourcePackage: mysql-5.7
UpgradeStatus: No upgrade log present (probably fresh install)
modified.conffile..etc.mysql.mysql.conf.d.mysqld.cnf: [modified]
mtime.conffile..etc.mysql.mysql.conf.d.mysqld.cnf: 2020-06-25T09:43:21.676802

[Maxxer (lorenzo-milesi)]

Granting RELOAD to the backup user restored previous dump behaviour

[mig5 (mig5)]

Upstream bug seems to be this one: https://bugs.mysql.com/bug.php?id=109685

It's not clear how to address this on a platform such as Amazon RDS (existing Amazon docs don't cover this precise new issue, only one to do with replication https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/)

The upstream MySQL bug refers to it as a 'docs bug' but doesn't clarify on the solution.

[Launchpad Janitor (janitor)]

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

[Maxxer (lorenzo-milesi)]

Can https://bugs.mysql.com/bug.php?id=109701 be relevant as well?

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

[Leonidas S. Barbosa (leosilvab)]

In order to fix this one can follow these Fix 1 step, till further mysql update on it: https://snapshooter.com/docs/fixing-mysqldump-couldn-t-execute-flush-tables-access-denied/

[Rowan Wookey (rwky)]

Just encountered this when dumping from AWS RDS which we can't adjust the permissions for, downgrading is the only workaround at the moment.

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

@Leonidas S. Barbosa: when trying to back up from an AWS RDS MySQL database, even after granting the RELOAD and PROCESS permissions, it still fails, with the following error message:

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

This is because AWS RDS doesn't allow FLUSH TABLES WITH READ LOCK regardless of permissions: https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/

So, as @Rowan Wookey reported, downgrading is the only option for AWS RDS users.

The thing is, issuing that FLUSH TABLES WITH READ LOCK is totally unnecessary when --single-transaction --set-gtid-purged=OFF are set. Since most AWS RDS users won't be using GTID, not issuing a FLUSH TABLES WITH READ LOCK in this case, when it isn't necessary, would solve the issue for them and for anyone else not using GTID (see https://bugs.mysql.com/bug.php?id=109701)

---

PS this is all information from https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/+bug/2004028 that is just documenting the issues caused by this same change for AWS RDS users. I don't mind if it's closed and merged into this one if you all think that's best.

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

[Jeffrey Walton (noloader)]

@Nevin,

> Fixed as of the upcoming MySQL Server 5.7.42 / 8.0.33 releases ...

We are running Ubuntu 20.04.6 (LTS) and MySQL 8.0.32, and we are experiencing the problem. It looks like our backups have been failing for a couple of months. Yuk...

MediaWiki (the reason MySQL is installed) released a security update today (https://www.mediawiki.org/wiki/MediaWiki_1.38). We need a backup before we can [attempt to] apply the update.

My question is, when will this be available to Ubuntu users?

[nevergone (nevergone)]

And now?

[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

[Lucas Kanashiro (lucaskanashiro)]

The update that will fix this issue is being prepared, it should land in the security pocket soon.

[Marc Deslauriers (mdeslaur)]

This regression should now be fixed with the following updates:

https://ubuntu.com/security/notices/USN-6060-1
https://ubuntu.com/security/notices/USN-6060-2

As such, I am marking this bug as "Fix Released". Please feel free to reopen it if the issue is still not fixed with the new version.

Thanks!