Chapter 22 INFORMATION_SCHEMA Tables

COLLATION_NAME is the collation name.

CHARACTER_SET_NAME is the name of the character set with which the collation is associated.

ID is the collation ID.

IS_DEFAULT indicates whether the collation is the default for its character set.

IS_COMPILED indicates whether the character set is compiled into the server.

SORTLEN is related to the amount of memory required to sort strings expressed in the character set.

In SHOW, the Type display includes values from several different COLUMNS columns.

ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW, SELECT does not have automatic ordering.

CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multibyte character sets.

CHARACTER_SET_NAME can be derived from Collation. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the Collation column a value of latin1_swedish_ci, the character set is what is before the first underscore: latin1.

GENERATION_EXPRESSION is nonempty for generated columns and displays the expression used to compute column values. For information about generated columns, see Section 14.1.18.5, “CREATE TABLE and Generated Columns”. This column was added in MySQL 5.7.6.

As of MySQL 5.7.6, the EXTRA column contains VIRTUAL GENERATED or VIRTUAL STORED for generated columns.

In the output from SHOW FULL COLUMNS, the privileges are all in one field and in lowercase, for example, select,insert,update,references. In COLUMN_PRIVILEGES, there is one privilege per row, in uppercase.

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.

If the user has GRANT OPTION privilege, IS_GRANTABLE should be YES. Otherwise, IS_GRANTABLE should be NO. The output does not list GRANT OPTION as a separate privilege.

The ENGINES table is a nonstandard table. Its contents correspond to the columns of the SHOW ENGINES statement. For descriptions of its columns, see Section 14.7.5.16, “SHOW ENGINES Syntax”.

The EVENTS table is a nonstandard table.

EVENT_CATALOG: The value of this column is always def.

EVENT_SCHEMA: The name of the schema (database) to which this event belongs.

EVENT_NAME: The name of the event.

DEFINER: The account of the user who created the event, in 'user_name'@'host_name' format.

TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.

EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 5.7, this is always SQL.

This column is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions.

EVENT_DEFINITION: The text of the SQL statement making up the event's DO clause; in other words, the statement executed by this event.

EVENT_TYPE: The event repetition type, either ONE TIME (transient) or RECURRING (repeating).

EXECUTE_AT: For a one-time event, this is the DATETIME value specified in the AT clause of the CREATE EVENT statement used to create the event, or of the last ALTER EVENT statement that modified the event. The value shown in this column reflects the addition or subtraction of any INTERVAL value included in the event's AT clause. For example, if an event is created using ON SCHEDULE AT CURRENT_TIMESTAMP + '1:6' DAY_HOUR, and the event was created at 2006-02-09 14:05:30, the value shown in this column would be '2006-02-10 20:05:30'.

If the event's timing is determined by an EVERY clause instead of an AT clause (that is, if the event is recurring), the value of this column is NULL.

INTERVAL_VALUE: For recurring events, this column contains the numeric portion of the event's EVERY clause.

For a one-time event (that is, an event whose timing is determined by an AT clause), this column is NULL.

INTERVAL_FIELD: For recurring events, this column contains the units portion of the EVERY clause governing the timing of the event. Thus, this column contains a value such as 'YEAR', 'QUARTER', 'DAY', and so on.

For a one-time event (that is, an event whose timing is determined by an AT clause), this column is NULL.

SQL_MODE: The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 6.1.7, “Server SQL Modes”.

STARTS: For a recurring event whose definition includes a STARTS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used.

If there is no STARTS clause affecting the timing of the event, this column is NULL

ENDS: For a recurring event whose definition includes a ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used.

If there is no ENDS clause affecting the timing of the event, this column is NULL.

STATUS: One of the three values ENABLED, DISABLED, or SLAVESIDE_DISABLED.

SLAVESIDE_DISABLED indicates that the creation of the event occurred on another MySQL server acting as a replication master and was replicated to the current MySQL server which is acting as a slave, but the event is not presently being executed on the slave. See Section 18.4.1.12, “Replication of Invoked Features”, for more information.

ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.

CREATED: The date and time when the event was created. This is a TIMESTAMP value.

LAST_ALTERED: The date and time when the event was last modified. This is a TIMESTAMP value. If the event has not been modified since its creation, this column holds the same value as the CREATED column.

LAST_EXECUTED: The date and time when the event last executed. A DATETIME value. If the event has never executed, this column is NULL.

LAST_EXECUTED indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED.

EVENT_COMMENT: The text of a comment, if the event has one. If not, the value of this column is an empty string.

ORIGINATOR: The server ID of the MySQL server on which the event was created; used in replication. The default value is 0.

CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the event was created.

COLLATION_CONNECTION: The session value of the collation_connection system variable when the event was created.

DATABASE_COLLATION: The collation of the database with which the event is associated.

Data reported by INFORMATION_SCHEMA.FILES is reported from the InnoDB in-memory cache for open files. By comparison, INFORMATION_SCHEMA.INNODB_SYS_DATAFILES reports data from the InnoDB SYS_DATAFILES internal data dictionary table.

The data reported by INFORMATION_SCHEMA.FILES includes temporary table tablespace data. This data is not available in the internal SYS_DATAFILES data dictionary table, and is therefore not reported by INNODB_SYS_DATAFILES.

Undo tablespace data is reported by INFORMATION_SCHEMA.FILES when separate undo tablespaces are configured using the innodb_undo_tablespaces configuration option.

FILE_ID is the tablespace ID, also referred to as the space_id or fil_space_t::id.

FILE_NAME is the name of the data file. File-per-table and general tablespaces have a .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. Temporary table tablespaces are prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (datadir).

FILE_TYPE is the tablespace file type. There are three possible file types for InnoDB files. TABLESPACE is the file type for any system, general, or file-per-table tablespace file that holds tables, indexes, or other forms of user data. TEMPORARY is the file type for temporary table tablespaces. UNDO LOG is the file type for undo log tablespaces, which hold undo records. By default, undo records are stored in the system tablespace. Separate undo log tablespaces can be added using the innodb_undo_tablespaces option.

TABLESPACE_NAME is the SQL name for the tablespace. A general tablespace name is the SYS_TABLESPACES.NAME value. For other tablespace files, names start with innodb_, such as innodb_system, innodb_undo, and innodb_file_per_table. The file-per-table tablespace name format is innodb_file_per_table_##, where ## is the tablespace ID.

ENGINE is the storage engine. For InnoDB files, the value is always InnoDB.

FREE_EXTENTS is the number of fully free extents in the current data file.

TOTAL_EXTENTS is the number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

EXTENT_SIZE is 1048576 (1MB) for files with a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB) for files with a 32k page size, and 4194304 (4MB) for files with a 64k page size. INFORMATION_SCHEMA.FILES does not report InnoDB page size. Page size is defined by the innodb_page_size option. Extent size information can also be retrieved from INNODB_SYS_TABLESPACES where FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID.

INITIAL_SIZE is the initial size of the file, in bytes.

MAXIMUM_SIZE is the maximum number of bytes allowed in the file. The value is NULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined by innodb_data_file_path. Maximum temporary table tablespace file size is defined by innodb_temp_data_file_path. A NULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.

AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or defined by innodb_temp_data_file_path for temporary table tablespaces.

DATA_FREE is the total amount of free space (in bytes) for the entire tablespace. Predefined system tablespaces, which include the system tablespace and temporary table tablespaces, may have one or more data files.

STATUS is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

The following query returns all data pertinent to InnoDB tablespaces.

mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS, 
EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE 
FROM INFORMATION_SCHEMA.FILES \G

The FILES table provides information about Disk Data files only; you cannot use it for determining disk space allocation or availability for individual NDB tables. However, it is possible to see how much space is allocated for each NDB table having data stored on disk—as well as how much remains available for storage of data on disk for that table—using ndb_desc. For more information, see Section 19.4.10, “ndb_desc — Describe NDB Tables”.

FILE_ID column values are auto-generated.

FILE_NAME is the name of an UNDO log file created by CREATE LOGFILE GROUP or ALTER LOGFILE GROUP, or of a data file created by CREATE TABLESPACE or ALTER TABLESPACE.

FILE_TYPE is one of the values UNDOFILE, DATAFILE, or TABLESPACE.

TABLESPACE_NAME is the name of the tablespace with which the file is associated.

Currently, the value of the TABLESPACE_CATALOG column is always NULL.

TABLE_NAME is the name of the Disk Data table with which the file is associated, if any.

The LOGFILE_GROUP_NAME column gives the name of the log file group to which the log file or data file belongs.

For an UNDO log file, the LOGFILE_GROUP_NUMBER contains the auto-generated ID number of the log file group to which the log file belongs.

For a MySQL Cluster Disk Data log file or data file, the value of the ENGINE column is always NDB or NDBCLUSTER.

For a MySQL Cluster Disk Data log file or data file, the value of the FULLTEXT_KEYS column is always empty.

The FREE EXTENTS column displays the number of extents which have not yet been used by the file. The TOTAL EXTENTS column show the total number of extents allocated to the file.

The difference between these two columns is the number of extents currently in use by the file:

SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
    FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_NAME = 'myfile.dat';

You can approximate the amount of disk space in use by the file by multiplying this difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes:

SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
    FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_NAME = 'myfile.dat';

Similarly, you can estimate the amount of space that remains available in a given file by multiplying FREE_EXTENTS by EXTENT_SIZE:

SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
    FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_NAME = 'myfile.dat';

It is also important to remember that once an extent is used, it cannot be freed again without dropping the data file of which it is a part. This means that deletes from a Disk Data table do not release disk space.

The extent size can be set in a CREATE TABLESPACE statement. See Section 14.1.19, “CREATE TABLESPACE Syntax”, for more information.

The INITIAL_SIZE column shows the size in bytes of the file. This is the same value that was used in the INITIAL_SIZE clause of the CREATE LOGFILE GROUP, ALTER LOGFILE GROUP, CREATE TABLESPACE, or ALTER TABLESPACE statement used to create the file.

For MySQL Cluster Disk Data files, the value of the MAXIMUM_SIZE column is always the same as INITIAL_SIZE, and the AUTOEXTEND_SIZE column is always empty.

The CREATION_TIME column shows the date and time when the file was created. The LAST_UPDATE_TIME column displays the date and time when the file was last modified. The LAST_ACCESSED column provides the date and time when the file was last accessed by the server.

Currently, the values of these columns are as reported by the operating system, and are not supplied by the NDB storage engine. Where no value is provided by the operating system, these columns display 0000-00-00 00:00:00.

For MySQL Cluster Disk Data files, the value of the RECOVER_TIME and TRANSACTION_COUNTER columns is always 0.

For MySQL Cluster Disk Data files, the following columns are always NULL:

For MySQL Cluster Disk Data files, the value of the STATUS column is always NORMAL.

For MySQL Cluster Disk Data files, the EXTRA column shows which data node the file belongs to, as each data node has its own copy of the file. Suppose that you use this statement on a MySQL Cluster with four data nodes:

CREATE LOGFILE GROUP mygroup
    ADD UNDOFILE 'new_undo.dat'
    INITIAL_SIZE 2G
    ENGINE NDB;

After running the CREATE LOGFILE GROUP statement successfully, you should see a result similar to the one shown here for this query against the FILES table:

mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
    ->     FROM INFORMATION_SCHEMA.FILES
    ->     WHERE FILE_NAME = 'new_undo.dat';
+--------------------+-------------+----------------+
| LOGFILE_GROUP_NAME | FILE_TYPE   | EXTRA          |
+--------------------+-------------+----------------+
| mygroup            | UNDO FILE   | CLUSTER_NODE=3 |
| mygroup            | UNDO FILE   | CLUSTER_NODE=4 |
| mygroup            | UNDO FILE   | CLUSTER_NODE=5 |
| mygroup            | UNDO FILE   | CLUSTER_NODE=6 |
+--------------------+-------------+----------------+
4 rows in set (0.01 sec)

The FILES table is a nonstandard table.

An additional row is present in the FILES table following the creation of a logfile group. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always UNDO FILE, and that of the STATUS column is always NORMAL. Currently, the value of the ENGINE column is always NDBCLUSTER.

The FREE_EXTENTS column in this row shows the total number of free extents available to all undo files belonging to a given log file group whose name and number are shown in the LOGFILE_GROUP_NAME and LOGFILE_GROUP_NUMBER columns, respectively.

Suppose there are no existing log file groups on your MySQL Cluster, and you create one using the following statement:

mysql> CREATE LOGFILE GROUP lg1
    ->   ADD UNDOFILE 'undofile.dat'
    ->   INITIAL_SIZE = 16M
    ->   UNDO_BUFFER_SIZE = 1M
    ->   ENGINE = NDB;
Query OK, 0 rows affected (3.81 sec)

You can now see this NULL row when you query the FILES table:

mysql> SELECT DISTINCT
    ->   FILE_NAME AS File,
    ->   FREE_EXTENTS AS Free,
    ->   TOTAL_EXTENTS AS Total,
    ->   EXTENT_SIZE AS Size,
    ->   INITIAL_SIZE AS Initial
    ->   FROM INFORMATION_SCHEMA.FILES;
+--------------+---------+---------+------+----------+
| File         | Free    | Total   | Size | Initial  |
+--------------+---------+---------+------+----------+
| undofile.dat |    NULL | 4194304 |    4 | 16777216 |
| NULL         | 4184068 |    NULL |    4 |     NULL |
+--------------+---------+---------+------+----------+
2 rows in set (0.01 sec)

The total number of free extents available for undo logging is always somewhat less than the sum of the TOTAL_EXTENTS column values for all undo files in the log file group due to overhead required for maintaining the undo files. This can be seen by adding a second undo file to the log file group, then repeating the previous query against the FILES table:

mysql> ALTER LOGFILE GROUP lg1
    ->   ADD UNDOFILE 'undofile02.dat'
    ->   INITIAL_SIZE = 4M
    ->   ENGINE = NDB;
Query OK, 0 rows affected (1.02 sec)

mysql> SELECT DISTINCT
    ->   FILE_NAME AS File,
    ->   FREE_EXTENTS AS Free,
    ->   TOTAL_EXTENTS AS Total,
    ->   EXTENT_SIZE AS Size,
    ->   INITIAL_SIZE AS Initial
    ->   FROM INFORMATION_SCHEMA.FILES;
+----------------+---------+---------+------+----------+
| File           | Free    | Total   | Size | Initial  |
+----------------+---------+---------+------+----------+
| undofile.dat   |    NULL | 4194304 |    4 | 16777216 |
| undofile02.dat |    NULL | 1048576 |    4 |  4194304 |
| NULL           | 5223944 |    NULL |    4 |     NULL |
+----------------+---------+---------+------+----------+
3 rows in set (0.01 sec)

The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size:

mysql> SELECT
    ->   FREE_EXTENTS AS 'Free Extents',
    ->   FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
    ->   FROM INFORMATION_SCHEMA.FILES
    ->   WHERE LOGFILE_GROUP_NAME = 'lg1'
    ->   AND FILE_NAME IS NULL;
+--------------+------------+
| Free Extents | Free Bytes |
+--------------+------------+
|      5223944 |   20895776 |
+--------------+------------+
1 row in set (0.02 sec)

If you create a MySQL Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example:

mysql> CREATE TABLESPACE ts1
    ->   ADD DATAFILE 'data1.dat'
    ->   USE LOGFILE GROUP lg1
    ->   INITIAL_SIZE 512M
    ->   ENGINE = NDB;
Query OK, 0 rows affected (8.71 sec)

mysql> CREATE TABLE dd (
    ->   c1 INT NOT NULL PRIMARY KEY,
    ->   c2 INT,
    ->   c3 DATE
    ->   )
    ->   TABLESPACE ts1 STORAGE DISK
    ->   ENGINE = NDB;
Query OK, 0 rows affected (2.11 sec)

mysql> INSERT INTO dd VALUES
    ->   (NULL, 1234567890, '2007-02-02'),
    ->   (NULL, 1126789005, '2007-02-03'),
    ->   (NULL, 1357924680, '2007-02-04'),
    ->   (NULL, 1642097531, '2007-02-05');
Query OK, 4 rows affected (0.01 sec)

mysql> SELECT
    ->   FREE_EXTENTS AS 'Free Extents',
    ->   FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
    ->   FROM INFORMATION_SCHEMA.FILES
    ->   WHERE LOGFILE_GROUP_NAME = 'lg1'
    ->   AND FILE_NAME IS NULL;
+--------------+------------+
| Free Extents | Free Bytes |
+--------------+------------+
|      5207565 |   20830260 |
+--------------+------------+
1 row in set (0.01 sec)

An additional row is present in the FILES table for any MySQL Cluster tablespace, whether or not any data files are associated with the tablespace. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always TABLESPACE, and that of the STATUS column is always NORMAL. Currently, the value of the ENGINE column is always NDBCLUSTER.

There are no SHOW statements associated with the FILES table.

For additional information, and examples of creating and dropping MySQL Cluster Disk Data objects, see Section 19.5.13, “MySQL Cluster Disk Data Tables”.

The VARIABLE_VALUE column for each of these tables is defined as VARCHAR(1024).

The VARIABLE_VALUE column for each of these tables is defined as VARCHAR(1024). For variables with very long values that are not completely displayed, use SELECT as a workaround. For example:

SELECT @@GLOBAL.innodb_data_file_path;

If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.

The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced.

Suppose that there are two tables name t1 and t3 that have the following definitions:

CREATE TABLE t1
(
    s1 INT,
    s2 INT,
    s3 INT,
    PRIMARY KEY(s3)
) ENGINE=InnoDB;

CREATE TABLE t3
(
    s1 INT,
    s2 INT,
    s3 INT,
    KEY(s1),
    CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;

For those two tables, the KEY_COLUMN_USAGE table has two rows:

For successive parameters of a stored procedure or function, the ORDINAL_POSITION values are 1, 2, 3, and so forth. For a stored function, there is also a row that describes the data type for the RETURNS clause. The return value is not a true parameter, so the row that describes it has these unique characteristics:

The PARTITIONS table is a nonstandard table.

Each record in this table corresponds to an individual partition or subpartition of a partitioned table.

TABLE_CATALOG: This column is always def.

TABLE_SCHEMA: This column contains the name of the database to which the table belongs.

TABLE_NAME: This column contains the name of the table containing the partition.

PARTITION_NAME: The name of the partition.

SUBPARTITION_NAME: If the PARTITIONS table record represents a subpartition, then this column contains the name of subpartition; otherwise it is NULL.

PARTITION_ORDINAL_POSITION: All partitions are indexed in the same order as they are defined, with 1 being the number assigned to the first partition. The indexing can change as partitions are added, dropped, and reorganized; the number shown is this column reflects the current order, taking into account any indexing changes.

SUBPARTITION_ORDINAL_POSITION: Subpartitions within a given partition are also indexed and reindexed in the same manner as partitions are indexed within a table.

PARTITION_METHOD: One of the values RANGE, LIST, HASH, LINEAR HASH, KEY, or LINEAR KEY; that is, one of the available partitioning types as discussed in Section 20.2, “Partitioning Types”.

SUBPARTITION_METHOD: One of the values HASH, LINEAR HASH, KEY, or LINEAR KEY; that is, one of the available subpartitioning types as discussed in Section 20.2.6, “Subpartitioning”.

PARTITION_EXPRESSION: This is the expression for the partitioning function used in the CREATE TABLE or ALTER TABLE statement that created the table's current partitioning scheme.

For example, consider a partitioned table created in the test database using this statement:

CREATE TABLE tp (
    c1 INT,
    c2 INT,
    c3 VARCHAR(25)
)
PARTITION BY HASH(c1 + c2)
PARTITIONS 4;

The PARTITION_EXPRESSION column in a PARTITIONS table record for a partition from this table displays c1 + c2, as shown here:

mysql> SELECT DISTINCT PARTITION_EXPRESSION
     >     FROM INFORMATION_SCHEMA.PARTITIONS
     >     WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';
+----------------------+
| PARTITION_EXPRESSION |
+----------------------+
| c1 + c2              |
+----------------------+
1 row in set (0.09 sec)

SUBPARTITION_EXPRESSION: This works in the same fashion for the subpartitioning expression that defines the subpartitioning for a table as PARTITION_EXPRESSION does for the partitioning expression used to define a table's partitioning.

If the table has no subpartitions, then this column is NULL.

PARTITION_DESCRIPTION: This column is used for RANGE and LIST partitions. For a RANGE partition, it contains the value set in the partition's VALUES LESS THAN clause, which can be either an integer or MAXVALUE. For a LIST partition, this column contains the values defined in the partition's VALUES IN clause, which is a comma-separated list of integer values.

For partitions whose PARTITION_METHOD is other than RANGE or LIST, this column is always NULL.

TABLE_ROWS: The number of table rows in the partition.

For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

AVG_ROW_LENGTH: The average length of the rows stored in this partition or subpartition, in bytes.

This is the same as DATA_LENGTH divided by TABLE_ROWS.

DATA_LENGTH: The total length of all rows stored in this partition or subpartition, in bytes—that is, the total number of bytes stored in the partition or subpartition.

MAX_DATA_LENGTH: The maximum number of bytes that can be stored in this partition or subpartition.

INDEX_LENGTH: The length of the index file for this partition or subpartition, in bytes.

DATA_FREE: The number of bytes allocated to the partition or subpartition but not used.

CREATE_TIME: The time of the partition's or subpartition's creation.

Prior to MySQL 5.7.8, for partitioned InnoDB tables, this column was always NULL. The correct creation time is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

UPDATE_TIME: The time that the partition or subpartition was last modified.

Prior to MySQL 5.7.8, for partitioned InnoDB tables, this column was always NULL. The correct modification time is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

CHECK_TIME: The last time that the table to which this partition or subpartition belongs was checked.

For partitioned InnoDB tables, this column is always NULL.

CHECKSUM: The checksum value, if any; otherwise, this column is NULL.

PARTITION_COMMENT: This column contains the text of any comment made for the partition.

In MySQL 5.7, the maximum length for a partition comment is defined as 1024 characters, and the display width of the PARTITION_COMMENT column is also 1024, characters to match this limit (Bug #11748924, Bug #37728).

The default value for this column is an empty string.

NODEGROUP: This is the nodegroup to which the partition belongs. This is relevant only to MySQL Cluster tables; otherwise the value of this column is always 0.

TABLESPACE_NAME: This column contains the name of the tablespace to which the partition belongs. The value of this column is always DEFAULT.

A nonpartitioned table has one record in INFORMATION_SCHEMA.PARTITIONS; however, the values of the PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, and PARTITION_DESCRIPTION columns are all NULL. (The PARTITION_COMMENT column in this case is blank.)

The PLUGINS table is a nonstandard table.

PLUGIN_NAME is the name used to refer to the plugin in statements such as INSTALL PLUGIN and UNINSTALL PLUGIN.

PLUGIN_VERSION is the version from the plugin's general type descriptor.

PLUGIN_STATUS indicates the plugin status, one of ACTIVE, INACTIVE, DISABLED, or DELETED.

PLUGIN_TYPE indicates the type of plugin, such as STORAGE ENGINE, INFORMATION_SCHEMA, or AUTHENTICATION.

PLUGIN_TYPE_VERSION is the version from the plugin's type-specific descriptor.

PLUGIN_LIBRARY is the name of the plugin shared library file. This is the name used to refer to the plugin file in statements such as INSTALL PLUGIN and UNINSTALL PLUGIN. This file is located in the directory named by the plugin_dir system variable. If the library name is NULL, the plugin is compiled in and cannot be uninstalled with UNINSTALL PLUGIN.

PLUGIN_LIBRARY_VERSION indicates the plugin API interface version.

PLUGIN_AUTHOR names the plugin author.

PLUGIN_DESCRIPTION provides a short description of the plugin.

PLUGIN_LICENSE indicates how the plugin is licensed; for example, GPL.

LOAD_OPTION indicates how the plugin was loaded. The value is OFF, ON, FORCE, or FORCE_PLUS_PERMANENT. See Section 6.5.2, “Installing and Uninstalling Plugins”.

The PROCESSLIST table is a nonstandard table.

Like the output from the corresponding SHOW statement, the PROCESSLIST table will only show information about your own threads, unless you have the PROCESS privilege, in which case you will see information about other threads, too. As an anonymous user, you cannot see any rows at all.

If an SQL statement refers to INFORMATION_SCHEMA.PROCESSLIST, MySQL populates the entire table once, when statement execution begins, so there is read consistency during the statement. There is no read consistency for a multi-statement transaction, though.

Process information is also available from the performance_schema.threads table. However, access to threads does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences because they require a mutex. threads also shows information about background threads, which INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST do not. This means that threads can be used to monitor activity the other thread information sources cannot.

QUERY_ID is a numeric statement identifier.

SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.

STATE is the profiling state to which the row measurements apply.

DURATION indicates how long statement execution remained in the given state, in seconds.

CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.

CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.

BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.

MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.

PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.

SWAPS indicates how many swaps occurred.

SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

TABLE_NAME has the same value as TABLE_NAME in INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

CONSTRAINT_SCHEMA and CONSTRAINT_NAME identify the foreign key.

UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, and REFERENCED_TABLE_NAME identify the referenced key.

The only valid value at this time for MATCH_OPTION is NONE.

The possible values for UPDATE_RULE or DELETE_RULE are CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.

MySQL calculates EXTERNAL_LANGUAGE thus:

CREATED: The date and time when the routine was created. This is a TIMESTAMP value.

LAST_ALTERED: The date and time when the routine was last modified. This is a TIMESTAMP value. If the routine has not been modified since its creation, this column holds the same value as the CREATED column.

SQL_MODE: The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Section 6.1.7, “Server SQL Modes”.

CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the routine was created.

COLLATION_CONNECTION: The session value of the collation_connection system variable when the routine was created.

DATABASE_COLLATION: The collation of the database with which the routine is associated.

The DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_NAME, and COLLATION_NAME columns provide information about the data type for the RETURNS clause of stored functions. If a stored routine is a stored procedure, these columns all are NULL.

Information about stored function RETURNS data types is also available in the PARAMETERS table. The return value data type row for a function can be identified as the row that has an ORDINAL_POSITION value of 0.

This is a nonstandard table. It takes its values from the mysql.db table.

There is no standard table for indexes. The preceding list is similar to what SQL Server 2000 returns for sp_statistics, except that we replaced the name QUALIFIER with CATALOG and we replaced the name OWNER with SCHEMA.

Clearly, the preceding table and the output from SHOW INDEX are derived from the same parent. So the correlation is already close.

Refer to SHOW TABLE STATUS for field descriptions.

TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.

TABLE_TYPE should be BASE TABLE or VIEW. The TABLES table does not list TEMPORARY tables.

For partitioned tables, the ENGINE column shows the name of the storage engine used by all partitions. (Previously, this column showed PARTITION for such tables.)

The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

The DATA_FREE column shows the free space in bytes for InnoDB tables.

Prior to MySQL 5.7.8, for partitioned InnoDB tables, the CREATE_TIME column always showed NULL. This column shows the correct table creation time for such tables in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

The UPDATE_TIME column also shows this information for partitioned InnoDB tables in MySQL 5.7.8 and later. Previously this column was always NULL for such tables. (Bug #17299181, Bug #69990)

For partitioned InnoDB tables, the CHECK_TIME column is always NULL.

We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

The CREATE_OPTIONS column shows partitioned if the table is partitioned.

The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.

The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name field in the output from SHOW INDEX when the Non_unique field is 0.

The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until we support CHECK.

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP, CREATE VIEW.

The names in the “SHOW Name” column refer to the SHOW TRIGGERS statement, not SHOW CREATE TRIGGER. See Section 14.7.5.38, “SHOW TRIGGERS Syntax”.

TRIGGER_SCHEMA and TRIGGER_NAME: The name of the database in which the trigger occurs and the trigger name, respectively.

EVENT_MANIPULATION: The trigger event. This is the type of operation on the associated table for which the trigger activates. The value is 'INSERT' (a row was inserted), 'DELETE' (a row was deleted), or 'UPDATE' (a row was modified).

EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE: As noted in Section 21.3, “Using Triggers”, every trigger is associated with exactly one table. These columns indicate the database in which this table occurs, and the table name, respectively.

ACTION_ORDER: The ordinal position of the trigger's action within the list of triggers on the same table with the same EVENT_MANIPULATION and ACTION_TIMING values. Before MySQL 5.7.2, this value is always 0 because it is not possible for a table to have more than one trigger with the same EVENT_MANIPULATION and ACTION_TIMING values.

ACTION_STATEMENT: The trigger body; that is, the statement executed when the trigger activates. This text uses UTF-8 encoding.

ACTION_ORIENTATION: Always contains the value 'ROW'.

ACTION_TIMING: Whether the trigger activates before or after the triggering event. The value is 'BEFORE' or 'AFTER'.

ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW: The old and new column identifiers, respectively. This means that ACTION_REFERENCE_OLD_ROW always contains the value 'OLD' and ACTION_REFERENCE_NEW_ROW always contains the value 'NEW'.

CREATED: The date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers created in MySQL 5.7.2 or later, NULL for triggers created prior to 5.7.2.

SQL_MODE: The SQL mode in effect when the trigger was created, and under which the trigger executes. For the permitted values, see Section 6.1.7, “Server SQL Modes”.

DEFINER: The account of the user who created the trigger, in 'user_name'@'host_name' format.

CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the trigger was created.

COLLATION_CONNECTION: The session value of the collation_connection system variable when the trigger was created.

DATABASE_COLLATION: The collation of the database with which the trigger is associated.

The following columns currently always contain NULL: ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, and ACTION_REFERENCE_NEW_TABLE.

This is a nonstandard table. It takes its values from the mysql.user table.

The VIEW_DEFINITION column has most of what you see in the Create Table field that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION. Suppose that the original statement was:

CREATE VIEW v AS
  SELECT s2,s1 FROM t
  WHERE s1 > 5
  ORDER BY s1
  WITH CHECK OPTION;

Then the view definition looks like this:

SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1

The CHECK_OPTION column has a value of NONE, CASCADE, or LOCAL.

MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the VIEWS table displays the status of this flag.

If a view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and will be rejected. (Note that even if a view is updatable, it might not be possible to insert into it; for details, refer to Section 21.5.3, “Updatable and Insertable Views”.)

The IS_UPDATABLE flag may be unreliable if a view depends on one or more other views, and one of these underlying views is updated. Regardless of the IS_UPDATABLE value, the server keeps track of the updatability of a view and correctly rejects data change operations to views that are not updatable. If the IS_UPDATABLE value for a view has become inaccurate to due to changes to underlying views, the value can be updated by deleting and recreating the view.

DEFINER: The account of the user who created the view, in 'user_name'@'host_name' format. SECURITY_TYPE has a value of DEFINER or INVOKER.

CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the view was created.

COLLATION_CONNECTION: The session value of the collation_connection system variable when the view was created.