mysql client >= 8.0 does not support --disable-ssl-verify-server-cert option

[ceesgeene]

Describe the bug

Starting with MariaDB 11.4, the mariadb client by defaults verifies the server certificate. See https://mariadb.com/docs/server/security/securing-mariadb/encryption/data-in-transit-encryption/securing-connections-for-client-and-server and https://mariadb.org/mission-impossible-zero-configuration-ssl/

So, when you use the mariab client >= 11.4 (default on alpine images, where mysql client is symlinked tomariab client) for a mysql (or older mariadb) server with TLS disabled, you also have to instruct this client to skip verifying the server certificate.

In #6355 a change was committed to \Drush\Sql\SqlMysql::creds() so that if a Drupal database connection configuration contains the PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT flag, that the disable-ssl-verify-server-cert option is added to the client arguments.

However, the disable-ssl-verify-server-cert option only exists for mariadb client and mysql <= 5.7. In the mysql client >= 8.0, the option is replaced by a new --ssl-mode option. Note that also options like --ssl, --disable-ssl and --skip-ssl are no longer available in mysql client >= 8.0.

So currently, when using the PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT flag, you cannot use the mysql client >= 8.0 in combination with drush.

This is especially a problem when you are using mysql servers with TLS disabled, but are mixing clients:

• in acceptance/staging/production the mariadb client (mysql symlinked to mariab), because the docker/OCI images are alpine based)
• in development the mysql client, because you use ddev with a mysql server.

To Reproduce

Add the MYSQL_ATTR_SSL_VERIFY_SERVER_CERT flag

$databases['default']['default'] = [
  'driver'    => 'mysql',
  'database'  => getenv('DB_NAME'),
  ...
  'pdo' => [
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => 'false', // Note that this actually should be a boolean FALSE, but then the change from https://github.com/drush-ops/drush/issues/6355 does not work at all.
  ],
];

Use mysql in ddev config.yaml:

database:
    type: mysql
    version: "8.4"

Execute a drush command that uses the mysql client, e.g.:

ddev drush sql:sql

Expected behavior
The disable-ssl-verify-server-cert option is only added when using the mariadb client or the mysql client <= 5.7.

Actual behavior
The disable-ssl-verify-server-cert option is added while using the mysql client >= 8.0.

The command "mysql --defaults-file=/tmp/drush_yHFaLH --database=db --host=db --disable-ssl-verify-server-cert= -A" failed.  
                                                                                                                              
  Exit Code: 7(Unknown error) 

Workaround
Before adding the option, first check which client is actually used.

Maybe add a drush configuration option to explicitly configure the client to be used?

System Configuration

Q	A
Drush version?	>= 13.x
Drupal version?	11.x/10.x/9.x/8.x/7.x
PHP version	8.x/7.x
OS?	Mac/Linux/Windows

[ceesgeene]

Related:

• Resolves #6355: Verify ssl peer for command line should adhere to server config #6356
• "TLS/SSL error: SSL is required, but the server does not support it" on mariadb 10.6 ddev/ddev#8119
• fix: disable SSL for MySQL/MariaDB client in web container, fixes #8119 ddev/ddev#8121