MySQL wait_timeout Variable - GLOBAL vs SESSION

SHOW VARIABLES LIKE "%wait%"

Result: 28800

SET @@GLOBAL.wait_timeout=300

SHOW GLOBAL VARIABLES LIKE "%wait%"

Result: 300

SHOW SESSION VARIABLES LIKE "%wait%"

Result:28800

I am confused by the results. Why does the last query give Result:28800 ?

Solution 1:

Your session status are set once you start a session, and by default, take the current GLOBAL value.

If you disconnected after you did SET @@GLOBAL.wait_timeout=300, then subsequently reconnected, you'd see

SHOW SESSION VARIABLES LIKE "%wait%";

Result: 300

Similarly, at any time, if you did

mysql> SET session wait_timeout=300;

You'd get

mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 300   |
+---------------+-------+

Solution 2:

SHOW SESSION VARIABLES LIKE "wait_timeout"; -- 28800
SHOW GLOBAL VARIABLES LIKE "wait_timeout"; -- 28800

At first, wait_timeout = 28800 which is the default value. To change the session value, you need to set the global variable because the session variable is read-only.

SET @@GLOBAL.wait_timeout=300

After you set the global variable, the session variable automatically grabs the value.

SHOW SESSION VARIABLES LIKE "wait_timeout"; -- 300
SHOW GLOBAL VARIABLES LIKE "wait_timeout"; -- 300

Next time when the server restarts, the session variables will be set to the default value i.e. 28800.

P.S. I m using MySQL 5.6.16

Solution 3:

As noted by Riedsio, the session variables do not change after connecting unless you specifically set them; setting the global variable only changes the session value of your next connection.

For example, if you have 100 connections and you lower the global wait_timeout then it will not affect the existing connections, only new ones after the variable was changed.

Specifically for the wait_timeout variable though, there is a twist. If you are using the mysql client in the interactive mode, or the connector with CLIENT_INTERACTIVE set via mysql_real_connect() then you will see the interactive_timeout set for @@session.wait_timeout

Here you can see this demonstrated:

> ./bin/mysql -Bsse 'select @@session.wait_timeout, @@session.interactive_timeout, @@global.wait_timeout, @@global.interactive_timeout' 
70      60      70      60

> ./bin/mysql -Bsse 'select @@wait_timeout'                                                                                                 
70

> ./bin/mysql                                                                                                                               
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.12-5 MySQL Community Server (GPL)

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|             60 |
+----------------+
1 row in set (0.00 sec)

So, if you are testing this using the client it is the interactive_timeout that you will see when connecting and not the value of wait_timeout