MySQLNonTransientConnectionException

背景

机房迁移后原始机房的Hive、Hadoop、MySQL和Redis等基础设施都开始逐步下线，DBA下线了某个MySQL实例时没有通知下游进而导致画像任务存储Metrics信息时日志抛出MySQL拒绝连接异常：com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:339)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2247)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2280)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.sql.DriverManager.getConnection(DriverManager.java:664)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.sql.DriverManager.getConnection(DriverManager.java:247)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.put2Mysql(MapReduceHelper.java:200)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.printMatrix(MapReduceHelper.java:136)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.dumpCounter(MapReduceHelper.java:81)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.dumpCounter(MapReduceHelper.java:60)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.runHdfs(CommitHadoopJob.java:522)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.run(CommitHadoopJob.java:382)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.run(CommitHadoopJob.java:362)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.main(CommitHadoopJob.java:540)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - Caused by: java.net.ConnectException: 拒绝连接 (Connection refused)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.net.PlainSocketImpl.socketConnect(Native Method)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at java.net.Socket.connect(Socket.java:589)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:214)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:298)
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - 	... 23 more
26-11-2021 15:43:59 CST user_proflie_related_work_flow_masking INFO - Communications link failure

库中存储的是指标数据而不是生产数据因此造成的影响并不大，而且被try … catch以后并不会影响任务本身的运行，只是看着不是很舒服。通过DBA申请了新的集群并在配置文件中应用新的链接，原本以为可以顺利切换但是由于MySQL Java驱动版本问题抛出新的异常：

26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.Util.getInstance(Util.java:383)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2326)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at java.sql.DriverManager.getConnection(DriverManager.java:664)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at java.sql.DriverManager.getConnection(DriverManager.java:247)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.put2Mysql(MapReduceHelper.java:200)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.printMatrix(MapReduceHelper.java:136)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.dumpCounter(MapReduceHelper.java:81)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.MapReduceHelper.dumpCounter(MapReduceHelper.java:60)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.runHdfs(CommitHadoopJob.java:522)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.run(CommitHadoopJob.java:382)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.run(CommitHadoopJob.java:362)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.company.package.masking.CommitHadoopJob.main(CommitHadoopJob.java:540)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - Caused by: java.lang.NullPointerException
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2999)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1885)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1814)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2249)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2280)
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - 	... 21 more
26-11-2021 16:41:00 CST user_proflie_related_work_flow_masking INFO - Could not create connection to database server.

问题定位

通过Navicat查看当前MySQL集群版本：

SELECT version()

结果显示8.0.24，查看Maven工程中引入的驱动版本：

<dependency> 
     <groupId>mysql</groupId> 
     <artifactId>mysql-connector-java</artifactId> 
     <version>5.1.36</version> 
</dependency>

MySQL驱动版本比Server版本低了不少，低版本驱动访问高版本MySQL集群时由于包名变更导致加载驱动失败进而抛出以上异常。

驱动升级

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.24</version>
</dependency>

除了将pom中驱动版本升级到8.0.24以外还需要注意一下，MySQL 8.0 以上版本的数据库连接有所不同，需要将com.mysql.jdbc.Driver变更com.mysql.cj.jdbc.Driver驱动才能正常加载。

参考文档

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-api-changes.html

转载请注明：雪后西塘 » MySQLNonTransientConnectionException