I have a service application that uses Hibernate and c3p0 to continuously poll an Oracle 9i database. If the database/connection goes down, I would like my application to try and reconnect to the database. I have set the settings in c3p0 (see below) which I think should do this. However, these settings only seem to work if the database is down when my application first tries to connect to it. If my application is in the middle of running, c3p0 doesn't try to reconnect to the database. It just throws an error. The stack trace below is what happens when I kill the session on the database while the application is running. I am catching HibernateException in my code.

Is this type of functionality possible with Hibernate and c3p0?
Thanks.
sliepmann

Hibernate version: 2.1.6 with c3p0-0.8.5-pre9

Full stack trace of any exception that occurs:

com.mchange.v2.c3p0.impl.NewPooledConnection@1c2dad7 invalidated by Exception: java.sql.SQLException: ORA-00028: your session has been killed

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Ocommoncall.receive(Ocommoncall.java:138)
at oracle.jdbc.ttc7.TTC7Protocol.setAutoCommit(TTC7Protocol.java:538)
at oracle.jdbc.driver.OracleConnection.setAutoCommit(OracleConnection.java:1220)
at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:705)
at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:35)
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:286)
at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3326)
at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3286)
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:2231)
at ... (application stack trace here)
CONNECTION ERROR OCCURRED!

(more application stack trace here)
Caused by: net.sf.hibernate.JDBCException: Cannot open connection
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:289)
at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3326)
at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3286)
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:2231)
at com.vmi.hl7.cardioims.hibernate.HibernateUtilHl7.beginTransaction(HibernateUtilHl7.java:194)
... 5 more
Caused by: java.sql.SQLException: ORA-00028: your session has been killed

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Ocommoncall.receive(Ocommoncall.java:138)
at oracle.jdbc.ttc7.TTC7Protocol.setAutoCommit(TTC7Protocol.java:538)
at oracle.jdbc.driver.OracleConnection.setAutoCommit(OracleConnection.java:1220)
at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:705)
at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:35)
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:286)
... 11 more

Name and version of the database you are using: Oracle 9i

Relevant Hibernate Properties
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.acquire_increment">1</property>

c3p0 Properties:
c3p0.acquireRetryDelay=30000
c3p0.acquireRetryAttempts=0
c3p0.breakAfterAcquireFailure=false

Stefanie,

A c3p0 pool with the settings you have should recover from a database reset, but that doesn't mean you will never see an Exception. Stale Connections from the old database session will still be broken, and if those Connections have already been checked out, or if they are in the pool and not tested on checkout, the application will see the broken Connection, in the form of an Exception.

You can use c3p0 to minimize the likelihood that your application will see a stale Connection on database shutdown/restart. The most reliable means of preventing this is to set hibernate.c3p0.validate to true (in a hibernate application -- all other c3p0 apps should use the c3p0-native property c3p0.testConnectionOnCheckout). If you set this property to true, c3p0 will test Connections prior to checkout, and your app will never see a stale Connection on database restart unless the Connection had already been checked out when the database went down.

Another less reliable, but potentially less expensive, strategy is to set c3p0.testConnectionsOnCheckin and hibernate.c3p0.idle_test_period (c3p0-native c3p0.idleConnectionTestPeriod) to a low value, in which case all connection tests are asynchronous and you are guanteed that no Connection will be checked out that hasn't been tested in the last idle_test_period seconds. Thus, your app will only see broken Connections from the pool if Connections are checked out during a short window of time.

In either case, I recommend setting "c3p0.preferredTestQuery" or "c3p0.automaticTestTable" in your c3p0 properties file, as c3p0's default Connection test is often slow.

See "Configuring Connection Testing" in c3p0's docs for more information.

Hope this helps!
Steve (c3p0 guy)

Hi. I'm working with Hibernate3, JBoss 4.0.2 and Oracle10g, and I'm using c3p0 settings bellow:
- hibernate.cfg.xml
<!-- configuration pool via c3p0-->
<!-- seconds -->
<property name="hibernate.c3p0.acquire_increment">3</property>
<property name="hibernate.c3p0.idle_test_period">600</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.min_size">10</property>
- c3p0-service.xml
<attribute name="AcquireRetryAttempts">30</attribute>
<attribute name="AcquireRetryDelay">1000</attribute>
<attribute name="BreakAfterAcquireFailure">false</attribute>
<attribute name="PreferredTestQuery">select dummy from dual</attribute>
<attribute name="TestConnectionOnCheckin">false</attribute>
<attribute name="TestConnectionOnCheckout">true</attribute>

But when I start JBoss when oracle is down, c3p0 cannot set the pool of connections. But when database has been started again, c3p0 doesn't retry to get pool of connections and show this error when I try to do some hibernate query:

org.hibernate.AssertionFailure: scrollable result sets are not enabled
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.scroll(Loader.java:1634)
at org.hibernate.loader.criteria.CriteriaLoader.scroll(CriteriaLoader.java:106)
at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1287)
at org.hibernate.impl.CriteriaImpl.scroll(CriteriaImpl.java:320)
at com.colt.aptcache.dao.ServiceDAO.searchServices(ServiceDAO.java:442)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)


What is wrong in my c3p0 settings? Can someone help me?

Tks.
Eliza.