Connection Java - MySQL : Public Key Retrieval is not allowed

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 "Driver properties"

4. Set these two properties: "allowPublicKeyRetrieval" to true and "useSSL" to false

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.

Note that this is suitable for local development only.

Use jdbc url as :

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

PortNo: 3306 can be different in your configuation

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

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

Open DBeaver->Edit connection->find driver properties->allowPublicKeyRetrieval=true and useSSl=true

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:

• use a secure connection (useSSL=true)
• use an unencrypted connection that supports password exchange using an RSA key pair (useSSL=false and additional configuration parameters - see https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

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)

I had the same issue with DBeaver database tools.

I used URL ( connection string ) instead of host as below :

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

and it works.

see the screenshot:

spring.datasource.url=jdbc:mysql://localhost:3306/database?createDatabaseIfNotExist=true&allowPublicKeyRetrieval=true&useSSL=false 

You can insert this line to your applications.properties file and this means,

• spring.datasource.url=jdbc:mysql://localhost:3306/ This one uses mysql as the database service. I think this can changed by using relavent name and the port of your database name.
• database?createDatabaseIfNotExist=true = use the database named database if you haven't already make a database like that, make a new one.
• allowPublicKeyRetrieval=true = to allow the client to automatically request the public key from the server. (This part might be additional)
• useSSL=false = This will disable SSL and also suppress the SSL errors

Furthermore, be alert about the spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect property in the same file.

Finally check whether you've added following dependency in dependencies in your pom.xml file.

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.23</version> </dependency> 

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 

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.

I managed to connect to MySQL using SSL through DBeaver with allowPublicKeyRetrieval=false and useSSL=true driver settings, which is preferred solution from security perspective, so I wanted to share my solution here.

To establish a successful SSL connection, you need to provide the correct paths to the certificates in your client (e.g., DBeaver).

MySQL generates the certificates by default, so you don't need to generate them manually.

They are located in the /var/lib/mysql directory.

This is from official MySQL documentation:

Automatic SSL and RSA File Generation

For MySQL distributions compiled using OpenSSL, the MySQL server has the capability of automatically generating missing SSL and RSA files at startup. The auto_generate_certs, sha256_password_auto_generate_rsa_keys, and caching_sha2_password_auto_generate_rsa_keys system variables control automatic generation of these files. These variables are enabled by default. They can be enabled at startup and inspected but not set at runtime.

At startup, the server automatically generates server-side and client-side SSL certificate and key files in the data directory if the auto_generate_certs system variable is enabled, no SSL options other than --ssl are specified, and the server-side SSL files are missing from the data directory. These files enable encrypted client connections using SSL; see Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.

If you can't find the certificates there, it's likely that you have modified the default behavior of MySQL using system variables. In that case, please refer to the documentation to learn how to resolve this issue.

The required certificate files for SSL connection are as follows:

• CA Certificate: /var/lib/mysql/ca.pem
• Client Certificate: /var/lib/mysql/client-cert.pem
• Client Private Key: /var/lib/mysql/client-key.pem

By providing these paths in the SSL configuration of your client application, you should be able to connect to MySQL using SSL successfully.

I hope this helps others who might encounter similar SSL connection issues with MySQL and DBeaver.

P.S If you use Docker Image, you need to copy certificates and key from docker container to your local computer, so MySQL client (e.g DBeaver) could find them there.

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.

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.

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.

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

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 

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

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.

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 

For folks that are using a .xml file to store their database connection details, modifying your jdbcUrl to below should make your connection from your test tomcat server to your database valid again:

jdbcUrl="jdbc:mysql://localhost:3306/DB_NAME?autoReconnect=true&allowPublicKeyRetrieval=true&useSSL=false" 

Go to your application.properties and change your spring.datasource.url

See my example:

spring.datasource.url=jdbc:mysql://localhost:3306/hotel_java?allowPublicKeyRetrieval=true&useSSL=false 

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

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 

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

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.

check below line one more time

spring.datasource.url=jdbc:mysql://localhost:3306/customerbankdb?autoReconnect=true&AllowPublicKeyRetrieval=true&useSSL=false