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

https://github.com/craftcms/cms/pull/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 https://github.com/craftcms/cms/pull/12560#issuecomment-1404814560?

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.

[rob-c-baker]

More info, not really helpful per-se but additional to the above:

It seems 8.0.33 and up has new advice marked 21st Feb 2023: https://bugs.mysql.com/bug.php?id=109701#c530124

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

If I'm reading this right, non blocking mysql dumps could happen again as long as --set-gtid-purged = OFF, which as far as I can see is only viable on a non-replicated MySQL instance. So not an assumption that can be made at core level.

[PeterBocan]

Hello all,

I have been looking into this for a day or two already, what is the quickest remedy for Craft3 and MySQL 8.0?

[rob-c-baker]

To my knowledge, Craft 3 has no configuration based way to adjust the backup command so it looks like you will need to backup manually with mysqldump or some other tool to extract SQL dumps from your database.

[PeterBocan]

Is there a guidance for a manual backup? What, how and where?

[rob-c-baker]

Probably not, as that's more a dev-ops / infrastructure question than one for Craft devs (which I am not 😉).

I can see the backup being broken was fixed in Craft 3.7.64 which if you don't have, you could try an upgrade. If you want backups to work as a single transaction (which is my involvement in this issue) then you will need to use an external backup tool to backup like mysqldump.