从零搭建开发脚手架 Druid错误discard long time none received connection

╰+攻爆jí腚メ 2022-09-12 01:00 62阅读 0赞

### 背景 ###

在我的开源项目[EasyAdmin][]中，发现有很多druid sql warn log，整理解决方案如下：

详细日志如下：

10:58:15.370  WARN --- [nio-8080-exec-6] com.alibaba.druid.pool.DruidAbstractDataSource               :1489 discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/laker?serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=false, version : 1.2.5, lastPacketReceivedIdleMillis : 98988
    10:58:15.372  WARN --- [nio-8080-exec-6] com.alibaba.druid.pool.DruidAbstractDataSource               :1489 discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/laker?serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=false, version : 1.2.5, lastPacketReceivedIdleMillis : 98991
    10:58:15.373  WARN --- [nio-8080-exec-6] com.alibaba.druid.pool.DruidAbstractDataSource               :1489 discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/laker?serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=false, version : 1.2.5, lastPacketReceivedIdleMillis : 107115

通过日志信息，可以得出问题出在`DruidAbstractDataSource`类的**1489**行，查看源码如下：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAbGFrZXJub3Rl_size_20_color_FFFFFF_t_70_g_se_x_16][]

继续跟踪`validConnectionChecker.isValidConnection`有oracle、mysql等数据库实现，这里我们的是`MySqlValidConnectionChecker`

连接检查的三种方式：

*  conn.isClosed()
 *  usePingMethod
 *  使用select 1的方式进行检查

public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception { 
          // 方式一 
           if (conn.isClosed()) {  
                return false;
            }
           // 方式二 ping
            if (usePingMethod) { 
                if (conn instanceof DruidPooledConnection) { 
                    conn = ((DruidPooledConnection) conn).getConnection();
                }
    
                if (conn instanceof ConnectionProxy) { 
                    conn = ((ConnectionProxy) conn).getRawObject();
                }
    
                if (clazz.isAssignableFrom(conn.getClass())) { 
                    if (validationQueryTimeout <= 0) { 
                        validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT;
                    }
    
                    try { 
                        ping.invoke(conn, true, validationQueryTimeout * 1000);
                    } catch (InvocationTargetException e) { 
                        Throwable cause = e.getCause();
                        if (cause instanceof SQLException) { 
                            throw (SQLException) cause;
                        }
                        throw e;
                    }
                    return true;
                }
            }
            // 方式三 使用select 1的方式进行检查
            String query = validateQuery;
            if (validateQuery == null || validateQuery.isEmpty()) { 
                query = DEFAULT_VALIDATION_QUERY; // "SELECT 1"
            }
    
            Statement stmt = null;
            ResultSet rs = null;
            try { 
                stmt = conn.createStatement();
                if (validationQueryTimeout > 0) { 
                    stmt.setQueryTimeout(validationQueryTimeout);
                }
                rs = stmt.executeQuery(query);
                return true;
            } finally { 
                JdbcUtils.close(rs);
                JdbcUtils.close(stmt);
            }
    
        }

**解决方案**

**设置ping方式是否启用，源码MySqlValidConnectionChecker.configFromProperties**

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

可以看到设置环境变量`druid.mysql.usePingMethod`为false即可。

*  在启动程序时在运行参数中增加：-Ddruid.mysql.usePingMethod=false。
 *  或者代码设置`System.setProperty("druid.mysql.usePingMethod","false");`

具体可参见 [https://gitee.com/lakernote/easy-admin/blob/master/src/main/java/com/laker/admin/config/DruidConfig.java][https_gitee.com_lakernote_easy-admin_blob_master_src_main_java_com_laker_admin_config_DruidConfig.java]

[EasyAdmin]: https://gitee.com/lakernote/easy-admin
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAbGFrZXJub3Rl_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/09bc36255e67483bbc8f3918db69adcb.png
[https_gitee.com_lakernote_easy-admin_blob_master_src_main_java_com_laker_admin_config_DruidConfig.java]: https://gitee.com/lakernote/easy-admin/blob/master/src/main/java/com/laker/admin/config/DruidConfig.java