問題描述

springBoot項(xiàng)目集成druid的時(shí)候，系統(tǒng)間歇性的報(bào)CommunicationsException:communications link failure的異常如下。由于是間歇性的，即并不是每次都報(bào)異常，而是過了一段時(shí)間之后就會出現(xiàn)，所以針對性的測試修復(fù)難度很大。

2021-06-11T10:52:09.365Z  APP  2021-06-11 10:52:09.358 ERROR 1 --- [nio-8082-exec-2] c.a.d.p.DruidDataSource                  : discard connection
2021-06-11T10:52:09.365Z  APP  
2021-06-11T10:52:09.365Z  APP  com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
2021-06-11T10:52:09.365Z  APP  
2021-06-11T10:52:09.365Z  APP  The last packet successfully received from the server was 300,811 milliseconds ago.  The last packet sent successfully to the server was 300,812 milliseconds ago.
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2179) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:2163) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.ha.MultiHostMySQLConnection.setReadOnly(MultiHostMySQLConnection.java:520) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]
2021-06-11T10:52:09.365Z  APP   at sun.reflect.GeneratedMethodAccessor198.invoke(Unknown Source) ~[?:?]
2021-06-11T10:52:09.365Z  APP   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_281]
2021-06-11T10:52:09.365Z  APP   at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_281]
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.ha.FailoverConnectionProxy.invokeMore(FailoverConnectionProxy.java:530) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]
2021-06-11T10:52:09.365Z  APP   at com.mysql.cj.jdbc.ha.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:479) ~[mysql-connector-java-8.0.15.jar!/:8.0.15]

經(jīng)過研究一段時(shí)間之后，發(fā)現(xiàn)配置文件中的兩個(gè)配置要注意。test-while-idle 和 time-between-eviction-runs-millis，具體配置的說明如下。

#druid連接池配置
spring:
  datasource:
    db:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver    #高版本使用 com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhsot:3306/db?autoReconnect=true&useSSL=false&failOverReadOnly=false&maxReconnects=10
      username: xxxx
      password: xxxx
    druid:
      max-active: 100                #指定連接池中最大的活躍連接數(shù).
      initial-size: 10               #指定連接的初始值
      min-idle: 10                   #指定必須保持連接的最小值
      max-wait: 60000                #指定連接池等待連接返回的最大等待時(shí)間，設(shè)置1分鐘；默認(rèn)-1不限時(shí)間
      test-on-borrow: false          #獲取連接時(shí)候驗(yàn)證，會影響性能，默認(rèn)為false
      test-while-idle: true          #驗(yàn)證連接的有效性
      time-between-eviction-runs-millis: 300000     #空閑連接回收的時(shí)間間隔，與test-while-idle一起使用，設(shè)置5分鐘
      min-evictable-idle-time-millis: 1800000       #連接池空閑連接的有效時(shí)間 ，設(shè)置30分鐘
      validation-query: select 1
      remove-abandoned-timeout: 30   #隔30秒回收斷開的連接
      remove-abandoned: true         #當(dāng)連接超過了removeAbandonedTimout時(shí)間，刪除泄露的連接，默認(rèn)false
      log-abandoned: true            #當(dāng)Statement或連接被泄露時(shí)打印程序的stack traces日志
      filter:
        slf4j:
          enabled: true                    #開啟slf4j debug日志打印
          statement-log-enabled: false     #關(guān)閉statement相關(guān)debug日志打印
          result-set-log-enabled: false    #關(guān)閉result-set相關(guān)debug日志打印

如果想在開發(fā)環(huán)境復(fù)現(xiàn)這種問題的話，
首先設(shè)置mysql變量
set global interactive_timeout=30
set global wait_timeout=30;?。ǔ瑫r(shí)時(shí)間由28800改為30秒）
然后再把項(xiàng)目druid的配置文件test-while-idle=false
最后啟動(dòng)項(xiàng)目，第一次請求sql才會初始化連接池，等待30+秒，再次請求sql就會出現(xiàn)以上錯(cuò)誤（CommunicationsException異常也不是只有一種情況，這里解決的是java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.