com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure错误解决-Toy模板网

这篇具有很好参考价值的文章主要介绍了com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure错误解决。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

项目上线一段时间后，多个环境经常出现com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure错误，堆栈信息如下：
The last packet successfully received from the server was 10,003 milliseconds ago. The last packet sent successfully to the server was 10,003 milliseconds ago.
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.StatementImpl.executeQuery(StatementImpl.java:1200)
at com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker.isValidConnection(MySqlValidConnectionChecker.java:140)
at com.alibaba.druid.pool.DruidAbstractDataSource.validateConnection(DruidAbstractDataSource.java:1440)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1812)
at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2877)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 10,003 milliseconds ago. The last packet sent successfully to the server was 10,003 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.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:538)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:702)
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:641)
at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:940)
at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:886)
at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1073)
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1168)
… 4 common frames omitted
Caused by: java.net.SocketTimeoutException: Read timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:171)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:475)
at sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:469)
at sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:69)
at sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1228)
at sun.security.ssl.SSLSocketImpl.access $300 (SS L S oc k e t I m pl . ja v a : 75) a t s u n . sec u r i t y . ss l . SS L S oc k e t I m pl$ AppInputStream.read(SSLSocketImpl.java:915)
at java.io.FilterInputStream.read(FilterInputStream.java:133)
at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:532)
… 10 common frames omitted
经过多次排查和测试，终于排查到了原因。
出现问题的原因是因为我们的项目需要动态配置数据源，程序中根据需要即时动态创建DruidDataSource，没有设置SocketTimeout和ConnectTimeout参数，默认值都是0，有文章说：connectTimeout的默认值为0，表示驱动层面不设置超时时间，但这并不意味着不会超时，此时将由操作系统来决定超时时间。实际经过测试，当SocketTimeout和ConnectTimeout为默认值0时，两个超时时间可能都为10秒。
测试代码：

public static void main(String[] args) throws Exception {

    //创建数据源
    DruidDataSource druidDataSource = new DruidDataSource();
    druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8");
    druidDataSource.setUsername("admin");
    druidDataSource.setPassword("admin");
    druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    String querySql = "SELECT sleep(11)";	//执行语句，该行语句的执行时间≥11秒
    druidDataSource.setConnectionErrorRetryAttempts(3);       //失败后重连次数
    druidDataSource.setBreakAfterAcquireFailure(true);
    druidDataSource.setMaxWait(30 * 1000);	//保持的最长时间
    
    // 以下配置自动检测链接的可用性
    druidDataSource.setTestWhileIdle(true);	//明确指定检测空闲链接是否可用
    druidDataSource.setValidationQuery("select 1");	//执行该SQL来检测空闲链接，如果不配置，实际不会执行检测
    //如果链接空闲时长超过60秒则执行检测，周而复始。如果该时长少于MySQL数据变量：wait_timeout 会出现链接不可用的情况。
    druidDataSource.setTimeBetweenEvictionRunsMillis(60 * 1000);
    
    druidDataSource.setSocketTimeout(20 * 1000);//超时时长：20秒，该值要大于执行语句的执行时间
    druidDataSource.setConnectTimeout(20 * 1000);//超时时长：20秒，该值要大于执行语句的执行时间

    List<Map<String, Object>> result = JdbcUtils.executeQuery(druidDataSource, querySql, Lists.newArrayList());
    System.out.println(JSONUtilExt.toJson(result));
}

请注意测试代码中的这两行代码：
druidDataSource.setSocketTimeout(20 * 1000);
druidDataSource.setConnectTimeout(20 * 1000);
当我将这两行代码中的任意一行代码注释掉或两行代码都注释掉时，都会出现文章开始的错误，且错误日志相同。

分析到此，基本可以确定，程序里出现上面错误就是因为SocketTimeout和ConnectTimeout没有设置，导致操作系统里实际默认的时间是10秒，而因为项目已经上线一段时间，个别表里数据量比较大，所以在执行语句时偶尔会出现执行时间超过10秒的情况，连接超时。因此，根据需要，在创建数据源时，增加设置SocketTimeout和ConnectTimeout的大小就可以避免再次出现上述错误。文章来源地址https://www.toymoban.com/news/detail-722525.html

到了这里，关于com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure错误解决的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！