`wordpress:cli` is shipped with mysql client that is incompatible with mysql server 8.0+

[Saggre]

WP images ship with a mysql client that is assembled from MariaDB sources and doesn't have support for caching_sha2_password.

$ mysql -V
mysql  Ver 15.1 Distrib 10.11.8-MariaDB, for Linux (x86_64) using readline 5.1

This is because MariaDB Server does not currently support caching_sha2_password as documented here.

When using mysql 8.0+ as the server, and running wp db cli for example, we get this error:

ERROR 1045 (28000): 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

This is because mysql 8.0+ uses caching_sha2_password as it's default authentication plugin as documented here: In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password.

I found PR #902 regarding the issue. Installing that package likely fixes the issue because it provides the caching_sha256_password plugin, which is compatible with MySQL's caching_sha2_password as documented here.

@tianon I am unsure if that would actually be a robust fix, but here's the issue explained more in detail.

[tianon]

This is an unfortunate constraint of our base distribution -- I don't believe they have MySQL's libmysql available :thinking:

[dicoeenvoud]

I would like to bump this issue. Currently wp db check does not work with any supported version of mysql. wordpress:cli did not support any version of mysql with support even when this issue was made a year ago, or when https://github.com/docker-library/wordpress/issues/862 was reported and its MR closed in 2023. This is the perpetual error you'll get:

mysqlcheck: 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

[tianon]

Oh, great find, yeah, this is definitely a duplicate of #862; I'll copy my question from the PR that was attempting to fix that, because I think it's still relevant: 😅

Hmm, do we have some documentation somewhere that points to this as the "correct" fix? It seems like kind of a random package name that isn't even very well connected to the problem it solves. 😞

Also, I think if we're going to explicitly install this mariadb package, we should probably switch from the mysql-client metapackage to a mariadb explicit one.