Springboot - [12] Question & Answer

题记部分

一、Public Key Retrieval is not allowed

百度翻译：不允许进行公钥检索

（1）报错信息

java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na]
    
......

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
### The error may exist in com/manulife/mapper/SyncTblConfigMapper.java (best guess)
### The error may involve com.manulife.mapper.SyncTblConfigMapper.selectList
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:97)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
	at com.sun.proxy.$Proxy67.selectList(Unknown Source)

（2）原因解析

MySQL服务器配置不允许从远程客户端检索公钥。

（3）解决方法

① 禁用SSL连接：如果不需要SSL连接，可以在application.properties或application.yml文件中添加以下配置

spring.datasource.tomcat.jdbc-url=jdbc:mysql://localhost:3306/yourdb?useSSL=false&serverTimezone=UTC

或者在YAML文件中：

spring:
  datasource:
    tomcat:
      jdbc-url: jdbc:mysql://localhost:3306/yourdb?useSSL=false&serverTimezone=UTC

② 配置SSL证书：如果需要使用SSL连接，确保你已经配置了正确的SSL证书，并且在数据库连接字符串中指定了相关参数

spring.datasource.tomcat.jdbc-url=jdbc:mysql://localhost:3306/yourdb?useSSL=true&requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.tomcat.username=root
spring.datasource.tomcat.password=password
spring.datasource.tomcat.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.tomcat.validation-query=SELECT 1

同时，你需要在系统中安装相应的SSL证书，并将其路径添加到JDBC连接URL中：

spring.datasource.tomcat.jdbc-url=jdbc:mysql://localhost:3306/yourdb?useSSL=true&requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useServerPrepStmts=true&rewriteBatchedStatements=true&cachePrepStmts=true&useAffectedRows=true&sslProtocol=TLSv1.2&sslTrustStore=/path/to/truststore.jks&sslTrustStorePassword=yourtruststorepassword

③ MySQL服务器端配置

检查MySQL服务器配置（my.cnf或my.ini文件），确保公钥检索，或者确认是否真的需要这个功能。如果问题依然存在，检查数据库用户是否有足够的权限进行操作，以及网络防火墙设置是否阻止了必要的通信。如果是在云服务上运行，还需要检查云服务提供商的安全组规则和VPC设置。

二、标题

三、标题

— 业精于勤荒于嬉，行成于思毁于随 —