问题描述
springBoot项目集成druid的时候，系统间歇性的报CommunicationsException:communications link failure的异常如下。由于是间歇性的，即并不是每次都报异常，而是过了一段时间之后就会出现，所以针对性的测试修复难度很大。
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]
经过研究一段时间之后，发现配置文件中的两个配置要注意。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 #指定连接池中最大的活跃连接数.
initial-size: 10 #指定连接的初始值
min-idle: 10 #指定必须保持连接的最小值
max-wait: 60000 #指定连接池等待连接返回的最大等待时间，设置1分钟；默认-1不限时间
test-on-borrow: false #获取连接时候验证，会影响性能，默认为false
test-while-idle: true #验证连接的有效性
time-between-eviction-runs-millis: 300000 #空闲连接回收的时间间隔，与test-while-idle一起使用，设置5分钟
min-evictable-idle-time-millis: 1800000 #连接池空闲连接的有效时间 ，设置30分钟
validation-query: select 1
remove-abandoned-timeout: 30 #隔30秒回收断开的连接
remove-abandoned: true #当连接超过了removeAbandonedTimout时间，删除泄露的连接，默认false
log-abandoned: true #当Statement或连接被泄露时打印程序的stack traces日志
filter:
slf4j:
enabled: true #开启slf4j debug日志打印
statement-log-enabled: false #关闭statement相关debug日志打印
result-set-log-enabled: false #关闭result-set相关debug日志打印
如果想在开发环境复现这种问题的话，
首先设置mysql变量
set global interactive_timeout=30
set global wait_timeout=30; （超时时间由28800改为30秒）
然后再把项目druid的配置文件test-while-idle=false
最后启动项目，第一次请求sql才会初始化连接池，等待30+秒，再次请求sql就会出现以上错误（CommunicationsException异常也不是只有一种情况，这里解决的是java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
