Announcement Announcement Module
Collapse
No announcement yet.
recovering from SQL connection loses... Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • recovering from SQL connection loses...

    I'm deploying an application using Spring and Hibernate 3.0.5 to a WebLogic 8.1 SP4 server. Things are working fine *UNLESS* the database connection goes down. Then I see the following errors from spring in my code..

    org.springframework.jdbc.UncategorizedSQLException : Hibernate operation: could not execute query; uncategorized SQLException for SQL [sql removed] SQL state [null]; error code [17410]; No more data to read from socket; nested exception is java.sql.SQLException:

    org.springframework.jdbc.UncategorizedSQLException : Hibernate operation: could not execute query; uncategorized SQLException for SQL [sql removed] Must be logged on to server; nested exception is java.sql.SQLException: Must be logged on to server

    Now, I know the problem is that I've lost the underlying WebLogic datasource and/or connection - but how do I *recover* from this...?

  • #2
    I have poked around this problem for a while. The best solution I've been able to come up with is below. I mostly wrote this for deadlock retry, but would work for lost connections as well. I haven't actually had a chance to try it yet, so YMMV.

    - Create a new exception RetryException (or whatever you like) which is a sublcass of a DataAccessException.

    - Create a an exception tranlator (I subclassed mine from SQLErrorCodeSQLExceptionTranslator and overrode customTranslate) which can convert the error code indicating a to a RetryException. Use this with your JdbcTemplate. I use a JdbcDaoSupport object to subclass which lets you specify the translator but delegating the JdbcTemplate creation to the class.

    - Create a MethodInterceptor to implement the retry logic. Here is one that I wrote which can be configured to retry a RetryException a set number of times before it gives up and throws the RetryException:

    Code:
    public class RetryInterceptor
        implements MethodInterceptor {
    
      private int retry = 0; // Defaults to no retries, bombs out on first failure.
      private long delay = 0;
    
      public void setDelay(long delay) {
        this.delay = delay;
      }
    
      public void setRetry(int retry) {
        this.retry = retry;
      }
    
      public Object invoke(MethodInvocation invocation) throws Throwable {
        for &#40;int i = 0; i < retry; i++&#41; &#123;
          try &#123;
            return invocation.proceed&#40;&#41;;
          &#125;
          catch &#40;RetryException e&#41; &#123;
            // Escape out of # of retries has been reached
            if &#40;i >= retry&#41;
              throw e;
            if &#40;delay > 0&#41;
              Thread.currentThread&#40;&#41;.sleep&#40;delay&#41;;
          &#125;
        &#125;
      &#125;
    &#125;
    - Use this interceptor in your proxy. You should make sure this interceptor wraps the transaction interceptor. This is to make sure that when invocation.proceed() is invoked that the code it calls attempts to get a new connection.

    Comment


    • #3
      Use weblogic datasource config options

      The weblogic datasource has some optional settings that you should turn on:
      Test Reserved Connections
      Specifies whether WebLogic Server tests a connection before giving it to the client. (You must specify a Test Table Name below.)
      Test Created Connections
      Specifies whether WebLogic Server tests a connection after creating it but before adding it to the list of connections available in the pool. (You must specify a Test Table Name.)
      Test Released Connections
      Specifies whether WebLogic Server tests a connection before returning it to this JDBC connection pool. (You must specify a Test Table Name.)
      ......
      Test Table Name: SQL SELECT 1 FROM DUAL
      The name of the database table to use when testing physical database connections. This field is required when you specify a Test Frequency and enable Test Reserved Connections, Test Created Connections, and Test Released Connections.
      These kind of settings should prevent your application's code to use dead connections.
      Anyway, loosing connection to your database is a major problem and you should investigate this problem. The datasource setting just help you minize the efects of the problem, they don't solve it.

      Comment


      • #4
        You can try using a different datasource provider - like c3p0. We got the same problem with the awful MySQL that likes to close connections after they have been idle for 8h or something like that. With c3p0 you can force the connections to be checked before they are aquired which will open basically a new connection.
        However, as croco said you should prevent this at db level (with MySQL I couldn't find a way to do so) because the checks on the datasource are expensive.

        Comment


        • #5
          I think the problem is if you lose the connection in the middle of processing. I've seen cases where the database gets confused enough that a particular SPID gets hung and has to be killed. Not because of an unreasonable or flawed request, but because of some other issue. At that point you'd like the code to try and recover from that.

          Comment


          • #6
            Re: Use weblogic datasource config options

            Originally posted by croco
            The weblogic datasource has some optional settings that you should turn on:
            Test Reserved Connections
            Specifies whether WebLogic Server tests a connection before giving it to the client. (You must specify a Test Table Name below.)
            Test Created Connections
            Specifies whether WebLogic Server tests a connection after creating it but before adding it to the list of connections available in the pool. (You must specify a Test Table Name.)
            Test Released Connections
            Specifies whether WebLogic Server tests a connection before returning it to this JDBC connection pool. (You must specify a Test Table Name.)
            ......
            Test Table Name: SQL SELECT 1 FROM DUAL
            The name of the database table to use when testing physical database connections. This field is required when you specify a Test Frequency and enable Test Reserved Connections, Test Created Connections, and Test Released Connections.
            These kind of settings should prevent your application's code to use dead connections.
            Anyway, loosing connection to your database is a major problem and you should investigate this problem. The datasource setting just help you minize the efects of the problem, they don't solve it.
            Croco,

            Thanks I found those and they provided a perfect solution to the problem.

            Comment


            • #7
              Originally posted by costin
              You can try using a different datasource provider - like c3p0. We got the same problem with the awful MySQL that likes to close connections after they have been idle for 8h or something like that. With c3p0 you can force the connections to be checked before they are aquired which will open basically a new connection.
              However, as croco said you should prevent this at db level (with MySQL I couldn't find a way to do so) because the checks on the datasource are expensive.
              Costin, do you mean that adding these parameters to the connection url are expensive?
              Code:
              jdbc&#58;mysql&#58;//localhost/database?autoReconnect=true&autoReconnectForPools=true&reconnectAtTxEnd=true&failOverReadOnly=false

              Comment


              • #8
                The settings for mysql are not necessarily expensive but the ones for the datasource are. Depending on the exact settings (check the c3p0 manual), the datasource before doing a simple query will verify the connection by doing a a check query.

                Comment


                • #9
                  Stale JDBC Connections exception

                  I know this is an old topic, but would like to see if the Spring team has new ideas on this.

                  This problem of stale JDBC connections has plagued developers for a long time and there are so many funny ways to handle that. croco mentioned weblogic's way of testing connections from the pool before handing them out (I know Websphere has the same feature), but that is not really acceptable that is one DB round-trip overhead per getConnection for all calls (in our production environment, probably less than 1% calls get Stale connections).

                  Websphere tries to help by mapping SQL error codes from different DB vendors into their proprietary com.ibm.xxxx.StaleConnectionException (and that xxxx package name changes from one version to another )

                  Now JDBC 4.0 finally got it right by having a branch of Transient Exceptions that caters for this kind of exceptions that should go away with a retry.

                  I think this an opportunity for Spring to add value here:

                  First, not all people can use JDBC 4.0 drivers just yet....Spring can provide a consistent exception handling model across old JDBC and new. The DataAccessException hierarchy should be re-structured to include the concept of transient exceptions and help map stale connection exceptions to a subclass of transient exception.

                  Second, the stale connection exception only bombs when you actually execute something with it, and you don't really want developers to put a retry loop around each JDBC execution. Can we have a jdbcTemplate that retry on transient exceptions (optionally, either through config or method variants, or even as an AOP advice)?

                  Thanks,

                  Joe
                  Last edited by joelee; May 9th, 2007, 01:46 AM.

                  Comment


                  • #10
                    StaleConnection handling in Spring

                    Anyone from Spring team have response to Joelee's post?

                    In Websphere 6 can't monkey with conn pool settings to prevent in all cases StaleConnection.

                    Our old homegrown jdbc framework easily handled this because all sql went through one place.

                    Isn't there an extension point on JDBCTemplate to add a short sleep and a retry?

                    Sure as heck don't want to have to create and educate our team about a new aspect for every tiny little piece of functionality like this?

                    Thanks I'm a newbie with Spring so probably missed it in Spring2 reference manual.

                    Comment


                    • #11
                      There is a more fully-featured retry framework in Spring Batch (http://static.springframwork.org/spring-batch). In the Infrastructure module (single jar file) look for RetryOperations and RetryOperationsInterceptor.

                      Comment

                      Working...
                      X