Re: mySQL server drops jdbc connections after 8 hours of inactivity - how can I prevent this from happening

The default value on the server for wait_timeout is 8 hours. This means that by default the server will kill a connection if it has been idle for more than 8 hours. This applies to all connections.

You can solve this in a couple of ways.

One option is to change wait_timeout to the max value (1 year), though arguably that just defers the problem. Also, this is a server-side solution and maybe someone affected by this either can't change the server config or has too many servers to change or maintain.

IMHO, the better solution is to use your connection pooling library's ability to remove connections from the pool after they have been idle or in existence for a relatively long period of time. This is pretty easy with c3p0.

You can read the docs on this at http://www.mchange.com/projects/c3p0/index.html#managing_pool_size

For our projects, we generally set maxConnectionAge and maxIdleTimeExcessConnections to be much less than 8 hours.

You can also configure a test (e.g., SELECT 1) to be executed on the connection, rather than just dropping it.

Also, check out http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

If you search through the list archives, you'll find this has been covered a couple of times.

Robert Stewart
Voxify

On Nov 21, 2009, at 8:40 PM, Lev Verkhovsky wrote:

Hi,

I have a hibernate-managed c3p0 connection pool of Connector/J connections
to a mySQL database and after 8 hours of inactivity the server drops the
connections, causing the connections die and throw exceptions when used.
What is the best way to prevent this from happening? I need something that
is production-strength.

Thanks in advance.


--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]