Ubuntu
mysql-5.7 package

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

Bug #2003866 reported by Maxxer
134
This bug affects 21 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
mysql-5.7 (Ubuntu)
Fix Released
Undecided
Unassigned
mysql-8.0 (Ubuntu)
Fix Released
Undecided
Unassigned

Bug Description

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

Revision history for this message
Maxxer (lorenzo-milesi) wrote :
summary: - Couldn't execute 'FLUSH TABLES': Access denied
+ Couldn't execute 'FLUSH TABLES': Access denied since 5.7.41
Revision history for this message
Maxxer (lorenzo-milesi) wrote : Re: Couldn't execute 'FLUSH TABLES': Access denied since 5.7.41

Granting RELOAD to the backup user restored previous dump behaviour

Revision history for this message
mig5 (mig5) wrote :

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.

Revision history for this message
Launchpad Janitor (janitor) wrote :

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

Changed in mysql-5.7 (Ubuntu):
status: New → Confirmed
Maxxer (lorenzo-milesi)
summary: - Couldn't execute 'FLUSH TABLES': Access denied since 5.7.41
+ Couldn't execute 'FLUSH TABLES': Access denied since 5.7.41/8.0.32
Revision history for this message
Maxxer (lorenzo-milesi) wrote :

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.

Marc Deslauriers (mdeslaur)
Changed in mysql-8.0 (Ubuntu):
status: New → Confirmed
Revision history for this message
Leonidas S. Barbosa (leosilvab) wrote :

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/

Revision history for this message
Rowan Wookey (rwky) wrote :

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

Revision history for this message
Mohamed Hafez (mohamed-m-m-hafez) wrote :

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

Paride Legovini (paride)
tags: added: regression-update
Revision history for this message
Nevin Lyne (nevin-f) wrote :

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

Revision history for this message
Jeffrey Walton (noloader) wrote :

@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?

Revision history for this message
nevergone (nevergone) wrote :

And now?

Revision history for this message
Patricia Gagnon-Renaud (pgrenaud-exolnet) wrote :

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

Revision history for this message
Lucas Kanashiro (lucaskanashiro) wrote :

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

Revision history for this message
Marc Deslauriers (mdeslaur) wrote :

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!

Changed in mysql-5.7 (Ubuntu):
status: Confirmed → Fix Released
Changed in mysql-8.0 (Ubuntu):
status: Confirmed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.