Bug #11782 SHOW VARIABLES (seemingly) misreporting wait_timeout

• Submitted: 6 Jul 2005 19:06
• Modified: 12 Jul 2005 8:34
• Reporter: Matthew Berg
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: mysql Ver 12.22 Distrib 4.0.20, for pc-
• OS: Linux (CentOS 4.0 (GNU/Linux 2.6.9-5.0))
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[6 Jul 2005 19:06] Matthew Berg

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

mysql> SELECT @@GLOBAL.wait_timeout;
+-----------------------+
| @@global.wait_timeout |
+-----------------------+
|                    30 |
+-----------------------+
1 row in set (0.00 sec)

It's fairly clear the lower timeout _has_ taken effect, so it appears it's being misreported.

How to repeat:
SET GLOBAL wait_timeout=<value>;

SHOW VARIABLES LIKE 'wait%';

[6 Jul 2005 19:22] MySQL Verification Team

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

mysql> SELECT @@GLOBAL.wait_timeout;
+-----------------------+
| @@global.wait_timeout |
+-----------------------+
|                 28800 |
+-----------------------+
1 row in set (0.02 sec)

mysql> SET GLOBAL wait_timeout=30;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT @@GLOBAL.wait_timeout;
+-----------------------+
| @@global.wait_timeout |
+-----------------------+
|                    30 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.25-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql>

[12 Jul 2005 8:34] Sergei Glukhov

See manual http://dev.mysql.com/doc/mysql/en/show-variables.html
'SHOW VARIABLES LIKE' returns value of the session variables.
To return value of global variable you can use 'show global variables'.

SHOW VARIABLES LIKE 'wait_%';
Variable_name Value
wait_timeout  28800
SHOW GLOBAL VARIABLES LIKE 'wait_%';
Variable_name Value
wait_timeout  28800
SET GLOBAL wait_timeout=30;
SHOW VARIABLES LIKE 'wait_%';
Variable_name Value
wait_timeout  28800
SHOW GLOBAL VARIABLES LIKE 'wait_%';
Variable_name Value
wait_timeout  30
SELECT @@GLOBAL.wait_timeout;
@@global.wait_timeout
30
SELECT @@SESSION.wait_timeout;
@@session.wait_timeout
28800

Result above is correct