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)

Gaurav Pathak · Answer 1 · 2018-12-07T03:03:30.637

205

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

edited Dec 07 '18 at 03:03

answered May 20 '18 at 10:15

Gaurav Pathak

2,576
1
10
20

12

Although this works, it is better to upgrade the MySQL Connector/J version (as suggested by some of the other answers) as that allows you to use the more secure sha2_caching authentication plugin. – Mark Rotteveel Nov 29 '18 at 16:26
@MarkRotteveel Yes! I agree on that too. – Gaurav Pathak Nov 30 '18 at 03:07
@MarkRotteveel how? – theonlygusti Jan 29 '19 at 03:16
6

@theonlygusti You need to update MySQL Connector/J to at least 5.1.46, or - better - to 8.0.14. – Mark Rotteveel Jan 29 '19 at 10:23
@MarkRotteveel how do I do that? https://stackoverflow.com/questions/54413365/unable-to-load-authentication-plugin-caching-sha2-password-even-when-mysql-co – theonlygusti Jan 29 '19 at 10:51
@theonlygusti By either updating the dependency in your Maven or Gradle dependency definition, or by manually downloading the newer version from the MySQL site and replacing it in the dependencies of your project. – Mark Rotteveel Jan 29 '19 at 10:52
did you see that ^^ question? – theonlygusti Jan 29 '19 at 11:02
faced with `ERROR 1396 (HY000): Operation ALTER USER failed for 'user'@'localhost' ` – Yuseferi Mar 17 '19 at 10:56
@Yusef Making a new user is not a good solution, but it works at least. – HeadhunterKev Nov 27 '19 at 13:42

Paul · Answer 2 · 2020-04-06T18:10:46.893

95

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.

edited Apr 06 '20 at 18:10

answered Feb 03 '20 at 16:30

Paul

7,155
8
41
40

8

Thanks, this was exactly my problem. Did you happen to then get another error: "Public Key Retrieval is not allowed"? – Tomáš Zato Feb 04 '20 at 21:41
Interesting, no after the above, dbeaver loaded the tables. – Paul Feb 05 '20 at 01:36
6

THANK YOU. This helped me a lot. For those who are still getting the "Public Ket Retrieval is not allowed" error in DBeaver I found a link that solves this [here](https://community.atlassian.com/t5/Confluence-questions/MySQL-Public-Key-Retrieval-is-not-allowed/qaq-p/778956). – jpisty Jun 02 '20 at 00:30
10

`Public key retrieval is not allowed` – aderchox Aug 04 '20 at 20:53
The people who got `Public Key Retrieval is not allowed`. Please, try rerun the program. – Pranithan T. Nov 26 '20 at 09:17
6

In DBeaver latest versions (checked with version 7.3) above two options available as "MySql 5" and "MySql". You have to choose "MySql" and also when you hover your mouse over that option, it will show a hint saying that for mysql version 8+. – techmagister Dec 13 '20 at 02:08
for "Public Key Retrieval is not allowed. " , just goto Driver properties in DBeaver and set allowPublicKeyRetrieval to "True", for non Dbeaver users, follow https://stackoverflow.com/questions/50379839/connection-java-mysql-public-key-retrieval-is-not-allowed – Dreamweaver Nov 20 '21 at 16:23

score 62 · Answer 3 · answered Jan 17 '19 at 05:19

62

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.

answered Jan 17 '19 at 05:19

alok

2,718
21
17

2

Thanks for the helpful answer. For others, to find the latest version, please use this link; https://mvnrepository.com/artifact/mysql/mysql-connector-java – yusuf Sep 07 '19 at 11:41

score 34 · Answer 4 · answered Jun 01 '18 at 14:56

34

May be you are using wrong mysql_connector.

Use connector of same mysql version

answered Jun 01 '18 at 14:56

Vishal Boinapalli

341
2
2

Thanks. This works for me (on DBeaver, select `MySQL 8+` instead of `MySQL`) and I believe should be the right solution. – ibic Mar 21 '21 at 04:58

score 18 · Answer 5 · answered Jun 05 '18 at 22:22

18

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.

answered Jun 05 '18 at 22:22

Greg Charles

1,880
4
20
39

This helped me! – Anand Varkey Philips Jul 27 '18 at 09:47
Worked for me! I was using version 1.5.8.RELEASE. – Ionut Ciuta Aug 30 '18 at 08:38

score 18 · Answer 6 · answered Aug 21 '19 at 13:53

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

score 15 · Answer 7 · answered Jul 29 '18 at 11:57

15

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

answered Jul 29 '18 at 11:57

Turab

183
2
7

It worked. I wasted almost 2 days finding this solution. Thanks. – Hemant Nagpal Dec 29 '18 at 16:53

score 11 · Answer 8 · answered Oct 03 '18 at 14:16

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.

answered Oct 03 '18 at 14:16

Santosh Gatlewar

111
1
3

Upgrade to at least 5.1.46 or the latest version like today we have 8.0.18. This will help to resolve the issue. – Atul Nov 14 '19 at 09:41

score 9 · Answer 9 · answered Oct 20 '18 at 15:28

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

score 6 · Answer 10 · 2018-05-17T09:37:22.580

6

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;

edited May 17 '18 at 09:37

answered May 17 '18 at 09:31

Hello what you mean by " your_db_name.* " on the third line what is that * represent for – Sarath Mohan May 17 '18 at 09:49
The db name where user suppose to get the access. or you can simply use only last ALTER statement that will also resolve your issue. In ALTER you just need to pass your user name thats all – May 17 '18 at 09:50
GRANT ALL PRIVILEGES ON your_db_name.* TO 'your_user_name'@'%' identified by 'your_user_password'; – Sarath Mohan May 17 '18 at 09:53
Okay, you are facing the issue while connecting to DB right? Mean you are also specifying some DB name in a connection string, the same DB need to be added in place of your_db_name. hope you clear – May 17 '18 at 09:55

score 6 · Answer 11 · answered Jun 05 '19 at 19:08

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.

answered Jun 05 '19 at 19:08

Junior Vieira

553
5
5

Jack Ma · Answer 12 · 2018-08-16T16:06:37.353

4

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.

edited Aug 16 '18 at 16:06

answered Aug 16 '18 at 15:54

Jack Ma

153
1
11

This is a great answer! I was using `5.1.44` with mysql 8. I used the latest version which is 8.0.19 and it worked! – Dean Christian Armada Jan 06 '21 at 17:28

Jack J · Answer 13 · 2018-12-12T03:10:11.697

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.

I realize my answer is not entirely relevant. I posted my NetBeans answer here because [my own question](https://stackoverflow.com/questions/53531588/connecting-a-db-to-netbeans-first-time?noredirect=1#comment93953573_53531588) is marked as a duplicate to this question. — Jack J, Dec 12 '18 at 02:25

score 2 · Answer 14 · answered Aug 16 '18 at 16:01

2

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.

answered Aug 16 '18 at 16:01

DataVader

740
1
5
19

score 0 · Answer 15 · answered Sep 29 '18 at 17:33

0

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.

answered Sep 29 '18 at 17:33

user10434384

1

score 0 · Answer 16 · answered Nov 20 '18 at 08:49

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.

score 0 · Answer 17 · answered Apr 12 '21 at 02:04

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

score 0 · Answer 18 · answered May 19 '22 at 03:32

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

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

18 Answers18

Linked

Related