Documentation Home
MySQL 9.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 259.0Kb
Man Pages (Zip) - 366.2Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


29.12.14.4 Performance Schema variables_metadata Table

The variables_metadata table shows, for each server system variable, its name, scope, type, range of values (where applicable), and description.

The variables_metadata table contains these columns:

  • VARIABLE_NAME

    The variable name.

  • VARIABLE_SCOPE

    The variable's scope; this is one of the values listed here:

    • GLOBAL

      The variable is global-only.

    • SESSION

      The variable can have global or session scope.

    • SESSION_ONLY

      The variable is session-only.

  • DATA_TYPE

    The variable's type; this is one of the following values:

    • Integer

      An integer.

    • Numeric

      A decimal value.

    • String

      A string.

    • Enumeration

      An enumeration.

    • Boolean

      A boolean true or false value.

    • Set

      A set of values.

    Possible values for variables of non-numeric types are often shown in the text of the DOCUMENTATION column; otherwise, see the variable's description in the Manual.

  • MIN_VALUE

    The minimum permitted value for the variable. For a variable that is not numeric, this is always an empty string.

    This column is intended to replace the variables_info table' MAX_VALUE column, which is deprecated in MySQL 9.0.

  • MAX_VALUE

    The maximum permitted value for the variable. For a variable that is not numeric, this is always an empty string.

    This column is intended to replace the variables_info table' MAX_VALUE column, which is deprecated in MySQL 9.0.

  • DOCUMENTATION

    A description of the variable; this is the same text as found in the output of mysqld --help --verbose.

The variables_metadata table has no indexes.

This table is read-only. The only DML statements allowed are SELECT and TABLE; DDL statements including TRUNCATE TABLE are not permitted.

The three queries using the variables_metadata table shown in the following example provide information about the binlog_row_image, innodb_doublewrite_batch_size, and secure_file_priv system variables:

Press CTRL+C to copy
mysql> SELECT * FROM variables_metadata WHERE VARIABLE_NAME='binlog_row_image'\G *************************** 1. row *************************** VARIABLE_NAME: binlog_row_image VARIABLE_SCOPE: SESSION DATA_TYPE: Enumeration MIN_VALUE: MAX_VALUE: DOCUMENTATION: Controls whether rows should be logged in 'FULL', 'NOBLOB' or 'MINIMAL' formats. 'FULL', means that all columns in the before and after image are logged. 'NOBLOB', means that mysqld avoids logging blob columns whenever possible (e.g. blob column was not changed or is not part of primary key). 'MINIMAL', means that a PK equivalent (PK columns or full row if there is no PK in the table) is logged in the before image, and only changed columns are logged in the after image. (Default: FULL). 1 row in set (0.01 sec) mysql> SELECT * FROM variables_metadata WHERE VARIABLE_NAME='innodb_doublewrite_batch_size'\G *************************** 1. row *************************** VARIABLE_NAME: innodb_doublewrite_batch_size VARIABLE_SCOPE: GLOBAL DATA_TYPE: Integer MIN_VALUE: 0 MAX_VALUE: 256 DOCUMENTATION: Number of double write pages to write in a batch 1 row in set (0.00 sec) mysql> SELECT * FROM variables_metadata WHERE VARIABLE_NAME='secure_file_priv'\G *************************** 1. row *************************** VARIABLE_NAME: secure_file_priv VARIABLE_SCOPE: GLOBAL DATA_TYPE: String MIN_VALUE: MAX_VALUE: DOCUMENTATION: Limit LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to files within specified directory 1 row in set (0.01 sec)

This table does not show the current values of system variables; this information is provided by the Performance Schema global_variables and session_variables tables.

The variables_metadata table was added in MySQL 9.0.