[MDEV-30228] For system variables defined NO_CMD_LINE that have manually-defined configuration options, information_schema.SYSTEM_VARIABLES is incorrect Created: 2022-12-14  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Information Schema, Variables
Affects Version/s: 10.3, 10.4, 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-31109 Parameters that can't be set from ser... Closed

 Description   

System variables can be defined with NO_CMD_LINE, which means that they technically do not support being set in configuration files or on the command-line. However, there are several cases like this, where a command-line option is separately defined with the same name that is tied to the system variable. From a user's perspective, these system variables can be set in configuration files and on the command-line.

autocommit is a system variable that falls into this category:

In these scenarios, the information_schema.SYSTEM_VARIABLES table can give bad information about the system variable. As the test below shows, this does apply to autocommit in particular, but it also probably impacts other system variables that are defined similarly, such as slave_parallel_mode.

Here is a non-exhaustive list of system variables that are likely impacted:

  • autocommit
  • default_storage_engine
  • slave_parallel_mode

If more are discovered, feel free to add them to the above list.

Steps to reproduce

1. Set autocommit=0 in a configuration file, such as /etc/my.cnf.d/z-server-no-autocommit.cnf:

[mariadb]
 
autocommit=0

2. Restart the server:

$ sudo systemctl restart mariadb

3. Connect to the server:

$ sudo mariadb

4. Query information_schema.SYSTEM_VARIABLES for details about the system variable:

SELECT * FROM information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='autocommit'\G

Actual results

Here are the results:

MariaDB [(none)]> SELECT * FROM information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='autocommit'\G
 
*************************** 1. row ***************************
 
        VARIABLE_NAME: AUTOCOMMIT
 
        SESSION_VALUE: OFF
 
         GLOBAL_VALUE: OFF
 
  GLOBAL_VALUE_ORIGIN: COMPILE-TIME
 
        DEFAULT_VALUE: ON
 
       VARIABLE_SCOPE: SESSION
 
        VARIABLE_TYPE: BOOLEAN
 
     VARIABLE_COMMENT: If set to 1, the default, all queries are committed immediately. If set to 0, they are only committed upon a COMMIT statement, or rolled back with a ROLLBACK statement. If autocommit is set to 0, and then changed to 1, all open transactions are immediately committed.
 
    NUMERIC_MIN_VALUE: NULL
 
    NUMERIC_MAX_VALUE: NULL
 
   NUMERIC_BLOCK_SIZE: NULL
 
      ENUM_VALUE_LIST: OFF,ON
 
            READ_ONLY: NO
 
COMMAND_LINE_ARGUMENT: NULL
 
    GLOBAL_VALUE_PATH: NULL
 
1 row in set (0.002 sec)

Expected results

There are a few things incorrect about the results:

  • GLOBAL_VALUE_ORIGIN should not be COMPILE-TIME, because that is incorrect
  • COMMAND_LINE_ARGUMENT should not be NULL
  • GLOBAL_VALUE_PATH should be /etc/my.cnf.d/z-server-no-autocommit.cnf instead of NULL

For comparison, here are the results for a system variable that is handled properly:

MariaDB [(none)]> SELECT * FROM information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='server_id'\G
 
*************************** 1. row ***************************
 
        VARIABLE_NAME: SERVER_ID
 
        SESSION_VALUE: 1
 
         GLOBAL_VALUE: 1
 
  GLOBAL_VALUE_ORIGIN: CONFIG
 
        DEFAULT_VALUE: 1
 
       VARIABLE_SCOPE: SESSION
 
        VARIABLE_TYPE: BIGINT UNSIGNED
 
     VARIABLE_COMMENT: Uniquely identifies the server instance in the community of replication partners
 
    NUMERIC_MIN_VALUE: 1
 
    NUMERIC_MAX_VALUE: 4294967295
 
   NUMERIC_BLOCK_SIZE: 1
 
      ENUM_VALUE_LIST: NULL
 
            READ_ONLY: NO
 
COMMAND_LINE_ARGUMENT: REQUIRED
 
    GLOBAL_VALUE_PATH: /etc/my.cnf.d/z-my.cnf
 
1 row in set (0.002 sec



 Comments   
Comment by Geoff Montee (Inactive) [ 2023-04-24 ]

As mentioned in MDEV-31109, default_storage_engine is another system variable that falls into this category.

Comment by Daniel Lenski [ 2023-04-24 ]

Thanks for linking MDEV-31109 here, GeoffMontee.

As discussed there, it appears that some relevant documentation (specifically https://mariadb.com/docs/skysql-new-release-dbaas/ref/mdb/system-variables/$VARIABLE_NAME) is automatically built from INFORMATION_SCHEMA.SYSTEM_VARIABLES, and thus incorrectly reflects whether or not these parameters can be set via CLI/option-file. Other documentation (https://mariadb.com/kb/en/server-system-variables/#$VARIABLE_NAME) doesn't seem to be automatically updated and is even more inconsistent.

Generated at Thu Feb 08 10:14:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.