diff options
Diffstat (limited to 'mysql-test')
31 files changed, 723 insertions, 135 deletions
diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc index 6e7f53ba9b2..8ff38c7e7a1 100644 --- a/mysql-test/include/handler.inc +++ b/mysql-test/include/handler.inc @@ -518,12 +518,15 @@ connect (flush,localhost,root,,); connection flush; --echo connection: flush --send flush tables; -connection default; ---echo connection: default +connect (waiter,localhost,root,,); +connection waiter; +--echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Flushing tables"; --source include/wait_condition.inc +connection default; +--echo connection: default handler t2 open; handler t2 read first; handler t1 read next; @@ -550,12 +553,14 @@ connect (flush,localhost,root,,); connection flush; --echo connection: flush --send rename table t1 to t2; -connection default; ---echo connection: default +connection waiter; +--echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table" and info = "rename table t1 to t2"; --source include/wait_condition.inc +connection default; +--echo connection: default handler t2 open; handler t2 read first; --error ER_NO_SUCH_TABLE @@ -566,7 +571,13 @@ connection flush; reap; connection default; drop table t2; +connection flush; disconnect flush; +--source include/wait_until_disconnected.inc +connection waiter; +disconnect waiter; +--source include/wait_until_disconnected.inc +connection default; # # Bug#30882 Dropping a temporary table inside a stored function may cause a server crash @@ -699,19 +710,24 @@ handler t1 read a next; # Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table # +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); + +connection default; --disable_warnings drop table if exists t1; --enable_warnings create table t1 (a int); insert into t1 values (1); handler t1 open; -connect(con1,localhost,root,,); +connection con1; send alter table t1 engine=memory; -connection default; +connection con2; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "rename result table" and info = "alter table t1 engine=memory"; + where state = "Waiting for table" and info = "alter table t1 engine=memory"; --source include/wait_condition.inc +connection default; --error ER_ILLEGAL_HA handler t1 read a next; handler t1 close; @@ -720,6 +736,9 @@ connection con1; drop table t1; disconnect con1; --source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc connection default; # diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 471cc6e9a3d..cf424b8b058 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -785,7 +785,7 @@ drop table t1; create table t1 select * from t2; ERROR 42S02: Table 'test.t2' doesn't exist create table t1 select * from t1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR 42S02: Table 'test.t1' doesn't exist create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce' create table t1 (primary key(a)) select "b" as b; @@ -805,6 +805,11 @@ Note 1050 Table 't1' already exists select * from t1; i 1 +create table if not exists t1 select * from t1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +select * from t1; +i +1 create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce' select * from t1; diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index b978304f59d..2be426d3a4a 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -33,6 +33,9 @@ flush tables with read lock; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction lock table t1 read; flush tables with read lock; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +flush tables with read lock; lock table t1 write; ERROR HY000: Can't execute the query because you have a conflicting read lock lock table t1 read; @@ -46,6 +49,7 @@ flush tables with read lock; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction lock table t1 read, t2 read, t3 read; flush tables with read lock; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction unlock tables; drop table t1, t2, t3; create table t1 (c1 int); @@ -69,6 +73,7 @@ ERROR HY000: Can't execute the given command because you have active locked tabl unlock tables; lock tables t1 read; flush tables with read lock; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction unlock tables; drop table t1, t2; set session low_priority_updates=default; diff --git a/mysql-test/r/flush_table.result b/mysql-test/r/flush_table.result index 8821bade6b4..2b0ee1cb205 100644 --- a/mysql-test/r/flush_table.result +++ b/mysql-test/r/flush_table.result @@ -3,21 +3,14 @@ create table t1 (a int not null auto_increment primary key); insert into t1 values(0); lock table t1 read; flush table t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +unlock tables; +lock table t1 write; +flush table t1; check table t1; Table Op Msg_type Msg_text test.t1 check status OK unlock tables; -lock table t1 read; -lock table t1 read; -flush table t1; -select * from t1; -a -1 -unlock tables; -select * from t1; -a -1 -unlock tables; lock table t1 write; lock table t1 read; flush table t1; @@ -26,7 +19,7 @@ a 1 unlock tables; unlock tables; -lock table t1 read; +lock table t1 write; lock table t1 write; flush table t1; select * from t1; diff --git a/mysql-test/r/handler_innodb.result b/mysql-test/r/handler_innodb.result index 957fc30acef..5990b19062b 100644 --- a/mysql-test/r/handler_innodb.result +++ b/mysql-test/r/handler_innodb.result @@ -548,6 +548,7 @@ c1 1 connection: flush flush tables;; +connection: waiter connection: default handler t2 open; handler t2 read first; @@ -567,6 +568,7 @@ handler t1 read first; c1 connection: flush rename table t1 to t2;; +connection: waiter connection: default handler t2 open; handler t2 read first; diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result index 90a1bdfe6be..f7b0ff2e04e 100644 --- a/mysql-test/r/handler_myisam.result +++ b/mysql-test/r/handler_myisam.result @@ -547,6 +547,7 @@ c1 1 connection: flush flush tables;; +connection: waiter connection: default handler t2 open; handler t2 read first; @@ -566,6 +567,7 @@ handler t1 read first; c1 connection: flush rename table t1 to t2;; +connection: waiter connection: default handler t2 open; handler t2 read first; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 04234eb3cc4..a66e494dcd2 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1644,6 +1644,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_full_table; 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; @@ -1682,28 +1733,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_full_table; 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 diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result index 8b6830d4798..1f4f4bb32eb 100644 --- a/mysql-test/r/kill.result +++ b/mysql-test/r/kill.result @@ -138,4 +138,107 @@ KILL CONNECTION_ID(); # of close of the connection socket SELECT 1; Got one of the listed errors +# +# Additional test for WL#3726 "DDL locking for all metadata objects" +# Check that DDL and DML statements waiting for metadata locks can +# be killed. Note that we don't cover all situations here since it +# can be tricky to write test case for some of them (e.g. REPAIR or +# ALTER and other statements under LOCK TABLES). +# +drop tables if exists t1, t2, t3; +create table t1 (i int primary key); +# Test for RENAME TABLE +# Switching to connection 'blocker' +lock table t1 read; +# Switching to connection 'ddl' +rename table t1 to t2; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# Test for DROP TABLE +drop table t1; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# Test for CREATE TRIGGER +create trigger t1_bi before insert on t1 for each row set @a:=1; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# +# Tests for various kinds of ALTER TABLE +# +# Full-blown ALTER which should copy table +alter table t1 add column j int; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# Two kinds of simple ALTER +alter table t1 rename to t2; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +alter table t1 disable keys; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# Fast ALTER +alter table t1 alter column i set default 100; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# Special case which is triggered only for MERGE tables. +# Switching to connection 'blocker' +unlock tables; +create table t2 (i int primary key) engine=merge union=(t1); +lock tables t2 read; +# Switching to connection 'ddl' +alter table t2 alter column i set default 100; +# Switching to connection 'default' +kill query ID; +# Switching to connection 'ddl' +ERROR 70100: Query execution was interrupted +# Test for DML waiting for meta-data lock +# Switching to connection 'blocker' +unlock tables; +drop table t2; +create table t2 (k int); +lock tables t1 read; +# Switching to connection 'ddl' +rename tables t1 to t3, t2 to t1; +# Switching to connection 'dml' +insert into t2 values (1); +# Switching to connection 'default' +kill query ID2; +# Switching to connection 'dml' +ERROR 70100: Query execution was interrupted +# Switching to connection 'blocker' +unlock tables; +# Switching to connection 'ddl' +# Test for DML waiting for tables to be flushed +# Switching to connection 'blocker' +lock tables t1 read; +# Switching to connection 'ddl' +# Let us mark locked table t1 as old +flush tables; +# Switching to connection 'dml' +select * from t1; +# Switching to connection 'default' +kill query ID2; +# Switching to connection 'dml' +ERROR 70100: Query execution was interrupted +# Switching to connection 'blocker' +unlock tables; +# Switching to connection 'ddl' +# Cleanup. +# Switching to connection 'default' +drop table t3; +drop table t1; set @@global.concurrent_insert= @old_concurrent_insert; diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result index 1f8f6aa04ae..a60c5c8383f 100644 --- a/mysql-test/r/lock.result +++ b/mysql-test/r/lock.result @@ -128,13 +128,14 @@ select * from v_bug5719; 1 1 drop view v_bug5719; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction sic: did not left LOCK TABLES mode automatically select * from t1; ERROR HY000: Table 't1' was not locked with LOCK TABLES unlock tables; -create view v_bug5719 as select * from t1; +create or replace view v_bug5719 as select * from t1; lock tables v_bug5719 write; select * from v_bug5719; a diff --git a/mysql-test/r/partition_column_prune.result b/mysql-test/r/partition_column_prune.result index 82c49453d43..844429d24a6 100644 --- a/mysql-test/r/partition_column_prune.result +++ b/mysql-test/r/partition_column_prune.result @@ -56,11 +56,11 @@ insert into t1 values (2,5), (2,15), (2,25), insert into t1 select * from t1; explain partitions select * from t1 where a=2; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p01,p02,p03,p11 ALL NULL NULL NULL NULL 13 Using where +1 SIMPLE t1 p01,p02,p03,p11 ALL NULL NULL NULL NULL 8 Using where explain partitions select * from t1 where a=4; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p11,p12,p13,p21 ALL NULL NULL NULL NULL 16 Using where +1 SIMPLE t1 p11,p12,p13,p21 ALL NULL NULL NULL NULL 14 Using where explain partitions select * from t1 where a=2 and b < 22; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p01,p02,p03 ALL NULL NULL NULL NULL 16 Using where +1 SIMPLE t1 p01,p02,p03 ALL NULL NULL NULL NULL 14 Using where drop table t1; diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index d7790cd8075..4c22c25ddb9 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -1960,7 +1960,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE X p1,p2 ALL a NULL NULL NULL 4 Using where +1 SIMPLE X p1,p2 ALL a NULL NULL NULL 2 Using where 1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2 drop table t1; create table t1 (a int) partition by hash(a) partitions 20; diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result index c7e8812320c..af0ffaf4ae5 100644 --- a/mysql-test/r/ps_ddl.result +++ b/mysql-test/r/ps_ddl.result @@ -850,7 +850,7 @@ flush table t1; execute stmt; f1() 6 -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); SUCCESS execute stmt; @@ -1706,7 +1706,7 @@ SUCCESS drop temporary table t2; execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); SUCCESS drop table t2; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index a73abf787d8..47d441ee182 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1083,11 +1083,9 @@ select f0()| f0() 100 select * from v0| -f0() -100 +ERROR HY000: Table 'v0' was not locked with LOCK TABLES select *, f0() from v0, (select 123) as d1| -f0() 123 f0() -100 123 100 +ERROR HY000: Table 'v0' was not locked with LOCK TABLES select id, f3() from t1| ERROR HY000: Table 't1' was not locked with LOCK TABLES select f4()| diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 2df2b0bafa6..5f16d88a0dc 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1102,10 +1102,8 @@ select * from v1; a select * from t2; ERROR HY000: Table 't2' was not locked with LOCK TABLES -drop view v1; -drop table t1, t2; -ERROR HY000: Table 't1' was locked with a READ lock and can't be updated unlock tables; +drop view v1; drop table t1, t2; create table t1 (a int); create view v1 as select * from t1 where a < 2 with check option; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 65fbf2d87b6..0c74d8ed91b 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -779,9 +779,9 @@ GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; GRANT SELECT ON db26813.t1 TO u26813@localhost; ALTER VIEW v1 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1' ALTER VIEW v2 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2' ALTER VIEW v3 AS SELECT f2 FROM t1; ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation SHOW CREATE VIEW v3; diff --git a/mysql-test/r/view_multi.result b/mysql-test/r/view_multi.result new file mode 100644 index 00000000000..95a8d572be4 --- /dev/null +++ b/mysql-test/r/view_multi.result @@ -0,0 +1,48 @@ +reset master; +create table t1 (i int); +create table t2 (i int); +create view v1 as select * from t1; +select get_lock("lock_bg25144", 1); +get_lock("lock_bg25144", 1) +1 +insert into v1 values (get_lock("lock_bg25144", 100));; +drop view v1;; +select release_lock("lock_bg25144"); +release_lock("lock_bg25144") +1 +select release_lock("lock_bg25144"); +release_lock("lock_bg25144") +1 +select * from t1; +i +1 +create view v1 as select * from t1; +select get_lock("lock_bg25144", 1); +get_lock("lock_bg25144", 1) +1 +insert into v1 values (get_lock("lock_bg25144", 100));; +alter view v1 as select * from t2;; +select release_lock("lock_bg25144"); +release_lock("lock_bg25144") +1 +select release_lock("lock_bg25144"); +release_lock("lock_bg25144") +1 +select * from t1; +i +1 +1 +select * from t2; +i +show binlog events in 'master-bin.000001' from 107; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; create table t1 (i int) +master-bin.000001 # Query 1 # use `test`; create table t2 (i int) +master-bin.000001 # Query 1 # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select * from t1 +master-bin.000001 # Query 1 # use `test`; insert into v1 values (get_lock("lock_bg25144", 100)) +master-bin.000001 # Query 1 # use `test`; drop view v1 +master-bin.000001 # Query 1 # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select * from t1 +master-bin.000001 # Query 1 # use `test`; insert into v1 values (get_lock("lock_bg25144", 100)) +master-bin.000001 # Query 1 # use `test`; ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select * from t2 +drop table t1, t2; +drop view v1; diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index bed019f9c79..da962a7fb9d 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -13,3 +13,6 @@ rpl_get_master_version_and_clock: # Bug#46931 2009-10-17 joro rpl.rpl_get_master_version_and_clock fails rpl_cross_version : BUG#43913 2009-10-22 luis rpl_cross_version fails with symptom in described in bug report rpl_spec_variables : BUG#47661 2009-10-27 jasonh rpl_spec_variables fails on PB2 hpux +rpl_empty_master_crash : BUG#48048 +rpl_load_from_master : BUG#48048 +rpl_load_table_from_master : BUG#48048 diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 9f3c3a88151..c07014bfc19 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -683,8 +683,8 @@ drop table t1; # Error during open_and_lock_tables() of tables --error ER_NO_SUCH_TABLE create table t1 select * from t2; -# Rather special error which also caught during open tables pahse ---error ER_UPDATE_TABLE_USED +# A special case which is also caught during open tables pahse +--error ER_NO_SUCH_TABLE create table t1 select * from t1; # Error which happens before select_create::prepare() --error ER_CANT_AGGREGATE_2COLLATIONS @@ -706,6 +706,10 @@ create table t1 (i int); create table t1 select 1 as i; create table if not exists t1 select 1 as i; select * from t1; +# Error which is detected after successfull table open. +--error ER_UPDATE_TABLE_USED +create table if not exists t1 select * from t1; +select * from t1; # Error before select_create::prepare() --error ER_CANT_AGGREGATE_2COLLATIONS create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index ad7617b9403..bb2749e902b 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -15,3 +15,4 @@ partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Inn partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes innodb-autoinc : Bug#48482 2009-11-02 svoj innodb-autoinc.test fails with results difference rpl_killed_ddl : Bug#45520: rpl_killed_ddl fails sporadically in pb2 +merge : WL#4144 diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index f27d4cf2fad..4172230a54d 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -68,10 +68,13 @@ drop table t1; create table t1 (c1 int); lock table t1 write; # Cannot get the global read lock with write locked tables. ---error 1192 +--error ER_LOCK_OR_ACTIVE_TRANSACTION flush tables with read lock; lock table t1 read; -# Can get the global read lock with read locked tables. +# Cannot get the global read lock with read locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; flush tables with read lock; --error 1223 lock table t1 write; @@ -84,12 +87,12 @@ create table t2 (c1 int); create table t3 (c1 int); lock table t1 read, t2 read, t3 write; # Cannot get the global read lock with write locked tables. ---error 1192 +--error ER_LOCK_OR_ACTIVE_TRANSACTION flush tables with read lock; lock table t1 read, t2 read, t3 read; -# Can get the global read lock with read locked tables. +# Cannot get the global read lock with read locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION flush tables with read lock; -# Release all table locks and the global read lock. unlock tables; drop table t1, t2, t3; @@ -157,6 +160,7 @@ flush tables with read lock; unlock tables; lock tables t1 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION flush tables with read lock; unlock tables; diff --git a/mysql-test/t/flush_table.test b/mysql-test/t/flush_table.test index 50e7e91419a..e4fc1b0c39f 100644 --- a/mysql-test/t/flush_table.test +++ b/mysql-test/t/flush_table.test @@ -15,30 +15,21 @@ insert into t1 values(0); # Test for with read lock + flush lock table t1 read; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE flush table t1; -check table t1; unlock tables; -# Test for with 2 read lock in different thread + flush +# Test for with write lock + flush -lock table t1 read; -connect (locker,localhost,root,,test); -connection locker; -lock table t1 read; -connection default; -send flush table t1; -connection locker; ---sleep 2 -select * from t1; -unlock tables; -connection default; -reap; -select * from t1; +lock table t1 write; +flush table t1; +check table t1; unlock tables; # Test for with a write lock and a waiting read lock + flush lock table t1 write; +connect (locker,localhost,root,,test); connection locker; send lock table t1 read; connection default; @@ -51,9 +42,9 @@ reap; unlock tables; connection default; -# Test for with a read lock and a waiting write lock + flush +# Test for with a write lock and a waiting write lock + flush -lock table t1 read; +lock table t1 write; connection locker; send lock table t1 write; connection default; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 9da7cc1042d..c6cb28a4a30 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -544,6 +544,7 @@ AND table_name not like 'ndb%' AND table_name not like 'innodb_%' GROUP BY TABLE_SCHEMA; + # # TRIGGERS table test # @@ -914,8 +915,8 @@ DROP PROCEDURE p1; DROP USER mysql_bug20230@localhost; # -# Bug#2123 query with a simple non-correlated subquery over -# INFORMARTION_SCHEMA.TABLES +# Bug#21231 query with a simple non-correlated subquery over +# INFORMARTION_SCHEMA.TABLES # SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'); @@ -1391,9 +1392,66 @@ SET TIMESTAMP=DEFAULT; --echo End of 5.1 tests. +--echo # +--echo # Additional test for WL#3726 "DDL locking for all metadata objects" +--echo # To avoid possible deadlocks process of filling of I_S tables should +--echo # use high-priority metadata lock requests when opening tables. +--echo # Below we just test that we really use high-priority lock request +--echo # since reproducing a deadlock will require much more complex test. +--echo # +--disable_warnings +drop tables if exists t1, t2, t3; +--enable_warnings +create table t1 (i int); +create table t2 (j int primary key auto_increment); +connect (con3726_1,localhost,root,,test); +--echo # Switching to connection 'con3726_1' +connection con3726_1; +lock table t2 read; +connect (con3726_2,localhost,root,,test); +--echo # Switching to connection 'con3726_2' +connection con3726_2; +--echo # RENAME below will be blocked by 'lock table t2 read' above but +--echo # will add two pending requests for exclusive metadata locks. +--send rename table t2 to t3 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info like "rename table t2 to t3"; +--source include/wait_condition.inc +--echo # 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'); +select table_name, auto_increment from information_schema.tables + where table_schema = 'test' and table_name in ('t1', 't2'); +--echo # Switching to connection 'con3726_1' +connection con3726_1; +unlock tables; +--echo # Switching to connection 'con3726_2' +connection con3726_2; +--reap +--echo # Switching to connection 'default' +connection default; +disconnect con3726_1; +disconnect con3726_2; +drop tables t1, t3; + +# +# Bug#39270 I_S optimization algorithm does not work properly in some cases +# +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS + WHERE CONSTRAINT_SCHEMA='test'; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS + WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS + WHERE EVENT_OBJECT_SCHEMA='test'; + # # Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema -# +# --error ER_DBACCESS_DENIED_ERROR create table information_schema.t1 (f1 INT); --error ER_DBACCESS_DENIED_ERROR @@ -1429,27 +1487,18 @@ DROP TABLE t1, information_schema.tables; LOCK TABLES t1 READ, information_schema.tables READ; DROP TABLE t1; -# -# Bug#39270 I_S optimization algorithm does not work properly in some cases -# -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS - WHERE CONSTRAINT_SCHEMA='test'; -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS - WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; -EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS - WHERE EVENT_OBJECT_SCHEMA='test'; +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc # -# Bug #43834 Assertion in Natural_join_column::db_name() on an I_S query +# Bug #43834 Assertion in Natural_join_column::db_name() on an I_S query # + SELECT * -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'; +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'; + -# Wait till all disconnects are completed ---source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 02b033df2e5..98cb9f64d98 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -329,6 +329,232 @@ KILL CONNECTION_ID(); SELECT 1; --connection default +--echo # +--echo # Additional test for WL#3726 "DDL locking for all metadata objects" +--echo # Check that DDL and DML statements waiting for metadata locks can +--echo # be killed. Note that we don't cover all situations here since it +--echo # can be tricky to write test case for some of them (e.g. REPAIR or +--echo # ALTER and other statements under LOCK TABLES). +--echo # +--disable_warnings +drop tables if exists t1, t2, t3; +--enable_warnings + +create table t1 (i int primary key); +connect (blocker, localhost, root, , ); +connect (dml, localhost, root, , ); +connect (ddl, localhost, root, , ); + +--echo # Test for RENAME TABLE +--echo # Switching to connection 'blocker' +connection blocker; +lock table t1 read; +--echo # Switching to connection 'ddl' +connection ddl; +let $ID= `select connection_id()`; +--send rename table t1 to t2 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Test for DROP TABLE +--send drop table t1 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "drop table t1"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Test for CREATE TRIGGER +--send create trigger t1_bi before insert on t1 for each row set @a:=1 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "create trigger t1_bi before insert on t1 for each row set @a:=1"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # +--echo # Tests for various kinds of ALTER TABLE +--echo # +--echo # Full-blown ALTER which should copy table +--send alter table t1 add column j int +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "alter table t1 add column j int"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Two kinds of simple ALTER +--send alter table t1 rename to t2 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "alter table t1 rename to t2"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap +--send alter table t1 disable keys +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "alter table t1 disable keys"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap +--echo # Fast ALTER +--send alter table t1 alter column i set default 100 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "alter table t1 alter column i set default 100"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap +--echo # Special case which is triggered only for MERGE tables. +--echo # Switching to connection 'blocker' +connection blocker; +unlock tables; +create table t2 (i int primary key) engine=merge union=(t1); +lock tables t2 read; +--echo # Switching to connection 'ddl' +connection ddl; +--send alter table t2 alter column i set default 100 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "alter table t2 alter column i set default 100"; +--replace_result $ID ID +eval kill query $ID; +--echo # Switching to connection 'ddl' +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Test for DML waiting for meta-data lock +--echo # Switching to connection 'blocker' +connection blocker; +unlock tables; +drop table t2; +create table t2 (k int); +lock tables t1 read; +--echo # Switching to connection 'ddl' +connection ddl; +# Let us add pending exclusive metadata lock on t2 +--send rename tables t1 to t3, t2 to t1 +--echo # Switching to connection 'dml' +connection dml; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "rename tables t1 to t3, t2 to t1"; +let $ID2= `select connection_id()`; +--send insert into t2 values (1) +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "insert into t2 values (1)"; +--replace_result $ID2 ID2 +eval kill query $ID2; +--echo # Switching to connection 'dml' +connection dml; +--error ER_QUERY_INTERRUPTED +--reap +--echo # Switching to connection 'blocker' +connection blocker; +unlock tables; +--echo # Switching to connection 'ddl' +connection ddl; +--reap + +--echo # Test for DML waiting for tables to be flushed +--echo # Switching to connection 'blocker' +connection blocker; +lock tables t1 read; +--echo # Switching to connection 'ddl' +connection ddl; +--echo # Let us mark locked table t1 as old +--send flush tables +--echo # Switching to connection 'dml' +connection dml; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables" and + info = "flush tables"; +--send select * from t1 +--echo # Switching to connection 'default' +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "select * from t1"; +--replace_result $ID2 ID2 +eval kill query $ID2; +--echo # Switching to connection 'dml' +connection dml; +--error ER_QUERY_INTERRUPTED +--reap +--echo # Switching to connection 'blocker' +connection blocker; +unlock tables; +--echo # Switching to connection 'ddl' +connection ddl; +--reap + +--echo # Cleanup. +--echo # Switching to connection 'default' +connection default; +drop table t3; +drop table t1; + ########################################################################### # Restore global concurrent_insert value. Keep in the end of the test file. diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index 04994e3e48f..856ae020492 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -178,6 +178,7 @@ select * from t2; --error ER_TABLE_NOT_LOCKED select * from t3; select * from v_bug5719; +--error ER_LOCK_OR_ACTIVE_TRANSACTION drop view v_bug5719; --echo --echo sic: did not left LOCK TABLES mode automatically @@ -185,7 +186,7 @@ drop view v_bug5719; --error ER_TABLE_NOT_LOCKED select * from t1; unlock tables; -create view v_bug5719 as select * from t1; +create or replace view v_bug5719 as select * from t1; lock tables v_bug5719 write; select * from v_bug5719; --echo diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 75ee6d07723..58f46941920 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -164,7 +164,7 @@ connection locker; # Sleep a bit till the select of connection reader is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = + where state = "Locked" and info = "SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1"; --source include/wait_condition.inc # Make test case independent from earlier grants. @@ -196,7 +196,7 @@ connection writer; # Sleep a bit till the flush of connection locker is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK"; + where state = "Waiting for table" and info = "FLUSH TABLES WITH READ LOCK"; --source include/wait_condition.inc # This must not block. CREATE TABLE t2 (c1 int); @@ -226,7 +226,7 @@ connection writer; # Sleep a bit till the flush of connection locker is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK"; + where state = "Waiting for table" and info = "FLUSH TABLES WITH READ LOCK"; --source include/wait_condition.inc --error ER_TABLE_NOT_LOCKED CREATE TABLE t2 AS SELECT * FROM t1; @@ -337,10 +337,10 @@ connection con2; send flush tables with read lock; connection con5; --echo # con5 -let $show_statement= SHOW PROCESSLIST; -let $field= State; -let $condition= = 'Flushing tables'; ---source include/wait_show_condition.inc +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "flush tables with read lock"; +--source include/wait_condition.inc --echo # global read lock is taken connection con3; --echo # con3 @@ -528,7 +528,7 @@ connection default; --echo connection: default let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc alter table t1 add column j int; connect (insert,localhost,root,,test,,); @@ -536,7 +536,7 @@ connection insert; --echo connection: insert let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc --send insert into t1 values (1,2); --echo connection: default @@ -586,18 +586,14 @@ connection default; --echo connection: default let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc flush tables; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc unlock tables; -let $wait_condition= - select count(*) = 0 from information_schema.processlist - where state = "Flushing tables"; ---source include/wait_condition.inc connection flush; --reap connection default; @@ -656,18 +652,14 @@ connection default; --echo connection: default let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc flush tables; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc drop table t1; -let $wait_condition= - select count(*) = 0 from information_schema.processlist - where state = "Flushing tables"; ---source include/wait_condition.inc connection flush; --reap connection default; diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test index fee235cd36c..6e771d44b3f 100644 --- a/mysql-test/t/ps_ddl.test +++ b/mysql-test/t/ps_ddl.test @@ -745,7 +745,7 @@ execute stmt; call p_verify_reprepare_count(1); flush table t1; execute stmt; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); execute stmt; --echo # Test 18-c: dependent VIEW has changed @@ -1453,7 +1453,7 @@ execute stmt; call p_verify_reprepare_count(0); drop temporary table t2; execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); drop table t2; execute stmt; call p_verify_reprepare_count(0); diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index b0342491a34..b477b5a3e24 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -1308,9 +1308,11 @@ select f3()| select id, f3() from t1 as t11 order by id| # Degenerate cases work too :) select f0()| +# But these should not (particularly views should be locked explicitly). +--error ER_TABLE_NOT_LOCKED select * from v0| +--error ER_TABLE_NOT_LOCKED select *, f0() from v0, (select 123) as d1| -# But these should not ! --error ER_TABLE_NOT_LOCKED select id, f3() from t1| --error ER_TABLE_NOT_LOCKED diff --git a/mysql-test/t/trigger_notembedded.test b/mysql-test/t/trigger_notembedded.test index 7a7e6c6bc85..8a570a7e87d 100644 --- a/mysql-test/t/trigger_notembedded.test +++ b/mysql-test/t/trigger_notembedded.test @@ -896,7 +896,7 @@ connection default; --echo connection: default let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc create trigger t1_bi before insert on t1 for each row begin end; unlock tables; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index abf8dac2870..c3ff58880c9 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1016,10 +1016,8 @@ lock tables t1 read, v1 read; select * from v1; -- error ER_TABLE_NOT_LOCKED select * from t2; -drop view v1; ---error ER_TABLE_NOT_LOCKED_FOR_WRITE -drop table t1, t2; unlock tables; +drop view v1; drop table t1, t2; # diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index f01edb1e499..d94bcfc29ed 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1047,9 +1047,9 @@ GRANT SELECT ON db26813.t1 TO u26813@localhost; connect (u1,localhost,u26813,,db26813); connection u1; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR +--error ER_TABLEACCESS_DENIED_ERROR ALTER VIEW v1 AS SELECT f2 FROM t1; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR +--error ER_TABLEACCESS_DENIED_ERROR ALTER VIEW v2 AS SELECT f2 FROM t1; --error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v3 AS SELECT f2 FROM t1; diff --git a/mysql-test/t/view_multi.test b/mysql-test/t/view_multi.test new file mode 100644 index 00000000000..a61e7738095 --- /dev/null +++ b/mysql-test/t/view_multi.test @@ -0,0 +1,110 @@ +# +# QQ: Should we find a better place for this test? +# May be binlog or rpl suites ? +# +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed_or_statement.inc + +# +# Bug #25144 "replication / binlog with view breaks". +# Statements that used views didn't ensure that view were not modified +# during their execution. Indeed this led to incorrect binary log with +# statement based logging. +# +--disable_parsing +drop table if not exists t1, t2; +drop view if exists v1; +--enable_parsing + +# We are going to use binary log later to check that statements are +# logged in proper order, so it is good idea to reset it here. +reset master; + +connect (addconn1,localhost,root,,); +connect (addconn2,localhost,root,,); +connection default; + +create table t1 (i int); +create table t2 (i int); +create view v1 as select * from t1; + +# First we try to concurrently execute statement that uses view +# and statement that drops it. We use "user" locks as means to +# suspend execution of first statement once it opens our view. +select get_lock("lock_bg25144", 1); + +connection addconn1; +--send insert into v1 values (get_lock("lock_bg25144", 100)); + +connection addconn2; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and info like "insert into v1 %lock_bg25144%"; +--source include/wait_condition.inc +--send drop view v1; + +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "drop view v1"; +--source include/wait_condition.inc + +select release_lock("lock_bg25144"); + +connection addconn1; +--reap +select release_lock("lock_bg25144"); + +connection addconn2; +--reap + +connection default; +# Check that insertion through view did happen. +select * from t1; +# At the end of test we will check that statements were +# logged in proper order. + +# Now we will repeat the test by trying concurrently execute +# statement that uses a view and statement that alters it. +create view v1 as select * from t1; + +select get_lock("lock_bg25144", 1); + +connection addconn1; +--send insert into v1 values (get_lock("lock_bg25144", 100)); + +connection addconn2; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and info like "insert into v1 %lock_bg25144%"; +--source include/wait_condition.inc +--send alter view v1 as select * from t2; + +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "alter view v1 as select * from t2"; +--source include/wait_condition.inc + +select release_lock("lock_bg25144"); + +connection addconn1; +--reap +select release_lock("lock_bg25144"); + +connection addconn2; +--reap + +connection default; + +# Second insertion should go to t1 as well. +select * from t1; +select * from t2; + +# Now let us check that statements were logged in proper order +--replace_column 2 # 5 # +show binlog events in 'master-bin.000001' from 107; + +drop table t1, t2; +drop view v1; |