Supporting continuity and open collaboration

MariaDB 10.1.1: system variables and their metadata

I don’t think it’ll surprise anybody if I say that MariaDB or MySQL server knows a lot more about server system variables, then just their values. Indeed, every variable can be session or global only, read-only or writable, it has an associated help text (that is printed on mysqld --help --verbose), certain variables only accept values from a given set of strings (this set of allowed values is also printed in mysqld --help --verbose since MariaDB 10.1.0), numeric variables have lower and upper range boundaries of valid values (that are never printed anywhere), and so on. I always thought it’s kind of a waste that there is no way to query this information. That could’ve been very convenient, in particular for various GUI clients — they could show the help in tooltips, validate values and so on.

But recently we’ve got our users asking for it — precisely, for system variable metadata, whether a variable is read-only, how it was set and so on. You ask for it — you got it. Let me introduce the INFORMATION_SCHEMA.SYSTEM_VARIABLES table. For every system variable it shows both its global and session values, where the global value comes from (config file or a command line, assigned from SQL, auto-configured by the server, compile-time default), the type and the scope of the variable, its default value, range of values for numeric variables, set of allowed values for ENUM/SET variables, whether a variable is read-only, whether it can be set from the command line. And a help text, of course.
It is said that a picture is worth a thousand words, so here you are:

MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME='JOIN_BUFFER_SIZE'G
*************************** 1. row *****************************
        VARIABLE_NAME: JOIN_BUFFER_SIZE
        SESSION_VALUE: 131072
         GLOBAL_VALUE: 131072
  GLOBAL_VALUE_ORIGIN: COMPILE-TIME
        DEFAULT_VALUE: 131072
       VARIABLE_SCOPE: SESSION
        VARIABLE_TYPE: BIGINT UNSIGNED
     VARIABLE_COMMENT: The size of the buffer that is used for joins
    NUMERIC_MIN_VALUE: 128
    NUMERIC_MAX_VALUE: 18446744073709551615
   NUMERIC_BLOCK_SIZE: 128
      ENUM_VALUE_LIST: NULL
            READ_ONLY: NO
COMMAND_LINE_ARGUMENT: REQUIRED
1 row in set (0.01 sec)

MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME='PLUGIN_MATURITY'G
*************************** 1. row ***************************
        VARIABLE_NAME: PLUGIN_MATURITY
        SESSION_VALUE: NULL
         GLOBAL_VALUE: beta
  GLOBAL_VALUE_ORIGIN: CONFIG
        DEFAULT_VALUE: unknown
       VARIABLE_SCOPE: GLOBAL
        VARIABLE_TYPE: ENUM
     VARIABLE_COMMENT: The lowest desirable plugin maturity. Plugins less mature than that will not be installed or loaded
    NUMERIC_MIN_VALUE: NULL
    NUMERIC_MAX_VALUE: NULL
   NUMERIC_BLOCK_SIZE: NULL
      ENUM_VALUE_LIST: unknown,experimental,alpha,beta,gamma,stable
            READ_ONLY: YES
COMMAND_LINE_ARGUMENT: REQUIRED
1 row in set (0.01 sec)

Note that READ_ONLY only means “cannot be changed run-time”, the variable might still be writable from the command line or a config file. Here’s the list of all truly read-only variables that can not be modified at all:

MariaDB [test]> SELECT VARIABLE_NAME,VARIABLE_TYPE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE READ_ONLY='YES' AND COMMAND_LINE_ARGUMENT IS NULL ORDER BY 1;
+---------------------------+-----------------+
| VARIABLE_NAME             | VARIABLE_TYPE   |
+---------------------------+-----------------+
| ARIA_USED_FOR_TEMP_TABLES | BOOLEAN         |
| CHARACTER_SET_SYSTEM      | ENUM            |
| ERROR_COUNT               | BIGINT UNSIGNED |
| EXTERNAL_USER             | VARCHAR         |
| GTID_BINLOG_POS           | VARCHAR         |
| GTID_CURRENT_POS          | VARCHAR         |
| HAVE_COMPRESS             | VARCHAR         |
| HAVE_CRYPT                | VARCHAR         |
| HAVE_DYNAMIC_LOADING      | VARCHAR         |
| HAVE_GEOMETRY             | VARCHAR         |
| HAVE_OPENSSL              | VARCHAR         |
| HAVE_PROFILING            | VARCHAR         |
| HAVE_QUERY_CACHE          | VARCHAR         |
| HAVE_RTREE_KEYS           | VARCHAR         |
| HAVE_SSL                  | VARCHAR         |
| HAVE_SYMLINK              | VARCHAR         |
| HOSTNAME                  | VARCHAR         |
| IN_TRANSACTION            | BIGINT UNSIGNED |
| LARGE_FILES_SUPPORT       | BOOLEAN         |
| LARGE_PAGE_SIZE           | INT UNSIGNED    |
| LAST_GTID                 | VARCHAR         |
| LICENSE                   | VARCHAR         |
| LOCKED_IN_MEMORY          | BOOLEAN         |
| LOG_BIN                   | BOOLEAN         |
| LOWER_CASE_FILE_SYSTEM    | BOOLEAN         |
| PROTOCOL_VERSION          | INT UNSIGNED    |
| PROXY_USER                | VARCHAR         |
| SKIP_EXTERNAL_LOCKING     | BOOLEAN         |
| SYSTEM_TIME_ZONE          | VARCHAR         |
| VERSION                   | VARCHAR         |
| VERSION_COMMENT           | VARCHAR         |
| VERSION_COMPILE_MACHINE   | VARCHAR         |
| VERSION_COMPILE_OS        | VARCHAR         |
| VERSION_MALLOC_LIBRARY    | VARCHAR         |
| WARNING_COUNT             | BIGINT UNSIGNED |
+---------------------------+-----------------+
35 rows in set (0.06 sec)

You might wonder what LOG_BIN is doing in this list. It’s because on the command line you can never specify this boolean read-only variable, what you actually specify is a command-line option --log-bin that takes a string. It happens that this new SYSTEM_VARIABLES table is good at highlighting dark corners in the MariaDB/MySQL internal implementation of system variables.

Platinum Sponsors

MariaDB Foundation Platinum sponsors

Thank you, Booking.com! Thank you, Alibaba Cloud! Thank you, Tencent Cloud! Thank you, Microsoft! Thank you, MariaDB Corporation!

Gold Sponsors

MariaDB Foundation Gold sponsors

Thank you, DBS! Thank you, Visma! Thank you, IBM! Thank you, Tencent Games!

Code statistics