测试环境数据库从 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.<init>(ConnectionImpl.java:768)
at com.mysql.jdbc.JDBC4Connection.<init>(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
所以就算握手失败了哦. 那么这是发生在哪一阶段的呢?
之前有讲过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只会降低性能)
服务端禁用ssl. 在配置文件添加skip_ssl
即可. (永除后患).
skip_ssl
应用连接的时候, 连接串加上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
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MySQLConnTest57{
public static void main(String[] args) {
String url = "jdbc:mysql://192.168.101.202:3306/db1?useSSL=false";
//String url = "jdbc:mysql://192.168.101.202:3306/db1?";
String username = "ddcw";
String password = "123456";
System.out.println("Connecting...");
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 # 指定驱动包路径(方便测试版本)
基本上都是之前给过的, 这次只有一丢丢改变
t20240717.py
import hashlib
import socket
import struct
import os
#来自pymysql
def _lenenc_int(i):
if i < 0:
raise ValueError("Encoding %d is less than 0 - no representation in LengthEncodedInteger" % i)
elif i < 0xFB:
return bytes([i])
elif i < (1 << 16):
return b"\xfc" + struct.pack("<H", i)
elif i < (1 << 24):
return b"\xfd" + struct.pack("<I", i)[:3]
elif i < (1 << 64):
return b"\xfe" + struct.pack("<Q", i)
else:
raise ValueError("Encoding %x is larger than %x - no representation in LengthEncodedInteger"% (i, (1 << 64)))
def native_password(password,salt):
stage1 = hashlib.sha1(password).digest()
stage2 = hashlib.sha1(stage1).digest()
rp = hashlib.sha1(salt)
rp.update(stage2)
result = bytearray(rp.digest())
for x in range(len(result)):
result[x] ^= stage1[x]
return result
def _read_lenenc(bdata,i):
length = btoint(bdata[i:i+1])
i += 1
data = bdata[i:i+length]
i += length
return data,i
def btoint(bdata,t='little'):
return int.from_bytes(bdata,t)
class mysql(object):
def __init__(self):
self.host = '192.168.101.202'
self.port = 3306
self.user = 'ddcw'
self.password = '123456'
def read_pack(self,):
pack_header = self.rf.read(4)
btrl, btrh, packet_seq = struct.unpack("<HBB", pack_header)
pack_size = btrl + (btrh << 16)
self._next_seq_id = (self._next_seq_id + 1) % 256
bdata = self.rf.read(pack_size) #也懒得考虑超过16MB的包了
return bdata
def write_pack(self,data):
#3字节长度, 1字节seq, data
bdata = struct.pack("<I", len(data))[:3] + bytes([self._next_seq_id]) + data
self.sock.sendall(bdata)
self._next_seq_id = (self._next_seq_id + 1) % 256
def handshake(self,bdata):
i = 0 #已经读取的字节数, 解析binlog的时候也是这么用的.....
protocol_version = bdata[:1] #只解析10
server_end = bdata.find(b"\0", i)
self.server_version = bdata[i:server_end]
i = server_end + 1
self.thread_id = btoint(bdata[i:i+4])
i += 4
self.salt = bdata[i:i+8]
i += 9 #还有1字节的filter, 没啥意义,就不保存了
self.server_capabilities = btoint(bdata[i:i+2])
i += 2
self.server_charset = btoint(bdata[i:i+1])
i += 1
self.server_status = btoint(bdata[i:i+2])
i += 2
self.server_capabilities |= btoint(bdata[i:i+2]) << 16 #往左移16位 为啥不把capability_flags_1和capability_flags_2和一起呢
i += 2
salt_length = struct.unpack('<B',bdata[i:i+1])[0] #懒得去判断capabilities & CLIENT_PLUGIN_AUTH了
salt_length = max(13,salt_length-8) #前面已经有8字节了
i += 1
i += 10 #reserved
self.salt += bdata[i:i+salt_length]
i += salt_length
self.server_plugname = bdata[i:]
def HandshakeResponse41(self,):
client_flag = 3842565 #不含DBname
CLIENT_SSL = 1 << 11
client_flag &= CLIENT_SSL # 加上SSL
#client_flag |= 1 << 3
charset_id = 45 #45:utf8mb4 33:utf8
#bdata = client_flag.to_bytes(4,'little') #其实应该最后在加, 毕竟还要判断很多参数, 可能还需要修改, 但是懒
bdata = struct.pack('<iIB23s',client_flag,2**24-1,charset_id,b'')
bdata += self.user.encode() + b'\0'
auth_password = native_password(self.password.encode(), self.salt[:20])
auth_response = _lenenc_int(len(auth_password)) + auth_password
bdata += auth_response
bdata += b"mysql_native_password" + b'\0'
#本文有设置连接属性, 主要是为了方便观察
attr = {'_client_name':'ddcw_for_pymysql', '_pid':str(os.getpid()), "_client_version":'0.0.1',}
#key长度+k+v长度+v
connect_attrs = b""
for k, v in attr.items():
k = k.encode()
connect_attrs += _lenenc_int(len(k)) + k
v = v.encode()
connect_attrs += _lenenc_int(len(v)) + v
bdata += _lenenc_int(len(connect_attrs)) + connect_attrs
self.write_pack(bdata)
auth_pack = self.read_pack() #看看是否连接成功
if auth_pack[:1] == b'\0':
print('OK',)
else:
print('FAILED',auth_pack)
def query(self,sql):
"""不考虑SQL超过16MB情况"""
# payload_length:3 sequence_id:1 payload:N
# payload: com_query(0x03):1 sql:n
bdata = struct.pack('<IB',len(sql)+1,0x03) #I:每个com_query的seq_id都从0开始,第4字节固定为0, 所以直接用I, +1:com_query占用1字节, 0x03:com_query
bdata += sql.encode()
self.sock.sendall(bdata)
self._next_seq_id = 1 #下一个包seq_id = 1
def result(self):
#https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response_text_resultset_column_definition.html
#Protocol::ColumnDefinition41
#字段数量
stat = self.read_pack()
filed_count = struct.unpack('<B',stat)[0] #不考虑0xFF(error) 0xFB(字段太多) 0x00(无返回数据,就是成功)
#字段描述(字段数据类型)
des_list = []
for x in range(filed_count):
i = 0
bdata = self.read_pack()
catalog,i = _read_lenenc(bdata,i)
schema,i = _read_lenenc(bdata,i)
table,i = _read_lenenc(bdata,i)
org_table,i = _read_lenenc(bdata,i)
name,i = _read_lenenc(bdata,i)
org_name,i = _read_lenenc(bdata,i)
i += 1 #0x0c
character_set = btoint(bdata[i:i+2])
i += 2
column_length = btoint(bdata[i:i+4])
i += 4
_type = btoint(bdata[i:i+1]) #只解析int和str, 之前解析binlog的时候还有date.... 算了
i += 1
flags = btoint(bdata[i:i+2])
i += 2
decimals = btoint(bdata[i:i+1])
i += 1
des_list.append([catalog,schema,table,org_table,name,org_name,character_set,column_length,_type,flags,decimals])
self.des_list = des_list
bdata = self.read_pack() #EOF包
warnings = btoint(bdata[1:3])
row = []
while True:
bdata = self.read_pack()
if bdata[0:1] == b'\xfe': #EOF包
break
_row = []
i = 0
for x in des_list:
length = btoint(bdata[i:i+1]) #不考虑长字符
i += 1
_row.append(bdata[i:i+length]) #懒得做数据类型转换了
row.append(_row)
print(f'warnings:{warnings} rows:{len(row)}')
return row
def connect(self):
sock = socket.create_connection((self.host, self.port))
sock.settimeout(None)
self.sock = sock
self.rf = sock.makefile("rb")
self._next_seq_id = 0
#解析server的握手包
bdata = self.read_pack()
self.handshake(bdata)
#握手.发账号密码
self.HandshakeResponse41()
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。