MySQL Bugs: #68969: add "show all variables" for distinguish global-only variables

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

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

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.

Thank you for a feature request and contribution!

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.

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.

All the global only vairiables in MySQL 5.6.10

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

Thank you for taking the time to explain what you needed.
Re-opening the bug.

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.

This is a duplicate of:
Bug #27508 	Semantic of SHOW [SESSION|GLOBAL] STATUS