Connection handle has been closed and is unusable

[msduk]

Hi. There is a huge amount of noise relating to this error all over the place. I'm not really sure if it is a seam/AS/Hibernate issue.

The stack is as follows

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.prepareStatement(WrappedConnection.java:184)
 at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
 at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
 at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
 at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1560)
 at org.hibernate.loader.Loader.doQuery(Loader.java:661)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
 at org.hibernate.loader.Loader.loadCollection(Loader.java:1918)

This was occurring frequently and I added

<valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
 <check-valid-connection-sql>SELECT count(warehouseId) FROM warehouse</check-valid-connection-sql>

which I dont think had an effect (the errors were still common). I upgraded the hibernate versions in Seam 1.2.1 to the latest hibernate which definitely reduced the frequency however they are still there. Any suggestions welcome.


(Just in case it helps)
One thing I did notice which admittedly is likely a red herring is that the errors seem to happen where concurrent modification is likely i.e. separate threads modifying the same data. As one thread is only reading ever then I cant see that this could be an issue.

[alanlynott]

I'm getting the same problem. I'm wondering whether it's mySql because I've been told it's concurrent transaction handling is poor. I'm going to move to Postgres today to see if there are any improvements.

Thanks, AL.

[monkeyden]

Check the manifest file in the hibernate3.jar file. If it's 3.2.0 GA, upgrade it to 3.2.5GA. I just had this problem the other day.


reference:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2206

download:
http://www.hibernate.org/6.html

[monkeyden]

I upgraded the hibernate versions in Seam 1.2.1 to the latest hibernate which definitely reduced the frequency however they are still there. Any suggestions welcome.

Oops, sorry. It's 2:00AM here. Sleepy.

[monkeyden]

Incidentally, why are you using an aggregate function in your test SQL statement? Doesn't MySQL have a dummy table like Oracle "dual", where you can write a simple query like:

SELECT 1 FROM dual

[msduk]

I'm getting the same problem. I'm wondering whether it's mySql because I've been told it's concurrent transaction handling is poor. I'm going to move to Postgres today to see if there are any improvements.

Thanks, AL.

I am pretty sure it isn't down to mysql's concurrency ability but I did think it might be some configuration issue or one with the drivers. Grateful for the feedback.

[msduk]

SELECT 1 is actually valid mysql syntax (although I have never understood why). I will switch to that and save some cpu cycles.

[monkeyden]

The real question is, how do you ever see the issue when using <check-valid-connection-sql>? The connection is obviously bad but that fact wasn't picked up by the check. Post the whole <local-tx-datasource> node, minus the p/w, of course.

[msduk]

<local-tx-datasource>
 <jndi-name>nrg2Datasource</jndi-name>
 <connection-url>jdbc:mysql://localhost:3306/db</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>db</user-name>
 <password>pass</password>
 <exception-sorter-class-name>
 org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
 </exception-sorter-class-name>
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
 <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
 <check-valid-connection-sql>SELECT count(warehouseId) FROM warehouse</check-valid-connection-sql>
 <new-connection-sql>SELECT count(warehouseId) FROM warehouse</new-connection-sql>
 </local-tx-datasource>

[mjz]

this sounds strangely familiar... looking back at my -ds.xml ive got failed attempts commented out all over the place, but the last two days tags seem to have resolved the issue( configure by coincidence i always say):

...
 <!-- 20march2007-->
 <track-statements>true</track-statements>
 <min-pool-size>0</min-pool-size>

 <!-- change pooling to use jta 21march2007 -->
 <max-pool-size>20</max-pool-size>
 <idle-timeout-minutes>4</idle-timeout-minutes>
 </local-tx-datasource>
</datasources>

dunno what that jta pooling bits about

/matt