Connection Java - MySQL : Public Key Retrieval is not allowed

JavaMysqlExceptionConnector

Java Problem Overview

I try to connect MySQL database with Java using connector 8.0.11. Everything seems to be OK, but I get this exception:

Exception in thread "main" java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed at
     com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:108) at 
     com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) at
     com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at     
     com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862) at 
     com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:444) at
     com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230) at
     com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226) at
     com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:438) at
     com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:146) at
     com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:119) at
     ConnectionManager.getConnection(ConnectionManager.java:28) at
     Main.main(Main.java:8)

Here is my Connection Manager class:

public class ConnectionManager {

    public static final String serverTimeZone = "UTC";
    public static final String serverName = "localhost";
    public static final String databaseName ="biblioteka";
    public static final int portNumber = 3306;
    public static final String user = "anyroot";
    public static final String password = "anyroot";
    
    public static Connection getConnection() throws SQLException {
    
        MysqlDataSource dataSource = new MysqlDataSource();
    
        dataSource.setUseSSL( false );
        dataSource.setServerTimezone( serverTimeZone );
        dataSource.setServerName( serverName );
        dataSource.setDatabaseName( databaseName );
        dataSource.setPortNumber( portNumber );
        dataSource.setUser( user );
        dataSource.setPassword( password );
        
        return dataSource.getConnection();
    }
}

Java Solutions

Solution 1 - Java

You should add client option to your mysql-connector allowPublicKeyRetrieval=true to allow the client to automatically request the public key from the server. Note that allowPublicKeyRetrieval=True could allow a malicious proxy to perform a MITM attack to get the plaintext password, so it is False by default and must be explicitly enabled.

https://mysql-net.github.io/MySqlConnector/connection-options/

you could also try adding useSSL=false when you use it for testing/develop purposes

example:

jdbc:mysql://localhost:3306/db?allowPublicKeyRetrieval=true&useSSL=false

Solution 2 - Java

For DBeaver users:

  1. Right click your connection, choose "Edit Connection"

  2. On the "Connection settings" screen (main screen) click on "Edit Driver Settings"

  3. Click on "Connection properties", (In recent versions it named "Driver properties")

  4. Right click the "user properties" area and choose "Add new property"

  5. Add two properties: "useSSL" and "allowPublicKeyRetrieval"

  6. Set their values to "false" and "true" by double clicking on the "value" column

Solution 3 - Java

When doing this from DBeaver I had to go to "Connection settings" -> "SSL" tab and then :

  • uncheck the "Verify server certificate"
  • check the "Allow public key retrival"

This is how it looks like. DBeaver configuration

Note that this is suitable for local development only.

Solution 4 - Java

Use jdbc url as :

jdbc:mysql://localhost:3306/Database_dbName?allowPublicKeyRetrieval=true&useSSL=False;

PortNo: 3306 can be different in your configuation

Solution 5 - Java

Alternatively to the suggested answers you could try and use mysql_native_password authentication plugin instead of caching_sha2_password authentication plugin.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';

Solution 6 - Java

I updated this parameter when I faced the issue of "public-key-retrieval-is-not-allowed" with root account.

DBeaver update this parameter

Solution 7 - Java

I solve this issue using below configuration on spring boot framework

spring.datasource.url=jdbc:mysql://localhost:3306/db-name?useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

Solution 8 - Java

First of all, please make sure your Database server is up and running. I was getting the same error, after trying all the answers listed here I found out that my Database server was not running.

You can check the same from MySQL Workbench, or Command line using

mysql -u USERNAME -p

This sounds obvious, but many times we assume that Database server is up and running all the time, especially when we are working on our local machine, when we restart/shutdown the machine, Database server will be shutdown automatically.

Solution 9 - Java

This also can be happened due to wrong user name or password. As solutions I've added allowPublicKeyRetrieval=true&useSSL=false part but still I got error then I checked the password and it was wrong.

Solution 10 - Java

Another way, on DBeaver.

You can edit the connection of a database, go to SSL tab in connection settings. There's a checkbox "allow public key retrieval" mark it as true. That'll sove the issue.

Solution 11 - Java

The above error in my case was actually due to the wrong username and password. Solving the issue:

  1. Go to the line DriverManager.getConnection("jdbc:mysql://localhost:3306/?useSSL=false", "username", "password"); The fields username and password might be wrong. Enter the username and password which you use to start your mysql client. The username is generally root and password is the string which you enter when a screen similar to this appears Startup screen of mysql

Note: The portname 3306 might be different in your case.

Solution 12 - Java

This solution worked for MacOS Sierra, and running MySQL version 8.0.11. Please make sure driver you have added in your build path - "add external jar" should match up with SQL version.

String url = "jdbc:mysql://localhost:3306/syscharacterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";

Solution 13 - Java

In my case it was user error. I was using the root user with an invalid password. I am not sure why I didn't get an auth error but instead received this cryptic message.

Solution 14 - Java

I found this issue frustrating because I was able to interact with the database yesterday, but after coming back this morning, I started getting this error.

I tried adding the allowPublicKeyRetrieval=true flag, but I kept getting the error.

What fixed it for me was doing Project->Clean in Eclipse and Clean on my Tomcat server. One (or both) of those fixed it.

I don't understand why, because I build my project using Maven, and have been restarting my server after each code change. Very irritating...

Solution 15 - Java

Give connection URL as jdbc:mysql://localhost:3306/hb_student_tracker?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC

Solution 16 - Java

If you are getting the following error while connecting the mysql (either local or mysql container running the mysql):

java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

Solution: Add the following line in your database service:

command: --default-authentication-plugin=mysql_native_password

Solution 17 - Java

Update the useSSL=true in spring boot application connection with mysql;

jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&useSSL=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Solution 18 - Java

In MySQL 8.0 the default authentication plugin was changed from mysql_native_password to caching_sha2_password. See https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password for more information about this change.

What that means is that in order to use caching_sha2_password the connection must do one of the following:

You have a few options:

  • ALTER the users to use the mysql_native_password plugin (like how it was doing historically and will also work with older clients / connections which don't support caching_sha2_password)
  • useSSL=true
  • useSSL=false and configure public key retrieval (this doesn't mean using allowPublicKeyRetrieval=true which would avoid the error - but defeats the objective of this extra security and is slow - it does mean using something like server-public-key-path to point to the client side copy of the public key)

Solution 19 - Java

I was also facing such an issue while dockerizing our existing application. The solution si to add allowPublicKeyRetrieval connection option of MySQL with a value of true to the JDBC connection string. If that is not working , try adding useSSL option to false as well .

The resultant string would look like this :

jdbc:mysql://<database server ip>:3306/databaseName?allowPublicKeyRetrieval=true&useSSL=false

Solution 20 - Java

> Setting Server Time Zone to my local place, fixed the issue.

ServerTimeZone

Solution 21 - Java

My problem was in pom.xml (spring boot). My pom.xml had two dependencies entries for different databases. Make sure to keep only the MySQL dependency and remove any other database dependency entry.

Categories
Recommended Java Solutions
Recommended Mysql Solutions
Recommended Exception Solutions

Previous Solution:

How to get query parameters from URL in Angular 5?

AngularTypescriptAngular Routing

Next Solution:

Multiple submit buttons in an HTML form

HtmlFormsForm SubmitSubmit Button

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestiondannyView Question on Stackoverflow
Solution 1 - JavajtomaszkView Answer on Stackoverflow
Solution 2 - JavaJavier AvilesView Answer on Stackoverflow
Solution 3 - JavaMichał KrzywańskiView Answer on Stackoverflow
Solution 4 - Javasusan097View Answer on Stackoverflow
Solution 5 - JavaTorsten OjapervView Answer on Stackoverflow
Solution 6 - JavaDuc Toan PhamView Answer on Stackoverflow
Solution 7 - JavaduyuanchaoView Answer on Stackoverflow
Solution 8 - JavaVinod Kumar K VView Answer on Stackoverflow
Solution 9 - JavaHashanRView Answer on Stackoverflow
Solution 10 - JavaRaviView Answer on Stackoverflow
Solution 11 - JavarisingStarkView Answer on Stackoverflow
Solution 12 - JavasurendrapandayView Answer on Stackoverflow
Solution 13 - JavajuiceView Answer on Stackoverflow
Solution 14 - JavaCameron HudsonView Answer on Stackoverflow
Solution 15 - Javasamit tiwaryView Answer on Stackoverflow
Solution 16 - JavaRohtash Singh LakraView Answer on Stackoverflow
Solution 17 - JavaBheem SinghView Answer on Stackoverflow
Solution 18 - JavaYosephView Answer on Stackoverflow
Solution 19 - JavaArun sView Answer on Stackoverflow
Solution 20 - JavaManohar Reddy PoreddyView Answer on Stackoverflow
Solution 21 - JavaSavrigeView Answer on Stackoverflow