Bug #31973 SHOW GLOBAL VARIABLES will display the session variables and vice versa.

• Submitted: 31 Oct 2007 7:04
• Modified: 31 Oct 2007 9:19
• Reporter: Lu Jingdong
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Linux
• CPU Architecture: Any

[31 Oct 2007 7:04] Lu Jingdong

Description:
All session variables are displayed on the result of "SHOW GLOBAL VARIABLES" statement and vice versa. I think it will lead to confusion.

How to repeat:
For example, "autocommit" is a session variable.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.21-beta-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.07 sec)

mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> select @@global.autocommit;
ERROR 1238 (HY000): Variable 'autocommit' is a SESSION variable

mysql> select @@session.autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Suggested fix:
I think if this variable is one of the session variables, it shouldn't appear in the result of "show global variables" and vice versa.

[31 Oct 2007 9:19] Hartmut Holzgraefe

The SHOW GLOBAL VARIABLES output is showing the default value for variables that are actually session specific. So while the actual variable only exists on the session level the presence of the value in the SHOW GLOBALS output still makes sense