MySQL Bugs: #363: SHOW SESSION VARIABLES LIKE 'x' != SELECT @@SESSION.x

Bug #363	SHOW SESSION VARIABLES LIKE 'x' != SELECT @@SESSION.x
Submitted:	3 May 2003 6:19	Modified:	19 May 2009 17:55
Reporter:	Guilhem Bichot	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any (all)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	affects_connectors

Description:
I tested it in 5.0, but may be true for older versions.

MASTER> set global sql_log_bin=0;
ERROR 1228: Variable 'sql_log_bin' is a LOCAL variable and can't be used with SET GLOBAL
MASTER> show session variables like 'sql_log_bin';
Empty set (0.00 sec)
MASTER> select @@session.sql_log_bin;
+-----------------------+
| @@session.sql_log_bin |
+-----------------------+
|                     1 |
+-----------------------+

What I don't understand is why SHOW returned empty set while the SELECT worked.
This is at least inconsistent; I would expect SHOW to return 1.

How to repeat:
show session variables like 'sql_log_bin';
select @@session.sql_log_bin;

Some more strange things :
mysql> select @@session.max_binlog_size;
ERROR 1229: Variable 'max_binlog_size' is a GLOBAL variable and should be set with SET GLOBAL
mysql> show session variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

Apparently SHOW SESSION VARIABLES prints nothing for a session-only variable,
and prints the global value for a global-only variable. It works fine for a
session-and-global variable.
I will check mysqld_show() in sql_show.cc.

Even the current SELECT @@SESSION.xx behaviour may not be consistent.
When I write SELECT @@SESSION.xx, I probably want the value of xx that applies to me, that is:
- if this variable has a session variant, I want the session value.
- if it does not, i.e. is only global, then the global value is what applies to me, so I should get the global value, not an error like now (but I should still get an error when doing SET @@SESSION.xx = ).
Here's an array to sum up the consistent way that should be:
(in [] the current way when it is different) :

                    | x is glob&sess  |   x is glob only   |    x is sess only
--------------------|-----------------|--------------------|--------------------
SELECT @@SESSION.x  |  sess val of x  | glob val of x [err]|      sess val of x
SHOW SESSION LIKE x |  sess val of x  |    glob val of x   |sess val of x[empty]  
SET @@SESSION.x     |  sess val of x  |    error           |      sess val of x         
SELECT @@GLOBAL.x   |  glob val of x  |    glob val of x   |      error
SHOW GLOBAL LIKE x  |  glob val of x  |    glob val of x   |      empty
SET @@GLOBAL.x      |  glob val of x  |    glob val of x   |      error

Note that we must always honour SHOW VARIABLES = SHOW SESSION VARIABLES.

Extension, not bug report.  Should be in worklog, not here.

A small addtion to this:

log_bin stands for 'server is logging data to the binary log'

sql_log_bin stands for 'should data updated in the current thread be logged to the binary log'

We should probably in the future add all sql_xxx variables to 'show variables'

this looks fixed.

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

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

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.3-m13 |
+-----------+
1 row in set (0.00 sec)

Apparently the problem noted in the third column of my array ("X is session only") is indeed fixed:
(gtid_next is a session only variable)

mysql> show session variables like 'gtid_next';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| gtid_next     | AUTOMATIC |
+---------------+-----------+
1 row in set (0,02 sec)

mysql> select @@session.gtid_next;
+---------------------+
| @@session.gtid_next |
+---------------------+
| AUTOMATIC           |
+---------------------+

But the problem in the second column ("X is a global only") is not yet fixed:

mysql> select @@session.general_log_file;
--------------
select @@session.general_log_file
--------------

ERROR 1238 (HY000): Variable 'general_log_file' is a GLOBAL variable
mysql> show session variables like 'general_log_file';
--------------
show session variables like 'general_log_file'
--------------

+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log_file | /m/tmp/zo/t3500.log |
+------------------+---------------------+
1 row in set (0,01 sec)

Above we have an inconsistency where SELECT SESSION returns an error, but SHOW SESSION shows the global value.

Even worse: general_log_file also appears in I_S.SESSION_VARIABLES:

mysql> select @@session.general_log_file;
ERROR 1238 (HY000): Variable 'general_log_file' is a GLOBAL variable
mysql> show session variables like 'general_log_file';
+------------------+-----------------------------------------------+
| Variable_name    | Value                                         |
+------------------+-----------------------------------------------+
| general_log_file | /usr/local/mysql-5.6.14/var/hartmut-t430s.log |
+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.session_variables where variable_name = 'general_log_file';
+------------------+-----------------------------------------------+
| VARIABLE_NAME    | VARIABLE_VALUE                                |
+------------------+-----------------------------------------------+
| GENERAL_LOG_FILE | /usr/local/mysql-5.6.14/var/hartmut-t430s.log |
+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

In 8.x (maybe earlier versions)
I_S.SESSION_VARIABLES no longer exists and the behavior for session and global variables is consistent:
mysql> show session variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.05 sec)

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

mysql> show session variables like 'general_log_file';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log_file | /home/justin/datadir/msi.log |
+------------------+------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.general_log_file;
ERROR 1238 (HY000): Variable 'general_log_file' is a GLOBAL variable

I think this is verified as fixed and this bug should be closed.

Sorry.  I missed that there was a request to make
@@session.XXXX work when it is a global variable since SHOW SESSION VARIABLES ... reports the value.  Maybe this should just be a doc bug now, since this is the accepted behavior and this isn't a 'feature request'.  Suggest change to doc bug and update documentation to explain current behavior.