Geek Answers Handbook

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

I had a strange production problem. Environment:

  • JBOSS 4.0.2
  • SQL Server 2005
  • JTDS 1.2.5 driver

From time to time, the following scenario occurs.

SQL command cannot invoke with

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

(I can live with it if this happens only twice a day or so)

But from now on, the connection seems to have disappeared without a pool recognizing it, as I constantly get

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

from this moment. The only thing that helps is restarting JBOSS. This happens despite the fact that I have

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

configured in my Datasource definition.

I was wondering if I can use a special ValidConnectionChecker, which either rebuilds the connection itself or explicitly throws an exception to fix this. Maybe someone has other suggestions.

Here is my complete definition of DS.

  <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 is appriciated.

Hi

+4
source share
3 answers

Try changing the driver class string to net.sourceforge.jtds.jdbcx.JtdsDataSource. net.sourceforge.jtds.jdbc.Driver does not implement the javax.sql.ConnectionPoolDataSource interface. source: http://jtds.sourceforge.net/faq.html#features

+7
source

Maybe the solution is too late, but I'm stuck here with the jtds driver. Hope this saves half an hour of your productive time.

The fix is ​​to specify validationQuery to implement the Apache dbcp2 connection pool. For jtds / sql server, I set spring configuration as follows:

 <bean id="sqlServerDS" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" > <property name="driverClassName" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <property name="defaultReadOnly" value="true" /> <property name="validationQuery" value="select 1" /> </bean>

If you are not using Spring, call the setValidationQuery method on the BasicDataSource in your Java code.

 BasicDataSource bds = new BasicDataSource(); bds.setValidationQuery("select 1");
+3
source

Connection.isValid() not implemented in JTDS. I found that I even caught the exception and force a full restart of the connection did not work.

0
source

More articles:


All Articles