Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30228

For system variables defined NO_CMD_LINE that have manually-defined configuration options, information_schema.SYSTEM_VARIABLES is incorrect

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
    • 10.5, 10.6, 10.11
    • Information Schema, Variables
    • None

    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

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31109 Parameters that can't be set from server command-line (and inconsistent documentation thereof)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            GeoffMontee Geoff Montee (Inactive) added a comment -

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

            GeoffMontee Geoff Montee (Inactive) added a comment - As mentioned in MDEV-31109 , default_storage_engine is another system variable that falls into this category.
            dlenski Daniel Lenski (Inactive) added a comment -

            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.

            dlenski Daniel Lenski (Inactive) added a comment - 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.

            People

              sanja Oleksandr Byelkin
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.