This is the configuration I have:

My c3p0.properties:
c3p0.acquireRetryAttempts=30
c3p0.breakAfterAcquireFailure=false
c3p0.acquireRetryDelay=1000
c3p0.maxConnectionAge=14400
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckin=true


My hibernate.cfg.xml:
...
<!--connection pool-->
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.max_size">300</property>
<property name="hibernate.c3p0.idle_test_period">3600</property>
<property name="hibernate.c3p0.timeout">10800</property>
<property name="hibernate.c3p0.acquire_increment">5</property>
<property name="hibernate.c3p0.max_statements">150</property>
...



When I start my web app everything works fine but after a while the following C3P0 message is shown, should I worry??:

BTW: this message is shown every hour, as follows:
2008-03-20 14:50:25,401
2008-03-20 15:50:25,365
2008-03-20 16:50:25,366
2008-03-20 17:50:25,375

Message from log:

2008-03-20 17:50:25,375 -- DEBUG -- com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask - BasicResourcePool: An idle resource is broken and will be purged. [com.mchange.v2.c3p0.impl.NewPooledConnection@76abcb]
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1197)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:374)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishIdleResource(C3P0PooledConnectionPool.java:310)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask.run(BasicResourcePool.java:1999)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2586)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1197)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:374)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishIdleResource(C3P0PooledConnectionPool.java:310)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask.run(BasicResourcePool.java:1999)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2008-03-20 17:50:25,376 -- DEBUG -- com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask - BasicResourcePool: An idle resource is broken and will be purged. [com.mchange.v2.c3p0.impl.NewPooledConnection@e2bc79]
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1197)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:374)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishIdleResource(C3P0PooledConnectionPool.java:310)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask.run(BasicResourcePool.java:1999)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2586)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1197)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:374)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishIdleResource(C3P0PooledConnectionPool.java:310)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask.run(BasicResourcePool.java:1999)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

Thank you

It's fine. It has something to do with this setting:





This is instructing c3p0 to test its connections every hour. MySQL closes its idle connections after a while and this detects those connections.



Farzad-

Hi all,

I have exactly the same problem, and I did try to change these values (in bold) to various configurations :

<prop key="hibernate.c3p0.min_size">5</prop>
<prop key="hibernate.c3p0.max_size">100</prop>
<prop key="hibernate.c3p0.timeout">300</prop>
<prop key="hibernate.c3p0.max_statements">50</prop>
<prop key="hibernate.c3p0.idle_test_period">60</prop>
<prop key="hibernate.c3p0.acquire_increment">2</prop>

The only thing it does is changing the time I have to wait to see a crash.
I have tried to set idle test period to same value as timeout, or (as there) something lower.
I also force MySQL to have idle timeout to 28800 (which is the default, but I juste explicitly set it).

Could you explain us more about how it really works, and which values should be set according to others ?

Has it something to with min_size, for instance c3p0 keep 5 connections, event if they are broken ?
In fact I can't understand why a connection pool can still have broken connections is database is reacheable. I though it was exactly why it is designed for ? Is it only a lack of chance, by trying to use a connection that is freshly broken before the idle tester thread have a look to it ?

Thank for every response that you can give me ... I am getting mad !!

You still need to set the flag for testing on borrow and check in. The reason you have this problem is that connections are precious server resources and you do not want to have a bunch of them hanging around for nothing, so yes reducing the minimum size in your connection pool will reduce the likelihood of running into this problem but in any case you need to check the health status of pooled connections before using them. If you look up in this post you will find other properties that will instruct such checks.

Are you talking of this property ?
c3p0.testConnectionOnCheckin=true (found in first post)

I saw in other forums that testing connection each time we want to use it is very expensive and man should use idleTestPeriod instead ?

But there is some thing I can't understand. I change my configuration to set a timeout to 300, with a test period of 60, and a min-size to 1.
Then I launched my application, wait for about 10 minutes, try something ... and get an error.
After 10 minutes, the pool should be empty because the broken connection (because 300 s of timeout) should have been tested ? Does it kept the broken connection to ensure having at least one connection ? Does it create a new one ?

I don't really know the implementation of c3p0 but it might keep the minimum anyhow, or somehow you could get a stale connection because you weren't lucky enough time wise. In any case, I wouldn't worry much about checking the connection on check but I would definitely worry about checking the connection on borrowing. As for performance hit, what kind of application are we talking about? I have used this configuration in a high performance application and it works well. How many hits per second are you expecting?


Farzad-

Nothing too big, in fact something that should not require much connection load ...

I had just with a minimum at zero ... same problem ... and it is really annoying now.

You speak about a configuration, but which one ? What is the property to set to make c3p0 checks at "borrowing" , testConnectionOnCheckin ?

I don't know which version you use but the latest version's property is testConnectionOnCheckout.



Farzad-

Hum ... don't know how to say it ...
In fact I am really sorry with all these questions ...

In my spring configuration, taken from samples, the datasource used is a dbcp one ....
So c3p0 was not used ...

I change the datasource class in the spring config, and it works (with testConnectionOnCheckout = true, didn't try with other values) ...

I am really sorry, and thanks for helping me !