MySQL Bugs: #115662: Unexpected error and exit on unrecognized flags in flagset sysvar

Bug #115662	Unexpected error and exit on unrecognized flags in flagset sysvar
Submitted:	22 Jul 2024 8:32	Modified:	22 Jul 2024 8:35
Reporter:	Kaiwang CHen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	9.0.0, 8.4.0, 8.0.38	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
    In a setup with multiple installations of MySQL on the same machine,
    either name or value of an option might not be recognized by all
    versions. The --loose prefix can be used to ignore unrecognized
    option names, while numeric option values can be adjusted. Both
    issue warnings instead of exit on error.
    
    Flagset options are common practice to deliver new sub-features
    among minor versions. However, an unrecognized flagset value could
    not be adjusted, which would generate an error and abort bootstrap:
    
        mysqld --optimizer_switch='mrr=on,foo=off'
        [ERROR] [MY-000077] [Server] mysqld: Error while setting value
        'mrr=on,foo=off' to 'optimizer_switch'.

Note: It is a long-standing problem in production setup.  People are usually confused when the loose prefix
could not enable graceful start. It is actually not related to loose, because loose handles option names
rather than flag names. So far, flagset options are used to tune optimizer features, which is always changing.
There is no cost-based or feedback-based way yet in MySQL, so tuning flags are kind of common practice.

How to repeat:
With a 9.0.0 mysqld:

mysqld --optimizer_switch='mrr=on,foo=off' ...
2024-07-22T07:58:33.045977Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-07-22T07:58:33.367397Z 0 [ERROR] [MY-000077] [Server] mysqld: Error while setting value 'mrr=on,foo=off' to 'optimizer_switch'.
2024-07-22T07:58:33.367470Z 0 [ERROR] [MY-010119] [Server] Aborting

mysql> set optimizer_switch='mrr=on,foo=off';
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'foo=off'

In comparison, a numeric option is adjusted:

mysqld --transaction_alloc_block_size=131073
[Warning] [MY-000081] [Server] option 'transaction_alloc_block_size': unsigned value 131073 adjusted to 131072;

mysql> set transaction_alloc_block_size=131073;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect transaction_alloc_block_size value: '131073' |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
    Unrecognized flags resemble out-of-bound values, and could be
    adjusted in similar way as truncating numeric values.
    
    So, add extra params to find_set_from_flags() to collect known and
    unrecognized flags separately. Generate a warning instead of an
    error in both bootstrap and non-strict modes:
    
        mysqld --optimizer_switch='mrr=on,foo=off'
        [Warning] [MY-000094] [Server] option 'optimizer_switch': value
        'mrr=on,foo=off' adjusted to 'mrr=on', discarding 'foo=off'.
    
        mysql> set optimizer_switch='mrr=on,foo=off';
        -- non-strict mode
        Warning 6424 Adjusted incorrect optimizer_switch value:
        'mrr=on,foo=off', discarding 'foo=off'.
        -- strict mode, same error
        ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to
        the value of 'foo=off'

The enclosed patch is based on 9.0.0. It could be applied to previous releases by adapting error numbers. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: flagset_9000.patch (application/octet-stream, text), 14.99 KiB.

Hello Kaiwang,

Thank you for the feature request!

regards,
Umesh

postfix to flagset_9000 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: flagset_9000_2.patch (application/octet-stream, text), 5.62 KiB.

Thank you for your contribution!

However your basic assumption is not entirely correct IMHO. I believe that the flagset values are nothing like the numeric ones. And even if they are, it's the wrong usability direction.

If the user goes into the trouble of specifying a set value the more common assumption is that they want the server to understand it or refuse to accept it. Silent alteration of invalid values is a gotcha IMHO
that decreases the usability.

And to drive the parsing of the SQL mode value with a flag you specify as SQL mode value is even more confusing IMHO.

SQL mode is not a replacement for a system variable!

But I understand that this is a valid issue.

The best way I can currently think of going about it is to convert the invalid mode error to a warning only if the --loose prefix is specified.

We could probably also have a flag when the sysvar is defined in the code, but that would put the control in the wrong hands IMHO.