How to resolve Unable to load authentication plugin 'caching_sha2_password' issue
JavaMysqlEclipseMavenSpring BootJava Problem Overview
In eclipse when i started my application i got this - Could not discover the dialect to use. java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
> at java.sql.SQLException: Unable to load authentication plugin
> 'caching_sha2_password'. at at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:868) at at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:864) at at
> com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1746)
> at at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226) at at
> com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2191)
> at at
> com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2222)
> at at
> com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2017)
> at at com.mysql.jdbc.ConnectionImpl.
Java Solutions
Solution 1 - Java
> Starting with MySQL 8.0.4, they have changed the default > authentication plugin for MySQL server from mysql_native_password to > caching_sha2_password.
You can run the below command to resolve the issue.
sample username / password => student / pass123
ALTER USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123';
Refer the official page for details: MySQL Reference Manual
Solution 2 - Java
Others have pointed to the root issue, but in my case I was using dbeaver and initially when setting up the mysql connection with dbeaver was selecting the wrong mysql driver (credit here for answer: https://github.com/dbeaver/dbeaver/issues/4691#issuecomment-442173584 )
Selecting the MySQL choice in the below figure will give the error mentioned as the driver is mysql 4+ which can be seen in the database information tip.
Rather than selecting the MySQL driver instead select the MySQL 8+ driver, shown in the figure below.
Solution 3 - Java
Upgrade your mysql-connector" lib package with your mysql version like below i am using 8.0.13 version and in pom I changed the version:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>8.0.13</version>
</dependency>
My problem has resolved after this.
Solution 4 - Java
May be you are using wrong mysql_connector.
Use connector of same mysql version
Solution 5 - Java
I was hitting this error in one Spring Boot app, but not in another. Finally, I found the Spring Boot version in the one not working was 2.0.0.RELEASE and the one that was working was 2.0.1.RELEASE. That led to a difference in the MySQL Connector -- 5.1.45 vs. 5.1.46. I updated the Spring Boot version for the app that was throwing this error at startup and now it works.
Solution 6 - Java
This could be your connectors for MySQL which need to be updated, as MySQL8 changed the encryption of passwords - so older connectors are encrypting them incorrectly.
The maven repo for the java connector can be found here.
If you use flyway plugin, you should also consider updating it, too!
Then you can simply update your maven pom with:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
Or for others who use Gradle, you can update build.gradle with:
buildscript {
ext {
...
}
repositories {
...
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
classpath('mysql:mysql-connector-java:8.0.11')
}
}
Solution 7 - Java
You need just to delete your older connector and download new version (mysql-connector-java-5.1.46)
Solution 8 - Java
I am using mysql 8.0.12 and updating the mysql connector to mysql-connector-java-8.0.12 resolved the issue for me.
Hope it helps somebody.
Solution 9 - Java
As Mentioned in above repilies:
Starting with MySQL 8.0.4, the MySQL team changed the default authentication plugin for MySQL server from mysql_native_password to caching_sha2_password.
So there are three ways to resolve this issue:
1. drop USER 'user_name'@'localhost';
flush privileges;
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_name';
GRANT ALL PRIVILEGES ON * . * TO 'user_name'@'localhost';
ALTER USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY
'user_name';
2. drop USER 'user_name'@'localhost';
flush privileges;
CREATE USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'user_name';
GRANT ALL PRIVILEGES ON * . * TO 'user_name'@'localhost'
3. If the user is already created, the use the following command:
ALTER USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY
'user_name';
Solution 10 - Java
You are having issue with newly MySQL version that came with "caching_sha2_password" plugin, follow the below command to get it resolved.
DROP USER 'your_user_name'@'%';
CREATE USER 'your_user_name'@'%' IDENTIFIED WITH mysql_native_password BY 'your_user_password';
GRANT ALL PRIVILEGES ON your_db_name.* TO 'your_user_name'@'%' identified by 'your_user_password';
Or you can just use the below command to keep your privileges as it is:
ALTER USER your_user_name IDENTIFIED WITH mysql_native_password;
Solution 11 - Java
I did exactly the same issue using the dependency below:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
I just change to version 46 and everything works.
Solution 12 - Java
I think it is better to update your "mysql-connector" lib package, so database can be still more safe.
I am using mysql of version 8.0.12. When I updated the mysql-connector-java to version 8.0.11, the problem was gone.
Solution 13 - Java
I ran into this problem on NetBeans when working with a ready-made project from this Murach JSP book. The problem was caused by using the 5.1.23 Connector J with a MySQL 8.0.13 Database. I needed to replace the old driver with a new one. After downloading the Connector J, this took three steps.
How to replace NetBeans project Connector J:
-
Download the current Connector J from here. Then copy it in your OS.
-
In NetBeans, click on the Files tab which is next to the Projects tab. Find the mysql-connector-java-5.1.23.jar or whatever old connector you have. Delete this old connector. Paste in the new Connector.
-
Click on the Projects tab. Navigate to the Libraries folder. Delete the old mysql connector. Right click on the Libraries folder. Select Add Jar / Folder. Navigate to the location where you put the new connector, and select open.
-
In the Project tab, right click on the project. Select Resolve Data Sources on the bottom of the popup menu. Click on Add Connection. At this point NetBeans skips forward and assumes you want to use the old connector. Click the Back button to get back to the skipped window. Remove the old connector, and add the new connector. Click Next and Test Connection to make sure it works.
For video reference, I found this to be useful. For IntelliJ IDEA, I found this to be useful.
Solution 14 - Java
If you can update your connector to a version, which supports the new authentication plugin of MySQL 8, then do that. If that is not an option for some reason, change the default authentication method of your database user to native.
Solution 15 - Java
remove the connect .jar file if you added the multiple version connector jar file only add the connector jar file that match with your sql version.
Solution 16 - Java
Another cause might be the fact that you're pointing to the wrong port.
Make sure you are actually pointing to the right SQL server. You may have a default installation of MySQL running on 3306 but you may actually be needing a different MySQL instance.
Check the ports and run some query against the db.
Solution 17 - Java
I also have this error which is confusing. Eventually I find this is none of business for mysql java connector version, the error in fact is that I have set wrong datasource-user-name(this config: spring.datasource.druid.username).
- The sprint boot error info is :
create connection error,
url: jdbc:mysql://..amazonaws.com:3306/moe_grooming?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false, errorCode 0, state null
3624 java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password
My Environment:
MySQL Server 8.0.22- mysql java connector version
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>5.1.44</version>
</dependency>
- The used password plugin of this user is
mysql_native_password
Solution 18 - Java
If you encounter this error Authentication plugin 'caching_sha2_password' cannot be loaded by export or else. Because DBeaver still using MySQL binary version 5.
So you need to download MySQL binary version 8 from https://dev.mysql.com/downloads/mysql/ then extract it to some path for a new environment like the below image.
Then you need to set binary from here or on edit connection
