[java] How to resolve Unable to load authentication plugin 'caching_sha2_password' issue

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.(ConnectionImpl.java:779) at at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47) at at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at at java.lang.reflect.Constructor.newInstance(Unknown Source) at at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389) at at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330) at at java.sql.DriverManager.getConnection(Unknown Source) at at java.sql.DriverManager.getConnection(Unknown Source) at at ch.qos.logback.core.db.DriverManagerConnectionSource.getConnection(DriverManagerConnectionSource.java:54) at at ch.qos.logback.core.db.ConnectionSourceBase.discoverConnectionProperties(ConnectionSourceBase.java:46) at at ch.qos.logback.core.db.DriverManagerConnectionSource.start(DriverManagerConnectionSource.java:38) at at ch.qos.logback.core.joran.action.NestedComplexPropertyIA.end(NestedComplexPropertyIA.java:161) at at ch.qos.logback.core.joran.spi.Interpreter.callEndAction(Interpreter.java:309) at at ch.qos.logback.core.joran.spi.Interpreter.endElement(Interpreter.java:193) at at ch.qos.logback.core.joran.spi.Interpreter.endElement(Interpreter.java:179) at at ch.qos.logback.core.joran.spi.EventPlayer.play(EventPlayer.java:62) at at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:165) at at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:152) at at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:110) at at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:53) at at ch.qos.logback.classic.util.ContextInitializer.configureByResource(ContextInitializer.java:75) at at ch.qos.logback.classic.util.ContextInitializer.autoConfig(ContextInitializer.java:150) at at org.slf4j.impl.StaticLoggerBinder.init(StaticLoggerBinder.java:84) at at org.slf4j.impl.StaticLoggerBinder.(StaticLoggerBinder.java:55) at at org.slf4j.LoggerFactory.bind(LoggerFactory.java:150) at at org.slf4j.LoggerFactory.performInitialization(LoggerFactory.java:124) at at org.slf4j.LoggerFactory.getILoggerFactory(LoggerFactory.java:412) at at ch.qos.logback.classic.util.StatusViaSLF4JLoggerFactory.addStatus(StatusViaSLF4JLoggerFactory.java:32) at at ch.qos.logback.classic.util.StatusViaSLF4JLoggerFactory.addInfo(StatusViaSLF4JLoggerFactory.java:20) at at ch.qos.logback.classic.servlet.LogbackServletContainerInitializer.onStartup(LogbackServletContainerInitializer.java:32) at at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5245) at at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) at at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1421) at at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1411) at at java.util.concurrent.FutureTask.run(Unknown Source) at at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at at java.lang.Thread.run(Unknown Source)

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

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.

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.

May be you are using wrong mysql_connector.

Use connector of same mysql version

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.

You need just to delete your older connector and download new version (mysql-connector-java-5.1.46)

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')
    }
 }

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.

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';

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;

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.

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:

1. Download the current Connector J from here. Then copy it in your OS.

2. 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.

3. 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.

4. 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.

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.

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.

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.

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.