configured value of ‘wait_timeout‘.....Connector/J connection property ‘autoReconnect=true‘解决方案

1. 错误日志，看到这里大家应该都猜测出why了？
   

2. mysql wait_timeout配置（ps这么查不是特别准受另一个参数影响，配合下图4中）
   

3. 查看mysql主动关闭的链接（ps应用服务器傻傻不知道自己已经被单方面分手）
   

4. 看到这里猜测30分钟左右可能会有一次报错果然如此
   
   

5. 原始连接池配置

##bdm数据库连接配置
spring.datasource.bdm.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.bdm.url =xxx?useUnicode=true&useSSL=false&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
spring.datasource.bdm.username = xxx
spring.datasource.bdm.password = xxx
spring.datasource.bdm.max-idle = 20
spring.datasource.bdm.max-wait = 10000
spring.datasource.bdm.min-idle = 5
spring.datasource.bdm.initial-size = 10
spring.datasource.bdm.maxActive = 20

6. 看到这里大家肯定都知道如何修复，此时以为用的druid（然而并没有，怀疑）

spring.datasource.bdm.testWhileIdle = true
spring.datasource.bdm.validationQuery = select 1
spring.datasource.bdm.timeBetweenEvictionRunsMillis = 60000

7. 最后debug首先发现bdm使用的是 org.apache.tomcat.jdbc.pool数据库连接池http://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html确定是支持如上参数的，再次debug是新加入的属性参数当前写法注入不进去

8. 原始代码

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = "xxx.xxx.xxx.bdm.mapper", sqlSessionTemplateRef = "bdmSqlSessionTemplate")
public class BdmMybatisConfig {
    //mybaits mapper xml搜索路径
    private final static String MAPPER_LOCATIONS = "classpath*:**/xxx/xxx/mapper/**/*Mapper.xml";
    @Bean(name = "bdmDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.bdm")
    public DataSourceProperties bdmDataSourceProperties() {
        return new DataSourceProperties();
    }
    @Bean(name = "bdmDataSource")
    public DataSource bdmDataSource(@Qualifier("bdmDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }
}

9. 修复后代码，此时3中新增的参数生效

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = "xxx.xxx.xxx.bdm.mapper", sqlSessionTemplateRef = "bdmSqlSessionTemplate")
public class BdmMybatisConfig {

    //mybaits mapper xml搜索路径
    private final static String MAPPER_LOCATIONS = "classpath*:**/rsc/bdm/mapper/**/*Mapper.xml";


   
    /**
     * 留给大家切换druid的可能
     */
    @Configuration
    @ConditionalOnClass(DruidDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.bdm.druid.enabled", havingValue = "true", matchIfMissing = false)
    public class DruidDataSourceConfig {
        @Bean(name = "bdmDataSource")
        @ConfigurationProperties("spring.datasource.druid.bdm")
        public DataSource bdmDataSource() {
            return new DruidDataSource();
        }
    }


    /**
     * 这里还是默认开启使用TomcatPool同之前版本保持一致
     */
    @Configuration
    @ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.bdm.tomcat.enabled", havingValue = "true", matchIfMissing = true)
    public class TomcatPoolDataSourceConfig {

        @Bean(name = "bdmDataSourceProperties")
        @ConfigurationProperties(prefix = "spring.datasource.bdm")
        public org.apache.tomcat.jdbc.pool.PoolProperties bdmDataSourceProperties() {
            return new org.apache.tomcat.jdbc.pool.PoolProperties();
        }

        @Bean(name = "bdmDataSource")
        public DataSource bdmDataSource(@Qualifier("bdmDataSourceProperties") org.apache.tomcat.jdbc.pool.PoolProperties  dataSourceProperties) {
            DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource(dataSourceProperties);
            return dataSource;
        }
    }
}

修复方法：

1. 添加连接池配置

spring.datasource.bdm.testWhileIdle = true
spring.datasource.bdm.validationQuery = select 1
spring.datasource.bdm.timeBetweenEvictionRunsMillis = 60000

3. 修改rsc-bdm服务中 BdmMybatisConfig类中创建datasource bean代码

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = "xxx.xxx.xxxx.bdm.mapper", sqlSessionTemplateRef = "bdmSqlSessionTemplate")
public class BdmMybatisConfig {

    //mybaits mapper xml搜索路径
    private final static String MAPPER_LOCATIONS = "classpath*:**/xxxx/xxx/mapper/**/*Mapper.xml";


   
    /**
     * 留给大家切换druid的可能
     */
    @Configuration
    @ConditionalOnClass(DruidDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.bdm.druid.enabled", havingValue = "true", matchIfMissing = false)
    public class DruidDataSourceConfig {
        @Bean(name = "bdmDataSource")
        @ConfigurationProperties("spring.datasource.druid.bdm")
        public DataSource bdmDataSource() {
            return new DruidDataSource();
        }
    }


    /**
     * 这里还是默认开启使用TomcatPool同之前版本保持一致
     */
    @Configuration
    @ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.bdm.tomcat.enabled", havingValue = "true", matchIfMissing = true)
    public class TomcatPoolDataSourceConfig {

        @Bean(name = "bdmDataSourceProperties")
        @ConfigurationProperties(prefix = "spring.datasource.bdm")
        public org.apache.tomcat.jdbc.pool.PoolProperties bdmDataSourceProperties() {
            return new org.apache.tomcat.jdbc.pool.PoolProperties();
        }

        @Bean(name = "bdmDataSource")
        public DataSource bdmDataSource(@Qualifier("bdmDataSourceProperties") org.apache.tomcat.jdbc.pool.PoolProperties  dataSourceProperties) {
            DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource(dataSourceProperties);
            return dataSource;
        }
    }
}