3

I'm building a system with a login. When I was just testing it on my own computer, it worked perfectly fine. Once I uploaded it to our server, we started running into the problem whereby ... it wouldn't log in in the morning, for a few seconds. Works fine the rest of the time.

I've only run into this yesterday and today, and I can only test it once each morning, so it's hard to give too many details yet. Here's the setup:

The front page is an html page using javascript and angular js. It brings up an empty frame, then makes an ajax request for the contents of the page. On any failed ajax request that has a return value of 'insufficient access priviliges' it will un-hide a div holding a login form. That login form blanks out the page and provides a submit button. Once the submit button is clicked on, it sends an ajax login request to the Java server. Once that request is returned, the page hides the login form again, and things move on as usual.

On the back end, we have a java server running something between 1.6 and 1.7 (yes, I know, I should know better - the server reports back that it's 1.7.001, but 1.7 functionality like string-based switch-case statements don't work, so we compile for 1.6 java when uploading. It's a known issue we're working on.) We're using Stripes to do the front-facing code. The database is a mysql database, located on the same server.

When this error occurs, it's always the first login in the morning. I load up the page, type in the user/pass, and click login. The page blinks and comes back to the login screen. I haven't cleared out the user/pass after a login yet, because we're still in a testing phase, so I can just click 'login' again, and again, a dozen times before it finally logs in. This problem has only happened twice: yesterday and today, the first login of the morning.

My co-worker had a similar issue with another similar setup on a previous project, same server. He said he believes the problem has to do with the sql server closing the connection without the java server realizing that the connection has been closed, but he's not sure, and doesn't know how to fix it.

I don't even know what other information to offer, what code to show, or anything like that. The best suggestion I can find anywhere is "Set up a scheduled event to make a sure-fire database request every couple of hours," but this seems so ... kludgy. I'd love to be able to test for a more definitive problem and solution, if anyone could help me out. If anyone can tell me what code they'd like to see to figure this out, I'll gladly paste it up. More information? Just ask. The scope seems so ... big ... that I don't know which pieces might be relevant.

Edit 1: Error code!

[ERROR] - Database Error 1 on verify
java.sql.SQLException: Already closed.
    at org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.java:114)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)

Edit 2: Connection Code

This is from an object we keep specifically for all mysql database connections.

private static ConnectionSource coreConnection = null;
private static DataSource coreDataSource = null;

public static ConnectionSource getCoreConnection() throws SQLException, NamingException {
    if (coreConnection == null || !coreConnection.isOpen()) {        
        Context env = (Context)new InitialContext().lookup("java:comp/env");        
        coreDataSource = (DataSource)env.lookup("webapps/test");
        coreConnection = new DataSourceConnectionSource(coreDataSource, new MysqlDatabaseType());
    }
    return coreConnection;
}
Wolfman Joe
  • 799
  • 1
  • 8
  • 23
  • 4
    It sounds like some kind of timeout issue. What driver are you using in your Java code to connect to MySQL? Are you re-using a single connection for all your queries, or creating a new one every now and then? – WouterH Jun 14 '13 at 13:52
  • 1
    Could http://stackoverflow.com/questions/16331959/java-sql-sqlexception-already-closed be related? – WouterH Jun 14 '13 at 13:58
  • 1
    @WouterH I think your first impression was correct. I bet the DB closes the idle connection at least after a couple hours. – Fildor Jun 14 '13 at 13:59
  • @WouterH I included the error message and the connection code. I'm re-using a single connection for all queries. And yes, I think the DB closes the idle connection after a few hours. I'm looking at your link and I think it's useful ... – Wolfman Joe Jun 14 '13 at 14:01
  • Great! Handle the SQLException by getting a new Connection and retry :) – Fildor Jun 14 '13 at 14:01
  • @Fildor SQLException is a very general sort of exception, right? Not specifically this one? How would I test for this specific problem instead of just catching a general SQLException? I don't want some other SQLException to just keep retrying over and over again in an infinite loop. – Wolfman Joe Jun 14 '13 at 14:04
  • Hmm, with MaxDB, I think I remember that we could retrieve some ErrorCode from the SQLException ... not sure any more, though. – Fildor Jun 14 '13 at 14:06
  • 1
    @WolfmanJoe The exception itself is very general, yes. You can use getMessage(), getErrorCode() and getSQLState() to look for identifying information to detect this variant. It also might be a good idea to build in protection against infinite recursion, such as giving up after a few attempts by throwing the exception. Also see http://docs.oracle.com/javase/6/docs/api/java/sql/SQLException.html – WouterH Jun 14 '13 at 14:06
  • Yes, getErrorCode was the one, I think. Of course you'd have to use a retry-penalty, Wolfman. – Fildor Jun 14 '13 at 14:08

2 Answers2

6

Configure your connection pool to validate connections. The connection pool will then send a simple query (which you specify during configuration) to the DB. If that query fails, it will close the connection and give you a new one, otherwise it will use it in place. That helps solve problems like these as well as problems where the database goes down behind the back of the server.

Will Hartung
  • 115,893
  • 19
  • 128
  • 203
  • This could conceivably happen several times during a single call. Would it be worthwhile to have a 'last validated' private property on the object and validate the connection only when, say, a half-hour or hour has gone by? Or is the test sufficiently small in cost that it doesn't really matter? – Wolfman Joe Jun 14 '13 at 14:22
  • As a note, I'm likely to mark this as the answer, but I need to run some tests to make sure it's the answer. Once I'm sure it's working (which will happen likely next week) I'll mark this correct. – Wolfman Joe Jun 14 '13 at 18:53
  • It will only validate the connection when you ask for it from the pool. Obviously if the connection goes bad during processing, you will have to deal with that issue. You can handle those cases in your code, or you can close the connection and ask for a new "fresh" one. The latter makes more sense if you have a particularly long process happening. Of course, if you close the connection, you would need to start a new transaction with the DB. – Will Hartung Jun 14 '13 at 20:38
2

MySQL closes connections which have been open for a long time, but don't do anything. From what you've described it sounds like this is what you're running in to.

The first login fails because it finds that the used database connection is no longer working, and the driver notices this as well. The driver will then use a new connection for the next request.

The first way to handle this that comes to mind is detecting this SQLException, and handling it by opening a new connection with the same query. There may be other solutions available within the driver that you're using, but unfortunately I am not aware of any.

To aid with detecting this SQLException you can take a look at the docs, and evaluate if the methods listed there return anything unique to this error.

If you are using a connection pool, then this question may contain the answer to keeping the connections alive.

Community
  • 1
  • 1
WouterH
  • 1,345
  • 10
  • 16
  • I'm not sure I like the idea of responding only to SQLExceptions, because that spreads the code all over the system. I'm probably going to go more with Will Hartung's suggestion above regarding testing the connection, but thank you very much for your help in diagnosing the problem and giving me more information! +1 – Wolfman Joe Jun 14 '13 at 14:23
  • @WolfmanJoe Please see the last sentence of my answer, which appears to be specific to dbcp, which you appear to be using ;) But yes, if you can do that, definitely do. I only discovered about that after doing some more searching related to your question, which is why I didn't start off with it. – WouterH Jun 14 '13 at 14:30
  • Yes, I was looking at that, and it seems very useful. Thank you, again. :) – Wolfman Joe Jun 14 '13 at 14:41