Mysql5的auto Reconnect错误

在服务器上部署好这个Web系统后，这时访问系统是很正常的。当把服务器的时间（例如：2008-03-31）加一天或更多天（例如：2008-04-01，2008-04-02...），这时再访问这个Web系统，报出如下的异常：

org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'saveMessage' on target class [class com.sun.proxy.$Proxy8] failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 
	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:273)
	at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at com.sun.proxy.$Proxy8.saveMessage(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor380.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)
	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:264)
	... 3 more
Caused by: org.hibernate.TransactionException: JDBC begin failed: 
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:92)
	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
	... 14 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31,293,844 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3589)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3478)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4019)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728)
	at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5075)
	at com.mysql.jdbc.jdbc2.optional.ConnectionWrapper.setAutoCommit(ConnectionWrapper.java:154)
	at sun.reflect.GeneratedMethodAccessor376.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at com.atomikos.jdbc.AtomikosConnectionProxy.invoke(AtomikosConnectionProxy.java:157)
	at com.sun.proxy.$Proxy5.setAutoCommit(Unknown Source)
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:87)
	... 16 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3039)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3489)
	... 29 more
2013-11-14 08:34:35,175-[QC] ERROR [org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-7] org.quartz.core.ErrorLogger.schedulerError(2015) | Job (DEFAULT.messageServiceDetail threw an exception.
org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'saveMessage' on target class [class com.sun.proxy.$Proxy8] failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: ]
	at org.quartz.core.JobRunShell.run(JobRunShell.java:214)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
* Nested Exception (Underlying Cause) ---------------
org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'saveMessage' on target class [class com.sun.proxy.$Proxy8] failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 
	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:273)
	at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at com.sun.proxy.$Proxy8.saveMessage(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor380.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)
	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:264)
	... 3 more
Caused by: org.hibernate.TransactionException: JDBC begin failed: 
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:92)
	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
	... 14 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31,293,844 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3589)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3478)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4019)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728)
	at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5075)
	at com.mysql.jdbc.jdbc2.optional.ConnectionWrapper.setAutoCommit(ConnectionWrapper.java:154)
	at sun.reflect.GeneratedMethodAccessor376.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at com.atomikos.jdbc.AtomikosConnectionProxy.invoke(AtomikosConnectionProxy.java:157)
	at com.sun.proxy.$Proxy5.setAutoCommit(Unknown Source)
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:87)
	... 16 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3039)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3489)
	... 29 more

我们首先判断是连接池出了问题，即当系统时间改变后，数据库连接发现超时，因此需要重新连接或完全重建连接池。我们换用了一下数据库，如果使用Oracle数据库，同样的修改不会出现问题，系统运行正常，根据不会出现这样的异常。这就说明了出现这个异常是Mysql相关的，通过查阅资料得知可能和Mysql的运行参数（超时）有关，顺着这个思路我们来分析Mysql的一些运行参数。

在MySQL Command Line Client中执行show variables like '%timeout%';如下图所示：

在图中我们可以看到有两个变量wait_timeout和interactive-timeout，它们的默认值都为28800秒，即为8小时。也就是说默认情况下，Mysql在经过8小时（28800秒）不使用后会自动关闭已打开的连接。

为了解决这个问题，对于MySQL5之前的版本，如Mysql4.x，只需要修改连接池配置中的URL，添加一个参数：autoReconnect=true，如果是MySQL5及以后的版本，则需要修改my.cnf(或者my.ini)文件，在[mysqld]后面添加上：

wait_timeout = n

interactive-timeout = n

n为服务器关闭交互式连接前等待活动的秒数。

可是就部署而言每次修改my.ini比较麻烦，而且n等于多少才是合适的呢？所以这个解决办法不好。

怎么才能比较好的解决这个问题呢？通过仔细分析，原因大致为：当修改系统日期后Mysql自动关闭已打开的连接，可以数据库连接池(DBCP)还认为这些连接是活动的，如果这时有请求（需要执行读写数据库的操作），连接池就用一个连接去操作数据库，而这个连接在Mysql的连接池中并不存在，所以会出现以上的异常。如果一个连接在和Mysql建立连接时能检查就不会有这样的问题了。

这时我们同事想起了他以前用过的一个数据库连接池proxool，它有两个属性：一个是test-before-use，还有一个是test-after-use，这两个属性就是在使用前和使用后都要进行对连接的检查，如果连接无效就扔掉再创建一个新的连接，它们的解释如下：

test-before-use:

If you set this to true then each connection is tested (with whatever is defined in house-keeping-test-sql) before being served. If a connection fails then it is discarded and another one is picked. If all connections fail a new one is built. If that one fails then you get an SQLException saying so.

test-after-use:

If you set this to true then each connection is tested (with whatever is defined in house-keeping-test-sql) after it is closed (that is, returned to the connection pool). If a connection fails then it is discarded.

于是我们在项目中换上proxool的连接池，再运行并访问系统，更改系统时间，再访问，一切OK，问题就这样解决了！

不但如此，proxool的连接池中的连接的数量，活动数以及每个连接的的具体情况都可以一目了然的看到，如下图所示：

通过上面的测试和分析，针对于Mysql5数据库，选择proxool数据库连接池是比较合适的。

c3p0的连接池有 idleConnectionTestPeriod 属性，可以设置一段时间后连接池自动测试执行，保持连接开放，效果也是一样的。