3

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.

4

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.

  • Okay, I sort of get that, but I thought the whole point of the variable "interactive_timeout" was for client connectivity (as opposed to via script), so I wonder why it needs to bother using "wait_timeout". – Cheddar Oct 23 '17 at 18:46
  • That's a good question, given that wait_timeout also says "The number of seconds the server waits for activity on a noninteractive connection before closing it." – Willem Renzema Oct 23 '17 at 18:58
  • @Cheddar If you agree that my answer answered your question of why the value is not what you expected, please consider accepting my answer. See also stackoverflow.com/help/someone-answers – Willem Renzema Oct 24 '17 at 1:34
  • I appreciate your answer, but I don't see how it explains why the client session wait_timeout is 28800; the client session already knows to use interactive_timeout, rather than wait_timeout. As you noted, wait_timeout is for non-interactive connections. If I set wait_timeout in my.cnf to 180, as I did, I don't see there should be anything else that changes it, anywhere. Yet it displays 28800, not 180. – Cheddar Oct 24 '17 at 12:06
  • The very first line of the documentation I quoted says "On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value". It is using interactive_timeout to SET the session wait_timeout value. The fact that it is a weird design does not change the fact that this is how it states it works, and why the value is as you see. – Willem Renzema Oct 24 '17 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.