23

I have a process that exports the data from an AWS RDS MariaDB using mysqldump which has been running succesfully in a docker-image on Concourse for years.

Since two nights ago the process has started failing with the error:

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

The official AWS explanation seems to be that because they do not allow super privileges to the master user or GLOBAL READ LOCK the mysqldump fails if the --master-data option is set.

I do not have that option set. I'm running with these flags:

mysqldump -h ${SOURCE_DB_HOST} ${SOURCE_CREDENTIALS} ${SOURCE_DB_NAME} --single-transaction --compress | grep -v '^SET .*;$' > /tmp/dump.sql

mysqldump works fine when executed from my local Mac. It fails with the error that it couldn't execute FLUSH TABLES WITH READ LOCK only from the linux environment.

My question is, does anyone know how to disable the FLUSH TABLES WITH READ LOCK command in mysqldump on linux?

EDIT: Happy to accept @sergey-payu answer below as having fixed my problem, but here's a link to the MySQL bug report for the benefit of anyone else coming across this issue https://bugs.mysql.com/bug.php?id=109685

cjashwell
  • 419
  • 1
  • 6
  • 17
  • 1
    I’m also facing similar issue for few days. AWS has done something. I have multiple accounts in all accounts it’s happening. My error is below: mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045) – Kiran M Jan 21 '23 at 00:24
  • Thank you for that information. I will reach out to AWS support in that case. – cjashwell Jan 21 '23 at 10:07
  • 3
    https://bugs.mysql.com/bug.php?id=109685#c530123 reported on _2023-02-21_ that a fix will be forthcoming: ❝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`). [...]❞ – cueedee Feb 28 '23 at 14:32

3 Answers3

23

I faced the same issue couple of days ago. My mysqldump script had been working for years just fine until it started to give me the Access denied; you need (at least one of) the RELOAD privilege(s) for this operation error. My first instinct was to grant this privilege. But after that I started to get Access denied for user 'user'@'%' (using password: YES) (1045) error, which is documented in AWS docs. After couple hours of investigation it turned out to be a bug of the most recent 5.7.41 version of mysql (It was released 17th of January, exactly when we started to get errors). Downgrading to 5.7.40 solved the problem. It's interesting that 5.7.41 changelog doesn't list anything close to the FLUSH TABLES WITH READ LOCK or to default values.

Sergey Payu
  • 286
  • 2
  • 5
  • I was running into the same issue and this minor version downgrade solved it for me, at least for the time being. – Eric Sanders Jan 23 '23 at 06:17
  • Thank you very much @Sergey Payu that's excellent, your information has solved my problem. I downgraded the MySQL version I was using in my Concourse job to 5.7.40 and the error has gone away. – cjashwell Jan 23 '23 at 11:49
  • 1
    Do you have a reference for this bug? thanks – Maxxer Jan 25 '23 at 12:26
  • 1
    @Maxxer - https://bugs.mysql.com/bug.php?id=109701 looks like the culprit to me... – cueedee Jan 27 '23 at 07:39
  • 2
    I filed a bug [to Ubuntu](https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866), they point to [109685](https://bugs.mysql.com/bug.php?id=109685) upstream. 109701 could be relevant too. – Maxxer Jan 27 '23 at 09:52
  • We're using mysql server 8.0.28 in RDS and `mysqldump Ver 8.0.32-0buntu0.20.04.1 for Linux on x86_64 ((Ubuntu))` so this is an issue lots of places – Panda Jan 27 '23 at 16:13
  • Sergey Payu thanks for the digging in and figuring this one out, and thank you @Maxxer for submitting the bug and posting the links here. I'm running into this issue as well. – dylst Jan 27 '23 at 17:34
  • 14
    For anyone on Ubuntu looking to rollback this is what I did: `sudo apt-get install mysql-client-8.0=8.0.19-0ubuntu5` followed by `sudo apt-get install mysql-client-core-8.0=8.0.19-0ubuntu5` That will get you mysqldump version 8.0.19 which fixed the problem> I also put a hold on both packages: `sudo apt-mark hold mysql-client-8.0` and `sudo apt-mark hold mysql-client-core-8.0` – Panda Jan 27 '23 at 21:25
  • Interestingly this issue seems to be with 5.7.41, but we are using 5.7.38 in AWS RDS, and we have the same problem, and it just started happening very recently (it started about a week ago). – Moises Jan 30 '23 at 19:31
  • does the `sudo apt-get install mysql-client-8.0=8.0.19-0ubuntu5` works for ubuntu 22.04? i'm getting an error with packages missing and the version replaced according to `/var/log/history.log` was `8.0.30-0ubuntu0.22.04.1` but that version is not available either – theist Jan 30 '23 at 19:57
  • 3
    answering my own comment, in case someone is with this in ubuntu 22.04, according to `apt-cache madison` the version to fix is initial `jammy` wich is `8.0.28-0ubuntu4` – theist Jan 30 '23 at 20:16
  • I just painted myself into a corner with this issue (upgrading to 5.7.41 without a server snapshot to restore from). Any suggestions on how to do a rollback to 5.7.40 on ubuntu 18 server? I've tried the usual, but the package doesn't seem to exist anywhere. Or am I missing something? – NumerousHats Jan 31 '23 at 18:18
  • @NumerousHats I don't know your exact situations OPs-wise but I would stand up a different box with an older version of mysqldump and take a snapshot from there if you can. Hopefully that works for you. – Panda Jan 31 '23 at 22:16
  • @Panda I just saw your comment, but that's basically what I did (after first installing the wrong version again and using @ theist's hint on how to downgrade). I think I'm good now, knock on wood. – NumerousHats Feb 01 '23 at 23:25
  • answering my comment too, the Docker container where I was running the mysqldump was 5.7.41, I downgraded the MySQL image, and now it is working like before. – Moises Feb 02 '23 at 16:59
  • @theist do you have the steps you took to install 8.0.28 i seem to always be getting mysqldump version 8.0.32-0ubuntu0.22.04.2 – Kay Feb 07 '23 at 13:43
  • 3
    @Kay in my case (ubuntu 22.04 hosted in AWS) `apt-get install mysql-client-core-8.0=8.0.28-0ubuntu4 mysql-client-8.0=8.0.28-0ubuntu4; apt-mark hold mysql-client-core-8.0 mysql-client-8.0` note that your available packages can vary depending on distro / repository available etc. As a generic solution for any apt based distro is to check available versions with `apt-cache madison ` to see what versions are offered for your system – theist Feb 07 '23 at 18:21
  • Also Ubuntu 22.04 here, confirming `sudo apt install mysql-client-core-8.0=8.0.28-0ubuntu4` and `sudo apt-mark hold mysql-client-core-8.0` does the trick. For some reason leaving `mysql-client` and `mysql-client-8.0` at version 8.0.32 is just fine; `mysqldump --version` still returns 8.0.28. This is a dedicated backup-script server, so YMMV. I'd be paranoid about running incongruent versions otherwise. – sheng Feb 11 '23 at 07:24
  • 1
    https://bugs.mysql.com/bug.php?id=109685#c530123 reported on _2023-02-21_ that a fix will be forthcoming: ❝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`). [...]❞ – cueedee Feb 28 '23 at 14:26
  • Downgrading on CentOS is something like `yum list installed` which will show the exact platform your client is using. (e.g. `mysql-community-client.x86_64`) and then running something like `sudo yum downgrade mysql-community-client.x86_64`. – Bower Mar 08 '23 at 19:36
  • I found that no matter the minor version (even 5.7.42 with the supposed fix for this bug), I was getting this same error. It turns out that the updated mysql-client version was causing the problem. I could have downgraded the mysql-client, but I simply installed the mariadb client and everything works fine again (even my original mysqldump scripts). – user6096790 Jul 04 '23 at 18:04
10

I found that installing MariaDB Client resolves this issue.

In Ubuntu, run:

sudo apt remove mysql-client -y && sudo apt install mariadb-client -y
S.B
  • 13,077
  • 10
  • 22
  • 49
Dave
  • 101
  • 3
0

On Debian, I'm having a really difficult time downgrading mysqlclient and mysqldump from 8.0.32 to 8.0.31 (or any other 8.0.x). (At least on ubuntu it looks like downgrading to 8.0.28 is easy. I don't know if 8.0.28 is stable.)

Here's the best I've come up with. It seems to work for me but YMMV (your mileage may vary). Generally the deb packages in the https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/ directory seem to work.

# May be optional:
apt-get remove -y default-libmysqlclient-dev && apt-get autoremove -y

# NOTE: Use `dpkg -I abc.deb | grep Depends` (recursively, on mysql-client
#       and each of its mysql package dependencies) to find out which other
#       mysql packages are needed.
# apt-get check: Report if there are now any broken dependencies.
# rm -rf /var/lib/apt/lists/*: This is meant to be run in a Dockerfile,
# and deleting these files after apt-get install is commonly done to
# make the resulting image layer smaller, which speeds up image download
# from a docker image repo later.
wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-client_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-community-client_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-community-client-core_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-community-client-plugins_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/libmysqlclient-dev_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/libmysqlclient21_8.0.31-1debian11_amd64.deb && \
    wget https://repo.mysql.com/apt/debian/pool/mysql-8.0/m/mysql-community/mysql-common_8.0.31-1debian11_amd64.deb && \
    dpkg -i *_8.0.31-1debian11_amd64.deb && \
    rm *_8.0.31-1debian11_amd64.deb && \
    apt-get update && \
    apt-get check && \
    rm -rf /var/lib/apt/lists/*
David Winiecki
  • 4,093
  • 2
  • 37
  • 39