Hibernate cannot recover after socket has timed out

Question

I have an application that uses Hibernate to connect to my MySQL database. I'm just having a weird problem with timeouts. Let's say that the socket timeout in MySQL's configurations is set to one hour. After an hour of inactivity, the socket times out as expected. The problem is that Hibernate doesn't seem to be able to handle the socket timeout, because when Hibernate tries to make a database query, a SocketException is thrown. Instead of handling the exception internally, hibernate just "dies" and cannot recover from the exception in any other way than for me to manually build a new SessionFactory.

The question is, how can I avoid this problem so that I do not have to manually keep track of timeouts?

EDIT Your suggestions seem to be the right way to go, but for some reason I'm not able to get it to work. I tried following the instructions on http://www.michaelstudman.com/fullfathomfive/articles/2004/06/07/mysql-dropping-connections-and-hibernate and the instructions on the page Jimmy linked to, but I'm still getting the same exception and my application fails due to it. Might it have something to do that I've set mysql's timeout to 120 seconds (for testing purposes only), but I changed the c3p0 timeout to 100 seconds.

Answer 1

The best way to handle these types of issue is to configure you connection pool to validate the connection before it is handed out to the application (be it hibernate or what ever).

Most connection pools allow you to configure a simple query some thing like SELECT 1=1 for mysql or SELECT 1 FROM DUAL in oracle. If you are using commons dbcp then the configuration name is validateQuery and testOnBorrow.

I've actually answered a similar question...relating to the flip side of this issue and that's too much validation of connections!

http://stackoverflow.com/questions/917927/select-1-from-dual-mysql/917946#917946

Answer 2

Have a look here for some good info on this issue . In a nut shell you can get the connection pool to check for stale connections using setting like

c3p0.testConnectionOnCheckout=true