"Connection handle has been closed and is unusable" when usi

[gmoran]

We are getting the following error when using a JNDI datasource and Hibernate:

java.sql.SQLException: Connection handle has been closed and is unusable
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:537)
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:524)
at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConnection.java:333)
at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)
at org.pentaho.repository.HibernateUtil.commitTransaction(HibernateUtil.java:377)
at org.pentaho.repository.HibernateUtil.systemExitPoint(HibernateUtil.java:631)
at org.pentaho.core.system.StandaloneApplicationContext.invokeExitPoints(StandaloneApplicationContext.java:132)
at org.pentaho.core.system.PentahoSystem.systemExitPoint(PentahoSystem.java:1278)
at org.pentaho.ui.servlet.GetImage.doPost(GetImage.java:135)

Caused by: java.sql.SQLException: Connection handle has been closed and is unusable
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:537)
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:524)
at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConnection.java:333)
at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)
... 65 more

I have seen several forum posts regarding this issue, some old, some new and have tried all the bits and twiddles suggested to fix the problem, but to no avail. Here is the list of suggestions that I have tried:

1. setting several hibernate properties in the config file, such as hibernate.connection.release_mode and hibernate.statement_cache.size
2. setting several properties in the datasource, such as <new-connection-sql>, <check-valid-connection-sql>, <valid-connection-checker-class-name>, and <min-pool-size> set to zero.
3. Tried commenting out all instances of the org.jboss.resource.connectionmanager.CachedConnectionInterceptor in the standardjboss.xml.

None of these suggestions resolved my issue, although setting the properties on the datasource and Hibernate did lead to a different exception - the message is "You cannot commit with autocommit set to true." A whole new can of worms.

I have reproduced the same problem with both Oracle and MySQL, so I'm fairly certain this is not some wierd driver problem.

My suspicion is with the JNDI configuration, we should not be trying to commit or rollback any transaction or connections. Can someone please confirm that this is causing our problems, or give me some insight as to what we may be doing wrong?

Here is our hibernate.cfg.xml file:

<property name="hibernate.generate_statistics">true</property>
 <property name="hibernate.cache.use_query_cache">true</property>
 <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
 <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
 <property name="connection.datasource">java:Hibernate</property>
 <property name="hibernate.statement_cache.size">0</property>
 <property name="hibernate.connection.release_mode">after_statement</property>
 <property name="connection.pool_size">10</property>
 <property name="show_sql">false</property>
 <property name="hibernate.jdbc.use_streams_for_binary">true</property>

... and our -ds.xml file:

<local-tx-datasource>

 <jndi-name>Hibernate</jndi-name>
 <connection-url>jdbc:oracle:oci:@localhost:1521:hibernate</connection-url>
 <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
 <user-name>XXX</user-name>
 <password>XXX</password>

 <new-connection-sql>select 1 from dual</new-connection-sql>
 <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
 <max-pool-size>20</max-pool-size>
 <min-pool-size>5</min-pool-size>
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
 <metadata>
 <type-mapping>Oracle10g</type-mapping>
 </metadata>
 <connection-property name="autoCommit">false</connection-property>
 </local-tx-datasource>

[gmoran]

Also, for detailed reproduction of our search for a solution, visit http://jira.pentaho.org/browse/BISERVER-474.

Thanks in advance for any assistance,
G

[pavlovrv]

This is not hibernate configuration issue and not data source problem but a concurrency connection usage problem. The problem is definitely in your code.

I've spent 6 days on similar problem. The same exception was thrown only under load testing with chance of occurrence about 0.08% (8 exceptions on 10000 requests).

I tried all possible settings for jboss data source (local-tx and xa datasource were tried too), all settings of hibernate which were posted in forums as "last two rows helps me". Actually they just decrease the chance of occurrence but it is not a complete solution.

Following links helps me to understand in which direction I have to look:
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=42481
http://forum.java.sun.com/thread.jspa?threadID=782570

In my case it was Seam EntityManager field in component with ScopeType.APPLICATION it was used from several places. Added @Synchronized solves this problem.