c3p0 and MySQL connection lost after 8 hours

最近遇到了这样的一个问题，使用了c3p0数据库连接池，连接池中的连接在第二天使用“show processlist;”检查时全部断掉了。

Application在执行sql时，报出“communication failure with Mysql database”异常。

查询了下，发现mysql的默认wait_timeout是8小时，c3p0中可以在checkout connection时检查连接的有效性，如果是过时(stale)的连接，就把该连接清掉，然后建立新的连接。

具体参考如下：

1.http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

2.http://jimlaren.iteye.com/blog/137103

3.http://osbi.nl/2009/09/pentaho-and-mysql-connection-lost-after-8-hours/

4.http://soft-app.iteye.com/blog/921828

最终选定的参数组合是

//set to 'SELECT 1' preferredTestQuery = 'SELECT 1' //set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out maxIdleTime = 25000 //if you can take the performance 'hit', set to "true" testConnectionOnCheckout = true
但是发现了新的异常，

08/Nov/2011 03:35:00, {com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1} - DEBUG NewPooledConnection : com.mchange.v2.c3p0.impl.NewPooledConnection@41ab11b0 closed by a client java.lang.Exception: DEBUG -- CLOSE BY CLIENT STACK TRAC at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:566) at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:234) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470) at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
从http://forum.springsource.org/archive/index.php/t-47689.html 可知，把log4j的level从DEBUG改成INFO，异常就不会报出了。