MySQL Bugs: #76239: server-system-variables.html documentation is incomplete

Bug #76239	server-system-variables.html documentation is incomplete
Submitted:	10 Mar 2015 9:55	Modified:	16 Dec 2019 13:54
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	configuration, dynamic configuration, global variables

Description:
This is really related to making the server be able to show it's settings, see http://bugs.mysql.com/bug.php?id=68451.

I'd like to be able to determine by querying the server if the variables are dynamic or static and also the type of variable and it's default settings etc.

There is documentation on the said page, so I tried to parse the html to collect the data and then build a table with the appropriate configuration information (as MysQL doesn't do this for me).

How to repeat:
Notice the following issues with the documentation:

(1) not all variables have a detailed description below the table

e.g. In table 'Table 5.3 System Variable Summary' there are 12 audit_* variables whose use is not described elsewhere.

(2) in the same table there are some variables with 2 rows differentiated by using underscores or dashes in their names.
e.g. big-tables, bind-address, collation-server etc.

Please be consistent. if the SHOW GLOBAL VARIABLES output shows values with underscores, use these consistently
and provide the command line values showing the hyphened version is being used.

(3) Dynamic scope.

There's only 1 value here "YES", which implies that if it's not set
the answer is "NO". In reality there are several different behaviours,
especially related to replication:
(a) YES - the value is truly dynamic

(b) NO - the value is truly static and can not be changed

(c) YES_IF_SLAVE_STOPPED - the value can be changed if the replication thread(s) are stopped.
e.g. SQL_SLAVE_SKIP_COUNTER can only be set if replication is stopped.
(It would be good to be clear whch threads actually need to be stopped.)

(d) YES_BUT_REQUIRES_SLAVE_RESTART - you can change the value but the new value only takes place once replication has been stopped and restarted.
Therefore checking the setting of this variable doesn't tell you if the value is actually being applied. e.g. slave_parallel_workers. I am
of the opinion that it would be best to change this type of variable to (c) above to avoid confusion or the need to check "other things" to
see if things are working as expected.

There may be other behaviours that I've missed.

The documentation is incomplete about the actual "dynamic behaviour".

Suggested fix:
So all these issues mean that quite a lot of work arounds need to be made to actually parse the html page to generate a set of tables with the MySQL system variables configuration settings.

Maybe not a big deal for a lot of people but if I want to dynamically configure my server I'd really like to be able to automatically determine which settings can be changed and which can't and under which circumstances. This also allows me to validate my /etc/my.cnf settings for configuration mistakes.

If possible please consider fixing the documentation so that it's as consistent as possible, or auto-generate it from something based in the code which would also allow bug#68451 to be resolved.

Thanks.

Thank you for the bug report.

"
(1) not all variables have a detailed description below the table

e.g. In table 'Table 5.3 System Variable Summary' there are 12 audit_* variables whose use is not described elsewhere.
"

That's true, they're not described below the table. It's not true they're not described elsewhere. They're described in the audit log section. There are a lot of entries in the table that are not in the list that follows the table. Replication and binary log-related variables come to mind.

Should I therefore assume that all options not mentioned _will_ have:
(1) a command line entry replacing underscores with hyphens?
(2) if they take a parameter how can I validate what value it may accept? (the type)
- some parameters don't take values and have a implicit " = 1 " others don't. This can be confusing.
(3) what's the default value ?
(4) Can I also assume that the /etc/my.cnf settings work with _either_ <option_name> with underscores or with hyphens, and again how can I validate what values i can use?

So I _really_ understand this is horribly tedious. ideally this information would be extracted directly from part of the source, so the documentation would be consistent.

Maybe it is not realistic to hope that this documentation is complete but for the reasons stated earlier it makes configuration management much more of a nuisance than might otherwise be the case.

My reply was limited to point 1 only, there are no implications about the other points.

Posted by developer:
 
"
Should I therefore assume that all options not mentioned _will_ have:
(1) a command line entry replacing underscores with hyphens?
...
(4) Can I also assume that the /etc/my.cnf settings work with _either_
<option_name> with underscores or with hyphens, and again how can I validate
what values i can use?
"

At server startup, yes, underscores and hyphens are equivalent.
https://dev.mysql.com/doc/refman/8.0/en/command-line-options.html says:

Within option names, dash (-) and underscore (_) may be used interchangeably. For example, --skip-grant-tables and --skip_grant_tables are equivalent. (However, the leading dashes cannot be given as underscores.)

Posted by developer:
 
"
So I _really_ understand this is horribly tedious. ideally this information
would be extracted directly from part of the source, so the documentation
would be consistent.
"

Generating the information from the source would be the ideal solution, yes.
But it's not possible because some of the option/variable values are determined
only during startup and cannot be determined statically.

Posted by developer:

re: "There is documentation on the said page, so I tried to parse the html to
collect the data and then build a table with the appropriate configuration
information (as MysQL doesn't do this for me)."

The refman is of course not intended to support the development of screen-scraping tools. That said, the following changes have been made based on your comments.

re: "(1) not all variables have a detailed description below the table"

Already commented on previously.

re: "(2) in the same table there are some variables with 2 rows differentiated by
using underscores or dashes in their names.
e.g. big-tables, bind-address, collation-server etc.

Please be consistent. if the SHOW GLOBAL VARIABLES output shows values with
underscores, use these consistently
and provide the command line values showing the hyphened version is being
used."

Instances where there were two lines should now be collapsed to a single-line entries, other than instances where an option and its associated system variable do have names that differ other than in dash/underscore (for example external-locking/skip_external_locking). There are two lines for log-bin/log_bin because those in fact are two different things.

Regarding use of dash versus underscore for the names in the first column, the rule now is: If an item can be used only at startup, the name has dashes. If it can be used at runtime (and possibly at startup), the name has underscores. Which contexts apply are indicated for all items in the following columns.

re: "(3) Dynamic scope."

Not touching this one, i.e., this is a "won't fix" issue. This will remain YES/NO. Such exceptions as may exist could be described in the more detailed information accompanying the relevant variables, but as I see no such information in the refman, I won't attempt to encode it in the summary tables.

The changes described above are about as far as I can take this on the docs end. Will also add a note to Bug#68451 indicating the same, since the long-term solution as you note is for the server to report more information about its configuration.

re: "Maybe not a big deal for a lot of people but if I want to dynamically
configure my server I'd really like to be able to automatically determine
which settings can be changed and which can't and under which circumstances.
This also allows me to validate my /etc/my.cnf settings for configuration
mistakes."

One way to check at runtime whether a variable is dynamic: Attempt to assign the variable its current value and check whether an error occurs. Inelegant, perhaps, but I find this a useful technique myself.