druid升级1.0.26遇到的连接闲置断开的问题
来源:互联网 发布:王者荣耀网络不稳定 编辑:程序博客网 时间:2024/05/22 19:13
1. 背景
之前处理了一个mybatis的bug(详见之前的文章),在给系统升级mybatis的时候,想顺便把所有系统的druid也升级了,从版本(0.2.18~1.0.18不等)升级到统一的最新版本(1.0.26)。
但是在beta测试期间,遇到如下异常:
ERROR com.alibaba.druid.pool.DruidDataSource:1243 discard connectioncom.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 1,463,838 milliseconds ago. The last packet sent successfully to the server was 1,463,839 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) ~[na:1.7.0_45] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_45] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_45] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_45] at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) ~[mysql-connector-java-5.1.32.jar:na] at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137) ~[mysql-connector-java-5.1.32.jar:na]
复现的方法是程序启动后,访问一些需要查库的功能,然后闲置直至超过mysql配置的wait_timeout时间(默认8小时,可以改小,如3分钟,方便复现),再访问需要查库的资源,就出现这个问题了。
2.相关配置
代码封装了一个工厂,对druid进行了统一的配置。
其中和连接相关的配置只有:
url中的autoReconnect=true(但这个看网上说在mysql 5.x以上不管用了,没有具体考证,不敢乱说)
testWhileIdle=true这两条。
3. 代码分析
查询时会获取连接(DruidDataSource.getConnectionDirect),在内部会检测连接的状态(DruidDataSource.testConnectionInternal),检测时使用validConnectionChecker.isValidConnection。
下面比较不存在这个问题的1.0.18版本和存在问题的1.0.26版本,通过跟踪代码的执行,发现两个版本在该部分使用的判断逻辑不同,如下:
3.1 1.0.18版本
/* * Copyright 1999-2101 Alibaba Group Holding Ltd. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */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 = 1000; 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 { clazz = Utils.loadClass("com.mysql.jdbc.MySQLConnection"); ping = clazz.getMethod("pingInternal", boolean.class, int.class); if (ping != null) { usePingMethod = true; } } catch (Exception e) { LOG.warn("Cannot resolve com.mysq.jdbc.Connection.ping method. Will use 'SELECT 1' instead.", e); } configFromProperties(System.getProperties()); } @Override 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); } } public boolean isUsePingMethod() { return usePingMethod; } public void setUsePingMethod(boolean usePingMethod) { this.usePingMethod = usePingMethod; } public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) { try { if (conn.isClosed()) { return false; } } catch (SQLException ex) { // skip return false; } 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); return true; } catch (InvocationTargetException e) { Throwable cause = e.getCause(); if (cause instanceof SQLException) { return false; } LOG.warn("Unexpected error in ping", e); return false; } catch (Exception e) { LOG.warn("Unexpected error in ping", e); return false; } } } Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (validationQueryTimeout > 0) { stmt.setQueryTimeout(validationQueryTimeout); } rs = stmt.executeQuery(validateQuery); return true; } catch (SQLException e) { return false; } catch (Exception e) { LOG.warn("Unexpected error in ping", e); return false; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); } }}
3.2 1.0.26版本
/* * Copyright 1999-2101 Alibaba Group Holding Ltd. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */package com.alibaba.druid.pool;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.util.Properties;import com.alibaba.druid.util.JdbcUtils;/** * @author wenshao [szujobs@hotmail.com] * @since 0.2.21 */public class ValidConnectionCheckerAdapter implements ValidConnectionChecker { @Override public boolean isValidConnection(Connection conn, String query, int validationQueryTimeout) throws Exception { if (query == null || query.length() == 0) { return true; } 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); } } @Override public void configFromProperties(Properties properties) { }}
可见在1.0.26版本中,需要配置validationQuery,否则一直返回true(连接可用),当达到mysql的wait_timeout后,就出问题了(druid认为连接还有效,但实际连接已经断开了)。
再翻看代码,发现1.0.26版本中也是有MySqlValidConnectionChecker啊,为什么没用上呢?原来是方法名改了,没重载上=。=
/* * Copyright 1999-2101 Alibaba Group Holding Ltd. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */package com.alibaba.druid.pool.vendor;import java.io.Serializable;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.util.Properties;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;public class MySqlValidConnectionChecker extends ValidConnectionCheckerAdapter implements ValidConnectionChecker, Serializable { public static final int DEFAULT_VALIDATION_QUERY_TIMEOUT = 1000; 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 { clazz = Utils.loadClass("com.mysql.jdbc.MySQLConnection"); if (clazz == null) { clazz = Utils.loadClass("com.mysql.cj.jdbc.ConnectionImpl"); } if (clazz != null) { ping = clazz.getMethod("pingInternal", boolean.class, int.class); } if (ping != null) { usePingMethod = true; } } catch (Exception e) { LOG.warn("Cannot resolve com.mysql.jdbc.Connection.ping method. Will use 'SELECT 1' instead.", e); } configFromProperties(System.getProperties()); } @Override 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); } } public boolean isUsePingMethod() { return usePingMethod; } public void setUsePingMethod(boolean usePingMethod) { this.usePingMethod = usePingMethod; } public boolean validConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception { if (conn.isClosed()) { return false; } 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; } ping.invoke(conn, true, validationQueryTimeout * 1000); return true; } } Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (validationQueryTimeout > 0) { stmt.setQueryTimeout(validationQueryTimeout); } rs = stmt.executeQuery(validateQuery); return true; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); } }}
往前看了几个版本,发现从1.0.21版本就是这样了。
目前还没有理解这么做的原因是什么,是因为mysql版本太多,这个功能不通用?还是当时开发的时候名字改错了?
提了个issue,等大大们回复了。
————————————————————————————————————————————
最新进展:大大们已经修复了,更新到1.0.27版本即可。
- druid升级1.0.26遇到的连接闲置断开的问题
- 解决ssh登录后闲置时间过长而断开连接的问题
- mysql闲置8小时候就自动断开链接的问题
- Mysql闲置超时,连接无效的问题
- golang :连接数据库闲置断线的问题
- mysql超时设置的问题,如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat
- mysql 连接断开的问题
- druid-1.0.21源代码导入eclipse工程遇到的问题
- 使用4.3.11版本的hibernate结合druid连接池遇到的问题
- 避免SSH连接因超时闲置断开
- 避免SSH连接因超时闲置断开
- 避免SSH连接因超时闲置断开
- 解决ssh闲置时间过长断开问题
- MySQL 的自动断开连接问题
- Secure CRT 连接自动断开的问题
- 解决ssh超时断开连接的问题
- 关于mysql_query导致连接断开的问题
- 解决ssh超时断开连接的问题
- two_sum
- css3第三天
- ExpandableListView的用法
- Atitit 图像处理之理解卷积attilax总结
- scala类型系统:1) 类型与类
- druid升级1.0.26遇到的连接闲置断开的问题
- 数据结构和算法--栈的数组实现
- Android自定义控件系列(三)—底部菜单(上)
- 利用Tag接口自定义标签扩展
- 饮水思源
- vcg数据结构1
- Atitit图片复制父目录给你设计的实现 基于win 图片浏览器
- 51nod 1205 流水线调度
- 数据结构和算法--栈的链式实现