System information:
- Operating system (distribution) and version: Pop_OS 20.4
- DBeaver version: 7.15
- Additional extensions: None
Connection specification:
- Database name and version: MySQL 8.0.21
- Driver name: mysql-connector-java:RELEASE [8.0.17]
- Do you use tunnels or proxies (SSH, SOCKS, etc)?
Describe the problem you’re observing:
I created a docker instance with the command:
docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d -p 3306:3306 mysql
But when I try to create a new connection, it throws an error saying:
Public Key Retrieval is not allowed
I saw people on stackoverflow changing the driver property AllowPublicKeyRetrieval to true and the option useSSL to false. And it really works. But when I was tweaking the properties, I found that you don’t really need to change those properties to make it work, you can just set useSSL or sslMode to another value, it can be an invalid value, and change this properties back to its default values and the connection works as intented, even if I put useSSL to false, the connection still works with no problem, unless I remove the container and create again, where the initial error returns. In my vision, if it is working with the properties manually set, it should work with the default ones too, this is why I’m submiting this as a bug. If it is not, can someone please explain why this happen?
Steps to reproduce, if exist:
- Create a new docker container with the command:
docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d -p 3306:3306 mysql
-
Create a connection for MySQL with the root user and the password 123456.
-
When testing the connection, it should throw the error saying Public Key Retrieval is not allowed.
-
Go to the tab Driver Properties, look for the option useSSL change it to false and test the connection, it should give you the same error as before.
-
Now change the property back to true, and the connection should work.
-
The connection works even if I set the property back to false. What I don’t understand.
-
Instead of changing the property to false, you can put any other invalid value, test the connection, it will throw an error saying the value is not acceptable, change the property back to true, the default value, test the connection, and it will work.
-
Instead changing the useSSL property, you can change the sslMode property. Change it from PREFERRED to DISABLED, test the connection, the error will persist, change it back to PREFERRED, the error goes away. You can, too, put a invalid value, test the connection, change it back to PREFERRED and now the connection works.
Include any warning/errors/backtraces from the logs
Я использую DBeaver для просмотра базы данных SQL на MySQL 8+.
Все работает, но иногда, открывая DBeaver, я получаю следующее сообщение об ошибке:
Public Key Retrieval is not allowed
И тогда DBeaver не может подключиться к MySQL.
Чтобы решить эту проблему, я должен перенастроить MySQL.
Есть ли самый простой способ решить эту проблему?
2 ответа
Исправьте это, удалив файл SSL или установив useSSL = false
0
Ashish Sondagar
14 Май 2020 в 19:23
Вы пытались явно разрешить извлечение открытого ключа в DBeaver? В настройках подключения, вкладка SSL вы можете найти его
Это в случае, если вы используете SSL, иначе на том же экране вы можете отключить SSL, сняв флажки «использовать SSL» и «требовать SSL»
0
Carlos Robles
16 Май 2020 в 06:37
My db server is mysql 8.0.11 and driver version 5.1.46.
When ssl is enabled, and tries to connect to the database, confluence setup reports the following.
Problem connecting to your database
SQLState - S1000
java.sql.SQLException: Illegal operation on empty result set.
But if «skip_ssl» is written to my.cnf and restarted mysql, then it shows the following error message.
Problem connecting to your database
SQLState - 08001
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
Below is the output of logs/catalina.out
20-Apr-2018 14:35:35.680 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in 6392 ms
Fri Apr 20 14:37:39 SGT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
I also tried with the following connection string to disable verification and ssl
jdbc:mysql://localhost/confluencedb?verifyServerCertificate?=false&useSSL=false
4 answers
1 accepted
Comments for this post are closed
Community moderators have prevented the ability to post new answers.
Post a new question
Comments for this post are closed
Community moderators have prevented the ability to post new answers.
Post a new question
Still have a question?
Get fast answers from people who know.
Was this helpful?
Thanks!
Sign in to follow this
Followers
0
By
Perpetual Newbie, November 17, 2018 in General discussions
Recommended Posts
Perpetual Newbie
1
Posted November 17, 2018
Can’t connect to MySQL with DBeaver. Error:
Public Key Retrieval is not allowed
How to fix?
Share this post
Link to post
Share on other sites
C.M.
15
Posted November 17, 2018
Add option allowPublicKeyRetrieval with vaule true in MySQL driver settings.
-
1
-
1
Share this post
Link to post
Share on other sites
Perpetual Newbie
1
Posted December 4, 2019
Wow, that simple? 👀
It works now! Thanks for your help again 😊
Share this post
Link to post
Share on other sites
Everlasting Summer
3
Posted August 14, 2021
17.11.2018 в 16:27, C.M. сказал:
Add option allowPublicKeyRetrieval with vaule true in MySQL driver settings.
Thank you. It worked 👍
Share this post
Link to post
Share on other sites
Sign in to follow this
Followers
0
Go To Topic Listing
Connection Java-MySql : Public Key Retrieval is not Allowed
In this post, I will provide a solution for frequently occurring exception while working with MySQL database — «Connection Java-MySql: Public Key Retrieval is not allowed«.
Problem
I have encountered this issue and below issue description.
I was using a MySQL database with Java using the mysql-connector 8.0.13 version. Everything seems to be correct but I have this exception:
Exception in thread "main" java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
Stack Trace:
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) Solution
We need to set AllowPublicKeyRetrieval=true to allow the client to automatically request the public key from the server.
allowPublicKeyRetrieval=true
You can also disable SSL and also suppress the SSL errors:
useSSL=falseComplete Code Snippet (Use this code)
private static String jdbcURL = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false&allowPublicKeyRetrieval=true"; private static String jdbcUsername = "root"; private static String jdbcPassword = "root";
Reference
- https://stackoverflow.com/questions/50379839/connection-java-mysql-public-key-retrieval-is-not-allowed
- https://mysqlconnector.net/connection-options/
JDBC
JDBC 4.2
MySQL
Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours
Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course
If you are connecting to MySQL version 8 from your java application, there are bright chances that you might have come across the following error
java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
and if you are accessing a MySQL database from a web or application server such as Wildfly or Tomcat, then you would have encountered this error
java.sql.SQLException: Cannot create PoolableConnectionFactory (Public Key Retrieval is not allowed)
Solution
Add allowPublicKeyRetrieval connection option of MySQL with a value of true to the JDBC connection string. Thus, your connection string should be modified as
jdbc:mysql://<database server ip>:3306/databaseName?allowPublicKeyRetrieval=true
Reason
Since you are connecting to a database, you will be using password authentication. Obviously, the password should be protected while transmitting over the network. By default Transport Layer Security(TLS) is used but if it cannot be used, then RSA public key encryption is used.
In order for the client to request for the public key from the database server, allowPublicKeyRetrieval option is used. But remember, using this option could expose the password to malicious attacks. That is why, this option is set to false by default.