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

User · Answer

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;

User · Answer

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    lt dependency gt       lt groupId gt mysql lt  groupId gt       lt artifactId gt mysql-connector-java lt  artifactId gt       lt version gt 8 0 17 lt  version gt   lt  dependency gt    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

User · Answer

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

User · Answer

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

User · Answer

You need just to delete your older connector and download new version  mysql-connector-java-5 1 46

User · Answer

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

User · Answer

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    lt dependency gt       lt groupId gt mysql lt  groupId gt       lt artifactId gt mysql-connector-java lt  artifactId gt       lt scope gt runtime lt  scope gt       lt version gt 8 0 13 lt  version gt   lt  dependency gt    My problem has resolved after this

User · Answer

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

User · Answer

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

User · Answer

I did exactly the same issue using the dependency below    lt dependency gt       lt groupId gt mysql lt  groupId gt       lt artifactId gt mysql-connector-java lt  artifactId gt       lt version gt 5 1 44 lt  version gt   lt  dependency gt    I just change to version 46 and everything works

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

May be you are using wrong mysql connector   Use connector of same mysql version

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

Examples related to java

Examples related to mysql

Examples related to eclipse

Examples related to maven

Examples related to spring-boot