craftcms
[5.x] [4.x]: Database backup broken with 5.1.0 and 4.9.0
What happened?
Description
The recent release of 4.9.0 and 5.1.0 has a bug which makes MySQL backups fail. This is because in this commit, the logic of when to use --single-transaction was reversed.
https://github.com/craftcms/cms/pull/14897/commits/97da6ca6d5cc73700c332f0a89e9a924cb9651cf#diff-83d39a4592b31403e3830a3100e22c596322d8dd9a240e07f6ecf173ec1d7bb7
This condition should be reversed again, to make mysqldump work, see pull request https://github.com/craftcms/cms/pull/14925
Breaking change in detail
Before the commit if mysql version is newer than 5.7.41 or 8.0.32 results in FALSE
if (($isMySQL5 && version_compare($serverVersion, '5.7.41', '>=')) ||
($isMySQL8 && version_compare($serverVersion, '8.0.32', '>='))) {
$useSingleTransaction = false;
}
After the commit if mysql version is newer than 5.7.41 or 8.0.32 results in TRUE
$useSingleTransaction =
($isMySQL5 && version_compare($serverVersion, '5.7.41', '>=')) ||
($isMySQL8 && version_compare($serverVersion, '8.0.32', '>='));
Why do we even check MySQL versions?
This check is done because after those versions, mysqldump will require the FLUSH TABLES privileges to run, which shared hosting usually will not give, as it is considered system privileges. Thus breaking any use of mysqldump. (See https://github.com/craftcms/cms/issues/12557)
Here at fortrabbit hosting we try to work around this by setting our defult mysqldump.conf to always disable the GTID option, which also removes the need for FLUSH TABLES privileges on mysqldump versions 5.7.42/8.0.33 or later. (See https://bugs.mysql.com/bug.php?id=109685)
But Craft CMS overrides the default config file with their own config, so this workaround is disabled.
Steps to reproduce
- Have a craft install that runs on shared hosting, like fortrabbit
- Upgrade to or install to Craft CMS 4.9.0 or 5.1.0
- Open control panel, go to Utilities -> Database Backup
- Click backup, and you will get this error in the logs
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) at /srv/app/htdocs/vendor/craftcms/cms/src/controllers/UtilitiesController.php:226
Expected behavior
Backup is generated without issue.
Actual behavior
Backup fails to be generated, due to mysqldump being called with --single-transaction and --set-gtid-purged defaults to AUTO which fails, because superuser FLUSH TABLES privilege is needed.
Craft CMS version
4.9.0
PHP version
8.3
Operating system and version
No response
Database type and version
MySQL 8.0.35
Image driver and version
No response
Installed plugins and versions
No response
