how to properly handle connection idle timeout error

Question

I'm using Java with SpringFramework to do database programming on Mysql Server using the JdbcTemplate class.

Using org.apache.commons.dbcp.BasicDataSource as the db data source.

Sometimes when the connections are idle for long time, CommunicationException is being thrown with the following message:

The last packet successfully received from the server was XXXXX milliseconds ago.

I don't want to handle this issue by adding autoReconnect parameter to the connection or to add a property that will execute select 1 before each query to make sure the connection is properly opened. I also don't want to touch the configuration of the mysql server and to raise the timeout values.

What I would like to do is to properly handle that exception.

I thought about catching the CommunicationException and just retrying till it succeeds and if it fails for more then X times then to throw an Exception that shows that retrying for X times failed.

• does anyone have any other idea how to handle this issue?
• how's my idea ? :)
• maybe there is something in the springframework that does that for me automatically and I missed it?

any information would be greatly appreciated.

thanks!

Answer 1

If your query is restartable then it might make sense to retry. I know we do this in places and it works fine for the odd, temporary glitch. We do log the event though as it really should be rare.

Connection failures are part of life and should be handled differently to connection timeouts.

While you should have a sane way of handling a connection that you have "in hand" failing, if you don't keep hold of connections for too long then you could also look at the testOnBorrow and testOnReturn properties of BasicDataSource. These don't necessarily mean a test select prior to each query, unless you really do just collect a handle before each and every query.

If you have many connections on the pool, and they're not used often enough to stop timeouts, then it's really a configuration error. Writing code to avoid that seems a bit backward.