Hi all,

I'm using Hibernate 2 with struts, tomcat, postgres and a c3p0 connection pool reach thanks to a tomcat datasource.
tomcat and postgres are on 2 differents servers.

After simulating a loss of connection between tomcat and postgres (by disconnecting a cable network, doing a request and reconnecting it), Hibernate/c3po is not able to find a connection anymore...

I have the following stacktrace :
ERROR: Cannot open connection
java.sql.SQLException: com.mchange.v2.resourcepool.ResourcePoolException: Attempted to use a closed or broken resource pool
at com.mchange.v2.resourcepool.BasicResourcePool.ensureNotBroken(BasicResourcePool.java:920)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:191)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:170)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:172)
at com.mchange.v2.c3p0.PoolBackedDataSource.getConnection(PoolBackedDataSource.java:58)
at com.mchange.v2.c3p0.ComboPooledDataSource.getConnection(ComboPooledDataSource.java:260)
at net.sf.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:59)
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:278)
at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3297)
at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3277)
at net.sf.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:40)
at net.sf.hibernate.transaction.JDBCTransactionFactory.beginTransaction(JDBCTransactionFactory.java:19)
at net.sf.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:2220)

As I understand, c3p0 sets my pool as closed or broken and is not able to recover it...

Here are my pool parameters :
<Resource name="jdbc/myDB" auth="Container" type="com.mchange.v2.c3p0.ComboPooledDataSource" />
<ResourceParams name="jdbc/myDB">
<parameter> <name>factory</name> <value>org.apache.naming.factory.BeanFactory</value> </parameter>
<parameter> <name>driverClass</name> <value>org.postgresql.Driver</value> </parameter>
<parameter> <name>jdbcUrl</name> <value>jdbc:postgresql://10.0.0.193/myDB?autoReconnect=true</value> </parameter>
<parameter> <name>user</name> <value>user</value> </parameter>
<parameter> <name>password</name> <value>password</value> </parameter>
<parameter> <name>initialPoolSize</name> <value>5</value> </parameter>
<parameter> <name>minPoolSize</name> <value>5</value> </parameter>
<parameter> <name>maxPoolSize</name> <value>15</value> </parameter>
<parameter> <name>maxIdleTime</name> <value>30</value> </parameter>
<parameter> <name>idleConnectionTestPeriod</name> <value>30</value> </parameter>

How can I configure my pool to keep it alive after this kind of problem ?

Thanks,
Lo

Here is my hibernate.cfg.xml

<hibernate-configuration>

<session-factory>

<property name="connection.datasource">java:comp/env/jdbc/parametres</property>

<property name="dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>
<property name="show_sql">false</property>
<property name="transaction.factory_class">
net.sf.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="hibernate.cache.provider_class">
net.sf.hibernate.cache.HashtableCacheProvider
</property>

<property name="hibernate.use_outer_join">true</property>

<mapping>
.....
<mapping/>
</session-factory>

</hibernate-configuration>

I now use c3p0 0.8.5-pre2 and the problem doesn't happen anymore. This version offer 3 new configuration properties to determine what to do after a loss of connection :

- acquireRetryAttempts
Default: 30
Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to zero, c3p0 will keep trying to fetch a Connection indefinitely

- acquireRetryDelay
Default: 1000
Milliseconds, time c3p0 will wait between acquire attempts.

- breakAfterAcquireFailure
Default: false
If true, a pooled DataSource will declare itself broken and be permanently closeed if a Connection cannot be obtained from the database after making acquireRetryAttempts to acquire one. If false, failure to obtain a Connection will cause all Threads waiting for the pool to acquire a Connection to throw an Exception, but the DataSource will remain valid, and will attempt to acquire again following a call to getConnection().

This thread seems to match my question quite closely. I'm using hibernate and c3po in an application that only needs a single connection. I'm using c3po only to take advantage of statement caching, which is essential in our application.

Here's my problem:

If the there is a problem connecting to the DB (an expected scenario in our application), I need to be able to change the url to include an extra parameter and retry. By default, c3po will retry 30 times and then fail silently if there are connection problems, rather than throwing an exception.

I checked the c3po docs and the following three properties sound useful for this case:

acquireRetryDelay
acquireRetryAttempts
breakAfterAcquireFailure

However, setting these properties in hibernate.cfg.xml does not change the default behavior. Stepping through the hibernate source, it seems any properties other than those listed in the default hibernate.properties file are ignored. Is there a way to shoehorn these properties into the C3PO Pool Configuration?

Or for that matter, am I missing an easier approach to detect and retry an initial connection attempt with C3PO?

Thanks,
Thom

PS. Here is the non-functional code I'd like to get working:

1- download last version of c3p0 (i think acquireRetryDelay,acquireRetryAttempts,breakAfterAcquireFailure are newest features)
2- replace c3p0 jar in lib directory
3- only configure max_size & connection provider in hibernate.cfg.xml
4- add a c3p0.properties in your classpah and fill it with the parameters you need (referer to c3p0 doc)

Please tell me if it works anthony@hibernate.org , i'll update the wiki about c3p0

Hi.

You should be able to set c3p0 configuration parameters that are not mapped into hibernate's config file by defining a file called c3p0.properties in the top level of your classloader's resource path (e.g. at the top of your CLASSPATH). An example properties file with the parameters of interest to you would be...

c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=60
c3p0.breakAfterAcquireFailure=false

Please note that configuration parameters that ARE mapped into hibernate's config file MUST be set within hibernate's configuration, or else they will be overridden by hibernate-specified defaults.

[The hibernate-mapped c3p0 parameters are minPoolSize, maxPoolSize, maxIdleTime, maxStatements, acquireIncrement, testConnectionOnCheckout, and idleConnectionTestPeriod. These map to the fllowing hibernate parameters: hibernate.c3p0.min_size, hibernate.c3p0.max_size, hibernate.c3p0.timeout, hibernate.c3p0.max_statements, hibernate.c3p0.acquire_increment, hibernate.c3p0.validate, and hibernate.c3p0.idle_test_period. DataSources configured in Tomcat should always use c3p0-native parameter names. But pools constructed under the covers by Hibernate must be configured in hibernate's config file with hibernate-defined parameter names where applicable.]

Please see c3p0's docs for more details on configuration parameters.

Regardless of any configuration parameters, a Thread that calls getConnnection() on a c3p0 DataSource that for whatever reason fails to acquire an underlying Connection from the database should see an SQLException. The acquireRetryDelay and acquireRetryAttempts parameters will affect the length of time between the call to getConnection() and the eventual Exception. breakAfterAcquireFailure controls whether, after a full round of acquisition failures, future calls to getConnection() will start trying all over again, or whether the DataSource will simply consider itself broken and throw an immediate Exception. These parameters are new, and only available in prerelease versions of c3p0-0.8.5. Older versions behave as if they had the following configuration: [acquireRetryDelay -> 1000 ms, acquireRetryAttempts -> 30, breakAfterAcquireFailure -> true ]

I hope this is useful!

smiles,
Steve (c3p0 maintainer)

thank you very much steve,
can you update
http://www.hibernate.org/214.html ?

again, thank you

Thanks for the informative response!

I downloaded the very newest C3PO, version 0.8.5-pre4 (was using pre2 previously) and added to my project.

I created c3po.properties at the base of my src directory and made sure this directory was in my classpath.

Added the three properties being discussed and set acquireRetryAttempts=1.

Ran my program -- no change. Still seeing thirty connection attempts spaced at one second. After thirty seconds, I get the following console output:

com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@f7c31d -- Acquisition Attempt Failed!!! Clearing pending acquires.
While trying to acquire a needed new resource, we failed
to succeed more than the maximum number of allowed
acquisition attempts (30).

The SQLException is caught and logged within hibernate, but not rethrown, so application execution continues despite the failure to obtain a connection. When the first attempt is made to obtain a Connection this process repeats, with another thirty attempts, after which the SQLException is received by the application and it terminates.

I tried stepping through again, but was unable to find the spot in code where the c3po.properties are loaded. What I did find is where a PoolConfig instance is being created and passed to C3PO. This PoolConfig contains properties from the hibernate config file, but not the additional properties from the c3po.properties file.




I assume that I have put c3po.properties in the wrong place, but I thought it might be possible that Hibernate is initializing C3PO in such a way that is doesn't bother looking at c3po.properties...

Is it obvious from this post what I'm doing wrong?

Thanks again,
Thom

Thom,

First thing: the file is c3p0.properties, NOT c3po.properties. It's letter-digit-letter-digit, all lower case. This may be your problem.

The c3p0.properties file is loaded internally by the c3p0 library -- there's not an easy way hibernate could prevent this from happening. c3p0.properties just redefines the default configuration values for c3p0 DataSources. Thus, when hibernate directly configures a DataSource using a PoolConfig, that configuration takes precedence of c3p0.properties-defined defaults. This is the reason why you cannnot use c3p0.properties to set minPoolSize, maxPoolSize, maxIdleTime, maxStatements, acquireIncrement, testConnectionOnCheckout, idleConnectionTestPeriod, or initialPoolSize if you are using hibernate -- hibernate sets these properties directly.

To see if you are getting the configuration you expect, you don't have to go through the trouble of waiting for an acquisition failure. c3p0 pooled DataSources helpfully (or annoyingly) dump their full configuration to standard error on initialization, to help you debug this kind of issue.

smiles,
Steve

Hi,

This may be a silly question but I'm trying to find the cause of a memory leak that is killing our application. It may be a coincidence but we see this error when we run out of memory. We are using the jTDS 1.0.1 driver with c3p0 0.8.5 and Hibernate 2.0.8.

Personally, I think the problem may lie elsewhere in a different library we are using (or maybe our code!) but it'd be nice if anyone could remove c3p0/jTDS from the equation.

Cheers

R

R,

I am a jTDS developer and as far as I know jTDS doesn't have any memory leaks. Of course this is only true until proven otherwise.

I would recommend you enable JVM profiling (using the -Xrunhprof parameter), leave the application running for a while, terminate it (either normally or by hitting Ctrl+C) and look into the dump. It's a very low level approach, but works just great.

Alin.

Rilux, were you able to find the cause of your leak? i am also getting a memory leak, and am using hibernate 2 with jtds 1.0.2, we have a memory leak that is crippling our app and I am having trouble lockign it down...

Paul

Paul,

Have you tried profiling? It's impossible to tell what's happening without knowing anything about your code. It could be something in your code or it could be some particular issue in jTDS or Hibernate that no one has encountered yet (although, obviously less likely), but unless you find out at least what type of objects use up your memory there's little anyone can do to help.

Alin,
The jTDS Project.

Hello,

I have the same issue as in this topic : lost connection with mySql is lost for ever ...

I've tried to update my C3PO API, i've put C3PO.0.9.0.2

But i always have the same issue ...

I would try to put the three parameters in c3pO.properties file, i've created it and put i in my WebInf folder, but i d'ont understand how to put it in my classpath ... Isn't only jar in a classpath ???
Note : i'm using Eclipse


Thanks a lot for answers.. I'm lost ....

And... sorry for my english, i'm french .... :-/

In Eclipse, open the Project Properties dialog. Select "Java Build Path", select the "Libraries" tab and click the "Add Class Folder..." button. Select the folder where your c3p0.properties file resides.

Or in the same dialog, check the "Source" location and put the c3po.properties file there.

Stefanie