java jdbc mysql connector: how to resolve disconnection after a long idle time

Question

I'm using red5 1.0.0rc1 to create an online game. I'm connecting to a MySQL database using a jdbc mysql connector v5.1.12

it seems that after several hours of idle my application can continue running queries because the connection to the db got closed and i have to restart the application.

how can I resolve the issue ?

Kfir

Answer 1

The MySQL JDBC driver has an autoreconnect feature that can be helpful on occasion; see "Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J"¹, and read the caveats.

A second option is to use a JDBC connection pool.

A third option is to perform a query to test that your connection is still alive at the start of each transaction. If the connection is not alive, close it and open a new connection. A common query is SELECT 1. See also:

• Cheapest way to to determine if a MySQL connection is still alive

---

A simple solution is to change the MySQL configuration properties to set the session idle timeout to a really large number. However:

• This doesn't help if your application is liable to be idle for a really long time.
• If your application (or some other application) is leaking connections, increasing the idle timeout could mean that lost connections stay open indefinitely ... which is not good for database memory utilization.

---

^{1 - If the link breaks (again), please Google for the quoted page title then edit the answer to update it with the new URL.}