MySQL Bugs: #68451: Provide a I_S table with global variable scope and dynamic information.

Bug #68451	Provide a I_S table with global variable scope and dynamic information.
Submitted:	21 Feb 2013 8:40	Modified:	11 Apr 2018 12:57
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.6.10, 5.7.*, 8.0.4	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
I push out configuration changes using configuration management software to my servers' /etc/my.cnf files, and have scripts which determine if the running config does not match the /etc/my.cnf.  The script can "reconfigure" the server when there are differences and the settings are dynamic. Removing the logic based on version etc to figure out which settings can be changed and which can not is a nuisance  and I believe should not be necessary.

The information is available as documentation only here: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html 

How to repeat:
Look for tables which provide this information and see that none currently exist.

Suggested fix:
Please provide the information shown in the documentation about a variables scope ( global, local or both), and whether it's dynamic or not in msyqld itself, probably as a I_S table, or augment the existing I_S tables.

Basically make the information in Table 5.3. (System Variable Summary) available to be queried.

This will simplify the configuration management of a server under the conditions outlined above.

Please consider for 5.7.

Please ensure the default settings are included so that you can directly see which settings are different to default values.

Somewhat related to Bug #69147

Note: the "dynamic" or not is not quite straightforward as I originally thought.
* some variables are indeed static and are set at startup.
* some variables are dynamic and can be changed at any time
* some variables can only be changed at certain times, like if replication is stopped. For example:

root@myserver [(none)]> set global master_info_repository = 'FILE';
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

So we have at least 3 types of indications of whether the variable can be changed or not.

CAN_BE_CHANGED = ENUM('NO', 'YES', 'IF_SLAVE_STOPPED' )

See also bug.php#71057 which is really a feature request.

Related: http://bugs.mysql.com/bug.php?id=71059

Is this a documentation request? I'd say it is, but the category is set to Server:Options, and I'm not clear whether or not this is really a request for a code change in I_S. Please clarify. -Stefan

Oh dear. You ask this now, after the initial FR at: 21 Feb 2013 8:40, that's 2 over years ago.

No this is not a documentation request. I want to be able to query I_S on the configuration of the running server.

So I want to know:
* if the variable is statically configured, or dynamically configured. [*]
* the global variables name and the equivalent /etc/my.cnf setting or command line parameters (if appropriate).
* I want to know if there's session or global differences for the configurations
* I want to know the default setting of each variable.
* I'd like to know the type of variables so I can figure out what valid settings it can take.

Why? As explained earlier because I have a master (or slave servers) up and running for some time (several months, but sometimes over a year). Over that time the configuration requirements may change, so I want to adjust the server's configuration from its original settings.

I do this by changing /etc/my.cnf and then running a script which looks at the /etc/my.cnf and tries to compare the settings against the SHOW GLOBAL VARIABLES values, and then figures out if it can change the settings and if so adjust them accordingly.

The "magic" currently involved to do this (including changing the name in /etc/my.cnf to the name in SHOW GLOBAL VARIABLES, or the fact I have to keep a _per major version_ list of dynamic or static variable settings (even then that's wrong as some settings are changed but not actually acted upon) and others can be changed but only if for example replication is stopped) is horrible and needs to be kept in the application logic. I should be able to query mysqld and ask it how it's configuration works and get it to answer these questions directly to me.  The the logic disappears from the script and anyone can use it.

So the script  can figure out if the required changes affect dynamic settings and can change those settings which are dynamic.
Those settings which aren't dynamic require a mysqld restart, which means that if the server is busy doing real work, I have to move the applications away from it to another server, while I simple bounce mysql to pick up the change, then warm it up again so it can be used again. Doing that on a slave is a pain. Doing that on a master is really disruptive.

I'd like all settings to be dynamic and I know things have improved and will be better in 5.7 but there are still static settings and having to manually collect this information so I don't try to change a static setting is a nuisance. If I have to do this others will do too. It would be nice if I could just ask mysqld to tell me that.

[*] it's not really this "2 option" setting either.

I hope that's clearer.

Also consider things like defaults.
- There is a special keyword DEFAULT. So I can reset a value to the default setting, but I can't query MySQL to know what the value would be.

root@localhost [(none)]> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

root@localhost [(none)]> set global max_connections = 300;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> set global max_connections = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> set global max_connections = 300;
Query OK, 0 rows affected (0.00 sec)

The default value for some of these settings is static but for others it depends on other factors.  The defaults also change between MySQL versions.

So being able to query MySQL to see what the default is might be handy. I try to avoid configuring in /etc/my.cnf values which are their default values, unless there's a specific reason I want to _ensure_ that value is used, or perhaps if I am going through an upgrade process and want to ensure that servers running MySQL N and slaves under it running N+1 have the _same_ setting even though the defaults change between versions.

So I had tried to build some tables myself inside MySQL based on parsing the documentation in http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html, but have seen after trying it that the documentation is not complete.
See: https://bugs.mysql.com/76239

If the documentation were parseable and complete then I could build some support tables and populate them with this information and that would mainly solve my problem, but I guess it was never intended to be used this way (which to some extent is fair enough) so I am unable to do that, or if I use that information I'd need to be very careful to check it's actually correct.

It would also be helpful to also specify the type of the column, and possible values, and also command-line option if such an option exists.  Then the mapping between /etc/my.cnf and SHOW GLOBAL VARIABLES output becomes explicit.  Now it is not.

Posted by developer:

WL#8688 Support ability to persist SET GLOBAL settings

implements  (among other things) performance_schema.variables_info

See

http://mysqlserverteam.com/mysql-8-0-persisting-configuration-variables/

MySQL 8.0 still does not provide a way to determine information about a variable:
* it's scope: local / global / both
* whether it can be configured dynamically or not

The new performance_schema.variable_info provides more context than was available previously but not the equivalent information that can be found here: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html in the Table 5.2 System Variable Summary.

It really would be good to have this information provided in the server.
* changes are often made in minor versions so scripts may need to be minor version aware
* scripts don't need to keep a hard-coded list of "dynamic vs non-dynamic variables" which it needs if it wants to reconfigure the server dynamically.
* basically providing this information would simplify automation tasks around the server, such as deploying and reconfiguring etc.

Changed status from closed as the problem has not been solved.

There is a related docs bug, Bug#76239. I have addressed the issues noted there in the reference manual as much as I can, but the long-term solution for DBA purposes is for the server to report configuration about itself.