记一次MySQL链接异常排查与解决

avatar
Raye Wang
1.6k
8min

起因程序代码中链接MySQL数据库时，提示异常，提示内容如下：

COPY 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
2023-09-14 11:05:30.207 ERROR 74928 --- [eate-2053481312] com.alibaba.druid.pool.DruidDataSource   : create connection SQLException, url: jdbc:mysql://xxxx:3306/xxx?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8&requireSSL=false&&autoReconnect=true&allowMultiQueries=true&connectTimeout=20000&rewriteBatchedStatements=true, errorCode 0, state 08001

java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:897) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:822) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:447) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:156) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:150) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.filter.FilterAdapter.connection_connect(FilterAdapter.java:787) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.filter.FilterEventAdapter.connection_connect(FilterEventAdapter.java:38) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:150) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1646) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1710) ~[druid-1.1.21.jar:1.1.21]
    at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2753) ~[druid-1.1.21.jar:1.1.21]
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_201]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_201]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_201]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_201]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:340) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeAuthenticationProvider.negotiateSSLConnection(NativeAuthenticationProvider.java:777) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:486) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:202) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1348) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.NativeSession.connect(NativeSession.java:163) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:841) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    ... 13 common frames omitted
Caused by: javax.net.ssl.SSLException: Received fatal alert: internal_error
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:208) ~[na:1.8.0_201]
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:154) ~[na:1.8.0_201]
    at sun.security.ssl.SSLSocketImpl.recvAlert(SSLSocketImpl.java:2020) ~[na:1.8.0_201]
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1127) ~[na:1.8.0_201]
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1367) ~[na:1.8.0_201]
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1395) ~[na:1.8.0_201]
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1379) ~[na:1.8.0_201]
    at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:316) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:188) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:99) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:331) ~[mysql-connector-java-8.0.17.jar:8.0.17]
    ... 19 common frames omitted

网上搜索了一圈，要么说防火墙问题，要么说服务没有打开或者连接数问题的，试了一圈下来，并没有解决，于是开始排查

排查点一

首先可以确定数据库服务本身是没有问题的，因为使用工具是能正常链接的

排查点二

项目代码问题，因为工具能正常链接，修改了URL链接到另外一个数据库，测试结果发现执行结果成功，说明跟代码中的jar包和代码本身没有太大关系

排查点三

上面排查了代码本身，但是忽略了一个问题就是2个MySQL本身的版本问题，于是查看版本，能链接的MySQL版本是：5.7.32  ，不能链接的版本是：5.7.43，2个版本相差并不大，并且我用的驱动版本是8的，按道理来说也不关驱动的问题

排查点四

由于其他方面都没有找到具体原因，最终还是回到错误日志本身来找原因，其他的错误日志本身看不出来任何问题，唯独其中一行日志：

COPY 
1
Caused by: javax.net.ssl.SSLException: Received fatal alert: internal_error

通过查询这行日志是因为证书原因导致的，但是数据库链接本身并没有强制使用SSL（requireSSL=false），所以这个日志也很奇怪，于是找了MySQL链接参数相关SSL配置的参数，尝试配置，发现加上配置：

COPY 
1
enabledTLSProtocols=TLSv1.2

后链接正常。但是至于为什么就可以正常使用了，目前还不清楚具体原因，因为如果是2个MySQL版本本身支持的TLS版本不一致导致的其中一个可以链接上另外一个不行，或者其中一个指定了TLS版本导致无法链接都可以理解，但是其实在2个MySQL上面执行

COPY 
1
SHOW VARIABLES LIKE 'tls_version'

返回的结果都是：TLSv1,TLSv1.1,TLSv1.2也就是2个版本本身对TLS版本支持是一致的，并且2个版本的配置文件本身都没有对TLS相关内容进行配置，所以实际上具体原因并不清楚，但是问题却解决了，只能有空仔细研究一下了

解决方案

在链接的URL上面加上参数enabledTLSProtocols=TLSv1.2则可以解决问题，并且目前没有发现因为这个配置产生的其他问题或者其他异常情况

Categories:
MySQL
Authorship: 作者
Article Link: https://raye.wang/2023/09/19/%E8%AE%B0%E4%B8%80%E6%AC%A1MySQL%E9%93%BE%E6%8E%A5%E5%BC%82%E5%B8%B8%E6%8E%92%E6%9F%A5%E4%B8%8E%E8%A7%A3%E5%86%B3/
Copyright: All posts on this blog are licensed under the CC BY-NC-SA 4.0 license unless otherwise stated. Please cite Raye Blog !