aspose file tools*
The moose likes JDBC and the fly likes Help! Getting IOException and Communication Link error with DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help! Getting IOException and Communication Link error with DB" Watch "Help! Getting IOException and Communication Link error with DB" New topic
Author

Help! Getting IOException and Communication Link error with DB

Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
I have a servlet that connects to a DB and it has worked perfectly with no problems during all my tests until recently. Now all of a sudden I'm getting java.io.IOException: Communication link failure. Does anyone know what might be causing this and how I might fix it?
Tina Coleman
Ranch Hand

Joined: Dec 12, 2001
Posts: 150
Have you gone down the basic rundown of making sure that the database server is up? That the port for the database hasn't been blocked by some firewall configuration?
Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
yes, this is the thing: it works sometimes but sometimes it throws that exception. So I know that the DB is up and it can reach it.
Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
I have narrowed the problem down, and it's a weird one.
If I have not logged in to MySQL through the mysql command-line utility in a while, the web application cannot connect to the database. When it tries to connect through JDBC, it comes back with a communication link error. This continues until I log in (through a bash prompt) with the mysql command-line utility. Then the web app can suddenly get through. Does anyone know why this is? What might be causing this? How can I stop this from happening?
Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
I'm not 100% sure, but I think what might be happening is this:
1. Connection Pool Manager creates connection and returns it
2. Connection is used by app, close() called and is returned to pool
3. Connection not used for long time, mysql times it out
4. Pool manager doesn't know it's timed out, returns it when connection asked for
Does this sound correct? how would I fix this? Unfortunately, the javax.sql.PooledConnection and Listener interfaces don't offer any callbacks for timeouts. So how would I timeout a connection? Or should I, before returning a pooled connection, test it first? The only thing is this adds an extra trip to the DB for every time someone asks for a connection. But I don't see any other way to handle this, except maybe the timeout on a connection in the "idle" pool?
Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
Please, anyone who knows tell me if my idea as to what is causing this problem is correct or wrong. The problem is that with testing, it's hard to know 100% if I'm right because it doesn't happen all the time, and if I'm right, it won't happen if I test it a lot (because the connections wouldn't time out from inactivity).
Assuming I'm right, what I ended up doing was this:
1. Instead of storing "closed" connections in the idle List, I store them in the idle list in IdleConnection wrappers:

This is a nested (inner) class. So my PoolManager has access to the private fields. So now, when I pull a connection from the idle List collection, I simply check the current time minus the idleStartTime and if that is >= to the timeout set when my poolmanager was created, I discard this connection and create a new one.
I had a few other ideas, but wasn't sure they were the smartest:
1. Always have the IdleConnection wrapper even when in active connections (just rename the class so it makes more sense in this context)
Currently, I pull the IdleConnection and then pull the pooled connection from that and store just the pooledConnection in activeConnections List when I'm returning it to a user (i.e. it will be used/active). This means that every time I put it back into idleConnections List, I have to create a new wrapper object. Is this a waste of resources? Will it cause a lot of garbage collection?
2. If idle connection found to be timed out, go back into idleConnections to see if it has any more idle connections.
Currently, when I've found an idle connection has timed out, I discard it and just create a new one. I did this because otherwise, I'd have to make my method recursive (i.e. getConnection() would call itself in this case). I was wary of doing that since every call grabs a lock on idleConnections and possibly a call out to another method which does the same. I was wary of doing this because I did not know if there were risks or if it was slower to continually grab locks inside of locks on that same object (or two objects). What do you think? Is it worth it to make it recursive, or should I just create a new connection?
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

with the mysql driver (ConnectorJ / mmmysql), there is a parameter you can send in that will allow your driver to auto reconnect. In fact, i'm pretty sure it's called 'autoReconnect'.

By default, it is false, and so that is why an idle application will get the IO errors. I'm not on my machine with the mysql install, but I'm pretty sure it's in one of the *.txt read me's as a fairly well-known gotcha.
Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
Thanks for the reply.
Unfortunately, I can't use or rely on that. My Pool Manager can be used for any JDBC DataSource (or XADataSource). So it can't know before hand if the DataSource has a proprietary method or parameter that can extend the usefulness of a connection beyond its timeout. Some databases or database drivers might not even have such a thing. So I think the only solution is to allow the user to set a timeout length, and if it's been in the idle pool for that long then it's removed and a new connection returned. Does this sound right to everyone?
Dan Bizman
Ranch Hand

Joined: Feb 25, 2003
Posts: 387
OK, here's what I've done:
1. When connection is put into the idle pool, it is wrapped by class: IdleConnection so a timeout can be set.
2. In order that new object creation does not have to occur every time (since I found this slows things down A LOT), I created a third List: idleWrappers (ArrayList) to hold the empty wrappers.
I then synchronize on that third list and pull a wrapper from there, if one exists, and use that to wrap the connection going into idleConnections (otherwise, create a new wrapper). When they're removed from idle, the wrapper is put into the idleWrappers List for the next idleConnection that needs it. I found this DOES save a lot of time versus creating a new IdleConnection object every time something's returned to the idle pool.
How does this sound to everyone? Am I on the right track here?
 
jQuery in Action, 2nd edition
 
subject: Help! Getting IOException and Communication Link error with DB