[4.x]: Backups are broken under mysql 5.7.41 and 8.0.32

[nitriques]

What happened?

Description

We run many craft instances, on many servers.
They all are cpanel/whm servers, which are a quite common.
With the last update, it installed mysql 5.7.41 / 8.0.32 on most our the servers.
This version was released by Oracle on Jan 17th (3 days ago).
Since then, all running craft instances on those server can no longer run backups.

We tested on other servers that still have mysql 5.7.39 and it stills works.
We tested with craft 4.2.5.2 and 4.3.6.1.
Both works/fails depending on mysql's version (so it's not directly craft related).

The problem is only present when the --single-transaction is used. Removing it creates a backup.
Nothing seems related to this flag in the release notes:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-41.html
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html

Since --single-transaction locks the database, I can understand why mysql would want to FLUSH TABLES before doing so. But having a user that has this privilege might be something pretty rare: Under cpanel, your can't even create a user with such privilege. And it's a global privilege and can not be enable only for specific db.
Using the --single-transaction seems like a good practice tho so maybe craft could check for the privilege before including it ?

I am pretty sure that we are not the only ones with broken backups right now and even tho this is not a bug in craft, we think it should try to maximize the chance of producing a backup.

Steps to reproduce

1. Run ./craft db/backup

Expected behavior

It creates a backup, with a warning that it may be inconsistent because the DB user does not have the RELOAD privilege (?)

Actual behavior

It errors out with the message:

failed with exit code 2: mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)

Craft CMS version

4.x

PHP version

7.4-8.1

Operating system and version

Centos

Database type and version

mysql 5.7.41 and 8.0.32

Image driver and version

gd

Installed plugins and versions

• Does not mater...

[nitriques]

Side note: It is also curious that --single-transaction is used to dump the schema but not for the data...
So it's kind of useless as of now no?

Maybe the whole operation could be done in a single mysqldump operation ?

Ref: https://github.com/craftcms/cms/blob/develop/src/db/mysql/Schema.php#L200-L212

[ddarbyson]

MySql 8.0.32 broke our backups too... Here is a related case

https://support.cpanel.net/hc/en-us/articles/11786450334359-mysqldump-fails-in-MySQL5-7-8-0-with-Couldn-t-execute-FLUSH-TABLES-

[angrybrad]

Here’s the MySQL bug report https://bugs.mysql.com/bug.php?id=109685

Basically, they made a significant change in behavior in a minor point release and didn’t bother to document it.

Got a workaround for it here that should be included in the next Craft 3 and 4 releases.

#12560

[nitriques]

Basically, they made a significant change in behavior in a minor point release and didn’t bother to document it.

That's what I was fearing the most.

Thanks for the fix.

[darylknight]

I notice this is closed but the release isn't out, and the pull request hasn't passed all the checks. Brad would you mind chipping in on #12560 (comment)?

Updating the privileges works, and I'm a little concerned about what removing the single transaction flag will do.

[jamiematrix]

We've been experiencing problems running database backups from Craft 3.x since the 23rd. Hosting provider says there were PHP and MySQL updates applied that same day, but we're using 5.7.

I'm guessing from the links provided the update affects MySQL 5.7 too?

[darylknight]

Yes. This affects MySQL 5.7.41 and 8.0.32. Here's my write-up post on everything I've found so far.

[jamiematrix]

Thanks @darylknight
We have 70+ sites it affects too 😢

[brandonkelly]

This is fixed for the next Craft 3 and 4 releases, via #12560.

[brandonkelly]

Craft 3.7.64 and 4.3.7 have been released with that fix.