hibernate + primrose pool connection closed after some time

santiagozky · **Joined:** Fri Apr 03, 2009 4:12 pm **Posts:** 9

I have an application running under a tomcat with a primrose pool. The application uses the datasource for connecting to an oracle database.

The problem is that after several hours of deploying/restrting the app I start getting "closed connection" exceptions:

Code:

org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2223)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
        at org.hibernate.loader.Loader.list(Loader.java:2099)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    .....
    .....
Caused by: java.sql.SQLException: Closed Connection
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:219)
        at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:337)
        at uk.org.primrose.pool.core.PoolConnection.prepareStatement(PoolConnection.java:186)
        at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
        at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
        at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
        at org.hibernate.loader.Loader.doQuery(Loader.java:673)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2220)

  

I suppose this is because primrose is dropping the connection

Unfourtunately I cannot find any documentation about hibernate + primrose or how to make hibernate reconnect to the database. The only hint I got was to use

Code:

But I also read that this was not recommended (Don´t know why)

this is the hibernate conf (V3.2 using annotations)

Code:

<hibernate-configuration>

<session-factory>
   <property name="hibernate.hbm2ddl.auto">update</property>
   <property name="connection.datasource">
      java:comp/env/l2bhiponac
   </property>
   <property name="dialect">
      org.hibernate.dialect.Oracle9Dialect
   </property>
   <property name="hibernate.show_sql">false</property>
   <property name="hibernate.cglib.use_reflection_optimizer">false</property>
   <property name="hibernate.cache.use_second_level_cache">false</property>
</session-factory>

</hibernate-configuration>

dthe pool config looks like this:

Code:

poolName=l2bhiponac
base=5
overflow=5
log=/var/log/pools-dev.l2bhiponac-${yyyy-MM-dd}.log
idleTime=60000
messageLogging=false
sizeLogging=true
driverClass=oracle.jdbc.driver.OracleDriver
driverURL=jdbc:oracle:thin:@XXX.XX.XX.XX:YY:secret
user=secret
password=verysecret
killActiveConnectionsOverAge=3000
cycleConnections=1000
queueConnectionRequests=true
runPooledMode=true

I would really like to keep the pool since is the same pool we use for the others applications (they doesnt use hibernate) and I have to put the app in many tomcats with the pool using different users.

Any idea of how can I resolve this?

Thank you in advance.

santiagozky · **Joined:** Fri Apr 03, 2009 4:12 pm **Posts:** 9

Hello again,

The IT guys just give me some information about what is going on. Apparently the application is using all the available conections from the pool .We tried to allow more conections but it only make the problem appear after a longer time.

I don't know why the pool is being overused. All my transactions look like this:

Code:

CorredorDAO DAO=new CorredorDAO();
try {
DAO.getSession().beginTransaction();
DAO.findSomething();
DAO.saveThis();
....
...
DAO.getSession().getTransaction().commit();
} catch (HibernateException e) {
   e.printStackTrace();
}finally{
   DAO.getSession().flush();
   DAO.getSession().clear();
   DAO.getSession().close();
   DAO.getSession().disconnect();
}

Before I was only committing and flushing but In my desperation I added the close and disconnect calls with the hope of solving the problem.

The session if obtained from a call to sessionFactory.openSession()

Any ideas of how can I solve the problem?