Tuesday, May 28, 2013

Ambiguities in the wait_timeout parameter in MySQL

The wait_timeout parameter in MySQL defines the duration of inactive connections to remain open. However, when doing SHOW VARIABLES at a MySQL shell, you may be fooled into thinking that the setting is set to 28800 for all new connections:

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| wait_timeout             | 28800    |
+--------------------------+----------+
You have to keep in mind though that the setting is derived from either interactive_timeout or wait_timeout, depending on how you initiated the connection from MySQL. If you're using the MySQL client, chances are it's using the interactive_timeout.  This article helps provide some good explanation of the behavior: http://blog.mozilla.org/it/2012/04/24/when-is-qwait_timeout-not-wait_timeout/

Therefore, it's important to use the GLOBAL modifier to determine what the default settings are currently set: http://dev.mysql.com/doc/refman/5.1/en/show-variables.html
With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. If a variable has no global value, the session value is displayed. With SESSION, SHOW VARIABLES displays the values that are in effect forthe current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

1 comment: