springboot集成Druid之后报错：discard long time none received connection. , jdbcUrl:jdbc:mysql://lo

1. 整合druid的配置：

在pom.xml中：

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

在application.yml中：

spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3307/shopping?useSSl=false&&allowMultiQueries=true&useAffectedRows=true #数据库地址
      username: root #用户名
      password: 123456 #密码
      max-active: 20  #连接池最大值
      initial-size: 5 #连接池初始值
      min-idle: 5 #连接池最小空值
      min-evictable-idle-time-millis: 300000
      max-wait: 60000 #最大等待时间
      validation-query: select 1 # 检测连接是否有效时执行的sql命令
      test-on-borrow: false # 借用连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      test-on-return: false  #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      test-while-idle: true # 连接空闲时检测，如果连接空闲时间大于timeBetweenEvictionRunsMillis指定的毫秒，执行validationQuery指定的SQL来检测连接是否有效
      time-between-eviction-runs-millis: 60000 # 空闲连接检查、废弃连接清理、空闲连接池大小调整的操作时间间隔，单位是毫秒(1分钟)
      #监控有关的配置↓
      filters: stat,wall # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      connection-properties:
        druid.stat.mergeSql: true
      filter:
        stat:
          slow-sql-millis: 500
      web-stat-filter:
        enabled: true   #是否启用StatFilter,默认值false，用于采集 web-jdbc 关联监控的数据。
        url-pattern: /*  # 需要监控的url
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico  #是否启用StatFilter默认值false，用于采集 web-jdbc 关联监控的数据。
      stat-view-servlet:
        enabled: true #启用sql监控服务
        url-pattern:  /druid/*
        reset-enable: false
        login-username: admin 
        login-password: 123456

但是在运行之后，成功获取到数据，但是后台却报了错误:discard long time none received connection. , jdbcUrl:jdbc:mysql://localhost:3306/XXX 这是怎么一回事呢？以前的版本并不会有这样的问题，

发现报错的来源是：这个package com.alibaba.druid.pool 里的这个方法：

protected boolean testConnectionInternal(DruidConnectionHolder holder, Connection conn) {
        String sqlFile = JdbcSqlStat.getContextSqlFile();
        String sqlName = JdbcSqlStat.getContextSqlName();
        if (sqlFile != null) {
            JdbcSqlStat.setContextSqlFile((String)null);
        }

        if (sqlName != null) {
            JdbcSqlStat.setContextSqlName((String)null);
        }

        boolean var6;
        try {
            boolean valid;
            if (this.validConnectionChecker == null) {
                if (conn.isClosed()) {
                    valid = false;
                    return valid;
                }

                if (null == this.validationQuery) {
                    valid = true;
                    return valid;
                }

                Statement stmt = null;
                ResultSet rset = null;

                boolean var7;
                try {
                    stmt = conn.createStatement();
                    if (this.getValidationQueryTimeout() > 0) {
                        stmt.setQueryTimeout(this.validationQueryTimeout);
                    }

                    rset = stmt.executeQuery(this.validationQuery);
                    if (!rset.next()) {
                        var7 = false;
                        return var7;
                    }
                } finally {
                    JdbcUtils.close(rset);
                    JdbcUtils.close(stmt);
                }

                if (this.onFatalError) {
                    this.lock.lock();

                    try {
                        if (this.onFatalError) {
                            this.onFatalError = false;
                        }
                    } finally {
                        this.lock.unlock();
                    }
                }

                var7 = true;
                return var7;
            }

            valid = this.validConnectionChecker.isValidConnection(conn, this.validationQuery, this.validationQueryTimeout); 
            long currentTimeMillis = System.currentTimeMillis();
            if (holder != null) {
                holder.lastValidTimeMillis = currentTimeMillis;
                holder.lastExecTimeMillis = currentTimeMillis;
            }

            if (valid && this.isMySql) {
                long lastPacketReceivedTimeMs = MySqlUtils.getLastPacketReceivedTimeMs(conn); //获取mysql上次使用的时间
                if (lastPacketReceivedTimeMs > 0L) {
                    long mysqlIdleMillis = currentTimeMillis - lastPacketReceivedTimeMs;
                    if (lastPacketReceivedTimeMs > 0L && mysqlIdleMillis >= this.timeBetweenEvictionRunsMillis) {
                        this.discardConnection(holder);
                        String errorMsg = "discard long time none received connection. , jdbcUrl : " + this.jdbcUrl + ", jdbcUrl : " + this.jdbcUrl + ", lastPacketReceivedIdleMillis : " + mysqlIdleMillis;
                        LOG.error(errorMsg);
//想来error应该就是这里发出来的吧
                        boolean var13 = false;
                        return var13;
                    }
                }
            }

            if (valid && this.onFatalError) {
                this.lock.lock();

                try {
                    if (this.onFatalError) {
                        this.onFatalError = false;
                    }
                } finally {
                    this.lock.unlock();
                }
            }

            boolean var46 = valid;
            return var46;
        } catch (Throwable var41) {
            var6 = false;
        } finally {
            if (sqlFile != null) {
                JdbcSqlStat.setContextSqlFile(sqlFile);
            }

            if (sqlName != null) {
                JdbcSqlStat.setContextSqlName(sqlName);
            }

        }

        return var6;
    }

    public Set<DruidPooledConnection> getActiveConnections() {
        this.activeConnectionLock.lock();

        HashSet var1;
        try {
            var1 = new HashSet(this.activeConnections.keySet());
        } finally {
            this.activeConnectionLock.unlock();
        }

        return var1;
    }

this.timeBetweenEvictionRunsMillis 我们是 60000ms 也就是1分钟，是空闲连接检查，如果超过了1分钟，就discardConnection(holder); 和LOG.error(errorMsg);//error是这里发出来的,之后我们发现，在前面还有一个条件。if (valid && this.isMySql) ，我们设置valid为false即可。

找一下valid有什么来源

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package com.alibaba.druid.pool.vendor;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.druid.pool.ValidConnectionChecker;
import com.alibaba.druid.pool.ValidConnectionCheckerAdapter;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;
import com.alibaba.druid.support.logging.Log;
import com.alibaba.druid.support.logging.LogFactory;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.druid.util.Utils;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class MySqlValidConnectionChecker extends ValidConnectionCheckerAdapter implements ValidConnectionChecker, Serializable {
    public static final int DEFAULT_VALIDATION_QUERY_TIMEOUT = 1;
    public static final String DEFAULT_VALIDATION_QUERY = "SELECT 1";
    private static final long serialVersionUID = 1L;
    private static final Log LOG = LogFactory.getLog(MySqlValidConnectionChecker.class);
    private Class<?> clazz;
    private Method ping;
    private boolean usePingMethod = false;

    public MySqlValidConnectionChecker() {
        try {
            this.clazz = Utils.loadClass("com.mysql.jdbc.MySQLConnection");
            if (this.clazz == null) {
                this.clazz = Utils.loadClass("com.mysql.cj.jdbc.ConnectionImpl");
            }

            if (this.clazz != null) {
                this.ping = this.clazz.getMethod("pingInternal", Boolean.TYPE, Integer.TYPE);
            }

            if (this.ping != null) {
                this.usePingMethod = true;
            }
        } catch (Exception var2) {
            LOG.warn("Cannot resolve com.mysql.jdbc.Connection.ping method.  Will use 'SELECT 1' instead.", var2);
        }

        this.configFromProperties(System.getProperties());
    }

    public void configFromProperties(Properties properties) {
        String property = properties.getProperty("druid.mysql.usePingMethod");
        if ("true".equals(property)) {
            this.setUsePingMethod(true);
        } else if ("false".equals(property)) {
            this.setUsePingMethod(false);
        }

    }

    public boolean isUsePingMethod() {
        return this.usePingMethod;
    }

    public void setUsePingMethod(boolean usePingMethod) {
        this.usePingMethod = usePingMethod;
    }
//这里就是决定valid取值的方法
    public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {
        if (conn.isClosed()) {
            return false;
        } else {
            if (this.usePingMethod) {
//可以设置它false」让验证空闲连接使用 select 1，而不是使用MySQL的Ping，这样就刷新了上次使用时间，不会出现空闲 60秒以上的连接
                if (conn instanceof DruidPooledConnection) {
                    conn = ((DruidPooledConnection)conn).getConnection();
                }

                if (conn instanceof ConnectionProxy) {
                    conn = ((ConnectionProxy)conn).getRawObject();
                }

                if (this.clazz.isAssignableFrom(conn.getClass())) {
                    if (validationQueryTimeout <= 0) {
                        validationQueryTimeout = 1;
                    }

                    try {
                        this.ping.invoke(conn, true, validationQueryTimeout * 1000);
                        return true;
                    } catch (InvocationTargetException var11) {
                        Throwable cause = var11.getCause();
                        if (cause instanceof SQLException) {
                            throw (SQLException)cause;
                        }

                        throw var11;
                    }
                }
            }

            String query = validateQuery;
            if (validateQuery == null || validateQuery.isEmpty()) {
                query = "SELECT 1";
            }

            Statement stmt = null;
            ResultSet rs = null;

            boolean var7;
            try {
                stmt = conn.createStatement();
                if (validationQueryTimeout > 0) {
                    stmt.setQueryTimeout(validationQueryTimeout);
                }

                rs = stmt.executeQuery(query);
                var7 = true;
            } finally {
                JdbcUtils.close(rs);
                JdbcUtils.close(stmt);
            }

            return var7;
        }
    }
}

mysql数据库有一个wait_timeout的配置，默认值为28800(即8小时).在默认配置不改变的情况下，如果连续8小时内数据库空闲的话，就会关闭空闲的连接，而druid是1分钟就断开，主要为了提高数据库的处理能力。我们让验证空闲连接使用 select 1，而不是使用MySQL的Ping。

    static {
        System.setProperty("druid.mysql.usePingMethod","false");
    }

加入这个就好了，不适用PingMethod