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)

Solution 1

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

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

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

May be you are using wrong mysql_connector.

Use connector of same mysql version

Solution 5

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.

View more solutions

Comments

[Anand Varkey Philips]

This helped me!

[Ionut Ciuta]

Worked for me! I was using version 1.5.8.RELEASE.

[Mark Rotteveel]

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.

[Gaurav Pathak]

@MarkRotteveel Yes! I agree on that too.

[Jack J]

I realize my answer is not entirely relevant. I posted my NetBeans answer here because my own question is marked as a duplicate to this question.

[Hemant Nagpal]

It worked. I wasted almost 2 days finding this solution. Thanks.

[theonlygusti]

@MarkRotteveel how?

[Mark Rotteveel]

@theonlygusti You need to update MySQL Connector/J to at least 5.1.46, or - better - to 8.0.14.

[theonlygusti]

@MarkRotteveel how do I do that? stackoverflow.com/questions/54413365/…

[Mark Rotteveel]

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

[theonlygusti]

did you see that ^^ question?

[Yuseferi]

faced with ERROR 1396 (HY000): Operation ALTER USER failed for 'user'@'localhost'

[yusuf]

Thanks for the helpful answer. For others, to find the latest version, please use this link; mvnrepository.com/artifact/mysql/mysql-connector-java

[Atul]

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.

[HeadhunterKev]

@Yusef Making a new user is not a good solution, but it works at least.

[Tomáš Zato]

Thanks, this was exactly my problem. Did you happen to then get another error: "Public Key Retrieval is not allowed"?

[Paul]

Interesting, no after the above, dbeaver loaded the tables.

[jpisty]

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.

[aderchox]

Public key retrieval is not allowed

[Pranithan T.]

The people who got Public Key Retrieval is not allowed. Please, try rerun the program.

[techmagister]

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

[Dean Christian Armada]

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!

[ibic]

Thanks. This works for me (on DBeaver, select MySQL 8+ instead of MySQL) and I believe should be the right solution.

[Dreamweaver]

for "Public Key Retrieval is not allowed. " , just goto Driver properties in DBeaver and set allowPublicKeyRetrieval to "True", for non Dbeaver users, follow stackoverflow.com/questions/50379839/…