Hi,

I know, that this problem has come up now and then in this forum already, whoever I found nothing matching the annoying behaviour, that I try to fix the last 2 days.

Background:
We are running a Webapplication under Tomcat5, using Hibernate, C3P0 and MaxDB. Tomcat and MaxDB are on different servers.
Normal operation works fine, but the application is only used throughout the day and is idle overnight. Due to maintenance work or networkproblems, the database connections are not sustained overnight. This would not be a problem if a broken DB-connection was handled by the pool or our application.

What happens (can be reproduced every time) is:
- A user signs in and does some work on the system.
He gets a HTTP-session, a Hibernate session and a connection (on first DB-access)

- The user signs out. Http-session is invalidated. Hibernate session is closed. (session.close)

- DB-Connections are taken down. (DB-session timeout or DB-restart). DB comes back up.

- Time passes. Long enough for all idle DB-connections in the C3P0 pool to time out.

- A user signs on. A new Http-session is generated. A new Hibernate session is created.

- The user tries to access the Database.
The connection pool assigns THE OLD, BROKEN connection to the session, which fails consequently.


I tried to adjust the C3P0-config to avoid this behavior:
- set minPoolSize to 0, so that all idle conncetions are removed on timeout (presumably)
- set timeout to 60 secs and idle_test_period to 10 sec. So after 80 idle seconds an idle connection should be history.
- for testing, I set the DB-sessiontimeout to 600 seconds. So a connection should always be timed out by the application.

Still, no change!

My 3 questions are:
- If my application is supposed to detect a broken connection, how do I react to that situation under hibernate? How can I force the pool to drop that connection and give me a different/new one?

- My understanding was, that idle_test_period controls how often the validity of connections in the pool is checked. This does not work or it means something else?

- The notion of holding a number of idle connections in a pool over a prolonged period is an unreliable, "academic" approach. For our application, reliability has priority over performance. Do you know a pool with better characteristics? Are proxool or tomcat5-pooling better for our scenario?

I'd really appreciate your hints on this subject.
Peter

----------------------------------------------------------------------------------
Hibernate version: 2.1

<hibernate-configuration>
<!-- a SessionFactory instance listed as /jndi/name -->
<session-factory name="java:comp/env/hibernate/SessionFactory">
<!-- properties -->
...
<property name="hibernate.c3p0.minPoolSize">0</property>
<property name="hibernate.c3p0.maxPoolSize">20</property>
<property name="hibernate.c3p0.timeout">60</property>
<property name="hibernate.c3p0.idle_test_period">10</property>
<property name="hibernate.c3p0.max_statement">50</property>

<property name="hibernate.transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
...
</session-factory>
</hibernate-configuration>

Pool: C3P0 0.8.5pre7a

Well,

had nothing better to do yesterday evening, so I reviewed our library versions and replaced c3p0 with proxool. Proxool installed quite painless and its got the pool-parameters I was looking for. Initial tests proved, that our problem with broken connections from the pool was gone.

This morning, we found another odd piece of code in our app: It looked as if some had (for testing) instanciated a second sessionFactory with exactly the same configuration as the main one. While c3p0 did not notice, proxool did complain big time. And it was right. We removed the factory and everything seems to be ok now. Maybe that second factory caused the strange behaviour of c3p0.

I definitely prefer proxool, as it logs all its actions nicely on level "debug" and its admin-Servlet gives me valuable insight into pool activity. So we found a connection leak this morning and know where to look for it now.
Also, broken DB-connections are no issue for proxool as it checks them before use and removes them.

phe,

Sorry you had problems with c3p0. I'm sorry about c3p0's unconfigurable logging to standard error; I think that's the library's weakest point. (this will hopefully be remedied c3p0-0.9.0, which will make use of JDK1.4 standard logging. but that'll come after c3p0-0.8.5 is finalized. c3p0-0.8.x is JDK1.3.x compatible.)

c3p0 does have quite extensive tools to permit you to ensure that Connections your application sees are not stale. By default, c3p0 does no Connection testing at all, because Connnection testing can be expensive. But c3p0 (as of v0.8.5-pre7 and above) provides the following parameters to permit as testing that is as cautious or as loose as you'd like:

c3p0.automaticTestTable
c3p0.preferredTestQuery
c3p0.testConnectionOnCheckin
c3p0.testConnectionOnCheckout [maps to hibernate.c3p0.validate]*
c3p0.idleConnectionTestPeriod [maps to hibernate.c3p0.idle_test_period]*

* NOTE that where hibernate maps c3p0 params to hibernate config params, hibernate users MUST use the hibernate versions, in your hibernate configuration, or your settings will be overridden by hibernate specified defaults! Other parameters should be specified in a file called c3p0.properties at the root of your application's effective CLASSPATH. [see http://forum.hibernate.org/viewtopic.ph ... highlight= ]

To reproduce the behavior you are seeing with Proxool -- i.e. to have each Connection tested prior to checkout -- set hibernate.c3p0.validate to true in your hibernate configuration.

Testing every Connection on checkout is the most reliable, but also the most performance costly approach to Connection testing. If you do this with c3p0, I'd recommend you define c3p0.automaticTestTable or c3p0.preferredTestQuery, which may dramatically improve test performance. c3p0.automaticTestTable is the most convenient approach -- just set this to the name of a table c3p0 can create and use to run test queries against.

Another good approach to connection testing is to define c3p0.testConnectionOnCheckin and c3p0.idleConnectionTestPeriod. This is less reliable than testing in checkout, but has an advantage because check-in and idle tests are performed asynchronously by c3p0, and therefore less directly impact client performance. By setting both of these parameters, you can be assured that your Connection has been tested recently prior to a checkout, without putting the test synchronously in the client's code path. [In hibernate, do be sure to set hibernate.c3p0.idle_test_period in your hibernate config and c3p0.testConnectionOnCheckin separately in c3p0 properties file! See above!]

If you do try c3p0 again, there were a few issues with your configuration. The hibernate-mapped properties for controlling the pool's size are hibernate.c3p0.min_size and hibernate.c3p0.max_size. hibernate.c3p0.minPoolSize won't work. c3p0 dumps its configuration on initialization -- if you're having problems, it's a good idea to verify your configuration by double checking this. The hibernate-mapped property for controlling the statement cache size is hibernate.c3p0.max_statements, not hibernate.c3p0.max_statement. The minimum pool size has no impact on Connection testing and verification. c3p0 will cull bad Connections regardless of pool size if the idle check is turned on.

Anyway, I'm sorry you had problems with c3p0. Good luck!

Steve (c3p0 guy)