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

1. 整合druid的配置：

在pom.xml中：

      com.alibaba      druid-spring-boot-starter      1.1.23

在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 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

来源地址：https://blog.csdn.net/m0_54409739/article/details/129261025