1 of 100

Information Schema

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.

The information_schema is a standard-compliant database available in every MariaDB instance, providing metadata about all other databases, tables, columns, and server-level objects.

Information Schema Tables

Provides a comprehensive list of all tables available within the information_schema, including their purpose and the metadata they provide.

TIME_MS column in INFORMATION_SCHEMA.PROCESSLIST

Details the TIME_MS column in the INFORMATION_SCHEMA.PROCESSLIST table, which provides high-resolution timing for active server processes.

TIME_MS column in INFORMATION_SCHEMA.PROCESSLIST

The TIME_MS column in the INFORMATION_SCHEMA.PROCESSLIST table displays the time a thread has been in its current state in milliseconds, offering higher precision than the standard TIME column.

In MariaDB, an extra column TIME_MS was added to the INFORMATION_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.

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 . This means that it can be reliably used also for threads that change @TIMESTAMP (such as the SQL thread). See also .

As a consequence of this, the TIME column ofSHOW FULL PROCESSLIST andINFORMATION_SCHEMA.PROCESSLIST cannot be used to determine if a slave is lagging behind. For this, use instead theSeconds_Behind_Master column in the output of .

The addition of the TIME_MS column is based on the microsec_process patch, developed by .

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema Tables

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.

Information Schema ALL_PLUGINS Table

The Information Schema ALL_PLUGINS table contains information about server plugins, whether installed or not, providing a superset of SHOW PLUGINS SONAME.

The ALL_PLUGINS table contains information about , whether installed or not.

It contains the following columns:

Column

Description

Information Schema APPLICABLE_ROLES Table

The Information Schema APPLICABLE_ROLES table shows the role authorizations that the current user may use, detailing grantable and default status.

The Information Schema APPLICABLE_ROLES table shows the role authorizations that the current user may use.

It contains the following columns:

Column

Description

GRANTEE

Account that the role was granted to.

The current role is in the Information Schema table.

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema CHARACTER_SETS Table

The Information Schema CHARACTER_SETS table contains a list of supported character sets, their default collations, and maximum lengths.

The CHARACTER_SETS table contains a list of supported , their default collations and maximum lengths.

It contains the following columns:

Column

Description

Information Schema ENABLED_ROLES Table

The Information Schema ENABLED_ROLES table lists all roles that are currently enabled for the current session, including nested roles.

The ENABLED_ROLES table shows the enabled for the current session.

It contains the following column:

Column

Description

Information Schema FILE_KEY_MANAGEMENT_KEYS Table

The Information Schema FILES table provides information about files stored in tablespaces, such as those used by the InnoDB storage engine.

This table is available from MariaDB Enterprise Server 11.8.

The INFORMATION_SCHEMA.FILE_KEY_MANAGEMENT_KEYS table lists key ID and latest key version for keys managed by the file_key_management plugin. The table's contents can be queried directly, or viewed using the SHOW FILE_KEY_MANAGEMENT_KEYS statement.

You can also can reload the data from the key file into the cache by issuing this statement:

FLUSH FILE_KEY_MANAGEMENT_KEYS

This functionality is provided by the file_key_management plugin.

This Information Schema table allows to query the key numbers and key versions which are currently in the cache (which can be different to the file). It also allows to verify which keys are in use when joining the table with INNODB_TABLESPACES_ENCRYPTION.

Information Schema HASHICORP_KEY_MANAGEMENT_CACHE Table

The Information Schema HASHICORP_KEY_MANAGEMENT_CACHE table provides information about keys managed by the HashiCorp Key Management plugin.

The INFORMATION_SCHEMA.HASHICORP_KEY_MANAGEMENT_CACHE table lists the Key ID and the latest Key Version for keys managed by the . The table's contents can be queried directly, or viewed using the SHOW HASHICORP_KEY_MANAGEMENT_CACHE statement.

You can reload the data from the Vault server into the cache by issuing this statement:

This functionality is provided by the HashiCorp Key Management plugin.

This Information Schema table allows you to query the key numbers and key versions which are currently in the cache.

The table contains the following columns:

Information Schema GLOBAL_STATUS and SESSION_STATUS Tables

The GLOBAL_STATUS and SESSION_STATUS tables store the global and session values of server status variables, respectively.

The GLOBAL_STATUS and SESSION_STATUS tables store a record of all and their global and session values respectively. This is the same information as displayed by the commands SHOW GLOBAL STATUS and SHOW SESSION STATUS.

They contain the following columns:

Column

Description

Information Schema GLOBAL_VARIABLES and SESSION_VARIABLES Tables

The GLOBAL_VARIABLES and SESSION_VARIABLES tables store the global and session values of all system variables, respectively.

The GLOBAL_VARIABLES and SESSION_VARIABLES tables stores a record of all and their global and session values respectively. This is the same information as displayed by the commands SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES.

It contains the following columns:

Column

Description

Information Schema KEY_PERIOD_USAGE Table

The Information Schema KEY_PERIOD_USAGE table describes how keys reference application-time periods, detailing constraints involving those periods.

The KEY_PERIOD_USAGE table shows information about .

It contains the following columns:

Column

Description

Information Schema OPTIMIZER_TRACE Table

The Information Schema OPTIMIZER_TRACE table contains details about the optimizer's decision-making process for the last executed query.

The OPTIMIZER_TRACE table contains information.

It contains the following columns:

Column

Description

Information Schema PARAMETERS Table

The Information Schema PARAMETERS table stores information about parameters for stored procedures and functions, including data types and modes.

The Information Schema PARAMETERS table stores information about stored procedures and stored functions parameters.

It contains the following columns:

Column

Description

SPECIFIC_CATALOG

Information from this table is similar to that found in the param_list column in the table, and the output of the and statements.

To obtain information about the routine itself, you can query the .

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema PERIODS Table

The Information Schema PERIODS table provides information about Application-Time Periods, detailing the start and end columns for system versioning.

The PERIODS table provides information about .

It contains the following columns:

Column

Description

Information Schema QUERY_RESPONSE_TIME Table

The Information Schema QUERY_RESPONSE_TIME table displays the distribution of query execution times, helping to identify performance bottlenecks.

The QUERY_RESPONSE_TIME table contains information about queries that take a long time to execute . It is only available if the plugin has been installed.

It contains the following columns:

Column

Description

Information Schema REFERENTIAL_CONSTRAINTS Table

The Information Schema REFERENTIAL_CONSTRAINTS table contains metadata about foreign keys, including update and delete rules.

The REFERENTIAL_CONSTRAINTS table contains information about . The single columns are listed in the table.

It has the following columns:

Column

Description

Information Schema SLAVE_STATUS Table

The Information Schema SLAVE_STATUS table displays status information for the replication slave threads, similar to SHOW SLAVE STATUS.

This table is available as of MariaDB .

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}

Information Schema SPIDER_ALLOC_MEM Table

The Information Schema SPIDER_ALLOC_MEM table tracks memory allocation for the Spider storage engine, aiding in resource monitoring.

The SPIDER_ALLOC_MEM table is installed along with the storage engine. It shows information about Spider's memory usage.

It contains the following columns:

Column

Description

Information Schema TABLE_CONSTRAINTS Table

The Information Schema TABLE_CONSTRAINTS table describes constraints on tables, such as PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.

The TABLE_CONSTRAINTS table contains information about tables that have .

It has the following columns:

Column

Description

Information Schema TABLE_PRIVILEGES Table

The Information Schema TABLE_PRIVILEGES table lists privileges granted specifically at the table level.

The TABLE_PRIVILEGES table contains table privilege information derived from the grant table.

It has the following columns:

Column

Description

Information Schema THREAD_POOL_GROUPS Table

The Information Schema THREAD_POOL_GROUPS table provides details about the thread groups in the thread pool, including active thread counts.

The table provides information about groups, and contains the following columns:

Column

Description

Information Schema THREAD_POOL_QUEUES Table

The Information Schema THREAD_POOL_QUEUES table shows the status of the queues within the thread pool, detailing pending connections.

The table provides information about queues, and contains the following columns:

Column

Description

Information Schema THREAD_POOL_STATS Table

The Information Schema THREAD_POOL_STATS table provides performance statistics for the thread pool, such as thread creation and efficiency.

The table provides performance counter information for the , and contains the following columns:

Column

Description

Information Schema THREAD_POOL_WAITS Table

The Information Schema THREAD_POOL_WAITS table lists the number of times threads in the thread pool have waited for various events.

This table is available from MariaDB 10.5.

The table provides wait counters for the thread pool, and contains the following columns:

Column

Description

REASON

name of the reason for waiting, e.g. ROW_LOCK, DISKIO, NET ...

COUNT

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema USER_PRIVILEGES Table

The Information Schema USER_PRIVILEGES table lists global privileges granted to user accounts, derived from the mysql.user table.

The USER_PRIVILEGES table contains global user privilege information derived from the grant table.

It contains the following columns:

Column

Description

Information Schema USERS Table

The Information Schema USERS table lists user accounts, including whether they are global or local, and details about their password expiration.

The USERS table contains information about users, , and the limits set by . Unprivileged users can access their own data, which is different to what provides.

It contains the following columns:

Column

Description

Information Schema WSREP_CERT_KEYS_HISTORY

The Information Schema WSREP_CERT_KEYS_HISTORY table logs a history of certification keys for recently processed Galera transactions.

This table is used in .

This table is available as of MariaDB Enterprise Server 11.8.

This table contains certification key information history for Galera threads. See the WSREP_CERT_KEYS documentation for details.

Information Schema WSREP_CONNECTIONS

The Information Schema WSREP_CONNECTIONS table shows active connections to the Galera Cluster, primarily used for debugging cluster membership.

This table is used in .

This table is available as of MariaDB Enterprise Server 11.8.

This table contains active Galera connections on queried nodes in the cluster. Example output:

MariaDB [(none)]> SELECT * FROM information_schema.wsrep_connections;
+-----------------+-------------------+-----------------+-----------------+------------------------+---------------------------------------------------+------------------------------------------------------+---------------------+
| connection_id   | connection_scheme | local_address   | remote_address  | cipher                 | certificate_subject                               | certificate_issuer                                   | certificate_version |
+-----------------+-------------------+-----------------+-----------------+------------------------+---------------------------------------------------+------------------------------------------------------+---------------------+
| 136018124822096 | ssl               | 127.0.0.1:19033 | 127.0.0.1:59010 | TLS_AES_256_GCM_SHA384 | /C=FI/ST=Helsinki/L=Helsinki/O=Galera/CN=galera.1 | /C=FI/ST=Helsinki/L=Helsinki/O=Galera/CN=galera.root | TLSv1.3             |
| 136018124966656 | ssl               | 127.0.0.1:49650 | 127.0.0.1:19039 | TLS_AES_256_GCM_SHA384 | /C=FI/ST=Helsinki/L=Helsinki/O=Galera/CN=galera.1 | /C=FI/ST=Helsinki/L=Helsinki/O=Galera/CN=galera.root | TLSv1.3             |
+-----------------+-------------------+-----------------+-----------------+------------------------+---------------------------------------------------+------------------------------------------------------+---------------------+
2 rows in set (0.004 sec)

Information Schema WSREP_STATUS Table

The Information Schema WSREP_STATUS table provides a comprehensive view of the Galera Cluster status variables, similar to SHOW STATUS LIKE 'wsrep%'

The WSREP_STATUS table makes node cluster status information available through the . The same information can be returned using the statement. Only users with the privilege can access information from this table.

The WSREP_STATUS table is part of the .

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema WSREP_THD_STATE

The Information Schema WSREP_THD_STATE table displays the internal state of Galera threads, helping to monitor replication progress and locking.

This table contains execution state information for Galera threads, and has these columns:

ID - Thread ID
OS_THREAD_ID - Os thread ID

Information Schema WSREP_THD_STATE_HISTORY

The Information Schema WSREP_THD_STATE_HISTORY table maintains a history of thread states for Galera replication, aiding in post-incident analysis.

This table is used in .

This table is available as of MariaDB Enterprise Server 11.8.

This table contains execution state information history for Galera threads. See the WSREP_THD_STATE documentation for details.

Information Schema InnoDB Tables

These system tables provide detailed metadata about InnoDB tables, including their structure, storage, and other crucial properties for analysis.

Information Schema INNODB_CHANGED_PAGES Table

The Information Schema INNODB_CHANGED_PAGES table lists pages that have been modified since a specific checkpoint.

The 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 system variable.

The PROCESS is required to view the table.

It has the following columns:

Column

Information Schema INNODB_FT_BEING_DELETED Table

This table is used only during an OPTIMIZE TABLE operation to store the document IDs of rows currently being deleted from an InnoDB FULLTEXT index.

The INNODB_FT_BEING_DELETED table is only used while document ID's in the related are being removed from an InnoDB while an is underway. At all other times the table will be empty.

The SUPER is required to view the table, and it also requires the system variable to be set.

It has the following column:

Column

Description

Information Schema INNODB_FT_DELETED Table

The INNODB_FT_DELETED table lists document IDs of rows that have been deleted from an InnoDB FULLTEXT index but not yet removed from the index files.

The INNODB_FT_DELETED table contains rows that have been deleted from an InnoDB . 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 statement is underway. The related table contains rows being deleted while an OPTIMIZE TABLE is in the process of running.

The SUPER is required to view the table, and it also requires the system variable to be set.

It has the following column:

Information Schema INNODB_METRICS Table

The Information Schema INNODB_METRICS table contains a wide range of low-level performance metrics and counters for the InnoDB storage engine.

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:

Column

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 system variable. You can either enable a variable by its name, for example:

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 Name

Subsystem Field

There are four counters in the icp subsystem:

To enable them all, use the associated module name from the table above, module_icp.

The % wildcard, used to represent any number of characters, can also be used when naming counters, for example:

To disable counters, use the 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 (for individual counters) and (for all counters) system variables.

From MariaDB 10.6, the interface was simplified by removing the following variables:

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:
buffer_LRU_batch_evict_total_pages:

The intention is to eventually remove the interface entirely (see ).

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_DATAFILES Table

The INNODB_SYS_DATAFILES table, now deprecated and removed, was intended to provide metadata for InnoDB tablespace paths.

The 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 . The PROCESS was required to view the table.

It contains the following columns:

Column

Description

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 matches

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: Stable

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 | +-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+

SELECT * FROM INFORMATION_SCHEMA.WSREP_BF_ABORT_HISTORY LIMIT 100; +----------------------------+---------------+------------------+-----------------+--------------+---------------------+--------------------+------------------+----------------------------+-----------+--------------+-------------+----------+-----------------+----------------+--------------+----------------------------+----------+---------+------------+------------------+ | TIME | VICTIM_TRX_ID | VICTIM_THREAD_ID | VICTIM_QUERY_ID | VICTIM_SEQNO | VICTIM_CLIENT_STATE | VICTIM_CLIENT_MODE | VICTIM_TRX_STATE | VICTIM_LOCK | BF_TRX_ID | BF_THREAD_ID | BF_QUERY_ID | BF_SEQNO | BF_CLIENT_STATE | BF_CLIENT_MODE | BF_TRX_STATE | BF_LOCK | SPACE_ID | PAGE_NO | INDEX_NAME | TABLE_NAME | +----------------------------+---------------+------------------+-----------------+--------------+---------------------+--------------------+------------------+----------------------------+-----------+--------------+-------------+----------+-----------------+----------------+--------------+----------------------------+----------+---------+------------+------------------+ | 2025-05-14 09:48:31.539207 | 378998 | 94 | 2365214 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379010 | 1 | 2365229 | 174271 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.597005 | 379044 | 81 | 2365344 | NULL | exec | local | certifying | REC|LOCK_X|NOT_GAP | 379060 | 1 | 2365419 | 174284 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.609301 | 379055 | 104 | 2365412 | NULL | exec | local | certifying | REC|LOCK_X|NOT_GAP | 379070 | 1 | 2365445 | 174289 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.725899 | 379134 | 101 | 2366437 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP | 379164 | 1 | 2366536 | 174307 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 147008 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789128 | 379094 | 131 | 2366355 | NULL | exec | local | certifying | REC|LOCK_X|NOT_GAP | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789140 | 379101 | 124 | 2366103 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789145 | 379093 | 82 | 2366113 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789148 | 379110 | 117 | 2366193 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789152 | 379126 | 111 | 2366201 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789156 | 379100 | 134 | 2366318 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789159 | 379109 | 93 | 2366334 | NULL | exec | local | executing | REC|LOCK_X|NOT_GAP|WAITING | 379195 | 1 | 2366599 | 174323 | exec | high priority | executing | REC|LOCK_X|NOT_GAP|WAITING | 12 | 4 | PRIMARY | `test`.`sbtest1` | | 2025-05-14 09:48:31.789163 | 379119 | 90 | 2366384 | NULL | exec | local | executing +----------------------------+---------------+------------------+-----------------+--------------+---------------------+--------------------+------------------+----------------------------+-----------+--------------+-------------+----------+-----------------+----------------+--------------+----------------------------+----------+---------+------------+------------------+ ...

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)

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)

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 | +------------+--------------+-------------+-----------+--------+----------+

Information Schema

TIME_MS column in INFORMATION_SCHEMA.PROCESSLIST

Information Schema Tables

Information Schema ALL_PLUGINS Table

Information Schema APPLICABLE_ROLES Table

Information Schema CHARACTER_SETS Table

Information Schema ENABLED_ROLES Table

Information Schema FILE_KEY_MANAGEMENT_KEYS Table

Information Schema HASHICORP_KEY_MANAGEMENT_CACHE Table

Information Schema GLOBAL_STATUS and SESSION_STATUS Tables

Information Schema GLOBAL_VARIABLES and SESSION_VARIABLES Tables

Information Schema KEY_PERIOD_USAGE Table

Information Schema OPTIMIZER_TRACE Table

Information Schema PARAMETERS Table

Information Schema PERIODS Table

Information Schema QUERY_RESPONSE_TIME Table

Information Schema REFERENTIAL_CONSTRAINTS Table

Information Schema SLAVE_STATUS Table

Information Schema SPIDER_ALLOC_MEM Table

Information Schema TABLE_CONSTRAINTS Table

Information Schema TABLE_PRIVILEGES Table

Information Schema THREAD_POOL_GROUPS Table

Information Schema THREAD_POOL_QUEUES Table

Information Schema THREAD_POOL_STATS Table

Information Schema THREAD_POOL_WAITS Table

Information Schema USER_PRIVILEGES Table

Information Schema USERS Table

Information Schema WSREP_CERT_KEYS_HISTORY

Information Schema WSREP_CONNECTIONS

Information Schema WSREP_STATUS Table

Information Schema WSREP_THD_STATE

Information Schema WSREP_THD_STATE_HISTORY

Information Schema InnoDB Tables

Information Schema INNODB_CHANGED_PAGES Table

Information Schema INNODB_FT_BEING_DELETED Table

Information Schema INNODB_FT_DELETED Table

Information Schema INNODB_METRICS Table

Information Schema INNODB_SYS_DATAFILES Table

Information Schema PARAMETERS Table

Information Schema INNODB_METRICS Table

Information Schema

Enabling and Disabling Counters

Resetting Counters

Simplification from MariaDB 10.6

Examples

Example

TIME_MS column in INFORMATION_SCHEMA.PROCESSLIST

Information Schema Tables

Information Schema APPLICABLE_ROLES Table

Information Schema ALL_PLUGINS Table

Information Schema InnoDB Tables

Example

Information Schema FILE_KEY_MANAGEMENT_KEYS Table

Description

Example

Information Schema SLAVE_STATUS Table

Information Schema THREAD_POOL_WAITS Table

Information Schema WSREP_CERT_KEYS_HISTORY

Information Schema WSREP_CONNECTIONS

Information Schema WSREP_THD_STATE_HISTORY

Information Schema CHARACTER_SETS Table

Information Schema ENABLED_ROLES Table

Information Schema HASHICORP_KEY_MANAGEMENT_CACHE Table

Information Schema GLOBAL_STATUS and SESSION_STATUS Tables

Information Schema GLOBAL_VARIABLES and SESSION_VARIABLES Tables

Information Schema KEY_PERIOD_USAGE Table

Information Schema OPTIMIZER_TRACE Table

Information Schema PERIODS Table

Information Schema QUERY_RESPONSE_TIME Table

Information Schema REFERENTIAL_CONSTRAINTS Table

Information Schema SPIDER_ALLOC_MEM Table

Information Schema TABLE_CONSTRAINTS Table

Information Schema TABLE_PRIVILEGES Table

Information Schema THREAD_POOL_GROUPS Table

Information Schema THREAD_POOL_QUEUES Table

Information Schema THREAD_POOL_STATS Table

Information Schema USER_PRIVILEGES Table

Information Schema USERS Table

Information Schema WSREP_STATUS Table

Information Schema WSREP_THD_STATE