Table of Contents
INFORMATION_SCHEMA provides access to database
metadata, information about
the MySQL server such as the name of a database or table, the data
type of a column, or access privileges. Other terms that are
sometimes used for this information are
data dictionary and
system catalog.
INFORMATION_SCHEMA is a database within each
MySQL instance, the place that stores information about all the
other databases that the MySQL server maintains. The
INFORMATION_SCHEMA database contains several
read-only tables. They are actually views, not base tables, so there
are no files associated with them, and you cannot set triggers on
them. Also, there is no database directory with that name.
Although you can select INFORMATION_SCHEMA as the
default database with a USE
statement, you can only read the contents of tables, not perform
INSERT,
UPDATE, or
DELETE operations on them.
Here is an example of a statement that retrieves information from
INFORMATION_SCHEMA:
mysql>SELECT table_name, table_type, engine->FROM information_schema.tables->WHERE table_schema = 'db5'->ORDER BY table_name;+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | fk | BASE TABLE | InnoDB | | fk2 | BASE TABLE | InnoDB | | goto | BASE TABLE | MyISAM | | into | BASE TABLE | MyISAM | | k | BASE TABLE | MyISAM | | kurs | BASE TABLE | MyISAM | | loop | BASE TABLE | MyISAM | | pk | BASE TABLE | InnoDB | | t | BASE TABLE | MyISAM | | t2 | BASE TABLE | MyISAM | | t3 | BASE TABLE | MyISAM | | t7 | BASE TABLE | MyISAM | | tables | BASE TABLE | MyISAM | | v | VIEW | NULL | | v2 | VIEW | NULL | | v3 | VIEW | NULL | | v56 | VIEW | NULL | +------------+------------+--------+ 17 rows in set (0.01 sec)
Explanation: The statement requests a list of all the tables in
database db5, showing just three pieces of
information: the name of the table, its type, and its storage
engine.
The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
VARCHAR( where N) CHARACTER SET
utf8N is at least 64.
MySQL uses the default collation for this character set
(utf8_general_ci) for all searches, sorts,
comparisons, and other string operations on such columns. However,
searches in INFORMATION_SCHEMA string columns are
also affected by file system case sensitivity. For more information,
see Section 10.1.8.8, “Collation and INFORMATION_SCHEMA Searches”.
Each MySQL user has the right to access these tables, but can see
only the rows in the tables that correspond to objects for which the
user has the proper access privileges. In some cases (for example,
the ROUTINE_DEFINITION column in the
INFORMATION_SCHEMA.ROUTINES table),
users who have insufficient privileges will see
NULL.
The definition for character columns (for example,
TABLES.TABLE_NAME) is generally
VARCHAR( where N) CHARACTER SET
utf8N is at least 64.
MySQL uses the default collation for this character set
(utf8_general_ci) for all searches, sorts,
comparisons, and other string operations on such columns.
Because some MySQL objects are represented as files, searches in
INFORMATION_SCHEMA string columns can be affected
by file system case sensitivity. For more information, see
Section 10.1.8.8, “Collation and INFORMATION_SCHEMA Searches”.
The SELECT ... FROM INFORMATION_SCHEMA statement
is intended as a more consistent way to provide access to the
information provided by the various
SHOW statements that MySQL supports
(SHOW DATABASES,
SHOW TABLES, and so forth). Using
SELECT has these advantages, compared
to SHOW:
It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the
SELECT statement, and only need
to learn some table and column names.
The implementor need not worry about adding keywords.
You can filter, sort, concatenate, and transform the results
from INFORMATION_SCHEMA queries into whatever
format your application needs, such as a data structure or a
text representation to parse.
This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary.
Because SHOW is familiar and widely
used, the SHOW statements remain as
an alternative. In fact, along with the implementation of
INFORMATION_SCHEMA, there are enhancements to
SHOW as described in
Section 21.31, “Extensions to SHOW Statements”.
Each MySQL user has the right to access these tables, but can see
only the rows in the tables that correspond to objects for which the
user has the proper access privileges. In some cases (for example,
the ROUTINE_DEFINITION column in the
INFORMATION_SCHEMA.ROUTINES table),
users who have insufficient privileges see NULL.
These restrictions do not apply for
InnoDB tables; you can see them with
only the PROCESS privilege.
The same privileges apply to selecting information from
INFORMATION_SCHEMA and viewing the same
information through SHOW statements.
In either case, you must have some privilege on an object to see
information about it.
INFORMATION_SCHEMA queries that search for
information from more than one database might take a long time and
impact performance. To check the efficiency of a query, you can use
EXPLAIN. For information about using
EXPLAIN output to tune
INFORMATION_SCHEMA queries, see
Section 8.2.4, “Optimizing INFORMATION_SCHEMA Queries”.
The implementation for the INFORMATION_SCHEMA
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 Schemata. Our intent is approximate
compliance with SQL:2003 core feature F021 Basic
information schema.
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many columns
that are not relevant for our implementation, and added columns that
are MySQL-specific. One such column is the ENGINE
column in the INFORMATION_SCHEMA.TABLES
table.
To avoid using any name that is reserved in the standard or in DB2,
SQL Server, or Oracle, we changed the names of some columns marked
“MySQL extension”. (For example, we changed
COLLATION to TABLE_COLLATION
in the TABLES table.) See the list of
reserved words near the end of this article:
https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5.
The following sections describe each of the tables and columns in
INFORMATION_SCHEMA. For each column, there are
three pieces of information:
“INFORMATION_SCHEMA Name”
indicates the name for the column in the
INFORMATION_SCHEMA table. This corresponds to
the standard SQL name unless the “Remarks” field
says “MySQL extension.”
“SHOW Name”
indicates the equivalent field name in the closest
SHOW statement, if there is one.
“Remarks” provides additional information where
applicable. If this field is NULL, it means
that the value of the column is always NULL.
If this field says “MySQL extension,” the column is
a MySQL extension to standard SQL.
Many sections indicate what SHOW
statement is equivalent to a SELECT
that retrieves information from
INFORMATION_SCHEMA. For
SHOW statements that display
information for the default database if you omit a FROM
clause, you can often
select information for the default database by adding an
db_nameAND TABLE_SCHEMA = SCHEMA() condition to the
WHERE clause of a query that retrieves
information from an INFORMATION_SCHEMA table.
For information about INFORMATION_SCHEMA tables
specific to the InnoDB storage engine,
see Section 21.28, “INFORMATION_SCHEMA Tables for InnoDB”. For information about
INFORMATION_SCHEMA tables specific to the
NDB storage engine (MySQL Cluster), see
Section 21.29, “INFORMATION_SCHEMA Tables for MySQL Cluster”. For information about
INFORMATION_SCHEMA tables specific to the thread
pool plugin, see Section 21.30, “Thread Pool INFORMATION_SCHEMA Tables”.
For answers to questions that are often asked concerning the
INFORMATION_SCHEMA database, see
Section A.7, “MySQL 5.5 FAQ: INFORMATION_SCHEMA”.
The CHARACTER_SETS table provides
information about available character sets.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CHARACTER_SET_NAME | Charset | |
DEFAULT_COLLATE_NAME | Default collation | |
DESCRIPTION | Description | MySQL extension |
MAXLEN | Maxlen | MySQL extension |
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE CHARACTER_SET_NAME LIKE 'wild'] SHOW CHARACTER SET [LIKE 'wild']
The COLLATIONS table provides
information about collations for each character set.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset | MySQL extension |
ID | Id | MySQL extension |
IS_DEFAULT | Default | MySQL extension |
IS_COMPILED | Compiled | MySQL extension |
SORTLEN | Sortlen | MySQL extension |
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.
Collation information is also available from the
SHOW COLLATION statement. The
following statements are equivalent:
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS [WHERE COLLATION_NAME LIKE 'wild'] SHOW COLLATION [LIKE 'wild']
The
COLLATION_CHARACTER_SET_APPLICABILITY
table indicates what character set is applicable for what
collation. The columns are equivalent to the first two display
fields that we get from SHOW
COLLATION.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
COLLATION_NAME | Collation | |
CHARACTER_SET_NAME | Charset |
The COLUMNS table provides
information about columns in tables.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | Field | |
ORDINAL_POSITION | see notes | |
COLUMN_DEFAULT | Default | |
IS_NULLABLE | Null | |
DATA_TYPE | Type | |
CHARACTER_MAXIMUM_LENGTH | Type | |
CHARACTER_OCTET_LENGTH | ||
NUMERIC_PRECISION | Type | |
NUMERIC_SCALE | Type | |
CHARACTER_SET_NAME | ||
COLLATION_NAME | Collation | |
COLUMN_TYPE | Type | MySQL extension |
COLUMN_KEY | Key | MySQL extension |
EXTRA | Extra | MySQL extension |
PRIVILEGES | Privileges | MySQL extension |
COLUMN_COMMENT | Comment | MySQL extension |
Notes:
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.
The following statements are nearly equivalent:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' [AND table_schema = 'db_name'] [AND column_name LIKE 'wild'] SHOW COLUMNS FROMtbl_name[FROMdb_name] [LIKE 'wild']
The COLUMN_PRIVILEGES table provides
information about column privileges. This information comes from
the mysql.columns_priv grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value | |
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
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 following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES SHOW GRANTS ...
The ENGINES table provides
information about storage engines.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
ENGINE | Engine | MySQL extension |
SUPPORT | Support | MySQL extension |
COMMENT | Comment | MySQL extension |
TRANSACTIONS | Transactions | MySQL extension |
XA | XA | MySQL extension |
SAVEPOINTS | Savepoints | MySQL extension |
Notes:
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 13.7.5.17, “SHOW ENGINES Syntax”.
See also Section 13.7.5.17, “SHOW ENGINES Syntax”.
The EVENTS table provides information
about scheduled events, which are discussed in
Section 20.4, “Using the Event Scheduler”. The SHOW
Name values correspond to column names of the
SHOW EVENTS statement.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
EVENT_CATALOG | def, MySQL extension | |
EVENT_SCHEMA | Db | MySQL extension |
EVENT_NAME | Name | MySQL extension |
DEFINER | Definer | MySQL extension |
TIME_ZONE | Time zone | MySQL extension |
EVENT_BODY | MySQL extension | |
EVENT_DEFINITION | MySQL extension | |
EVENT_TYPE | Type | MySQL extension |
EXECUTE_AT | Execute at | MySQL extension |
INTERVAL_VALUE | Interval value | MySQL extension |
INTERVAL_FIELD | Interval field | MySQL extension |
SQL_MODE | MySQL extension | |
STARTS | Starts | MySQL extension |
ENDS | Ends | MySQL extension |
STATUS | Status | MySQL extension |
ON_COMPLETION | MySQL extension | |
CREATED | MySQL extension | |
LAST_ALTERED | MySQL extension | |
LAST_EXECUTED | MySQL extension | |
EVENT_COMMENT | MySQL extension | |
ORIGINATOR | Originator | MySQL extension |
CHARACTER_SET_CLIENT | character_set_client | MySQL extension |
COLLATION_CONNECTION | collation_connection | MySQL extension |
DATABASE_COLLATION | Database Collation | MySQL extension |
Notes:
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
'
format.
user_name'@'host_name'
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.5, 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 5.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 17.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.
Example: Suppose that the user
jon@ghidora creates an event named
e_daily, and then modifies it a few minutes
later using an ALTER EVENT
statement, as shown here:
DELIMITER |
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
DELIMITER ;
ALTER EVENT e_daily
ENABLE;
(Note that comments can span multiple lines.)
This user can then run the following
SELECT statement, and obtain the
output shown:
mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTS>WHERE EVENT_NAME = 'e_daily'>AND EVENT_SCHEMA = 'myschema'\G*************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: test EVENT_NAME: e_daily DEFINER: me@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: DAY SQL_MODE: STARTS: 2008-09-03 12:13:39 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2008-09-03 12:13:39 LAST_ALTERED: 2008-09-03 12:13:39 LAST_EXECUTED: NULL EVENT_COMMENT: Saves total number of sessions then clears the table each day ORIGINATOR: 1 CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci
Times in the EVENTS table are
displayed using the event time zone or the current session time
zone, as described in Section 20.4.4, “Event Metadata”.
See also Section 13.7.5.19, “SHOW EVENTS Syntax”.
The GLOBAL_STATUS
and SESSION_STATUS
tables provide information about server status variables. Their
contents correspond to the information produced by the
SHOW GLOBAL
STATUS and
SHOW SESSION
STATUS statements (see Section 13.7.5.36, “SHOW STATUS Syntax”).
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| VARIABLE_NAME | Variable_name | |
| VARIABLE_VALUE | Value |
Notes:
The VARIABLE_VALUE column for each of these
tables is defined as VARCHAR(1024).
The
GLOBAL_VARIABLES
and
SESSION_VARIABLES
tables provide information about server status variables. Their
contents correspond to the information produced by the
SHOW GLOBAL
VARIABLES and
SHOW SESSION
VARIABLES statements (see
Section 13.7.5.40, “SHOW VARIABLES Syntax”).
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| VARIABLE_NAME | Variable_name | |
| VARIABLE_VALUE | Value |
Notes:
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;
The KEY_COLUMN_USAGE table describes
which key columns have constraints.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
COLUMN_NAME | ||
ORDINAL_POSITION | ||
POSITION_IN_UNIQUE_CONSTRAINT | ||
REFERENCED_TABLE_SCHEMA | ||
REFERENCED_TABLE_NAME | ||
REFERENCED_COLUMN_NAME |
Notes:
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:
One row with CONSTRAINT_NAME =
'PRIMARY',
TABLE_NAME = 't1',
COLUMN_NAME = 's3',
ORDINAL_POSITION =
1,
POSITION_IN_UNIQUE_CONSTRAINT =
NULL.
One row with CONSTRAINT_NAME =
'CO', TABLE_NAME =
't3', COLUMN_NAME =
's2',
ORDINAL_POSITION =
1,
POSITION_IN_UNIQUE_CONSTRAINT =
1.
The PARAMETERS table provides
information about stored procedure and function parameters, and
about return values for stored functions. Parameter information is
similar to the contents of the param_list
column in the mysql.proc table.
INFORMATION_SCHEMA Name | mysql.proc Name | Remarks |
|---|---|---|
SPECIFIC_CATALOG | def | |
SPECIFIC_SCHEMA | db | routine database |
SPECIFIC_NAME | name | routine name |
ORDINAL_POSITION | 1, 2, 3, ... for parameters, 0 for function RETURNS
clause | |
PARAMETER_MODE | IN, OUT, INOUT
(NULL for RETURNS) | |
PARAMETER_NAME | parameter name (NULL for RETURNS) | |
DATA_TYPE | same as for COLUMNS table | |
CHARACTER_MAXIMUM_LENGTH | same as for COLUMNS table | |
CHARACTER_OCTET_LENGTH | same as for COLUMNS table | |
NUMERIC_PRECISION | same as for COLUMNS table | |
NUMERIC_SCALE | same as for COLUMNS table | |
CHARACTER_SET_NAME | same as for COLUMNS table | |
COLLATION_NAME | same as for COLUMNS table | |
DTD_IDENTIFIER | same as for COLUMNS table | |
ROUTINE_TYPE | type | same as for ROUTINES table |
Notes:
The PARAMETERS table was added in
MySQL 5.5.3.
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 ORDINAL_POSITION value is 0.
The PARAMETER_NAME and
PARAMETER_MODE values are
NULL because the return value has no
name and the mode does not apply.
The PARTITIONS table provides
information about table partitions. See
Chapter 19, Partitioning, for more information about
partitioning tables.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
PARTITION_NAME | MySQL extension | |
SUBPARTITION_NAME | MySQL extension | |
PARTITION_ORDINAL_POSITION | MySQL extension | |
SUBPARTITION_ORDINAL_POSITION | MySQL extension | |
PARTITION_METHOD | MySQL extension | |
SUBPARTITION_METHOD | MySQL extension | |
PARTITION_EXPRESSION | MySQL extension | |
SUBPARTITION_EXPRESSION | MySQL extension | |
PARTITION_DESCRIPTION | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
PARTITION_COMMENT | MySQL extension | |
NODEGROUP | MySQL extension | |
TABLESPACE_NAME | MySQL extension |
Notes:
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 19.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 19.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.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
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.
You can also obtain equivalent information using the ndb_desc utility.
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.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
MAX_DATA_LENGTH: The maximum number of
bytes that can be stored in this partition or subpartition.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
INDEX_LENGTH: The length of the index file
for this partition or subpartition, in bytes.
For partitions of NDB tables,
whether the tables use implicit or explicit partitioning, the
INDEX_LENGTH column value is always 0.
However, you can obtain equivalent information using the
ndb_desc utility.
DATA_FREE: The number of bytes allocated to
the partition or subpartition but not used.
For NDB tables, you can also
obtain this information using the ndb_desc
utility.
CREATE_TIME: The time of the partition's or
subpartition's creation.
Prior to MySQL 5.5.44, for partitioned
InnoDB tables, this column was
always NULL. The correct creation time is
shown in MySQL 5.5.44 and later. (Bug #17299181, Bug #69990)
UPDATE_TIME: The time that the partition or
subpartition was last modified.
For partitioned InnoDB tables,
this column is always NULL.
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.5, the display width of this column is 80 characters, and partition comments which exceed this length are truncated to fit. This issue is fixed in MySQL 5.6. (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.)
In MySQL 5.5, there is also only one record in
the PARTITIONS table for a table
using the NDBCLUSTER storage
engine. The same columns are also NULL (or
empty) as for a nonpartitioned table.
The PLUGINS table provides
information about server plugins.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
PLUGIN_NAME | Name | MySQL extension |
PLUGIN_VERSION | MySQL extension | |
PLUGIN_STATUS | Status | MySQL extension |
PLUGIN_TYPE | Type | MySQL extension |
PLUGIN_TYPE_VERSION | MySQL extension | |
PLUGIN_LIBRARY | Library | MySQL extension |
PLUGIN_LIBRARY_VERSION | MySQL extension | |
PLUGIN_AUTHOR | MySQL extension | |
PLUGIN_DESCRIPTION | MySQL extension | |
PLUGIN_LICENSE | MySQL extension | |
LOAD_OPTION | MySQL extension |
Notes:
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 5.5.2, “Installing and Uninstalling Plugins”. This column was added
in MySQL 5.5.7.
For plugins installed with INSTALL
PLUGIN, the PLUGIN_NAME and
PLUGIN_LIBRARY values are also registered in
the mysql.plugin table.
These statements are equivalent:
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_LICENSE FROM INFORMATION_SCHEMA.PLUGINS; SHOW PLUGINS;
For information about plugin data structures that form the basis
of the information in the PLUGINS
table, see Section 24.2, “The MySQL Plugin API”.
Plugin information is also available using the
SHOW PLUGINS statement. See
Section 13.7.5.26, “SHOW PLUGINS Syntax”.
The PROCESSLIST table provides
information about which threads are running.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
ID | Id | MySQL extension |
USER | User | MySQL extension |
HOST | Host | MySQL extension |
DB | db | MySQL extension |
COMMAND | Command | MySQL extension |
TIME | Time | MySQL extension |
STATE | State | MySQL extension |
INFO | Info | MySQL extension |
For an extensive description of the table columns, see Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.
Notes:
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.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST SHOW FULL PROCESSLIST
The PROFILING table provides
statement profiling information. Its contents correspond to the
information produced by the SHOW
PROFILES and SHOW PROFILE
statements (see Section 13.7.5.32, “SHOW PROFILES Syntax”). The table is
empty unless the profiling
session variable is set to 1.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
QUERY_ID | Query_ID | |
SEQ | | |
STATE | Status | |
DURATION | Duration | |
CPU_USER | CPU_user | |
CPU_SYSTEM | CPU_system | |
CONTEXT_VOLUNTARY | Context_voluntary | |
CONTEXT_INVOLUNTARY | Context_involuntary | |
BLOCK_OPS_IN | Block_ops_in | |
BLOCK_OPS_OUT | Block_ops_out | |
MESSAGES_SENT | Messages_sent | |
MESSAGES_RECEIVED | Messages_received | |
PAGE_FAULTS_MAJOR | Page_faults_major | |
PAGE_FAULTS_MINOR | Page_faults_minor | |
SWAPS | Swaps | |
SOURCE_FUNCTION | Source_function | |
SOURCE_FILE | Source_file | |
SOURCE_LINE | Source_line |
Notes:
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.
The REFERENTIAL_CONSTRAINTS table
provides information about foreign keys.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| CONSTRAINT_CATALOG | def | |
| CONSTRAINT_SCHEMA | ||
| CONSTRAINT_NAME | ||
| UNIQUE_CONSTRAINT_CATALOG | def | |
| UNIQUE_CONSTRAINT_SCHEMA | ||
| UNIQUE_CONSTRAINT_NAME | ||
| MATCH_OPTION | ||
| UPDATE_RULE | ||
| DELETE_RULE | ||
| TABLE_NAME | ||
| REFERENCED_TABLE_NAME |
Notes:
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.
The ROUTINES table provides
information about stored routines (both procedures and functions).
The ROUTINES table does not include
user-defined functions (UDFs).
The column named “mysql.proc name”
indicates the mysql.proc table column that
corresponds to the
INFORMATION_SCHEMA.ROUTINES table
column, if any.
INFORMATION_SCHEMA Name | mysql.proc Name | Remarks |
|---|---|---|
SPECIFIC_NAME | specific_name | |
ROUTINE_CATALOG | def | |
ROUTINE_SCHEMA | db | |
ROUTINE_NAME | name | |
ROUTINE_TYPE | type | {PROCEDURE|FUNCTION} |
DATA_TYPE | same as for COLUMNS table | |
CHARACTER_MAXIMUM_LENGTH | same as for COLUMNS table | |
CHARACTER_OCTET_LENGTH | same as for COLUMNS table | |
NUMERIC_PRECISION | same as for COLUMNS table | |
NUMERIC_SCALE | same as for COLUMNS table | |
CHARACTER_SET_NAME | same as for COLUMNS table | |
COLLATION_NAME | same as for COLUMNS table | |
DTD_IDENTIFIER | data type descriptor | |
ROUTINE_BODY | SQL | |
ROUTINE_DEFINITION | body_utf8 | |
EXTERNAL_NAME | NULL | |
EXTERNAL_LANGUAGE | language | NULL |
PARAMETER_STYLE | SQL | |
IS_DETERMINISTIC | is_deterministic | |
SQL_DATA_ACCESS | sql_data_access | |
SQL_PATH | NULL | |
SECURITY_TYPE | security_type | |
CREATED | created | |
LAST_ALTERED | modified | |
SQL_MODE | sql_mode | MySQL extension |
ROUTINE_COMMENT | comment | MySQL extension |
DEFINER | definer | MySQL extension |
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension | |
DATABASE_COLLATION | MySQL extension |
Notes:
MySQL calculates EXTERNAL_LANGUAGE thus:
If mysql.proc.language='SQL',
EXTERNAL_LANGUAGE is
NULL
Otherwise, EXTERNAL_LANGUAGE is what is
in mysql.proc.language. However, we do
not have external languages yet, so it is always
NULL.
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 5.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,
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.
These columns were added in MySQL 5.5.3.
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.
A schema is a database, so the
SCHEMATA table provides information
about databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CATALOG_NAME | def | |
SCHEMA_NAME | Database | |
DEFAULT_CHARACTER_SET_NAME | ||
DEFAULT_COLLATION_NAME | ||
SQL_PATH | NULL |
The following statements are equivalent:
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild'] SHOW DATABASES [LIKE 'wild']
The SCHEMA_PRIVILEGES table provides
information about schema (database) privileges. This information
comes from the mysql.db grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | def, MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a nonstandard table. It takes its values from the
mysql.db table.
The STATISTICS table provides
information about table indexes.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | = Database | |
TABLE_NAME | Table | |
NON_UNIQUE | Non_unique | |
INDEX_SCHEMA | = Database | |
INDEX_NAME | Key_name | |
SEQ_IN_INDEX | Seq_in_index | |
COLUMN_NAME | Column_name | |
COLLATION | Collation | |
CARDINALITY | Cardinality | |
SUB_PART | Sub_part | MySQL extension |
PACKED | Packed | MySQL extension |
NULLABLE | Null | MySQL extension |
INDEX_TYPE | Index_type | MySQL extension |
COMMENT | Comment | MySQL extension |
Notes:
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.
The following statements are equivalent:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' SHOW INDEX FROMtbl_nameFROMdb_name
The TABLES table provides information
about tables in databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | Table_... | |
TABLE_NAME | Table_... | |
TABLE_TYPE | ||
ENGINE | Engine | MySQL extension |
VERSION | Version | The version number of the table's .frm file, MySQL
extension |
ROW_FORMAT | Row_format | MySQL extension |
TABLE_ROWS | Rows | MySQL extension |
AVG_ROW_LENGTH | Avg_row_length | MySQL extension |
DATA_LENGTH | Data_length | MySQL extension |
MAX_DATA_LENGTH | Max_data_length | MySQL extension |
INDEX_LENGTH | Index_length | MySQL extension |
DATA_FREE | Data_free | MySQL extension |
AUTO_INCREMENT | Auto_increment | MySQL extension |
CREATE_TIME | Create_time | MySQL extension |
UPDATE_TIME | Update_time | MySQL extension |
CHECK_TIME | Check_time | MySQL extension |
TABLE_COLLATION | Collation | MySQL extension |
CHECKSUM | Checksum | MySQL extension |
CREATE_OPTIONS | Create_options | MySQL extension |
TABLE_COMMENT | Comment | MySQL extension |
INDEX_COMMENT | Index_comment | MySQL extension |
Notes:
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.)
For NDB tables,
DATA_LENGTH includes data stored in main
memory only; the MAX_DATA_LENGTH and
DATA_FREE columns apply to Disk Data.
For MySQL Cluster Disk Data tables,
MAX_DATA_LENGTH shows the space allocated
for the disk part of a Disk Data table or fragment. (In-memory
data resource usage is reported by the
DATA_LENGTH column.)
The DATA_FREE column shows the free space
in bytes for InnoDB tables.
For MySQL Cluster, DATA_FREE shows the
space allocated on disk for, but not used by, a Disk Data
table or fragment on disk. (In-memory data resource usage is
reported by the DATA_LENGTH column.)
Prior to MySQL 5.5.44, for partitioned
InnoDB tables, the
CREATE_TIME column showed
NULL. This column shows the correct table
creation time for such tables in MySQL 5.5.44 and later. (Bug
#17299181, Bug #69990)
For partitioned InnoDB tables,
the UPDATE_TIME and
CHECK_TIME columns are 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 following statements are equivalent:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' [AND table_name LIKE 'wild'] SHOW TABLES FROMdb_name[LIKE 'wild']
The TABLESPACES table provides
information about active tablespaces. The table was added in MySQL
5.5.3.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLESPACE_NAME | MySQL extension | |
ENGINE | MySQL extension | |
TABLESPACE_TYPE | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
NODEGROUP_ID | MySQL extension | |
TABLESPACE_COMMENT | MySQL extension |
Notes:
The INFORMATION_SCHEMA.TABLESPACES table does
not provide information about InnoDB
tablespaces.
The TABLE_CONSTRAINTS table describes
which tables have constraints.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
CONSTRAINT_CATALOG | def | |
CONSTRAINT_SCHEMA | ||
CONSTRAINT_NAME | ||
TABLE_SCHEMA | ||
TABLE_NAME | ||
CONSTRAINT_TYPE |
Notes:
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.
The TABLE_PRIVILEGES table provides
information about table privileges. This information comes from
the mysql.tables_priv grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value | |
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
PRIVILEGE_TYPE | ||
IS_GRANTABLE |
Notes:
PRIVILEGE_TYPE can contain one (and only
one) of these values: SELECT,
INSERT,
UPDATE,
REFERENCES,
ALTER,
INDEX,
DROP,
CREATE VIEW.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES SHOW GRANTS ...
The TRIGGERS table provides
information about triggers. To see information about a table's
triggers, you must have the TRIGGER
privilege for the table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TRIGGER_CATALOG | def | |
TRIGGER_SCHEMA | ||
TRIGGER_NAME | Trigger | |
EVENT_MANIPULATION | Event | |
EVENT_OBJECT_CATALOG | def | |
EVENT_OBJECT_SCHEMA | ||
EVENT_OBJECT_TABLE | Table | |
ACTION_ORDER | 0 | |
ACTION_CONDITION | NULL | |
ACTION_STATEMENT | Statement | |
ACTION_ORIENTATION | ROW | |
ACTION_TIMING | Timing | |
ACTION_REFERENCE_OLD_TABLE | NULL | |
ACTION_REFERENCE_NEW_TABLE | NULL | |
ACTION_REFERENCE_OLD_ROW | OLD | |
ACTION_REFERENCE_NEW_ROW | NEW | |
CREATED | Created | |
SQL_MODE | sql_mode | MySQL extension |
DEFINER | Definer | MySQL extension |
CHARACTER_SET_CLIENT | character_set_client | MySQL extension |
COLLATION_CONNECTION | collation_connection | MySQL extension |
DATABASE_COLLATION | Database Collation | MySQL extension |
Notes:
The names in the “SHOW
Name” column refer to the SHOW
TRIGGERS statement, not SHOW
CREATE TRIGGER. See Section 13.7.5.39, “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 20.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 all similar triggers on
the same table. This value is always 0,
because it is not possible to have more than one trigger with
the same EVENT_MANIPULATION and
ACTION_TIMING on the same table.
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'.
SQL_MODE: The SQL mode in effect when the
trigger was created, and under which the trigger executes. For
the permitted values, see Section 5.1.7, “Server SQL Modes”.
DEFINER: The account of the user who
created the trigger, in
'
format.
user_name'@'host_name'
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,
ACTION_REFERENCE_NEW_TABLE, and
CREATED.
Example, using the ins_sum trigger defined in
Section 20.3, “Using Triggers”:
mysql>SELECT * FROM INFORMATION_SCHEMA.TRIGGERS->WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G*************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: ins_sum EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: account ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: SET @sum = @sum + NEW.amount ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: DEFINER: me@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci
The USER_PRIVILEGES table provides
information about global privileges. This information comes from
the mysql.user grant table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
GRANTEE | '
value, MySQL extension | |
TABLE_CATALOG | def, MySQL extension | |
PRIVILEGE_TYPE | MySQL extension | |
IS_GRANTABLE | MySQL extension |
Notes:
This is a nonstandard table. It takes its values from the
mysql.user table.
The VIEWS table provides information
about views in databases. You must have the
SHOW VIEW privilege to access this
table.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | ||
TABLE_NAME | ||
VIEW_DEFINITION | ||
CHECK_OPTION | ||
IS_UPDATABLE | ||
DEFINER | ||
SECURITY_TYPE | ||
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension |
Notes:
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. It means that the server always knows whether a
view is updatable.
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 20.5.3, “Updatable and Insertable Views”.)
DEFINER: The account of the user who
created the view, in
'
format. user_name'@'host_name'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.
MySQL lets you use different
sql_mode settings to tell the
server the type of SQL syntax to support. For example, you might
use the ANSI SQL mode to ensure
MySQL correctly interprets the standard SQL concatenation
operator, the double bar (||), in your queries.
If you then create a view that concatenates items, you might worry
that changing the sql_mode
setting to a value different from
ANSI could cause the view to
become invalid. But this is not the case. No matter how you write
out a view definition, MySQL always stores it the same way, in a
canonical form. Here is an example that shows how the server
changes a double bar concatenation operator to a
CONCAT() function:
mysql>SET sql_mode = 'ANSI';Query OK, 0 rows affected (0.00 sec) mysql>CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;Query OK, 0 rows affected (0.00 sec) mysql>SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS->WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';+----------------------------------+ | VIEW_DEFINITION | +----------------------------------+ | select concat('a','b') AS `col1` | +----------------------------------+ 1 row in set (0.00 sec)
The advantage of storing a view definition in canonical form is
that changes made later to the value of
sql_mode will not affect the
results from the view. However an additional consequence is that
comments prior to SELECT are
stripped from the definition by the server.
This section provides table definitions for
InnoDB INFORMATION_SCHEMA
tables. For related information and examples, see
Section 14.18, “InnoDB INFORMATION_SCHEMA Tables”.
InnoDB INFORMATION_SCHEMA
tables can be used to monitor ongoing InnoDB
activity, to detect inefficiencies before they turn into issues, or
to troubleshoot performance and capacity issues. As your database
becomes bigger and busier, running up against the limits of your
hardware capacity, you monitor and tune these aspects to keep the
database running smoothly.
The INNODB_CMP and
INNODB_CMP_RESET tables contain status
information on operations related to compressed
InnoDB tables.
Table 21.1 Columns of INNODB_CMP and INNODB_CMP_RESET
| Column name | Description |
|---|---|
PAGE_SIZE | Compressed page size in bytes. |
COMPRESS_OPS | Number of times a B-tree page of the size PAGE_SIZE
has been compressed. Pages are compressed whenever an
empty page is created or the space for the uncompressed
modification log runs out. |
COMPRESS_OPS_OK | Number of times a B-tree page of the size PAGE_SIZE
has been successfully compressed. This count should never
exceed COMPRESS_OPS. |
COMPRESS_TIME | Total time in seconds spent in attempts to compress B-tree pages of the
size PAGE_SIZE. |
UNCOMPRESS_OPS | Number of times a B-tree page of the size PAGE_SIZE
has been uncompressed. B-tree pages are uncompressed
whenever compression fails or at first access when the
uncompressed page does not exist in the buffer pool. |
UNCOMPRESS_TIME | Total time in seconds spent in uncompressing B-tree pages of the size
PAGE_SIZE. |
Example:
mysql> 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
*************************** 2. row ***************************
page_size: 2048
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 3. row ***************************
page_size: 4096
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 4. row ***************************
page_size: 8192
compress_ops: 199755
compress_ops_ok: 112015
compress_time: 83
uncompress_ops: 74253
uncompress_time: 13
*************************** 5. row ***************************
page_size: 16384
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0Notes:
Use these tables to measure the effectiveness of
InnoDB table compression in your database.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of these tables including data
types and default values.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.18.1.3, “Using the Compression Information Schema Tables”.
The INNODB_CMPMEM and
INNODB_CMPMEM_RESET tables contain status
information on compressed pages within the
InnoDB buffer pool.
Table 21.2 Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET
| Column name | Description |
|---|---|
PAGE_SIZE | Block size in bytes. Each record of this table describes blocks of this size. |
BUFFER_POOL_INSTANCE | A unique identifier for the buffer pool instance. |
PAGES_USED | Number of blocks of the size PAGE_SIZE that are
currently in use. |
PAGES_FREE | Number of blocks of the size PAGE_SIZE that are
currently available for allocation. This column shows the
external fragmentation in the memory pool. Ideally, these
numbers should be at most 1. |
RELOCATION_OPS | Number of times a block of the size PAGE_SIZE has
been relocated. The buddy system can relocate the
allocated “buddy neighbor” of a freed block
when it tries to form a bigger freed block. Reading from
the table INNODB_CMPMEM_RESET resets
this count. |
RELOCATION_TIME | Total time in microseconds spent in relocating blocks of the size
PAGE_SIZE. Reading from the table
INNODB_CMPMEM_RESET resets this count. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM \G
*************************** 1. row ***************************
page_size: 1024
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 2. row ***************************
page_size: 2048
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 3. row ***************************
page_size: 4096
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 4. row ***************************
page_size: 8192
buffer_pool_instance: 0
pages_used: 9043
pages_free: 1
relocation_ops: 2457
relocation_time: 0
*************************** 5. row ***************************
page_size: 16384
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0Notes:
Use these tables to measure the effectiveness of
InnoDB table compression in your database.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of these tables including data
types and default values.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.18.1.3, “Using the Compression Information Schema Tables”.
The INNODB_TRX table contains
information about every transaction currently executing inside
InnoDB, including whether the transaction is
waiting for a lock, when the transaction started, and the SQL
statement the transaction is executing, if any.
Table 21.3 INNODB_TRX Columns
| Column name | Description |
|---|---|
TRX_ID | Unique transaction ID number, internal to InnoDB.
(Starting in MySQL 5.6, these IDs are not created for
transactions that are read-only and non-locking. See
Optimizing InnoDB Read-Only Transactions for details.) |
TRX_WEIGHT | The weight of a transaction, reflecting (but not necessarily the exact
count of) the number of rows altered and the number of
rows locked by the transaction. To resolve a deadlock,
InnoDB selects the transaction with the
smallest weight as the “victim” to rollback.
Transactions that have changed non-transactional tables
are considered heavier than others, regardless of the
number of altered and locked rows. |
TRX_STATE | Transaction execution state. One of RUNNING,
LOCK WAIT, ROLLING
BACK or COMMITTING. |
TRX_STARTED | Transaction start time. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if
TRX_STATE is LOCK
WAIT, otherwise NULL).
Details about the lock can be found by joining with
INNODB_LOCKS on
LOCK_ID. |
TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if
TRX_STATE is LOCK
WAIT, otherwise NULL). |
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with
PROCESSLIST on ID.
See
Section 14.18.2.3.1, “Potential Inconsistency with PROCESSLIST Data”. |
TRX_QUERY | The SQL query that is being executed by the transaction. |
TRX_OPERATION_STATE | The transaction's current operation, or NULL. |
TRX_TABLES_IN_USE | The number of InnoDB tables used while processing the
current SQL statement of this transaction. |
TRX_TABLES_LOCKED | Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) |
TRX_LOCK_STRUCTS | The number of locks reserved by the transaction. |
TRX_LOCK_MEMORY_BYTES | Total size taken up by the lock structures of this transaction in memory. |
TRX_ROWS_LOCKED | Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction. |
TRX_ROWS_MODIFIED | The number of modified and inserted rows in this transaction. |
TRX_CONCURRENCY_TICKETS | A value indicating how much work the current transaction can do before
being swapped out, as specified by the
innodb_concurrency_tickets option. |
TRX_ISOLATION_LEVEL | The isolation level of the current transaction. |
TRX_UNIQUE_CHECKS | Whether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_FOREIGN_KEY_CHECKS | Whether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_LAST_FOREIGN_KEY_ERROR | Detailed error message for last FK error, or NULL. |
TRX_ADAPTIVE_HASH_LATCHED | Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.) |
TRX_ADAPTIVE_HASH_TIMEOUT | Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G
*************************** 1. row ***************************
trx_id: 3B7
trx_state: RUNNING
trx_started: 2014-11-19 14:33:45
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 2
trx_query: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 1
trx_lock_memory_bytes: 376
trx_rows_locked: 0
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_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: 3B6
trx_state: RUNNING
trx_started: 2014-11-19 14:32:38
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 94055
trx_mysql_thread_id: 1
trx_query: DELETE FROM employees.salaries WHERE salary > 75000
trx_operation_state: updating or deleting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 841
trx_lock_memory_bytes: 129464
trx_rows_locked: 392752
trx_rows_modified: 93214
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_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 14.18.2.3, “Data Persistence and Consistency for InnoDB Transaction and Locking Tables”.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.18.2.1, “Usage Examples for InnoDB Transaction and Locking Tables”.
The INNODB_LOCKS table contains
information about each lock that an InnoDB
transaction has requested but not yet acquired, and each lock that
a transaction holds that is blocking another transaction.
Table 21.4 INNODB_LOCKS Columns
| Column name | Description |
|---|---|
LOCK_ID | Unique lock ID number, internal to InnoDB. Treat it
as an opaque string. Although LOCK_ID
currently contains TRX_ID, the format
of the data in LOCK_ID is not
guaranteed to remain the same in future releases. Do not
write programs that parse the LOCK_ID
value. |
LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction
can be found by joining with INNODB_TRX
on TRX_ID. |
LOCK_MODE | Mode of the lock. One of S[,GAP],
X[,GAP], IS[,GAP],
IX[,GAP], AUTO_INC,
or UNKNOWN. Lock modes other than
AUTO_INC and UNKNOWN
will indicate GAP locks, if present. Refer to
Section 14.8.1, “InnoDB Locking” for information about
S, X,
IS, and IX locks.
Refer to Section 14.8.1, “InnoDB Locking” for information
about GAP locks. |
LOCK_TYPE | Type of the lock. One of RECORD or
TABLE for record (row) level or table
level locks, respectively. |
LOCK_TABLE | Name of the table that has been locked or contains locked records. |
LOCK_INDEX | Name of the index if LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_SPACE | Tablespace ID of the locked record if
LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_PAGE | Page number of the locked record if
LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_REC | Heap number of the locked record within the page if
LOCK_TYPE='RECORD', otherwise
NULL. |
LOCK_DATA | Primary key value(s) of the locked record if
LOCK_TYPE='RECORD', otherwise
NULL. This column contains the value(s)
of the primary key column(s) in the locked row, formatted
as a valid SQL string (ready to be copied to SQL
statements). If there is no primary key then the
InnoDB internal unique row ID number is
used. If a gap lock is taken for key values or ranges
above the largest value in the index,
LOCK_DATA reports “supremum
pseudo-record”. When the page containing the locked
record is not in the buffer pool (in the case that it was
paged out to disk while the lock was held),
InnoDB does not fetch the page from
disk, to avoid unnecessary disk operations. Instead,
LOCK_DATA is set to
NULL. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS \G
*************************** 1. row ***************************
lock_id: 3723:72:3:2
lock_trx_id: 3723
lock_mode: X
lock_type: RECORD
lock_table: `mysql`.`t`
lock_index: PRIMARY
lock_space: 72
lock_page: 3
lock_rec: 2
lock_data: 1, 9
*************************** 2. row ***************************
lock_id: 3722:72:3:2
lock_trx_id: 3722
lock_mode: S
lock_type: RECORD
lock_table: `mysql`.`t`
lock_index: PRIMARY
lock_space: 72
lock_page: 3
lock_rec: 2
lock_data: 1, 9Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 14.18.2.3, “Data Persistence and Consistency for InnoDB Transaction and Locking Tables”.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.18.2.1, “Usage Examples for InnoDB Transaction and Locking Tables”.
The INNODB_LOCK_WAITS table contains
one or more rows for each blocked InnoDB
transaction, indicating the lock it has requested and any locks
that are blocking that request.
Table 21.5 INNODB_LOCK_WAITS Columns
| Column name | Description |
|---|---|
REQUESTING_TRX_ID | ID of the requesting transaction. |
REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the
lock can be found by joining with
INNODB_LOCKS on
LOCK_ID. |
BLOCKING_TRX_ID | ID of the blocking transaction. |
BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from
proceeding. Details about the lock can be found by joining
with INNODB_LOCKS on
LOCK_ID. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS \G *************************** 1. row *************************** requesting_trx_id: 3B7 requested_lock_id: 3B7:0:306:2 blocking_trx_id: 3B6 blocking_lock_id: 3B6:0:306:2 1 row in set (0.00 sec)
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 14.18.2.3, “Data Persistence and Consistency for InnoDB Transaction and Locking Tables”.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.
You must have the PROCESS privilege to
query this table.
For usage information, see Section 14.18.2.1, “Usage Examples for InnoDB Transaction and Locking Tables”.
The INNODB_BUFFER_PAGE table holds information
about each page in the
InnoDB buffer
pool.
For related usage information and examples, see Section 14.18.3, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Querying the INNODB_BUFFER_PAGE table can
introduce significant performance overhead. Do not query this
table on a production system unless you are aware of the
performance impact that your query may have, and have determined
it to be acceptable. To avoid impacting performance, reproduce
the issue you want to investigate on a test instance and query
the INNODB_BUFFER_PAGE table on the test
instance.
Table 21.6 INNODB_BUFFER_PAGE Columns
| Column name | Description |
|---|---|
POOL_ID | Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. |
BLOCK_ID | Buffer Pool Block ID. |
SPACE | Tablespace ID. Uses the same value as in
INNODB_SYS_TABLES.SPACE. |
PAGE_NUMBER | Page number. |
PAGE_TYPE | Page type. One of ALLOCATED (Freshly 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
comp BLOB page), IBUF_INDEX (Insert
buffer index), UNKNOWN (unknown). |
FLUSH_TYPE | Flush type. |
FIX_COUNT | Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. |
IS_HASHED | Whether hash index has been built on this page. |
NEWEST_MODIFICATION | Log Sequence Number of the youngest modification. |
OLDEST_MODIFICATION | Log Sequence Number of the oldest modification. |
ACCESS_TIME | An abstract number used to judge the first access time of the page. |
TABLE_NAME | Name of the table the page belongs to. This column is only applicable to
pages of type INDEX. |
INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered
index or a secondary index. This column is only applicable
to pages of type INDEX. |
NUMBER_RECORDS | Number of records within the page. |
DATA_SIZE | Sum of the sizes of the records. This column is only applicable to pages
of type INDEX. |
COMPRESSED_SIZE | Compressed page size. Null for pages that are not compressed. |
PAGE_STATE | Page state. A page with valid data has one of the following states:
FILE_PAGE (buffers a page of data from
a file), MEMORY (buffers a page from an
in-memory object), COMPRESSED. Other
possible states (managed by InnoDB)
are: NULL,
READY_FOR_USE,
NOT_USED,
REMOVE_HASH. |
IO_FIX | Specifies whether any I/O is pending for this page:
IO_NONE = no pending I/O,
IO_READ = read pending,
IO_WRITE = write pending. |
IS_OLD | Specifies whether or not the block is in the sublist of old blocks in the LRU list. |
FREE_PAGE_CLOCK | The value of the freed_page_clock counter when the
block was the last placed at the head of the LRU list. The
freed_page_clock counter tracks the
number of blocks removed from the end of the LRU list. |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE BLOCK_ID=9\G
*************************** 1. row ***************************
POOL_ID: 0
BLOCK_ID: 9
SPACE: 0
PAGE_NUMBER: 8019
PAGE_TYPE: INDEX
FLUSH_TYPE: 2
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 226918754
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3376847655
TABLE_NAME: employees/salaries
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 8
1 row in set (0.10 sec)Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.
You must have the PROCESS privilege to
query this table.
When tables, table rows, partitions, or indexes are deleted,
associated pages remain in the buffer pool until space is
required for other data. The
INNODB_BUFFER_PAGE table reports
information about these pages until they are evicted from the
buffer pool. For more information about how the
InnoDB manages buffer pool data, see
Section 14.9.2.1, “The InnoDB Buffer Pool”.
The INNODB_BUFFER_PAGE_LRU table holds
information about the pages in the InnoDB
buffer pool, in particular
how they are ordered that determines which pages to
evict from the buffer pool
when it becomes full.
The INNODB_BUFFER_PAGE_LRU table has
the same columns as the
INNODB_BUFFER_PAGE table, except that
the INNODB_BUFFER_PAGE_LRU table has
an LRU_POSITION column instead of a
BLOCK_ID column.
For related usage information and examples, see Section 14.18.3, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Querying the INNODB_BUFFER_PAGE_LRU table can
introduce significant performance overhead. Do not query this
table on a production system unless you are aware of the
performance impact that your query may have and have determined
it to be acceptable. To avoid impacting performance, reproduce
the issue you want to investigate on a test instance and query
the INNODB_BUFFER_PAGE_LRU table on the test
instance.
Table 21.7 INNODB_BUFFER_PAGE_LRU Columns
| Column name | Description |
|---|---|
POOL_ID | Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. |
LRU_POSITION | The position of the page in the LRU list. |
SPACE | Tablespace ID. Uses the same value as in
INNODB_SYS_TABLES.SPACE. |
PAGE_NUMBER | Page number. |
PAGE_TYPE | Page type. One of ALLOCATED (Freshly 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
comp BLOB page), IBUF_INDEX (Insert
buffer index), UNKNOWN (unknown). |
FLUSH_TYPE | Flush type. |
FIX_COUNT | Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. |
IS_HASHED | Whether hash index has been built on this page. |
NEWEST_MODIFICATION | Log Sequence Number of the youngest modification. |
OLDEST_MODIFICATION | Log Sequence Number of the oldest modification. |
ACCESS_TIME | An abstract number used to judge the first access time of the page. |
TABLE_NAME | Name of the table the page belongs to. This column is only applicable to
pages of type INDEX. |
INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered
index or a secondary index. This column is only applicable
to pages of type INDEX. |
NUMBER_RECORDS | Number of records within the page. |
DATA_SIZE | Sum of the sizes of the records. This column is only applicable to pages
of type INDEX. |
COMPRESSED_SIZE | Compressed page size. Null for pages that are not compressed. |
PAGE_STATE | Page state. A page with valid data has one of the following states:
FILE_PAGE (buffers a page of data from
a file), MEMORY (buffers a page from an
in-memory object), COMPRESSED. Other
possible states (managed by InnoDB)
are: NULL,
READY_FOR_USE,
NOT_USED,
REMOVE_HASH. |
IO_FIX | Specifies whether any I/O is pending for this page:
IO_NONE = no pending I/O,
IO_READ = read pending,
IO_WRITE = write pending. |
IS_OLD | Specifies whether or not the block is in the sublist of old blocks in the LRU list. |
FREE_PAGE_CLOCK | The value of the freed_page_clock counter when the
block was the last placed at the head of the LRU list. The
freed_page_clock counter tracks the
number of blocks removed from the end of the LRU list. |
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
LRU_POSITION: 0
SPACE: 0
PAGE_NUMBER: 7485
PAGE_TYPE: INDEX
FLUSH_TYPE: 2
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 216319316
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3376846384
TABLE_NAME: employees/salaries
INDEX_NAME: emp_no
NUMBER_RECORDS: 1300
DATA_SIZE: 15600
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 0
1 row in set (0.10 sec)
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS privilege to
query this table.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.
Querying this table can require MySQL to allocate a large block of contiguous memory, more than 64 bytes time the number of active pages in the buffer pool. This allocation could potentially cause an out-of-memory error, especially for systems with multi-gigabyte buffer pools.
Querying this table requires MySQL to lock the data structure representing the buffer pool while traversing the LRU list, which can reduce concurrency, especially for systems with multi-gigabyte buffer pools.
When tables, table rows, partitions, or indexes are deleted,
associated pages remain in the buffer pool until space is
required for other data. The
INNODB_BUFFER_PAGE_LRU table reports
information about these pages until they are evicted from the
buffer pool. For more information about how the
InnoDB manages buffer pool data, see
Section 14.9.2.1, “The InnoDB Buffer Pool”.
The INNODB_BUFFER_POOL_STATS table provides
much of the same buffer pool information provided in SHOW
ENGINE INNODB STATUS output. Much of the same
information may also be obtained using InnoDB
buffer pool server status
variables.
The idea of making pages in the buffer pool “young” or “not young” refers to transferring them between the sublists at the head and tail of the buffer pool data structure. Pages made “young” take longer to age out of the buffer pool, while pages made “not young” are moved much closer to the point of eviction.
For related usage information and examples, see Section 14.18.3, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
Table 21.8 INNODB_BUFFER_POOL_STATS Columns
| Column name | Description |
|---|---|
POOL_ID | Buffer Pool ID. A unique identifier to distinguish between multiple buffer pool instances. |
POOL_SIZE | The InnoDB buffer pool size in pages. |
FREE_BUFFERS | The number of free pages in the InnoDB buffer pool |
DATABASE_PAGES | The number of pages in the InnoDB buffer pool
containing data. The number includes both dirty and clean
pages. |
OLD_DATABASE_PAGES | The number of pages in the old buffer pool sublist. |
MODIFIED_DATABASE_PAGES | The number of modified (dirty) database pages |
PENDING_DECOMPRESS | The number of pages pending decompression |
PENDING_READS | The number of pending reads |
PENDING_FLUSH_LRU | The number of pages pending flush in the LRU |
PENDING_FLUSH_LIST | The number of pages pending flush in the flush list |
PAGES_MADE_YOUNG | The number of pages made young |
PAGES_NOT_MADE_YOUNG | The number of pages not made young |
PAGES_MADE_YOUNG_RATE | The number of pages made young per second (pages made young since the last printout / time elapsed) |
PAGES_MADE_NOT_YOUNG_RATE | The number of pages not made per second (pages not made young since the last printout / time elapsed) |
NUMBER_PAGES_READ | The number of pages read |
NUMBER_PAGES_CREATED | The number of pages created |
NUMBER_PAGES_WRITTEN | The number of pages written |
PAGES_READ_RATE | The number of pages read per second (pages read since the last printout / time elapsed) |
PAGES_CREATE_RATE | The number of pages created per second (pages created since the last printout / time elapsed) |
PAGES_WRITTEN_RATE | The number of pages written per second (pages written since the last printout / time elapsed) |
NUMBER_PAGES_GET | The number of logical read requests. |
HIT_RATE | The buffer pool hit rate |
YOUNG_MAKE_PER_THOUSAND_GETS | The number of pages made young per thousand gets |
NOT_YOUNG_MAKE_PER_THOUSAND_GETS | The number of pages not made young per thousand gets |
NUMBER_PAGES_READ_AHEAD | The number of pages read ahead |
NUMBER_READ_AHEAD_EVICTED | The number of pages read into the InnoDB buffer pool
by the read-ahead background thread that were subsequently
evicted without having been accessed by queries. |
READ_AHEAD_RATE | The read ahead rate per second (pages read ahead since the last printout / time elapsed) |
READ_AHEAD_EVICTED_RATE | The number of read ahead pages evicted without access per second (read ahead pages not accessed since the last printout / time elapsed) |
LRU_IO_TOTAL | LRU IO total |
LRU_IO_CURRENT | LRU IO for the current interval |
UNCOMPRESS_TOTAL | Total number of pages decompressed |
UNCOMPRESS_CURRENT | The number of pages decompressed in the current interval |
Example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS \G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 0
DATABASE_PAGES: 8014
OLD_DATABASE_PAGES: 2938
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 7380
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 2723
NUMBER_PAGES_CREATED: 12657
NUMBER_PAGES_WRITTEN: 16181
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 28952710
HIT_RATE: 1000
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 2469
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Use DESCRIBE or
SHOW COLUMNS to view additional
information about the columns of this table including data
types and default values.
You must have the PROCESS privilege to
query this table.
The following sections provide information about
INFORMATION_SCHEMA tables which are specific to
MySQL Cluster. The FILES table was
added in MySQL Server 5.1.6 as part of MySQL Cluster data-on-disk
support (it is available in standard MySQL 5.5 but is not used
there). The
ndb_transid_mysql_connection_map table
was added as part of additions made to MySQL Cluster's
ndbinfo information database in
MySQL Cluster NDB 7.2.2; it is implemented as an
INFORMATION_SCHEMA plugin available only in MySQL
Cluster binaries or source, and does not exist in MySQL Server 5.5.
Additional statistical and other data about MySQL Cluster
transactions, operations, threads, blocks, and other aspects of
performance can be obtained from the tables in the
ndbinfo database. Information
about these tables, see Section 18.5.10, “The ndbinfo MySQL Cluster Information Database”.
The FILES table provides information
about the files in which MySQL NDB
Disk Data tables are stored.
This 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 18.4.10, “ndb_desc — Describe NDB Tables”.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
FILE_ID | MySQL extension | |
FILE_NAME | MySQL extension | |
FILE_TYPE | MySQL extension | |
TABLESPACE_NAME | MySQL extension | |
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
LOGFILE_GROUP_NUMBER | MySQL extension | |
ENGINE | MySQL extension | |
FULLTEXT_KEYS | MySQL extension | |
DELETED_ROWS | MySQL extension | |
UPDATE_COUNT | MySQL extension | |
FREE_EXTENTS | MySQL extension | |
TOTAL_EXTENTS | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
INITIAL_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
CREATION_TIME | MySQL extension | |
LAST_UPDATE_TIME | MySQL extension | |
LAST_ACCESS_TIME | MySQL extension | |
RECOVER_TIME | MySQL extension | |
TRANSACTION_COUNTER | MySQL extension | |
VERSION | MySQL extension | |
ROW_FORMAT | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
STATUS | MySQL extension | |
EXTRA | MySQL extension |
Notes:
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.
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';
The byte values produced by the preceding queries are
approximations only, and their precision is inversely
proportional to the value of EXTENT_SIZE.
That is, the larger EXTENT_SIZE becomes,
the less accurate the approximations are.
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 13.1.18, “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.
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:
VERSION
ROW_FORMAT
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
DATA_FREE
CREATE_TIME
UPDATE_TIME
CHECK_TIME
CHECKSUM
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. 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. 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 18.5.12, “MySQL Cluster Disk Data Tables”.
The ndb_transid_mysql_connection_map table
provides a mapping between NDB transactions,
NDB transaction coordinators, and MySQL Servers
attached to a MySQL Cluster as API nodes. This information is used
when populating the
server_operations and
server_transactions tables of
the ndbinfo MySQL Cluster
information database.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
mysql_connection_id | MySQL Server connection ID | |
node_id | Transaction coordinator node ID | |
ndb_transid | NDB transaction ID |
The mysql_connection_id is the same as the
connection or session ID shown in the output of
SHOW PROCESSLIST.
There are no SHOW statements associated with
this table.
This is a nonstandard table, added in MySQL Cluster NDB 7.2.2. It
is implemented as an INFORMATION_SCHEMA plugin;
you can verify that it is supported by checking the output of
SHOW PLUGINS. If
ndb_transid_mysql_connection_map support is
enabled, the output from this statement includes a plugin having
this name, of type INFORMATION SCHEMA, and
having status ACTIVE, as shown here (using
emphasized text):
mysql> 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 |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbinfo | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndb_transid_mysql_connection_map | ACTIVE | INFORMATION SCHEMA | 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 |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+----------------------------------+--------+--------------------+---------+---------+
22 rows in set (0.00 sec)
The plugin is enabled by default. You can disable it (or force the
server not to run unless the plugin starts) by starting the server
with the
--ndb-transid-mysql-connection-map
option. If the plugin is disabled, the status is shown by
SHOW PLUGINS as
DISABLED. The plugin cannot be enabled or
disabled at runtime.
Although the names of this table and its columns are displayed using lowercase, you can use uppercase or lowercase when referring to them in SQL statements.
For this table to be created, the MySQL Server must be a binary
that wass supplied with the MySQL Cluster distribution, or one
that was built from the MySQL Cluster sources with
NDB storage engine support enabled.
It is not available in the standard MySQL 5.5 Server.
The following sections describe the
INFORMATION_SCHEMA tables associated with the
thread pool plugin. They provide information about thread pool
operation:
TP_THREAD_STATE: Information about
thread pool thread states
TP_THREAD_GROUP_STATE: Information
about thread pool thread group states
TP_THREAD_GROUP_STATS: Thread group
statistics
Rows in these tables represent snapshots in time. In the case of
TP_THREAD_STATE, all rows for a thread
group comprise a snapshot in time. Thus, the MySQL server holds the
mutex of the thread group while producing the snapshot. But it does
not hold mutexes on all thread groups at the same time, to prevent a
statement against TP_THREAD_STATE from
blocking the entire MySQL server.
The thread pool INFORMATION_SCHEMA tables are
implemented by individual plugins and the decision whether to load
one can be made independently of the others (see
Section 5.5.4.2, “Thread Pool Installation”). However, the content of
all the tables depends on the thread pool plugin being enabled. If a
table plugin is enabled but the thread pool plugin is not, the table
becomes visible and can be accessed but will be empty.
This table has one row per thread created by the thread pool to handle connections. The table has these columns:
TP_GROUP_ID
The thread group ID.
TP_THREAD_NUMBER
The ID of the thread within its thread group.
TP_GROUP_ID and
TP_THREAD_NUMBER together provide a unique
key within the table.
PROCESS_COUNT
The 10ms interval in which the statement that uses this thread is currently executing. 0 means no statement is executing, 1 means it is in the first 10ms, and so forth.
WAIT_TYPE
The type of wait for the thread. NULL means
the thread is not blocked. Otherwise, the thread is blocked by
a call to thd_wait_begin() and the value
specifies the type of wait. The
xxx_WAITTP_THREAD_GROUP_STATS
table accumulate counts for each wait type.
The WAIT_TYPE value is a string that
describes the type of wait, as shown in the following table.
Table 21.9 WAIT_TYPE Values
| Wait Type | Meaning |
|---|---|
THD_WAIT_SLEEP | Waiting for sleep |
THD_WAIT_DISKIO | Waiting for Disk IO |
THD_WAIT_ROW_LOCK | Waiting for row lock |
THD_WAIT_GLOBAL_LOCK | Waiting for global lock |
THD_WAIT_META_DATA_LOCK | Waiting for metadata lock |
THD_WAIT_TABLE_LOCK | Waiting for table lock |
THD_WAIT_USER_LOCK | Waiting for user lock |
THD_WAIT_BINLOG | Waiting for binlog |
THD_WAIT_GROUP_COMMIT | Waiting for group commit |
THD_WAIT_SYNC | Waiting for fsync |
This table has one row per thread group in the thread pool. Each row provides information about the current state of a group. The table has these columns:
TP_GROUP_ID
The thread group ID. This is a unique key within the table.
CONSUMER THREADS
The number of consumer threads. There is at most one thread ready to start executing if the active threads become stalled or blocked.
RESERVE_THREADS
The number of threads in the reserved state. This means that they will not be started until there is a need to wake a new thread and there is no consumer thread. This is where most threads end up when the thread group has created more threads than needed for normal operation. Often a thread group needs additional threads for a short while and then does not need them again for a while. In this case, they go into the reserved state and remain until needed again. They take up some extra memory resources, but no extra computing resources.
CONNECTION_COUNT
The number of connections using this thread group.
QUEUED_QUERIES
The number of statements waiting in the high-priority queue.
QUEUED_TRANSACTIONS
The number of statements waiting in the low-priority queue. These are the initial statements for transactions that have not started, so they also represent queued transactions.
STALL_LIMIT
The value of the
thread_pool_stall_limit
variable on the thread group. This is the same value for all
thread groups.
PRIO_KICKUP_TIMER
The value of the
thread_pool_prio_kickup_timer
on the thread group. This is the same value for all thread
groups.
ALGORITHM
The value of the
thread_pool_algorithm on the
thread group. This is the same value for all thread groups.
THREAD_COUNT
The number of threads started in the thread pool as part of this thread group.
ACTIVE_THREAD_COUNT
The number of threads active executing statements.
MAX_THREAD_IDS_IN_GROUP
The maximum thread ID of the threads in the group. This is the
same as MAX(TP_THREAD_NUMBER)
for the threads when selected from the
TP_THREAD_GROUP_STATE table. That
is, these two queries are equivalent:
SELECT TP_GROUP_ID, MAX_THREAD_IDS_IN_GROUP FROM TP_THREAD_GROUP_STATE; SELECT TP_GROUP_ID, MAX(TP_THREAD_NUMBER) FROM TP_THREAD_STATE GROUP BY TP_GROUP_ID;
STALLED_THREAD_COUNT
The number of stalled statements in the thread group. A stalled statement could be executing, but from a thread pool perspective it is stalled and making no progress. A long-running statement quickly ends up in this category.
WAITING_THREAD_NUMBER
If there is a thread handling the polling of statements in the thread group, this specifies the thread number within this thread group. It is possible that this thread could be executing a statement.
OLDEST_QUEUED
How long in milliseconds the oldest queued statement has been waiting for execution.
This table reports statistics per thread group. There is one row per group. The table has these columns:
TP_GROUP_ID
The thread group ID. This is a unique key within the table.
CONNECTIONS_STARTED
The number of connections started.
CONNECTIONS_CLOSED
The number of connections closed.
QUERIES_EXECUTED
The number of statements executed. This number is incremented when a statement starts executing, not when it finishes.
QUERIES_QUEUED
The number of statements received that were queued for execution. This does not count statements that the thread group was able to begin executing immediately without queuing, which can happen under the conditions described in Section 5.5.4.3, “Thread Pool Operation”.
THREADS_STARTED
The number of threads started.
PRIO_KICKUPS
The number of statements that have been moved from
low-priority queue to high-priority queue based on the value
of the
thread_pool_prio_kickup_timer
system variable. If this number increases quickly, consider
increasing the value of that variable. A quickly increasing
counter means that the priority system is not keeping
transactions from starting too early. For
InnoDB, this most likely means
deteriorating performance due to too many concurrent
transactions..
STALLED_QUERIES_EXECUTED
The number of statements that have become defined as stalled
due to executing for a time longer than the value of the
thread_pool_stall_limit
system variable.
BECOME_CONSUMER_THREAD
The number of times thread have been assigned the consumer thread role.
BECOME_RESERVE_THREAD
The number of times threads have been assigned the reserve thread role.
BECOME_WAITING_THREAD
The number of times threads have been assigned the waiter thread role. When statements are queued, this happens very often, even in normal operation, so rapid increases in this value are normal in the case of a highly loaded system where statements are queued up.
WAKE_THREAD_STALL_CHECKER
The number of times the stall check thread decided to wake or create a thread to possibly handle some statements or take care of the waiter thread role.
SLEEP_WAITS
The number of THD_WAIT_SLEEP waits. These
occur when threads go to sleep; for example, by calling the
SLEEP() function.
DISK_IO_WAITS
The number of THD_WAIT_DISKIO waits. These
occur when threads perform disk I/O that is likely to not hit
the file system cache. Such waits occur when the buffer pool
reads and writes data to disk, not for normal reads from and
writes to files.
ROW_LOCK_WAITS
The number of THD_WAIT_ROW_LOCK waits for
release of a row lock by another transaction.
GLOBAL_LOCK_WAITS
The number of THD_WAIT_GLOBAL_LOCK waits
for a global lock to be released.
META_DATA_LOCK_WAITS
The number of THD_WAIT_META_DATA_LOCK waits
for a metadata lock to be released.
TABLE_LOCK_WAITS
The number of THD_WAIT_TABLE_LOCK waits for
a table to be unlocked that the statement needs to access.
USER_LOCK_WAITS
The number of THD_WAIT_USER_LOCK waits for
a special lock constructed by the user thread.
BINLOG_WAITS
The number of THD_WAIT_BINLOG_WAITS waits
for the binary log to become free.
GROUP_COMMIT_WAITS
The number of THD_WAIT_GROUP_COMMIT waits.
These occur when a group commit must wait for the other
parties to complete their part of a transaction.
FSYNC_WAITS
The number of THD_WAIT_SYNC waits for a
file sync operation.
Some extensions to SHOW statements
accompany the implementation of
INFORMATION_SCHEMA:
INFORMATION_SCHEMA is an information database,
so its name is included in the output from
SHOW DATABASES. Similarly,
SHOW TABLES can be used with
INFORMATION_SCHEMA to obtain a list of its
tables:
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
27 rows in set (0.00 sec)
SHOW COLUMNS and
DESCRIBE can display information
about the columns in individual
INFORMATION_SCHEMA tables.
SHOW statements that accept a
LIKE clause to limit the rows
displayed also permit a WHERE clause that
specifies more general conditions that selected rows must satisfy:
SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW INDEX SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW TRIGGERS SHOW VARIABLES
The WHERE clause, if present, is evaluated
against the column names displayed by the
SHOW statement. For example, the
SHOW CHARACTER SET statement
produces these output columns:
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...
To use a WHERE clause with
SHOW CHARACTER SET, you would refer
to those column names. As an example, the following statement
displays information about character sets for which the default
collation contains the string 'japanese':
mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
This statement displays the multibyte character sets:
mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+