mysqldump: Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so: No such file or directory

[jamesmacwhite]

Description

When running the backup database option in Craft CMS through Utilities mysqdump is throwing an exit code. The full stack trace is below.

I know more recently mysql-client tools where added to the dev images, as before mysqdump wasn't included at all. The path within the php-fpm Docker container at /usr/lib/mariadb/plugin/ is indeed empty, although I'm using MySQL 8 not mariadb so I'm not sure why it's being referenced. I'm wondering if the client tools have been built against mariadb?

yii\base\Exception: Could not create backup: The shell command "mysqldump --defaults-extra-file="/tmp/my.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="/app/storage/backups/nottingham-college--2021-02-19-112326--v3.6.6.sql" dev_craftcms && mysqldump --defaults-extra-file="/tmp/my.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=dev_craftcms.craft_assetindexdata --ignore-table=dev_craftcms.craft_assettransformindex --ignore-table=dev_craftcms.craft_sessions --ignore-table=dev_craftcms.craft_templatecaches --ignore-table=dev_craftcms.craft_templatecachequeries --ignore-table=dev_craftcms.craft_templatecacheelements --ignore-table=dev_craftcms.craft_cache --ignore-table=dev_craftcms.craft_templatecachecriteria dev_craftcms >> "/app/storage/backups/nottingham-college--2021-02-19-112326--v3.6.6.sql"" failed with exit code 2: mysqldump: Got error: 1045: "Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so: No such file or directory" when trying to connect in /app/vendor/craftcms/cms/src/controllers/UtilitiesController.php:345
Stack trace:
#0 [internal function]: craft\controllers\UtilitiesController->actionDbBackupPerformAction()
#1 /app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#2 /app/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array)
#3 /app/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('db-backup-perfo...', Array)
#4 /app/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('db-backup-perfo...', Array)
#5 /app/vendor/craftcms/cms/src/web/Application.php(274): yii\base\Module->runAction('utilities/db-ba...', Array)
#6 /app/vendor/craftcms/cms/src/web/Application.php(577): craft\web\Application->runAction('utilities/db-ba...', Array)
#7 /app/vendor/craftcms/cms/src/web/Application.php(253): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#8 /app/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request))
#9 /app/web/index.php(23): yii\base\Application->run()
#10 {main}

Steps to reproduce

1. Use any dev PHP image
2. Try running a database backup from Utilities in Craft CMS CP
3. You will get an error saying to check the logs for more info, which the stack trace is the reason of the export failure.

Additional info

• Craft version: 3.6.6
• PHP version: 7.4 (dev image)
• Database driver & version: MySQL 8

[jamesmacwhite]

I'm aware that you can switch to native password, but this wouldn't seem to be the right thing given a library is missing entirely and Craft 3 should of course be able to support the caching_sha2_password mechanism with the PHP 7.4 dev image.

Is there perhaps a missing package that contains the libraries in addition to mysql-client?

[jasonmccallister]

Hi @jamesmacwhite, are you using the -dev images or the non-dev? The -dev images install mysql-client tools because they are using for the Nitro sites containers... The non-dev images do not install composer, git, or the database tools.

In the usage section we document how to add your own extensions and backup tools. These are from the Alpine Linux repository which appear to be mariadb.

[jamesmacwhite]

Hi @jasonmccallister. Yep using the dev images PHP 7.4 dev to be exact. I noticed these were added to the images more recently, which is nice I had modified our Dockerfile to add git, and mysql-client manually but since pulled the latest PHP FPM image to negate doing that now.

It does indeed appear to be that the Alpine packages are built against mariadb. I'm trying to get some further understanding on why the library file is missing entirely. I was wondering if there's a further dependency package needed or something, although even in Docker related searches I'm not getting a clear picture why the lib file isn't present. A lot of the guidance is switch to native password, but that's not the fix, because caching_sha2_password should be handled fine with MySQL 8 and Craft 3, it appears it's just the library itself is just not there. Perhaps not a bug in these Docker images specifically but something with Alpine?

The whole folder plugin folder being empty is a bit strange.

[jamesmacwhite]

@jasonmccallister Found the dependency it's mariadb-connector-c, this package adds the plugin that's missing. I can add this to my own Dockerfile, or if you are willing to include in the dev Craft Docker images potentially? This seems like a potentially broken mysqldump situation with MySQL 8 environments out of the box.

The whole caching_sha2_password situation has been a right mess. MariaDB themselves even noped out on it: https://mariadb.com/kb/en/authentication-plugin-sha-256/

However, the fix is having mariadb-connector-c package from Alpine installed.

[jamesmacwhite]

By default the mysql-client package does not have support for caching_sha2_password because it is built against mariadb, so the addition of this package is needed for supporting modern connections to MySQL 8, where native password is not used or has been enabled (Any newly provisioned MySQL 8 DB is unlikely to have it enabled). The MySQL 8 container itself of course supports this without any problems, but mysqldump will be running from the PHP-FPM container which is where this problem is coming from.

Certainly on the dev images it would be nice to have the mariadb-connector-c package installed. Anyone who's using these docker images with MySQL 8, who hasn't allowed their database to use the legacy native password method is going to hit this, possibly in Nitro as well?

[kylecotter]

@jamesmacwhite I've experienced this for a while (not specific to the Craft Docker image) and couldn't for the life of me figure out which extra dependency I needed to add. Thanks for figuring it out! 🎉

Can confirm adding that to our Dockerfile allows Craft's backup commands to work OOTB with MySQL8.

[jamesmacwhite]

@kylecotter Glad it helped! Hoping @jasonmccallister will implement this dependency directly in the dev images. After discovering the MySQL client tools package is built against MariaDB, it doesn't have support for caching_sha2_paasword by default and likely never will given MariaDB doesn't support it out of the box either.

I guess only MySQL 8 specifically is hit when the default password mechanism changed. 5.7 and lower will not encounter it, MariaDB won't either as it doesn't use it.

[khalwat]

Thx @jamesmacwhite -- this fixed it for me as well.

@jasonmccallister you'll likely want to add mariadb-connector-c to the base images; a bet a fair number of people will be picking mysql:8.0 as their db of choice, since that's the example in the docs.

[jamesmacwhite]

@khalwat Thanks! I have submitted a PR with the package added to the PHP 7.4 and 8.0 dev images. Despite the PHP docs, it seems 7.4 is the first PHP version to have full support.

[jasonmccallister]

Changes by @jamesmacwhite (thank you!) have been merged, I will tag a release in the next couple of days to trigger a new build.

[kylecotter]

@jamesmacwhite @khalwat either of you ever have the issue with the dump from this setup not actually be importable elsewhere?