Add an option to disable SSL verification when connecting to MySQL during DB backups

[uandco]

Hi,

I noticed that the latest local image of MySQL I'm using enforces SSL. This causes the local DB backup to fail during updates:

2025-04-22 10:23:12 [web.ERROR] [craft\controllers\UpdaterController::actionBackup] Error backing up the database: The shell command "mysqldump '--defaults-file'='/tmp/ndjnkbadzurp.cnf' '--add-drop-table' '--comments' '--create-options' '--dump-date' '--no-autocommit' '--routines' '--default-character-set'='utf8' '--set-charset' '--triggers' '--no-tablespaces' '--no-data' '--skip-triggers' '--result-file'='/app/storage/backups/xxx--2025-04-22-002311--v4.14.15.sql' 'lagoon' && mysqldump '--defaults-file'='/tmp/ndjnkbadzurp.cnf' '--add-drop-table' '--comments' '--create-options' '--dump-date' '--no-autocommit' '--routines' '--default-character-set'='utf8' '--set-charset' '--triggers' '--no-tablespaces' '--no-create-info' '--ignore-table'='lagoon.assetindexdata' '--ignore-table'='lagoon.cache' '--ignore-table'='lagoon.imagetransformindex' '--ignore-table'='lagoon.resourcepaths' '--ignore-table'='lagoon.phpsessions' '--ignore-table'='lagoon.sessions' 'lagoon' >> "/app/storage/backups/xxx--2025-04-22-002311--v4.14.15.sql"" failed with exit code 2: mysqldump: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb-dump' instead
mysqldump: Got error: 2026: "TLS/SSL error: self-signed certificate in certificate chain" when trying to connect {"memory":2896048} 

All I would need is craft\db\mysql\Schema::_createDumpConfigFile to an extra ssl=0 to the config. I only need this to be added when running local to, not in my online envs as they would have receive a valid MySQL SSL cert.

I thought about using the backupCommand config option but that seems overkill as the command is basically the same, Only the defaults need to change.

A way to achieve this would be to add a check for PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT and write the config accordingly, something along these lines:

        if (isset($this->db->attributes[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT])) {
            $contents .= PHP_EOL . 'ssl-verify-server-cert=' . (int)$this->db->attributes[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT];
        }

With the config having:

    'attributes' => [
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => (App::env('CRAFT_ENVIRONMENT') === 'DEV' ? false : true)
    ],    

Btw, the deprecation warning should probably be taken into account and the default command be switched to /usr/bin/mariadb-dump

I hope that makes sense,
Thanks.

[brandonkelly]

A way to achieve this would be to add a check for PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT and write the config accordingly, something along these lines:

        if (isset($this->db->attributes[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT])) {
            $contents .= PHP_EOL . 'ssl-verify-server-cert=' . (int)$this->db->attributes[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT];
        }

I’m not finding any mention of my.cnf supporting an ssl-verify-server-cert setting. Where are you seeing that?

[uandco]

My bad, I must be using an old 5.x client package, the option is now deprecated in 8.x and replaced with ssl-mode: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/connection-options.html#option_general_ssl-verify-server-cert

[uandco]

Ok so this is a bit confusing. It seems like the latest Alpine Linux mysql-client package is still using MariaDB, which has the "old" MySQL 5.7 SSL options.
mysqldump from 11.4.5-MariaDB, client 10.19 for Linux (aarch64)

$ mysqldump --help | grep "ssl"
mysqldump: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb-dump' instead
  --ssl               Enable SSL for connection (automatically enabled with
                      (Defaults to on; use --skip-ssl to disable.)
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-crl=name      Certificate revocation list (implies --ssl).
  --ssl-crlpath=name  Certificate revocation list path (implies --ssl).
  --ssl-fp=name       Server certificate fingerprint (implies --ssl).
  --ssl-fplist=name   File with accepted server certificate fingerprints, one
                      per line (implies --ssl).
  --ssl-verify-server-cert 
                      (Defaults to on; use --skip-ssl-verify-server-cert to disable.)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
ssl-fp                            (No default value)
ssl-fplist                        (No default value)
ssl-verify-server-cert            TRUE

[angrybrad]

@uandco generally speaking, mixing MariaDB/MySQL client and server engines and versions will cause headaches. If you're using "real" MySQL (which we recommend over MariaDB), it's best to use "real" MySQL client tools. Granted a lot of distros don't make this easy.

[uandco]

Sure is, but, indeed, it doesn't seem like Alpine Linux is offering a real MySQL client package, as stated in https://wiki.alpinelinux.org/wiki/MySQL

In the meantime, I solved it by copying a .my.cnf file having ssl-verify-server-cert=FALSE in my docker file, only for my local env.

This is obviously the best way, but might not be easy to implement for less container/devops savvy developers, hence why I reckon having Craft CMS set the ssl-mode based on the PDO attribute would still be worth exploring, knowing that would only be "real" MySQL compatible.

[brandonkelly]

Glad you got it sorted! Going to go ahead and close this, as this really does feel like it should just be solved at the MySQL config level.

This is obviously the best way, but might not be easy to implement for less container/devops savvy developers

Agree, but we strongly recommend people use DDEV for local development (savvy or not) and it’s not an issue there.

[uandco]

@brandonkelly sorry to revive this but I figured the .my.cnf solution doesn't work because the the default mysqldump command uses the --defaults-file option, which overwrites any other configuration. The only option would be to use .mylogin.cnf, but this can only set login options, not ssl related ones. The only way to have .my.cnf set the SSL option would be to use mysqldump --defaults-extra-file=... instead of mysqldump --defaults-file=.... This way, the auto generated config file would be read on top of the options defined in .my.cnf. Do you think you could change that in the default mysqldump command?