S
Sergei
Guest
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.
Continue reading...
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.
Continue reading...
