Forwarded to devs Caching_sha2_password when connecting to remote MySQL 8 server(s)

[Kaspar]

Username: Rasp

TITLE

Caching_sha2_password when connecting to remote MySQL 8 server(s)

PRODUCT, VERSION, OPERATING SYSTEM, ARCHITECTURE

CentOS Linux 7.9.2009, Plesk version 18.0.33

PROBLEM DESCRIPTION

As of Plesk version 18.0.33 MySQL 8 is supported.

Some cloud providers, such as DigitalOcean, offer managed database instances using MySQL 8 with the default authentication type caching_sha2_password. As these are managed databases instances it is not possible to change any database configuration, such as for example the authentication type.

Manually converting the administrator password of such a remote database to mysql_native_password luckily still allows you to add the remote MySQL database to Plesk. However any other database user created by Plesk still use the caching_sha2_password authentication type (as this is the default authentication type). Creating authentication issues with PHPmyAdmin and the Wordpress toolkit.

STEPS TO REPRODUCE

Prerequisite: have a MySQL 8 database server with the caching_sha2_password authentication type enabled. (Could be a remote database or for testing purposes a local database would be just as fine).

1) Create an administrator database user with a mysql_native_password type.
2) Add the (remote) MySQL8 database server to Plesk
3) Create a subscription in Plesk
4) Create database in subscription
5a) Try to view database with PHPmyAdmin (trough Plesk)
or
5b) Install Wordpress on subscription using the WP toolkit

ACTUAL RESULT

Creates errors in PHPmyAdmin:

mysqli::real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli::real_connect(): (HY000/2054): The server requested authentication method unknown to the client

EXPECTED RESULT

Would be nice to have some sort of mechanism/function in Plesk which recognizes that the database server uses caching_sha2_password and thus creates every database users using the mysql_native_password.

ANY ADDITIONAL INFORMATION



YOUR EXPECTATIONS FROM PLESK SERVICE TEAM

Confirm bug

 

[IgorG]

@Rasp thank you!

Caching_sha2_password supported by PHP >= 7.4.
So, it won't work until sw-engine will not be switched to PHP >= 7.4 (at least on Linux we now using 7.3).
We are going to switch sw-engine to php 7.4 (PPP-47405) in some future, but for now, I have no ETA for it.

 

[cool_sh]

Just wondering if there is an update on this?

Regarding to the changelog for Plesk Obsidian 18.0.38,

• Updated sw-engine PHP to version 7.4.22.

But I still get the named error, when trying to connect to the database with MySQL 8.0.25 on CentOs 7.9


ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory < Is this a mysql-problem as the folder is not for php?

 

[cool_sh]

Error still remaining with Plesk Obsidian 18.0.40 Update 1

 

[Kaspar]

The issue described in the bug report has been solved with Plesk version 18.0.39 when the sw-engine started using PHP 7.4.

@cool_sh from the error you're getting is looks like caching_sha2_password module/plugin isn't installed. As MySQL 8 isn't native to CentOS 7.9 the module/plugin probably didn't got installed when you installed MySQL 8. Perhaps this link will help you out: ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password'