Error 2059 hy000

Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.

When MySQL client connects to the server it uses one of the authentication plugins. On the server-side, plugins are located in the directory, specified by the option plugin-dir that defaults to
BASEDIR/lib/plugin  where
BASEDIR  is the base directory of your MySQL installation. This perfectly works whenever you install MySQL using package manager, or from the Linux tarball.

However, authentication plugins should be also loaded on the client-side. If you installed MySQL into the custom location, you may end up with an error when trying to connect using any plugin requiring a separate client library.

For example, let’s set the Test authentication plugin to authenticate a user:

Then, if you run the
mysql  client without option
—plugin—dir , you will get an error:

To avoid this error, specify
—plugin—dir either in the configuration file or as a command-line parameter:

This issue is applicable to all plugins that require loading an external library at the client-side, including the PAM authentication plugin. In this case, you will get the error
ERROR 2059 (HY000): Authentication plugin ‘dialog’ cannot be loaded: /usr/local/Percona—Server—8.0.25—15—Linux.x86_64.glibc2.17/lib/plugin/dialog.so: cannot open shared object file: No such file or directory

Oracle-compatible auth_pam_compat plugin does not experience this issue, because, instead of
dialog.so it uses the built-in
mysql_clear_password  plugin.

See also:

PAM plugin does not enforce plugin_dir when installed from tarball

MySQL and MariaDB authentication against pam_unix

When testing mysql 8.0 today, I found such a problem when connecting to mysql remotely

# mysql -h10.254.73.103
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
#

Let’s see what the official website says?

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password.

Well, if we understand the whole story of the problem, we can solve it

Download resource library: https://dev.mysql.com/downloads/

Choose according to your own needs

centos yum is selected here

Machine model: centos 7

download

# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

install

# yum install mysql80-community-release-el7-3.noarch.rpm

check client Library

# yum list all | grep mysql | grep client
mysql-community-client.i686       8.0.21-1.el7           mysql80-community
mysql-community-client.x86_64     8.0.21-1.el7           mysql80-community
#

Install client

# yum install mysql-community-client -y

test

# mysql -uroot -p123456 -h10.254.73.103
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye
#

Nice!

Posted by linux622
at Sep 02, 2020 — 12:22 AM
Tag:
CentOS
Oracle
MySQL
bash

Recently, I created an issue on the WP-CLI’s entity command repository to introduce a wp site generate command to allow for multisite installs to be easily populated for testing purposes.

I decided to set up a local WP-CLI sandbox on my machine (not within a virtual machine) so I can more easily and consistently contribute to the project. There is great documentation detailing how to contribute code to the project in the WP-CLI handbook. I ended up using the wp-cli-dev repo instead, which sets up every package for development.

After I had everything set up, I experienced database connection issues that took me quite a while to figure out. I hope that this will help save someone else time and frustration!

tl;dr (but you totally should read): MySQL 8.0 changed the preferred authentication plugin from mysql_native_password to caching_sha2_password, which PHP does not yet support. Running this command will fix the issue for the wp_cli_test user expected when running the WP-CLI Behat tests.

ALTER USER 'wp_cli_test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password1';

The Problem: More Details

Being on MacOS, I use Homebrew to install various packages on my machine. After installing mysql with Homebrew, creating the required wp_cli_test database and user, and granting that user the needed privileges for that database, I couldn’t get WP-CLI to connect (connecting via command line worked fine).

$ wp core install
Error: Error establishing a database connection. This either means that the username and password information in your `wp-config.php` file is incorrect or we can’t contact the database server at `localhost`. This could mean your host’s database server is down.

What made the situation even more weird was that certain database tasks appeared to complete successfully.

$ wp db check
Success: Database checked.
$ wp db optimize
Success: Database optimized.

I worked through the issue with a coworker and they were able to reproduce the issue. We dug deeper and discovered the following error:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

After researching, I found that there are a few other variations of this notice.

Authentication plugin 'caching_sha2_password' is not supported

Warning: mysqli_connect(): The server requested authentication
method unknown to the client [caching_sha2_password]

What’s Happening

In MySQL 8.0, the default authentication plugin was changed from mysql_native_password to caching_sha2_password (on the MySQL server). This is a problem when the client (PHP in this case) is not aware of this change or does not support the new plugin.

When wp core install is called, the WordPress Database API is used. This means that the call is initiated by PHP as the MySQL client. When wp db check is called, the result is a mysqlcheck call directly to the MySQL server, which is aware of and supports the new authentication plugin.

PHP does not seem to have support for the new caching_sha2_password plugin yet. That explains why PHP initiated requests to the database are failing.

The Solution

After some searching and trial and error, I found three solutions that worked.

First Solution

You can change the authentication plugin used on a per-user basis by running this MySQL command:

ALTER USER 'wp_cli_test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password1';

I chose this route because I was only looking to configure one user for running WP-CLI tests and I want to start using the new authentication plugin as soon as it’s supported by PHP. Future connection failures for new users will remind me to check up on this issue.

Second Solution

If you want to fix the issue for all new users, you can edit the server configuration file to use the previous default authentication plugin (mysql_native_password) by default. To do this, you need to add a line to the my.cnf located in /etc/. When testing this solution, I did not have a my.cnf file present. So, I had to create one.

cd /etc
sudo touch my.cnf

Add the following to that file:

default_authentication_plugin=mysql_native_password

Save and exit, and then restart the server.

mysql.server restart

Note: This change will only apply to new users. The authentication plugin for users created after installing or upgrading to MySQL >= 8.0 will remain unchanged. You will need to apply the first solution to each of these users.

Alternative Solution

There is a third option you could take that I do not really recommend. You could uninstall MySQL 8.x and install 5.7.x. Personally, I always try to work using the latest versions of packages and tools. If you want to take this approach, this is the process I found worked best (this is a combination of steps found in a few different locations).

Note: This will remove all MySQL related data. Make sure to back up any databases you need.

ps -ax | grep mysql
mysql.server stop
brew remove mysql
brew cleanup
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql*
sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
sudo rm /etc/my.cnf

Check your .bash_profile file for any MySQL related code and restart your computer.

brew doctor
brew update
brew install [email protected]
echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.bash_profile

Open a new terminal window.

mysql.server start

Notes

It’s worth mentioning that all of these should only be a temporary fix and not a long-term or permanent solution. caching_sha2_password was introduced to provide improved security and performance and it should be utilized when possible.

If you want to read more, a complete breakdown of this change can be found in the MySQL 8.0 Reference Guide, and some details about the issue in PHP can be found on this page. I have also opened a PR to add a notice about this on the Pull Requests page in the WP-CLI handbook.

При переносе базы с сервера MySQL 5.7 на сервер MySQL 8.0 возникла проблема подключения пользователей (созданных заново на сервере MySQL 8.0) к базе:
ERROR 2059 (HY000): Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
Прочитал, что нужно установить значение переменной default_authentication_plugin в mysql_native_password.
Я прописал в my.ini:
[mysqld]
default-authentication-plugin=mysql_native_password
[mysql]
default-authentication-plugin=mysql_native_password.
Затем рестартанул сервер. Но значение переменной не изменилось:

Как же все-таки установить переменную default_authentication_plugin в mysql_native_password и решить проблему с аутентификацией пользователей?
ps. Много где пишут не про переменную, а про опцию ––default-authentication-plugin. Что это и как изменить значение опции? Это то же самое, что и переменная?
pps. В консоли изменить значение переменной тоже не удается:

• 
  Вопрос задан

  более трёх лет назад
  

• 14817 просмотров