JTDS and JBOSS JDBC Connection Pool Problem, any solution? Maybe a custom ValidConnectionChecker?

Question

Hello,

I'm facing a weird production problem. Environment is the following:

• JBOSS 4.0.2
• SQL Server 2005
• Driver JTDS 1.2.5

From time to time the following szenario occurs.

A SQL command fails to Excute with

 java.sql.SQLException: I/O Error: Read timed out 

(I can live with that, if it just happens twice a day or so)

But from that moment on the connection seems to be wasted without the pool recognizing it, as I continously receive

java.sql.SQLException: Invalid state, the Connection object is closed.

from that moment on. The only thing that helps is restarting JBOSS. This occurs despite of the fact that I have

 <check-valid-connection-sql>select getdate()</check-valid-connection-sql>

set up in my Datasource definition.

I was wondering if I can use a custom ValidConnectionChecker, that either rebuilds the connection itself, or explicitly throws a Exception to fix this. Maybe anyone has other suggestions.

Here is my complete DS definition.

  <local-tx-datasource>
    <jndi-name>MyDS</jndi-name>
    <connection-url>jdbc:jtds:sqlserver://192.168.35.235:1433/MyDb;user=user1;password=pwd;appName=MyApp;loginTimeout=15;socketTimeout=120</connection-url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <user-name>user1</user-name>
    <password>pwd</password>
    <min-pool-size>10</min-pool-size>
    <max-pool-size>25</max-pool-size>
    <blocking-timeout-millis>60000</blocking-timeout-millis>
    <idle-timeout-minutes>1</idle-timeout-minutes>
    <check-valid-connection-sql>select getdate()</check-valid-connection-sql>
  </local-tx-datasource>

Any help appriciated.

Regards

Answer 1

Connection.isValid() isn't implemented in jtds. I found even catching the exception and forcing a complete restart of the connection didn't work.

Answer 2

Try changing your driver class line to net.sourceforge.jtds.jdbcx.JtdsDataSource. net.sourceforge.jtds.jdbc.Driver doesn't implement the javax.sql.ConnectionPoolDataSource interface. source: http://jtds.sourceforge.net/faq.html#features