c3p0 and MySQL connection lost after 8 hours

最新推荐文章于 2024-10-21 23:06:57 发布

nomad2

最新推荐文章于 2024-10-21 23:06:57 发布

阅读量1.5k

点赞数

分类专栏：数据库 J2SE 文章标签： c3p0 mysql 数据库连接池 performance log4j application

本文链接：https://blog.youkuaiyun.com/nomad2/article/details/6949423

版权

J2SE 同时被 2 个专栏收录

62 篇文章

订阅专栏

数据库

36 篇文章

订阅专栏

本文讨论了在使用C3P0数据库连接池时遇到的连接断开问题，以及如何通过调整配置参数解决连接有效性问题。通过设置`preferredTestQuery`、`maxIdleTime`和`testConnectionOnCheckout`来防止过时连接被使用，并避免了异常`DEBUG--CLOSEBYCLIENT`的出现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近遇到了这样的一个问题，使用了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，异常就不会报出了。