Suddenly failing to backup on RDS with 1045 error (Couldn't execute 'FLUSH TABLES WITH READ LOCK':)

[leeovery]

The main error Im consistently now getting is:

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

This was working perfectly up until a couple of days ago. Its not likely this package as nothing has changed. More likley something on RDS side but I cant seem to see what it is.

When I run a mysqldump command from the command line via an EC2 instance, trying to dump an RDS database, I am getting the same error. It occurs with --single-transaction. Removing that option allows things to work but thats not the solution as that option is required.

I wonder if anybody else is seeing this issue? Perhaps we can figure it out together.

Cheers
Lee

[EvilLooney]

Seems to be an issue in mysqldump version 8.0.32 that was raised here :
https://bugs.mysql.com/bug.php?id=109685

[leeovery]

Thanks for sharing this! I googled for so long I didn't come across this lol.

Doesn't read like they're going to fix it but just update the docs. Bit confusing. See how it pans out.

[leeovery]

Seems like this has been fixed for me. I guess RDS got an update. All good now.

[leeovery]

I actually spoke too soon! It worked last night but this afternoon's backup failed on prod but succeeded on staging. Both running the same mysql version, both on RDS. Very odd!!

[leeovery]

Prod and staging are both running MySQL 8.0.28.

[EvilLooney]

You might have different mysqldump versions on your EC2 instances?

[leeovery]

Yea it's possible. I'll check tomorrow. The super odd thing is that I manually tried to do a dump yesterday using mysqldump with single transaction. It failed on prod which I expected (because the automated backups had been failing). But it also failed on staging. The automated backups had been passing on staging. But then yesterday afternoon both automated backups failed. Then early hours this morning both prod and staging passed. Then again this afternoon, both failed.

So something odd is afoot.

[EvilLooney]

I downgraded mysql on my instance (running Ubuntu) until this whole debacle is figured out using this method: https://stackoverflow.com/questions/75183032/mysqldump-for-aws-rds-flush-tables-error-on-linux-only#comment132809986_75202033

Hope this helps.