MySQL5数据库连接超时

系统发布以后，出现了一个奇怪的现象，待机一晚上后，第二天早上第一次登录总是失败。重启Tomcat 服务器后，虽然当天可以正常使用，但是到第二天问题依旧。

引用

…………………………………
exception
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.TransactionSystemException: Could not roll back Hibernate transaction; nested exception is org.hibernate.TransactionException: JDBC rollback failed org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:583)
…………………………….
root cause
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
…………………………
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.17 logs.

从上面的错误我们可以看出Connection.close()，数据库连接关闭了。为什么之前的系统中我们没有发现过这样的错误呢？我们从数据库连接开始排查：
查看 Hibernate 配置文件：

<property name="connection.username">root</property>
		<property name="connection.password"></property>

		<!-- JDBC connection pool (use the built-in one) -->
		<property name="connection.pool_size">20</property>

		<!-- Enable Hibernate's automatic session context management -->
		<property name="current_session_context_class">thread</property>

在系统中我们使用了数据库连接池来提高数据库的性能。猜测可能是连接池引发的数据库连接问题。顺着这个思路，通过查阅相关得知和MySQL的运行超时有关。
在MySQL Command Line Client中执行show global variables like 'wait_timeout'



我们发现MySQL5将其连接的等待时间(wait_timeout)缺省为28800 seconds，也就是8小时。在wait_timeout秒期间内，数据库连接(java.sql.Connection)一直处于等待状态，如果没有数据库操作，MySQL5就将该连接关闭。这时，应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时，就碰到上述错误。这解释了为什么程序第二天不能登录的问题。

为了解决这个问题，对于MySQL5之前的版本，如Mysql4.x，只需要修改连接池配置中的URL，添加一个参数：autoReconnect=true，如果是MySQL5及以后的版本，则需要修改my.ini文件。
查看mysql5的手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。我们使用的Windows系统，假设我们要将其设为21天，我们只要修改mysql5的配置文件“my.ini”中的[mysqld]后面添加上： wait_timeout=1814400
重新启动mysql5。

就部署而言每次修改my.ini比较麻烦，怎么才能比较好的解决这个问题呢？由于MySQL自动关闭已打开的连接，如果这时有请求（需要执行读写数据库的操作），连接池就用一个连接去操作数据库，而这个连接在Mysql的连接中并不存在，所以会出现以上的异常。如果一个连接在和Mysql建立连接时能检查就不会有这样的问题了。
网络搜索一番发现了一个开源的数据库连接池proxool，它有两个属性：一个是test-before-use，还有一个是test-after-use，这两个属性就是在使用前和使用后都要进行对连接的检查，如果连接无效就扔掉再创建一个新的连接。

在系统不断升级的过程中，我们常常引入一些新技术，这些技术可能会在某些细节上引发其他技术的问题。MySQL5数据连接超时就是一个很好的例子，这就需要我们不断深入，探索这些技术，对于问题的排查也要从新技术的细节入手。

评论

4 楼 zjfshowtime 2012-10-29  

学习了！

3 楼 kc_ren 2010-05-16  

wangacidlemon 写道

I met the same problem today. Your essay is useful thanks.
But no need to modifiy my.ini, just use mysql command can resolve it:
set global wait_timeout=1728000;

使用命令 重启服务的时候 会恢复

2 楼 programmer365 2010-03-24  

我也遇到了类似的问题，我没有使用连接池。hiberante通过threadlocal来维护session，每次业务开始都创建session，业务结束关闭session。也就是说session相关联的connection不会有超过8小时的可能性。但是系统运行一段时间后也发生了同样的异常，暂想不出合理的解释。根据楼主的描述“待机一晚上后，第二天早上第一次登录总是失败”，难道楼主的系统只创建一个Connection长连接？系统的业务只要频繁操作数据库连接池的话，数据库连接池中的连接理论上都不会长时间处于sleep状态。

1 楼 wangacidlemon 2009-12-11  

I met the same problem today. Your essay is useful thanks.
But no need to modifiy my.ini, just use mysql command can resolve it:
set global wait_timeout=1728000;