diff options
Diffstat (limited to 'mysql-test/r/information_schema.result')
| -rw-r--r-- | mysql-test/r/information_schema.result | 97 |
1 files changed, 65 insertions, 32 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 3041c195b59..86fa9d76864 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -169,11 +169,11 @@ table_name t1 t4 select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT -def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE show keys from t3 where Key_name = "a_data"; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t3 1 a_data 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t3 1 a_data 1 a A NULL NULL NULL YES BTREE show tables like 't%'; Tables_in_test (t%) t2 @@ -400,7 +400,7 @@ latin1_general_ci latin1_general_cs latin1_spanish_ci show keys from v4; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment select * from information_schema.views where TABLE_NAME like "v%"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION def test v0 select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci @@ -620,7 +620,7 @@ user drop view v1; create view vo as select 'a' union select 'a'; show index from vo; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE @@ -1130,8 +1130,8 @@ describe t1; Field Type Null Key Default Extra f1 int(11) YES MUL NULL show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 f1 1 f1 A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 f1 1 f1 A NULL NULL NULL YES BTREE drop table t1; create table t1(f1 binary(32), f2 varbinary(64)); select character_maximum_length, character_octet_length @@ -1533,9 +1533,9 @@ CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG U select * from information_schema.schemata where schema_name = NULL; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT select * from information_schema.tables where table_schema = NULL; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT select * from information_schema.tables where table_catalog = NULL; @@ -1682,6 +1682,57 @@ DROP USER nonpriv; DROP TABLE db1.t1; DROP DATABASE db1; End of 5.1 tests. +# +# Additional test for WL#3726 "DDL locking for all metadata objects" +# To avoid possible deadlocks process of filling of I_S tables should +# use high-priority metadata lock requests when opening tables. +# Below we just test that we really use high-priority lock request +# since reproducing a deadlock will require much more complex test. +# +drop tables if exists t1, t2, t3; +create table t1 (i int); +create table t2 (j int primary key auto_increment); +# Switching to connection 'con3726_1' +lock table t2 read; +# Switching to connection 'con3726_2' +# RENAME below will be blocked by 'lock table t2 read' above but +# will add two pending requests for exclusive metadata locks. +rename table t2 to t3; +# Switching to connection 'default' +# These statements should not be blocked by pending lock requests +select table_name, column_name, data_type from information_schema.columns +where table_schema = 'test' and table_name in ('t1', 't2'); +table_name column_name data_type +t1 i int +t2 j int +select table_name, auto_increment from information_schema.tables +where table_schema = 'test' and table_name in ('t1', 't2'); +table_name auto_increment +t1 NULL +t2 1 +# Switching to connection 'con3726_1' +unlock tables; +# Switching to connection 'con3726_2' +# Switching to connection 'default' +drop tables t1, t3; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE KEY_COLUMN_USAGE ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PARTITIONS ALL NULL TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 1 database +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS +WHERE CONSTRAINT_SCHEMA='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE REFERENTIAL_CONSTRAINTS ALL NULL CONSTRAINT_SCHEMA NULL NULL NULL Using where; Open_full_table; Scanned 1 database +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS +WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE TABLE_CONSTRAINTS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS +WHERE EVENT_OBJECT_SCHEMA='test'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE TRIGGERS ALL NULL EVENT_OBJECT_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database create table information_schema.t1 (f1 INT); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' drop table information_schema.t1; @@ -1720,28 +1771,10 @@ ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_ LOCK TABLES t1 READ, information_schema.tables READ; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE t1; -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE KEY_COLUMN_USAGE ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE PARTITIONS ALL NULL TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 1 database -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS -WHERE CONSTRAINT_SCHEMA='test'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE REFERENTIAL_CONSTRAINTS ALL NULL CONSTRAINT_SCHEMA NULL NULL NULL Using where; Open_full_table; Scanned 1 database -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS -WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE TABLE_CONSTRAINTS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS -WHERE EVENT_OBJECT_SCHEMA='test'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE TRIGGERS ALL NULL EVENT_OBJECT_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database SELECT * -FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE -LEFT JOIN INFORMATION_SCHEMA.COLUMNS -USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) -WHERE COLUMNS.TABLE_SCHEMA = 'test' +FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE +LEFT JOIN INFORMATION_SCHEMA.COLUMNS +USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) +WHERE COLUMNS.TABLE_SCHEMA = 'test' AND COLUMNS.TABLE_NAME = 't1'; TABLE_SCHEMA TABLE_NAME COLUMN_NAME CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME TABLE_CATALOG ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT |