Explore system tables in MariaDB Server. These internal tables store metadata and configuration information about the database, essential for administration, monitoring, and advanced querying.
This system database contains crucial metadata about the server, including information schema, statistics, and optimizer hints, for internal operations.
mariadb_schema is a data type qualifier that allows one to create MariaDB native date types in an SQL_MODE that has conflicting data type translations.
For example, in SQL_MODE=ORACLE, if you create a table with the DATE type, it actually creates a DATETIME column to match what an Oracle user is expecting. To be able to create a MariaDB DATE in Oracle mode, you would have to use mariadb_schema:
CREATE TABLE t1 (d mariadb_schema.DATE);mariadb_schema is also shown if one creates a table with DATE in MariaDB native mode and then does a SHOW CREATE TABLE in ORACLE mode:
SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"d" mariadb_schema.DATE DEFAULT NULL
) |
+-------+--------------------------------------------------------------+When the server sees the mariadb_schema qualifier, it disables sql_mode-specific data type translation and interprets the data type literally, so for example mariadb_schema.DATE is interpreted as the traditional MariaDB DATE data type, no matter what the current sql_mode is.
The mariadb_schema prefix is displayed only when the data type name would be ambiguous otherwise. The prefix is displayed together with MariaDB DATE when SHOW CREATE TABLE is executed in SQL_MODE=ORACLE. The prefix is not displayed when SHOW CREATE TABLE is executed in SQL_MODE=DEFAULT, or when a non-ambiguous data type is displayed.
Note, the mariadb_schema prefix can be used with any data type, including non-ambiguous ones:
CREATE OR REPLACE TABLE t1 (a mariadb_schema.INT);
SHOW CREATE TABLE t1;
+-------+--------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" INT(11) DEFAULT NULL
) |
+-------+--------------------------------------------------+Currently the mariadb_schema prefix is only used in the following case:
For a MariaDB native DATE type when running SHOW CREATE TABLE in Oracle mode.
When running with SQL_MODE=ORACLE, MariaDB server translates the data type DATE to DATETIME, for better Oracle compatibility:
SET SQL_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (
d DATE
);
SHOW CREATE TABLE t1;
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"d" datetime DEFAULT NULL
) |
+-------+---------------------------------------------------+Notice, DATE was translated to DATETIME.
This translation may cause some ambiguity. Suppose a user creates a table with a column of the traditional MariaDB DATE data type using the default sql_mode, but then switches to SQL_MODE=ORACLE and runs a SHOW CREATE TABLE statement:
SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;Before mariadb_schema was introduced, the above script displayed:
CREATE TABLE "t1" (
"d" DATE DEFAULT NULL
);which had two problems:
It was confusing for the reader: its not clear if it is the traditional MariaDB DATE, or is it Oracle-alike date (which is actually DATETIME);
It broke replication and caused data type mismatch on the master and on the slave (see MDEV-19632).
To address this problem, starting from the mentioned versions, MariaDB uses the idea of qualified data types:
SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"d" mariadb_schema.DATE DEFAULT NULL
) |
+-------+--------------------------------------------------------------+This page is licensed: CC BY-SA / Gnu FDL
Query information_schema.tables in MariaDB Server. This system table provides metadata about all tables in the databases, including their names, types, storage engines, and other crucial properties.
In MariaDB, an extra column TIME_MS has been added to theINFORMATION_SCHEMA.PROCESSLIST table. This column shows the same information as the column 'TIME', but in units of milliseconds with microsecond precision (the unit and precision of theTIME column is one second).
For details about microseconds support in MariaDB, see microseconds in MariaDB.
The value displayed in the TIME andTIME_MS columns is the period of time that the given thread has been in its current state. Thus it can be used to check for example how long a thread has been executing the current query, or for how long it has been idle.
SELECT id, TIME, time_ms, command, state FROM
information_schema.processlist, (SELECT sleep(2)) t;
+----+------+----------+---------+-----------+
| id | time | time_ms | command | state |
+----+------+----------+---------+-----------+
| 37 | 2 | 2000.493 | Query | executing |
+----+------+----------+---------+-----------+Note that as a difference to MySQL, in MariaDB the TIME column (and also the TIME_MS column) are not affected by any setting of @TIMESTAMP. This means that it can be reliably used also for threads that change @TIMESTAMP (such as the replication SQL thread). See also MySQL Bug #22047.
As a consequence of this, the TIME column ofSHOW FULL PROCESSLIST andINFORMATION_SCHEMA.PROCESSLIST can not be used to determine if a slave is lagging behind. For this, use instead theSeconds_Behind_Master column in the output ofSHOW SLAVE STATUS.
The addition of the TIME_MS column is based on the microsec_process patch, developed by Percona.
This page is licensed: CC BY-SA / Gnu FDL
Query information_schema.tables in MariaDB Server. These system tables provides metadata about all tables in the databases, including their names, types, storage engines, and other crucial properties.
The Information Schema ALL_PLUGINS table contains information about server plugins, whether installed or not.
It contains the following columns:
PLUGIN_NAME
Name of the plugin.
PLUGIN_VERSION
Version from the plugin's general type descriptor.
PLUGIN_STATUS
Plugin status, one of ACTIVE, INACTIVE, DISABLED, DELETED or NOT INSTALLED.
PLUGIN_TYPE
Plugin type; STORAGE ENGINE, INFORMATION_SCHEMA, AUTHENTICATION, REPLICATION, DAEMON or AUDIT.
PLUGIN_TYPE_VERSION
Version from the plugin's type-specific descriptor.
PLUGIN_LIBRARY
Plugin's shared object file name, located in the directory specified by the plugin_dir system variable, and used by the INSTALL PLUGIN and UNINSTALL PLUGIN statements. NULL if the plugin is complied in and cannot be uninstalled.
PLUGIN_LIBRARY_VERSION
Version from the plugin's API interface.
PLUGIN_AUTHOR
Author of the plugin.
PLUGIN_DESCRIPTION
Description.
PLUGIN_LICENSE
Plugin's licence.
LOAD_OPTION
How the plugin was loaded; one of OFF, ON, FORCE or FORCE_PLUS_PERMANENT. See Installing Plugins.
PLUGIN_MATURITY
Plugin's maturity level; one of Unknown, Experimental, Alpha, Beta,'Gamma, and Stable.
PLUGIN_AUTH_VERSION
Plugin's version as determined by the plugin author. An example would be '0.99 beta 1'.
It provides a superset of the information shown by the SHOW PLUGINS SONAME statement, as well as the information_schema.PLUGINS table. For specific information about storage engines (a particular type of plugin), see the Information Schema ENGINES table and the SHOW ENGINES statement.
The table is not a standard Information Schema table, and is a MariaDB extension.
SELECT * FROM information_schema.all_plugins\G
*************************** 1. row ***************************
PLUGIN_NAME: binlog
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 100314.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: MySQL AB
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 2. row ***************************
PLUGIN_NAME: mysql_native_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 2.1
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Native MySQL authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 3. row ***************************
PLUGIN_NAME: mysql_old_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 2.1
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Old MySQL-4.0 authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
...
*************************** 104. row ***************************
PLUGIN_NAME: WSREP_MEMBERSHIP
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: NOT INSTALLED
PLUGIN_TYPE: INFORMATION SCHEMA
PLUGIN_TYPE_VERSION: 100314.0
PLUGIN_LIBRARY: wsrep_info.so
PLUGIN_LIBRARY_VERSION: 1.13
PLUGIN_AUTHOR: Nirbhay Choubey
PLUGIN_DESCRIPTION: Information about group members
PLUGIN_LICENSE: GPL
LOAD_OPTION: OFF
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 105. row ***************************
PLUGIN_NAME: WSREP_STATUS
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: NOT INSTALLED
PLUGIN_TYPE: INFORMATION SCHEMA
PLUGIN_TYPE_VERSION: 100314.0
PLUGIN_LIBRARY: wsrep_info.so
PLUGIN_LIBRARY_VERSION: 1.13
PLUGIN_AUTHOR: Nirbhay Choubey
PLUGIN_DESCRIPTION: Group view information
PLUGIN_LICENSE: GPL
LOAD_OPTION: OFF
PLUGIN_MATURITY: StableThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema APPLICABLE_ROLES table shows the role authorizations that the current user may use.
It contains the following columns:
GRANTEE
Account that the role was granted to.
ROLE_NAME
Name of the role.
IS_GRANTABLE
Whether the role can be granted or not.
IS_DEFAULT
Whether the role is the user's default role or not
The current role is in the ENABLED_ROLES Information Schema table.
SELECT * FROM information_schema.APPLICABLE_ROLES;
+----------------+-------------+--------------+------------+
| GRANTEE | ROLE_NAME | IS_GRANTABLE | IS_DEFAULT |
+----------------+-------------+--------------+------------+
| root@localhost | journalist | YES | NO |
| root@localhost | staff | YES | NO |
| root@localhost | dd | YES | NO |
| root@localhost | dog | YES | NO |
+----------------+-------------+--------------+------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema CATALOG table stores information about catalogs on the server.
It contains the following columns:
CATALOG_NAME
Catalog name.
DEFAULT_CHARACTER_SET_NAME
Default character set for the database.
DEFAULT_COLLATION_NAME
Default collation.
SCHEMA_COMMENT
Catalog comment
MariaDB [def.test]> SELECT * FROM INFORMATION_SCHEMA.CATALOGS\G
*************************** 1. row ***************************
CATALOG_NAME: c1
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
CATALOG_COMMENT: This is catalog c1
*************************** 2. row ***************************
CATALOG_NAME: cat2
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
CATALOG_COMMENT:
*************************** 3. row ***************************
CATALOG_NAME: def
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
CATALOG_COMMENT: default catalog
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema CHARACTER_SETS table contains a list of supported character sets, their default collations and maximum lengths.
It contains the following columns:
CHARACTER_SET_NAME
Name of the character set.
DEFAULT_COLLATE_NAME
Default collation used.
DESCRIPTION
Character set description.
MAXLEN
Maximum length.
The SHOW CHARACTER SET statement returns the same results (although in a different order), and both can be refined in the same way. For example, the following two statements return the same results:
SHOW CHARACTER SET WHERE Maxlen LIKE '2';and
SELECT * FROM information_schema.CHARACTER_SETS
WHERE MAXLEN LIKE '2';See Setting Character Sets and Collations for details on specifying the character set at the server, database, table and column levels, and Supported Character Sets and Collations for a full list of supported characters sets and collations.
SELECT CHARACTER_SET_NAME FROM information_schema.CHARACTER_SETS
WHERE DEFAULT_COLLATE_NAME LIKE '%chinese%';
+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| big5 |
| gb2312 |
| gbk |
+--------------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema CHECK_CONSTRAINTS table stores metadata about the constraints defined for tables in all databases.
It contains the following columns:
CONSTRAINT_CATALOG
Always contains the string 'def'.
CONSTRAINT_SCHEMA
Database name.
CONSTRAINT_NAME
Constraint name.
TABLE_NAME
Table name.
LEVEL
Type of the constraint ('Column' or 'Table'). From MariaDB 10.5.10.
CHECK_CLAUSE
Constraint clause.
A table with a numeric table check constraint and with a default check constraint name:
CREATE TABLE t ( a INT, CHECK (a>10));To see check constraint call check_constraints table from information schema.
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS\G*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: CONSTRAINT_1
TABLE_NAME: t
CHECK_CLAUSE: `a` > 10A new table check constraint called a_upper:
ALTER TABLE t ADD CONSTRAINT a_upper CHECK (a<100);SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS\G*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: CONSTRAINT_1
TABLE_NAME: t
CHECK_CLAUSE: `a` > 10
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: a_upper
TABLE_NAME: t
CHECK_CLAUSE: `a` < 100A new table tt with a field check constraint called b , as well as a table check constraint called b_upper:
CREATE TABLE tt(b INT CHECK(b>0),CONSTRAINT b_upper CHECK(b<50));
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+------------+--------------+
| def | test | b | tt | `b` > 0 |
| def | test | b_upper | tt | `b` < 50 |
| def | test | CONSTRAINT_1 | t | `a` > 10 |
| def | test | a_upper | t | `a` < 100 |
+--------------------+-------------------+-----------------+------------+--------------+Note: The name of the field constraint is the same as the field name.
After dropping the default table constraint called CONSTRAINT_1:
ALTER TABLE t DROP CONSTRAINT CONSTRAINT_1;
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+------------+--------------+
| def | test | b | tt | `b` > 0 |
| def | test | b_upper | tt | `b` < 50 |
| def | test | a_upper | t | `a` < 100 |
+--------------------+-------------------+-----------------+------------+--------------+Trying to insert invalid arguments into table t and tt generates an error.
INSERT INTO t VALUES (10),(20),(100);
ERROR 4025 (23000): CONSTRAINT `a_upper` failed for `test`.`t`
INSERT INTO tt VALUES (10),(-10),(100);
ERROR 4025 (23000): CONSTRAINT `b` failed for `test`.`tt`
INSERT INTO tt VALUES (10),(20),(100);
ERROR 4025 (23000): CONSTRAINT `b_upper` failed for `test`.`tt`From MariaDB 10.5.10:
CREATE TABLE majra(CHECK(x>0), x INT, y INT CHECK(y < 0), z INT,
CONSTRAINT z CHECK(z>0), CONSTRAINT xyz CHECK(x<10 AND y<10 AND z<10));
Query OK, 0 rows affected (0.036 sec)
SHOW CREATE TABLE majra;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| majra | CREATE TABLE `majra` (
`x` int(11) DEFAULT NULL,
`y` int(11) DEFAULT NULL CHECK (`y` < 0),
`z` int(11) DEFAULT NULL,
CONSTRAINT `CONSTRAINT_1` CHECK (`x` > 0),
CONSTRAINT `z` CHECK (`z` > 0),
CONSTRAINT `xyz` CHECK (`x` < 10 AND `y` < 10 AND `z` < 10)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
SELECT * FROM information_schema.check_constraints WHERE table_name='majra';
+--------------------+-------------------+------------+-----------------+--------+------------------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | LEVEL | CHECK_CLAUSE |
+--------------------+-------------------+------------+-----------------+--------+------------------------------------+
| def | test | majra | y | Column | `y` < 0 |
| def | test | majra | CONSTRAINT_1 | Table | `x` > 0 |
| def | test | majra | z | Table | `z` > 0 |
| def | test | majra | xyz | Table | `x` < 10 and `y` < 10 and `z` < 10 |
+--------------------+-------------------+------------+-----------------+--------+------------------------------------+
4 rows in set (0.001 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema CLIENT_STATISTICS table holds statistics about client connections. This is part of the User Statistics feature, which is not enabled by default.
It contains the following columns:
CLIENT
VARCHAR(64)
The IP address or hostname the connection originated from.
TOTAL_CONNECTIONS
BIGINT(21)
The number of connections created for this client.
CONCURRENT_CONNECTIONS
BIGINT(21)
The number of concurrent connections for this client.
CONNECTED_TIME
BIGINT(21)
The cumulative number of seconds elapsed while there were connections from this client.
BUSY_TIME
DOUBLE
The cumulative number of seconds there was activity on connections from this client.
CPU_TIME
DOUBLE
The cumulative CPU time elapsed while servicing this client's connections. Note that this number may be wrong on SMP system if there was a CPU migration for the thread during the execution of the query.
BYTES_RECEIVED
BIGINT(21)
The number of bytes received from this client's connections.
BYTES_SENT
BIGINT(21)
The number of bytes sent to this client's connections.
BINLOG_BYTES_WRITTEN
BIGINT(21)
The number of bytes written to the binary log from this client's connections.
ROWS_READ
BIGINT(21)
The number of rows read by this client's connections.
ROWS_SENT
BIGINT(21)
The number of rows sent by this client's connections.
ROWS_DELETED
BIGINT(21)
The number of rows deleted by this client's connections.
ROWS_INSERTED
BIGINT(21)
The number of rows inserted by this client's connections.
ROWS_UPDATED
BIGINT(21)
The number of rows updated by this client's connections.
OTHER_COMMANDS
BIGINT(21)
The number of other commands executed from this client's connections.
DENIED_CONNECTIONS
BIGINT(21)
The number of connections denied to this client.
LOST_CONNECTIONS
BIGINT(21)
The number of this client's connections that were terminated uncleanly.
ACCESS_DENIED
BIGINT(21)
The number of times this client's connections issued commands that were denied.
EMPTY_QUERIES
BIGINT(21)
The number of times this client's connections sent queries that returned no results to the server.
MAX_STATEMENT_TIME_EXCEEDED
BIGINT(21)
The number of times a statement was aborted, because it was executed longer than its MAX_STATEMENT_TIME threshold.
SELECT * FROM information_schema.CLIENT_STATISTICS\G
*************************** 1. row ***************************
CLIENT: localhost
TOTAL_CONNECTIONS: 3
CONCURRENT_CONNECTIONS: 0
CONNECTED_TIME: 4883
BUSY_TIME: 0.009722
CPU_TIME: 0.0102131
BYTES_RECEIVED: 841
BYTES_SENT: 13897
BINLOG_BYTES_WRITTEN: 0
ROWS_READ: 0
ROWS_SENT: 214
ROWS_DELETED: 0
ROWS_INSERTED: 207
ROWS_UPDATED: 0
SELECT_COMMANDS: 10
UPDATE_COMMANDS: 0
OTHER_COMMANDS: 13
COMMIT_TRANSACTIONS: 0
ROLLBACK_TRANSACTIONS: 0
DENIED_CONNECTIONS: 0
LOST_CONNECTIONS: 0
ACCESS_DENIED: 0
EMPTY_QUERIES: 1This page is licensed: CC BY-SA / Gnu FDL
The Information Schema COLLATION_CHARACTER_SET_APPLICABILITY table shows which character sets are associated with which collations.
It contains the following columns:
COLLATION_NAME
Collation name.
CHARACTER_SET_NAME
Name of the associated character set.
FULL_COLLATION_NAME
Name of the associated character set/collation combination.
ID
The unique identifier of this character set/collation combination.
IS_DEFAULT
If the collation is the default for this character set.
The COLLATIONS table is table of the base COLLATION_NAMES in the same way that CHARACTER_SETS table is table of the base CHARACTER_SET_NAMES. The COLLATION_CHARACTER_SET_APPLICABILITY table is the mapping between collations and character sets.
When joining the information_schema.TABLES table with its field TABLE_COLLATIONS, this should be joined to FULL_COLLATION_NAME in the COLLATION_CHARACTER_SET_APPLICABILITY table.
See Setting Character Sets and Collations for details on specifying the character set at the server, database, table and column levels.
SELECT * FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY WHERE
CHARACTER_SET_NAME='utf32' ORDER BY IS_DEFAULT DESC, ID LIMIT 10;
+--------------------+--------------------+---------------------+-----+------------+
| COLLATION_NAME | CHARACTER_SET_NAME | FULL_COLLATION_NAME | ID | IS_DEFAULT |
+--------------------+--------------------+---------------------+-----+------------+
| utf32_general_ci | utf32 | utf32_general_ci | 60 | Yes |
| utf32_bin | utf32 | utf32_bin | 61 | |
| utf32_unicode_ci | utf32 | utf32_unicode_ci | 160 | |
| utf32_icelandic_ci | utf32 | utf32_icelandic_ci | 161 | |
| utf32_latvian_ci | utf32 | utf32_latvian_ci | 162 | |
| utf32_romanian_ci | utf32 | utf32_romanian_ci | 163 | |
| utf32_slovenian_ci | utf32 | utf32_slovenian_ci | 164 | |
| utf32_polish_ci | utf32 | utf32_polish_ci | 165 | |
| utf32_estonian_ci | utf32 | utf32_estonian_ci | 166 | |
| utf32_spanish_ci | utf32 | utf32_spanish_ci | 167 | |
+--------------------+--------------------+---------------------+-----+------------+
10 rows in set (0.004 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema COLLATIONS table contains a list of supported collations.
It contains the following columns:
COLLATION_NAME
Name of the collation.
CHARACTER_SET_NAME
Associated character set.
ID
Collation id.
IS_DEFAULT
Whether the collation is the character set's default.
IS_COMPILED
Whether the collation is compiled into the server.
SORTLEN
Sort length, used for determining the memory used to sort strings in this collation.
COMMENT
For utf8mb4_0900 collations, contains the corresponding alias collation.
The SHOW COLLATION statement returns the same results and both can be reduced in a similar way.
The following two statements return the same results:
SHOW COLLATION WHERE Charset LIKE 'utf8mb3';SELECT * FROM information_schema.COLLATIONS
WHERE CHARACTER_SET_NAME LIKE 'utf8mb3';The following two statements return the same results:
SHOW COLLATION WHERE Charset LIKE 'utf8';SELECT * FROM information_schema.COLLATIONS
WHERE CHARACTER_SET_NAME LIKE 'utf8';NO PAD collations regard trailing spaces as normal characters. You can get a list of all NO PAD collations as follows:
SELECT collation_name FROM information_schema.COLLATIONS
WHERE collation_name LIKE "%nopad%";
+------------------------------+
| collation_name |
+------------------------------+
| big5_chinese_nopad_ci |
| big5_nopad_bin |
...SELECT * FROM information_schema.COLLATIONS;
+------------------------------+--------------------+------+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------------------+--------------------+------+------------+-------------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| big5_chinese_nopad_ci | big5 | 1025 | | Yes | 1 |
| big5_nopad_bin | big5 | 1108 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| dec8_swedish_nopad_ci | dec8 | 1027 | | Yes | 1 |
| dec8_nopad_bin | dec8 | 1093 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
...Setting Character Sets and Collations - specifying the character set at the server, database, table and column levels
Supported Character Sets and Collations - full list of supported characters sets and collations.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema COLUMN_PRIVILEGES table contains column privilege information derived from the mysql.columns_priv grant table.
It has the following columns:
GRANTEE
In the format user_name@host_name.
TABLE_CATALOG
Always def.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
COLUMN_NAME
Column name.
PRIVILEGE_TYPE
One of SELECT, INSERT, UPDATE or REFERENCES.
IS_GRANTABLE
Whether the user has the GRANT OPTION for this privilege.
Similar information can be accessed with the SHOW FULL COLUMNS and SHOW GRANTS statements. See the GRANT article for more about privileges.
This information is also stored in the columns_priv table, in the mysql system database.
For a description of the privileges that are shown in this table, see column privileges.
In the following example, no column-level privilege has been explicitly assigned:
SELECT * FROM information_schema.COLUMN_PRIVILEGES;
Empty SETThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema COLUMNS table provides information about columns in each table on the server.
It contains the following columns:
TABLE_CATALOG
Always contains the string 'def'.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
COLUMN_NAME
Column name.
ORDINAL_POSITION
Column position in the table. Can be used for ordering.
COLUMN_DEFAULT
Default value for the column. Literals are quoted to distinguish them from expressions. NULL means that the column has no default.
IS_NULLABLE
Whether the column can contain NULLs.
DATA_TYPE
The column's data type.
CHARACTER_MAXIMUM_LENGTH
Maximum length.
CHARACTER_OCTET_LENGTH
Same as the CHARACTER_MAXIMUM_LENGTH except for multi-byte character sets.
NUMERIC_PRECISION
For numeric types, the precision (number of significant digits) for the column. NULL if not a numeric field.
NUMERIC_SCALE
For numeric types, the scale (significant digits to the right of the decimal point). NULL if not a numeric field.
DATETIME_PRECISION
Fractional-seconds precision, or NULL if not a time data type.
CHARACTER_SET_NAME
Character set if a non-binary string data type, otherwise NULL.
COLLATION_NAME
Collation if a non-binary string data type, otherwise NULL.
COLUMN_TYPE
Column definition, a MySQL and MariaDB extension.
COLUMN_KEY
Index type. PRI for primary key, UNI for unique index, MUL for multiple index. A MySQL and MariaDB extension.
EXTRA
Additional information about a column, for example whether the column is an invisible column, or WITHOUT SYSTEM VERSIONING if the table is not a system-versioned table. A MySQL and MariaDB extension.
PRIVILEGES
Which privileges you have for the column. A MySQL and MariaDB extension.
COLUMN_COMMENT
Column comments.
IS_GENERATED
Indicates whether the column value is generated (virtual, or computed). Can be ALWAYS or NEVER.
GENERATION_EXPRESSION
The expression used for computing the column value in a generated (virtual, or computed) column.
IS_SYSTEM_TIME_PERIOD_START
From MariaDB 11.4.1.
IS_SYSTEM_TIME_PERIOD_END
From MariaDB 11.4.1.
It provides information similar to, but more complete, than SHOW COLUMNS and mariadb-show.
SELECT * FROM information_schema.COLUMNS\G
...
*************************** 9. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t2
COLUMN_NAME: j
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: longtext
CHARACTER_MAXIMUM_LENGTH: 4294967295
CHARACTER_OCTET_LENGTH: 4294967295
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_bin
COLUMN_TYPE: longtext
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
IS_GENERATED: NEVER
GENERATION_EXPRESSION: NULL
...CREATE TABLE t (
s1 VARCHAR(20) DEFAULT 'ABC',
s2 VARCHAR(20) DEFAULT (concat('A','B')),
s3 VARCHAR(20) DEFAULT ("concat('A','B')"),
s4 VARCHAR(20),
s5 VARCHAR(20) DEFAULT NULL,
s6 VARCHAR(20) NOT NULL,
s7 VARCHAR(20) DEFAULT 'NULL' NULL,
s8 VARCHAR(20) DEFAULT 'NULL' NOT NULL
);
SELECT
table_name,
column_name,
ordinal_position,
column_default,
column_default IS NULL
FROM information_schema.COLUMNS
WHERE table_schema=DATABASE()
AND TABLE_NAME='t';
+------------+-------------+------------------+-----------------------+------------------------+
| table_name | column_name | ordinal_position | column_default | column_default IS NULL |
+------------+-------------+------------------+-----------------------+------------------------+
| t | s1 | 1 | 'ABC' | 0 |
| t | s2 | 2 | concat('A','B') | 0 |
| t | s3 | 3 | 'concat(''A'',''B'')' | 0 |
| t | s4 | 4 | NULL | 0 |
| t | s5 | 5 | NULL | 0 |
| t | s6 | 6 | NULL | 1 |
| t | s7 | 7 | 'NULL' | 0 |
| t | s8 | 8 | 'NULL' | 0 |
+------------+-------------+------------------+-----------------------+------------------------+In the results above, the two single quotes in concat(''A'',''B'') indicate an escaped single quote - see string-literals. Note that while mariadb client appears to show the same default value for columns s5 and s6, the first is a 4-character string "NULL", while the second is the SQL NULL value.
From MariaDB 11.3:
CREATE TABLE t(
x INT,
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
IS_SYSTEM_TIME_PERIOD_START, IS_SYSTEM_TIME_PERIOD_END
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t'\G
*************************** 1. row ***************************
TABLE_NAME: t
COLUMN_NAME: x
ORDINAL_POSITION: 1
IS_SYSTEM_TIME_PERIOD_START: NO
IS_SYSTEM_TIME_PERIOD_END: NO
*************************** 2. row ***************************
TABLE_NAME: t
COLUMN_NAME: start_timestamp
ORDINAL_POSITION: 2
IS_SYSTEM_TIME_PERIOD_START: YES
IS_SYSTEM_TIME_PERIOD_END: NO
*************************** 3. row ***************************
TABLE_NAME: t
COLUMN_NAME: end_timestamp
ORDINAL_POSITION: 3
IS_SYSTEM_TIME_PERIOD_START: NO
IS_SYSTEM_TIME_PERIOD_END: YESThis page is licensed: CC BY-SA / Gnu FDL
The plugin only works on Linux.
The DISKS table is created when the DISKS plugin is enabled, and shows metadata about disks on the system.
This plugin requires the FILE privilege.
This plugin does not check user privileges. When it is enabled, any user can query the INFORMATION_SCHEMA.DISKS table and see all the information it provides.
The table contains the following columns:
DISK
Name of the disk itself.
PATH
Mount point of the disk.
TOTAL
Total space in KiB.
USED
Used amount of space in KiB.
AVAILABLE
Amount of space in KiB available to non-root users.
Note that as the amount of space available to root (OS user) may be more that what is available to non-root users, 'available' + 'used' may be less than 'total'.
All paths to which a particular disk has been mounted are reported. The rationale is that someone might want to take different action e.g. depending on which disk is relevant for a particular path. This leads to the same disk being reported multiple times.
SELECT * FROM information_schema.DISKS;
+-----------+-------+----------+---------+-----------+
| Disk | Path | Total | Used | Available |
+-----------+-------+----------+---------+-----------+
| /dev/vda1 | / | 26203116 | 2178424 | 24024692 |
| /dev/vda1 | /boot | 26203116 | 2178424 | 24024692 |
| /dev/vda1 | /etc | 26203116 | 2178424 | 24024692 |
+-----------+-------+----------+---------+-----------+Disks Plugin for details on installing, options
Plugin Overview for details on managing plugins.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema ENABLED_ROLES table shows the enabled roles for the current session.
It contains the following column:
ROLE_NAME
The enabled role name, or NULL.
This table lists all roles that are currently enabled, one role per row — the current role, roles granted to the current role, roles granted to these roles and so on. If no role is set, the row contains a NULL value.
The roles that the current user can enable are listed in the APPLICABLE_ROLES Information Schema table.
See also CURRENT_ROLE().
SELECT * FROM information_schema.ENABLED_ROLES;
+-----------+
| ROLE_NAME |
+-----------+
| NULL |
+-----------+
SET ROLE staff;
SELECT * FROM information_schema.ENABLED_ROLES;
+-----------+
| ROLE_NAME |
+-----------+
| staff |
+-----------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema ENGINES table displays status information about the server's storage engines.
It contains the following columns:
ENGINE
Name of the storage engine.
SUPPORT
Whether the engine is the default, or is supported or not.
COMMENT
Storage engine comments.
TRANSACTIONS
Whether or not the engine supports transactions.
XA
Whether or not the engine supports XA transactions.
SAVEPOINTS
Whether or not savepoints are supported.
It provides identical information to the SHOW ENGINES statement. Since storage engines are plugins, different information about them is also shown in the information_schema.PLUGINS table and by the SHOW PLUGINS statement.
The table is not a standard Information Schema table, and is a MySQL and MariaDB extension.
Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See XtraDB and InnoDB. The same applies to FederatedX.
SELECT * FROM information_schema.ENGINES\G;
*************************** 1. row ***************************
ENGINE: InnoDB
SUPPORT: DEFAULT
COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
XA: YES
SAVEPOINTS: YES
*************************** 2. row ***************************
ENGINE: CSV
SUPPORT: YES
COMMENT: CSV storage engine
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 3. row ***************************
ENGINE: MyISAM
SUPPORT: YES
COMMENT: MyISAM storage engine
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 4. row ***************************
ENGINE: BLACKHOLE
SUPPORT: YES
COMMENT: /dev/null storage engine (anything you write to it disappears)
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 5. row ***************************
ENGINE: FEDERATED
SUPPORT: YES
COMMENT: FederatedX pluggable storage engine
TRANSACTIONS: YES
XA: NO
SAVEPOINTS: YES
*************************** 6. row ***************************
ENGINE: MRG_MyISAM
SUPPORT: YES
COMMENT: Collection of identical MyISAM tables
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 7. row ***************************
ENGINE: ARCHIVE
SUPPORT: YES
COMMENT: Archive storage engine
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 8. row ***************************
ENGINE: MEMORY
SUPPORT: YES
COMMENT: Hash based, stored in memory, useful for temporary tables
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 9. row ***************************
ENGINE: PERFORMANCE_SCHEMA
SUPPORT: YES
COMMENT: Performance Schema
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
*************************** 10. row ***************************
ENGINE: Aria
SUPPORT: YES
COMMENT: Crash-safe tables with MyISAM heritage
TRANSACTIONS: NO
XA: NO
SAVEPOINTS: NO
10 rows in set (0.00 sec)Check if a given storage engine is available:
SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE LIKE 'tokudb';
Empty SETCheck which storage engine supports XA transactions:
SELECT ENGINE FROM information_schema.ENGINES WHERE XA = 'YES';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema EVENTS table stores information about Events on the server.
It contains the following columns:
EVENT_CATALOG
Always def.
EVENT_SCHEMA
Database where the event was defined.
EVENT_NAME
Event name.
DEFINER
Event definer.
TIME_ZONE
Time zone used for the event's scheduling and execution, by default SYSTEM.
EVENT_BODY
SQL.
EVENT_DEFINITION
The SQL defining the event.
EVENT_TYPE
Either ONE TIME or RECURRING.
EXECUTE_AT
DATETIME when the event is set to execute, or NULL if recurring.
INTERVAL_VALUE
Numeric interval between event executions for a recurring event, or NULL if not recurring.
INTERVAL_FIELD
Interval unit (e.g., HOUR)
SQL_MODE
The SQL_MODE at the time the event was created.
STARTS
Start DATETIME for a recurring event, NULL if not defined or not recurring.
ENDS
End DATETIME for a recurring event, NULL if not defined or not recurring.
STATUS
One of ENABLED, DISABLED or /SLAVESIDE_DISABLED.
ON_COMPLETION
The ON COMPLETION clause, either PRESERVE or NOT PRESERVE .
CREATED
When the event was created.
LAST_ALTERED
When the event was last changed.
LAST_EXECUTED
When the event was last run.
EVENT_COMMENT
The comment provided in the CREATE EVENT statement, or an empty string if none.
ORIGINATOR
MariaDB server ID on which the event was created.
CHARACTER_SET_CLIENT
character_set_client system variable session value at the time the event was created.
COLLATION_CONNECTION
collation_connection system variable session value at the time the event was created.
DATABASE_COLLATION
Database collation with which the event is linked.
The SHOW EVENTS and SHOW CREATE EVENT statements provide similar information.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema FEEDBACK table is created when the Feedback Plugin is enabled, and contains the complete contents submitted by the plugin.
It contains two columns:
VARIABLE_NAME
Name of the item of information being collected.
VARIABLE_VALUE
Contents of the item of information being collected.
It is possible to disable automatic collection, by setting the feedback_url variable to an empty string, and to submit the contents manually, as follows:
$ mysql -e 'SELECT * FROM information_schema.FEEDBACK' > report.txtThen you can send it by opening post in your browser, and uploading your generated report.txt. Or you can do it from the command line with (for example):
$ curl -F data=@report.txt https://mariadb.org/feedback_plugin/postManual uploading allows you to be absolutely sure that we receive only the data shown in the information_schema.FEEDBACK table and that no private or sensitive information is being sent.
SELECT * FROM information_schema.FEEDBACK\G
...
*************************** 906. row ***************************
VARIABLE_NAME: Uname_sysname
VARIABLE_VALUE: Linux
*************************** 907. row ***************************
VARIABLE_NAME: Uname_release
VARIABLE_VALUE: 3.13.0-53-generic
*************************** 908. row ***************************
VARIABLE_NAME: Uname_version
VARIABLE_VALUE: #89-Ubuntu SMP Wed May 20 10:34:39 UTC 2015
*************************** 909. row ***************************
VARIABLE_NAME: Uname_machine
VARIABLE_VALUE: x86_64
*************************** 910. row ***************************
VARIABLE_NAME: Uname_distribution
VARIABLE_VALUE: lsb: Ubuntu 14.04.2 LTS
*************************** 911. row ***************************
VARIABLE_NAME: Collation used latin1_german1_ci
VARIABLE_VALUE: 1
*************************** 912. row ***************************
VARIABLE_NAME: Collation used latin1_swedish_ci
VARIABLE_VALUE: 18
*************************** 913. row ***************************
VARIABLE_NAME: Collation used utf8_general_ci
VARIABLE_VALUE: 567
*************************** 914. row ***************************
VARIABLE_NAME: Collation used latin1_bin
VARIABLE_VALUE: 1
*************************** 915. row ***************************
VARIABLE_NAME: Collation used binary
VARIABLE_VALUE: 16
*************************** 916. row ***************************
VARIABLE_NAME: Collation used utf8_bin
VARIABLE_VALUE: 4044This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema GEOMETRY_COLUMNS table provides support for Spatial Reference systems for GIS data.
It contains the following columns:
F_TABLE_CATALOG
VARCHAR(512)
NO
Together with F_TABLE_SCHEMA and F_TABLE_NAME, the fully qualified name of the featured table containing the geometry column.
F_TABLE_SCHEMA
VARCHAR(64)
NO
Together with F_TABLE_CATALOG and F_TABLE_NAME, the fully qualified name of the featured table containing the geometry column.
F_TABLE_NAME
VARCHAR(64)
NO
Together with F_TABLE_CATALOG and F_TABLE_SCHEMA, the fully qualified name of the featured table containing the geometry column.
F_GEOMETRY_COLUMN
VARCHAR(64)
NO
Name of the column in the featured table that is the geometry golumn.
G_TABLE_CATALOG
VARCHAR(512)
NO
G_TABLE_SCHEMA
VARCHAR(64)
NO
Database name of the table implementing the geometry column.
G_TABLE_NAME
VARCHAR(64)
NO
Table name that is implementing the geometry column.
G_GEOMETRY_COLUMN
VARCHAR(64)
NO
STORAGE_TYPE
TINYINT(2)
NO
Binary geometry implementation. Always 1 in MariaDB.
GEOMETRY_TYPE
INT(7)
NO
Integer reflecting the type of geometry stored in this column (see table below).
COORD_DIMENSION
TINYINT(2)
NO
Number of dimensions in the spatial reference system. Always 2 in MariaDB.
MAX_PPR
TINYINT(2)
NO
Always 0 in MariaDB.
SRID
SMALLINT(5)
NO
ID of the Spatial Reference System used for the coordinate geometry in this table. It is a foreign key reference to the SPATIAL_REF_SYS table.
The integers in the storage_type field match the geometry types as follows:
0
GEOMETRY
1
POINT
3
LINESTRING
5
POLYGON
7
MULTIPOINT
9
MULTILINESTRING
11
MULTIPOLYGON
CREATE TABLE g1(g GEOMETRY(9,4) REF_SYSTEM_ID=101);
SELECT * FROM information_schema.GEOMETRY_COLUMNS\G
*************************** 1. row ***************************
F_TABLE_CATALOG: def
F_TABLE_SCHEMA: test
F_TABLE_NAME: g1
F_GEOMETRY_COLUMN:
G_TABLE_CATALOG: def
G_TABLE_SCHEMA: test
G_TABLE_NAME: g1
G_GEOMETRY_COLUMN: g
STORAGE_TYPE: 1
GEOMETRY_TYPE: 0
COORD_DIMENSION: 2
MAX_PPR: 0
SRID: 101The SPATIAL_REF_SYS table.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema GLOBAL_STATUS and SESSION_STATUS tables store a record of all status variables and their global and session values respectively. This is the same information as displayed by the SHOW STATUS commands SHOW GLOBAL STATUS and SHOW SESSION STATUS.
They contain the following columns:
VARIABLE_NAME
Status variable name.
VARIABLE_VALUE
Global or session value.
SELECT * FROM information_schema.GLOBAL_STATUS;
+-----------------------------------------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------------------+--------------------+
...
| BINLOG_SNAPSHOT_FILE | mariadb-bin.000208 |
| BINLOG_SNAPSHOT_POSITION | 369 |
...
| THREADS_CONNECTED | 1 |
| THREADS_CREATED | 1 |
| THREADS_RUNNING | 1 |
| UPTIME | 57358 |
| UPTIME_SINCE_FLUSH_STATUS | 57358 |
+-----------------------------------------------+--------------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema GLOBAL_VARIABLES and SESSION_VARIABLES tables stores a record of all system variables and their global and session values respectively. This is the same information as displayed by the SHOW VARIABLES commands SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES.
It contains the following columns:
VARIABLE_NAME
System variable name.
VARIABLE_VALUE
Global or session value.
SELECT * FROM information_schema.GLOBAL_VARIABLES ORDER BY VARIABLE_NAME\G
*************************** 1. row *****************************
VARIABLE_NAME: ARIA_BLOCK_SIZE
VARIABLE_VALUE: 8192
*************************** 2. row *****************************
VARIABLE_NAME: ARIA_CHECKPOINT_LOG_ACTIVITY
VARIABLE_VALUE: 1048576
*************************** 3. row *****************************
VARIABLE_NAME: ARIA_CHECKPOINT_INTERVAL
VARIABLE_VALUE: 30
...
*************************** 455. row ***************************
VARIABLE_NAME: VERSION_COMPILE_MACHINE
VARIABLE_VALUE: x86_64
*************************** 456. row ***************************
VARIABLE_NAME: VERSION_COMPILE_OS
VARIABLE_VALUE: debian-linux-gnu
*************************** 457. row ***************************
VARIABLE_NAME: WAIT_TIMEOUT
VARIABLE_VALUE: 600This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INDEX_STATISTICS table shows statistics on index usage and makes it possible to do such things as locating unused indexes and generating the commands to remove them.
This is part of the User Statistics feature, which is not enabled by default.
It contains the following columns:
TABLE_SCHEMA
VARCHAR(192)
The schema (database) name.
TABLE_NAME
VARCHAR(192)
The table name.
ROWS_READ
BIGINT(21)
The number of rows read from this index.
QUERIES
BIGINT(21)
Incremented for each index the query is part of. This assists one to see how effective the index is. From MariaDB 11.5
SELECT * FROM information_schema.INDEX_STATISTICS
WHERE TABLE_NAME = "author";
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| books | author | by_name | 15 |
+--------------+------------+------------+-----------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema KEY_CACHES table shows statistics about the segmented key cache.
It contains the following columns:
KEY_CACHE_NAME
The name of the key cache
SEGMENTS
total number of segments (set to NULL for regular key caches)
SEGMENT_NUMBER
segment number (set to NULL for any regular key caches and for rows containing aggregation statistics for segmented key caches)
FULL_SIZE
memory for cache buffers/auxiliary structures
BLOCK_SIZE
size of the blocks
USED_BLOCKS
number of currently used blocks
UNUSED_BLOCKS
number of currently unused blocks
DIRTY_BLOCKS
number of currently dirty blocks
READ_REQUESTS
number of read requests
READS
number of actual reads from files into buffers
WRITE_REQUESTS
number of write requests
WRITES
number of actual writes from buffers into files
SELECT * FROM information_schema.KEY_CACHES \G
********************** 1. row **********************
KEY_CACHE_NAME: default
SEGMENTS: NULL
SEGMENT_NUMBER: NULL
FULL_SIZE: 134217728
BLOCK_SIZE: 1024
USED_BLOCKS: 36
UNUSED_BLOCKS: 107146
DIRTY_BLOCKS: 0
READ_REQUESTS: 40305
READS: 21
WRITE_REQUESTS: 19239
WRITES: 358This page is licensed: CC BY-SA / Gnu FDL
The Information Schema KEY_COLUMN_USAGE table shows which key columns have constraints.
It contains the following columns:
CONSTRAINT_CATALOG
Always def.
CONSTRAINT_SCHEMA
Database name of the constraint.
CONSTRAINT_NAME
Name of the constraint (PRIMARY for the primary key).
TABLE_CATALOG
Always #def.
TABLE_SCHEMA
Database name of the column constraint.
TABLE_NAME
Table name of the column constraint.
COLUMN_NAME
Column name of the constraint.
ORDINAL_POSITION
Position of the column within the constraint.
POSITION_IN_UNIQUE_CONSTRAINT
For foreign keys, the position in the unique constraint.
REFERENCED_TABLE_SCHEMA
For foreign keys, the referenced database name.
REFERENCED_TABLE_NAME
For foreign keys, the referenced table name.
REFERENCED_COLUMN_NAME
For foreign keys, the referenced column name.
SELECT * FROM information_schema.KEY_COLUMN_USAGE LIMIT 1 \G
********************** 1. row **********************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: my_website
CONSTRAINT_NAME: PRIMARY
TABLE_CATALOG: def
TABLE_SCHEMA: users
COLUMN_NAME: user_id
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
REFERENCED_TABLE_SCHEMA: NULL
REFERENCED_TABLE_NAME: NULL
REFERENCED_COLUMN_NAME: NULLThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema KEY_PERIOD_USAGE table shows information about Application-Time Periods.
It contains the following columns:
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
PERIOD_NAME
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema KEYWORDS table contains the list of MariaDB keywords.
It contains a single column:
WORD
Keyword
The table is not a standard Information Schema table, but a MariaDB extension.
SELECT * FROM INFORMATION_SCHEMA.KEYWORDS;
+-------------------------------+
| WORD |
+-------------------------------+
| && |
| <= |
| <> |
| != |
| >= |
| << |
| >> |
| <=> |
| ACCESSIBLE |
| ACCOUNT |
| ACTION |
| ADD |
| ADMIN |
| AFTER |
| AGAINST |
| AGGREGATE |
| ALL |
| ALGORITHM |
| ALTER |
| ALWAYS |
| ANALYZE |
| AND |
| ANY |
| AS |
| ASC |
| ASCII |
| ASENSITIVE |
| AT |
| ATOMIC |
| AUTHORS |
| AUTO_INCREMENT |
| AUTOEXTEND_SIZE |
| AUTO |
| AVG |
| AVG_ROW_LENGTH |
| BACKUP |
| BEFORE |
| BEGIN |
| BETWEEN |
| BIGINT |
| BINARY |
| BINLOG |
| BIT |
| BLOB |
| BLOCK |
| BODY |
| BOOL |
| BOOLEAN |
| BOTH |
| BTREE |
| BY |
| BYTE |
| CACHE |
| CALL |
| CASCADE |
| CASCADED |
| CASE |
| CATALOG_NAME |
| CHAIN |
| CHANGE |
| CHANGED |
| CHAR |
| CHARACTER |
| CHARSET |
| CHECK |
| CHECKPOINT |
| CHECKSUM |
| CIPHER |
| CLASS_ORIGIN |
| CLIENT |
| CLOB |
| CLOSE |
| COALESCE |
| CODE |
| COLLATE |
| COLLATION |
| COLUMN |
| COLUMN_NAME |
| COLUMNS |
| COLUMN_ADD |
| COLUMN_CHECK |
| COLUMN_CREATE |
| COLUMN_DELETE |
| COLUMN_GET |
| COMMENT |
| COMMIT |
| COMMITTED |
| COMPACT |
| COMPLETION |
| COMPRESSED |
| CONCURRENT |
| CONDITION |
| CONNECTION |
| CONSISTENT |
| CONSTRAINT |
| CONSTRAINT_CATALOG |
| CONSTRAINT_NAME |
| CONSTRAINT_SCHEMA |
| CONTAINS |
| CONTEXT |
| CONTINUE |
| CONTRIBUTORS |
| CONVERT |
| CPU |
| CREATE |
| CROSS |
| CUBE |
| CURRENT |
| CURRENT_DATE |
| CURRENT_POS |
| CURRENT_ROLE |
| CURRENT_TIME |
| CURRENT_TIMESTAMP |
| CURRENT_USER |
| CURSOR |
| CURSOR_NAME |
| CYCLE |
| DATA |
| DATABASE |
| DATABASES |
| DATAFILE |
| DATE |
| DATETIME |
| DAY |
| DAY_HOUR |
| DAY_MICROSECOND |
| DAY_MINUTE |
| DAY_SECOND |
| DEALLOCATE |
| DEC |
| DECIMAL |
| DECLARE |
| DEFAULT |
| DEFINER |
| DELAYED |
| DELAY_KEY_WRITE |
| DELETE |
| DELETE_DOMAIN_ID |
| DESC |
| DESCRIBE |
| DES_KEY_FILE |
| DETERMINISTIC |
| DIAGNOSTICS |
| DIRECTORY |
| DISABLE |
| DISCARD |
| DISK |
| DISTINCT |
| DISTINCTROW |
| DIV |
| DO |
| DOUBLE |
| DO_DOMAIN_IDS |
| DROP |
| DUAL |
| DUMPFILE |
| DUPLICATE |
| DYNAMIC |
| EACH |
| ELSE |
| ELSEIF |
| ELSIF |
| EMPTY |
| ENABLE |
| ENCLOSED |
| END |
| ENDS |
| ENGINE |
| ENGINES |
| ENUM |
| ERROR |
| ERRORS |
| ESCAPE |
| ESCAPED |
| EVENT |
| EVENTS |
| EVERY |
| EXAMINED |
| EXCEPT |
| EXCHANGE |
| EXCLUDE |
| EXECUTE |
| EXCEPTION |
| EXISTS |
| EXIT |
| EXPANSION |
| EXPIRE |
| EXPORT |
| EXPLAIN |
| EXTENDED |
| EXTENT_SIZE |
| FALSE |
| FAST |
| FAULTS |
| FEDERATED |
| FETCH |
| FIELDS |
| FILE |
| FIRST |
| FIXED |
| FLOAT |
| FLOAT4 |
| FLOAT8 |
| FLUSH |
| FOLLOWING |
| FOLLOWS |
| FOR |
| FORCE |
| FOREIGN |
| FORMAT |
| FOUND |
| FROM |
| FULL |
| FULLTEXT |
| FUNCTION |
| GENERAL |
| GENERATED |
| GET_FORMAT |
| GET |
| GLOBAL |
| GOTO |
| GRANT |
| GRANTS |
| GROUP |
| HANDLER |
| HARD |
| HASH |
| HAVING |
| HELP |
| HIGH_PRIORITY |
| HISTORY |
| HOST |
| HOSTS |
| HOUR |
| HOUR_MICROSECOND |
| HOUR_MINUTE |
| HOUR_SECOND |
| ID |
| IDENTIFIED |
| IF |
| IGNORE |
| IGNORED |
| IGNORE_DOMAIN_IDS |
| IGNORE_SERVER_IDS |
| IMMEDIATE |
| IMPORT |
| INTERSECT |
| IN |
| INCREMENT |
| INDEX |
| INDEXES |
| INFILE |
| INITIAL_SIZE |
| INNER |
| INOUT |
| INSENSITIVE |
| INSERT |
| INSERT_METHOD |
| INSTALL |
| INT |
| INT1 |
| INT2 |
| INT3 |
| INT4 |
| INT8 |
| INTEGER |
| INTERVAL |
| INVISIBLE |
| INTO |
| IO |
| IO_THREAD |
| IPC |
| IS |
| ISOLATION |
| ISOPEN |
| ISSUER |
| ITERATE |
| INVOKER |
| JOIN |
| JSON |
| JSON_TABLE |
| KEY |
| KEYS |
| KEY_BLOCK_SIZE |
| KILL |
| LANGUAGE |
| LAST |
| LAST_VALUE |
| LASTVAL |
| LEADING |
| LEAVE |
| LEAVES |
| LEFT |
| LESS |
| LEVEL |
| LIKE |
| LIMIT |
| LINEAR |
| LINES |
| LIST |
| LOAD |
| LOCAL |
| LOCALTIME |
| LOCALTIMESTAMP |
| LOCK |
| LOCKED |
| LOCKS |
| LOGFILE |
| LOGS |
| LONG |
| LONGBLOB |
| LONGTEXT |
| LOOP |
| LOW_PRIORITY |
| MASTER |
| MASTER_CONNECT_RETRY |
| MASTER_DELAY |
| MASTER_GTID_POS |
| MASTER_HOST |
| MASTER_LOG_FILE |
| MASTER_LOG_POS |
| MASTER_PASSWORD |
| MASTER_PORT |
| MASTER_SERVER_ID |
| MASTER_SSL |
| MASTER_SSL_CA |
| MASTER_SSL_CAPATH |
| MASTER_SSL_CERT |
| MASTER_SSL_CIPHER |
| MASTER_SSL_CRL |
| MASTER_SSL_CRLPATH |
| MASTER_SSL_KEY |
| MASTER_SSL_VERIFY_SERVER_CERT |
| MASTER_USER |
| MASTER_USE_GTID |
| MASTER_HEARTBEAT_PERIOD |
| MATCH |
| MAX_CONNECTIONS_PER_HOUR |
| MAX_QUERIES_PER_HOUR |
| MAX_ROWS |
| MAX_SIZE |
| MAX_STATEMENT_TIME |
| MAX_UPDATES_PER_HOUR |
| MAX_USER_CONNECTIONS |
| MAXVALUE |
| MEDIUM |
| MEDIUMBLOB |
| MEDIUMINT |
| MEDIUMTEXT |
| MEMORY |
| MERGE |
| MESSAGE_TEXT |
| MICROSECOND |
| MIDDLEINT |
| MIGRATE |
| MINUS |
| MINUTE |
| MINUTE_MICROSECOND |
| MINUTE_SECOND |
| MINVALUE |
| MIN_ROWS |
| MOD |
| MODE |
| MODIFIES |
| MODIFY |
| MONITOR |
| MONTH |
| MUTEX |
| MYSQL |
| MYSQL_ERRNO |
| NAME |
| NAMES |
| NATIONAL |
| NATURAL |
| NCHAR |
| NESTED |
| NEVER |
| NEW |
| NEXT |
| NEXTVAL |
| NO |
| NOMAXVALUE |
| NOMINVALUE |
| NOCACHE |
| NOCYCLE |
| NO_WAIT |
| NOWAIT |
| NODEGROUP |
| NONE |
| NOT |
| NOTFOUND |
| NO_WRITE_TO_BINLOG |
| NULL |
| NUMBER |
| NUMERIC |
| NVARCHAR |
| OF |
| OFFSET |
| OLD_PASSWORD |
| ON |
| ONE |
| ONLINE |
| ONLY |
| OPEN |
| OPTIMIZE |
| OPTIONS |
| OPTION |
| OPTIONALLY |
| OR |
| ORDER |
| ORDINALITY |
| OTHERS |
| OUT |
| OUTER |
| OUTFILE |
| OVER |
| OVERLAPS |
| OWNER |
| PACKAGE |
| PACK_KEYS |
| PAGE |
| PAGE_CHECKSUM |
| PARSER |
| PARSE_VCOL_EXPR |
| PATH |
| PERIOD |
| PARTIAL |
| PARTITION |
| PARTITIONING |
| PARTITIONS |
| PASSWORD |
| PERSISTENT |
| PHASE |
| PLUGIN |
| PLUGINS |
| PORT |
| PORTION |
| PRECEDES |
| PRECEDING |
| PRECISION |
| PREPARE |
| PRESERVE |
| PREV |
| PREVIOUS |
| PRIMARY |
| PRIVILEGES |
| PROCEDURE |
| PROCESS |
| PROCESSLIST |
| PROFILE |
| PROFILES |
| PROXY |
| PURGE |
| QUARTER |
| QUERY |
| QUICK |
| RAISE |
| RANGE |
| RAW |
| READ |
| READ_ONLY |
| READ_WRITE |
| READS |
| REAL |
| REBUILD |
| RECOVER |
| RECURSIVE |
| REDO_BUFFER_SIZE |
| REDOFILE |
| REDUNDANT |
| REFERENCES |
| REGEXP |
| RELAY |
| RELAYLOG |
| RELAY_LOG_FILE |
| RELAY_LOG_POS |
| RELAY_THREAD |
| RELEASE |
| RELOAD |
| REMOVE |
| RENAME |
| REORGANIZE |
| REPAIR |
| REPEATABLE |
| REPLACE |
| REPLAY |
| REPLICA |
| REPLICAS |
| REPLICA_POS |
| REPLICATION |
| REPEAT |
| REQUIRE |
| RESET |
| RESIGNAL |
| RESTART |
| RESTORE |
| RESTRICT |
| RESUME |
| RETURNED_SQLSTATE |
| RETURN |
| RETURNING |
| RETURNS |
| REUSE |
| REVERSE |
| REVOKE |
| RIGHT |
| RLIKE |
| ROLE |
| ROLLBACK |
| ROLLUP |
| ROUTINE |
| ROW |
| ROWCOUNT |
| ROWNUM |
| ROWS |
| ROWTYPE |
| ROW_COUNT |
| ROW_FORMAT |
| RTREE |
| SAVEPOINT |
| SCHEDULE |
| SCHEMA |
| SCHEMA_NAME |
| SCHEMAS |
| SECOND |
| SECOND_MICROSECOND |
| SECURITY |
| SELECT |
| SENSITIVE |
| SEPARATOR |
| SEQUENCE |
| SERIAL |
| SERIALIZABLE |
| SESSION |
| SERVER |
| SET |
| SETVAL |
| SHARE |
| SHOW |
| SHUTDOWN |
| SIGNAL |
| SIGNED |
| SIMPLE |
| SKIP |
| SLAVE |
| SLAVES |
| SLAVE_POS |
| SLOW |
| SNAPSHOT |
| SMALLINT |
| SOCKET |
| SOFT |
| SOME |
| SONAME |
| SOUNDS |
| SOURCE |
| STAGE |
| STORED |
| SPATIAL |
| SPECIFIC |
| REF_SYSTEM_ID |
| SQL |
| SQLEXCEPTION |
| SQLSTATE |
| SQLWARNING |
| SQL_BIG_RESULT |
| SQL_BUFFER_RESULT |
| SQL_CACHE |
| SQL_CALC_FOUND_ROWS |
| SQL_NO_CACHE |
| SQL_SMALL_RESULT |
| SQL_THREAD |
| SQL_TSI_SECOND |
| SQL_TSI_MINUTE |
| SQL_TSI_HOUR |
| SQL_TSI_DAY |
| SQL_TSI_WEEK |
| SQL_TSI_MONTH |
| SQL_TSI_QUARTER |
| SQL_TSI_YEAR |
| SSL |
| START |
| STARTING |
| STARTS |
| STATEMENT |
| STATS_AUTO_RECALC |
| STATS_PERSISTENT |
| STATS_SAMPLE_PAGES |
| STATUS |
| STOP |
| STORAGE |
| STRAIGHT_JOIN |
| STRING |
| SUBCLASS_ORIGIN |
| SUBJECT |
| SUBPARTITION |
| SUBPARTITIONS |
| SUPER |
| SUSPEND |
| SWAPS |
| SWITCHES |
| SYSDATE |
| SYSTEM |
| SYSTEM_TIME |
| TABLE |
| TABLE_NAME |
| TABLES |
| TABLESPACE |
| TABLE_CHECKSUM |
| TEMPORARY |
| TEMPTABLE |
| TERMINATED |
| TEXT |
| THAN |
| THEN |
| TIES |
| TIME |
| TIMESTAMP |
| TIMESTAMPADD |
| TIMESTAMPDIFF |
| TINYBLOB |
| TINYINT |
| TINYTEXT |
| TO |
| TRAILING |
| TRANSACTION |
| TRANSACTIONAL |
| THREADS |
| TRIGGER |
| TRIGGERS |
| TRUE |
| TRUNCATE |
| TYPE |
| TYPES |
| UNBOUNDED |
| UNCOMMITTED |
| UNDEFINED |
| UNDO_BUFFER_SIZE |
| UNDOFILE |
| UNDO |
| UNICODE |
| UNION |
| UNIQUE |
| UNKNOWN |
| UNLOCK |
| UNINSTALL |
| UNSIGNED |
| UNTIL |
| UPDATE |
| UPGRADE |
| USAGE |
| USE |
| USER |
| USER_RESOURCES |
| USE_FRM |
| USING |
| UTC_DATE |
| UTC_TIME |
| UTC_TIMESTAMP |
| VALUE |
| VALUES |
| VARBINARY |
| VARCHAR |
| VARCHARACTER |
| VARCHAR2 |
| VARIABLES |
| VARYING |
| VIA |
| VIEW |
| VIRTUAL |
| VISIBLE |
| VERSIONING |
| WAIT |
| WARNINGS |
| WEEK |
| WEIGHT_STRING |
| WHEN |
| WHERE |
| WHILE |
| WINDOW |
| WITH |
| WITHIN |
| WITHOUT |
| WORK |
| WRAPPER |
| WRITE |
| X509 |
| XOR |
| XA |
| XML |
| YEAR |
| YEAR_MONTH |
| ZEROFILL |
| || |
+-------------------------------+
694 rows in set (0.000 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema LOCALES table contains a list of all compiled-in locales. It is only available if the LOCALES plugin has been installed.
It contains the following columns:
ID
Row ID.
NAME
Locale name, for example en_GB.
DESCRIPTION
Locale description, for example English - United Kingdom.
MAX_MONTH_NAME_LENGTH
Numeric length of the longest month in the locale
MAX_DAY_NAME_LENGTH
Numeric length of the longest day name in the locale.
DECIMAL_POINT
Decimal point character (some locales use a comma).
THOUSAND_SEP
Thousand's character separator,
ERROR_MESSAGE_LANGUAGE
Error message language.
The table is not a standard Information Schema table, and is a MariaDB extension.
The SHOW LOCALES statement returns a subset of the information.
SELECT * FROM information_schema.LOCALES;
+-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+
| ID | NAME | DESCRIPTION | MAX_MONTH_NAME_LENGTH | MAX_DAY_NAME_LENGTH | DECIMAL_POINT | THOUSAND_SEP | ERROR_MESSAGE_LANGUAGE |
+-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+
| 0 | en_US | English - United States | 9 | 9 | . | , | english |
| 1 | en_GB | English - United Kingdom | 9 | 9 | . | , | english |
| 2 | ja_JP | Japanese - Japan | 3 | 3 | . | , | japanese |
| 3 | sv_SE | Swedish - Sweden | 9 | 7 | , | | swedish |
| 4 | de_DE | German - Germany | 9 | 10 | , | . | german |
| 5 | fr_FR | French - France | 9 | 8 | , | | french |
| 6 | ar_AE | Arabic - United Arab Emirates | 6 | 8 | . | , | english |
| 7 | ar_BH | Arabic - Bahrain | 6 | 8 | . | , | english |
| 8 | ar_JO | Arabic - Jordan | 12 | 8 | . | , | english |
...
| 106 | no_NO | Norwegian - Norway | 9 | 7 | , | . | norwegian |
| 107 | sv_FI | Swedish - Finland | 9 | 7 | , | | swedish |
| 108 | zh_HK | Chinese - Hong Kong SAR | 3 | 3 | . | , | english |
| 109 | el_GR | Greek - Greece | 11 | 9 | , | . | greek |
+-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema METADATA_LOCK_INFO table is created by the metadata_lock_info plugin. It shows active metadata locks and user locks (the locks acquired with GET_LOCK).
It has the following columns:
THREAD_ID
LOCK_MODE
One of MDL_INTENTION_EXCLUSIVE, MDL_SHARED, MDL_SHARED_HIGH_PRIO, MDL_SHARED_READ, MDL_SHARED_READ_ONLY, MDL_SHARED_WRITE, MDL_SHARED_NO_WRITE, MDL_SHARED_NO_READ_WRITE, MDL_SHARED_UPGRADABLE or MDL_EXCLUSIVE.
LOCK_DURATION
One of MDL_STATEMENT, MDL_TRANSACTION or MDL_EXPLICIT
LOCK_TYPE
One of Global read lock, Schema metadata lock, Table metadata lock, Stored function metadata lock, Stored procedure metadata lock, Trigger metadata lock, Event metadata lock, Commit lock or User lock.
TABLE_SCHEMA
TABLE_NAME
The LOCK_MODE column can have the following values:
MDL_INTENTION_EXCLUSIVE
An intention exclusive metadata lock (IX). Used only for scoped locks. Owner of this type of lock can acquire upgradable exclusive locks on individual objects. Compatible with other IX locks, but is incompatible with scoped S and X locks. IX lock is taken in SCHEMA namespace when we intend to modify object metadata. Object may refer table, stored procedure, trigger, view/etc.
MDL_SHARED
A shared metadata lock (S). To be used in cases when we are interested in object metadata only and there is no intention to access object data (e.g. for stored routines or during preparing prepared statements). We also mis-use this type of lock for open HANDLERs, since lock acquired by this statement has to be compatible with lock acquired by LOCK TABLES ... WRITE statement, i.e. SNRW (We can't get by acquiring S lock at HANDLER ... OPEN time and upgrading it to SR lock for HANDLER ... READ as it doesn't solve problem with need to abort DML statements which wait on table level lock while having open HANDLER in the same connection). To avoid deadlock which may occur when SNRW lock is being upgraded to X lock for table on which there is an active S lock which is owned by thread which waits in its turn for table-level lock owned by thread performing upgrade we have to use thr_abort_locks_for_thread() facility in such situation. This problem does not arise for locks on stored routines as we don't use SNRW locks for them. It also does not arise when S locks are used during PREPARE calls as table-level locks are not acquired in this case. This lock is taken for global read lock, when caching a stored procedure in memory for the duration of the transaction and for tables used by prepared statements.
MDL_SHARED_HIGH_PRIO
A high priority shared metadata lock. Used for cases when there is no intention to access object data (i.e. data in the table). "High priority" means that, unlike other shared locks, it is granted ignoring pending requests for exclusive locks. Intended for use in cases when we only need to access metadata and not data, e.g. when filling an INFORMATION_SCHEMA table. Since SH lock is compatible with SNRW lock, the connection that holds SH lock should not try to acquire any kind of table-level or row-level lock, as this can lead to a deadlock. Moreover, after acquiring SH lock, the connection should not wait for any other resource, as it might cause starvation for X locks and a potential deadlock during upgrade of SNW or SNRW to X lock (e.g. if the upgrading connection holds the resource that is being waited for).
MDL_SHARED_READ
A shared metadata lock (SR) for cases when there is an intention to read data from table. A connection holding this kind of lock can read table metadata and read table data (after acquiring appropriate table and row-level locks). This means that one can only acquire TL_READ, TL_READ_NO_INSERT, and similar table-level locks on table if one holds SR MDL lock on it. To be used for tables in SELECTs, subqueries, and LOCK TABLE ... READ statements.
MDL_SHARED_WRITE
A shared metadata lock (SW) for cases when there is an intention to modify (and not just read) data in the table. A connection holding SW lock can read table metadata and modify or read table data (after acquiring appropriate table and row-level locks). To be used for tables to be modified by INSERT, UPDATE, DELETE statements, but not LOCK TABLE ... WRITE or DDL). Also taken by SELECT ... FOR UPDATE.
MDL_SHARED_UPGRADABLE
An upgradable shared metadata lock for cases when there is an intention to modify (and not just read) data in the table. Can be upgraded to MDL_SHARED_NO_WRITE and MDL_EXCLUSIVE. A connection holding SU lock can read table metadata and modify or read table data (after acquiring appropriate table and row-level locks). To be used for the first phase of ALTER TABLE.
MDL_SHARED_READ_ONLY
A shared metadata lock for cases when we need to read data from table and block all concurrent modifications to it (for both data and metadata). Used by LOCK TABLES READ statement.
MDL_SHARED_NO_WRITE
An upgradable shared metadata lock which blocks all attempts to update table data, allowing reads. A connection holding this kind of lock can read table metadata and read table data. Can be upgraded to X metadata lock. Note, that since this type of lock is not compatible with SNRW or SW lock types, acquiring appropriate engine-level locks for reading (TL_READ* for MyISAM, shared row locks in InnoDB) should be contention-free. To be used for the first phase of ALTER TABLE, when copying data between tables, to allow concurrent SELECTs from the table, but not UPDATEs.
MDL_SHARED_NO_READ_WRITE
An upgradable shared metadata lock which allows other connections to access table metadata, but not data. It blocks all attempts to read or update table data, while allowing INFORMATION_SCHEMA and SHOW queries. A connection holding this kind of lock can read table metadata modify and read table data. Can be upgraded to X metadata lock. To be used for LOCK TABLES WRITE statement. Not compatible with any other lock type except S and SH.
MDL_EXCLUSIVE
An exclusive metadata lock (X). A connection holding this lock can modify both table's metadata and data. No other type of metadata lock can be granted while this lock is held. To be used for CREATE/DROP/RENAME TABLE statements and for execution of certain phases of other DDL statements.
First, install the metadata _lock_info plugin, if it is not already installed:
install plugin if not exists METADATA_LOCK_INFO soname "metadata_lock_info.so";User lock:
SELECT GET_LOCK('abc',1000);
+----------------------+
| GET_LOCK('abc',1000) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM information_schema.METADATA_LOCK_INFO;
+-----------+--------------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+--------------------------+---------------+-----------+--------------+------------+
| 61 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT | User lock | abc | |
+-----------+--------------------------+---------------+-----------+--------------+------------+Table metadata lock:
START TRANSACTION;
INSERT INTO t VALUES (1,2);
SELECT * FROM information_schema.METADATA_LOCK_INFO \G
*************************** 1. row ***************************
THREAD_ID: 4
LOCK_MODE: MDL_SHARED_WRITE
LOCK_DURATION: MDL_TRANSACTION
LOCK_TYPE: Table metadata lock
TABLE_SCHEMA: test
TABLE_NAME: tSELECT * FROM information_schema.METADATA_LOCK_INFO;
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
| 31 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
| 31 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Commit lock | | |
| 31 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | dbname | |
| 31 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT | Table metadata lock | dbname | exotics |
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema MROONGA_STATS table only exists if the Mroonga storage engine is installed, and contains information about its activities.
VERSION
Mroonga version.
rows_written
Number of rows written into Mroonga tables.
rows_read
Number of rows read from all Mroonga tables.
This table always contains 1 row.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema OPTIMIZER_TRACE table contains Optimizer Trace information.
It contains the following columns:
QUERY
Displays the query that was asked to be traced.
TRACE
A JSON document displaying the stats we collected when the query was run.
MISSING_BYTES_BEYOND_MAX_MEM_SIZE
For huge trace, where the trace is truncated due to the optimizer_trace_max_mem_size limit being reached, displays the bytes that are missing in the trace
INSUFFICENT_PRIVILEGES
Set to 1 if the user running the trace does not have the privileges to see the trace.
Structure:
SHOW CREATE TABLE INFORMATION_SCHEMA.OPTIMIZER_TRACE \G
*************************** 1. row ***************************
Table: OPTIMIZER_TRACE
Create Table: CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
`QUERY` longtext NOT NULL DEFAULT '',
`TRACE` longtext NOT NULL DEFAULT '',
`MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT 0,
`INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=0This page is licensed: CC BY-SA / Gnu FDL
The Information Schema PARAMETERS table stores information about stored procedures and stored functions parameters.
It contains the following columns:
SPECIFIC_CATALOG
Always def.
SPECIFIC_SCHEMA
Database name containing the stored routine parameter.
SPECIFIC_NAME
Stored routine name.
ORDINAL_POSITION
Ordinal position of the parameter, starting at 1. 0 for a function RETURNS clause.
PARAMETER_MODE
One of IN, OUT, INOUT or NULL for RETURNS.
PARAMETER_NAME
Name of the parameter, or NULL for RETURNS.
DATA_TYPE
The column's data type.
CHARACTER_MAXIMUM_LENGTH
Maximum length.
CHARACTER_OCTET_LENGTH
Same as the CHARACTER_MAXIMUM_LENGTH except for multi-byte character sets.
NUMERIC_PRECISION
For numeric types, the precision (number of significant digits) for the column. NULL if not a numeric field.
NUMERIC_SCALE
For numeric types, the scale (significant digits to the right of the decimal point). NULL if not a numeric field.
DATETIME_PRECISION
Fractional-seconds precision, or NULL if not a time data type.
CHARACTER_SET_NAME
Character set if a non-binary string data type, otherwise NULL.
COLLATION_NAME
Collation if a non-binary string data type, otherwise NULL.
DTD_IDENTIFIER
Description of the data type.
ROUTINE_TYPE
PROCEDURE or FUNCTION.
Information from this table is similar to that found in the param_list column in the mysql.proc table, and the output of the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.
To obtain information about the routine itself, you can query the Information Schema ROUTINES table.
SELECT * FROM information_schema.PARAMETERS
LIMIT 1 \G
********************** 1. row **********************
SPECIFIC_CATALOG: def
SPECIFIC_SCHEMA: accounts
SPECIFIC_NAME: user_counts
ORDINAL_POSITION: 1
PARAMETER_MODE: IN
PARAMETER_NAME: user_order
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 255
CHARACTER_OCTET_LENGTH: 765
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
DTD_IDENTIFIER: varchar(255)
ROUTINE_TYPE: PROCEDUREThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema PARTITIONS contains information about table partitions, with each record corresponding to a single partition or subpartition of a partitioned table. Each non-partitioned table also has a record in the PARTITIONS table, but most of the values are NULL.
It contains the following columns:
TABLE_CATALOG
Always def.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name containing the partition.
PARTITION_NAME
Partition name.
SUBPARTITION_NAME
Subpartition name, or NULL if not a subpartition.
PARTITION_ORDINAL_POSITION
Order of the partition starting from 1.
SUBPARTITION_ORDINAL_POSITION
Order of the subpartition starting from 1.
PARTITION_METHOD
The partitioning type; one of RANGE, LIST, HASH, LINEAR HASH, KEY or LINEAR KEY.
SUBPARTITION_METHOD
Subpartition type; one of HASH, LINEAR HASH, KEY or LINEAR KEY, or NULL if not a subpartition.
PARTITION_EXPRESSION
Expression used to create the partition by the CREATE TABLE or ALTER TABLE statement.
SUBPARTITION_EXPRESSION
Expression used to create the subpartition by the CREATE TABLE or ALTER TABLE statement, or NULL if not a subpartition.
PARTITION_DESCRIPTION
For a RANGE partition, contains either MAXINTEGER or an integer, as set in the VALUES LESS THAN clause. For a LIST partition, contains a comma-separated list of integers, as set in the VALUES IN. For a SYSTEM_TIME INTERVAL partition, shows a defined upper boundary timestamp for historical values (the last history partition can contain values above the upper boundary). NULL if another type of partition.
TABLE_ROWS
Number of rows in the table (may be an estimate for some storage engines).
AVG_ROW_LENGTH
Average row length, that is DATA_LENGTH divided by TABLE_ROWS
DATA_LENGTH
Total number of bytes stored in all rows of the partition.
MAX_DATA_LENGTH
Maximum bytes that could be stored in the partition.
INDEX_LENGTH
Size in bytes of the partition index file.
DATA_FREE
Unused bytes allocated to the partition.
CREATE_TIME
Time the partition was created
UPDATE_TIME
Time the partition was last modified.
CHECK_TIME
Time the partition was last checked, or NULL for storage engines that don't record this information.
CHECKSUM
Checksum value, or NULL if none.
PARTITION_COMMENT
Partition comment, truncated to 80 characters, or an empty string if no comment.
NODEGROUP
Node group, only used for MySQL Cluster, defaults to 0.
TABLESPACE_NAME
Always default.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema PERIODS table provides information about Application-Time Periods.
It contains the following columns:
TABLE_CATALOG
Always contains the string 'def'.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
PERIOD
Period name.
START_COLUMN_NAME
Name of the column that starts the period.
END_COLUMN_NAME
Name of the column that ends the period.
CREATE OR REPLACE TABLE t1(
name VARCHAR(50),
date_1 DATE,
date_2 DATE,
PERIOD FOR date_period(date_1, date_2)
);
SELECT * FROM INFORMATION_SCHEMA.PERIODS;
+---------------+--------------+------------+-------------+-------------------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PERIOD | START_COLUMN_NAME | END_COLUMN_NAME |
+---------------+--------------+------------+-------------+-------------------+-----------------+
| def | test | t1 | date_period | date_1 | date_2 |
+---------------+--------------+------------+-------------+-------------------+-----------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema PLUGINS table contains information about server plugins.
It contains the following columns:
PLUGIN_NAME
Name of the plugin.
PLUGIN_VERSION
Version from the plugin's general type descriptor.
PLUGIN_STATUS
Plugin status, one of ACTIVE, INACTIVE, DISABLED or DELETED.
PLUGIN_TYPE
Plugin type; STORAGE ENGINE, INFORMATION_SCHEMA, AUTHENTICATION, REPLICATION, DAEMON or AUDIT.
PLUGIN_TYPE_VERSION
Version from the plugin's type-specific descriptor.
PLUGIN_LIBRARY
Plugin's shared object file name, located in the directory specified by the plugin_dir system variable, and used by the INSTALL PLUGIN and UNINSTALL PLUGIN statements. NULL if the plugin is complied in and cannot be uninstalled.
PLUGIN_LIBRARY_VERSION
Version from the plugin's API interface.
PLUGIN_AUTHOR
Author of the plugin.
PLUGIN_DESCRIPTION
Description.
PLUGIN_LICENSE
Plugin's licence.
LOAD_OPTION
How the plugin was loaded; one of OFF, ON, FORCE or FORCE_PLUS_PERMANENT. See Installing Plugins.
PLUGIN_MATURITY
Plugin's maturity level; one of Unknown, Experimental, Alpha, Beta,'Gamma, and Stable.
PLUGIN_AUTH_VERSION
Plugin's version as determined by the plugin author. An example would be '0.99 beta 1'.
It provides a superset of the information shown by the SHOW PLUGINS statement. For specific information about storage engines (a particular type of plugins), see the information_schema.ENGINES table and the SHOW ENGINES statement.
This table provides a subset of the Information Schema information_schema.ALL_PLUGINS table, which contains all available plugins, installed or not.
The table is not a standard Information Schema table, and is a MariaDB extension.
The easiest way to get basic information on plugins is with SHOW PLUGINS:
SHOW PLUGINS;
+----------------------------+----------+--------------------+-------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INSERTED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SPHINX | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |
+----------------------------+----------+--------------------+-------------+---------+SELECT LOAD_OPTION
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'tokudb';
Empty SETThe equivalent SELECT query would be:
SELECT PLUGIN_NAME, PLUGIN_STATUS,
PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_LICENSE
FROM INFORMATION_SCHEMA.PLUGINS;Other SELECT queries can be used to see additional information. For example:
SELECT PLUGIN_NAME, PLUGIN_DESCRIPTION,
PLUGIN_MATURITY, PLUGIN_AUTH_VERSION
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_TYPE='STORAGE ENGINE'
ORDER BY PLUGIN_MATURITY \G
*************************** 1. row ***************************
PLUGIN_NAME: FEDERATED
PLUGIN_DESCRIPTION: FederatedX pluggable storage engine
PLUGIN_MATURITY: Beta
PLUGIN_AUTH_VERSION: 2.1
*************************** 2. row ***************************
PLUGIN_NAME: Aria
PLUGIN_DESCRIPTION: Crash-safe tables with MyISAM heritage
PLUGIN_MATURITY: Gamma
PLUGIN_AUTH_VERSION: 1.5
*************************** 3. row ***************************
PLUGIN_NAME: PERFORMANCE_SCHEMA
PLUGIN_DESCRIPTION: Performance Schema
PLUGIN_MATURITY: Gamma
PLUGIN_AUTH_VERSION: 0.1
*************************** 4. row ***************************
PLUGIN_NAME: binlog
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 5. row ***************************
PLUGIN_NAME: MEMORY
PLUGIN_DESCRIPTION: Hash based, stored in memory, useful for temporary tables
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 6. row ***************************
PLUGIN_NAME: MyISAM
PLUGIN_DESCRIPTION: MyISAM storage engine
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 7. row ***************************
PLUGIN_NAME: MRG_MyISAM
PLUGIN_DESCRIPTION: Collection of identical MyISAM tables
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 8. row ***************************
PLUGIN_NAME: CSV
PLUGIN_DESCRIPTION: CSV storage engine
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 9. row ***************************
PLUGIN_NAME: InnoDB
PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.2.5
*************************** 10. row ***************************
PLUGIN_NAME: BLACKHOLE
PLUGIN_DESCRIPTION: /dev/null storage engine (anything you write to it disappears)
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 11. row ***************************
PLUGIN_NAME: ARCHIVE
PLUGIN_DESCRIPTION: Archive storage engine
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
*************************** 12. row ***************************
PLUGIN_NAME: partition
PLUGIN_DESCRIPTION: Partition Storage Engine Helper
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0Check if a given plugin is available:
SELECT LOAD_OPTION
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'tokudb';
Empty SETShow authentication plugins:
SELECT PLUGIN_NAME, LOAD_OPTION
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_TYPE LIKE 'authentication' \G
*************************** 1. row ***************************
PLUGIN_NAME: mysql_native_password
LOAD_OPTION: FORCE
*************************** 2. row ***************************
PLUGIN_NAME: mysql_old_password
LOAD_OPTION: FORCEThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema PROCESSLIST table contains information about running threads.
Similar information can also be returned with the SHOW [FULL] PROCESSLIST statement, or the mariadb-admin processlist command.
It contains the following columns:
ID
Connection identifier.
USER
MariaDB User.
HOST
The hostname from which this thread is connected.For Unix socket connections, localhost. For TCP/IP connections, the TCP port is appended (e.g. 192.168.1.17:58061 or other-host.company.com:58061). For system user, this column is blank ('').
DB
Default database, or NULL if none.
COMMAND
Type of command running, corresponding to the Com_ status variables. See Thread Command Values.
TIME
Seconds that the thread has spent on the current COMMAND so far.
STATE
Current state of the thread. See Thread States.
INFO
Statement the thread is executing, or NULL if none.
TIME_MS
Time in milliseconds with microsecond precision that the thread has spent on the current COMMAND so far (see more).
STAGE
The stage the process is currently in.
MAX_STAGE
The maximum number of stages.
PROGRESS
The progress of the process within the current stage (0-100%).
MEMORY_USED
Memory in bytes used by the thread.
MAX_MEMORY_USED
Maximum memory in bytes used by the thread.
EXAMINED_ROWS
Rows examined by the thread. Only updated by UPDATE, DELETE, and similar statements. For SELECT and other statements, the value remains zero.
SENT_ROWS
Number of rows sent by the statement being executed. From MariaDB 11.3.0.
QUERY_ID
Query ID.
INFO_BINARY
Binary data information
TID
Thread ID (MDEV-6756)
TMP_SPACE_USED
Note that as a difference to MySQL, in MariaDB the TIMEcolumn (and also the TIME_MS column) are not affected by any setting of @TIMESTAMP. This means that it can be reliably used also for threads that change @TIMESTAMP (such as the replication SQL thread). See also MySQL Bug #22047.
As a consequence of this, the TIME column ofSHOW FULL PROCESSLIST andINFORMATION_SCHEMA.PROCESSLIST can not be used to determine if a slave is lagging behind. For this, use instead theSeconds_Behind_Master column in the output ofSHOW SLAVE STATUS.
Note that the PROGRESS field from the information schema, and the PROGRESS field from SHOW PROCESSLIST display different results. SHOW PROCESSLIST shows the total progress, while the information schema shows the progress for the current stage only.. To retrieve a similar "total" Progress value from information_schema.PROCESSLIST as the one from SHOW PROCESSLIST, use
SELECT CASE WHEN Max_Stage < 2 THEN Progress ELSE (Stage-1)/Max_Stage*100+Progress/Max_Stage END
AS Progress FROM INFORMATION_SCHEMA.PROCESSLIST;SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST\G
*************************** 1. row ***************************
ID: 9
USER: msandbox
HOST: localhost
DB: NULL
COMMAND: Query
TIME: 0
STATE: Filling schema table
INFO: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
TIME_MS: 0.351
STAGE: 0
MAX_STAGE: 0
PROGRESS: 0.000
MEMORY_USED: 85392
EXAMINED_ROWS: 0
QUERY_ID: 15
INFO_BINARY: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
TID: 11838
*************************** 2. row ***************************
ID: 5
USER: system user
HOST:
DB: NULL
COMMAND: Daemon
TIME: 0
STATE: InnoDB shutdown handler
INFO: NULL
TIME_MS: 0.000
STAGE: 0
MAX_STAGE: 0
PROGRESS: 0.000
MEMORY_USED: 24160
EXAMINED_ROWS: 0
QUERY_ID: 0
INFO_BINARY: NULL
TID: 3856
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema PROFILING table contains information about statement resource usage. Profiling information is only recorded if the profiling session variable is set to 1.
It contains the following columns:
QUERY_ID
Query_ID.
SEQ
Sequence number showing the display order for rows with the same QUERY_ID.
STATE
Profiling state.
DURATION
Time in seconds that the statement has been in the current state.
CPU_USER
User CPU usage in seconds.
CPU_SYSTEM
System CPU usage in seconds.
CONTEXT_VOLUNTARY
Number of voluntary context switches.
CONTEXT_INVOLUNTARY
Number of involuntary context switches.
BLOCK_OPS_IN
Number of block input operations.
BLOCK_OPS_OUT
Number of block output operations.
MESSAGES_SENT
Number of communications sent.
MESSAGES_RECEIVED
Number of communications received.
PAGE_FAULTS_MAJOR
Number of major page faults.
PAGE_FAULTS_MINOR
Number of minor page faults.
SWAPS
Number of swaps.
SOURCE_FUNCTION
Function in the source code executed by the profiled state.
SOURCE_FILE
File in the source code executed by the profiled state.
SOURCE_LINE
Line in the source code executed by the profiled state.
It contains similar information to the SHOW PROFILE and SHOW PROFILES statements.
Profiling is enabled per session. When a session ends, its profiling information is lost.
This page is licensed: CC BY-SA / Gnu FDL
The table is not a standard Information Schema table, and is a MariaDB extension.
The QUERY_CACHE_INFO table is created by the QUERY_CACHE_INFO plugin, and allows you to see the contents of the query cache. It creates a table in the information_schema database that shows all queries that are in the cache. You must have the PROCESS privilege (see GRANT) to use this table.
It contains the following columns:
STATEMENT_SCHEMA
Database used when query was included
STATEMENT_TEXT
Query text
RESULT_BLOCKS_COUNT
Number of result blocks
RESULT_BLOCKS_SIZE
Size in bytes of result blocks
RESULT_BLOCKS_SIZE_USED
Size in bytes of used blocks
LIMIT
Added MariaDB 10.1.8.
MAX_SORT_LENGTH
Added MariaDB 10.1.8.
GROUP_CONCAT_MAX_LENGTH
Added MariaDB 10.1.8.
CHARACTER_SET_CLIENT
Added MariaDB 10.1.8.
CHARACTER_SET_RESULT
Added MariaDB 10.1.8.
COLLATION
Added MariaDB 10.1.8.
TIMEZONE
Added MariaDB 10.1.8.
DEFAULT_WEEK_FORMAT
Added MariaDB 10.1.8.
DIV_PRECISION_INCREMENT
Added MariaDB 10.1.8.
SQL_MODE
Added MariaDB 10.1.8.
LC_TIME_NAMES
Added MariaDB 10.1.8.
CLIENT_LONG_FLAG
Added MariaDB 10.1.8.
CLIENT_PROTOCOL_41
Added MariaDB 10.1.8.
PROTOCOL_TYPE
Added MariaDB 10.1.8.
MORE_RESULTS_EXISTS
Added MariaDB 10.1.8.
IN_TRANS
Added MariaDB 10.1.8.
AUTOCOMMIT
Added MariaDB 10.1.8.
PACKET_NUMBER
Added MariaDB 10.1.8.
HITS
Incremented each time the query cache is hit. Added MariaDB 10.3.2.
For example:
SELECT * FROM information_schema.QUERY_CACHE_INFO;
+------------------+-----------------+---------------------+--------------------+-------------------------+
| STATEMENT_SCHEMA | STATEMENT_TEXT | RESULT_BLOCKS_COUNT | RESULT_BLOCKS_SIZE | RESULT_BLOCKS_SIZE_USED |
+------------------+-----------------+---------------------+--------------------+-------------------------+
...
| test | SELECT * FROM a | 1 | 512 | 143 |
| test | select * FROM a | 1 | 512 | 143 |
...
+------------------+-----------------+---------------------+--------------------+-------------------------This page is licensed: CC BY-SA / Gnu FDL
The Information Schema QUERY_RESPONSE_TIME table contains information about queries that take a long time to execute . It is only available if the QUERY_RESPONSE_TIME plugin has been installed.
It contains the following columns:
TIME
Time interval
COUNT
Count of queries falling into the time interval
TOTAL
Total execution time of all queries for this interval
See QUERY_RESPONSE_TIME plugin for a full description.
The table is not a standard Information Schema table, and is a MariaDB extension.
SHOW QUERY_RESPONSE_TIME is available as an alternative for retrieving the data.
SELECT * FROM information_schema.QUERY_RESPONSE_TIME;
+----------------+-------+----------------+
| TIME | COUNT | TOTAL |
+----------------+-------+----------------+
| 0.000001 | 0 | 0.000000 |
| 0.000010 | 17 | 0.000094 |
| 0.000100 | 4301 0.236555 |
| 0.001000 | 1499 | 0.824450 |
| 0.010000 | 14851 | 81.680502 |
| 0.100000 | 8066 | 443.635693 |
| 1.000000 | 0 | 0.000000 |
| 10.000000 | 0 | 0.000000 |
| 100.000000 | 1 | 55.937094 |
| 1000.000000 | 0 | 0.000000 |
| 10000.000000 | 0 | 0.000000 |
| 100000.000000 | 0 | 0.000000 |
| 1000000.000000 | 0 | 0.000000 |
| TOO LONG | 0 | TOO LONG |
+----------------+-------+----------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema REFERENTIAL_CONSTRAINTS table contains information about foreign keys. The single columns are listed in the KEY_COLUMN_USAGE table.
It has the following columns:
CONSTRAINT_CATALOG
Always def.
CONSTRAINT_SCHEMA
Database name, together with CONSTRAINT_NAME identifies the foreign key.
CONSTRAINT_NAME
Foreign key name, together with CONSTRAINT_SCHEMA identifies the foreign key.
UNIQUE_CONSTRAINT_CATALOG
Always def.
UNIQUE_CONSTRAINT_SCHEMA
Database name, together with UNIQUE_CONSTRAINT_NAME and REFERENCED_TABLE_NAME identifies the referenced key.
UNIQUE_CONSTRAINT_NAME
Referenced key name, together with UNIQUE_CONSTRAINT_SCHEMA and REFERENCED_TABLE_NAME identifies the referenced key.
MATCH_OPTION
Always NONE.
UPDATE_RULE
The Update Rule; one of CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.
DELETE_RULE
The Delete Rule; one of CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.
TABLE_NAME
Table name from the TABLE_CONSTRAINTS table.
REFERENCED_TABLE_NAME
Referenced key table name, together with UNIQUE_CONSTRAINT_SCHEMA and UNIQUE_CONSTRAINT_NAME identifies the referenced key.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema ROUTINES table stores information about stored procedures and stored functions.
It contains the following columns:
SPECIFIC_NAME
ROUTINE_CATALOG
Always def.
ROUTINE_SCHEMA
Database name associated with the routine.
ROUTINE_NAME
Name of the routine.
ROUTINE_TYPE
Whether the routine is a PROCEDURE or a FUNCTION.
DATA_TYPE
The return value's data type (for stored functions).
CHARACTER_MAXIMUM_LENGTH
Maximum length.
CHARACTER_OCTET_LENGTH
Same as the CHARACTER_MAXIMUM_LENGTH except for multi-byte character sets.
NUMERIC_PRECISION
For numeric types, the precision (number of significant digits) for the column. NULL if not a numeric field.
NUMERIC_SCALE
For numeric types, the scale (significant digits to the right of the decimal point). NULL if not a numeric field.
DATETIME_PRECISION
Fractional-seconds precision, or NULL if not a time data type.
CHARACTER_SET_NAME
Character set if a non-binary string data type, otherwise NULL.
COLLATION_NAME
Collation if a non-binary string data type, otherwise NULL.
DATA_TYPE
The column's data type.
ROUTINE_BODY
Always SQL.
ROUTINE_DEFINITION
Definition of the routine.
EXTERNAL_NAME
Always NULL.
EXTERNAL_LANGUAGE
Always SQL.
PARAMETER_STYLE
Always SQL.
IS_DETERMINISTIC
Whether the routine is deterministic (can produce only one result for a given list of parameters) or not.
SQL_DATA_ACCESS
One of READS SQL DATA, MODIFIES SQL DATA, CONTAINS SQL, or NO SQL.
SQL_PATH
Always NULL.
SECURITY_TYPE
INVOKER or DEFINER. Indicates which user's privileges apply to this routine.
CREATED
Date and time the routine was created.
LAST_ALTERED
Date and time the routine was last changed.
SQL_MODE
The SQL_MODE at the time the routine was created.
ROUTINE_COMMENT
Comment associated with the routine.
DEFINER
If the SECURITY_TYPE is DEFINER, this value indicates which user defined this routine.
CHARACTER_SET_CLIENT
The character set used by the client that created the routine.
COLLATION_CONNECTION
The collation (and character set) used by the connection that created the routine.
DATABASE_COLLATION
The default collation (and character set) for the database, at the time the routine was created.
It provides information similar to, but more complete, than the SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS statements.
For information about the parameters accepted by the routine, you can query the information_schema.PARAMETERS table.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SCHEMA_PRIVILEGES table contains information about database privileges.
It contains the following columns:
GRANTEE
Account granted the privilege in the format user_name@host_name.
TABLE_CATALOG
Always def
TABLE_SCHEMA
Database name.
PRIVILEGE_TYPE
The granted privilege.
IS_GRANTABLE
Whether the privilege can be granted.
The same information in a different format can be found in the mysql.db table.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SCHEMATA table stores information about databases on the server.
It contains the following columns:
CATALOG_NAME
Always def.
SCHEMA_NAME
Database name.
DEFAULT_CHARACTER_SET_NAME
Default character set for the database.
DEFAULT_COLLATION_NAME
Default collation.
SQL_PATH
Always NULL.
SCHEMA_COMMENT
Database comment. From MariaDB 10.5.0.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA\G
...
*************************** 2. row ***************************
CATALOG_NAME: def
SCHEMA_NAME: presentations
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
SCHEMA_COMMENT: Presentations for conferences
...SELECT * FROM INFORMATION_SCHEMA.SCHEMATA\G
*************************** 1. row ***************************
CATALOG_NAME: def
SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8
DEFAULT_COLLATION_NAME: utf8_general_ci
SQL_PATH: NULL
*************************** 2. row ***************************
CATALOG_NAME: def
SCHEMA_NAME: mysql
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
*************************** 3. row ***************************
CATALOG_NAME: def
SCHEMA_NAME: performance_schema
DEFAULT_CHARACTER_SET_NAME: utf8
DEFAULT_COLLATION_NAME: utf8_general_ci
SQL_PATH: NULL
*************************** 4. row ***************************
CATALOG_NAME: def
SCHEMA_NAME: test
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SEQUENCES table stores information about sequences on the server.
It contains the following columns. See CREATE SEQUENCE for details.
SEQUENCE_CATALOG
Catalog name
SEQUENCE_SCHEMA
Database name
SEQUENCE_NAME
Sequence name
DATA_TYPE
Data type, for example BIGINT or TINYINT UNSIGNED
NUMERIC_PRECISION
NUMERIC_PRECISION_RADIX
NUMERIC_SCALE
START_VALUE
MINIMUM_VALUE
MAXIMUM_VALUE
INCREMENT
CYCLE_OPTION
1 if the CYCLE OPTION is set, otherwise 0
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
SEQUENCE_CATALOG: def
SEQUENCE_SCHEMA: test
SEQUENCE_NAME: s
DATA_TYPE: bigint
NUMERIC_PRECISION: 64
NUMERIC_PRECISION_RADIX: 2
NUMERIC_SCALE: 0
START_VALUE: 100
MINIMUM_VALUE: 1
MAXIMUM_VALUE: 9223372036854775806
INCREMENT: 10
CYCLE_OPTION: 0SETVAL(). Set next value for the sequence.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SLAVE_STATUS table contains replica status information, similar to SHOW REPLICA STATUS.
See SHOW REPLICA STATUS for column descriptions.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SPATIAL_REF_SYS table stores information on each spatial reference system used in the database.
It contains the following columns:
SRID
smallint(5)
NO
An integer value that uniquely identifies each Spatial Reference System within a database.
AUTH_NAME
varchar(512)
NO
The name of the standard or standards body that is being cited for this reference system.
AUTH_SRID
smallint(5)
NO
The numeric ID of the coordinate system in the above authority's catalog.
Note: See MDEV-7540.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SPIDER_ALLOC_MEM table is installed along with the Spider storage engine. It shows information about Spider's memory usage.
It contains the following columns:
ID
FUNC_NAME
FILE_NAME
LINE_NO
TOTAL_ALLOC_MEM
CURRENT_ALLOC_MEM
ALLOC_MEM_COUNT
FREE_MEM_COUNT
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SQL_FUNCTIONS table contains the list of MariaDB functions.
It contains a single column:
FUNCTION
Function name
The table is not a standard Information Schema table, and is a MariaDB extension.
SELECT * FROM INFORMATION_SCHEMA.SQL_FUNCTIONS;
+---------------------------+
| FUNCTION |
+---------------------------+
| ADDDATE |
| ADD_MONTHS |
| BIT_AND |
| BIT_OR |
| BIT_XOR |
| CAST |
| COUNT |
| CUME_DIST |
| CURDATE |
| CURTIME |
| DATE_ADD |
| DATE_SUB |
| DATE_FORMAT |
| DECODE |
| DENSE_RANK |
| EXTRACT |
| FIRST_VALUE |
| GROUP_CONCAT |
| JSON_ARRAYAGG |
| JSON_OBJECTAGG |
| LAG |
| LEAD |
| MAX |
| MEDIAN |
| MID |
| MIN |
| NOW |
| NTH_VALUE |
| NTILE |
| POSITION |
| PERCENT_RANK |
| PERCENTILE_CONT |
| PERCENTILE_DISC |
| RANK |
| ROW_NUMBER |
| SESSION_USER |
| STD |
| STDDEV |
| STDDEV_POP |
| STDDEV_SAMP |
| SUBDATE |
| SUBSTR |
| SUBSTRING |
| SUM |
| SYSTEM_USER |
| TRIM |
| TRIM_ORACLE |
| VARIANCE |
| VAR_POP |
| VAR_SAMP |
| ABS |
| ACOS |
| ADDTIME |
| AES_DECRYPT |
| AES_ENCRYPT |
| ASIN |
| ATAN |
| ATAN2 |
| BENCHMARK |
| BIN |
| BINLOG_GTID_POS |
| BIT_COUNT |
| BIT_LENGTH |
| CEIL |
| CEILING |
| CHARACTER_LENGTH |
| CHAR_LENGTH |
| CHR |
| COERCIBILITY |
| COLUMN_CHECK |
| COLUMN_EXISTS |
| COLUMN_LIST |
| COLUMN_JSON |
| COMPRESS |
| CONCAT |
| CONCAT_OPERATOR_ORACLE |
| CONCAT_WS |
| CONNECTION_ID |
| CONV |
| CONVERT_TZ |
| COS |
| COT |
| CRC32 |
| DATEDIFF |
| DAYNAME |
| DAYOFMONTH |
| DAYOFWEEK |
| DAYOFYEAR |
| DEGREES |
| DECODE_HISTOGRAM |
| DECODE_ORACLE |
| DES_DECRYPT |
| DES_ENCRYPT |
| ELT |
| ENCODE |
| ENCRYPT |
| EXP |
| EXPORT_SET |
| EXTRACTVALUE |
| FIELD |
| FIND_IN_SET |
| FLOOR |
| FORMAT |
| FOUND_ROWS |
| FROM_BASE64 |
| FROM_DAYS |
| FROM_UNIXTIME |
| GET_LOCK |
| GREATEST |
| HEX |
| IFNULL |
| INSTR |
| ISNULL |
| IS_FREE_LOCK |
| IS_USED_LOCK |
| JSON_ARRAY |
| JSON_ARRAY_APPEND |
| JSON_ARRAY_INSERT |
| JSON_COMPACT |
| JSON_CONTAINS |
| JSON_CONTAINS_PATH |
| JSON_DEPTH |
| JSON_DETAILED |
| JSON_EXISTS |
| JSON_EXTRACT |
| JSON_INSERT |
| JSON_KEYS |
| JSON_LENGTH |
| JSON_LOOSE |
| JSON_MERGE |
| JSON_MERGE_PATCH |
| JSON_MERGE_PRESERVE |
| JSON_QUERY |
| JSON_QUOTE |
| JSON_OBJECT |
| JSON_REMOVE |
| JSON_REPLACE |
| JSON_SET |
| JSON_SEARCH |
| JSON_TYPE |
| JSON_UNQUOTE |
| JSON_VALID |
| JSON_VALUE |
| LAST_DAY |
| LAST_INSERT_ID |
| LCASE |
| LEAST |
| LENGTH |
| LENGTHB |
| LN |
| LOAD_FILE |
| LOCATE |
| LOG |
| LOG10 |
| LOG2 |
| LOWER |
| LPAD |
| LPAD_ORACLE |
| LTRIM |
| LTRIM_ORACLE |
| MAKEDATE |
| MAKETIME |
| MAKE_SET |
| MASTER_GTID_WAIT |
| MASTER_POS_WAIT |
| MD5 |
| MONTHNAME |
| NAME_CONST |
| NVL |
| NVL2 |
| NULLIF |
| OCT |
| OCTET_LENGTH |
| ORD |
| PERIOD_ADD |
| PERIOD_DIFF |
| PI |
| POW |
| POWER |
| QUOTE |
| REGEXP_INSTR |
| REGEXP_REPLACE |
| REGEXP_SUBSTR |
| RADIANS |
| RAND |
| RELEASE_ALL_LOCKS |
| RELEASE_LOCK |
| REPLACE_ORACLE |
| REVERSE |
| ROUND |
| RPAD |
| RPAD_ORACLE |
| RTRIM |
| RTRIM_ORACLE |
| SEC_TO_TIME |
| SHA |
| SHA1 |
| SHA2 |
| SIGN |
| SIN |
| SLEEP |
| SOUNDEX |
| SPACE |
| SQRT |
| STRCMP |
| STR_TO_DATE |
| SUBSTR_ORACLE |
| SUBSTRING_INDEX |
| SUBTIME |
| SYS_GUID |
| TAN |
| TIMEDIFF |
| TIME_FORMAT |
| TIME_TO_SEC |
| TO_BASE64 |
| TO_CHAR |
| TO_DAYS |
| TO_SECONDS |
| UCASE |
| UNCOMPRESS |
| UNCOMPRESSED_LENGTH |
| UNHEX |
| UNIX_TIMESTAMP |
| UPDATEXML |
| UPPER |
| UUID |
| UUID_SHORT |
| VERSION |
| WEEKDAY |
| WEEKOFYEAR |
| WSREP_LAST_WRITTEN_GTID |
| WSREP_LAST_SEEN_GTID |
| WSREP_SYNC_WAIT_UPTO_GTID |
| YEARWEEK |
+---------------------------+
234 rows in set (0.001 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema STATISTICS table provides information about table indexes.
It contains the following columns:
TABLE_CATALOG
Always def.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
NON_UNIQUE
1 if the index can have duplicates, 0 if not.
INDEX_SCHEMA
Database name.
INDEX_NAME
Index name. The primary key is always named PRIMARY.
SEQ_IN_INDEX
The column sequence number, starting at 1.
COLUMN_NAME
Column name.
COLLATION
A for sorted in ascending order, or NULL for unsorted.
CARDINALITY
Estimate of the number of unique values stored in the index based on statistics stored as integers. Higher cardinalities usually mean a greater chance of the index being used in a join. Updated by the ANALYZE TABLE statement or myisamchk -a.
SUB_PART
NULL if the whole column is indexed, or the number of indexed characters if partly indexed.
PACKED
NULL if not packed, otherwise how the index is packed.
NULLABLE
YES if the column may contain NULLs, empty string if not.
INDEX_TYPE
Index type, one of BTREE, RTREE, HASH or FULLTEXT. See Storage Engine Index Types.
COMMENT
Index comments from the CREATE INDEX statement.
IGNORED
Whether or not an index will be ignored by the optimizer. See Ignored Indexes. From MariaDB 10.6.0.
The SHOW INDEX statement produces similar output.
SELECT * FROM INFORMATION_SCHEMA.STATISTICS\G
...
*************************** 85. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: table1
NON_UNIQUE: 1
INDEX_SCHEMA: test
INDEX_NAME: col2
SEQ_IN_INDEX: 1
COLUMN_NAME: col2
COLLATION: A
CARDINALITY: 6
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema SYSTEM_VARIABLES table shows current values and various metadata of all system variables.
It contains the following columns:
VARIABLE_NAME
System variable name.
SESSION_VALUE
Session value of the variable or NULL if the variable only has a global scope.
GLOBAL_VALUE
Global value of the variable or NULL if the variable only has a session scope.
GLOBAL_VALUE_ORIGIN
How the global value was set — a compile-time default, auto-configured by the server, configuration file (or a command line), with the SQL statement.
DEFAULT_VALUE
Compile-time default value of the variable.
VARIABLE_SCOPE
Global, session, or session-only.
VARIABLE_TYPE
Data type of the variable value.
VARIABLE_COMMENT
Help text, usually shown in mariadbd --help --verbose.
NUMERIC_MIN_VALUE
For numeric variables — minimal allowed value.
NUMERIC_MAX_VALUE
For numeric variables — maximal allowed value.
NUMERIC_BLOCK_SIZE
For numeric variables — a valid value must be a multiple of the "block size".
ENUM_VALUE_LIST
For ENUM, SET, and FLAGSET variables — the list of recognized values.
READ_ONLY
Whether a variable can be set with the SQL statement. Note that many "read only" variables can still be set on the command line.
COMMAND_LINE_ARGUMENT
Whether an argument is required when setting the variable on the command line. NULL when a variable can not be set on the command line.
GLOBAL_VALUE_PATH
Which config file the variable got its value from. NULL if not set in any config file. Added in MariaDB 10.5.0.
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: REQUIREDThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema TABLE_CONSTRAINTS table contains information about tables that have constraints.
It has the following columns:
CONSTRAINT_CATALOG
Always def.
CONSTRAINT_SCHEMA
Database name containing the constraint.
CONSTRAINT_NAME
Constraint name.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
CONSTRAINT_TYPE
Type of constraint; one of UNIQUE, PRIMARY KEY, FOREIGN KEY or CHECK.
The REFERENTIAL_CONSTRAINTS table has more information about foreign keys.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema TABLE_PRIVILEGES table contains table privilege information derived from the mysql.tables_priv grant table.
It has the following columns:
GRANTEE
In the format user_name@host_name.
TABLE_CATALOG
Always def.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
PRIVILEGE_TYPE
One of SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP or CREATE VIEW.
IS_GRANTABLE
Whether the user has the GRANT OPTION for this privilege.
Similar information can be accessed with the SHOW GRANTS statement. See the GRANT article for more about privileges.
The table only shows privileges granted on the table level. This differs from the Sys Schema privileges_by_table_by_level view, which shows all privileges, broken down by table.
For a description of the privileges that are shown in this table, see table privileges.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema TABLE_STATISTICS table shows statistics on table usage.
This is part of the User Statistics feature, which is not enabled by default.
It contains the following columns:
TABLE_SCHEMA
varchar(192)
The schema (database) name.
TABLE_NAME
varchar(192)
The table name.
ROWS_READ
bigint(21)
The number of rows read from the table.
ROWS_CHANGED
bigint(21)
The number of rows changed in the table.
ROWS_CHANGED_X_INDEXES
bigint(21)
The number of rows changed in the table, multiplied by the number of indexes changed.
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='user';
+--------------+------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+------------+-----------+--------------+------------------------+
| mysql | user | 5 | 2 | 2 |
+--------------+------------+-----------+--------------+------------------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema table shows information about the various tables and views on the server.
The Information Schema table shows information about the various tables, excludingTEMPORARY tables, except for tables from the Information Schema database) and views on the server.
It contains the following columns:
TABLE_CATALOG
Always def.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
TABLE_TYPE
One of BASE TABLE for a regular table, VIEW for a view, SYSTEM VIEW for Information Schema tables, SYSTEM VERSIONED for system-versioned tables, SEQUENCE for sequences or, from MariaDB 11.2.0, TEMPORARY for local temporary tables.
ENGINE
VERSION
Version number from the table's .frm file
TABLE_ROWS
Number of rows in the table. Some engines, such as XtraDB and InnoDB may store an estimate.
AVG_ROW_LENGTH
Average row length in the table.
DATA_LENGTH
For InnoDB/XtraDB, the index size, in pages, multiplied by the page size. For Aria and MyISAM, length of the data file, in bytes. For MEMORY, the approximate allocated memory.
MAX_DATA_LENGTH
Maximum length of the data file, ie the total number of bytes that could be stored in the table. Not used in XtraDB and InnoDB.
INDEX_LENGTH
Length of the index file.
DATA_FREE
Bytes allocated but unused. For InnoDB tables in a shared tablespace, the free space of the shared tablespace with small safety margin. An estimate in the case of partitioned tables - see the PARTITIONS table.
AUTO_INCREMENT
Next AUTO_INCREMENT value.
CREATE_TIME
Time the table was created. Some engines just return the ctime information from the file system layer here, in that case the value is not necessarily the table creation time but rather the time the file system metadata for it had last changed.
UPDATE_TIME
Time the table was last updated. On Windows, the timestamp is not updated on update, so MyISAM values will be inaccurate. In InnoDB, if shared tablespaces are used, will be NULL, while buffering can also delay the update, so the value will differ from the actual time of the last UPDATE, INSERT or DELETE.
CHECK_TIME
Time the table was last checked. Not kept by all storage engines, in which case will be NULL.
TABLE_COLLATION
CHECKSUM
Live checksum value, if any.
CREATE_OPTIONS
Extra CREATE TABLE options.
TABLE_COMMENT
Table comment provided when MariaDB created the table.
MAX_INDEX_LENGTH
Maximum index length (supported by MyISAM and Aria tables). Added in MariaDB 10.3.5.
TEMPORARY
Until MariaDB 11.2.0, placeholder to signal that a table is a temporary table and always "N", except "Y" for generated information_schema tables and NULL for views. From MariaDB 11.2.0, will also be set to "Y" for local temporary tables. Added in MariaDB 10.3.5.
Although the table is standard in the Information Schema, all but TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE and VERSION are MySQL and MariaDB extensions.
SHOW TABLES lists all tables in a database.
SELECT * FROM information_schema.tables WHERE table_schema='test'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: xx5
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2020-11-18 15:57:10
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
MAX_INDEX_LENGTH: 0
TEMPORARY: N
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: xx4
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 1970324836974591
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2020-11-18 15:56:57
UPDATE_TIME: 2020-11-18 15:56:57
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
MAX_INDEX_LENGTH: 17179868160
TEMPORARY: N
...Example with temporary = 'y':
SELECT * FROM information_schema.tables WHERE temporary='y'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: information_schema
TABLE_NAME: INNODB_FT_DELETED
TABLE_TYPE: SYSTEM VIEW
ENGINE: MEMORY
VERSION: 11
ROW_FORMAT: Fixed
TABLE_ROWS: NULL
AVG_ROW_LENGTH: 9
DATA_LENGTH: 0
MAX_DATA_LENGTH: 9437184
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2020-11-17 21:54:02
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: max_rows=1864135
TABLE_COMMENT:
MAX_INDEX_LENGTH: 0
TEMPORARY: Y
...Returns a list of all tables in the database, ordered by size:
SELECT table_schema AS `DB`, table_name AS `TABLE`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
+--------------------+---------------------------------------+-----------+
| DB | Table | Size (MB) |
+--------------------+---------------------------------------+-----------+
| wordpress | wp_simple_history_contexts | 7.05 |
| wordpress | wp_posts | 6.59 |
| wordpress | wp_simple_history | 3.05 |
| wordpress | wp_comments | 2.73 |
| wordpress | wp_commentmeta | 2.47 |
| wordpress | wp_simple_login_log | 2.03 |
...Returns information about a temporary table:
CREATE TEMPORARY TABLE foo.t1 (a INT);
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='foo' AND TEMPORARY='y'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: foo
TABLE_NAME: t1
TABLE_TYPE: TEMPORARY
...
TEMPORARY: YThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema TABLESPACES table contains information about active tablespaces..
The table is a MariaDB and MySQL extension, and does not include information about InnoDB tablespaces.
TABLESPACE_NAME
ENGINE
TABLESPACE_TYPE
LOGFILE_GROUP_NAME
EXTENT_SIZE
AUTOEXTEND_SIZE
MAXIMUM_SIZE
NODEGROUP_ID
TABLESPACE_COMMENT
This page is licensed: CC BY-SA / Gnu FDL
The table provides information about thread pool groups, and contains the following columns:
GROUP_ID
the thread group this row is showing data for
CONNECTIONS
the number of clients currently connected to this thread group
THREADS
total number of threads in this group (ACTIVE+STANDBY+LISTENER)
ACTIVE_THREADS
number of threads currently executing a query
STANDBY_THREADS
number of threads in reserve that do not currently execute anything
QUEUE_LENGTH
number of client requests waiting for execution
HAS_LISTENER
whether there is an active listener thread right now, always 1 if thread_pool_dedicated_listener is ON
IS_STALLED
whether there's currently an active worker thread in this group that has exceeded thread_pool_stall_limit time
Setting thread_pool_dedicated_listener will give each group its own dedicated listener, and the listener thread will not pick up work items. As a result, the actual queue size in the table will be more exact, since IO requests are immediately dequeued from poll, without delay.
This page is licensed: CC BY-SA / Gnu FDL
The table provides information about thread pool queues, and contains the following columns:
GROUP_ID
the thread group this row is showing data for
POSITION
position in the groups queue
PRIORITY
request priority, see priority scheduling
CONNECTION_ID
ID of the client connection that submitted the queued request
QUEUEING_TIME_MICROSECONDS
how long the request has already been waiting in the queue in microseconds
Setting thread_poll_exact_stats will provides better queueing time statistics by using a high precision timestamp, at a small performance cost, for the time when the connection was added to the queue. This timestamp helps calculate the queuing time shown in the table.
Setting thread_pool_dedicated_listener will give each group its own dedicated listener, and the listener thread will not pick up work items. As a result, the queueing time in the table will be more exact, since IO requests are immediately dequeued from poll, without delay.
This page is licensed: CC BY-SA / Gnu FDL
The table provides performance counter information for the thread pool, and contains the following columns:
GROUP_ID
the thread group this row is showing data for
THREAD_CREATIONS
number of threads created for this group so far
THREAD_CREATIONS_DUE_TO_STALL
number of threads created due to detected stalls
WAKES
standby thread wakeups
WAKES_DUE_TO_STALL
wakeups due to stalls
THROTTLES
how often thread creation was throttled, see also: thread-creation-throttling
STALLS
number of detected stalls
POLLS_BY_LISTENER
POLLS_BY_WORKER
DEQUEUES_BY_LISTENER
DEQUEUES_BY_WORKER
This page is licensed: CC BY-SA / Gnu FDL
The table provides wait counters for the thread pool, and contains the following columns:
REASON
name of the reason for waiting, e.g. ROW_LOCK, DISKIO, NET ...
COUNT
how often a wait for this specific reason has happened so far
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema TRIGGERS table contains information about triggers.
It has the following columns:
TRIGGER_CATALOG
Always def.
TRIGGER_SCHEMA
Database name in which the trigger occurs.
TRIGGER_NAME
Name of the trigger.
EVENT_MANIPULATION
The event that activates the trigger. One of INSERT, UPDATE or 'DELETE.
EVENT_OBJECT_CATALOG
Always def.
EVENT_OBJECT_SCHEMA
Database name on which the trigger acts.
EVENT_OBJECT_TABLE
Table name on which the trigger acts.
ACTION_ORDER
Indicates the order that the action will be performed in (of the list of a table's triggers with identical EVENT_MANIPULATION and ACTION_TIMING values). Before MariaDB 10.2.3 introduced the FOLLOWS and PRECEDES clauses, always 0
ACTION_CONDITION
NULL
ACTION_STATEMENT
Trigger body, UTF-8 encoded.
ACTION_ORIENTATION
Always ROW.
ACTION_TIMING
Whether the trigger acts BEFORE or AFTER the event that triggers it.
ACTION_REFERENCE_OLD_TABLE
Always NULL.
ACTION_REFERENCE_NEW_TABLE
Always NULL.
ACTION_REFERENCE_OLD_ROW
Always OLD.
ACTION_REFERENCE_NEW_ROW
Always NEW.
CREATED
Always NULL.
SQL_MODE
The SQL_MODE when the trigger was created, and which it uses for execution.
DEFINER
The account that created the trigger, in the form user_name@host_name
CHARACTER_SET_CLIENT
The client character set when the trigger was created, from the session value of the character_set_client system variable.
COLLATION_CONNECTION
The client collation when the trigger was created, from the session value of the collation_connection system variable.
DATABASE_COLLATION
Collation of the associated database.
Queries to the TRIGGERS table will return information only for databases and tables for which you have the TRIGGER privilege. Similar information is returned by the SHOW TRIGGERS statement.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema USER_PRIVILEGES table contains global user privilege information derived from the mysql.global_priv grant table.
It contains the following columns:
GRANTEE
In the format user_name@host_name.
TABLE_CATALOG
Always def.
PRIVILEGE_TYPE
The specific privilege, for example CREATE USER, RELOAD, SHUTDOWN, SELECT, INSERT, UPDATE or REFERENCES.
IS_GRANTABLE
Whether the user has the GRANT OPTION for this privilege.
The database, table and column privileges returned here are the ones granted on all databases and tables, and by implication all columns.
Similar information can be accessed with the SHOW GRANTS statement. See the GRANT article for more about privileges.
This information is also stored in the mysql.global_priv table, in the mysql system database.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema USER_STATISTICS table holds statistics about user activity. This is part of the User Statistics feature, which is not enabled by default.
You can use this table to find out such things as which user is causing the most load and which users are being abusive. You can also use this table to measure how close to capacity the server may be.
It contains the following columns:
USER
varchar(48)
The username. The value '#mysql_system_user#' appears when there is no username (such as for the slave SQL thread).
TOTAL_CONNECTIONS
int(11)
The number of connections created for this user.
CONCURRENT_CONNECTIONS
int(11)
The number of concurrent connections for this user.
CONNECTED_TIME
int(11)
The cumulative number of seconds elapsed while there were connections from this user.
BUSY_TIME
double
The cumulative number of seconds there was activity on connections from this user.
CPU_TIME
double
The cumulative CPU time elapsed while servicing this user's connections.
BYTES_RECEIVED
bigint(21)
The number of bytes received from this user's connections.
BYTES_SENT
bigint(21)
The number of bytes sent to this user's connections.
BINLOG_BYTES_WRITTEN
bigint(21)
The number of bytes written to the binary log from this user's connections.
ROWS_READ
bigint(21)
The number of rows read by this user's connections.
ROWS_SENT
bigint(21)
The number of rows sent by this user's connections.
ROWS_DELETED
int(21)
The number of rows deleted by this user's connections.
ROWS_INSERTED
bigint(21)
The number of rows inserted by this user's connections.
ROWS_UPDATED
bigint(21)
The number of rows updated by this user's connections.
OTHER_COMMANDS
bigint(21)
The number of other commands executed from this user's connections.
COMMIT_TRANSACTIONS
bigint(21)
The number committed transactions. Note that in autocommit mode every statement is a separate transaction.
ROLLBACK_TRANSACTIONS
bigint(21)
The number of transactions that were rolled back.
DENIED_CONNECTIONS
bigint(21)
The number of connections denied to this user.
LOST_CONNECTIONS
bigint(21)
The number of this user's connections that were terminated uncleanly.
ACCESS_DENIED
bigint(21)
The number of times this user's connections issued commands that were denied.
EMPTY_QUERIES
bigint(21)
The number of times this user's connections sent queries to the server that did not return data to the client (a per-user aggregate of the empty_queries status variable).
MAX_STATEMENT_TIME_EXCEEDED
bigint(21)
The number of times a statement was aborted, because it was executed longer than its MAX_STATEMENT_TIME threshold.
SELECT * FROM information_schema.USER_STATISTICS\G
*************************** 1. row ***************************
USER: root
TOTAL_CONNECTIONS: 1
CONCURRENT_CONNECTIONS: 0
CONNECTED_TIME: 297
BUSY_TIME: 0.001725
CPU_TIME: 0.001982
BYTES_RECEIVED: 388
BYTES_SENT: 2327
BINLOG_BYTES_WRITTEN: 0
ROWS_READ: 0
ROWS_SENT: 12
ROWS_DELETED: 0
ROWS_INSERTED: 13
ROWS_UPDATED: 0
SELECT_COMMANDS: 4
UPDATE_COMMANDS: 0
OTHER_COMMANDS: 3
COMMIT_TRANSACTIONS: 0
ROLLBACK_TRANSACTIONS: 0
DENIED_CONNECTIONS: 0
LOST_CONNECTIONS: 0
ACCESS_DENIED: 0
EMPTY_QUERIES: 1This page is licensed: CC BY-SA / Gnu FDL
The USER_VARIABLES table is created when the user_variables plugin is enabled, and contains information about user-defined variables.
The table contains the following columns:
VARIABLE_NAME
Variable name.
VARIABLE_VALUE
Variable value.
VARIABLE_TYPE
Variable type.
CHARACTER_SET_NAME
User variables are reset and the table emptied with the FLUSH USER_VARIABLES statement. SHOW USER_VARIABLES displays a subset of the data.
SET @v1 = 0;
SET @v2 = 'abc';
SET @v3 = CAST(123 AS CHAR(5));
SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| v1 | 0 | INT | latin1 |
| v2 | abc | VARCHAR | utf8 |
| v3 | 123 | VARCHAR | utf8 |
+---------------+----------------+---------------+--------------------+
SHOW USER_VARIABLES;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| v3 | 123 |
| v2 | abc |
| v1 | 0 |
+---------------+-------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema USERS table contains information about users, password expiry, and the limits set by max_password_errors. Unprivileged users can access their own data, which is different to what mysql.global_priv provides.
It contains the following columns:
USER
In the format user_name@host_name.
PASSWORD_ERRORS
A current accumulated value of consecutive password login failures. If password_errors is not applicable for the user (see max_password_errors), PASSWORD_ERRORS will be NULL. Only password login attempts with nonempty password are taken into account.
PASSWORD_EXPIRATION_TIME
A timestamp with the exact point in time calculated from password_last_changed and password_lifetime (i.e. days) stored for the user.
This page is licensed: CC BY-SA / Gnu FDL
The WSREP_STATUS table makes Galera node cluster membership information available through the Information Schema. The same information can be returned using the SHOW WSREP_MEMBERSHIP statement. Only users with the SUPER can access information from this table.
The WSREP_MEMBERSHIP table is part of the WSREP_INFO plugin.
SELECT * FROM information_schema.WSREP_MEMBERSHIP;
+-------+--------------------------------------+-------+-----------------+
| INDEX | UUID | NAME | ADDRESS |
+-------+--------------------------------------+-------+-----------------+
| 0 | 46da96e3-6e9e-11e4-95a2-f609aa5444b3 | node1 | 10.0.2.15:16000 |
| 1 | 5f6bc72a-6e9e-11e4-84ed-57ec6780a3d3 | node2 | 10.0.2.15:16001 |
| 2 | 7473fd75-6e9e-11e4-91de-0b541ad91bd0 | node3 | 10.0.2.15:16002 |
+-------+--------------------------------------+-------+-----------------+This page is licensed: CC BY-SA / Gnu FDL
The WSREP_STATUS table makes Galera node cluster status information available through the Information Schema. The same information can be returned using the SHOW WSREP_STATUS statement. Only users with the SUPER privilege can access information from this table.
The WSREP_STATUS table is part of the WSREP_INFO plugin.
SELECT * FROM information_schema.WSREP_STATUS\G
*************************** 1. row ***************************
NODE_INDEX: 0
NODE_STATUS: Synced
CLUSTER_STATUS: Primary
CLUSTER_SIZE: 3
CLUSTER_STATE_UUID: 00b0fbad-6e84-11e4-8a8b-376f19ce8ee7
CLUSTER_STATE_SEQNO: 2
CLUSTER_CONF_ID: 3
GAP: NO
PROTOCOL_VERSION: 3This page is licensed: CC BY-SA / Gnu FDL
These system tables provide detailed metadata about InnoDB tables, including their structure, storage, and other crucial properties for analysis.
The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables, grouped by individual indexes. These tables are only populated if the innodb_cmp_per_index_enabled system variable is set to ON.
The PROCESS privilege is required to query this table.
These tables contains the following columns:
DATABASE_NAME
Database containing the index.
TABLE_NAME
Table containing the index.
INDEX_NAME
Other values are totals which refer to this index's compression.
COMPRESS_OPS
How many times a page of INDEX_NAME has been compressed. This happens when a new page is created because the compression log runs out of space. This value includes both successful operations and compression failures.
COMPRESS_OPS_OK
How many times a page of INDEX_NAME has been successfully compressed. This value should be as close as possible to COMPRESS_OPS. If it is notably lower, either avoid compressing some tables, or increase the KEY_BLOCK_SIZE for some compressed tables.
COMPRESS_TIME
Time (in seconds) spent to compress pages of the size PAGE_SIZE. This value includes time spent in compression failures.
UNCOMPRESS_OPS
How many times a page of INDEX_NAME has been uncompressed. This happens when an uncompressed version of a page is created in the buffer pool, or when a compression failure occurs.
UNCOMPRESS_TIME
Time (in seconds) spent to uncompress pages of INDEX_NAME.
These tables can be used to measure the effectiveness of XtraDB/InnoDB compression, per table or per index. The values in these tables show which tables perform better with index compression, and which tables cause too many compression failures or perform too many compression/uncompression operations. When compression performs badly for a table, this might mean that you should change its KEY_BLOCK_SIZE, or that the table should not be compressed.
INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET have the same columns and always contain the same values, but when INNODB_CMP_PER_INDEX_RESET is queried, both the tables are cleared. INNODB_CMP_PER_INDEX_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMP_PER_INDEX can be used to see the cumulated statistics.
Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_BUFFER_PAGE table contains information about pages in the buffer pool.
Querying this table can have a noticeable performance impact on a production server.
The PROCESS privilege is required to view the table.
It has the following columns:
POOL_ID
Buffer Pool identifier. From MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
BLOCK_ID
Buffer Pool Block identifier.
SPACE
Tablespace identifier. Matches the SPACE value in the INNODB_SYS_TABLES table.
PAGE_NUMBER
Buffer pool page number.
PAGE_TYPE
Page type; one of allocated (newly-allocated page), index (B-tree node), undo_log (undo log page), inode (index node), ibuf_free_list (insert buffer free list), ibuf_bitmap (insert buffer bitmap), system (system page), trx_system (transaction system data), file_space_header (file space header), extent_descriptor (extent descriptor page), blob (uncompressed blob page), compressed_blob (first compressed blob page), compressed_blob2 (subsequent compressed blob page) or unknown.
FLUSH_TYPE
Flush type.
FIX_COUNT
Count of the threads using this block in the buffer pool. When it is zero, the block can be evicted from the buffer pool.
IS_HASHED
Whether or not a hash index has been built on this page.
NEWEST_MODIFICATION
Most recent modification's Log Sequence Number.
OLDEST_MODIFICATION
Oldest modification's Log Sequence Number.
ACCESS_TIME
Abstract number representing the time the page was first accessed.
TABLE_NAME
Table that the page belongs to.
INDEX_NAME
Index that the page belongs to, either a clustered index or a secondary index.
NUMBER_RECORDS
Number of records the page contains.
DATA_SIZE
Size in bytes of all the records contained in the page.
COMPRESSED_SIZE
Compressed size in bytes of the page, or NULL for pages that aren't compressed.
PAGE_STATE
Page state; one of FILE_PAGE (page from a file) or MEMORY (page from an in-memory object) for valid data, or one of NULL, READY_FOR_USE, NOT_USED, REMOVE_HASH.
IO_FIX
Whether there is I/O pending for the page; one of IO_NONE (no pending I/O), IO_READ (read pending), IO_WRITE (write pending).
IS_OLD
Whether the page is old or not.
FREE_PAGE_CLOCK
Freed_page_clock counter, which tracks the number of blocks removed from the end of the least recently used (LRU) list, at the time the block was last placed at the head of the list.
The related INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU table contains the same information, but with an LRU (least recently used) position rather than block id.
DESC information_schema.innodb_buffer_page;
+---------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID | bigint(21) unsigned | NO | | 0 | |
| BLOCK_ID | bigint(21) unsigned | NO | | 0 | |
| SPACE | bigint(21) unsigned | NO | | 0 | |
| PAGE_NUMBER | bigint(21) unsigned | NO | | 0 | |
| PAGE_TYPE | varchar(64) | YES | | NULL | |
| FLUSH_TYPE | bigint(21) unsigned | NO | | 0 | |
| FIX_COUNT | bigint(21) unsigned | NO | | 0 | |
| IS_HASHED | varchar(3) | YES | | NULL | |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| ACCESS_TIME | bigint(21) unsigned | NO | | 0 | |
| TABLE_NAME | varchar(1024) | YES | | NULL | |
| INDEX_NAME | varchar(1024) | YES | | NULL | |
| NUMBER_RECORDS | bigint(21) unsigned | NO | | 0 | |
| DATA_SIZE | bigint(21) unsigned | NO | | 0 | |
| COMPRESSED_SIZE | bigint(21) unsigned | NO | | 0 | |
| PAGE_STATE | varchar(64) | YES | | NULL | |
| IO_FIX | varchar(64) | YES | | NULL | |
| IS_OLD | varchar(3) | YES | | NULL | |
| FREE_PAGE_CLOCK | bigint(21) unsigned | NO | | 0 | |
+---------------------+---------------------+------+-----+---------+-------+SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE\G
...
*************************** 6. row ***************************
POOL_ID: 0
BLOCK_ID: 5
SPACE: 0
PAGE_NUMBER: 11
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 2046835
OLDEST_MODIFICATION: 0
ACCESS_TIME: 2585566280
TABLE_NAME: `SYS_INDEXES`
INDEX_NAME: CLUST_IND
NUMBER_RECORDS: 57
DATA_SIZE: 4016
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_BUFFER_PAGE_LRU table contains information about pages in the buffer pool and how they are ordered for eviction purposes.
The PROCESS privilege is required to view the table.
It has the following columns:
POOL_ID
Buffer Pool identifier. From MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
LRU_POSITION
LRU (Least recently-used), for determining eviction order from the buffer pool.
SPACE
Tablespace identifier. Matches the SPACE value on the INNODB_SYS_TABLES table.
PAGE_NUMBER
Buffer pool page number.
PAGE_TYPE
Page type; one of allocated (newly-allocated page), index (B-tree node), undo_log (undo log page), inode (index node), ibuf_free_list (insert buffer free list), ibuf_bitmap (insert buffer bitmap), system (system page), trx_system (transaction system data), file_space_header (file space header), extent_descriptor (extent descriptor page), blob (uncompressed blob page), compressed_blob (first compressed blob page), compressed_blob2 (subsequent compressed blob page) or unknown.
FLUSH_TYPE
Flush type. 0= FLUSH_KEEP, 1 =FLUSH_RELEASE, 2 = FLUSH_IGNORE_CHANGED, 3= FLUSH_FORCE_WRITE
FIX_COUNT
Count of the threads using this block in the buffer pool. When it is zero, the block can be evicted from the buffer pool.
IS_HASHED
Whether or not a hash index has been built on this page.
NEWEST_MODIFICATION
Most recent modification's Log Sequence Number.
OLDEST_MODIFICATION
Oldest modification's Log Sequence Number.
ACCESS_TIME
Abstract number representing the time the page was first accessed.
TABLE_NAME
Table that the page belongs to.
INDEX_NAME
Index that the page belongs to, either a clustered index or a secondary index.
NUMBER_RECORDS
Number of records the page contains.
DATA_SIZE
Size in bytes of all the records contained in the page.
COMPRESSED_SIZE
Compressed size in bytes of the page, or NULL for pages that aren't compressed.
PAGE_STATE
Page state; one of FILE_PAGE (page from a file) or MEMORY (page from an in-memory object) for valid data, or one of NULL, READY_FOR_USE, NOT_USED, REMOVE_HASH.
IO_FIX
Whether there is I/O pending for the page; one of IO_NONE (no pending I/O), IO_READ (read pending), IO_WRITE (write pending).
IS_OLD
Whether the page is old or not.
FREE_PAGE_CLOCK
Freed_page_clock counter, which tracks the number of blocks removed from the end of the LRU list, at the time the block was last placed at the head of the list.
The related INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table contains the same information, but with a block id rather than LRU position.
DESC information_schema.innodb_buffer_page_lru;
+---------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID | bigint(21) unsigned | NO | | 0 | |
| LRU_POSITION | bigint(21) unsigned | NO | | 0 | |
| SPACE | bigint(21) unsigned | NO | | 0 | |
| PAGE_NUMBER | bigint(21) unsigned | NO | | 0 | |
| PAGE_TYPE | varchar(64) | YES | | NULL | |
| FLUSH_TYPE | bigint(21) unsigned | NO | | 0 | |
| FIX_COUNT | bigint(21) unsigned | NO | | 0 | |
| IS_HASHED | varchar(3) | YES | | NULL | |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| ACCESS_TIME | bigint(21) unsigned | NO | | 0 | |
| TABLE_NAME | varchar(1024) | YES | | NULL | |
| INDEX_NAME | varchar(1024) | YES | | NULL | |
| NUMBER_RECORDS | bigint(21) unsigned | NO | | 0 | |
| DATA_SIZE | bigint(21) unsigned | NO | | 0 | |
| COMPRESSED_SIZE | bigint(21) unsigned | NO | | 0 | |
| COMPRESSED | varchar(3) | YES | | NULL | |
| IO_FIX | varchar(64) | YES | | NULL | |
| IS_OLD | varchar(3) | YES | | NULL | |
| FREE_PAGE_CLOCK | bigint(21) unsigned | NO | | 0 | |
+---------------------+---------------------+------+-----+---------+-------+SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU\G
...
*************************** 6. row ***************************
POOL_ID: 0
LRU_POSITION: 5
SPACE: 0
PAGE_NUMBER: 11
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 2046835
OLDEST_MODIFICATION: 0
ACCESS_TIME: 2585566280
TABLE_NAME: `SYS_INDEXES`
INDEX_NAME: CLUST_IND
NUMBER_RECORDS: 57
DATA_SIZE: 4016
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_BUFFER_POOL_PAGES table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains a record for each page in the buffer pool.
It has the following columns:
PAGE_TYPE
Type of page; one of index, undo_log, inode, ibuf_free_list, allocated, bitmap, sys, trx_sys, fsp_hdr, xdes, blob, zblob, zblob2 and unknown.
SPACE_ID
Tablespace ID.
PAGE_NO
Page offset within tablespace.
LRU_POSITION
Page position in the LRU (least-recently-used) list.
FIX_COUNT
Page reference count, incremented each time the page is accessed. 0 if the page is not currently being accessed.
FLUSH_TYPE
Flush type of the most recent flush.0 (LRU), 2 (flush_list)
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_BUFFER_POOL_PAGES_BLOB table is a Percona enchancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about buffer pool blob pages.
It has the following columns:
SPACE_ID
Tablespace ID.
PAGE_NO
Page offset within tablespace.
COMPRESSED
1 if the blob contains compressed data, 0 if not.
PART_LEN
Page data length.
NEXT_PAGE_NO
Next page number.
LRU_POSITION
Page position in the LRU (least-recently-used) list.
FIX_COUNT
Page reference count, incremented each time the page is accessed. 0 if the page is not currently being accessed.
FLUSH_TYPE
Flush type of the most recent flush.0 (LRU), 2 (flush_list)
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_BUFFER_POOL_PAGES table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about buffer pool index pages.
It has the following columns:
INDEX_ID
Index name
SPACE_ID
Tablespace ID
PAGE_NO
Page offset within tablespace.
N_RECS
Number of user records on the page.
DATA_SIZE
Total data size in bytes of records in the page.
HASHED
1 if the block is in the adaptive hash index, 0 if not.
ACCESS_TIME
Page's last access time.
MODIFIED
1 if the page has been modified since being loaded, 0 if not.
DIRTY
1 if the page has been modified since it was last flushed, 0 if not
OLD
1 if the page in the in the old blocks of the LRU (least-recently-used) list, 0 if not.
LRU_POSITION
Position in the LRU (least-recently-used) list.
FIX_COUNT
Page reference count, incremented each time the page is accessed. 0 if the page is not currently being accessed.
FLUSH_TYPE
Flush type of the most recent flush.0 (LRU), 2 (flush_list)
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_BUFFER_POOL_STATS table contains information about pages in the buffer pool, similar to what is returned with the SHOW ENGINE INNODB STATUS statement.
The PROCESS privilege is required to view the table.
It has the following columns:
POOL_ID
Buffer Pool identifier. From MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
POOL_SIZE
Size in pages of the buffer pool.
FREE_BUFFERS
Number of free pages in the buffer pool.
DATABASE_PAGES
Total number of pages in the buffer pool.
OLD_DATABASE_PAGES
Number of pages in the old sublist.
MODIFIED_DATABASE_PAGES
Number of dirty pages.
PENDING_DECOMPRESS
Number of pages pending decompression.
PENDING_READS
Pending buffer pool level reads.
PENDING_FLUSH_LRU
Number of pages in the LRU pending flush.
PENDING_FLUSH_LIST
Number of pages in the flush list pending flush.
PAGES_MADE_YOUNG
Pages moved from the old sublist to the new sublist.
PAGES_NOT_MADE_YOUNG
Pages that have remained in the old sublist without moving to the new sublist.
PAGES_MADE_YOUNG_RATE
Hits that cause blocks to move to the top of the new sublist.
PAGES_MADE_NOT_YOUNG_RATE
Hits that do not cause blocks to move to the top of the new sublist due to the innodb_old_blocks delay not being met.
NUMBER_PAGES_READ
Number of pages read.
NUMBER_PAGES_CREATED
Number of pages created.
NUMBER_PAGES_WRITTEN
Number of pages written.
PAGES_READ_RATE
Number of pages read since the last printout divided by the time elapsed, giving pages read per second.
PAGES_CREATE_RATE
Number of pages created since the last printout divided by the time elapsed, giving pages created per second.
PAGES_WRITTEN_RATE
Number of pages written since the last printout divided by the time elapsed, giving pages written per second.
NUMBER_PAGES_GET
Number of logical read requests.
HIT_RATE
Buffer pool hit rate.
YOUNG_MAKE_PER_THOUSAND_GETS
For every 1000 gets, the number of pages made young.
NOT_YOUNG_MAKE_PER_THOUSAND_GETS
For every 1000 gets, the number of pages not made young.
NUMBER_PAGES_READ_AHEAD
Number of pages read ahead.
NUMBER_READ_AHEAD_EVICTED
Number of pages read ahead by the read-ahead thread that were later evicted without being accessed by any queries.
READ_AHEAD_RATE
Pages read ahead since the last printout divided by the time elapsed, giving read-ahead rate per second.
READ_AHEAD_EVICTED_RATE
Read-ahead pages not accessed since the last printout divided by time elapsed, giving the number of read-ahead pages evicted without access per second.
LRU_IO_TOTAL
Total least-recently used I/O.
LRU_IO_CURRENT
Least-recently used I/O for the current interval.
UNCOMPRESS_TOTAL
Total number of pages decompressed.
UNCOMPRESS_CURRENT
Number of pages decompressed in the current interval
DESC information_schema.innodb_buffer_pool_stats;
+----------------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------------+------+-----+---------+-------+
| POOL_ID | bigint(21) unsigned | NO | | 0 | |
| POOL_SIZE | bigint(21) unsigned | NO | | 0 | |
| FREE_BUFFERS | bigint(21) unsigned | NO | | 0 | |
| DATABASE_PAGES | bigint(21) unsigned | NO | | 0 | |
| OLD_DATABASE_PAGES | bigint(21) unsigned | NO | | 0 | |
| MODIFIED_DATABASE_PAGES | bigint(21) unsigned | NO | | 0 | |
| PENDING_DECOMPRESS | bigint(21) unsigned | NO | | 0 | |
| PENDING_READS | bigint(21) unsigned | NO | | 0 | |
| PENDING_FLUSH_LRU | bigint(21) unsigned | NO | | 0 | |
| PENDING_FLUSH_LIST | bigint(21) unsigned | NO | | 0 | |
| PAGES_MADE_YOUNG | bigint(21) unsigned | NO | | 0 | |
| PAGES_NOT_MADE_YOUNG | bigint(21) unsigned | NO | | 0 | |
| PAGES_MADE_YOUNG_RATE | double | NO | | 0 | |
| PAGES_MADE_NOT_YOUNG_RATE | double | NO | | 0 | |
| NUMBER_PAGES_READ | bigint(21) unsigned | NO | | 0 | |
| NUMBER_PAGES_CREATED | bigint(21) unsigned | NO | | 0 | |
| NUMBER_PAGES_WRITTEN | bigint(21) unsigned | NO | | 0 | |
| PAGES_READ_RATE | double | NO | | 0 | |
| PAGES_CREATE_RATE | double | NO | | 0 | |
| PAGES_WRITTEN_RATE | double | NO | | 0 | |
| NUMBER_PAGES_GET | bigint(21) unsigned | NO | | 0 | |
| HIT_RATE | bigint(21) unsigned | NO | | 0 | |
| YOUNG_MAKE_PER_THOUSAND_GETS | bigint(21) unsigned | NO | | 0 | |
| NOT_YOUNG_MAKE_PER_THOUSAND_GETS | bigint(21) unsigned | NO | | 0 | |
| NUMBER_PAGES_READ_AHEAD | bigint(21) unsigned | NO | | 0 | |
| NUMBER_READ_AHEAD_EVICTED | bigint(21) unsigned | NO | | 0 | |
| READ_AHEAD_RATE | double | NO | | 0 | |
| READ_AHEAD_EVICTED_RATE | double | NO | | 0 | |
| LRU_IO_TOTAL | bigint(21) unsigned | NO | | 0 | |
| LRU_IO_CURRENT | bigint(21) unsigned | NO | | 0 | |
| UNCOMPRESS_TOTAL | bigint(21) unsigned | NO | | 0 | |
| UNCOMPRESS_CURRENT | bigint(21) unsigned | NO | | 0 | |
+----------------------------------+---------------------+------+-----+---------+-------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_CHANGED_PAGES Table contains data about modified pages from the bitmap file. It is updated at checkpoints by the log tracking thread parsing the log, so does not contain real-time data.
The number of records is limited by the value of the innodb_max_changed_pages system variable.
The PROCESS privilege is required to view the table.
It has the following columns:
SPACE_ID
Modified page space id
PAGE_ID
Modified page id
START_LSN
Interval start after which page was changed (equal to checkpoint LSN)
END_LSN
Interval end before which page was changed (equal to checkpoint LSN)
This page is licensed: CC BY-SA / Gnu FDL
The INNODB_CMP and INNODB_CMP_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables.
The PROCESS privilege is required to query this table.
These tables contain the following columns:
PAGE_SIZE
Compressed page size, in bytes. This value is unique in the table; other values are totals which refer to pages of this size.
COMPRESS_OPS
How many times a page of the size PAGE_SIZE has been compressed. This happens when a new page is created because the compression log runs out of space. This value includes both successful operations and compression failures.
COMPRESS_OPS_OK
How many times a page of the size PAGE_SIZE has been successfully compressed. This value should be as close as possible to COMPRESS_OPS. If it is notably lower, either avoid compressing some tables, or increase the KEY_BLOCK_SIZE for some compressed tables.
COMPRESS_TIME
Time (in seconds) spent to compress pages of the size PAGE_SIZE. This value includes time spent in compression failures.
UNCOMPRESS_OPS
How many times a page of the size PAGE_SIZE has been uncompressed. This happens when an uncompressed version of a page is created in the buffer pool, or when a compression failure occurs.
UNCOMPRESS_TIME
Time (in seconds) spent to uncompress pages of the size PAGE_SIZE.
These tables can be used to measure the effectiveness of XtraDB/InnoDB table compression. When you have to decide a value for KEY_BLOCK_SIZE, you can create more than one version of the table (one for each candidate value) and run a realistic workload on them. Then, these tables can be used to see how the operations performed with different page sizes.
INNODB_CMP and INNODB_CMP_RESET have the same columns and always contain the same values, but when INNODB_CMP_RESET is queried, both the tables are cleared. INNODB_CMP_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMP can be used to see the cumulated statistics.
SELECT * FROM information_schema.INNODB_CMP\G
**************************** 1. row *****************************
page_size: 1024
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
...Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables contain status information on compressed pages in the buffer pool (see InnoDB COMPRESSED format).
The PROCESS privilege is required to query this table.
These tables contain the following columns:
PAGE_SIZE
Compressed page size, in bytes. This value is unique in the table; other values are totals which refer to pages of this size.
BUFFER_POOL_INSTANCE
Buffer Pool identifier. From MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
PAGES_USED
Number of pages of the size PAGE_SIZE which are currently in the buffer pool.
PAGES_FREE
Number of pages of the size PAGE_SIZE which are currently free, and thus are available for allocation. This value represents the buffer pool's fragmentation. A totally unfragmented buffer pool has at most 1 free page.
RELOCATION_OPS
How many times a page of the size PAGE_SIZE has been relocated. This happens when data exceeds a page (because a row must be copied into a new page) and when two pages are merged (because their data shrunk and can now be contained in one page).
RELOCATION_TIME
Time (in seconds) spent in relocation operations for pages of the size PAGE_SIZE. This column is reset when the INNODB_CMPMEM_RESET table is queried.
These tables can be used to measure the effectiveness of InnoDB table compression. When you have to decide a value for KEY_BLOCK_SIZE, you can create more than one version of the table (one for each candidate value) and run a realistic workload on them. Then, these tables can be used to see how the operations performed with different page sizes.
INNODB_CMPMEM and INNODB_CMPMEM_RESET have the same columns and always contain the same values, but when INNODB_CMPMEM_RESET is queried, the RELOCATION_TIME column from both the tables are cleared. INNODB_CMPMEM_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMPMEM can be used to see the cumulated statistics.
SELECT * FROM information_schema.INNODB_CMPMEM\G
********************** 1. row **********************
page_size: 1024
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
reloacation_ops: 0
relocation_time: 0Other tables that can be used to monitor InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_FT_BEING_DELETED table is only used while document ID's in the related INNODB_FT_DELETED are being removed from an InnoDB fulltext index while an OPTIMIZE TABLE is underway. At all other times the table will be empty.
The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.
It has the following column:
DOC_ID
Document ID of the row being deleted. Either an underlying ID value, or a sequence value generated by InnoDB if no usable option exists.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_FT_CONFIG table contains InnoDB fulltext index metadata.
The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.
It has the following columns:
KEY
Metadata item name.
VALUE
Associated value.
SELECT * FROM INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180 |
| synced_doc_id | 6 |
| last_optimized_word | |
| deleted_doc_count | 0 |
| total_word_count | |
| optimize_start_time | |
| optimize_end_time | |
| stopword_table_name | |
| use_stopword | 1 |
| table_state | 0 |
+---------------------------+-------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_FT_DEFAULT_STOPWORD table contains a list of default stopwords used when creating an InnoDB fulltext index.
The PROCESS privilege is required to view the table.
It has the following column:
VALUE
Default stopword for an InnoDB fulltext index. Setting either the innodb_ft_server_stopword_table or the innodb_ft_user_stopword_table system variable will override this.
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD\G
*************************** 1. row ***************************
value: a
*************************** 2. row ***************************
value: about
*************************** 3. row ***************************
value: an
*************************** 4. row ***************************
value: are
...
*************************** 36. row ***************************
value: wwwThis page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_FT_DELETED table contains rows that have been deleted from an InnoDB fulltext index. This information is then used to filter results on subsequent searches, removing the need to expensively reorganise the index each time a row is deleted.
The fulltext index is then only reorganized when an OPTIMIZE TABLE statement is underway. The related INNODB_FT_BEING_DELETED table contains rows being deleted while an OPTIMIZE TABLE is in the process of running.
The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.
It has the following column:
DOC_ID
Document ID of the deleted row deleted. Either an underlying ID value, or a sequence value generated by InnoDB if no usable option exists.
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 2 |
+--------+
DELETE FROM test.ft_innodb LIMIT 1;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 2 |
| 3 |
+--------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_FT_INDEX_CACHE table contains information about rows that have recently been inserted into an InnoDB fulltext index. To avoid re-organizing the fulltext index each time a change is made, which would be very expensive, new changes are stored separately and only integrated when an OPTIMIZE TABLE is run.
The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.
It has the following columns:
WORD
Word from the text of a newly added row. Words can appear multiple times in the table, once per DOC_ID and POSITION combination.
FIRST_DOC_ID
First document ID where this word appears in the index.
LAST_DOC_ID
Last document ID where this word appears in the index.
DOC_COUNT
Number of rows containing this word in the index.
DOC_ID
Document ID of the newly added row, either an appropriate ID column or an internal InnoDB value.
POSITION
Position of this word instance within the DOC_ID, as an offset added to the previous POSITION instance.
Note that for OPTIMIZE TABLE to process InnoDB fulltext index data, the innodb_optimize_fulltext_only system variable needs to be set to 1. When this is done, and an OPTIMIZE TABLE statement run, the INNODB_FT_INDEX_CACHE table will be emptied, and the INNODB_FT_INDEX_TABLE table will be updated.
SELECT * FROM INNODB_FT_INDEX_CACHE;
+------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| and | 4 | 4 | 1 | 4 | 0 |
| arrived | 4 | 4 | 1 | 4 | 20 |
| ate | 1 | 1 | 1 | 1 | 4 |
| everybody | 1 | 1 | 1 | 1 | 8 |
| goldilocks | 4 | 4 | 1 | 4 | 9 |
| hungry | 3 | 3 | 1 | 3 | 8 |
| then | 4 | 4 | 1 | 4 | 4 |
| wicked | 2 | 2 | 1 | 2 | 4 |
| witch | 2 | 2 | 1 | 2 | 11 |
+------------+--------------+-------------+-----------+--------+----------+
9 rows in set (0.00 sec)
INSERT INTO test.ft_innodb VALUES(3,'And she ate a pear');
SELECT * FROM INNODB_FT_INDEX_CACHE;
+------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| and | 4 | 5 | 2 | 4 | 0 |
| and | 4 | 5 | 2 | 5 | 0 |
| arrived | 4 | 4 | 1 | 4 | 20 |
| ate | 1 | 5 | 2 | 1 | 4 |
| ate | 1 | 5 | 2 | 5 | 8 |
| everybody | 1 | 1 | 1 | 1 | 8 |
| goldilocks | 4 | 4 | 1 | 4 | 9 |
| hungry | 3 | 3 | 1 | 3 | 8 |
| pear | 5 | 5 | 1 | 5 | 14 |
| she | 5 | 5 | 1 | 5 | 4 |
| then | 4 | 4 | 1 | 4 | 4 |
| wicked | 2 | 2 | 1 | 2 | 4 |
| witch | 2 | 2 | 1 | 2 | 11 |
+------------+--------------+-------------+-----------+--------+----------+OPTIMIZE TABLE test.ft_innodb\G
*************************** 1. row ***************************
Table: test.ft_innodb
Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
Table: test.ft_innodb
Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (2.24 sec)
SELECT * FROM INNODB_FT_INDEX_CACHE;
+------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| and | 4 | 5 | 2 | 4 | 0 |
| and | 4 | 5 | 2 | 5 | 0 |
| arrived | 4 | 4 | 1 | 4 | 20 |
| ate | 1 | 5 | 2 | 1 | 4 |
| ate | 1 | 5 | 2 | 5 | 8 |
| everybody | 1 | 1 | 1 | 1 | 8 |
| goldilocks | 4 | 4 | 1 | 4 | 9 |
| hungry | 3 | 3 | 1 | 3 | 8 |
| pear | 5 | 5 | 1 | 5 | 14 |
| she | 5 | 5 | 1 | 5 | 4 |
| then | 4 | 4 | 1 | 4 | 4 |
| wicked | 2 | 2 | 1 | 2 | 4 |
| witch | 2 | 2 | 1 | 2 | 11 |
+------------+--------------+-------------+-----------+--------+----------+
13 rows in set (0.00 sec)The OPTIMIZE TABLE statement has no effect, because the innodb_optimize_fulltext_only variable wasn't set:
SHOW VARIABLES LIKE 'innodb_optimize_fulltext_only';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | OFF |
+-------------------------------+-------+
SET GLOBAL innodb_optimize_fulltext_only =1;
OPTIMIZE TABLE test.ft_innodb;
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.ft_innodb | optimize | status | OK |
+----------------+----------+----------+----------+
SELECT * FROM INNODB_FT_INDEX_CACHE;
Empty set (0.00 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_FT_INDEX_TABLE table contains information about InnoDB fulltext indexes. To avoid re-organizing the fulltext index each time a change is made, which would be very expensive, new changes are stored separately and only integrated when an OPTIMIZE TABLE is run. See the INNODB_FT_INDEX_CACHE table.
The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.
It has the following columns:
WORD
Word from the text of a column with a fulltext index. Words can appear multiple times in the table, once per DOC_ID and POSITION combination.
FIRST_DOC_ID
First document ID where this word appears in the index.
LAST_DOC_ID
Last document ID where this word appears in the index.
DOC_COUNT
Number of rows containing this word in the index.
DOC_ID
Document ID of the newly added row, either an appropriate ID column or an internal InnoDB value.
POSITION
Position of this word instance within the DOC_ID, as an offset added to the previous POSITION instance.
Note that for OPTIMIZE TABLE to process InnoDB fulltext index data, the innodb_optimize_fulltext_only system variable needs to be set to 1. When this is done, and an OPTIMIZE TABLE statement run, the INNODB_FT_INDEX_CACHE table will be emptied, and the INNODB_FT_INDEX_TABLE table will be updated.
SELECT * FROM INNODB_FT_INDEX_TABLE;
Empty set (0.00 sec)
SET GLOBAL innodb_optimize_fulltext_only =1;
OPTIMIZE TABLE test.ft_innodb;
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.ft_innodb | optimize | status | OK |
+----------------+----------+----------+----------+
SELECT * FROM INNODB_FT_INDEX_TABLE;
+------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| and | 4 | 5 | 2 | 4 | 0 |
| and | 4 | 5 | 2 | 5 | 0 |
| arrived | 4 | 4 | 1 | 4 | 20 |
| ate | 1 | 5 | 2 | 1 | 4 |
| ate | 1 | 5 | 2 | 5 | 8 |
| everybody | 1 | 1 | 1 | 1 | 8 |
| goldilocks | 4 | 4 | 1 | 4 | 9 |
| hungry | 3 | 3 | 1 | 3 | 8 |
| pear | 5 | 5 | 1 | 5 | 14 |
| she | 5 | 5 | 1 | 5 | 4 |
| then | 4 | 4 | 1 | 4 | 4 |
| wicked | 2 | 2 | 1 | 2 | 4 |
| witch | 2 | 2 | 1 | 2 | 11 |
+------------+--------------+-------------+-----------+--------+----------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_LOCK_WAITS table contains information about blocked InnoDB transactions. The PROCESS privilege is required to view the table.
It contains the following columns:
REQUESTING_TRX_ID
Requesting transaction ID from the INNODB_TRX table.
REQUESTED_LOCK_ID
Lock ID from the INNODB.LOCKS table for the waiting transaction.
BLOCKING_TRX_ID
Blocking transaction ID from the INNODB_TRX table.
BLOCKING_LOCK_ID
Lock ID from the INNODB.LOCKS table of a lock held by a transaction that is blocking another transaction.
The table is often used in conjunction with the INNODB_LOCKS and INNODB_TRX tables to diagnose problematic locks and transactions.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_LOCKS table stores information about locks that InnoDB transactions have requested but not yet acquired, or that are blocking another transaction.
It has the following columns:
LOCK_ID
Lock ID number - the format is not fixed, so do not rely upon the number for information.
LOCK_TRX_ID
Lock's transaction ID. Matches the INNODB_TRX.TRX_ID column.
LOCK_MODE
Lock mode. One of S (shared), X (exclusive), IS (intention shared), IX (intention exclusive row lock), S_GAP (shared gap lock), X_GAP (exclusive gap lock), IS_GAP (intention shared gap lock), IX_GAP (intention exclusive gap lock) or AUTO_INC (auto-increment table level lock).
LOCK_TYPE
Whether the lock is RECORD (row level) or TABLE level.
LOCK_TABLE
Name of the locked table,or table containing locked rows.
LOCK_INDEX
Index name if a RECORD LOCK_TYPE, or NULL if not.
LOCK_SPACE
Tablespace ID if a RECORD LOCK_TYPE, or NULL if not.
LOCK_PAGE
Locked record page number if a RECORD LOCK_TYPE, or NULL if not.
LOCK_REC
Locked record heap number if a RECORD LOCK_TYPE, or NULL if not.
LOCK_DATA
Locked record primary key as an SQL string if a RECORD LOCK_TYPE, or NULL if not. If no primary key exists, the internal InnoDB row_id number is instead used. To avoid unnecessary IO, also NULL if the locked record page is not in the buffer pool
The table is often used in conjunction with the INNODB_LOCK_WAITS and INNODB_TRX tables to diagnose problematic locks and transactions
-- session 1
START TRANSACTION;
UPDATE t SET id = 15 WHERE id = 10;
-- session 2
DELETE FROM t WHERE id = 10;
-- session 1
USE information_schema;
SELECT l.*, t.*
FROM information_schema.INNODB_LOCKS l
JOIN information_schema.INNODB_TRX t
ON l.lock_trx_id = t.trx_id
WHERE trx_state = 'LOCK WAIT' \G
*************************** 1. row ***************************
lock_id: 840:40:3:2
lock_trx_id: 840
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: PRIMARY
lock_space: 40
lock_page: 3
lock_rec: 2
lock_data: 10
trx_id: 840
trx_state: LOCK WAIT
trx_started: 2019-12-23 18:43:46
trx_requested_lock_id: 840:40:3:2
trx_wait_started: 2019-12-23 18:43:46
trx_weight: 2
trx_mysql_thread_id: 46
trx_query: DELETE FROM t WHERE id = 10
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_METRICS table contains a list of useful InnoDB performance metrics. Each row in the table represents an instrumented counter that can be stopped, started and reset, and which can be grouped together by module.
The PROCESS privilege is required to view the table.
It has the following columns:
NAME
Unique counter name.
SUBSYSTEM
InnoDB subsystem. See below for the matching module to use to enable/disable monitoring this subsytem with the innodb_monitor_enable and innodb_monitor_disable system variables.
COUNT
Count since being enabled.
MAX_COUNT
Maximum value since being enabled.
MIN_COUNT
Minimum value since being enabled.
AVG_COUNT
Average value since being enabled.
COUNT_RESET
Count since last being reset.
MAX_COUNT_RESET
Maximum value since last being reset.
MIN_COUNT_RESET
Minimum value since last being reset.
AVG_COUNT_RESET
Average value since last being reset.
TIME_ENABLED
Time last enabled.
TIME_DISABLED
Time last disabled
TIME_ELAPSED
Time since enabled
TIME_RESET
Time last reset.
ENABLED
1 if enabled, 0 otherwise
TYPE
Item type; one of counter, value, status_counter, set_owner, set_member.
COMMENT
Counter description.
Note: In MariaDB 10.4 and earlier the ENABLED column was called STATUS.
Most of the counters are disabled by default. To enable them, use the innodb_monitor_enable system variable. You can either enable a variable by its name, for example:
SET GLOBAL innodb_monitor_enable = icp_match;or enable a number of counters grouped by module. The SUBSYSTEM field indicates which counters are grouped together, but the following module names need to be used:
module_metadata
metadata
module_lock
lock
module_buffer
buffer
module_buf_page
buffer_page_io
module_os
os
module_trx
transaction
module_purge
purge
module_compress
compression
module_file
file_system
module_index
index
module_adaptive_hash
adaptive_hash_index From MariaDB 10.6.2, if innodb_adaptive_hash_index is disabled (the default), adaptive_hash_index will not be updated.
module_ibuf_system
change_buffer
module_srv
server
module_ddl
ddl
module_dml
dml
module_log
recovery
module_icp
icp
There are four counters in the icp subsystem:
SELECT NAME, SUBSYSTEM FROM INNODB_METRICS WHERE SUBSYSTEM='icp';
+------------------+-----------+
| NAME | SUBSYSTEM |
+------------------+-----------+
| icp_attempts | icp |
| icp_no_match | icp |
| icp_out_of_range | icp |
| icp_match | icp |
+------------------+-----------+To enable them all, use the associated module name from the table above, module_icp.
SET GLOBAL innodb_monitor_enable = module_icp;The % wildcard, used to represent any number of characters, can also be used when naming counters, for example:
SET GLOBAL innodb_monitor_enable = 'buffer%'To disable counters, use the innodb_monitor_disable system variable, using the same naming rules as described above for enabling.
Counter status is not persistent, and will be reset when the server restarts. It is possible to use the options on the command line, or the innodb_monitor_enable option only in a configuration file.
Counters can also be reset. Resetting sets all the *_COUNT_RESET values to zero, while leaving the *_COUNT values, which perform counts since the counter was enabled, untouched. Resetting is performed with the innodb_monitor_reset (for individual counters) and innodb_monitor_reset_all (for all counters) system variables.
MariaDB starting with 10.6
From MariaDB 10.6, the interface was simplified by removing the following:
buffer_LRU_batches_flush
buffer_LRU_batch_flush_pages
buffer_LRU_batches_evict
buffer_LRU_batch_evict_pages
and by making the following reflect the status variables:
buffer_LRU_batch_flush_total_pages: innodb_buffer_pool_pages_LRU_flushed
buffer_LRU_batch_evict_total_pages: innodb_buffer_pool_pages_LRU_freed
The intention is to eventually remove the interface entirely (see MDEV-15706).
SELECT name,subsystem,type,comment FROM INFORMATION_SCHEMA.INNODB_METRICS\G
*************************** 1. row ***************************
name: metadata_table_handles_opened
subsystem: metadata
type: counter
comment: Number of table handles opened
*************************** 2. row ***************************
name: lock_deadlocks
subsystem: lock
type: value
comment: Number of deadlocks
*************************** 3. row ***************************
name: lock_timeouts
subsystem: lock
type: value
comment: Number of lock timeouts
*************************** 4. row ***************************
name: lock_rec_lock_waits
subsystem: lock
type: counter
comment: Number of times enqueued into record lock wait queue
*************************** 5. row ***************************
name: lock_table_lock_waits
subsystem: lock
type: counter
comment: Number of times enqueued into table lock wait queue
*************************** 6. row ***************************
name: lock_rec_lock_requests
subsystem: lock
type: counter
comment: Number of record locks requested
*************************** 7. row ***************************
name: lock_rec_lock_created
subsystem: lock
type: counter
comment: Number of record locks created
*************************** 8. row ***************************
name: lock_rec_lock_removed
subsystem: lock
type: counter
comment: Number of record locks removed from the lock queue
*************************** 9. row ***************************
name: lock_rec_locks
subsystem: lock
type: counter
comment: Current number of record locks on tables
*************************** 10. row ***************************
name: lock_table_lock_created
subsystem: lock
type: counter
comment: Number of table locks created
...
*************************** 207. row ***************************
name: icp_attempts
subsystem: icp
type: counter
comment: Number of attempts for index push-down condition checks
*************************** 208. row ***************************
name: icp_no_match
subsystem: icp
type: counter
comment: Index push-down condition does not match
*************************** 209. row ***************************
name: icp_out_of_range
subsystem: icp
type: counter
comment: Index push-down condition out of range
*************************** 210. row ***************************
name: icp_match
subsystem: icp
type: counter
comment: Index push-down condition matchesThis page is licensed: CC BY-SA / Gnu FDL
The INNODB_MUTEXES table monitors mutex and rw locks waits. It has the following columns:
NAME
Name of the lock, as it appears in the source code.
CREATE_FILE
File name of the mutex implementation.
CREATE_LINE
Line number of the mutex implementation.
OS_WAITS
How many times the mutex occurred.
The CREATE_FILE and CREATE_LINE columns depend on the InnoDB/XtraDB version.
The table provides information about all columns listed in the previous table.
The table provides information about rw_lock_t, not about any mutexes.
The SHOW ENGINE INNODB STATUS statement provides similar information.
SELECT * FROM INNODB_MUTEXES;
+------------------------------+---------------------+-------------+----------+
| NAME | CREATE_FILE | CREATE_LINE | OS_WAITS |
+------------------------------+---------------------+-------------+----------+
| &dict_sys->mutex | dict0dict.cc | 989 | 2 |
| &buf_pool->flush_state_mutex | buf0buf.cc | 1388 | 1 |
| &log_sys->checkpoint_lock | log0log.cc | 1014 | 2 |
| &block->lock | combined buf0buf.cc | 1120 | 1 |
+------------------------------+---------------------+-------------+----------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_COLUMNS table contains information about InnoDB fields.
The PROCESS privilege is required to view the table.
It has the following columns:
TABLE_ID
Table identifier, matching the value from INNODB_SYS_TABLES.TABLE_ID.
NAME
Column name.
POS
Ordinal position of the column in the table, starting from 0. This value is adjusted when columns are added or removed.
MTYPE
Numeric column type identifier, (see the table below for an explanation of its values).
PRTYPE
Binary value of the InnoDB precise type, representing the data type, character set code and nullability.
LEN
Column length. For multi-byte character sets, represents the length in bytes.
The column MTYPE uses a numeric column type identifier, which has the following values:
SELECT * FROM information_schema.INNODB_SYS_COLUMNS LIMIT 3\G
*************************** 1. row ***************************
TABLE_ID: 11
NAME: ID
POS: 0
MTYPE: 1
PRTYPE: 524292
LEN: 0
*************************** 2. row ***************************
TABLE_ID: 11
NAME: FOR_NAME
POS: 0
MTYPE: 1
PRTYPE: 524292
LEN: 0
*************************** 3. row ***************************
TABLE_ID: 11
NAME: REF_NAME
POS: 0
MTYPE: 1
PRTYPE: 524292
LEN: 0
3 rows in set (0.00 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_DATAFILES table contains information about InnoDB datafile paths. It was intended to provide metadata for tablespaces inside InnoDB tables, which was never implemented in MariaDB and was removed in MariaDB 10.6. The PROCESS privilege was required to view the table.
It contains the following columns:
SPACE
Numeric tablespace. Matches the INNODB_SYS_TABLES.SPACE value.
PATH
Tablespace datafile path.
SELECT * FROM INNODB_SYS_DATAFILES;
+-------+--------------------------------+
| SPACE | PATH |
+-------+--------------------------------+
| 19 | ./test/t2.ibd |
| 20 | ./test/t3.ibd |
...
| 68 | ./test/animals.ibd |
| 69 | ./test/animal_count.ibd |
| 70 | ./test/t.ibd |
+-------+--------------------------------+This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_FIELDS table contains information about fields that are part of an InnoDB index.
The PROCESS privilege is required to view the table.
It has the following columns:
INDEX_ID
Index identifier, matching the value from INNODB_SYS_INDEXES.INDEX_ID.
NAME
Field name, matching the value from INNODB_SYS_COLUMNS.NAME.
POS
Ordinal position of the field within the index, starting from 0. This is adjusted as columns are removed.
SELECT * FROM information_schema.INNODB_SYS_FIELDS LIMIT 3\G
*************************** 1. row ***************************
INDEX_ID: 11
NAME: ID
POS: 0
*************************** 2. row ***************************
INDEX_ID: 12
NAME: FOR_NAME
POS: 0
*************************** 3. row ***************************
INDEX_ID: 13
NAME: REF_NAME
POS: 0
3 rows in set (0.00 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_FOREIGN table contains information about InnoDB foreign keys.
The PROCESS privilege is required to view the table.
It has the following columns:
ID
Database name and foreign key name.
FOR_NAME
Database and table name of the foreign key child.
REF_NAME
Database and table name of the foreign key parent.
N_COLS
Number of foreign key index columns.
TYPE
Bit flag providing information about the foreign key.
The TYPE column provides a bit flag with information about the foreign key. This information is OR'ed together to read:
1
ON DELETE CASCADE
2
ON UPDATE SET NULL
4
ON UPDATE CASCADE
8
ON UPDATE SET NULL
16
ON DELETE NO ACTION
32
ON UPDATE NO ACTION
SELECT * FROM INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: mysql/innodb_index_stats_ibfk_1
FOR_NAME: mysql/innodb_index_stats
REF_NAME: mysql/innodb_table_stats
N_COLS: 2
TYPE: 0
...This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_FOREIGN_COLS table contains information about InnoDB foreign key columns.
The PROCESS privilege is required to view the table.
It has the following columns:
ID
Foreign key index associated with this column, matching the INNODB_SYS_FOREIGN.ID field.
FOR_COL_NAME
Child column name.
REF_COL_NAME
Parent column name.
POS
Ordinal position of the column in the table, starting from 0.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_INDEXES table contains information about InnoDB indexes.
The PROCESS privilege is required to view the table.
It has the following columns:
INDEX_ID
bigint(21) unsigned
NO
0
A unique index identifier.
NAME
varchar(64)
NO
Index name, lowercase for all user-created indexes, or uppercase for implicitly-created indexes; PRIMARY (primary key), GEN_CLUST_INDEX (index representing primary key where there isn't one), ID_IND, FOR_IND (validating foreign key constraint) , REF_IND.
TYPE
int(11)
NO
0
Numeric type identifier; one of 0 (secondary index), 1 (clustered index), 2 (unique index), 3 (primary index), 32 (full-text index).
N_FIELDS
int(11)
NO
0
Number of columns in the index. GEN_CLUST_INDEX's have a value of 0 as the index is not based on an actual column in the table.
PAGE_NO
int(11)
NO
0
Index B-tree's root page number. -1 (unused) for full-text indexes, as they are laid out over several auxiliary tables.
SPACE
int(11)
NO
0
Tablespace identifier where the index resides. 0 represents the InnoDB system tablespace, while any other value represents a table created in file-per-table mode (see the innodb_file_per_table system variable). Remains unchanged after a TRUNCATE TABLE statement, and not necessarily unique.
MERGE_THRESHOLD
int(11)
NO
0
SELECT * FROM information_schema.INNODB_SYS_INDEXES LIMIT 3\G
*************************** 1. row ***************************
INDEX_ID: 11
NAME: ID_IND
TABLE_ID: 11
TYPE: 3
N_FIELDS: 1
PAGE_NO: 302
SPACE: 0
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
INDEX_ID: 12
NAME: FOR_IND
TABLE_ID: 11
TYPE: 0
N_FIELDS: 1
PAGE_NO: 303
SPACE: 0
MERGE_THRESHOLD: 50
*************************** 3. row ***************************
INDEX_ID: 13
NAME: REF_IND
TABLE_ID: 11
TYPE: 3
N_FIELDS: 1
PAGE_NO: 304
SPACE: 0
MERGE_THRESHOLD: 50
3 rows in set (0.00 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_SEMAPHORE_WAITS table is meant to contain information about current semaphore waits. At present it is not correctly populated. See MDEV-21330.
The PROCESS privilege is required to view the table.
It contains the following columns:
THREAD_ID
Thread id waiting for semaphore
OBJECT_NAME
Semaphore name
FILE
File name where semaphore was requested
LINE
Line number on above file
WAIT_TIME
Wait time
WAIT_OBJECT
WAIT_TYPE
Object type (mutex, rw-lock)
HOLDER_THREAD_ID
Holder thread id
HOLDER_FILE
File name where semaphore was acquired
HOLDER_LINE
Line number for above
CREATED_FILE
Creation file name
CREATED_LINE
Line number for above
WRITER_THREAD
Last write request thread id
RESERVATION_MODE
Reservation mode (shared, exclusive)
READERS
Number of readers if only shared mode
WAITERS_FLAG
Flags
LOCK_WORD
Lock word (for developers)
LAST_READER_FILE
Removed
LAST_READER_LINE
Removed
LAST_WRITER_FILE
Last writer file name
LAST_WRITER_LINE
Above line number
OS_WAIT_COUNT
Wait count
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_TABLES table contains information about InnoDB tables.
The PROCESS privilege is required to view the table.
It has the following columns:
TABLE_ID
bigint(21) unsigned
NO
0
Unique InnoDB table identifier.
NAME
varchar(655)
NO
Database and table name, or the uppercase InnoDB system table name.
N_COLS
int(11) unsigned (>= MariaDB 10.5) int(11) (<= MariaDB 10.4)
NO
0
Number of columns in the table. The count includes two or three hidden InnoDB system columns, appended to the end of the column list: DB_ROW_ID (if there is no primary key or unique index on NOT NULL columns), DB_TRX_ID, DB_ROLL_PTR.
SPACE
int(11) unsigned (>= MariaDB 10.5) int(11) (<= MariaDB 10.4)
NO
0
Tablespace identifier where the index resides. 0 represents the InnoDB system tablespace, while any other value represents a table created in file-per-table mode (see the innodb_file_per_table system variable). Remains unchanged after a TRUNCATE TABLE statement.
ROW_FORMAT
enum('Redundant', 'Compact', 'Compressed', 'Dynamic') (>= MariaDB 10.5)varchar(12) (<= MariaDB 10.4)
YES
NULL
InnoDB storage format (Compact, Redundant, Dynamic, or Compressed).
ZIP_PAGE_SIZE
int(11) unsigned
NO
0
For Compressed tables, the zipped page size.
SPACE_TYPE
enum('Single','System') (>= MariaDB 10.5)varchar(10) (<= MariaDB 10.4)
YES
NULL
The flag field returns the dict_table_t::flags that correspond to the data dictionary record.
0
Set if ROW_FORMAT is not REDUNDANT.
1 to 4
0, except for ROW_FORMAT=COMPRESSED, where they will determine the KEY_BLOCK_SIZE (the compressed page size).
5
Set for ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.
6
Set if the DATA DIRECTORY attribute was present when the table was originally created.
7
Set if the page_compressed attribute is present.
8 to 11
Determine the page_compression_level.
12 13
Normally 00, but 11 for "no-rollback tables" (MariaDB 10.3 CREATE SEQUENCE). In MariaDB 10.1, these bits could be 01 or 10 for ATOMIC_WRITES=ON or ATOMIC_WRITES=OFF.
Note that the table flags returned here are not the same as tablespace flags (FSP_SPACE_FLAGS).
SELECT * FROM information_schema.INNODB_SYS_TABLES LIMIT 2\G
*************************** 1. row ***************************
TABLE_ID: 14
NAME: SYS_DATAFILES
FLAG: 0
N_COLS: 5
SPACE: 0
ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
SPACE_TYPE: System
*************************** 2. row ***************************
TABLE_ID: 11
NAME: SYS_FOREIGN
FLAG: 0
N_COLS: 7
SPACE: 0
ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
SPACE_TYPE: System
2 rows in set (0.00 sec)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_TABLESPACES table contains information about InnoDB tablespaces. Until MariaDB 10.5 it was based on the internal SYS_TABLESPACES table. This internal table was removed in MariaDB 10.6.0, so this Information Schema table has been repurposed
to directly reflect the filesystem (fil_system.space_list).
The PROCESS privilege is required to view the table.
It has the following columns:
SPACE
Unique InnoDB tablespace identifier.
NAME
Database and table name separated by a backslash, or the uppercase InnoDB system table name.
FLAG
1 if a DATA DIRECTORY option has been specified in CREATE TABLE, otherwise 0.
FILE_FORMAT
InnoDB file format. Removed in MariaDB 10.3.1
ROW_FORMAT
InnoDB storage format used for this tablespace. If the Antelope file format is used, this value is always Compact or Redundant. When a table's checksum algorithm is full_crc32 (the default from MariaDB 10.5), the value can only be Compressed or NULL.
PAGE_SIZE
Page size in bytes for this tablespace. Until MariaDB 10.5.0, this was the value of the innodb_page_size variable. From MariaDB 10.6.0, contains the physical page size of a page (previously ZIP_PAGE_SIZE).
ZIP_PAGE_SIZE
Zip page size for this tablespace. Removed in MariaDB 10.6.0.
SPACE_TYPE
Tablespace type. Can be General for general tablespaces or Single for file-per-table tablespaces. Introduced MariaDB 10.2.1. Removed MariaDB 10.5.0.
FS_BLOCK_SIZE
File system block size. Introduced MariaDB 10.2.1.
FILE_SIZE
Maximum size of the file, uncompressed. Introduced MariaDB 10.2.1.
ALLOCATED_SIZE
Actual size of the file as per space allocated on disk. Introduced MariaDB 10.2.1.
FILENAME
Tablespace datafile path, previously part of the INNODB_SYS_DATAFILES table. Added in MariaDB 10.6.0.
DESC information_schema.innodb_sys_tablespaces;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| SPACE | int(11) unsigned | NO | | 0 | |
| NAME | varchar(655) | NO | | | |
| FLAG | int(11) unsigned | NO | | 0 | |
| ROW_FORMAT | varchar(22) | YES | | NULL | |
| PAGE_SIZE | int(11) unsigned | NO | | 0 | |
| ZIP_PAGE_SIZE | int(11) unsigned | NO | | 0 | |
| SPACE_TYPE | varchar(10) | YES | | NULL | |
| FS_BLOCK_SIZE | int(11) unsigned | NO | | 0 | |
| FILE_SIZE | bigint(21) unsigned | NO | | 0 | |
| ALLOCATED_SIZE | bigint(21) unsigned | NO | | 0 | |
+----------------+---------------------+------+-----+---------+-------+SELECT * FROM information_schema.INNODB_SYS_TABLESPACES LIMIT 2\G
*************************** 1. row ***************************
SPACE: 2
NAME: mysql/innodb_table_stats
FLAG: 33
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304
*************************** 2. row ***************************
SPACE: 3
NAME: mysql/innodb_index_stats
FLAG: 33
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_TABLESTATS table contains InnoDB status information. It can be used for developing new performance-related extensions, or high-level performance monitoring.
The PROCESS privilege is required to view the table.
Note that the MySQL InnoDB and Percona XtraDB versions of the tables differ (see XtraDB and InnoDB).
It contains the following columns:
TABLE_ID
Table ID, matching the INNODB_SYS_TABLES.TABLE_ID value.
SCHEMA
Database name (XtraDB only).
NAME
Table name, matching the INNODB_SYS_TABLES.NAME value.
STATS_INITIALIZED
Initialized if statistics have already been collected, otherwise Uninitialized.
NUM_ROWS
Estimated number of rows currently in the table. Updated after each statement modifying the data, but uncommited transactions mean it may not be accurate.
CLUST_INDEX_SIZE
Number of pages on disk storing the clustered index, holding InnoDB table data in primary key order, or NULL if not statistics yet collected.
OTHER_INDEX_SIZE
Number of pages on disk storing secondary indexes for the table, or NULL if not statistics yet collected.
MODIFIED_COUNTER
Number of rows modified by statements modifying data.
AUTOINC
Auto_increment value.
REF_COUNT
Countdown to zero, when table metadata can be removed from the table cache. (InnoDB only)
MYSQL_HANDLES_OPENED
(XtraDB only).
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_VIRTUAL table contains information about base columns of virtual columns. The PROCESS privilege is required to view the table.
It contains the following columns:
TABLE_ID
bigint(21) unsigned
NO
0
POS
int(11) unsigned
NO
0
BASE_POS
int(11) unsigned
NO
0
This page is licensed: CC BY-SA / Gnu FDL