How to resolve Unable to load authentication plugin 'caching_sha2_password' issue

Question

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)

Answer 1

Setup

I was facing the issue with the docker image MySQL Server 8.2.0. The microservice side was Java 21+ SpringBoot 3.2.0

As stated in many of these answers the mysql-connector-j.VERSION (former mysql-connector-java) has to fit the database version. Therefore <mysql-connector-j.version>8.2.0</mysql-connector-j.version> was added in the pom.xml.

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>${mysql-connector-j.version}</version>
</dependency>

Debugging

On database startup everything seemed smooth. The init-SQL-script was processed sucessfully

[Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql

Sample init.sql (for debugging):

-- creates the auth schema
CREATE SCHEMA IF NOT EXISTS auth;
CREATE USER 'authuser'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'sampleAuthPassword';
GRANT ALL PRIVILEGES ON auth.* TO 'authuser'@'%';

SELECT User, Host FROM mysql.user;

This should give you something alike this:

mysql-db  | User    Host
mysql-db  | authuser    %
mysql-db  | root    %

Problem

Still the microservice failed connecting to the MySQL-Database with the HikariPool-1 - Exception during pool initialization.

auth-endpoint-1  | 2023-12-12T08:21:38.114Z  INFO 1 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
auth-endpoint-1  | 2023-12-12T08:21:39.365Z ERROR 1 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.
auth-endpoint-1  |
auth-endpoint-1  | java.sql.SQLException: Access denied for user 'auth'@'192.168.48.3' (using password: YES)
auth-endpoint-1  |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
auth-endpoint-1  |  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
auth-endpoint-1  |  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:815) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
auth-endpoint-1  |  at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:438) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
auth-endpoint-1  |  at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]
auth-endpoint-1  |  at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:189) ~[mysql-connector-j-8.2.0.jar!/:8.2.0]

Three (3) attempts were fired by the SpringBoot-Service. All attempts could be seen as single error lines in the DB.

So far so known ... . .

Solution

Everyone here is right, that the versions of MySQL and mysql-connector-j have to match!

Additionally:

• For me it was irritating that the same error was published for using a wrong user/password combination.

After revision of

• docker-compose.yml
• DB -> init.sql
• SpringBoot -> application.yml

The error was fixed on fitting User/Password-Combination ;-)

Answer 2

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

Answer 3

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

0. 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:

1. MySQL Server 8.0.22
2. mysql java connector version

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    <version>5.1.44</version>
</dependency>

3. The used password plugin of this user is mysql_native_password

Answer 4

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.

---

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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

Answer 10

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.

Answer 11

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

Answer 12

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.

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

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

Answer 17

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.

Answer 18

May be you are using wrong mysql_connector.

Use connector of same mysql version

Answer 19

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;