MySql连接超时问题：execute error. SELECT 1 ，CommunicationsException

场景

服务访问量过小，长时间不访问数据库，日志报错。

数据库使用的5.7，使用mycat作为中间件，两主多从数据库。

execute error. SELECT 1

com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 2,629,594 milliseconds ago. The last packet sent successfully to the server was 2,629,594 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

中文翻译：

最后一个成功从服务器接收到的数据包是2,629,594毫秒之前。最后一个成功发送到服务器的数据包是在2,629,594毫秒之前。比服务器配置的'wait_timeout'值长。您应该在应用程序中使用连接之前考虑过期和/或测试连接有效性，增加服务器配置的客户端超时值，或者使用Connector/J连接属性“autoReconnect=true”来避免此问题。

配置

# spring配置
spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      druid:
        #初始化时建立物理连接的个数
        initial-size: 5
        #最小连接池数量
        min-idle: 5
        #最大连接池数量
        maxActive: 20
        # 获取连接时最大等待时间，单位毫秒
        maxWait: 60000
        # 验证连接是否有效的时间周期
        timeBetweenEvictionRunsMillis: 60000
        # 最小空闲时间
        minEvictableIdleTimeMillis: 300000
	    # 用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为nulltestOnBorrow、testOnReturn、testWhileIdle都不会其作用
        validationQuery: SELECT 1
        #申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
        testWhileIdle: true
        # 申请连接时执行validationQuery检测连接是否有效
        testOnBorrow: false
        # 归还连接时执行validationQuery检测连接是否有效
        testOnReturn: false
        #是否缓存preparedStatement，也就是PSCache
        poolPreparedStatements: true
        #	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
        maxPoolPreparedStatementPerConnectionSize: 20
        #属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j，防御sql注入的filter:wall
        filters: stat,slf4j
        # 配置扩展属性，用于监控统计分析SQL性能等
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasource:
        # 主库数据源
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:8066/student?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: mycat
          password: 123456

描述

根据报错信息，发现查询报错execute error. SELECT 1 

validationquery的作用是在连接池申请连接时进行验证，以保证连接池中的连接都是可用的。

查找原因

MySQL数据库一般默认的连接等待时间为28800s(即8小时)，超过8h，数据库会断开这个连接，但是应用不知道这个连接已经被数据库断开，所以链接池认为该连接是有效的，在下次请求时，连接池会把自己认为有效，但是数据库认为无效的连接分配出去，便会报错。

查询数据库的超时时间：

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

发现超时时间为10分钟，远远小于数据库默认的超时时间（8小时）。

解决方案

目前网上的方案主要是：

1、数据库连接添加autoReconnect=true，发现不管用。

2、延长数据库连接的超时时间，如果有较长的MySQL服务器的连接资源，线程堆积，从而导致服务器的性能下降。

3、上面两种方案都不可取。

可以使用druid设置线程最大空闲时间，默认7小时。

添加maxEvictableIdleTimeMillis配置，小于数据库连接的超时时间（wait_timeout），在数据库关连接超时之前，druid关闭此连接。

修改后的配置如下：

# spring配置
spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 240000


        # 设置线程池线程的最大空闲时间（毫秒），连接空闲时间大于该值，不管minIdle都关闭该连接
        maxEvictableIdleTimeMillis: 480000


        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,slf4j
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasource:
        # 主库数据源
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:8066/student?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: mycat
          password: 123456

• minEvictableIdleTimeMillis：最小空闲时间，默认30分钟，如果连接池中非运行中的连接数大于minIdle，并且那部分连接的非运行时间大于minEvictableIdleTimeMillis，则连接池会将那部分连接设置成Idle状态并关闭；也就是说如果一条连接30分钟都没有使用到，并且这种连接的数量超过了minIdle，则这些连接就会被关闭了。
• maxEvictableIdleTimeMillis：最大空闲时间，默认7小时，如果minIdle设置得比较大，连接池中的空闲连接数一直没有超过minIdle，这时那些空闲连接是不是一直不用关闭？当然不是，如果连接太久没用，数据库也会把它关闭，这时如果连接池不把这条连接关闭，系统就会拿到一条已经被数据库关闭的连接。为了避免这种情况，Druid会判断池中的连接如果非运行时间大于maxEvictableIdleTimeMillis，也会强行把它关闭，而不用判断空闲连接数是否小于minIdle；

参考链接：https://www.jianshu.com/p/be9dbe640daf