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/java?unsub=1
