解决 ‘The last packet successfully received from the server was xxx milliseconds ago‘ 问题

左手的ㄟ右手 2023-10-10 17:42 169阅读 0赞

`springboot` \+ `druid` \+ `mysql` 在查询某些比较耗时的sql会报错

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
    
    The last packet successfully received from the server was 10,010 milliseconds ago. The last packet sent successfully to the server was 10,010 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.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    	at com.sun.proxy.$Proxy160.execute(Unknown Source)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    	... ...

查阅了一下，网上一般的做法有以下几种：

1.  修改mysql的`wait_timeout`，修改spring数据源配置，增加`maxWait`（要比数据库`wait_timeout`小）、`testOnBorrow=true`、`queryTimeout`等配置，在`datasource.url`后面增加`autoReconnect=true`配置。
2.  更新`mysql-connector`版本，我用的是`8.0.28`
3.  更新jdk版本，如`jdk1.7`升级到`jdk1.8`

尝试过上面的几个方法都没办法解决。

从报错信息来看，这里设置的超时是10000毫秒，然后把`mysql-connector`源码下载下来debug了一下，发现有`socketTimeout`参数的值正好是10000，然后尝试在spring数据源配置上增加次配置依旧不行。

最后在`datasource.url`后面`socketTimeout`参数配置，重启测试发现能被正确识别，到此问题解决。

下面是`druid`和`datasource`的配置，仅供参考

druid:
      initial-size: 5
      min-idle: 2
      maxActive: 10
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat,slf4j
      connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      
    datasource:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false&allowLoadLocalInfile=false&autoReconnect=true&failOverReadOnly=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&connectTimeout=1000&socketTimeout=30000
      username: root
      password: 123456

解决 ‘The last packet successfully received from the server was xxx milliseconds ago‘ 问题

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 The last packet successfully received from the server was xxx milliseconds ago. The last packet sen

相关解决 ‘The last packet successfully received from the server was xxx milliseconds ago‘ 问题

相关 The last packet successfully received from the server was XXX milliseconds ago 问题排查

相关 mysql连接池报错:The last packet successfully received from the server was XXXXX milliseconds ago

相关 The last packet successfully received from the server was 4,758 milliseconds ago

相关 The last packet successfully received from the server was 1,547,682,071 milliseconds ago. The last

相关【异常】The last packet successfully received from the server was 179 milliseconds ago.

相关 The last packet sent successfully to the server was 0 milliseconds ago.

相关 The last packet successfully received from the server was 8,268,658 milliseconds ago

相关连接数据库报错：The last packet successfully received from the server was 67 milliseconds ago.