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.