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.