We've attempted to use the metadata in INFORMATION_SCHEMA.SYSTEM_VARIABLES as a reliable indicator of whether or not a system variable can be set from config-file or CLI options…

Per https://mariadb.com/kb/en/information-schema-system_variables-table, if INFORMATION_SCHEMA.SYSTEM_VARIABLES.COMMAND_LINE_ARGUMENT is NULL then a variable cannot be set via CLI:

COMMAND_LINE_ARGUMENT	Whether an argument is required when setting the variable on the command line. NULL when a variable can not be set on the command line.

However, this too appears to be inaccurate: according to I_S.S_V, DEFAULT_STORAGE_ENGINE cannot be set via CLI:

MariaDB [(none)]> select * from information_schema.system_variables where variable_name='DEFAULT_STORAGE_ENGINE'\G

*************************** 1. row ***************************

        VARIABLE_NAME: DEFAULT_STORAGE_ENGINE

        SESSION_VALUE: InnoDB

         GLOBAL_VALUE: InnoDB

  GLOBAL_VALUE_ORIGIN: COMPILE-TIME

        DEFAULT_VALUE: InnoDB

       VARIABLE_SCOPE: SESSION

        VARIABLE_TYPE: VARCHAR

     VARIABLE_COMMENT: The default storage engine for new tables

    NUMERIC_MIN_VALUE: NULL

    NUMERIC_MAX_VALUE: NULL

   NUMERIC_BLOCK_SIZE: NULL

      ENUM_VALUE_LIST: NULL

            READ_ONLY: NO

COMMAND_LINE_ARGUMENT: NULL

    GLOBAL_VALUE_PATH: NULL

1 row in set (0.003 sec)

… but this is clearly wrong, because mariadbd does accept --default_storage_engine as a command-line argument:

% build/bin/mariadbd --no-defaults --socket=$DDIR/mysql.sock \

   --datadir=$DDIR/data --skip-grant-tables --skip-networking \

   --default_storage_engine=Aria

2023-04-21 11:39:47 0 [Note] Starting MariaDB 10.6.12-MariaDB source revision $COMMIT as process $PID

2023-04-21 11:39:47 0 [Note] build/bin/mariadbd: ready for connections.

Version: '10.6.12-MariaDB'  socket: '$DDIR/mysql.sock'  port: 0  managed by https://aws.amazon.com/rds/

… and then correctly reflects the value set from the CLI via I_S.S_V:

MariaDB [(none)]> select * from information_schema.system_variables where variable_name='DEFAULT_STORAGE_ENGINE'\G

*************************** 1. row ***************************

        VARIABLE_NAME: DEFAULT_STORAGE_ENGINE

        SESSION_VALUE: Aria

         GLOBAL_VALUE: Aria

  GLOBAL_VALUE_ORIGIN: COMPILE-TIME

        DEFAULT_VALUE: Aria

       VARIABLE_SCOPE: SESSION

        VARIABLE_TYPE: VARCHAR

     VARIABLE_COMMENT: The default storage engine for new tables

    NUMERIC_MIN_VALUE: NULL

    NUMERIC_MAX_VALUE: NULL

   NUMERIC_BLOCK_SIZE: NULL

      ENUM_VALUE_LIST: NULL

            READ_ONLY: NO

COMMAND_LINE_ARGUMENT: NULL

    GLOBAL_VALUE_PATH: NULL

1 row in set (0.001 sec)

What is, or should be, the single source of truth for metadata on system variables?

I thought it might ultimately lie in sys_vars.cc, but there too I find that DEFAULT_STORAGE_ENGINE is marked as NO_CMD_LINE… which is clearly wrong.

NO_CMD_LINE means that there is no automatic --command-line-option from a sysvar. But there might be a separately created command line option with some special code to handle its values, sysvar subsystem wouldn't know anything about it.

This is the case here, the option is defined in https://github.com/MariaDB/server/blob/mariadb-10.6.12/sql/mysqld.cc#L6442

This is because my_getopt can only handle basic types, but not plugins, and default_storage_engine is Sys_var_plugin. This can be fixed, of course, but it's a rather minor issue priority-wise.

This is the case here [for `--default-storage-engine`], the option is defined in https://github.com/MariaDB/server/blob/mariadb-10.6.12/sql/mysqld.cc#L6442

Got it, thanks.

This is because my_getopt can only handle basic types, but not plugins, and default_storage_engine is Sys_var_plugin. This can be fixed, of course, but it's a rather minor issue priority-wise.

As I show in the description, the behavior of system variables is already unnecessarily complex (some global variables don't have command-line or config options… still unsure why not) and the metadata is stored in a sufficiently inconsistent way that there is no internal consistency in the documentation.

This makes it difficult and error-prone to run MariaDB as a managed service, since there are regular surprises and complications in the interfaces to the system variables.

This also makes it difficult to test setting system via different interfaces in a consistent way.

KB entry for check_constraints is fixed