An issue I've seen much discussed recently and that I'm contending with is the default 8 hour timeout for stale database connections in MySQL. When MySQL drops the connections, the default connection pool and DBCP don't recover, and so you have to restart the context to get things working again. C3P0 is better in that rather than just dying, it catches the failure and then grabs a new connection, but the first request still fails.

One proposed solution is adding autoReconnect=true to your JDBC URL, but this only works if you have auto commit turned on (I verified this by looking at the source for the MySQL driver). In my situation, that's not appropriate.

I've done some testing with C3P0 by itself, and found that one of the configuration properties, maxIdleTime, provides the perfect solution to this problem. In the JavaDoc, it's described as follows:



In my testing, this works exactly as expected. As long as you set the maxIdleTime for your pool to a value lower than the interactive_timeout and wait_timeout settings in MySQL, you'll never have the stale connection problem. Unfortunately, there's no easy way to set this value in the Hibernate context.

Hibernate configures C3P0 by copying Hibernate properties to the C3P0 PoolConfig object when it's instantiated. maxIdleTime isn't one of the properties that Hibernate supports. Under ordinary circumstances, C3P0 will read property values from a file called c3p0.properties in the classpath, but Hibernate instantiates the pool in such a way that this step is skipped.

What I'm wondering is the best way to set this property in my application, which is a Struts application running under Tomcat 4.1.x.

As I see it, there are three ways to make this work (theoretically).

The first is to just hack my c3p0.jar so that it has the value set to what I want. I don't like this approach because I don't want to have a self-hacked c3p0.jar out there.

The second is to hack Hibernate so that it passes through the maxIdleTime property to the PoolConfig when it instantiates a C3P0 pool. If I were to do this, I'd submit the patch back to the project so that it can be included in the future. This seems like a good solution to me if there's interest in adding this property to the set of configurable properties for C3P0.

The third is to not use C3P0 by configuring it using my Hibernate config but rather set it up using JNDI so that I can configure it in any way that I choose. I've been trying to do this but I haven't had any luck getting it to work. Has anyone successfully used Tomcat's JNDI provider to furnish DataSource objects for Hibernate? I tried using C3P0 and also the DBCP support built into Tomcat for this and haven't been successful.

Any advice on where to go next would be appreciated.

Thanks,
Rafe

The property is called:

hibernate.c3p0.timeout

How in the world did I miss that?

Thanks.

Yes I have configured a JNDI Datasource whithin the Application context and I use it with Hibernate.
HEre is the server.xml configuration :

<!-- My Application COnfig -->
<Context path="/myApp" docBase="D:\www" debug="0" reloadable="true">
<Resource name="jdbc/DS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/SS">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>username</name><value>login</value></parameter>
<parameter><name>password</name><value>password</value></parameter>
<parameter><name>driverClassName</name><value>com.mysql.jdbc.Driver</value></parameter>
<parameter><name>url</name><value>jdbc:mysql://localhost/MYBASE?autoReconnect=true</value></parameter>
<parameter><name>maxActive</name><value>20</value></parameter>
<parameter><name>maxIdle</name><value>2</value></parameter>
</ResourceParams>

</Context>


An the hibernate.properties file :
hibernate.connection.datasource=java:comp/env/jdbc/DS

hibernate.dialect = cirrus.hibernate.sql.MySQLDialect
hibernate.show_sql = false
It works well until now !

think this would work with DBCP as well?

I'm having trouble with Hibernate 1.2.


(6 hours)

would this help?

I'll test it, but perhaps someone has an answer before 8 hours have passed. :-)

WILL

umm... never mind. maxIdle's the wrong property, it means something different for DBCP.

Anyone have a suggestion for a way to do this with DBCP with Hibernate 1.2? I've tried setting a validation query (works with Hibernate 2) but apparently not with Hibernate 1.2

WILL

Yes I make my pool works under Tomcat and DBCP with Hibernate 1.2.

It works perfectly.

You could use those dbcp properties :
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>

When available db connections run low DBCP will recover and recyle any abandoned dB connections it finds. The default is false.

Use the removeAbandonedTimeout parameter to set the number of seconds a dB connection has been idle before it is considered abandoned.

<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>

Just try it ! and tell me.

rafeco, could you post your mysql and c3p0 settings? I'm having the same problem, even using the c3p0 timeout, and I'd like to see some settings that someone has gotten to work. :)

Thanks!

Bueller? Bueller? Anyone? :P

Ack, sorry I'm so late on this. Here are the settings that worked for me:

[/code]