MySQL Bugs: #103779: There is no way to know the default value of a variable

Bug #103779	There is no way to know the default value of a variable
Submitted:	22 May 2021 13:40	Modified:	23 May 2021 13:01
Reporter:	Eduardo Ortega (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Currently, there is no way to know the default value of a global variable. This information can be useful if you need to check that your running configuration (i.e. the output of SHOW GLOBAL VARIABLES) matches the one from your config files, particularly when you remove a variable from the later (for example, because you want to go back to using the default).

In such a case, you need to compare the value of running variables, compare with the ones from config and, if they defer, issue a SET GLOBAL <variable_name> = DEFAULT. Of course you could do that for every single variable that is not present in your config file, but that is probably an overkill and might cause unnecessary work on the server.

I am aware that performance_schema.variables_info gives you information regarding the source of the variable, but this information can lead to incorrect conclusions. For example, the default value for autocommit is ON, and you could check whether variable_source in variables_info is COMPILED or something else. However, when a client connects, it might change the value to OFF for its connection/session (in fact, plenty of client libraries and ORMs do this). After this, variable_source does not show COMPILED anymore; instead, it shows DYNAMIC. Even after this client disconnects, variable_source still says DYNAMIC, even though its value is still ON (the default). Therefore, just by inspecting the current contents of variables_info, you cannot know for sure whether the current value matches the default/compiled one or not.

It would be nice if the server offered a way to get the default/compiled value of any given variable.

How to repeat:
Not really a bug, but more a feature request; so nothing to repeat.

Suggested fix:
Add a column to performance_schema.variables_info with the default value.

A fix is proposed in this pull request: https://github.com/mysql/mysql-server/pull/338

Hello Eduardo Ortega,

Thank you for the reasonable feature request!

regards,
Umesh

Hi, Umesh:

I am closing the PR I had filed, as t is not showing the correct information. The feature request still stands, though; it would be handy if MySQL provided this.