Idle Database Connections Dropped by MySQL

Products

• Services Suite

Issue/Introduction

Symptoms:
Applications running on the Pivotal Web Services (PWS) that connect to a MySQL database, including ClearDb, receive errors like the following in the logs:

Error Message:

Communications link failure: The last packet successfully received from the server was X milliseconds ago. The last packet sent successfully to the server was X milliseconds ago.

Applications seeing this error often using some form of database connection pooling.

Environment

Cause

The MySQL and MariaDB servers (other database servers might have this as well) have an option to disconnect clients that have been connected but are idle for more than a given period of time. With MySQL and MariaDB, when a client is disconnected, it sees an error message like the one mentioned above in their logs.

Resolution

Before you can resolve this issue you'll need to know how long the server will permit you to be connected and idle before it will disconnect your clients.  This differs from server to server, so you'll need to check with your MySQL administrator to get the exact details.  The name of the property is wait_timeout.   For ClearDb at the time this article was written the value was set to 90 seconds.

To resolve the issue, you have a few options.

If you're not using a connection pool...

• Do not hold connections open for extended periods of time in your application.  Simply close them once you're done using them.
• Run a periodic background task that sends a ping style query to the database, something like SELECT 1 is sufficient to keep the connection active.  Be sure to schedule this task to run at an interval that gives sufficient time (perhaps 20% less than the limit) to ensure that the connection has not been idle for longer than the server limit.

If you're using a connection pool...

• Do not hold connections in your application code while they're idle.  Return them to the pool immediately after use.  Letting the connection idle in your application code will very likely throw off any idle checks that the pool performs.
• Configure the pool to periodically test the idle connections.  The pool will generally use a "validation query" to perform this test and the query is generally sufficient to ensure that the connection remains active.
• Keep the number of idle connections in the pool low, which often means aggressively closing idle connections.  With less idle connections in the pool, there's a greater chance that your application will pick one up and naturally keep it active.
• Configure the pool to test connections with a validation query prior to giving them to your application.  This "test on borrow" approach allows for the pool to detect connections that have been closed and replace them with new, good connections.
• Instruct the MySQL driver for your language to automatically reconnect (may not be supported on all languages).  For example, the MySQL JDBC driver supports the connection property "autoReconnect=true".  Please consult the driver documentation for further details.