ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

[Raj]

I am working with the client-serverm, which is powered by OpenSUSE 15.2 server with PHP version 7.4.6. I got this weird error when I ran mysql command from shell:

mysql --ssl-ca=/app/.private/mysql-server.pem -u username -h 1.2.3.4 -p password db_name

My error code:

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

PHP error code:

mysqli_real_connect(): (HY000/2006): MySQL server has gone away

How can I fix this error?

[Raj]

I got some help from sysadmin and DBA. I am sharing solution here. The problem is OpenSUSE 15.2 PHP 7.4.6 version not supporting caching_sha2_password :

MySQL 8

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server’s default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

How to fix ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

For mysql client we need to install mysql-community-client from mysql.com as OpenSUSE comes with mariadb-client. Hence, it will not work with MySQL 8 server when using caching_sha2_password plugin.

How to fix PHP 7.4.6 app

Ask your DBA or if you have root access to MySQL 8 database server to change auth plugin from caching_sha2_password to mysql_native_password. Here is how we did it:

mysql -u root -h mysql-8-server-ip -p 

List users and plugin using SQL statement:

SELECT user,plugin from mysql.user;

Now change plugin using ALTER USER SQL command:

ALTER USER 'phpapp-user-name' IDENTIFIED WITH mysql_native_password BY 'php-app-password';

Exit from MySQL 8 server:

quit

Back to your PHP app and test it:

https://url/myapp/

And it worked for us. I am sharing solution as this forum and nixcraft helped me daily. Just my small contribution.

[nixcraft]

FYI, I looked into my CentOS 8 dev server, and apparently, it worked with caching_sha2_password MySQL 8 server. However, I got PHP version 7.4.12:

php -v

So I have:

PHP 7.4.12 (cli) (built: Oct 27 2020 15:01:52) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.12, Copyright (c), by Zend Technologies

You need auth_plugin_sha256_password support in your PHP mysqlnd (from my phpinfo() output) :

You can also verify that from the Linux CLI itself:

 php -i | grep mysqlnd

And will see the following on CentOS 8 Linux server:

/etc/php.d/20-mysqlnd.ini,
Client API library version => mysqlnd 7.4.12
mysqlnd
mysqlnd => enabled
Version => mysqlnd 7.4.12
Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password
Client API version => mysqlnd 7.4.12

Here is what we see on OpenSUSE 15.2 Linux server:

Client API library version => mysqlnd 7.4.6
mysqlnd
mysqlnd => enabled
Version => mysqlnd 7.4.6
Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password
Client API version => mysqlnd 7.4.6

My best guess is OpenSUSE plugin has issues.

TL;DR

1. At least use PHP version 7.4.12.
2. PHP mysqlnd must have support for auth_plugin_caching_sha2_password plugin.
3. If everything failed, set MySQL 8 password auth type to mysql_native_password by running the following query as root user (mysql -u root -h hostname -p mysql):
   ALTER USER user@host IDENTIFIED WITH mysql_native_password BY 'password';
4. Always check PHP docs