[4.x]: MySQL SSL config not working for console/shell commands such as craft up

JLS-Envision commented 8 months ago

What happened?

Description

Craft is configured to use the Azure MySQL SSL certificate as follows:

$config['attributes']= [
    PDO::MYSQL_ATTR_SSL_CA => '/app/mysql/DigiCertGlobalRootCA.crt.pem'
];

This works fine for the website itself and it runs without issues. When I SSH in and try to run shell commands such as "craft up" I get errors such as the following:

Exception 'craft\errors\DbConnectException' with message 'Craft CMS can’t connect to the database.'

in /var/www/html/src/vendor/craftcms/cms/src/db/Connection.php:180

Caused by: Exception 'yii\db\Exception' with message 'SQLSTATE[HY000] [3159] Connections using insecure transport are prohibited while --require_secure_transport=ON.'

in /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php:648

Error Info:
Array
(
    [0] => HY000
    [1] => 3159
    [2] => Connections using insecure transport are prohibited while --require_secure_transport=ON.
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[HY000] [3159] Connections using insecure transport are prohibited while --require_secure_transport=ON.'

in /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php:722

I know there is nothing wrong with the path to the certificate because if I change it to an invalid path the "Caused by" becomes:

Caused by: Exception 'PDOException' with message 'failed loading cafile stream: `/var/www/html/src/mysq/DigiCertGlobalRootCA.crt.pem''

I have searched for for similar issues and this one sounds like it could be related, though kind of old: https://github.com/craftcms/cms/issues/11753

Here is the rest of the stack trace in case it helps somehow:

Stack trace:
#0 /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php(722): PDO->__construct('mysql:host=etad...', 'craftazurepoc', 'gSsA7kPjm2BDnbq...', Array)
#1 /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php(637): yii\db\Connection->createPdoInstance()
#2 /var/www/html/src/vendor/craftcms/cms/src/db/Connection.php(159): yii\db\Connection->open()
#3 /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php(1067): craft\db\Connection->open()
#4 /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php(1054): yii\db\Connection->getMasterPdo()
#5 /var/www/html/src/vendor/yiisoft/yii2/db/Schema.php(461): yii\db\Connection->getSlavePdo(true)
#6 /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php(946): yii\db\Schema->quoteValue('7e98035518a7366...')
#7 /var/www/html/src/vendor/yiisoft/yii2/db/Command.php(211): yii\db\Connection->quoteValue('7e98035518a7366...')
#8 /var/www/html/src/vendor/craftcms/cms/src/db/Command.php(282): yii\db\Command->getRawSql()
#9 /var/www/html/src/vendor/yiisoft/yii2/db/Command.php(1147): craft\db\Command->logQuery('yii\\db\\Command:...')
#10 /var/www/html/src/vendor/yiisoft/yii2/db/Command.php(436): yii\db\Command->queryInternal('fetchColumn', 0)
#11 /var/www/html/src/vendor/yiisoft/yii2/mutex/MysqlMutex.php(79): yii\db\Command->queryScalar()
#12 [internal function]: yii\mutex\MysqlMutex->yii\mutex\{closure}(Object(craft\db\Connection))
#13 /var/www/html/src/vendor/yiisoft/yii2/db/Connection.php(1130): call_user_func(Object(Closure), Object(craft\db\Connection))
#14 /var/www/html/src/vendor/yiisoft/yii2/mutex/MysqlMutex.php(80): yii\db\Connection->useMaster(Object(Closure))
#15 /var/www/html/src/vendor/yiisoft/yii2/mutex/Mutex.php(72): yii\mutex\MysqlMutex->acquireLock('isolated-comman...', 0)
#16 /var/www/html/src/vendor/craftcms/cms/src/mutex/Mutex.php(67): yii\mutex\Mutex->acquire('isolated-comman...', 0)
#17 /var/www/html/src/vendor/yiisoft/yii2/mutex/Mutex.php(72): craft\mutex\Mutex->acquireLock('isolated-comman...', 0)
#18 /var/www/html/src/vendor/craftcms/cms/src/mutex/MutexTrait.php(70): yii\mutex\Mutex->acquire('isolated-comman...', 0)
#19 /var/www/html/src/vendor/craftcms/cms/src/console/ControllerTrait.php(123): craft\mutex\Mutex->acquire('isolated-comman...')
#20 /var/www/html/src/vendor/yiisoft/yii2/base/Controller.php(176): craft\console\Controller->beforeAction(Object(yii\base\InlineAction))
#21 /var/www/html/src/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('', Array)
#22 /var/www/html/src/vendor/craftcms/cms/src/console/ControllerTrait.php(90): yii\console\Controller->runAction('', Array)
#23 /var/www/html/src/vendor/craftcms/cms/src/console/Controller.php(216): craft\console\Controller->traitRunAction('', Array)
#24 /var/www/html/src/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('', Array)
#25 /var/www/html/src/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('up', Array)
#26 /var/www/html/src/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('up', Array)
#27 /var/www/html/src/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('up', Array)
#28 /var/www/html/src/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest(Object(craft\console\Request))
#29 /var/www/html/src/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#30 /var/www/html/src/craft(13): yii\base\Application->run()
#31 {main}

Craft CMS version

4.7.3

PHP version

8.1

Operating system and version

Linux Alpine or Ubuntu

Database type and version

MySQL 8

Image driver and version

No response

Installed plugins and versions

-

angrybrad commented 8 months ago

Curious.

Is there any chance we could get some SSH access to that box to do some investigating? If so, could you shoot that over to support@craftcms.com and reference this GH issue?

angrybrad commented 8 months ago

Instead of setting custom PDO attributes on the primary database connection by overriding config/app.php, the place to do it should be config/db.php.

e.g. this will work for both web and console requests that require an SSL connection to the database in config/db.php

<?php

return \craft\config\DbConfig::create()
    ->pdoAttributes([ PDO::MYSQL_ATTR_SSL_CA => '/app/mysql/DigiCertGlobalRootCA.crt.pem']);

I've just pushed an update to the docs to help clarify this scenario as well.

craftcms / cms