caching_sha2_password Connection closed by foreign host

Question

I create the mysql container in docker with url:

jdbc:mysql://172.17.0.2:3306/'databaseName'

172.17.0.2 is the IP address of the docker container. I create the user with:

 CREATE USER 'saman'@'%' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON *.* TO 'saman'@'%'  WITH GRANT OPTION;

when i want to connect from intllje Ide i connect successfully, but when i want to connect from my java application i got the exception :

 Caused by: com.mysql.cj.core.exceptions.WrongArgumentException: Unable 
to load authentication plugin 'caching_sha2_password'

also i changed the default authentication password from caching_sha2_password to mysql_native_password in mysql users. for more information when i use telnet command:

telnet 172.17.0.2 3306 return: caching_sha2_passwordConnection closed by foreign host.

Answer 1

You got this issue because all new MySQL version came with added authentication plugin, called "caching_sha2_password" but you can bypass this by using below statement.

GRANT ALL PRIVILEGES ON *.* 
TO 'saman'@'%'  IDENTIFIED WITH 
mysql_native_password WITH GRANT OPTION;

Answer 2

Finally i found the solution for my question. I connect to mysql command line and run the following query:

alter user 'saman'@'%' identified with mysql_native_password by 'password';

Then in phpmyadmin the exception gone away. You can check the result of above query with:

select user,host,authentication_string,plugin from mysql.user;

and you must see the mysql_native_password in front of 'saman' user.