Issue
I have several server processes that once in a while respond to messages from the clients and perform read-only transactions.
After about a few days that the servers are running, they stop working correctly and when I check it turns out that there's a whole bunch of messages about the connection being closed.
When I checked it out, it turned out that hibernate by default works in some sort of development mode where connections are dropped after a few hours, and I started using c3po for connection pooling.
However, even with c3po, I get that problem about 24 hours or so after the servers are started.
Has anyone encountered that problem and knows how to address it? I'm not familiar enough with the intricacies of configuring hibernate.
Solution
The MySQL JDBC driver times out after 8 hours of inactivity and drops the connection.
You can set autoReconnect=true in your JDBC URL, and this causes the driver to reconnect if you try to query after it has disconnected. But this has side effects; for instance session state and transactions cannot be maintained over a new connection.
If you use autoReconnect, the JDBC connection is reestablished, but it doesn't automatically re-execute your query that got the exception. So you do need to catch SQLException in your application and retry queries.
Read http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html for more details.
Answered By - Bill Karwin
Answer Checked By - Candace Johnson (JavaFixing Volunteer)
