解决dbcp连接Mysql数据库wait_timeout连接超时报错的问题

       最近发现有一个项目的日志后台有大量的数据库连接报错的问题，大体错误信息如下：

### The error occurred while setting parameters
### SQL: select * from tcs_settleflow where 1=1                                                                                                                                     and stState = ?  order by addDatetime desc
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 305,969,401 milliseconds ago.  The last packet sent successfully to the server was 305,969,401 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.
; SQL []; The last packet successfully received from the server was 305,969,401 milliseconds ago.  The last packet sent successfully to the server was 305,969,401 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.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 305,969,401 milliseconds ago.  The last packet sent successfully to the server was 305,969,401 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 org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
	at com.sun.proxy.$Proxy24.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
	at com.cyl.frame.common.DAL.CommonDAL.selectList(CommonDAL.java:104)
	at com.cyl.module.pointsAccountMgt.BLL.SettleFlowBLL.SettlementAllForBatch(SettleFlowBLL.java:322)
	at sun.reflect.GeneratedMethodAccessor667.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy29.SettlementAllForBatch(Unknown Source)
	at com.cyl.module.commonMgt.JOBS.STJob.executeInternal(STJob.java:66)
	at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 305,969,401 milliseconds ago.  The last packet sent successfully to the server was 305,969,401 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.GeneratedConstructorAccessor68.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3983)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2596)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1307)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	at sun.reflect.GeneratedMethodAccessor150.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy93.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at sun.reflect.GeneratedMethodAccessor219.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
	... 14 more
Caused by: java.net.SocketException: Broken pipe
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3964)
	... 38 more

          大概意思是距离上一次连接MySQL的间隔时间，已经超出了MySQL设置的'wait_timeout'时长啦。连上MySQL，
mysql> show global variables like 'wait_timeout';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| wait_timeout | 28800 |
+----------------------------+--------+
        可以看到MySQL设置的wait_timeout是8小时，而日志显示上一次访问MySQL的时间已超出84小时。确认了问题后，就大概可以知道原因：项目使用dbcp管理jdbc连接。当超过'wait_timeout'时长后，MySQL会自动断开连接；而dbcp这边并不知道，当再次使用该连接时，发现连接不可用，就会报如上错误了。
        解决的方法主要有两种，一是增加MySQL的'wait_timeout'时长；二是保证连接在MySQL的'wait_timeout'时间内，至少访问一次数据库。第一种方法，需要修改mysql数据库配置文件并且重启不推荐使用。而且应用如果不能确定最长'wait_timeout'时间，则该值无论如何设定，理论上也无法避免如上问题。所以只能保证应用在MySQL的'wait_timeout'时间内，至少访问一次数据库。该方法实现也很简单，只需配置dbcp的三个配置项timeBetweenEvictionRunsMillis、testWhileIdle与validationQuery即可：
         timeBetweenEvictionRunsMillis=26400 # 失效检查线程运行时间间隔，要小于MySQL的'wait_timeout'时间（如果小于等于0，不会启动检查线程）
         testWhileIdle=true # 检查连接是否有效
         validationQuery=SELECT 1 FROM dual # 检查连接有效性的SQL语句
         这样dbcp会在timeBetweenEvictionRunsMillis指定的时间间隔（小于MySQL的'wait_timeout'）内，通过validationQuery指定的SQL语句来检查连接是否有效。避免了连接因长时间未执行SQL语句，而造成MySQL关闭连接。

       异常的最后一行是：or using the Connector/J connection property 'autoReconnect=true' to avoid this problem，这就涉及另外一个问题：autoReconnect=true在MySQL 5之前的版本可用，但是在MySQL 5之后的版本貌似不可用了。另外validationQuery=SELECT 1 FROM dual中，dual只是一个虚表，在MySQL中并不存在，查询时也不关联。只是为了保持select … from … 的形式罢了。