Daniel Lenski (Inactive) added a comment - 2023-04-21 18:44 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)

Daniel Lenski (Inactive) added a comment - 2023-04-21 18:51 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.

Sergei Golubchik added a comment - 2023-04-21 19:09 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

Sergei Golubchik added a comment - 2023-04-21 19:13 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.

Daniel Lenski (Inactive) added a comment - 2023-04-24 17:26 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.

Sergei Golubchik added a comment - 2023-05-27 16:27 KB entry for check_constraints is fixed