Proxysql with Mysql8 support

Drupal uses the value of \PDO::ATTR_SERVER_VERSION for the MySQL server version. Like $version_server = $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION); in the file core/lib/Drupal/Core/Database/Driver/mysql/Connection.php. My guess is that value is not the same as show VARIABLES like "version";. We have the same problem with AWS databases for MySQL. There is an issue for that. I cannot find it at the moment.

#3089902: "Azure Database for MySQL server" reports wrong database version

I think the solution would be the same as Azure also uses a proxy and this can probably be marked as duplicate against that issue.

Thanks for getting back to us with the solution, this might help others who use the same proxy.

I want to come back on this topic.
We use ProxySQL in front of a cluster of different Mysql servers. ProxySQL is advertising the lowest version number of its backends.
This makes the check fail as Drupal is incorrectly thinking our Mysql server has version 5.6, which is no longer supported in Drupal 9.
I believe the only correct way of obtaining the mysql version is by running
show variables like "version";
or
select version();

@christophego: The problem with show variables like "version"; and select version(); is that it will result in an extra query to the database. What we can do to solve it is to create 2 special database drivers for ProxySQL. One for MySQL 8 servers and on for MySQL 5.7.

So you mean this check is not only performed during installation?
What is the purpose of checking the mysql version on every new connection?

A similar problem is handled in Laravel:
https://github.com/laravel/framework/issues/32681

One user explained the problem as follows:

As it seems a server or proxy can arbitrarily modify its exposed server version upon handshaking. So I don't think version compatibility conditionals should ever rely on it in a non-configurable way

This is exactly what is done in Drupal...

In Laravel, a change is made to overwrite the server version:
https://github.com/laravel/framework/pull/32708

Moreover, this is the same problem:
https://www.drupal.org/project/drupal/issues/3089902

@daffie asked for this issue to be re-opened.

In the method Drupal\Core\Database\Driver\mysql::open() we have the following code:

    $sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY';
    // NO_AUTO_CREATE_USER is removed in MySQL 8.0.11
    // https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-deprecation-removal
    $version_server = $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION);
    if (version_compare($version_server, '8.0.11', '<')) {
      $sql_mode .= ',NO_AUTO_CREATE_USER';
    }
    $connection_options['init_commands'] += [
      'sql_mode' => "SET sql_mode = '$sql_mode'",
    ];

After Drupal drops support for MySQL 5.7 the problem will be fixed.

And yes we have the same problem with the Azure database.

What I don't understand:
This whole logic is created just to avoid a problem with creating a user/granting privileges which is probably a less-common use-case.
Wouldn't it be better to fix the problem where it occurs and check if a user exists before granting permissions.
That way you can simply remove this logic make it cleaner/faster for everyone.

Added link to the issue where the from comment #13 was added.

In #3089902: "Azure Database for MySQL server" reports wrong database version it was suggested that we allow the server version to be overridden in the database settings array.

As a workaround for this particular issue, we could also add if (!isset($connection_options['init_commands']['sql_mode'])) around this block of code, so if advanced users want to explicitly set sql_mode themselves, they bypass the version check? This isn't a behaviour change, as sql_mode won't be overridden if already set.

Found the issue where "NO_AUTO_CREATE_USER" was added to Drupal.

#3089902: "Azure Database for MySQL server" reports wrong database version has landed in 9.2.x. I suspect that that issue fixes this.

Based on #18 and no recent comments I’ll close this.