MySQL wait timeout Variable - GLOBAL vs SESSION

Question

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

User · Answer

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   |
+---------------+-------+

User · Answer

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

User · Answer

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    gt    bin mysql -Bsse  select   session wait timeout    session interactive timeout    global wait timeout    global interactive timeout   70      60      70      60   gt    bin mysql -Bsse  select   wait timeout                                                                                                   70   gt    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 gt  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

[mysql] MySQL wait_timeout Variable - GLOBAL vs SESSION

Examples related to mysql

Examples related to global-variables

Examples related to session-variables