[MYSQL] mysql升级后, 应用连不上, 报错 Bad handshake

问题

测试环境数据库从 5.7.27 升级到 5.7.44之后, 应用发现连不上数据库了.

程序侧报错如下(好它喵的长):

xception in thread "main" java.lang.IllegalStateException: Cannot connect to the database!
at MySQLConnTest57.main(MySQLConnTest57.java:18)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 308 milliseconds ago. The last packet sent successfully to the server was 304 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:202)
at com.mysql.jdbc.MysqlIO.negotiateSSLConnection(MysqlIO.java:4869)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1656)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1217)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:768)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at MySQLConnTest57.main(MySQLConnTest57.java:15)
Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1964)
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:328)
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:322)
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1614)
at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1052)
at sun.security.ssl.Handshaker.process_record(Handshaker.java:987)
at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1072)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1385)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1413)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1397)
at com.mysql.jdbc.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:187)
... 18 more
Caused by: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
at com.mysql.jdbc.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:303)
at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:985)
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1596)
... 26 more
Caused by: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
at sun.security.provider.certpath.PKIXCertPathValidator.validate(PKIXCertPathValidator.java:154)
at sun.security.provider.certpath.PKIXCertPathValidator.engineValidate(PKIXCertPathValidator.java:80)
at java.security.cert.CertPathValidator.validate(CertPathValidator.java:292)
at com.mysql.jdbc.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:296)
... 28 more

查看数据库日志, 发现大量报错如下:

2024-07-17T09:47:48.275413+08:00 112 [Note] Bad handshake
2024-07-17T09:47:48.837325+08:00 113 [Note] Bad handshake
2024-07-17T09:47:49.439363+08:00 114 [Note] Bad handshake
2024-07-17T09:47:50.039733+08:00 115 [Note] Bad handshake
2024-07-17T09:47:50.532872+08:00 116 [Note] Bad handshake
2024-07-17T09:47:51.254110+08:00 117 [Note] Bad handshake
2024-07-17T09:47:51.800288+08:00 118 [Note] Bad handshake
2024-07-17T09:47:52.398900+08:00 119 [Note] Bad handshake
2024-07-17T09:47:52.924803+08:00 120 [Note] Bad handshake
2024-07-17T09:47:53.531595+08:00 121 [Note] Bad handshake


分析过程

1. 从程序报错看是ssl证书问题导致握手失败: javax.net.ssl.SSLHandshakeException
2. 从mysql的error日志看也是握手失败.

所以就算握手失败了哦. 那么这是发生在哪一阶段的呢?

之前有讲过mysql的ssl连接过程: https://cloud.tencent.com/developer/article/2245416

也就是在client回复握手包的时候发生的. 即客户端声明了要使用ssl包, 但又不做ssl认证(表里不一). 这种情况在pymysql之类的第三方包是不会发生的.但官方提供的java驱动包却有这个问题…

复现

好巧不巧, 我们之前有mysql连接脚本. 我们可以直接使用: https://cloud.tencent.com/developer/article/2242582

只需要声明要使用ssl协议即可, 即修改Capabilities Flags的CLIENT_SSL即可. 完整的Capabilities Flags信息可看: https://cloud.tencent.com/developer/article/2243951

即在HandshakeResponse41添加如下代码即可

引入的包t20240717见文末

然后我们测试下

import t20240717
aa = t20240717.mysql()
aa.connect()
aa.query('select aa.id as sb,aa.name from db1.t1 as aa limit 4')
for x in aa.result():
print(x)
print(aa.des_list)


做连接的时候就已经返回 Bad handshake了. 我们再查看数据库日志, 也能找到这个报错

python对异常的处理确实比java要好一些(至少不是一大堆信息…)-_-

证明确实是声明了ssl(java驱动默认). 但又不使用ssl (未指定证书路径). 既然确认了原因, 那么就好处理了.

解决

解决方法主要是设置ssl证书信息或者取消ssl. 这里选择后者.(内网使用ssl只会降低性能)

方法1

服务端禁用ssl. 在配置文件添加skip_ssl即可. (永除后患).

skip_ssl


方法2

应用连接的时候, 连接串加上useSSL=false即可(难为开发了). 比如:

String url = "jdbc:mysql://192.168.101.202:3306/db1?useSSL=false";


总结

之前解析的mysql连接协议再一次用上了. 平时的一下看起来没diao用的知识,早晚会用到.

后续思考: 那么是从哪一个版本开始服务端默认使用(open)ssl了呢?

参考:
https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html
https://cloud.tencent.com/developer/article/2245416
https://cloud.tencent.com/developer/article/2242582
https://cloud.tencent.com/developer/article/2243951

附源码

java测试连接的源码如下

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

try (Connection connection = DriverManager.getConnection(url, username, password)) {
System.out.println("Connected successfully!");
} catch (SQLException e) {
throw new IllegalStateException("Cannot connect to the database!", e);
}
}
}


使用方法参考:

vim MySQLConnTest57.java # 写入上面的源码
javac MySQLConnTest57.java # 生成字节码
java -cp .:mysql-connector-java-5.1.49.jar MySQLConnTest57 # 指定驱动包路径(方便测试版本)


python测试连接的源码如下

基本上都是之前给过的, 这次只有一丢丢改变

t20240717.py

import hashlib
import socket
import struct
import os