2 Replies Latest reply on Apr 3, 2008 10:56 AM by Roman Pavlov

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

    Gretchen Moran Newbie

      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>