Communications link failure

参考声明:https://blog.csdn.net/zzhongcy/article/details/124488799

1.错误现象

最近升级Springboot 2.6.2到Springboot 2.6.7，

mysql版本： mysql-5.7.27

DB配置：

spring.datasource.url=jdbc:mysql://IP:3306/testmysql?useUnicode=true&characterEncoding=UTF8&allowMultiQueries=true&useSSL=true

发现下面错误：

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago.
The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:829)
at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:449)
...
...
...
Caused by: javax.net.ssl.SSLHandshakeException: Remote host terminated the handshake
at sun.security.ssl.SSLSocketImpl.handleEOF(SSLSocketImpl.java:1310)
at sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1151)
at sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1054)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:394)
at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:347)
at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:194)
at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:101)
at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:308)
... 90 common frames omitted
Suppressed: java.net.SocketException: Broken pipe (Write failed)
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
at sun.security.ssl.SSLSocketOutputRecord.encodeAlert(SSLSocketOutputRecord.java:81)
at sun.security.ssl.TransportContext.fatal(TransportContext.java:355)
at sun.security.ssl.TransportContext.fatal(TransportContext.java:267)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:397)
... 94 common frames omitted
Caused by: java.io.EOFException: SSL peer shut down incorrectly
at sun.security.ssl.SSLSocketInputRecord.decodeInputRecord(SSLSocketInputRecord.java:239)
at sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:190)
at sun.security.ssl.SSLTransport.decode(SSLTransport.java:108)
at sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1143)
... 96 common frames omitted
2022-04-28 15:01:31.042 [http-nio-5252-exec-10] ERROR c.l.n.s.controller.AdminController - findById Exception: Could not open JDBC Connection for transaction; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

2.原因：

查看错误关键字"Communications link failure"和"SSL peer shut down incorrectly"，发现应该是建立ssl链接出现问题。于是查了查资料。

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.

原因：修改是因为根据MySQL 5.5.45+、5.6.26+和5.7.6+的要求，为了数据的安全及维护，如果不设置显式选项，则必须建立默认的SSL连接（True），改成false关掉就好，删除的话是不建议在没有服务器身份验证的情况下建立 SSL 连接。

jdk版本过高会引起这种错误;查了很多资料，发现在MySQL5.7之前的版本，安全性较低，存在任何用户都可以连接上的 test 库，所以官方在5.7版本加大了对隐私的保护。并且采用了默认 useSSL = true值防止对数据库的随意修改，到了8.0版本，仍然保留了SSL，并且默认值为 true，所以只要将 “?useSSL= false” 放在url表名后即可。

解决方案在mysql路径后边加上 &useSSL=false 问题即可解决;

参考一下分析步骤，错误根本描述就是通信异常，数据库连接池创建失败。

排查步骤1：判断数据库是否正常，我使用工具MySQL Workbench和Navicat都可以正常连接上，确认数据库是没有问题的。
排查步骤2：判断MySQL版本兼容问题。程序中mysql-connector-java是8.0.13版本，driver-class-name是com.mysql.cj.jdbc.Driver。在MySQL Workbench里使用命令select version()查看MySQL版本，是5.7.28，这种情况下兼容是存在问题，driver-class-name只有在8.0版本才能使用com.mysql.cj.jdbc.Driver，而8.0以下只能使用com.mysql.jdbc.Driver。
排查步骤3：把mysql-connector-java降级到5.1.30（这是我常用的版本，MySQL5的与java的jar包版本不是一一对应的，可以相互兼容），driver-class-name改成com.mysql.jdbc.Driver。再次启动，还是报错。
排查步骤4：检查数据库连接参数：useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8，把这些参数全去掉，再一个一个添加上，逐一判断是那些的错误，最后断定是useSSL=true参数的问题。因为MySQL在高版本需要指明是否进行SSL连接，MySQL5是不需要这个参数的。

　　把上面的步骤执行一遍之后，就可以正常访问数据库了。

3. 解决：

解决方案1：升级mysql版本到8.0.22

升级8.0.22后链接正常。

解决方案2：mysql-connector-java降级

　　mysql-connector-java降级到5.1.30

　　driver-class-name改成com.mysql.jdbc.Driver

　　因为MySQL5，所以数据库连接参数去掉useSSL

参考:

mysql - Spring Boot: Jdbc javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify - Stack Overflow

解觉方案3：添加useSSL=false

　　如果是内网服务器访问内网DB，不存在安全问题，那完全可以改为useSSL=false调高效率。

如果不想更换mysql-connector-java版本，可以尝试把useSSL设置成false，也可以解决。

在数据库链接后添加&usessl=false,变成这样 jdbc:mysql://x.x.x.x:xxx/xxxx?useUnicode=true&characterEncoding=utf8&useSSL=false,这也是最快的解决方法，但不算彻底，因为我的项目为多人项目，而其他人都没有这问题，也没必要单独为我的原因添加这个后缀。

解觉方案4：更换JDK版本
通过描述，这是因为ssl端口问题，数据库连不上，但我这边多人项目，其他人都能链接，而只有我不能连接，说明不是数据库问题，最后定位在jdk问题，而我跟其他人一样，也是用的jdk1.8，通过对比同事的jdk版本，发现我是jdk 1.8.0_291，而同事的版本是 jdk 1.8.0_131，我就替换同事jdk，版本试验，发现启动就没问题了。

所以，遇到这种情况也可改变JDK版本号试试。

4.其他错误

4.1 No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

项目场景：
CentOS 7.3

JDK: openjdk version “1.8.0_302”

Mysql 5.7.35

问题描述：
数据库链接错误

Communications link failure

Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_302]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_302]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_302]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_302]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:351) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.negotiateSSLConnection(NativeAuthenticationProvider.java:777) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:486) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:202) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1442) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.NativeSession.connect(NativeSession.java:165) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:955) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
... 6 common frames omitted
Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
at sun.security.ssl.HandshakeContext.(HandshakeContext.java:171) ~[na:1.8.0_302]
at sun.security.ssl.ClientHandshakeContext.(ClientHandshakeContext.java:98) ~[na:1.8.0_302]
at sun.security.ssl.TransportContext.kickstart(TransportContext.java:220) ~[na:1.8.0_302]
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:428) ~[na:1.8.0_302]
at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:315) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:188) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:99) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:347) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
... 13 common frames omitted

原因分析：
没有支持的SSL协议

因为我数据库配置时是要用SSL的

url: jdbc:mysql://localhost:3306/db_xxx?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=CTT

解决方案：
方案一连接时关闭useSSL 即设置为useSSL=false
url: jdbc:mysql://localhost:3306/db_xxx?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=CTT

方案二 JDK
找到jdk/jre/lib/security/java.security文件

（centos7.3 路径是/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.302.b08-0.el7_9.x86_64/jre/lib/security）

修改

# jdk.tls.disabledAlgorithms=MD5, SSLv3, DSA, RSA keySize < 2048
jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA,
DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL,
include jdk.disabled.namedCurves

# jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA,
jdk.tls.disabledAlgorithms=RC4, DES, MD5withRSA,
DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL,
include jdk.disabled.namedCurves

保存再重启项目即可