Background

The customers give feedback that the application always reports "Connection is disabled, and you need to establish the connection anew". Connection timeout is mainly influenced by two parameters, namely, "interactive_timeout" and "wait_timeout". The following gives a detailed description of the two parameters.

Failure phenomenon

If there is no action to MySQL client or application for some time after a connection between MySQL client/applications and MySQL server is established, further action usually leads to the following error report:

This error signifies current connection has been disabled. Timeout value has something to do with the settings of "interactive_timeout" and "wait_timeout". Herein, "interactive_timeout" is designed for interactive connections and wait_timeout for non-interactive connections. That is to say, connection to the database via MySQL client belongs to interactive connection, and connection to the database via JDBC belongs to a non-interactive connection.

Analysis of cause

Parameter description:

interactive_timeout: The number of seconds the client waits for before the server disables the interactive connection.

wait_timeout: The number of seconds the application waits for before the server disables non-interactive connection. On thread startup, the session "wait_timeout" value is initialized from global "wait_timeout" value or global "interactive_timeout" value, depending on the type of client (as defined by "mysql_real_connect()" connection option “CLIENT_INTERACTIVE”).

Notice: both parameters are applicable to new connections.

Test verification

How do "interactive_timeout" and "wait_timeout" influence one another?

Verification1: only modify the global variable "interactive_timeout"

1. Interactive: interactive_timeout

Enable one MySQL client to modify "interactive_timeout":

Enable another "MySQL" client to view session "wait_timeout":

From this, we can see that the session "wait_timeout" did not inherit interactive_timeout, and its value is still 3600.

2. Non-interactive: interactive_timeout

Enable another MySQL client to view session "wait_timeout":

Output the result: interactive_timeout 20 wait_timeout 3600

From this, we can see that the session "wait_timeout" did not inherit interactive_timeout, and its value is still 3600.

Verification 2: only modify the global variable "wait_timeout"

(1) Non-interactive modification to wait_timeout

Enable a MySQL client to modify wait_timeout:

Enable another MySQL client to view session "wait_timeout":

From this, we can see that wait_timeout does not vary with the change of "wait_timeout", and its value is still 20.

(2) Non-interative modification to wait_timeout

Enable another MySQL client to view session "wait_timeout":

Output the result: interactive_timeout 20 wait_timeout 100

From this, we can see that "wait_timeout" has inherited the "interactive_timeout" value.

From the test results above, we can see that as for non-interactive connection, the current connection "wait_timeout" inherits the global "wait_timeout". As for the interactive connection, the current connection "wait_timeout" inherits the global interactive_timeout.

Solutions

To prevent MySQL client of applications from being unavailable, we give the following solutions:

1. Application: interactive_timeout (or with wait_timeout) value setting should be higher than the connection pool timeout value.
2. MySQL client: interactive_timeout (or with wait_timeout) value setting should be higher than the possible free time at the client.

Conclusions & suggestions

The parameter for control the connection's maximum "sleep" timeout: wait_timeout

1. As for non-interative connection (namely, JDBC connection), current connection "wait_timeout" value inherits the global variable "wait_timeout" value at the MySQL Server.
2. As for non-interative connection (namely, JDBC connection), current connection "wait_timeout" value inherits the global variable "interactive_timeout" value at the MySQL Server.