[5.x] [4.x]: Database backup broken with 5.1.0 and 4.9.0

[erinbit]

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.
97da6ca#diff-83d39a4592b31403e3830a3100e22c596322d8dd9a240e07f6ecf173ec1d7bb7

This condition should be reversed again, to make mysqldump work, see pull request #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 #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

1. Have a craft install that runs on shared hosting, like fortrabbit
2. Upgrade to or install to Craft CMS 4.9.0 or 5.1.0
3. Open control panel, go to Utilities -> Database Backup
4. 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

[TheFunkyMonk]

Thanks for this quick fix!