Before Upgrading to MySQL 9.7, Check Authentication Plugins and TLS Configuration

MySQL 8.0 reaches end of life on April 30, 2026, and the Community Edition will stop shipping patches. Plenty of teams put it off until the end of the year — and the moment the test database is upgraded, the service won't start: Hikari initialization fails, and the log has a single line sitting in it: Public Key Retrieval is not allowed. No code changed, no data was lost; what's stuck is the account authentication layer. This post breaks down where the three most common errors come from, what actually happens during the handshake, and a self-check tool you can run right away.

1. Can't connect after the upgrade — where's the problem, really?

When your app can't connect after upgrading MySQL, nine times out of ten it's not a driver bug. It's that two things aren't aligned: the authentication plugin the account uses, and whether the connection is encrypted.

There are three kinds of errors. They look completely different, yet they all point to the same place:

The first one is the most typical. For projects created around 2019, accounts were basically set up like this:

CREATE USER 'app_rw'@'%' IDENTIFIED WITH mysql_native_password BY 'xxx'; 

Writing it that way back then wasn't laziness. We were on Connector/J 5.1.x at the time, and hitting caching_sha2_password just threw "unknown plugin," so we had no choice but to fall back to the old plugin. After the driver upgrade, the account record is still in mysql.user and the password hash is still there, but the server no longer has any code that can verify it. The plugin was uninstalled — the account is now a key with no lock to fit.

The second one is sneakier. It doesn't say "plugin doesn't exist"; it says "public key retrieval not allowed." It looks like a permissions issue, but it's actually a failure to negotiate an encrypted channel. I'll go into that in detail in section two.

The third one is the easiest to fool people. The plugin was fixed correctly, but ALTER USER missed BY '新密码', so the password hash stays untouched, login still fails, and the error looks exactly the same as a wrong password.

Why the symptom is "the service won't start" instead of "the API returns an error"

The connection pool amplifies a single-point failure into a startup failure.

Hikari's initializationFailTimeout default value is 1, meaning it immediately grabs a connection at startup and throws PoolInitializationException if it cannot get one. This exception is thrown during the DataSource initialization phase, so Spring context creation is interrupted directly and the process exits.

So what you're seeing is "service failed to start," not "some endpoint returned a 500." The easier thing to misjudge: you can log in just fine from the mysql command line. The command-line client and the JDBC driver don't use the same set of default parameters — the CLI tries TLS by default, while on the driver side someone may have long since put sslMode=DISABLED in the URL.

Start your investigation with these three questions, and don't skip a single one:

1. What plugin is this account using right now? SELECT user, host, plugin FROM mysql.user WHERE user = 'app_rw';
2. Is this connection actually encrypted? Once connected, run SHOW STATUS LIKE 'Ssl_cipher'; — an empty value means plaintext.
3. Does the server still have an RSA public key available? SHOW VARIABLES LIKE 'caching_sha2_password%';

II. What Actually Happens During the Authentication Handshake

MySQL 9.x only accepts caching_sha2_password, and the password must leave the client encrypted via TLS or the server's RSA public key. If neither path works, the connection is rejected.

First, let's clarify what a plugin is. An authentication plugin is a piece of validation logic on the server, and the client must be able to work with it to complete the challenge-response exchange. Switching plugins isn't flipping a switch; it's switching to an entirely different cryptographic protocol.

The mysql_native_password flow is short: the server sends a 20-byte random salt, the client computes SHA1 twice and sends it back, and the server compares the result. SHA1 is fast, and fast means it can be brute-forced in bulk on GPUs. This was good enough in the 2010s, but today it's basically running naked—authentication works even over an unencrypted connection, and captured responses can be cracked offline.

caching_sha2_password has been the default plugin since 8.0. It uses SHA256 and adds a caching layer on the server. The handshake has two paths:

1. Fast path: The server cache contains the hash for this account, so it uses direct challenge-response and completes in one round trip.
2. Full path: If the cache doesn't have it (first connection, server restart, cache flushed), the client must send the plaintext password, but it must be encrypted—over TLS, or by requesting the server's RSA public key and encrypting it itself.

allowPublicKeyRetrieval controls whether you can request the server’s public key on the second path, and it defaults to false. If it can’t get one, it throws Public Key Retrieval is not allowed.

Why is it disabled by default? Because the public key is sent in plaintext during the handshake. A man-in-the-middle can impersonate the server, hand you a public key of their own, and when you use it to encrypt your password, they simply decrypt it with the private key. It’s like a courier showing up to collect a package: if you don’t check their ID, you just hand it over.

The landmine hidden in sslMode’s default

sslMode defaults to PREFERRED, which means “encrypt if you can, otherwise don’t bother.”

MySQL 8.0 self-signs a set of certificates during initialization, so TLS works out of the box: caching_sha2_password goes over TLS and everything just works. But once the server turns off TLS, or your URL includes sslMode=DISABLED, the connection falls back to plaintext — that's when you need the RSA public key, and that's when the errors start showing up.

This is often why the same config runs fine against database A but blows up on database B. PREFERRED also permits downgrade: an attacker interrupts the TLS handshake and the connection automatically drops to plaintext — that's a downgrade attack. In production, use VERIFY_CA and enforce certificate verification; only then is the downgrade path closed off.

Here’s a side-by-side look at the three connection methods:

Hashes cannot be converted directly

mysql_native_password is disabled by default in 8.4 and completely removed in 9.0. 9.7 is the LTS release, supported through 2031, and the upgrade path is basically a direct jump from 8.0 to 9.7 — there's no buffer in between.

When migrating from native to sha2, password hashes can't be converted. You can't derive a SHA256 hash from a SHA1 hash, so every account has to be reset once with ALTER USER ... BY '明文密码'. Skip this step and no amount of plugin changes will let you log in.

This is also why the upgrade needs a maintenance window: you need the plaintext passwords for those accounts. If you don't have them, you'll have to change them in the database or go through the password recovery flow.

3. Hands-On: Building a Self-Check Tool That Actually Runs

Run this tool once before and once after the upgrade, and you'll see all four things at a glance — plugins, TLS, public keys, and the account list — without SSHing into the server to type ten different commands.

3.1 The Complete pom.xml

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> ​ <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>4.1.2</version> <relativePath/> </parent> ​ <groupId>com.lvdongbin</groupId> <artifactId>mysql-doctor</artifactId> <version>1.0.0</version> <name>mysql-doctor</name> ​ <properties> <java.version>21</java.version> </properties> ​ <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <version>9.7.0</version> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <version>4.1.2</version> <scope>test</scope> </dependency> </dependencies> ​ <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <version>4.1.2</version> </plugin> </plugins> </build> </project> 

The driver coordinates are com.mysql:mysql-connector-j, not the ancient mysql:mysql-connector-java:5.1.49. The latter stopped getting updates long ago and doesn't recognize the new parameters — using it to connect to 9.x is just a waste of time.

3.2 The Complete application.yml

server: port: 8080 ​ spring: application: name: mysql-doctor datasource: url: jdbc:mysql://127.0.0.1:3306/shop?sslMode=PREFERRED&connectionTimeZone=Asia/Shanghai username: app_rw password: ${DB_PASSWORD:change-me} driver-class-name: com.mysql.cj.jdbc.Driver hikari: pool-name: shop-pool maximum-pool-size: 8 minimum-idle: 2 connection-timeout: 3000 validation-timeout: 2000 initialization-fail-timeout: 10000 keepalive-time: 60000 ​ management: endpoints: web: exposure: include: health,info endpoint: health: show-details: always ​ logging: level: com.mysql.cj: INFO 

Two parameters in the URL are worth calling out.

sslMode=PREFERRED lets the driver negotiate TLS on its own. The 9.x server ships with a self-signed certificate, so negotiation will most likely succeed and passwords will travel over an encrypted channel.

connectionTimeZone=Asia/Shanghai is the new parameter that replaces serverTimezone. The TIMESTAMP type does conversion in the session time zone; after the server time zone changes from SYSTEM to UTC, configuring it with the old parameter will shift every report time by 8 hours. That kind of incident is far more painful than simply failing to connect.

initialization-fail-timeout: 10000 gives you a 10-second retry window, which pairs well with container orchestration startup probes. Setting it to -1 is a different story — that means "don't validate at startup," which hides the problem until the first real request.

3.3 The Main Self-Check Program (Plain JDBC, No Spring Context)

package com.lvdongbin.mysqldoctor; ​ import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; ​ public class AuthPluginDoctor { ​ private static final String HOST = "127.0.0.1"; private static final int PORT = 3306; private static final String DATABASE = "shop"; private static final String USER = "app_rw"; private static final String PASSWORD = "change-me"; ​ public static void main(String[] args) { String base = "jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE + "?connectionTimeZone=Asia/Shanghai"; ​ System.out.println("=== 1. 服务器与账号现状 ==="); try (Connection conn = DriverManager.getConnection(base + "&sslMode=PREFERRED", USER, PASSWORD)) { printVersion(conn); printTls(conn); printAccounts(conn); printRsaVars(conn); } catch (SQLException e) { System.out.println("[FAIL] 连默认配置都进不去：" + e.getMessage()); System.out.println(" 先确认账号存在、密码正确，再看下面的明文探测结果。"); } ​ System.out.println(); System.out.println("=== 2. 三种连接方式探测 ==="); probe(base, "TLS 优先", "&sslMode=PREFERRED"); probe(base, "明文 + 取公钥", "&sslMode=DISABLED&allowPublicKeyRetrieval=true"); probe(base, "明文不取公钥", "&sslMode=DISABLED"); } ​ private static void printVersion(Connection conn) throws SQLException { try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT VERSION(), CURRENT_USER()")) { if (rs.next()) { System.out.println("服务端版本：" + rs.getString(1)); System.out.println("当前身份 ：" + rs.getString(2)); } } } ​ private static void printTls(Connection conn) throws SQLException { try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SHOW STATUS LIKE 'Ssl_cipher'")) { if (rs.next()) { String cipher = rs.getString(2); boolean plain = cipher == null || cipher.isEmpty(); System.out.println("连接加密 ：" + (plain ? "空（明文在跑）" : cipher)); } } } ​ private static void printAccounts(Connection conn) throws SQLException { String sql = "SELECT user, host, plugin FROM mysql.user ORDER BY user, host"; List<String> needMigrate = new ArrayList<>(); int total = 0; try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { total++; String user = rs.getString(1); String host = rs.getString(2); String plugin = rs.getString(3); if (plugin == null || !"caching_sha2_password".equals(plugin)) { needMigrate.add(user + "@" + host + " -> " + plugin); } } } catch (SQLException e) { System.out.println("账号清单 ：查不了（缺 mysql 库读权限），换个管理账号跑这一条"); System.out.println(" 原始错误：" + e.getMessage()); return; } System.out.println("账号总数 ：" + total + "，需要迁移的：" + needMigrate.size()); for (String line : needMigrate) { System.out.println(" [待迁移] " + line); } } ​ private static void printRsaVars(Connection conn) throws SQLException { String sql = "SHOW VARIABLES LIKE 'caching_sha2_password%'"; try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { System.out.println(rs.getString(1) + " = " + rs.getString(2)); } } } ​ private static void probe(String base, String label, String extra) { String url = base + extra; long start = System.currentTimeMillis(); try (Connection conn = DriverManager.getConnection(url, USER, PASSWORD); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SHOW STATUS LIKE 'Ssl_cipher'")) { String cipher = rs.next() ? rs.getString(2) : null; long cost = System.currentTimeMillis() - start; String mode = (cipher == null || cipher.isEmpty()) ? "明文" : cipher; System.out.printf("[OK] %-12s 耗时 %3d ms，加密=%s%n", label, cost, mode); } catch (SQLException e) { System.out.printf("[FAIL] %-12s %s%n", label, e.getMessage()); } } } 

In main, first connect once using PREFERRED to get the version, encryption status, account list, and RSA configuration; then connect once with each of three URLs and print out directly "which combination works."

The third probe is guaranteed to fail — it reproduces the exact Public Key Retrieval is not allowed you see in production. It's left in on purpose so you can see with your own eyes what the error looks like: once you've seen it, you won't fail to recognize it in the logs next time.

printAccounts handles the downgrade: application accounts usually don't have read permission on the mysql database, and the query throws 1142. Here, instead of swallowing the exception, it prints the raw error and suggests running it with an admin account.

3.4 Application-Side Code: Not a Single Line Needs to Change

package com.lvdongbin.mysqldoctor; ​ import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; ​ @SpringBootApplication public class MysqlDoctorApplication { ​ public static void main(String[] args) { SpringApplication.run(MysqlDoctorApplication.class, args); } } 

package com.lvdongbin.mysqldoctor.order; ​ import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; ​ import java.util.List; ​ @Repository public class OrderRepository { ​ private final JdbcTemplate jdbcTemplate; ​ public OrderRepository(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } ​ public List<String> findRecentOrderNos(int limit) { String sql = "SELECT order_no FROM t_order ORDER BY id DESC LIMIT ?"; return jdbcTemplate.queryForList(sql, String.class, limit); } ​ public int countByStatus(String status) { String sql = "SELECT COUNT(*) FROM t_order WHERE status = ?"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class, status); return count == null ? 0 : count; } } 

package com.lvdongbin.mysqldoctor.order; ​ import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web<...