| Chapter 20. INFORMATION_SCHEMA Tables 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. Usage Notes for the INFORMATION_SCHEMA Database 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. Example 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(N) CHARACTER SET utf8 where N 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.7.9, "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. Character Set Considerations The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N 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.7.9, "Collation and INFORMATION_SCHEMA Searches". INFORMATION_SCHEMA as Alternative to SHOW Statements 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 20.31, "Extensions to SHOW Statements". Privileges 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. Performance Considerations 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". Standards Considerations 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. Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA. 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: http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5. Conventions in the INFORMATION_SCHEMA Reference Sections 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 db_name clause, you can often select information for the default database by adding an AND 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 20.28, "INFORMATION_SCHEMA Tables for InnoDB". For information about INFORMATION_SCHEMA tables specific to the NDB storage engine (MySQL Cluster), see Section 20.29, "INFORMATION_SCHEMA Tables for MySQL Cluster". For information about INFORMATION_SCHEMA tables specific to the thread pool plugin, see Section 20.30, "Thread Pool INFORMATION_SCHEMA Tables". For answers to questions that are often asked concerning the INFORMATION_SCHEMA database, see Section B.7, "MySQL 5.5 FAQ: INFORMATION_SCHEMA". 20.1. The INFORMATION_SCHEMA CHARACTER_SETS Table The CHARACTER_SETS table provides information about available character sets. The following statements are equivalent: SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS [WHERE CHARACTER_SET_NAME LIKE 'wild']SHOW CHARACTER SET [LIKE 'wild'] 20.2. The INFORMATION_SCHEMA COLLATIONS Table The COLLATIONS table provides information about collations for each character set. 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'] 20.3. The INFORMATION_SCHEMACOLLATION_CHARACTER_SET_APPLICABILITY Table 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. 20.4. The INFORMATION_SCHEMA COLUMNS Table The COLUMNS table provides information about columns in tables. 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 multi-byte 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 FROM tbl_name [FROM db_name] [LIKE 'wild'] 20.5. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table The COLUMN_PRIVILEGES table provides information about column privileges. This information comes from the mysql.columns_priv grant table. 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_PRIVILEGESSHOW GRANTS ... 20.7. The INFORMATION_SCHEMA EVENTS Table The EVENTS table provides information about scheduled events, which are discussed in Section 19.4, "Using the Event Scheduler". The SHOW Name values correspond to column names of the SHOW EVENTS statement. 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 'user_name'@'host_name' format. TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM. EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 5.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 at the time the event was created or altered. 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 16.4.1.10, "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 DATETIME value. LAST_ALTERED: The date and time when the event was last modified. This is a DATETIME 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 is the session value of the character_set_client system variable when the event was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the event was created. DATABASE_COLLATION is 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 ENABLED; (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: paul@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: 1CHARACTER_SET_CLIENT: latin1COLLATION_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 19.4.4, "Event Metadata". See also Section 13.7.5.19, "SHOW EVENTS Syntax". 20.10. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table The KEY_COLUMN_USAGE table describes which key columns have constraints. 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.
20.11. The INFORMATION_SCHEMA PARAMETERS Table 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. 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:
20.12. The INFORMATION_SCHEMA PARTITIONS Table The PARTITIONS table provides information about table partitions. See Chapter 18, Partitioning, for more information about partitioning tables. 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 18.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 18.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. UPDATE_TIME: The time that the partition or subpartition was last modified. CHECK_TIME: The last time that the table to which this partition or subpartition belongs was checked. Note Some storage engines do not update this time; for tables using these storage engines, this value 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. Currently, 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.
20.13. The INFORMATION_SCHEMA PLUGINS Table The PLUGINS table provides information about server plugins. 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 object 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.1.8.1, "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_LICENSEFROM INFORMATION_SCHEMA.PLUGINS;SHOW PLUGINS; For information about plugin data structures that form the basis of the information in the PLUGINS table, see Section 23.2, "The MySQL Plugin API". Plugin information is also available using the SHOW PLUGINS statement. See Section 13.7.5.26, "SHOW PLUGINS Syntax". 20.14. The INFORMATION_SCHEMA PROCESSLIST Table The PROCESSLIST table provides information about which threads are running. 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, then MySQL will populate 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.PROCESSLISTSHOW FULL PROCESSLIST 20.15. The INFORMATION_SCHEMA PROFILING Table 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. 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.
20.16. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table The REFERENTIAL_CONSTRAINTS table provides information about foreign keys. 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.
20.17. The INFORMATION_SCHEMA ROUTINES Table 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. 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.
CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the routine was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the routine was created. DATABASE_COLLATION is 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.
20.18. The INFORMATION_SCHEMA SCHEMATA Table A schema is a database, so the SCHEMATA table provides information about databases. The following statements are equivalent: SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA [WHERE SCHEMA_NAME LIKE 'wild']SHOW DATABASES [LIKE 'wild'] 20.19. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table The SCHEMA_PRIVILEGES table provides information about schema (database) privileges. This information comes from the mysql.db grant table. Notes: 20.20. The INFORMATION_SCHEMA STATISTICS Table The STATISTICS table provides information about table indexes. 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 FROM tbl_name FROM db_name 20.21. The INFORMATION_SCHEMA TABLES Table The TABLES table provides information about tables in databases. Notes: 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. Currently, 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.) 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 FROM db_name [LIKE 'wild'] 20.22. The INFORMATION_SCHEMA TABLESPACES Table The TABLESPACES table provides information about active tablespaces. The table was added in MySQL 5.5.3. 20.23. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table The TABLE_CONSTRAINTS table describes which tables have constraints. 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.
20.24. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table The TABLE_PRIVILEGES table provides information about table privileges. This information comes from the mysql.tables_priv grant table. Notes: The following statements are not equivalent: SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGESSHOW GRANTS ... 20.25. The INFORMATION_SCHEMA TRIGGERS Table The TRIGGERS table provides information about triggers. You can see results only for databases and tables for which you have the TRIGGER privilege. Notes: The TRIGGER_SCHEMA and TRIGGER_NAME columns contain the name of the database in which the trigger occurs and the trigger name, respectively. The EVENT_MANIPULATION column contains one of the values 'INSERT', 'DELETE', or 'UPDATE'. As noted in Section 19.3, "Using Triggers", every trigger is associated with exactly one table. The EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE columns contain the database in which this table occurs, and the table's name. The ACTION_ORDER column contains the ordinal position of the trigger's action within the list of all similar triggers on the same table. Currently, 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. The ACTION_STATEMENT column contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in the Statement column of the output from SHOW TRIGGERS. Note that this text uses UTF-8 encoding. The ACTION_ORIENTATION column always contains the value 'ROW'. The ACTION_TIMING column contains one of the two values 'BEFORE' or 'AFTER'. The columns ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW contain 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'. The SQL_MODE column shows the server SQL mode that was in effect at the time when the trigger was created (and thus which remains in effect for this trigger whenever it is invoked, regardless of the current server SQL mode). The possible range of values for this column is the same as that of the sql_mode system variable. See Section 5.1.7, "Server SQL Modes". The DEFINER column indicates who defined the trigger. CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the trigger was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the trigger was created. DATABASE_COLLATION is 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 19.3, "Using Triggers": mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\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: BEFOREACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: DEFINER: me@localhost See also Section 13.7.5.39, "SHOW TRIGGERS Syntax". 20.26. The INFORMATION_SCHEMA USER_PRIVILEGES Table The USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user grant table. Notes: 20.27. The INFORMATION_SCHEMA VIEWS Table The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table. 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 the 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 13.1.20, "CREATE VIEW Syntax".) The DEFINER column indicates who defined the view. SECURITY_TYPE has a value of DEFINER or INVOKER. CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the view was created. COLLATION_CONNECTION is 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. 20.28. INFORMATION_SCHEMA Tables forInnoDB The InnoDB tables related to the InnoDB storage engine help you to monitor ongoing InnoDB activity, to detect inefficiencies before they turn into issues, or to troubleshoot performance and capacity issues that do occur. 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 monitoring information deals with: InnoDB table compression, a feature whose use depends on a balance between I/O reduction, CPU usage, buffer pool management, and how much compression is possible for your data. Transactions and locks, features that balance high performance for a single operation, against the ability to run multiple operations concurrently. (Transactions are the high-level, user-visible aspect of concurrency. Locks are the low-level mechanism that transactions use to avoid reading or writing unreliable data.)
20.28.1. The INFORMATION_SCHEMA INNODB_CMP andINNODB_CMP_RESET Tables The INNODB_CMP and INNODB_CMP_RESET tables contain status information on operations related to compressed InnoDB tables. Table 20.1. Columns of INNODB_CMP andINNODB_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 sizePAGE_SIZE. |
Notes: 20.28.2. The INFORMATION_SCHEMA INNODB_CMPMEM andINNODB_CMPMEM_RESET Tables The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables contain status information on compressed pages within the InnoDB buffer pool. Table 20.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. | 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 tableINNODB_CMPMEM_RESET resets this count. |
Notes: 20.28.3. The INFORMATION_SCHEMA INNODB_TRX Table 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. Table 20.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 Optimizations for 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.4.6.3.3, "Possible Inconsistency with PROCESSLIST". | 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 currently have any locks. (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. | TRX_IS_READ_ONLY | A value of 1 indicates the transaction is read-only. (5.6.4 and up.) | TRX_AUTOCOMMIT_NON_LOCKING | A value of 1 indicates the transaction is a SELECT statement that does not use the FOR UPDATE or LOCK IN SHARED MODE clauses, and is executing with the autocommit setting turned on so that the transaction will only contain this one statement. (5.6.4 and up.) When this column and TRX_IS_READ_ONLY are both 1, InnoDB optimizes the transaction to reduce the overhead associated with transactions that change table data. |
Notes: 20.28.4. The INFORMATION_SCHEMA INNODB_LOCKS Table 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 20.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, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC for shared, exclusive, intention shared, intention exclusive row locks, shared and exclusive gap locks, intention shared and intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections Section 14.3.9.1, "InnoDB Lock Modes" and Section 14.3.9, "The InnoDB Transaction Model and Locking" for information on InnoDB locking. | 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 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 commands). If there is no primary key then the InnoDB internal unique row ID number is used. 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 toNULL. |
Notes: 20.28.5. The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table 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 20.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 onLOCK_ID. |
Notes: 20.28.6. The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table The INNODB_BUFFER_PAGE table holds information about each page in the InnoDB buffer pool. Table 20.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 string. 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), 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 | Time of first access. | TABLE_NAME | Name of the table the page belongs to. | INDEX_NAME | Name of the index the page belongs to. It can be the name of a clustered index or a secondary index. | NUMBER_RECORDS | Number of records within the page. | DATA_SIZE | Sum of the sizes of the records. | 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), 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 | bpage->old. | FREE_PAGE_CLOCK | bpage->freed_page_clock. |
Notes: This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL. Since the INFORMATION_SCHEMA is a general-purpose way to monitor the MySQL server, use this table rather than the corresponding InnoDB system table for any new monitoring application you develop. You must have the PROCESS privilege to query this table.
20.28.7. The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table 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 definition for this page is the same as for INNODB_BUFFER_PAGE, except this table has an LRU_POSITION column instead of BLOCK_ID. Notes: This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL. Since the INFORMATION_SCHEMA is a general-purpose way to monitor the MySQL server, use this table rather than the corresponding InnoDB system table for any new monitoring application you develop. 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.
20.28.8. The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table The INNODB_BUFFER_POOL_STATS table represents the same kinds of InnoDB buffer pool information as the output from SHOW ENGINE INNODB STATUS. 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. Column meanings in this table are intended to be self-explanatory for developers who formerly parsed the SHOW ENGINE INNODB STATUS to get these values. Table 20.7. INNODB_BUFFER_POOL_STATS Columns Column name | Description |
---|
POOL_ID | | POOL_SIZE | | FREE_BUFFERS | | DATABASE_PAGES | | OLD_DATABASE_PAGES | | MODIFIED_DATABASE_PAGES | | PENDING_DECOMPRESS | | PENDING_READS | | PENDING_FLUSH_LRU | | PENDING_FLUSH_LIST | | PAGES_MADE_YOUNG | | PAGES_NOT_MADE_YOUNG | | PAGES_MADE_YOUNG_RATE | | PAGES_MADE_NOT_YOUNG_RATE | | NUMBER_PAGES_READ | | NUMBER_PAGES_CREATED | | NUMBER_PAGES_WRITTEN | | PAGES_READ_RATE | | PAGES_CREATE_RATE | | PAGES_WRITTEN_RATE | | NUMBER_PAGES_GET | | HIT_RATE | | YOUNG_MAKE_PER_THOUSAND_GETS | | NOT_YOUNG_MAKE_PER_THOUSAND_GETS | | NUMBER_PAGES_READ_AHEAD | | NUMBER_READ_AHEAD_EVICTED | | READ_AHEAD_RATE | | REAT_AHEAD_EVICTED_RATE | | LRU_IO_TOTAL | | LRU_IO_CURRENT | | UNCOMPRESS_TOTAL | | UNCOMPRESS_CURRENT | |
Notes: This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL. Since the INFORMATION_SCHEMA is a general-purpose way to monitor the MySQL server, use this table rather than the corresponding InnoDB system table for any new monitoring application you develop. You must have the PROCESS privilege to query this table.
20.29. INFORMATION_SCHEMA Tables for MySQL Cluster 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 17.5.10, "The ndbinfo MySQL Cluster Information Database". 20.29.1. The INFORMATION_SCHEMA FILES Table The FILES table provides information about the files in which MySQL NDB Disk Data tables are stored. Note 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 of data on disk for that table-using ndb_desc. For more information, see Section 17.4.9, "ndb_desc - Describe NDB Tables". 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. Currently, the value of the TABLESPACE_CATALOG column is always NULL. TABLE_NAME is the name of the Disk Data table with which the file is associated, if any. The LOGFILE_GROUP_NAME column gives the name of the log file group to which the log file or data file belongs. For an UNDO log file, the LOGFILE_GROUP_NUMBER contains the auto-generated ID number of the log file group to which the log file belongs. For a MySQL Cluster Disk Data log file or data file, the value of the ENGINE column is always NDB or NDBCLUSTER. For a MySQL Cluster Disk Data log file or data file, the value of the FULLTEXT_KEYS column is always empty. The FREE EXTENTS column displays the number of extents which have not yet been used by the file. The TOTAL EXTENTS column show the total number of extents allocated to the file. The difference between these two columns is the number of extents currently in use by the file: SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME = 'myfile.dat'; You can approximate the amount of disk space in use by the file by multiplying this difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes: SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME = 'myfile.dat'; Similarly, you can estimate the amount of space that remains available in a given file by multiplying FREE_EXTENTS by EXTENT_SIZE: SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME = 'myfile.dat'; Important 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. Currently, the values of these columns are as reported by the operating system, and are not supplied by the NDB storage engine. Where no value is provided by the operating system, these columns display 0000-00-00 00:00:00. For MySQL Cluster Disk Data files, the value of the RECOVER_TIME and TRANSACTION_COUNTER columns is always 0. For MySQL Cluster Disk Data files, the following columns are always NULL: 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. Currently, the value of the ENGINE column is always NDBCLUSTER. The FREE_EXTENTS column in this row shows the total number of free extents available to all undo files belonging to a given log file group whose name and number are shown in the LOGFILE_GROUP_NAME and LOGFILE_GROUP_NUMBER columns, respectively. Suppose there are no existing log file groups on your MySQL Cluster, and you create one using the following statement: mysql> CREATE LOGFILE GROUP lg1 -> ADD UNDOFILE 'undofile.dat' -> INITIAL_SIZE = 16M -> UNDO_BUFFER_SIZE = 1M -> ENGINE = NDB;Query OK, 0 rows affected (3.81 sec) You can now see this NULL row when you query the FILES table: mysql> SELECT DISTINCT -> FILE_NAME AS File, -> FREE_EXTENTS AS Free, -> TOTAL_EXTENTS AS Total, -> EXTENT_SIZE AS Size, -> INITIAL_SIZE AS Initial -> FROM INFORMATION_SCHEMA.FILES;+--------------+---------+---------+------+----------+| File | Free | Total | Size | Initial |+--------------+---------+---------+------+----------+| undofile.dat | NULL | 4194304 | 4 | 16777216 || NULL | 4184068 | NULL | 4 | NULL |+--------------+---------+---------+------+----------+2 rows in set (0.01 sec) The total number of free extents available for undo logging is always somewhat less than the sum of the TOTAL_EXTENTS column values for all undo files in the log file group due to overhead required for maintaining the undo files. This can be seen by adding a second undo file to the log file group, then repeating the previous query against the FILES table: mysql> ALTER LOGFILE GROUP lg1 -> ADD UNDOFILE 'undofile02.dat' -> INITIAL_SIZE = 4M -> ENGINE = NDB;Query OK, 0 rows affected (1.02 sec)mysql> SELECT DISTINCT -> FILE_NAME AS File, -> FREE_EXTENTS AS Free, -> TOTAL_EXTENTS AS Total, -> EXTENT_SIZE AS Size, -> INITIAL_SIZE AS Initial -> FROM INFORMATION_SCHEMA.FILES;+----------------+---------+---------+------+----------+| File | Free | Total | Size | Initial |+----------------+---------+---------+------+----------+| undofile.dat | NULL | 4194304 | 4 | 16777216 || undofile02.dat | NULL | 1048576 | 4 | 4194304 || NULL | 5223944 | NULL | 4 | NULL |+----------------+---------+---------+------+----------+3 rows in set (0.01 sec) The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size: mysql> SELECT -> FREE_EXTENTS AS 'Free Extents', -> FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes' -> FROM INFORMATION_SCHEMA.FILES -> WHERE LOGFILE_GROUP_NAME = 'lg1' -> AND FILE_NAME IS NULL;+--------------+------------+| Free Extents | Free Bytes |+--------------+------------+| 5223944 | 20895776 |+--------------+------------+1 row in set (0.02 sec) If you create a MySQL Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example: mysql> CREATE TABLESPACE ts1 -> ADD DATAFILE 'data1.dat' -> USE LOGFILE GROUP lg1 -> INITIAL_SIZE 512M -> ENGINE = NDB;Query OK, 0 rows affected (8.71 sec)mysql> CREATE TABLE dd ( -> c1 INT NOT NULL PRIMARY KEY, -> c2 INT, -> c3 DATE -> ) -> TABLESPACE ts1 STORAGE DISK -> ENGINE = NDB;Query OK, 0 rows affected (2.11 sec)mysql> INSERT INTO dd VALUES -> (NULL, 1234567890, '2007-02-02'), -> (NULL, 1126789005, '2007-02-03'), -> (NULL, 1357924680, '2007-02-04'), -> (NULL, 1642097531, '2007-02-05');Query OK, 4 rows affected (0.01 sec)mysql> SELECT -> FREE_EXTENTS AS 'Free Extents', -> FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes' -> FROM INFORMATION_SCHEMA.FILES -> WHERE LOGFILE_GROUP_NAME = 'lg1' -> AND FILE_NAME IS NULL;+--------------+------------+| Free Extents | Free Bytes |+--------------+------------+| 5207565 | 20830260 |+--------------+------------+1 row in set (0.01 sec) An additional row is present in the FILES table for any MySQL Cluster tablespace, whether or not any data files are associated with the tablespace. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always TABLESPACE, and that of the STATUS column is always NORMAL. Currently, the value of the ENGINE column is always NDBCLUSTER. There are no SHOW statements associated with the FILES table. For additional information, and examples of creating and dropping MySQL Cluster Disk Data objects, see Section 17.5.12, "MySQL Cluster Disk Data Tables".
20.29.2. The INFORMATION_SCHEMAndb_transid_mysql_connection_map Table 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. 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. 20.30. Thread Pool INFORMATION_SCHEMA Tables The following sections describe the INFORMATION_SCHEMA tables associated with the thread pool plugin. They provide information about thread pool operation: 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 8.11.6.1, "Thread Pool Components and 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. 20.30.1. The INFORMATION_SCHEMA TP_THREAD_STATE Table 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_WAIT columns of the TP_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 20.8. 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 |
20.30.2. The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table 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_GROUPFROM 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.
20.30.3. The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table 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 8.11.6.2, "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.
20.31. Extensions to SHOW Statements 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 SETSHOW COLLATIONSHOW COLUMNSSHOW DATABASESSHOW FUNCTION STATUSSHOW INDEXSHOW OPEN TABLESSHOW PROCEDURE STATUSSHOW STATUSSHOW TABLE STATUSSHOW TABLESSHOW TRIGGERSSHOW 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 multi-byte 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 |+---------+---------------------------+---------------------+--------+ Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |