MySQL Bugs: #65189: Don't pollute status settings in SHOW [GLOBAL] VARIABLES

Bug #65189	Don't pollute status settings in SHOW [GLOBAL] VARIABLES
Submitted:	3 May 2012 12:22	Modified:	18 May 2018 11:13
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.6.5-m8, 5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	global status, global variables

Description:
SHOW GLOBAL VARIABLES should show _configuration_ settings, not _status_ settings and thus I think the new GTID variables should be kept in the SHOW STATUS output rather than SHOW GLOBAL VARIABLES output.

I collect configuration settings for all the servers I manage and then use this to notify me of any changes which may be unexpected. With 5.6.5 and these new settings I get told of changes which are not _configuration changes_.  This is wrong. They are "status" settings and I believe should be stored somewhere else, perhaps in the output of SHOW GLOBAL STATUS.

While this doesn't seem like a big issue now, once 5.6 goes GA you won't want to change it and people will need to make workarounds to avoid these "settings being stored". It's better to do this right while you can change this.

How to repeat:
myuser@myhost [(none)]> show global variables like '%gtid%';
+--------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name                  | Value                                                                                     |
+--------------------------------+-------------------------------------------------------------------------------------------+
| disable_gtid_unsafe_statements | ON                                                                                        |
| gtid_done                      | 0C1CEAC6-8D5B-11E1-91F0-E4115BD787B8:1-57049163,
CF91CCAE-8D4E-11E1-91A3-E4115BD787B8:1-8 |
| gtid_lost                      | 0C1CEAC6-8D5B-11E1-91F0-E4115BD787B8:1-35370135,
CF91CCAE-8D4E-11E1-91A3-E4115BD787B8:1-8 |
| gtid_mode                      | ON                                                                                        |
| gtid_owned                     |                                                                                           |
+--------------------------------+-------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

myuser@myhost [(none)]> show global status like '%gtid%';
Empty set (0.01 sec)

myuser@myhost [(none)]> 

Suggested fix:
Make the gtid_done and gtid_lost become part of the output of SHOW [GLOBAL] STATUS. These are _not_ configuration items.

Still present in 5.7.5:

root@myserver [(none)]> select @@global.version, @@global.gtid_executed;
+------------------+--------------------------------------------------------------------------------------------------------------+
| @@global.version | @@global.gtid_executed                                                                                       |
+------------------+--------------------------------------------------------------------------------------------------------------+
| 5.7.5-m15-log    | 34778193-8b2c-11e3-8e61-e839352c66d2:1-12744598,
6a512bbf-6965-11e3-b220-b499bab5f9ba:1381936142-10170205982 |
+------------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Changed to Feature request as clearly it's not being considered a bug after having this reported as a bug 6 years ago.

Also needs adding here (not checked, but I guess names have changed):

gtid_executed
gtid_purged

These are visible in versions up to latest GA of 8.0.11

gtid_owned should also not be a global variable but is status information.

Update Version field with more versions.

The other option would be to add meta-data in 8.0 so I know which values are not "stable" and can ignore them. That would be fine too, and while there is more meta data in 8.0 vs earlier releases this sort of meta-data is still missing.