MySQL Bugs: #90437: mysqlsh caching_sha2_password authentication failing on centos

Bug #90437	mysqlsh caching_sha2_password authentication failing on centos
Submitted:	14 Apr 2018 0:10	Modified:	9 Aug 2018 17:24
Reporter:	Kenny Gryp	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Shell AdminAPI InnoDB Cluster / ReplicaSet	Severity:	S3 (Non-critical)
Version:	mysql-server-8.0.4 & mysql-shell-1.0.11	OS:	CentOS (7)
Assigned to:		CPU Architecture:	x86

Description:
I cannot connect with the default caching_sha2_password with mysqlshell for some reason on centos7

mysql-js> \c root@127.0.0.1:3306
Creating a Session to 'root@127.0.0.1:3306'
Enter password:
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
mysql-js>

How to repeat:
1. Install mysql-community-server & mysql-shell:

[vagrant@node1 ~]$ rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
mysql-community-client-8.0.4-0.1.rc.el7.x86_64
mysql-community-common-8.0.4-0.1.rc.el7.x86_64
mysql-community-libs-compat-8.0.4-0.1.rc.el7.x86_64
mysql-community-server-8.0.4-0.1.rc.el7.x86_64
mysql-community-libs-8.0.4-0.1.rc.el7.x86_64
mysql-shell-1.0.11-1.el7.x86_64

2. start mysql and change the password for root

3. connect with mysql client to see if things are working fine
[vagrant@node1 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

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> exit
Bye

4. try to connect with mysqlsh to the database

[vagrant@node1 ~]$ mysqlsh
MySQL Shell 1.0.11

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

Type '\help' or '\?' for help; '\quit' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \c root@127.0.0.1:3306
Creating a Session to 'root@127.0.0.1:3306'
Enter password:
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
mysql-js>

Suggested fix:
* allow caching_sha2_password authentication out of the box or provide easy instructions on how to connect with the new authentication method

Hello Kenny,

Thank you for the report!

Thanks,
Umesh

# Seen when mysqlsh session tries to connect(mysql port or mysqlx port) MySQL Server 8.0.4 and above.  Tried with --auth-method but still same issue. 

mysql-js> \c root@localhost:3333
Creating a Session to 'root@localhost:3333'
Enter password:
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
mysql-js> \c root@localhost:33060
Creating a Session to 'root@localhost:33060'
Enter password:
ERROR: 1045: Invalid user or password
mysql-js> \q
Bye!

Workaround - User created with mysql_native_password able to connect 8.0.4.

-- cli
mysql>    CREATE USER 'bug'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql>    grant all on *.* to 'bug'@'localhost';
Query OK, 0 rows affected (0.00 sec)

-- shell

mysql-js> \c bug@localhost:3333
Creating a Session to 'bug@localhost:3333'
Enter password:
Your MySQL connection id is 16
Server version: 8.0.4-rc-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
mysql-js>
mysql-js> \c bug@localhost:33060
Creating a Session to 'bug@localhost:33060'
Enter password:
Closing old connection...
Your MySQL connection id is 17 (X protocol)
Server version: 8.0.4-rc-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.

Posted by developer:
 
MySQL Shell 1.0.11 is no longer supported. Support for caching_sha2_password is available in MySQL Shell 8.0. Please upgrade your MySQL Shell to 8.0.12. MySQL Server upgrade is recommended as well.