Analysis and Solutions for 'Public Key Retrieval is not allowed' Error in Java-MySQL Connections

Problem Background and Exception Analysis

When Java applications connect to MySQL 8.0 databases, developers frequently encounter the SQLNonTransientConnectionException with the message "Public Key Retrieval is not allowed". This error typically occurs when using newer versions of MySQL Connector/J (8.0.x series) to connect to MySQL 8.0 servers.

From a technical perspective, the root cause of this exception lies in MySQL 8.0's introduction of the new default authentication plugin caching_sha2_password, which replaces the previous mysql_native_password plugin. The new authentication mechanism requires public key exchange during connection establishment to encrypt password transmission, but by default, clients are not allowed to actively request the server's public key, leading to connection failures.

Technical Principles Deep Dive

MySQL 8.0's security enhancements include mandatory SSL/TLS encrypted connections and the adoption of more secure password authentication mechanisms. When a client attempts to connect, the server requires password authentication using public key encryption. If the client configuration does not allow public key retrieval, the connection process fails during the authentication phase.

Here's a typical exception stack trace example:

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.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
    // More stack trace information...

Solution Implementation

For this issue, the most direct and effective solution is to explicitly enable public key retrieval functionality in the database connection configuration. Here are several common implementation approaches:

Connection String Parameter Configuration

Adding relevant parameters directly in the JDBC connection URL is the simplest method:

String url = "jdbc:mysql://localhost:3306/biblioteka?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC";
Connection conn = DriverManager.getConnection(url, "anyroot", "anyroot");

Using MysqlDataSource Configuration

For scenarios using MysqlDataSource, this can be achieved by setting connection properties:

public class EnhancedConnectionManager {
    
    public static Connection getConnection() throws SQLException {
        MysqlDataSource dataSource = new MysqlDataSource();
        
        // Basic connection configuration
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("biblioteka");
        dataSource.setPortNumber(3306);
        dataSource.setUser("anyroot");
        dataSource.setPassword("anyroot");
        
        // Critical configuration parameters
        dataSource.setUseSSL(false);
        dataSource.setServerTimezone("UTC");
        
        // Set connection properties to allow public key retrieval
        Properties props = new Properties();
        props.setProperty("allowPublicKeyRetrieval", "true");
        dataSource.setConnectionProperties(props);
        
        return dataSource.getConnection();
    }
}

Database Tool Configuration (DBeaver Example)

For users employing database management tools like DBeaver, configuration can be done through the graphical interface:

1. Right-click the database connection and select "Edit Connection"
2. Click "Edit Driver Settings" in the connection settings interface
3. Navigate to the "Driver Properties" tab
4. Add or modify the following properties:
   • allowPublicKeyRetrieval = true
   • useSSL = false
5. Save the configuration and reconnect

Security Considerations and Best Practices

While enabling allowPublicKeyRetrieval can resolve connection issues, it's essential to fully understand its security implications:

Security Risks: Allowing public key retrieval may make connections vulnerable to Man-in-the-Middle (MITM) attacks, as malicious proxies could intercept and obtain plaintext passwords. This requires careful evaluation in production environments.

Recommended Practices:

• Use this configuration for quick problem resolution in development and testing environments
• Production environments should implement complete SSL/TLS certificate verification
• Consider upgrading to client versions that fully support the new authentication mechanism
• Regularly update MySQL Connector/J to the latest version

Advanced Configuration Options

For scenarios requiring higher security, consider the following alternative approaches:

Complete SSL Configuration

String url = "jdbc:mysql://localhost:3306/biblioteka?" +
            "useSSL=true&" +
            "requireSSL=true&" +
            "verifyServerCertificate=true&" +
            "clientCertificateKeyStoreUrl=file:/path/to/keystore&" +
            "clientCertificateKeyStorePassword=keystore_password&" +
            "trustCertificateKeyStoreUrl=file:/path/to/truststore&" +
            "trustCertificateKeyStorePassword=truststore_password";

Authentication Plugin Switching

If the environment permits, consider switching the MySQL user authentication plugin back to mysql_native_password:

ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Version Compatibility Notes

This issue primarily affects the following version combinations:

• MySQL Server 8.0+
• MySQL Connector/J 8.0.x
• Users employing the caching_sha2_password authentication plugin

Older MySQL 5.7 versions typically don't encounter this issue since they default to using the mysql_native_password authentication plugin.

Conclusion

The "Public Key Retrieval is not allowed" exception is a compatibility issue arising from MySQL 8.0's security enhancements. By properly configuring the allowPublicKeyRetrieval parameter, developers can quickly resolve connection problems but must balance security risks. For long-term solutions, implementing complete SSL configuration or upgrading to client environments that fully support the new authentication mechanism is recommended.