[3.x]: Database backup after composer update not working with SSL

[carolineboeykens]

What happened?

Description

Our server requires an SSL connection on our database. We added the following code to the config/db.php

if(getenv('ENVIRONMENT') != 'dev') { $params['attributes']= [ PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/ca-bundle.crt' ]; }

Now the database backup on our production server won't work:

mysqldump: Got error: 3159: “Connections using insecure transport are prohibited while --require_secure_transport=ON.” when trying to connect

Steps to reproduce

1. Enable 'backupOnUpdate' => true in general.php
2. Add PDO::MYSQL_ATTR_SSL_CA to db.php
3. ./craft update --> so that the database backup will be triggered after the updates

Expected behavior

Does the database backup not use the parameters from db.php?

Craft CMS version

3.7.40

PHP version

7.4

Operating system and version

CentOS Linux

Database type and version

MySQL 8.0.26

Image driver and version

Imagick 3.7.0 (ImageMagick 6.9.12-55)

Installed plugins and versions

[angrybrad]

Was this working before an update and broke after or has it never worked for you? If the prior, do you recall the version you were on before?

[carolineboeykens]

It was working before we had to add the SSL variable in the db.php. We recently moved our database to an environment where this is required

[erickertzvfc]

Any updates on this? I also migrated to an env that requires SSL and DB backups no longer work using mysqldump.

failed with exit code 2: mysqldump: Got error: 9002: "SSL connection is required. Please specify SSL options and retry." when trying to connect {"trace":["#0 [internal function]: \\controllers\\UtilitiesController->actionDbBackupPerformAction()",

[erickertzvfc]

this should fix the problem:

https://github.com/craftcms/cms/issues/10351#issuecomment-1172898652

[brandonkelly]

@carolineboeykens Does that help?

[carolineboeykens]

@brandonkelly , This is not working for us because we're not using Alpine or Azure.

We got this error message:

error: The shell command "mysqldump --defaults-extra-file="/tmp/worqcqklduxq.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="/www/***--2022-08-09-090401--v3.7.50.sql" *** && mysqldump --defaults-extra-file="/tmp/worqcqklduxq.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=***.assetindexdata --ignore-table=***.assettransformindex --ignore-table=***.resourcepaths --ignore-table=***.sessions --ignore-table=***.templatecaches --ignore-table=***.templatecachequeries --ignore-table=***.templatecacheelements --ignore-table=***.cache --ignore-table=***.templatecachecriteria *** >> "/www/***--2022-08-09-090401--v3.7.50.sql"" failed with exit code 2: mysqldump: Got error: 3159: "Connections using insecure transport are prohibited while --require_secure_transport=ON." when trying to connect

Is it because the code is not checking the attributes from the db.php in the command?

<?php

$params = [
    'dsn' => getenv('DB_DSN'),
    'user' => getenv('DB_USER'),
    'password' => getenv('DB_PASSWORD'),
    'schema' => getenv('DB_SCHEMA'),
    'tablePrefix' => getenv('DB_TABLE_PREFIX'),
];

 if(getenv('ENVIRONMENT') != 'dev') {
     $params['attributes']= [
         PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/ca-bundle.crt'
     ];
 }
 return $params;

[angrybrad]

@carolineboeykens Can you take that generated command and add the --ssl-ca flag to it and try to execute it (obviously swapping the *** back with their original values).

Something like:

mysqldump --ssl-ca="/etc/ssl/certs/ca-bundle.crt" --defaults-extra-file="/tmp/worqcqklduxq.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="/www/***--2022-08-09-090401--v3.7.50.sql" *** && mysqldump --defaults-extra-file="/tmp/worqcqklduxq.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=***.assetindexdata --ignore-table=***.assettransformindex --ignore-table=***.resourcepaths --ignore-table=***.sessions --ignore-table=***.templatecaches --ignore-table=***.templatecachequeries --ignore-table=***.templatecacheelements --ignore-table=***.cache --ignore-table=***.templatecachecriteria *** >> "/www/***--2022-08-09-090401--v3.7.50.sql"

There are other SSL command line flags, but I'm guessing that's the one you want, based on your db.php file.

Also worth mentioning is that by using https://craftcms.com/docs/4.x/config/general.html#backupcommand, you can completely override Craft's default backup command with whatever you need.

[carolineboeykens]

Hi @angrybrad , Thank you for the suggestion. When we try this we get an error that the extra file does not exist: --defaults-extra-file="/tmp/worqcqklduxq.cnf"

When we leave this part out, we get an error from the mysql socket: mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" when trying to connect

Is this related to this issue or is this a problem with the server? The website and the connection with the database are working fine, justs not with this command.

[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.