Bug #68969 add "show all variables" for distinguish global-only variables
Submitted: 16 Apr 2013 3:17 Modified: 1 Jul 2013 14:16
Reporter: xiaobin lin (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: Marc ALFF CPU Architecture:Any
Tags: Contribution, show variables

[16 Apr 2013 3:17] xiaobin lin
Description:
In current syntex, there are "session" and "global" in command "show variables". in fact there are three type of variables like shown in this pic
http://dl.iteye.com/upload/attachment/0083/1030/7f9bb2fd-38d8-3f20-9f65-37764835367e.jpg

The "show global variables" returns all variables except that are session-only.
but the "show session variables" returns all.

That may lead to mis-understand. And another problem is that, when user needs the session-only variables, it can diff the results from the two current commands. But no way to get global-only variables.

How to repeat:
as above

Suggested fix:
if the syntax can be extended to "SHOW [ALL | GLOBAL | SESSION] VARIABLES" and redefine that "session" is "ALL except global-only", seems to be more reasonable? Simple modification as attachment
[16 Apr 2013 3:20] xiaobin lin
based on 5.6.10

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: show_all _variables.5610.diff (application/octet-stream, text), 2.24 KiB.

[21 Apr 2013 12:04] MySQL Verification Team
Thank you for a feature request and contribution!
[27 Jun 2013 13:49] Georgi Kodinov
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Your assumption that there exist global variables that don't have a session variable counterpart is not entirely correct. http://dev.mysql.com/doc/refman/5.6/en/using-system-variables.html says that for every global variable there always is a mirroring session variable.

This makes the session variables a super set of the global variables. 
And both the set and the superset are returned by variations of the SHOW command.
[28 Jun 2013 3:36] xiaobin lin
Hi, Georgi 
 Thank you for response.
 In fact, there are many variables that are "global only".
 For example:

mysql> set sync_binlog=1;       
ERROR 1229 (HY000): Variable 'sync_binlog' is a GLOBAL variable and should be set with SET GLOBAL

  The attachment bellow contains all the global_only variables, I get it from the patch and the method I mentioned above.
[28 Jun 2013 3:37] xiaobin lin
All the global only vairiables in MySQL 5.6.10

Attachment: global_only (application/octet-stream, text), 6.76 KiB.

[1 Jul 2013 12:32] Georgi Kodinov
Thank you for taking the time to explain what you needed.
Re-opening the bug.
[1 Jul 2013 12:34] Georgi Kodinov
I also don't think this is a feature request bug. 
It's just a bug in how SHOW SESSION/SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES works. 
It should have been made to return only the session variables.
If you need both kinds all you'll need to do is to make an UNION of session and global variables.
[1 Jul 2013 14:16] Marc ALFF
This is a duplicate of:
Bug #27508 	Semantic of SHOW [SESSION|GLOBAL] STATUS