Error "Backing up the database" with SSL config

[nickdunn]

Description

When I run a project config update, Craft backs up the database. This has been working fine, but I recently updated to 3.7.28 (from 3.7.23) and when I deploy I now see:

2022-01-11T16:23:30.529761721Z Backing up the database ... error: The shell command "mysqldump --defaults-extra-file="/tmp/amvmktvlfncx.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --default-character-set=utf8 --set-charset --triggers --no-tablespaces --single-transaction --no-data --result-file="/var/www/craft/storage/backups/uat--2022-01-11-162330--v3.7.28.sql" craftcms && mysqldump --defaults-extra-file="/tmp/amvmktvlfncx.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=craftcms.assetindexdata --ignore-table=craftcms.assettransformindex --ignore-table=craftcms.sessions --ignore-table=craftcms.templatecaches --ignore-table=craftcms.templatecachequeries --ignore-table=craftcms.templatecacheelements --ignore-table=craftcms.cache --ignore-table=craftcms.templatecachecriteria craftcms >> "/var/www/craft/storage/backups/uat--2022-01-11-162330--v3.7.28.sql"" failed with exit code 2: mysqldump: Got error: 9002: "SSL connection is required. Please specify SSL options and retry." when trying to connect

I'm hosted in Azure and we have SSL enabled on the database:

return [
    'driver' => 'mysql',
    'server' => App::env('DB_SERVER'),
    'port' => App::env('DB_PORT'),
    'database' => App::env('DB_DATABASE'),
    'user' => App::env('DB_USER'),
    'password' => App::env('DB_PASSWORD'),
    'password' => App::env('DB_PASSWORD'),
    'tablePrefix' => '',
    'attributes' => App::env('DB_SSL') === '1' ? [
        // https://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl
        PDO::MYSQL_ATTR_SSL_CA => '/var/www/craft/config/BaltimoreCyberTrustRoot.crt.pem',
        // https://cloud.google.com/sql/docs/mysql/samples/cloud-sql-mysql-pdo-timeout
        PDO::ATTR_TIMEOUT => 240,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ] : null,
];

Has anything changed recently in Craft that would break this?

Additional info

• Craft version: 3.7.28
• PHP version: 8.0

[nickdunn]

I'm going ahead and changing backupOnUpdate config setting to false to see if this lets me deploy... but nothing else has changed that I'm aware of so does feel like a Craft issue.

[angrybrad]

Nothing in Craft has changed around this in quite a while. I'm guessing something in the local certificate chain on that box has expired or is no longer valid and that's causing SSL not to work from PHP.

Take Craft out of the equation and try writing a simple PHP script that curls out using the same CA file.

[nickdunn]

Thanks Brad. Connections from Craft itself are fine and site is up, but it’s the mysqldump command during an install that seems to fail. My guess was there had been a change (maybe in Yii?) which is preventing the SSL config being read or passed into that command.

…

On 11 Jan 2022, at 17:24, Brad Bell ***@***.***> wrote:  Nothing in Craft has changed around this in quite a while. I'm guessing something in the local certificate chain on that box has expired or is no longer valid and that's causing SSL not to work from PHP. Take Craft out of the equation and try writing a simple PHP script that curls out using the same CA file. — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.

[angrybrad]

hrm... possibly, but I just glanced through the last few releases on https://github.com/yiisoft/yii2/blob/master/framework/CHANGELOG.md and nothing is jumping out at me.

Maybe try reaching out to Azure support? If it's a common occurrence, they've probably seen it before and might have some answers.

[okolvik-avento]

Craft 3 doesn't read the SSL settings at all.

cms/src/db/mysql/Schema.php

Line 151 in 43275a6

public function getDefaultBackupCommand(array $ignoreTables = null): string

[okolvik-avento]

Workaround for alpine (adjust paths for other distros).
I assume you use BaltimoreCyberTrustRoot since it's azure.
Create /etc/my.cnf.d/client-ssl.cnf

[client]
ssl-ca=/path/to/BaltimoreCyberTrustRoot.crt.pem

30.01.2023 - This method no longer works due to craft changing to using --defaults-file

[brandonkelly]

This is fixed now for the next Craft 3 and 4 releases, via #12596. Thanks @okolvik-avento!

[brandonkelly]

Craft 3.7.64 and 4.3.7 have been released with that fix.