springboot 集成druid 报错Communications link failure

最近线上的定时任务出现一个问题，晚上12点执行的时候出现数据库连接失败，具体堆栈信息如下：

2019-12-03 22:16:00.208 ERROR 24832 --- [ryBean_Worker-1] c.a.druid.pool.DruidPooledStatement      : CommunicationsException, druid version 1.1.16, jdbcUrl : jdbc:mysql://localhost:3306/openplatform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=PRC, testWhileIdle true, idle millis 31126, minIdle 0, poolingCount 0, timeBetweenEvictionRunsMillis 60000, lastValidIdleMillis 31126, driver com.mysql.cj.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter
2019-12-03 22:16:00.214 ERROR 24832 --- [ryBean_Worker-1] com.alibaba.druid.pool.DruidDataSource   : discard connection

com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 31,132 milliseconds ago.  The last packet sent successfully to the server was 31,148 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.
	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.ConnectionImpl.isReadOnly(ConnectionImpl.java:1429)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1408)
	at com.mysql.cj.jdbc.ClientPreparedStatement.checkReadOnlySafeStatement(ClientPreparedStatement.java:324)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:334)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
	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.$Proxy105.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	at com.sun.proxy.$Proxy87.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
	at com.sun.proxy.$Proxy92.insertSelective(Unknown Source)
	at cn.newhope.batch.service.impl.JobResultServiceImpl.insert(JobResultServiceImpl.java:19)
	at cn.newhope.batch.job.AbstractJob.execute(AbstractJob.java:105)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 31,132 milliseconds ago.  The last packet sent successfully to the server was 31,148 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.
	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:562)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:732)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:671)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:132)
	at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:321)
	at com.mysql.cj.NativeSession.queryServerVariable(NativeSession.java:1079)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1416)
	... 31 common frames omitted
Caused by: java.net.SocketException: Software caused connection abort: recv failed
	at java.net.SocketInputStream.socketRead0(Native Method)

这个报错，从网上看大致原因是因为应用从数据库获取到的连接是数据库单方面断开的失效连接，所以导致连接失败，但是正常来说这种情况是不会发生的，因为数据库连接池参数有加校验，
springboot集成druid后yml文件配置如下：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/openplatform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=PRC
    username: xxx
    password: xxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialSize: 10
    minIdle: 10
    maxActive: 40
    maxWait: 60000
    test-on-borrow: true
    test-while-idle: true

testonborrow=true,即每次应用从连接池获取连接都会先判断这个连接是否有效，无效不会用这个连接。那么为什么会有这个问题呢？？？
然后开始看druid官方文档：

如上所示，文档上说如果这个配置没有配置的话，那么testonborrow的配置是不会生效的，至此猜测是配置不合理导致的问题。
然后自己本地加了配置，后修改如下：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/openplatform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=PRC
    username: xxx
    password: xxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialSize: 10
    minIdle: 10
    maxActive: 40
    maxWait: 60000
    test-on-borrow: true
    test-while-idle: true
    validation-query-timeout: 3000
    validation-query: select 'x'

为了方便排查原因，我把本地数据库超时时间设置为30秒，应用连接数据库超时时间设置为60秒，如果按照现在的推理逻辑,应用首先拿到一个连接后闲置，然后30秒后这个连接超时，被数据库单方面断开，这时候应用从连接池获取连接，拿到连接后由于testonBorrow为true，会根据validationquery配置的sql检测这个连接是否可用，如果可用返回给应用，不可用换个连接。
但是代码运行过程中仍然会报上面的错。

那么问题回到原点，我们初始以为是参数设置不合理导致获取连接的时候没有校验导致的， 现在修改后还报这个错，那么问题的根源还不是这里。
那么现在数据连接池到底是因为什么断的呢？有异常的时候数据连接池到底是什么参数呢？开始怀疑我们的参数没有注入进去
本地调试，在MybatisAutoConfiguration这个类打断点，项目启动的时候这里会创建sqlsessionFactory,sqlSessionfactory会找datasource的bean，并set进去，调试发现除了连接数据库四要素，数据库连接池自定义的属性都没有set进去。。。
怀疑是断点的方式不对，可能后面再其他地方初始化了，所以本地调试执行一条sql,在DruidPooledStatement这个类的execute方法打断点
如下图方式，然后获取连接的connectionHolder，再根据connectionHolder获取dataSource

查看发现执行sql的连接的数据源仍然没有把我们自定义的连接池参数设置进去，至此，基本确认是参数配置失效的问题。
那么为什么会失效呢？网上那么多各种博客教程，不可能只有我们不生效，会不会是参数格式不对。修改为如下格式

#配置一
spring:
  application:
    name: dataEngine
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://xxx.xxx.xxxx.xx:3306/openplatform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=PRC
    username: xxx
    password: xxx
    driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      initialSize: 10
      maxActive: 40
      maxWait: 60000
      minEvictableIdleTimeMillis: 300000
      minIdle: 10
      testOnBorrow: true
      testOnReturn: false
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 60000
      validationQuery: SELECT 'x'
      validationQueryTimeout: 3000

调试发现还是用的自定义的参数。还有就是参数中间加 ‘-’的类似配置，但测试发现均不行。。。
突然想到会不会是版本的问题，当前项目springboot版本如下所示：

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

看源码发现确实没有针对druid的yml配置解析类。。。发现目前Spring Boot中默认支持的连接池有dbcp2, tomcat的dbcp, hikari三种连接池。
既然暂时没法通过配置直接实现，我们就需要自定义bean，然后读取配置文件参数把连接池相关参数设置进去。

spring:
  application:
    name: dataEngine
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/openplatform?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=PRC
    username: openplatformopr
    password: PBW_T2WqgPZvCg1v
    driverClassName: com.mysql.cj.jdbc.Driver
    initialSize: 10
    maxActive: 40
    maxWait: 60000
    minEvictableIdleTimeMillis: 300000
    minIdle: 10
    testOnBorrow: true
    testOnReturn: false
    testWhileIdle: true
    timeBetweenEvictionRunsMillis: 60000
    validationQuery: SELECT 'x'
    validationQueryTimeout: 3000

请求的配置类如下：

package cn.newhope.de.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.SQLException;

@Component
@ConfigurationProperties(prefix = "spring.datasource")
public class DruidSource {

    private String url;

    private String username;

    private String password;

    private String driverClassName;

    private int initialSize;

    private int minIdle;

    private int maxActive;

    private int maxWait;

    private int timeBetweenEvictionRunsMillis;

    private int minEvictableIdleTimeMillis;
    private String validationQuery;

    private boolean testWhileIdle;
    private boolean testOnBorrow;

    private boolean testOnReturn;


    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public int getInitialSize() {
        return initialSize;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public int getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public int getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public int getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public boolean isTestWhileIdle() {
        return testWhileIdle;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public boolean isTestOnReturn() {
        return testOnReturn;
    }

    public void setTestOnReturn(boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }





    @Bean   //声明其为Bean实例
    @Primary //在同样的DataSource中，首先使用被标注的DataSource
    public DataSource dataSource() throws SQLException {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
       datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        return datasource;
    }
}

如上，再在程序入口上加一个注解@EnableConfigurationProperties
发现相关自定义参数成功设置进去。

引发的思考：
1、至此 ，解决了yml配置的druid参数失效问题。那么为什么网上好多博客他们配置的是有效的呢？？？
2、关于druid的配置参数，还是需要从官网看相关参数设置方法，并自定义修改，后面会开一篇博客单独讲解
3、数据库相关超时时间需要考虑怎么和druid适配
这些问题留给我 也留给大家

//-------------------这是一条华丽的分界线-------------------------------//
yml关于连接池的配置失效，后来和同事分享了我的思路后，她给了我一点启发：可能我缺少一个springboot 和 druid的连接类，后来她给我找到了如下pom配置：

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.17</version>
</dependency>

至此，为啥别人配置的yml是生效的，我的无效找到了原因。加上如上配置后亲测有效，注意新加一层druid节点，连接池相关信息配置在druid下一层，如上面配置一样例所示。