3 Replies Latest reply on Apr 15, 2011 2:40 PM by vgarmash

    How to Auto-Reconnect jtds on DB restart

    jboden
      Default How to Auto-Reconnect jtds on DB restart

      Hi,

      I've read that the jtds connection pooling should auto-reconnect if I  restart the DB, but I really can't figure it out. I bet I'm missing  something really basic, but can anyone help? We're using spring on top  of jboss.

      Here is what I have in my applicationContext.xml:

      <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryB  ean">
      <property name="jndiName"><value>java:/MSSQLDS</value></property>
      </bean>

      <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou  rceTransactionManager">
      <property name="dataSource"><ref local="dataSource"/></property>
      </bean>

      And in jtds-ds.xml we have:

      <datasources>
      <local-tx-datasource>
      <jndi-name>MSSQLDS</jndi-name>
      <connection-url>jdbc:jtds:sqlserver://localhost/dbname;namedPipe=true;tds=8.0;lastupdatecount=true   ;sendStringParametersAsUnicode=false</connection-url>

      <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
      <user-name>user</user-name>
      <password>pass</password>
      <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>     
      <background-validation-minutes>5</background-validation-minutes>
      </local-tx-datasource>
      </datasources>


      But if we restart the DB, we then get forever of:

      org.jboss.util.NestedSQLException: Unable to get managed connection for MSSQLDS

      Can anyone help with an example of how to configure an auto re-connect?

      Thanks!
      Edit/Delete Message Reply With Quote Multi-Quote This Message Quick reply to this message
        • 1. Re: How to Auto-Reconnect jtds on DB restart
          thunder.farmer

          As far as I know, the  check-valid-connection-sql will do the trick for you. I verified this on both mysql and informix server.

          I believe your problem lies in spring beans which is cached and hence has no chance to get the newly created connection from JBoss.

           

          I would suggest that you change your datasource bean to "prototype" to try and verify my guess.

          Also you need to config ALL the beans which refer to datasource bean to be "prototype".

           

          BTW, configurating the datasource to be a "prototype" bean is not best practice.

          If you verified that my guess turn out to be true, you need to find sulotion from spring side and not JBoss side.

           

          Regards,

          Thunder

          • 2. Re: How to Auto-Reconnect jtds on DB restart
            hughbragg

            Connection.isValid() isn't implemented in jtds.

            I'm not using jboss, but I found even catching any SQLException and forcing a complete restart of the connection didn't work.

            • 3. Re: How to Auto-Reconnect jtds on DB restart
              vgarmash

              You can try to disable caching of your JNDI datasource in Spring like this:

               

              <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">

                   <property name="jndiName"><value>java:/MSSQLDS</value></property>

                   <property name="cache" value="false" />

                   <property name="proxyInterface" value="javax.sql.DataSource" />

              </bean>

               

              Here is a quote from JndiObjectFactoryBean:

               

              /**

                         * Set whether to cache the JNDI object once it has been located.

                         * Default is "true".

                         * <p>Can be turned off to allow for hot redeployment of JNDI objects.

                         * In this case, the JNDI object will be fetched for each invocation.

                         * <p>For hot redeployment, a proxy interface needs to be specified.

                         * @see #setProxyInterface

                         * @see #setLookupOnStartup

                         */