Results 1 to 4 of 4
  1. #1
    jrjan is offline Member
    Join Date
    Jan 2009
    Posts
    2
    Rep Power
    0

    Unhappy connection fails

    I have this application that has been live now for a couple of months. A couple times per day, a user will get a connection failed error. This is an ingres database, and I am using connection pooling. When they hit refresh, everything works. I originally was thinking it was a connection pooling problem, but that doesn't make sense since I only get a connection once and pass it around. For some reason, it's dropping the connection. I am using the same DAOUtil file for ingres and oracle and this only happens with Ingres. (I should mention that I didn't write this, but am stuck debugging since I put it on a new server and nobody else has access but me.)

    So in doPost I get a connection. Connection con = getConnection(blah, blah) That works fine.

    Then it calls a function - registrationForm(Connection con, blah, blah)
    That is fine too.

    Then I create a Statement stmt = con.createStatement();
    That's fine too.

    If I check it by <!-- con="+con+" --> at each place, there is a value in con, all the same hex numbers, so it is passing around the same connection successfully. I can even check <!-- stmt="+stmt+" --> and that is working fine.

    (The log isn't working right on the new vms yet, so I can't print this stuff to the log, I have to use the hidden html and view the source code from the browser.)

    Then I had some other calls to con -

    con.setAutoCommit(false);
    con.setTransactionIsolation(con.TRANSACTION_READ_U NCOMMITTED);//readlock=nolock
    rs = stmt.executeQuery(query);

    Whichever line is next, blows up. I removed the first 2 calls and went straight to the execute statement, and it blows up there.

    It is almost impossible to test because it doesn't happen very often. I can do it 100 times successfully and it won't blow up on me, but 3 or 4 times a day, a user gets the connection failed error. I have not been able to duplicate it, and the log only points me to the first call to con after creating the statement.

    Any ideas at all will be appreciated.

  2. #2
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    8

    Default Sounds like the database

    Based on what you said, it sounds like a problem with Ingres and its interaction with JDBC, since you don't have this problem with Oracle. Here are a few things I would try, as in, see what happens.

    1. Expire your connections at regular intervals. It's possible that the application is doing the same thing one too many times on a single connection. That shouldn't be an issue, but "should" and "are" don't always match.

    2. Create PreparedStatement's against each connection and reuse them. One way to do this is to create a static Map with the connection as the key and a second Map as the value. The second map has the String used to create each PreparedStatement as the key and the PreparedStatement as the value. If either map is missing an entry, create it; otherwise, just use the PreparedStatement. I put this code where I prepare the statement. First, I try to retrieve the statement. If it doesn't exist, I go ahead and create it and save it. I have a framework to avoid duplicating the code, but the code is simple enough that you can just in-line it. Since the connection pool should ensure only one thread is using the connection at a time, you won't need to synchronize the use of the PreparedStatement's.

    I say this because createStatement involves a lot of overhead and it requires the database to keep track of all the statements. This could be the source of your problem. Regardless, your application will perform better.

    3. Add error handling to the application. If a connection goes bad, tell the pool to close it, get a new one, and try again.

    I hate these kinds of errors. I hope you can get the logging going soon, that will make everything a lot easier...

  3. #3
    jrjan is offline Member
    Join Date
    Jan 2009
    Posts
    2
    Rep Power
    0

    Default connection failed

    Thank you for your reply.

    I have tried opening and closing the connection in every method, but that didn't make a difference.

    I tried if(con.isClosed()), but it never went inside that if, so I don't think it's actually closed. The error says failed, I'm not sure why that doesn't mean it is closed, or what else it could mean, but the if doesn't work. What is the best way to test the connection prior to running the query?

    There are preparedStatements elsewhere for complex queries, but this is just a simple query, so they didn't do one here. I hate to mess with the code too much, since I didn't write it, but if this is my last option, I will try this.

  4. #4
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    8

    Default A cheesy quick fix

    Connections have to be explicitly closed. Also, I don't think there is a simple way to test a connection before using it.

    As far as PreparedStatement's, that doesn't sound like a good idea in your current situation. That was more of a long-term solution that would involve some careful redesign.

    One way to "test" the connection would be create a utility class with a static method. This isn't real code, BTW.

    Java Code:
    public class ConnTester {
    
      public static Connection testConnection(final Connection pConn) {
        Connection conn = pConn;
        try {
          execute some something like a get schema command
        }
        catch (ConnectionFailedException e) {
          conn = create a new connection
        }
        return conn;
      }
    }
    Basically, you try out the connection with a dummy statement right before you use it. If it blows, you replace it. You always use the connection returned by the utility. 99% of the time, its the one you passed in. 1% of the time, it's a new one.

    I'm assuming the connection won't fail right after it works, but that could be completely wrong. Still, it's a quick patch you could try until you can figure out the root cause. The tester, hopefully, will give you a single point to log failures.

    Good luck. Your problem is a real stinker. Also, check the database logs. They may be showing exceptions.

Similar Threads

  1. Over-riding purpose fails..
    By udayadas in forum New To Java
    Replies: 7
    Last Post: 08-24-2008, 05:14 AM
  2. Replies: 0
    Last Post: 06-25-2008, 01:41 PM
  3. Setting cookie fails when jarred
    By damounh in forum Java Applets
    Replies: 0
    Last Post: 05-08-2008, 03:41 PM
  4. Repaint fails when using threads
    By rjevans2000 in forum Threads and Synchronization
    Replies: 1
    Last Post: 09-22-2007, 12:22 AM
  5. Repaint fails when using threads
    By rjevans2000 in forum AWT / Swing
    Replies: 3
    Last Post: 08-15-2007, 06:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •