diff options
Diffstat (limited to 'mysql-test/r/information_schema.result')
| -rw-r--r-- | mysql-test/r/information_schema.result | 77 |
1 files changed, 55 insertions, 22 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index ddb9a33b0c8..da5bbba18fe 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1656,6 +1656,57 @@ TEST_RESULT OK SET TIMESTAMP=DEFAULT; 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; @@ -1694,28 +1745,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 |