Wednesday, August 18, 2010

MySQL: the mystery of unsetable global variables

I just tried updating our mysql server to accept very long connections. I have a ton of jobs running, so I wanted to set the wait_timeout variable (via the mysql shell) to something reasonable for these jobs. The default of 8 hours is not sufficient in some rare cases so I tried to set the timeout higher:

mysql> SHOW VARIABLES LIKE 'wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| wait_timeout | 28800 |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL wait_timeout=86400;
mysql> SHOW VARIABLES LIKE 'wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| wait_timeout | 28800 |
+--------------------------+-------+
1 row in set (0.00 sec)

What? Why wasn't it set?

Well, the reason is that the SHOW VARIABLES command defaults to the session variables. So, the local session wait_timeout is still 2880, but the global wait_timeout was actually updated correctly:

mysql> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| wait_timeout | 86400 |
+--------------------------+-------+
1 row in set (0.00 sec)