MySQL – Understanding wait_timeout Variable

MySQLwaits

I've been trying to resolve an issue which I think may be helped by changing the wait_timeout variable in MySQL (5.1.67) on my CentOS machine.

So, I updated /etc/my.cnf with the value I want (180), and restarted MySQL, but now I'm surprised to find that I'm getting different values for wait_timeout depending on how I display it (see below): 

mysql> show global variables like "%wait%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 180   |
+--------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like "%wait%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
3 rows in set (0.00 sec)

I'm guessing that the second query is showing session variables, as opposed to global variables, but I don't understand why they would be different, after having exited my MySQL session, restarted MySQL, and then logged back in again. How come they're not the same? And, I guess, how to I ensure that the same value (180) is consistent across everything?

Any suggestions/help appreciated. Thanks.

Best Answer

The documentation states

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

Since you are connecting via the command line, rather than a script, it makes sense that the CLIENT_INTERACTIVE is being used as a connection option automatically. As such, the interactive_timeout value is being used, which defaults to 28800.

To make the session wait_timeout value be 180, update the interactive_timeout value to be 180.

As for why that is so, I can only speculate. However, as a programmer, I know it is more useful to only have to look up one value whenever checking to perform a certain action, rather than having to check multiple values, and make sure I'm always check all those values each time I want to check something.

More explicitly, in this case, every time the wait_timeout value is needed, it is best to just have to check that, rather than both wait_timeout and interactive_timeout. It helps keep code maintainable by having the session wait_timeout automatically be initialized with the appropriate value, and then have to only check that one value. To do otherwise invites mistakes, by potentially failing to check both values every time something needs to be done that involves this timeout.

Related Question