diff options
Diffstat (limited to 'mysql-test/suite/handler')
| -rw-r--r-- | mysql-test/suite/handler/aria.result | 990 | ||||
| -rw-r--r-- | mysql-test/suite/handler/handler.inc | 1084 | ||||
| -rw-r--r-- | mysql-test/suite/handler/heap.result | 970 | ||||
| -rw-r--r-- | mysql-test/suite/handler/heap.test | 1 | ||||
| -rw-r--r-- | mysql-test/suite/handler/init.inc | 6 | ||||
| -rw-r--r-- | mysql-test/suite/handler/innodb.result | 992 | ||||
| -rw-r--r-- | mysql-test/suite/handler/interface.result | 73 | ||||
| -rw-r--r-- | mysql-test/suite/handler/interface.test | 148 | ||||
| -rw-r--r-- | mysql-test/suite/handler/myisam.result | 990 |
9 files changed, 5126 insertions, 128 deletions
diff --git a/mysql-test/suite/handler/aria.result b/mysql-test/suite/handler/aria.result index efddd7784fd..72d096132e6 100644 --- a/mysql-test/suite/handler/aria.result +++ b/mysql-test/suite/handler/aria.result @@ -1,6 +1,6 @@ SET SESSION STORAGE_ENGINE = Aria; drop table if exists t1,t3,t4,t5; -create table t1 (a int, b char(10), key a (a), key b (a,b)); +create table t1 (a int, b char(10), key a using btree (a), key b using btree (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), @@ -362,7 +362,8 @@ prepare stmt from 'handler t1 read a=(?)'; flush tables; set @a=17; execute stmt using @a; -ERROR HY000: Prepared statement needs to be re-prepared +a b c +17 ddd NULL deallocate prepare stmt; handler t1 close; handler t1 open as t2; @@ -372,7 +373,7 @@ insert into t1 values (17); handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open as t2; -alter table t1 engine=CSV; +alter table t1 engine=csv; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER drop table t1; @@ -395,7 +396,7 @@ handler t1 read first; a 6 drop table t1; -create table t1(a int, index (a)); +create table t1(a int, index using btree (a)); insert into t1 values (1), (2), (3); handler t1 open; handler t1 read a=(W); @@ -421,7 +422,7 @@ Ok handler t close; use test; drop table t1; -create table t1 ( a int, b int, INDEX a (a) ); +create table t1 ( a int, b int, INDEX a using btree (a) ); insert into t1 values (1,2), (2,1); handler t1 open; handler t1 read a=(1) where b=2; @@ -549,7 +550,7 @@ Table Op Msg_type Msg_text test.t1 optimize status OK proceed with the normal connection drop table t1; -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)); +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY using btree (no1,no2)); INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); HANDLER t1 OPEN; HANDLER t1 READ `primary` = (1, 1000); @@ -582,7 +583,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1; Warnings: Note 1051 Unknown table 't1' -create table t1 (a int not null) ENGINE=CSV; +create table t1 (a int not null) ENGINE=csv; --> client 2 handler t1 open; ERROR HY000: Table storage engine for 't1' doesn't have this option @@ -600,7 +601,7 @@ handler t1_alias READ a next where inexistent > 0; ERROR 42S22: Unknown column 'inexistent' in 'field list' handler t1_alias close; drop table t1; -create temporary table t1 (a int, b char(1), key a (a), key b (a,b)); +create temporary table t1 (a int, b char(1), key a using btree (a), key b using btree (a,b)); insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"),(9,'k'); select a,b from t1; @@ -671,7 +672,7 @@ a b handler a2 close; drop table t1; create table t1 (a int); -create temporary table t2 (a int, key (a)); +create temporary table t2 (a int, key using btree (a)); handler t1 open as a1; handler t2 open as a2; handler a2 read a first; @@ -681,7 +682,7 @@ handler a2 read a next; ERROR 42S02: Unknown table 'a2' in HANDLER handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -694,7 +695,7 @@ handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); handler t1 open as a1; handler a1 read a first; a @@ -702,7 +703,7 @@ rename table t1 to t2; handler a1 read a first; ERROR 42S02: Unknown table 'a1' in HANDLER drop table t2; -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -718,9 +719,972 @@ ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; # +# Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL. +# +drop table if exists t1, t2, t3; +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 (a int, key a (a)) select * from t1; +create temporary table t3 (a int, key a (a)) select * from t2; +handler t1 open; +handler t2 open; +handler t3 open; +# +# No HANDLER sql is allowed under LOCK TABLES. +# But it does not implicitly closes all handlers. +# +lock table t1 read; +handler t1 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t2 close; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t3 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# After UNLOCK TABLES handlers should be around and +# we should be able to continue reading through them. +unlock tables; +handler t1 read next; +a +1 +handler t1 close; +handler t2 read next; +a +1 +handler t2 close; +handler t3 read next; +a +1 +handler t3 close; +drop temporary table t3; +# +# Other operations that implicitly close handler: +# +# TRUNCATE +# +handler t1 open; +truncate table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER +# +create trigger t1_ai after insert on t1 for each row set @a=1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER +# +handler t1 open; +drop trigger t1_ai; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE +# +handler t1 open; +alter table t1 add column b int; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE +# +handler t1 open; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE +# +handler t1 open; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE +# +handler t1 open; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE, naturally. +# +handler t1 open; +drop table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)) select a from t2; +# +# RENAME TABLE, naturally +# +handler t1 open; +rename table t1 to t3; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# CREATE TABLE (even with IF NOT EXISTS clause, +# and the table exists). +# +handler t2 open; +create table if not exists t2 (a int); +Warnings: +Note 1050 Table 't2' already exists +handler t2 read next; +ERROR 42S02: Unknown table 't2' in HANDLER +rename table t3 to t1; +drop table t2; +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush table t1; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +# +# FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE. +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush tables with read lock; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +unlock tables; +# +# Let us also check that these operations behave in similar +# way under LOCK TABLES. +# +# TRUNCATE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +truncate table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER under LOCK TABLES. +# +lock tables t1 write; +create trigger t1_ai after insert on t1 for each row set @a=1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +drop trigger t1_ai; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +alter table t1 drop column b; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE under LOCK TABLES, naturally. +# +handler t1 open; +lock tables t1 write; +drop table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +a b +5 NULL +lock tables t1 write; +flush table t1; +unlock tables; +handler t1 read a prev; +a b +5 NULL +handler t1 close; +# +# Explore the effect of HANDLER locks on concurrent DDL +# +handler t1 open; +# Establishing auxiliary connections con1, con2, con3 +# --> connection con1; +# Sending: +drop table t1 ; +# We can't use connection 'default' as wait_condition will +# autoclose handlers. +# --> connection con2 +# Waitng for 'drop table t1' to get blocked... +# --> connection default +handler t1 read a prev; +a b +5 NULL +handler t1 read a prev; +a b +4 NULL +handler t1 close; +# --> connection con1 +# Reaping 'drop table t1'... +# --> connection default +# +# Explore the effect of HANDLER locks in parallel with SELECT +# +create table t1 (a int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 close; +# --> connection con1; +# Sending: +drop table t1 ; +# --> connection con2 +# Waiting for 'drop table t1' to get blocked... +# --> connection default +# We can still use the table, it's part of the transaction +select * from t1; +a +1 +2 +3 +4 +5 +# Such are the circumstances that t1 is a part of transaction, +# thus we can reopen it in the handler +handler t1 open; +# We can commit the transaction, it doesn't close the handler +# and doesn't let DROP to proceed. +commit; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 read a prev; +a +3 +handler t1 close; +# --> connection con1 +# Now drop can proceed +# Reaping 'drop table t1'... +# --> connection default +# +# Demonstrate that HANDLER locks and transaction locks +# reside in the same context, and we don't back-off +# when have transaction or handler locks. +# +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t0 (a int, key a using btree (a)); +insert into t0 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +# --> connection con2 +# Sending: +rename table t0 to t3, t1 to t0, t3 to t1; +# --> connection con1 +# Waiting for 'rename table ...' to get blocked... +# --> connection default +handler t0 open; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select * from t0; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +handler t1 open; +commit; +handler t1 close; +# --> connection con2 +# Reaping 'rename table ...'... +# --> connection default +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 close; +drop table t0; +# +# Originally there was a deadlock error in this test. +# With implementation of deadlock detector +# we no longer deadlock, but block and wait on a lock. +# The HANDLER is auto-closed as soon as the connection +# sees a pending conflicting lock against it. +# +create table t2 (a int, key a (a)); +handler t1 open; +# --> connection con1 +lock tables t2 read; +# --> connection con2 +# Sending 'drop table t2'... +drop table t2; +# --> connection con1 +# Waiting for 'drop table t2' to get blocked... +# --> connection default +# Sending 'select * from t2' +select * from t2; +# --> connection con1 +# Waiting for 'select * from t2' to get blocked... +unlock tables; +# --> connection con2 +# Reaping 'drop table t2'... +# --> connection default +# Reaping 'select * from t2' +ERROR 42S02: Table 'test.t2' doesn't exist +handler t1 close; +# +# ROLLBACK TO SAVEPOINT releases transactional locks, +# but has no effect on open HANDLERs +# +create table t2 like t1; +create table t3 like t1; +begin; +# Have something before the savepoint +select * from t3; +a +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +drop table t3; +# +# A few special cases when using SAVEPOINT/ROLLBACK TO +# SAVEPOINT and HANDLER. +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction doesn't release mdl lock on +# the HANDLER that was opened later. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +begin; +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction works properly (no valgrind warnins, etc), +# even though it's done after the HANDLER mdl lock that was there +# at the beginning is released and added again. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +create table t3 like t1; +insert into t3 (a) select a from t1; +begin; +handler t1 open; +savepoint sv; +handler t1 read a first; +a +1 +select * from t2; +a +handler t1 close; +handler t3 open; +handler t3 read a first; +a +1 +rollback to savepoint sv; +# --> connection con1 +drop table t1, t2; +# Sending: +drop table t3; +# Let DROP TABLE statement sync in. +# --> connection con2 +# Waiting for 'drop table t3' to get blocked... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t3 read a next; +a +2 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t3 close; +# connection con1 +# Reaping 'drop table t3'... +# --> connection default +commit; +# +# If we have to wait on an exclusive locks while having +# an open HANDLER, ER_LOCK_DEADLOCK is reported. +# +create table t1 (a int, key a(a)); +create table t2 like t1; +handler t1 open; +# --> connection con1 +lock table t1 write, t2 write; +# --> connection default +drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default +# Demonstrate that there is no deadlock with FLUSH TABLE, +# even though it is waiting for the other table to go away +create table t2 like t1; +# Sending: +flush table t2; +# --> connection con2 +drop table t1; +# --> connection con1 +unlock tables; +# --> connection default +# Reaping 'flush table t2'... +drop table t2; +# +# Bug #46224 HANDLER statements within a transaction might +# lead to deadlocks +# +create table t1 (a int, key using btree (a)); +insert into t1 values (1), (2); +# --> connection default +begin; +select * from t1; +a +1 +2 +handler t1 open; +# --> connection con1 +# Sending: +lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. +# --> connection default +# Sending: +handler t1 read a next; +# --> connection con1 +# Waiting for 'handler t1 read a next' to get blocked... +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. +drop table t1; +unlock tables; +# --> connection default +# Reaping 'handler t1 read a next'... +ERROR 42S02: Table 'test.t1' doesn't exist +handler t1 close; +# --> connection con1 +# --> connection con2 +# --> connection con3 +# +# A temporary table test. +# Check that we don't loose positions of HANDLER opened +# against a temporary table. +# +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create temporary table t2 (a int, b int, key using btree (a)); +insert into t2 (a) select a from t1; +handler t1 open; +handler t1 read a next; +a b +1 NULL +handler t2 open; +handler t2 read a next; +a b +1 NULL +flush table t1; +handler t2 read a next; +a b +2 NULL +# Sic: the position is lost +handler t1 read a next; +a b +1 NULL +select * from t1; +a b +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL +# Sic: the position is not lost +handler t2 read a next; +a b +3 NULL +select * from t2; +ERROR HY000: Can't reopen table: 't2' +handler t2 read a next; +a b +4 NULL +drop table t1; +drop temporary table t2; +# +# A test for lock_table_names()/unlock_table_names() function. +# It should work properly in presence of open HANDLER. +# +create table t1 (a int, b int, key a (a)); +create table t2 like t1; +create table t3 like t1; +create table t4 like t1; +handler t1 open; +handler t2 open; +rename table t4 to t5, t3 to t4, t5 to t3; +handler t1 read first; +a b +handler t2 read first; +a b +drop table t1, t2, t3, t4; +# +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +a b +7 7 +commit; +# -> connection con1 +# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +# -> connection default +handler t1 read a prev; +a b +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default +# +# A test for Bug#50555 "handler commands crash server in +# my_hash_first()". +# +handler no_such_table read no_such_index first; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +handler no_such_table close; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +# +# Bug#50907 Assertion `hash_tables->table->next == __null' on +# HANDLER OPEN +# +DROP TABLE IF EXISTS t1, t2; +CREATE TEMPORARY TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); +HANDLER t2 OPEN; +HANDLER t2 READ FIRST; +i +HANDLER t2 CLOSE; +DROP TABLE t1, t2; +# +# Bug#50912 Assertion `ticket->m_type >= mdl_request->type' +# failed on HANDLER + I_S +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (id INT); +HANDLER t1 OPEN; +SELECT table_name, table_comment FROM information_schema.tables +WHERE table_schema= 'test' AND table_name= 't1'; +table_name table_comment +t1 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# Test for bug #50908 "Assertion `handler_tables_hash.records == 0' +# failed in enter_locked_tables_mode". +# +drop tables if exists t1, t2; +drop function if exists f1; +create table t1 (i int); +insert into t1 values (1), (2); +create table t2 (j int); +insert into t2 values (1); +create function f1() returns int return (select count(*) from t2); +# Check that open HANDLER survives statement executed in +# prelocked mode. +handler t1 open; +handler t1 read next; +i +1 +# The below statement were aborted due to an assertion failure. +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +handler t1 close; +# Check that the same happens under GLOBAL READ LOCK. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +unlock tables; +handler t1 close; +# Now, check that the same happens if LOCK TABLES is executed. +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Finally, check scenario with GRL and LOCK TABLES. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +# This unlocks both tables and GRL. +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Clean-up. +drop function f1; +drop tables t1, t2; +# +# Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + +# HANDLER + LOCK + SP". +# Also see additional coverage for this bug in flush.test. +# +drop tables if exists t1, t2; +create table t1 (i int); +create temporary table t2 (j int); +handler t1 open; +lock table t2 read; +# This commit should not release any MDL locks. +commit; +unlock tables; +# The below statement crashed before the bug fix as it +# has attempted to release metadata lock which was +# already released by commit. +handler t1 close; +drop tables t1, t2; +# +# Bug#51355 handler stmt cause assertion in +# bool MDL_context::try_acquire_lock(MDL_request*) +# +DROP TABLE IF EXISTS t1; +# Connection default +CREATE TABLE t1(id INT, KEY id(id)); +HANDLER t1 OPEN; +# Connection con51355 +# Sending: +DROP TABLE t1; +# Connection default +# This I_S query will cause the handler table to be closed and +# the metadata lock to be released. This will allow DROP TABLE +# to proceed. Waiting for the table to be removed. +# Connection con51355 +# Reaping: DROP TABLE t1 +# Connection default +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 CLOSE; +# Connection con51355 +# Connection default +# +# Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +# +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +SELECT 1 FROM t2 INTO @a; +RETURN 1; +END| +SELECT f1(); +ERROR 42S02: Table 'test.t2' doesn't exist +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# +# Bug#54920 Stored functions are allowed in HANDLER statements, +# but broken. +# +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# # BUG#51877 - HANDLER interface causes invalid memory read # -CREATE TABLE t1(a INT, KEY (a)); +CREATE TABLE t1(a INT, KEY using btree (a)); HANDLER t1 OPEN; HANDLER t1 READ a FIRST; a diff --git a/mysql-test/suite/handler/handler.inc b/mysql-test/suite/handler/handler.inc index 5c87652f2b5..e099d22e3ce 100644 --- a/mysql-test/suite/handler/handler.inc +++ b/mysql-test/suite/handler/handler.inc @@ -4,7 +4,6 @@ # # The variables # $engine_type -- storage engine to be tested -# $other_engine_type -- storage engine <> $engine_type # $other_handler_engine_type -- storage engine <> $engine_type, if possible # 1. $other_handler_engine_type must support handler # 2. $other_handler_engine_type must point to an all @@ -19,6 +18,8 @@ # main testing code put into handler.inc # +source include/have_csv.inc; + # # Start testing the table created in init.inc # @@ -215,7 +216,6 @@ handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; set @a=17; ---error ER_NEED_REPREPARE execute stmt using @a; deallocate prepare stmt; handler t1 close; @@ -230,7 +230,7 @@ insert into t1 values (17); --error 1109 handler t2 read first; handler t1 open as t2; -eval alter table t1 engine=$other_engine_type; +alter table t1 engine=csv; --error 1109 handler t2 read first; drop table t1; @@ -252,7 +252,7 @@ drop table t1; # # Test for #751 # -eval create table t1(a int, index $key_type (a)); +create table t1(a int, index using btree (a)); insert into t1 values (1), (2), (3); handler t1 open; --error 1054 @@ -279,7 +279,7 @@ drop table t1; # # BUG#3649 # -eval create table t1 ( a int, b int, INDEX a $key_type (a) ); +create table t1 ( a int, b int, INDEX a using btree (a) ); insert into t1 values (1,2), (2,1); handler t1 open; handler t1 read a=(1) where b=2; @@ -371,24 +371,24 @@ handler t1 read first; # client 2 connect (con2,localhost,root,,); connection con2; ---exec echo send the below to another connection, do not wait for the result +--echo send the below to another connection, do not wait for the result send optimize table t1; --sleep 1 # client 1 ---exec echo proceed with the normal connection +--echo proceed with the normal connection connection default; handler t1 read next; handler t1 close; # client 2 ---exec echo read the result from the other connection +--echo read the result from the other connection connection con2; reap; # client 1 ---exec echo proceed with the normal connection +--echo proceed with the normal connection connection default; drop table t1; -eval CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY $key_type (no1,no2)); +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY using btree (no1,no2)); INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); HANDLER t1 OPEN; HANDLER t1 READ `primary` = (1, 1000); @@ -416,14 +416,14 @@ drop table t1; # The drop waits for the global read lock to go away. # Without the addendum fix it locked LOCK_open before entering the wait loop. connection con2; ---exec echo send the below to another connection, do not wait for the result +--echo send the below to another connection, do not wait for the result send drop table t1; --sleep 1 # # client 1 # Now we need something that wants LOCK_open. A simple table access which # opens the table does the trick. ---exec echo proceed with the normal connection +--echo proceed with the normal connection connection default; # This would hang on LOCK_open without the 5.0 addendum fix. select * from t1; @@ -433,12 +433,12 @@ unlock tables; # client 2 # Read the result of the drop command. connection con2; ---exec echo read the result from the other connection +--echo read the result from the other connection reap; # # client 1 # Now back to normal operation. The table should not exist any more. ---exec echo proceed with the normal connection +--echo proceed with the normal connection connection default; --error 1146 select * from t1; @@ -448,7 +448,7 @@ drop table if exists t1; # # Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one # -eval create table t1 (a int not null) ENGINE=$other_engine_type; +create table t1 (a int not null) ENGINE=csv; --echo --> client 2 connection con2; --error 1031 @@ -481,7 +481,7 @@ drop table t1; # is open by a HANDLER, no other statement can access it. # -eval create temporary table t1 (a int, b char(1), key a $key_type (a), key b $key_type (a,b)); +create temporary table t1 (a int, b char(1), key a using btree (a), key b using btree (a,b)); insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"),(9,'k'); select a,b from t1; @@ -509,7 +509,7 @@ drop table t1; # Test that temporary tables associated with handlers are properly dropped. create table t1 (a int); -eval create temporary table t2 (a int, key $key_type (a)); +create temporary table t2 (a int, key using btree (a)); handler t1 open as a1; handler t2 open as a2; handler a2 read a first; @@ -521,7 +521,7 @@ handler a1 close; # Alter table drop handlers -eval create table t1 (a int, key $key_type (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -535,7 +535,7 @@ drop table t1, t2; # Rename table drop handlers -eval create table t1 (a int, key $key_type (a)); +create table t1 (a int, key using btree (a)); handler t1 open as a1; handler a1 read a first; rename table t1 to t2; @@ -545,7 +545,7 @@ drop table t2; # Optimize table drop handlers -eval create table t1 (a int, key $key_type (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -558,9 +558,1051 @@ handler a2 close; drop table t1, t2; --echo # +--echo # Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL. +--echo # +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 (a int, key a (a)) select * from t1; +create temporary table t3 (a int, key a (a)) select * from t2; +handler t1 open; +handler t2 open; +handler t3 open; +--echo # +--echo # No HANDLER sql is allowed under LOCK TABLES. +--echo # But it does not implicitly closes all handlers. +--echo # +lock table t1 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t1 open; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t1 read next; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t2 close; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t3 open; +--echo # After UNLOCK TABLES handlers should be around and +--echo # we should be able to continue reading through them. +unlock tables; +handler t1 read next; +handler t1 close; +handler t2 read next; +handler t2 close; +handler t3 read next; +handler t3 close; +drop temporary table t3; +--echo # +--echo # Other operations that implicitly close handler: +--echo # +--echo # TRUNCATE +--echo # +handler t1 open; +truncate table t1; +--error ER_UNKNOWN_TABLE +handler t1 read next; +handler t1 open; +--echo # +--echo # CREATE TRIGGER +--echo # +create trigger t1_ai after insert on t1 for each row set @a=1; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # DROP TRIGGER +--echo # +handler t1 open; +drop trigger t1_ai; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # ALTER TABLE +--echo # +handler t1 open; +alter table t1 add column b int; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # ANALYZE TABLE +--echo # +handler t1 open; +analyze table t1; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # OPTIMIZE TABLE +--echo # +handler t1 open; +optimize table t1; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # REPAIR TABLE +--echo # +handler t1 open; +repair table t1; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # DROP TABLE, naturally. +--echo # +handler t1 open; +drop table t1; +--error ER_UNKNOWN_TABLE +handler t1 read next; +create table t1 (a int, b int, key a using btree (a)) select a from t2; +--echo # +--echo # RENAME TABLE, naturally +--echo # +handler t1 open; +rename table t1 to t3; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # CREATE TABLE (even with IF NOT EXISTS clause, +--echo # and the table exists). +--echo # +handler t2 open; +create table if not exists t2 (a int); +--error ER_UNKNOWN_TABLE +handler t2 read next; +rename table t3 to t1; +drop table t2; +--echo # +--echo # FLUSH TABLE doesn't close the table but loses the position +--echo # +handler t1 open; +handler t1 read a prev; +flush table t1; +handler t1 read a prev; +handler t1 close; +--echo # +--echo # FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE. +--echo # +handler t1 open; +handler t1 read a prev; +flush tables with read lock; +handler t1 read a prev; +handler t1 close; +unlock tables; +--echo # +--echo # Let us also check that these operations behave in similar +--echo # way under LOCK TABLES. +--echo # +--echo # TRUNCATE under LOCK TABLES. +--echo # +handler t1 open; +lock tables t1 write; +truncate table t1; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +handler t1 open; +--echo # +--echo # CREATE TRIGGER under LOCK TABLES. +--echo # +lock tables t1 write; +create trigger t1_ai after insert on t1 for each row set @a=1; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # DROP TRIGGER under LOCK TABLES. +--echo # +handler t1 open; +lock tables t1 write; +drop trigger t1_ai; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # ALTER TABLE under LOCK TABLES. +--echo # +handler t1 open; +lock tables t1 write; +alter table t1 drop column b; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # ANALYZE TABLE under LOCK TABLES. +--echo # +handler t1 open; +lock tables t1 write; +analyze table t1; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # OPTIMIZE TABLE under LOCK TABLES. +--echo # +handler t1 open; +lock tables t1 write; +optimize table t1; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # REPAIR TABLE under LOCK TABLES. +--echo # +handler t1 open; +lock tables t1 write; +repair table t1; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--echo # +--echo # DROP TABLE under LOCK TABLES, naturally. +--echo # +handler t1 open; +lock tables t1 write; +drop table t1; +unlock tables; +--error ER_UNKNOWN_TABLE +handler t1 read next; +create table t1 (a int, b int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +--echo # +--echo # FLUSH TABLE doesn't close the table but loses the position +--echo # +handler t1 open; +handler t1 read a prev; +lock tables t1 write; +flush table t1; +unlock tables; +handler t1 read a prev; +handler t1 close; +--echo # +--echo # Explore the effect of HANDLER locks on concurrent DDL +--echo # +handler t1 open; +--echo # Establishing auxiliary connections con1, con2, con3 +connect(con1, localhost, root,,); +connect(con2, localhost, root,,); +connect(con3, localhost, root,,); +--echo # --> connection con1; +connection con1; +--echo # Sending: +--send drop table t1 +--echo # We can't use connection 'default' as wait_condition will +--echo # autoclose handlers. +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop table t1' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; +--source include/wait_condition.inc +--echo # --> connection default +connection default; +handler t1 read a prev; +handler t1 read a prev; +handler t1 close; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop table t1'... +--reap +--echo # --> connection default +connection default; +--echo # +--echo # Explore the effect of HANDLER locks in parallel with SELECT +--echo # +create table t1 (a int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +handler t1 open; +handler t1 read a prev; +handler t1 read a prev; +handler t1 close; +--echo # --> connection con1; +connection con1; +--echo # Sending: +--send drop table t1 +--echo # --> connection con2 +connection con2; +--echo # Waiting for 'drop table t1' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; +--source include/wait_condition.inc +--echo # --> connection default +connection default; +--echo # We can still use the table, it's part of the transaction +select * from t1; +--echo # Such are the circumstances that t1 is a part of transaction, +--echo # thus we can reopen it in the handler +handler t1 open; +--echo # We can commit the transaction, it doesn't close the handler +--echo # and doesn't let DROP to proceed. +commit; +handler t1 read a prev; +handler t1 read a prev; +handler t1 read a prev; +handler t1 close; +--echo # --> connection con1 +connection con1; +--echo # Now drop can proceed +--echo # Reaping 'drop table t1'... +--reap +--echo # --> connection default +connection default; +--echo # +--echo # Demonstrate that HANDLER locks and transaction locks +--echo # reside in the same context, and we don't back-off +--echo # when have transaction or handler locks. +--echo # +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t0 (a int, key a using btree (a)); +insert into t0 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +--echo # --> connection con2 +connection con2; +--echo # Sending: +send rename table t0 to t3, t1 to t0, t3 to t1; +--echo # --> connection con1 +connection con1; +--echo # Waiting for 'rename table ...' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='rename table t0 to t3, t1 to t0, t3 to t1'; +--source include/wait_condition.inc +--echo # --> connection default +connection default; +--error ER_LOCK_DEADLOCK +handler t0 open; +--error ER_LOCK_DEADLOCK +select * from t0; +handler t1 open; +commit; +handler t1 close; +--echo # --> connection con2 +connection con2; +--echo # Reaping 'rename table ...'... +--reap +--echo # --> connection default +connection default; +handler t1 open; +handler t1 read a prev; +handler t1 close; +drop table t0; +--echo # +--echo # Originally there was a deadlock error in this test. +--echo # With implementation of deadlock detector +--echo # we no longer deadlock, but block and wait on a lock. +--echo # The HANDLER is auto-closed as soon as the connection +--echo # sees a pending conflicting lock against it. +--echo # +create table t2 (a int, key a (a)); +handler t1 open; +--echo # --> connection con1 +connection con1; +lock tables t2 read; +--echo # --> connection con2 +connection con2; +--echo # Sending 'drop table t2'... +--send drop table t2 +--echo # --> connection con1 +connection con1; +--echo # Waiting for 'drop table t2' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; +--source include/wait_condition.inc +--echo # --> connection default +connection default; +--echo # Sending 'select * from t2' +send select * from t2; +--echo # --> connection con1 +connection con1; +--echo # Waiting for 'select * from t2' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='select * from t2'; +unlock tables; +--echo # --> connection con2 +connection con2; +--echo # Reaping 'drop table t2'... +--reap +--echo # --> connection default +connection default; +--echo # Reaping 'select * from t2' +--error ER_NO_SUCH_TABLE +reap; +handler t1 close; + +--echo # +--echo # ROLLBACK TO SAVEPOINT releases transactional locks, +--echo # but has no effect on open HANDLERs +--echo # +create table t2 like t1; +create table t3 like t1; +begin; +--echo # Have something before the savepoint +select * from t3; +savepoint sv; +handler t1 open; +handler t1 read a first; +handler t1 read a next; +select * from t2; +--echo # --> connection con1 +connection con1; +--echo # Sending: +--send drop table t1 +--echo # --> connection con2 +connection con2; +--echo # Sending: +--send drop table t2 +--echo # --> connection default +connection default; +--echo # Let DROP TABLE statements sync in. We must use +--echo # a separate connection for that, because otherwise SELECT +--echo # will auto-close the HANDLERs, becaues there are pending +--echo # exclusive locks against them. +--echo # --> connection con3 +connection con3; +--echo # Waiting for 'drop table t1' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; +--source include/wait_condition.inc +--echo # Waiting for 'drop table t2' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; +--source include/wait_condition.inc +--echo # Demonstrate that t2 lock was released and t2 was dropped +--echo # after ROLLBACK TO SAVEPOINT +--echo # --> connection default +connection default; +rollback to savepoint sv; +--echo # --> connection con2 +connection con2; +--echo # Reaping 'drop table t2'... +--reap +--echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +--echo # lock. +--echo # --> connection default +connection default; +handler t1 read a next; +handler t1 read a next; +--echo # Demonstrate that the drop will go through as soon as we close the +--echo # HANDLER +handler t1 close; +--echo # connection con1 +connection con1; +--echo # Reaping 'drop table t1'... +--reap +--echo # --> connection default +connection default; +commit; +drop table t3; +--echo # +--echo # A few special cases when using SAVEPOINT/ROLLBACK TO +--echo # SAVEPOINT and HANDLER. +--echo # +--echo # Show that rollback to the savepoint taken in the beginning +--echo # of the transaction doesn't release mdl lock on +--echo # the HANDLER that was opened later. +--echo # +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +begin; +savepoint sv; +handler t1 open; +handler t1 read a first; +handler t1 read a next; +select * from t2; +--echo # --> connection con1 +connection con1; +--echo # Sending: +--send drop table t1 +--echo # --> connection con2 +connection con2; +--echo # Sending: +--send drop table t2 +--echo # --> connection default +connection default; +--echo # Let DROP TABLE statements sync in. We must use +--echo # a separate connection for that, because otherwise SELECT +--echo # will auto-close the HANDLERs, becaues there are pending +--echo # exclusive locks against them. +--echo # --> connection con3 +connection con3; +--echo # Waiting for 'drop table t1' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; +--source include/wait_condition.inc +--echo # Waiting for 'drop table t2' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; +--source include/wait_condition.inc +--echo # Demonstrate that t2 lock was released and t2 was dropped +--echo # after ROLLBACK TO SAVEPOINT +--echo # --> connection default +connection default; +rollback to savepoint sv; +--echo # --> connection con2 +connection con2; +--echo # Reaping 'drop table t2'... +--reap +--echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +--echo # lock. +--echo # --> connection default +connection default; +handler t1 read a next; +handler t1 read a next; +--echo # Demonstrate that the drop will go through as soon as we close the +--echo # HANDLER +handler t1 close; +--echo # connection con1 +connection con1; +--echo # Reaping 'drop table t1'... +--reap +--echo # --> connection default +connection default; +commit; +--echo # +--echo # Show that rollback to the savepoint taken in the beginning +--echo # of the transaction works properly (no valgrind warnins, etc), +--echo # even though it's done after the HANDLER mdl lock that was there +--echo # at the beginning is released and added again. +--echo # +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +create table t3 like t1; +insert into t3 (a) select a from t1; +begin; +handler t1 open; +savepoint sv; +handler t1 read a first; +select * from t2; +handler t1 close; +handler t3 open; +handler t3 read a first; +rollback to savepoint sv; +--echo # --> connection con1 +connection con1; +drop table t1, t2; +--echo # Sending: +--send drop table t3 +--echo # Let DROP TABLE statement sync in. +--echo # --> connection con2 +connection con2; +--echo # Waiting for 'drop table t3' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t3'; +--source include/wait_condition.inc +--echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +--echo # lock. +--echo # --> connection default +connection default; +handler t3 read a next; +--echo # Demonstrate that the drop will go through as soon as we close the +--echo # HANDLER +handler t3 close; +--echo # connection con1 +connection con1; +--echo # Reaping 'drop table t3'... +--reap +--echo # --> connection default +connection default; +commit; + +--echo # +--echo # If we have to wait on an exclusive locks while having +--echo # an open HANDLER, ER_LOCK_DEADLOCK is reported. +--echo # +create table t1 (a int, key a(a)); +create table t2 like t1; +handler t1 open; +--echo # --> connection con1 +connection con1; +lock table t1 write, t2 write; +--echo # --> connection default +connection default; +send drop table t2; +--echo # --> connection con2 +connection con2; +--echo # Waiting for 'drop table t2' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; +--source include/wait_condition.inc +--echo # --> connection con1 +connection con1; +--error ER_LOCK_DEADLOCK +drop table t1; +unlock tables; +--echo # --> connection default +connection default; +reap; + +--echo # Demonstrate that there is no deadlock with FLUSH TABLE, +--echo # even though it is waiting for the other table to go away +create table t2 like t1; +--echo # Sending: +--send flush table t2 +--echo # --> connection con2 +connection con2; +drop table t1; +--echo # --> connection con1 +connection con1; +unlock tables; +--echo # --> connection default +connection default; +--echo # Reaping 'flush table t2'... +--reap +drop table t2; + +--echo # +--echo # Bug #46224 HANDLER statements within a transaction might +--echo # lead to deadlocks +--echo # +create table t1 (a int, key using btree (a)); +insert into t1 values (1), (2); + +--echo # --> connection default +connection default; +begin; +select * from t1; +handler t1 open; + +--echo # --> connection con1 +connection con1; +--echo # Sending: +--send lock tables t1 write + +--echo # --> connection con2 +connection con2; +--echo # Check that 'lock tables t1 write' waits until transaction which +--echo # has read from the table commits. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock tables t1 write"; +--source include/wait_condition.inc + +--echo # --> connection default +connection default; +--echo # The below 'handler t1 read ...' should not be blocked as +--echo # 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; + +--echo # Unblock 'lock tables t1 write'. +commit; + +--echo # --> connection con1 +connection con1; +--echo # Reap 'lock tables t1 write'. +--reap + +--echo # --> connection default +connection default; +--echo # Sending: +--send handler t1 read a next + +--echo # --> connection con1 +connection con1; +--echo # Waiting for 'handler t1 read a next' to get blocked... +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "handler t1 read a next"; +--source include/wait_condition.inc + +--echo # The below 'drop table t1' should be able to proceed without +--echo # waiting as it will force HANDLER to be closed. +drop table t1; +unlock tables; + +--echo # --> connection default +connection default; +--echo # Reaping 'handler t1 read a next'... +--error ER_NO_SUCH_TABLE +--reap +handler t1 close; + +--echo # --> connection con1 +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +--echo # --> connection con2 +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +--echo # --> connection con3 +connection con3; +disconnect con3; +--source include/wait_until_disconnected.inc +connection default; + +--echo # +--echo # A temporary table test. +--echo # Check that we don't loose positions of HANDLER opened +--echo # against a temporary table. +--echo # +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create temporary table t2 (a int, b int, key using btree (a)); +insert into t2 (a) select a from t1; +handler t1 open; +handler t1 read a next; +handler t2 open; +handler t2 read a next; +flush table t1; +handler t2 read a next; +--echo # Sic: the position is lost +handler t1 read a next; +select * from t1; +--echo # Sic: the position is not lost +handler t2 read a next; +--error ER_CANT_REOPEN_TABLE +select * from t2; +handler t2 read a next; +drop table t1; +drop temporary table t2; + +--echo # +--echo # A test for lock_table_names()/unlock_table_names() function. +--echo # It should work properly in presence of open HANDLER. +--echo # +create table t1 (a int, b int, key a (a)); +create table t2 like t1; +create table t3 like t1; +create table t4 like t1; +handler t1 open; +handler t2 open; +rename table t4 to t5, t3 to t4, t5 to t3; +handler t1 read first; +handler t2 read first; +drop table t1, t2, t3, t4; + +--echo # +--echo # A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +--echo # +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t1 read next; +--echo # This implicitly leaves LOCK TABLES but doesn't drop the GLR +--error ER_NO_SUCH_TABLE +lock table not_exists_write read; +--echo # We still have the read lock. +--error ER_CANT_UPDATE_WITH_READLOCK +drop table t1; +handler t1 open; +select a from t2; +handler t1 read next; +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +select a from t3; +handler t2 read next; +handler t1 close; +rollback; +handler t2 close; +--error ER_CANT_UPDATE_WITH_READLOCK +drop table t1; +commit; +flush tables; +--error ER_CANT_UPDATE_WITH_READLOCK +drop table t1; +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; + +--echo # +--echo # HANDLER statement and operation-type aware metadata locks. +--echo # Check that when we clone a ticket for HANDLER we downrade +--echo # the lock. +--echo # +--echo # Establish an auxiliary connection con1. +connect (con1,localhost,root,,); +--echo # -> connection default +connection default; +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +commit; +--echo # -> connection con1 +connection con1; +--echo # Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +--echo # -> connection default +connection default; +handler t1 read a prev; +handler t1 close; +--echo # Cleanup. +drop table t1; +--echo # -> connection con1 +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +--echo # -> connection default +connection default; + +--echo # +--echo # A test for Bug#50555 "handler commands crash server in +--echo # my_hash_first()". +--echo # +--error ER_UNKNOWN_TABLE +handler no_such_table read no_such_index first; +--error ER_UNKNOWN_TABLE +handler no_such_table close; + + +--echo # +--echo # Bug#50907 Assertion `hash_tables->table->next == __null' on +--echo # HANDLER OPEN +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TEMPORARY TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); + +# This used to trigger the assert +HANDLER t2 OPEN; + +# This also used to trigger the assert +HANDLER t2 READ FIRST; + +HANDLER t2 CLOSE; +DROP TABLE t1, t2; + + +--echo # +--echo # Bug#50912 Assertion `ticket->m_type >= mdl_request->type' +--echo # failed on HANDLER + I_S +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (id INT); +HANDLER t1 OPEN; + +# This used to trigger the assert. +SELECT table_name, table_comment FROM information_schema.tables + WHERE table_schema= 'test' AND table_name= 't1'; + +HANDLER t1 CLOSE; +DROP TABLE t1; + + +--echo # +--echo # Test for bug #50908 "Assertion `handler_tables_hash.records == 0' +--echo # failed in enter_locked_tables_mode". +--echo # +--disable_warnings +drop tables if exists t1, t2; +drop function if exists f1; +--enable_warnings +create table t1 (i int); +insert into t1 values (1), (2); +create table t2 (j int); +insert into t2 values (1); +create function f1() returns int return (select count(*) from t2); +--echo # Check that open HANDLER survives statement executed in +--echo # prelocked mode. +handler t1 open; +handler t1 read next; +--echo # The below statement were aborted due to an assertion failure. +select f1() from t2; +handler t1 read next; +handler t1 close; +--echo # Check that the same happens under GLOBAL READ LOCK. +flush tables with read lock; +handler t1 open; +handler t1 read next; +select f1() from t2; +handler t1 read next; +unlock tables; +handler t1 close; +--echo # Now, check that the same happens if LOCK TABLES is executed. +handler t1 open; +handler t1 read next; +lock table t2 read; +select * from t2; +unlock tables; +handler t1 read next; +handler t1 close; +--echo # Finally, check scenario with GRL and LOCK TABLES. +flush tables with read lock; +handler t1 open; +handler t1 read next; +lock table t2 read; +select * from t2; +--echo # This unlocks both tables and GRL. +unlock tables; +handler t1 read next; +handler t1 close; +--echo # Clean-up. +drop function f1; +drop tables t1, t2; + + +--echo # +--echo # Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + +--echo # HANDLER + LOCK + SP". +--echo # Also see additional coverage for this bug in flush.test. +--echo # +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +create table t1 (i int); +create temporary table t2 (j int); +handler t1 open; +lock table t2 read; +--echo # This commit should not release any MDL locks. +commit; +unlock tables; +--echo # The below statement crashed before the bug fix as it +--echo # has attempted to release metadata lock which was +--echo # already released by commit. +handler t1 close; +drop tables t1, t2; + + +--echo # +--echo # Bug#51355 handler stmt cause assertion in +--echo # bool MDL_context::try_acquire_lock(MDL_request*) +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +connect(con51355, localhost, root); + +--echo # Connection default +connection default; +CREATE TABLE t1(id INT, KEY id(id)); +HANDLER t1 OPEN; + +--echo # Connection con51355 +connection con51355; +--echo # Sending: +--send DROP TABLE t1 + +--echo # Connection default +connection default; +--echo # This I_S query will cause the handler table to be closed and +--echo # the metadata lock to be released. This will allow DROP TABLE +--echo # to proceed. Waiting for the table to be removed. +let $wait_condition= + SELECT COUNT(*) = 0 FROM information_schema.tables WHERE table_name = "t1"; +--source include/wait_condition.inc + +--echo # Connection con51355 +connection con51355; +--echo # Reaping: DROP TABLE t1 +--reap + +--echo # Connection default +connection default; +--error ER_NO_SUCH_TABLE +HANDLER t1 READ id NEXT; +# This caused an assertion +--error ER_NO_SUCH_TABLE +HANDLER t1 READ id NEXT; + +HANDLER t1 CLOSE; +--echo # Connection con51355 +connection con51355; +disconnect con51355; +--source include/wait_until_disconnected.inc +--echo # Connection default +connection default; + + +--echo # +--echo # Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +delimiter |; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN + SELECT 1 FROM t2 INTO @a; + RETURN 1; +END| +delimiter ;| + +# Get f1() parsed and cached +--error ER_NO_SUCH_TABLE +SELECT f1(); + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +# This used to cause the assert +--error ER_NOT_SUPPORTED_YET +HANDLER t1 READ FIRST WHERE f1() = 1; +HANDLER t1 CLOSE; + +DROP FUNCTION f1; +DROP TABLE t1; + +--echo # +--echo # Bug#54920 Stored functions are allowed in HANDLER statements, +--echo # but broken. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; + +--error ER_NOT_SUPPORTED_YET +HANDLER t1 READ FIRST WHERE f1() = 1; + +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; + +--echo # --echo # BUG#51877 - HANDLER interface causes invalid memory read --echo # -eval CREATE TABLE t1(a INT, KEY $key_type (a)); +CREATE TABLE t1(a INT, KEY using btree (a)); HANDLER t1 OPEN; HANDLER t1 READ a FIRST; INSERT INTO t1 VALUES(1); diff --git a/mysql-test/suite/handler/heap.result b/mysql-test/suite/handler/heap.result index 04bc9c73159..0d59440f88a 100644 --- a/mysql-test/suite/handler/heap.result +++ b/mysql-test/suite/handler/heap.result @@ -362,7 +362,8 @@ prepare stmt from 'handler t1 read a=(?)'; flush tables; set @a=17; execute stmt using @a; -ERROR HY000: Prepared statement needs to be re-prepared +a b c +17 ddd NULL deallocate prepare stmt; handler t1 close; handler t1 open as t2; @@ -372,7 +373,7 @@ insert into t1 values (17); handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open as t2; -alter table t1 engine=CSV; +alter table t1 engine=csv; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER drop table t1; @@ -582,7 +583,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1; Warnings: Note 1051 Unknown table 't1' -create table t1 (a int not null) ENGINE=CSV; +create table t1 (a int not null) ENGINE=csv; --> client 2 handler t1 open; ERROR HY000: Table storage engine for 't1' doesn't have this option @@ -718,6 +719,969 @@ ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; # +# Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL. +# +drop table if exists t1, t2, t3; +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 (a int, key a (a)) select * from t1; +create temporary table t3 (a int, key a (a)) select * from t2; +handler t1 open; +handler t2 open; +handler t3 open; +# +# No HANDLER sql is allowed under LOCK TABLES. +# But it does not implicitly closes all handlers. +# +lock table t1 read; +handler t1 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t2 close; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t3 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# After UNLOCK TABLES handlers should be around and +# we should be able to continue reading through them. +unlock tables; +handler t1 read next; +a +1 +handler t1 close; +handler t2 read next; +a +1 +handler t2 close; +handler t3 read next; +a +1 +handler t3 close; +drop temporary table t3; +# +# Other operations that implicitly close handler: +# +# TRUNCATE +# +handler t1 open; +truncate table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER +# +create trigger t1_ai after insert on t1 for each row set @a=1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER +# +handler t1 open; +drop trigger t1_ai; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE +# +handler t1 open; +alter table t1 add column b int; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE +# +handler t1 open; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze note The storage engine for the table doesn't support analyze +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE +# +handler t1 open; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize note The storage engine for the table doesn't support optimize +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE +# +handler t1 open; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair note The storage engine for the table doesn't support repair +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE, naturally. +# +handler t1 open; +drop table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)) select a from t2; +# +# RENAME TABLE, naturally +# +handler t1 open; +rename table t1 to t3; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# CREATE TABLE (even with IF NOT EXISTS clause, +# and the table exists). +# +handler t2 open; +create table if not exists t2 (a int); +Warnings: +Note 1050 Table 't2' already exists +handler t2 read next; +ERROR 42S02: Unknown table 't2' in HANDLER +rename table t3 to t1; +drop table t2; +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush table t1; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +# +# FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE. +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush tables with read lock; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +unlock tables; +# +# Let us also check that these operations behave in similar +# way under LOCK TABLES. +# +# TRUNCATE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +truncate table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER under LOCK TABLES. +# +lock tables t1 write; +create trigger t1_ai after insert on t1 for each row set @a=1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +drop trigger t1_ai; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +alter table t1 drop column b; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze note The storage engine for the table doesn't support analyze +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize note The storage engine for the table doesn't support optimize +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair note The storage engine for the table doesn't support repair +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE under LOCK TABLES, naturally. +# +handler t1 open; +lock tables t1 write; +drop table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +a b +5 NULL +lock tables t1 write; +flush table t1; +unlock tables; +handler t1 read a prev; +a b +5 NULL +handler t1 close; +# +# Explore the effect of HANDLER locks on concurrent DDL +# +handler t1 open; +# Establishing auxiliary connections con1, con2, con3 +# --> connection con1; +# Sending: +drop table t1 ; +# We can't use connection 'default' as wait_condition will +# autoclose handlers. +# --> connection con2 +# Waitng for 'drop table t1' to get blocked... +# --> connection default +handler t1 read a prev; +a b +5 NULL +handler t1 read a prev; +a b +4 NULL +handler t1 close; +# --> connection con1 +# Reaping 'drop table t1'... +# --> connection default +# +# Explore the effect of HANDLER locks in parallel with SELECT +# +create table t1 (a int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 close; +# --> connection con1; +# Sending: +drop table t1 ; +# --> connection con2 +# Waiting for 'drop table t1' to get blocked... +# --> connection default +# We can still use the table, it's part of the transaction +select * from t1; +a +1 +2 +3 +4 +5 +# Such are the circumstances that t1 is a part of transaction, +# thus we can reopen it in the handler +handler t1 open; +# We can commit the transaction, it doesn't close the handler +# and doesn't let DROP to proceed. +commit; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 read a prev; +a +3 +handler t1 close; +# --> connection con1 +# Now drop can proceed +# Reaping 'drop table t1'... +# --> connection default +# +# Demonstrate that HANDLER locks and transaction locks +# reside in the same context, and we don't back-off +# when have transaction or handler locks. +# +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t0 (a int, key a using btree (a)); +insert into t0 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +# --> connection con2 +# Sending: +rename table t0 to t3, t1 to t0, t3 to t1; +# --> connection con1 +# Waiting for 'rename table ...' to get blocked... +# --> connection default +handler t0 open; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select * from t0; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +handler t1 open; +commit; +handler t1 close; +# --> connection con2 +# Reaping 'rename table ...'... +# --> connection default +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 close; +drop table t0; +# +# Originally there was a deadlock error in this test. +# With implementation of deadlock detector +# we no longer deadlock, but block and wait on a lock. +# The HANDLER is auto-closed as soon as the connection +# sees a pending conflicting lock against it. +# +create table t2 (a int, key a (a)); +handler t1 open; +# --> connection con1 +lock tables t2 read; +# --> connection con2 +# Sending 'drop table t2'... +drop table t2; +# --> connection con1 +# Waiting for 'drop table t2' to get blocked... +# --> connection default +# Sending 'select * from t2' +select * from t2; +# --> connection con1 +# Waiting for 'select * from t2' to get blocked... +unlock tables; +# --> connection con2 +# Reaping 'drop table t2'... +# --> connection default +# Reaping 'select * from t2' +ERROR 42S02: Table 'test.t2' doesn't exist +handler t1 close; +# +# ROLLBACK TO SAVEPOINT releases transactional locks, +# but has no effect on open HANDLERs +# +create table t2 like t1; +create table t3 like t1; +begin; +# Have something before the savepoint +select * from t3; +a +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +drop table t3; +# +# A few special cases when using SAVEPOINT/ROLLBACK TO +# SAVEPOINT and HANDLER. +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction doesn't release mdl lock on +# the HANDLER that was opened later. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +begin; +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction works properly (no valgrind warnins, etc), +# even though it's done after the HANDLER mdl lock that was there +# at the beginning is released and added again. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +create table t3 like t1; +insert into t3 (a) select a from t1; +begin; +handler t1 open; +savepoint sv; +handler t1 read a first; +a +1 +select * from t2; +a +handler t1 close; +handler t3 open; +handler t3 read a first; +a +1 +rollback to savepoint sv; +# --> connection con1 +drop table t1, t2; +# Sending: +drop table t3; +# Let DROP TABLE statement sync in. +# --> connection con2 +# Waiting for 'drop table t3' to get blocked... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t3 read a next; +a +2 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t3 close; +# connection con1 +# Reaping 'drop table t3'... +# --> connection default +commit; +# +# If we have to wait on an exclusive locks while having +# an open HANDLER, ER_LOCK_DEADLOCK is reported. +# +create table t1 (a int, key a(a)); +create table t2 like t1; +handler t1 open; +# --> connection con1 +lock table t1 write, t2 write; +# --> connection default +drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default +# Demonstrate that there is no deadlock with FLUSH TABLE, +# even though it is waiting for the other table to go away +create table t2 like t1; +# Sending: +flush table t2; +# --> connection con2 +drop table t1; +# --> connection con1 +unlock tables; +# --> connection default +# Reaping 'flush table t2'... +drop table t2; +# +# Bug #46224 HANDLER statements within a transaction might +# lead to deadlocks +# +create table t1 (a int, key using btree (a)); +insert into t1 values (1), (2); +# --> connection default +begin; +select * from t1; +a +1 +2 +handler t1 open; +# --> connection con1 +# Sending: +lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. +# --> connection default +# Sending: +handler t1 read a next; +# --> connection con1 +# Waiting for 'handler t1 read a next' to get blocked... +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. +drop table t1; +unlock tables; +# --> connection default +# Reaping 'handler t1 read a next'... +ERROR 42S02: Table 'test.t1' doesn't exist +handler t1 close; +# --> connection con1 +# --> connection con2 +# --> connection con3 +# +# A temporary table test. +# Check that we don't loose positions of HANDLER opened +# against a temporary table. +# +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create temporary table t2 (a int, b int, key using btree (a)); +insert into t2 (a) select a from t1; +handler t1 open; +handler t1 read a next; +a b +1 NULL +handler t2 open; +handler t2 read a next; +a b +1 NULL +flush table t1; +handler t2 read a next; +a b +2 NULL +# Sic: the position is lost +handler t1 read a next; +a b +1 NULL +select * from t1; +a b +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL +# Sic: the position is not lost +handler t2 read a next; +a b +3 NULL +select * from t2; +ERROR HY000: Can't reopen table: 't2' +handler t2 read a next; +a b +4 NULL +drop table t1; +drop temporary table t2; +# +# A test for lock_table_names()/unlock_table_names() function. +# It should work properly in presence of open HANDLER. +# +create table t1 (a int, b int, key a (a)); +create table t2 like t1; +create table t3 like t1; +create table t4 like t1; +handler t1 open; +handler t2 open; +rename table t4 to t5, t3 to t4, t5 to t3; +handler t1 read first; +a b +handler t2 read first; +a b +drop table t1, t2, t3, t4; +# +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +a b +7 7 +commit; +# -> connection con1 +# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +# -> connection default +handler t1 read a prev; +a b +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default +# +# A test for Bug#50555 "handler commands crash server in +# my_hash_first()". +# +handler no_such_table read no_such_index first; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +handler no_such_table close; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +# +# Bug#50907 Assertion `hash_tables->table->next == __null' on +# HANDLER OPEN +# +DROP TABLE IF EXISTS t1, t2; +CREATE TEMPORARY TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); +HANDLER t2 OPEN; +HANDLER t2 READ FIRST; +i +HANDLER t2 CLOSE; +DROP TABLE t1, t2; +# +# Bug#50912 Assertion `ticket->m_type >= mdl_request->type' +# failed on HANDLER + I_S +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (id INT); +HANDLER t1 OPEN; +SELECT table_name, table_comment FROM information_schema.tables +WHERE table_schema= 'test' AND table_name= 't1'; +table_name table_comment +t1 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# Test for bug #50908 "Assertion `handler_tables_hash.records == 0' +# failed in enter_locked_tables_mode". +# +drop tables if exists t1, t2; +drop function if exists f1; +create table t1 (i int); +insert into t1 values (1), (2); +create table t2 (j int); +insert into t2 values (1); +create function f1() returns int return (select count(*) from t2); +# Check that open HANDLER survives statement executed in +# prelocked mode. +handler t1 open; +handler t1 read next; +i +1 +# The below statement were aborted due to an assertion failure. +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +handler t1 close; +# Check that the same happens under GLOBAL READ LOCK. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +unlock tables; +handler t1 close; +# Now, check that the same happens if LOCK TABLES is executed. +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Finally, check scenario with GRL and LOCK TABLES. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +# This unlocks both tables and GRL. +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Clean-up. +drop function f1; +drop tables t1, t2; +# +# Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + +# HANDLER + LOCK + SP". +# Also see additional coverage for this bug in flush.test. +# +drop tables if exists t1, t2; +create table t1 (i int); +create temporary table t2 (j int); +handler t1 open; +lock table t2 read; +# This commit should not release any MDL locks. +commit; +unlock tables; +# The below statement crashed before the bug fix as it +# has attempted to release metadata lock which was +# already released by commit. +handler t1 close; +drop tables t1, t2; +# +# Bug#51355 handler stmt cause assertion in +# bool MDL_context::try_acquire_lock(MDL_request*) +# +DROP TABLE IF EXISTS t1; +# Connection default +CREATE TABLE t1(id INT, KEY id(id)); +HANDLER t1 OPEN; +# Connection con51355 +# Sending: +DROP TABLE t1; +# Connection default +# This I_S query will cause the handler table to be closed and +# the metadata lock to be released. This will allow DROP TABLE +# to proceed. Waiting for the table to be removed. +# Connection con51355 +# Reaping: DROP TABLE t1 +# Connection default +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 CLOSE; +# Connection con51355 +# Connection default +# +# Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +# +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +SELECT 1 FROM t2 INTO @a; +RETURN 1; +END| +SELECT f1(); +ERROR 42S02: Table 'test.t2' doesn't exist +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# +# Bug#54920 Stored functions are allowed in HANDLER statements, +# but broken. +# +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# # BUG#51877 - HANDLER interface causes invalid memory read # CREATE TABLE t1(a INT, KEY using btree (a)); diff --git a/mysql-test/suite/handler/heap.test b/mysql-test/suite/handler/heap.test index d6715144caa..bc070cf743f 100644 --- a/mysql-test/suite/handler/heap.test +++ b/mysql-test/suite/handler/heap.test @@ -2,7 +2,6 @@ # let $engine_type= MEMORY; -let $key_type=using btree; --source init.inc --source handler.inc diff --git a/mysql-test/suite/handler/init.inc b/mysql-test/suite/handler/init.inc index 3e63f14a103..ff5b2218ed1 100644 --- a/mysql-test/suite/handler/init.inc +++ b/mysql-test/suite/handler/init.inc @@ -2,10 +2,9 @@ # # Input variables # $engine_type -- storage engine to be tested -# $key_type -- set if you want a non standard key type +# using btree -- set if you want a non standard key type # # This scripts sets up default values for: -# $other_engine_type -- storage engine <> $engine_type # $other_handler_engine_type -- storage engine <> $engine_type, if possible # 1. $other_handler_engine_type must support handler # 2. $other_handler_engine_type must point to an all @@ -17,7 +16,6 @@ -- source include/not_embedded.inc eval SET SESSION STORAGE_ENGINE = $engine_type; -let $other_engine_type= CSV; let $other_handler_engine_type= MyISAM; --disable_warnings @@ -26,7 +24,7 @@ drop table if exists t1,t3,t4,t5; # Create default test table -eval create table t1 (a int, b char(10), key a $key_type (a), key b $key_type (a,b)); +create table t1 (a int, b char(10), key a using btree (a), key b using btree (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), diff --git a/mysql-test/suite/handler/innodb.result b/mysql-test/suite/handler/innodb.result index 3bf3a1f2c70..ab7b8dc8848 100644 --- a/mysql-test/suite/handler/innodb.result +++ b/mysql-test/suite/handler/innodb.result @@ -1,6 +1,6 @@ SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1,t3,t4,t5; -create table t1 (a int, b char(10), key a (a), key b (a,b)); +create table t1 (a int, b char(10), key a using btree (a), key b using btree (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), @@ -362,7 +362,8 @@ prepare stmt from 'handler t1 read a=(?)'; flush tables; set @a=17; execute stmt using @a; -ERROR HY000: Prepared statement needs to be re-prepared +a b c +17 ddd NULL deallocate prepare stmt; handler t1 close; handler t1 open as t2; @@ -372,7 +373,7 @@ insert into t1 values (17); handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open as t2; -alter table t1 engine=CSV; +alter table t1 engine=csv; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER drop table t1; @@ -395,7 +396,7 @@ handler t1 read first; a 6 drop table t1; -create table t1(a int, index (a)); +create table t1(a int, index using btree (a)); insert into t1 values (1), (2), (3); handler t1 open; handler t1 read a=(W); @@ -421,7 +422,7 @@ Ok handler t close; use test; drop table t1; -create table t1 ( a int, b int, INDEX a (a) ); +create table t1 ( a int, b int, INDEX a using btree (a) ); insert into t1 values (1,2), (2,1); handler t1 open; handler t1 read a=(1) where b=2; @@ -550,7 +551,7 @@ test.t1 optimize note Table does not support optimize, doing recreate + analyze test.t1 optimize status OK proceed with the normal connection drop table t1; -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)); +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY using btree (no1,no2)); INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); HANDLER t1 OPEN; HANDLER t1 READ `primary` = (1, 1000); @@ -583,7 +584,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1; Warnings: Note 1051 Unknown table 't1' -create table t1 (a int not null) ENGINE=CSV; +create table t1 (a int not null) ENGINE=csv; --> client 2 handler t1 open; ERROR HY000: Table storage engine for 't1' doesn't have this option @@ -601,7 +602,7 @@ handler t1_alias READ a next where inexistent > 0; ERROR 42S22: Unknown column 'inexistent' in 'field list' handler t1_alias close; drop table t1; -create temporary table t1 (a int, b char(1), key a (a), key b (a,b)); +create temporary table t1 (a int, b char(1), key a using btree (a), key b using btree (a,b)); insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"),(9,'k'); select a,b from t1; @@ -672,7 +673,7 @@ a b handler a2 close; drop table t1; create table t1 (a int); -create temporary table t2 (a int, key (a)); +create temporary table t2 (a int, key using btree (a)); handler t1 open as a1; handler t2 open as a2; handler a2 read a first; @@ -682,7 +683,7 @@ handler a2 read a next; ERROR 42S02: Unknown table 'a2' in HANDLER handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -695,7 +696,7 @@ handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); handler t1 open as a1; handler a1 read a first; a @@ -703,7 +704,7 @@ rename table t1 to t2; handler a1 read a first; ERROR 42S02: Unknown table 'a1' in HANDLER drop table t2; -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -720,9 +721,974 @@ ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; # +# Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL. +# +drop table if exists t1, t2, t3; +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 (a int, key a (a)) select * from t1; +create temporary table t3 (a int, key a (a)) select * from t2; +handler t1 open; +handler t2 open; +handler t3 open; +# +# No HANDLER sql is allowed under LOCK TABLES. +# But it does not implicitly closes all handlers. +# +lock table t1 read; +handler t1 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t2 close; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t3 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# After UNLOCK TABLES handlers should be around and +# we should be able to continue reading through them. +unlock tables; +handler t1 read next; +a +1 +handler t1 close; +handler t2 read next; +a +1 +handler t2 close; +handler t3 read next; +a +1 +handler t3 close; +drop temporary table t3; +# +# Other operations that implicitly close handler: +# +# TRUNCATE +# +handler t1 open; +truncate table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER +# +create trigger t1_ai after insert on t1 for each row set @a=1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER +# +handler t1 open; +drop trigger t1_ai; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE +# +handler t1 open; +alter table t1 add column b int; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE +# +handler t1 open; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE +# +handler t1 open; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE +# +handler t1 open; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair note The storage engine for the table doesn't support repair +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE, naturally. +# +handler t1 open; +drop table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)) select a from t2; +# +# RENAME TABLE, naturally +# +handler t1 open; +rename table t1 to t3; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# CREATE TABLE (even with IF NOT EXISTS clause, +# and the table exists). +# +handler t2 open; +create table if not exists t2 (a int); +Warnings: +Note 1050 Table 't2' already exists +handler t2 read next; +ERROR 42S02: Unknown table 't2' in HANDLER +rename table t3 to t1; +drop table t2; +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush table t1; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +# +# FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE. +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush tables with read lock; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +unlock tables; +# +# Let us also check that these operations behave in similar +# way under LOCK TABLES. +# +# TRUNCATE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +truncate table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER under LOCK TABLES. +# +lock tables t1 write; +create trigger t1_ai after insert on t1 for each row set @a=1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +drop trigger t1_ai; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +alter table t1 drop column b; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair note The storage engine for the table doesn't support repair +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE under LOCK TABLES, naturally. +# +handler t1 open; +lock tables t1 write; +drop table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +a b +5 NULL +lock tables t1 write; +flush table t1; +unlock tables; +handler t1 read a prev; +a b +5 NULL +handler t1 close; +# +# Explore the effect of HANDLER locks on concurrent DDL +# +handler t1 open; +# Establishing auxiliary connections con1, con2, con3 +# --> connection con1; +# Sending: +drop table t1 ; +# We can't use connection 'default' as wait_condition will +# autoclose handlers. +# --> connection con2 +# Waitng for 'drop table t1' to get blocked... +# --> connection default +handler t1 read a prev; +a b +5 NULL +handler t1 read a prev; +a b +4 NULL +handler t1 close; +# --> connection con1 +# Reaping 'drop table t1'... +# --> connection default +# +# Explore the effect of HANDLER locks in parallel with SELECT +# +create table t1 (a int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 close; +# --> connection con1; +# Sending: +drop table t1 ; +# --> connection con2 +# Waiting for 'drop table t1' to get blocked... +# --> connection default +# We can still use the table, it's part of the transaction +select * from t1; +a +1 +2 +3 +4 +5 +# Such are the circumstances that t1 is a part of transaction, +# thus we can reopen it in the handler +handler t1 open; +# We can commit the transaction, it doesn't close the handler +# and doesn't let DROP to proceed. +commit; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 read a prev; +a +3 +handler t1 close; +# --> connection con1 +# Now drop can proceed +# Reaping 'drop table t1'... +# --> connection default +# +# Demonstrate that HANDLER locks and transaction locks +# reside in the same context, and we don't back-off +# when have transaction or handler locks. +# +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t0 (a int, key a using btree (a)); +insert into t0 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +# --> connection con2 +# Sending: +rename table t0 to t3, t1 to t0, t3 to t1; +# --> connection con1 +# Waiting for 'rename table ...' to get blocked... +# --> connection default +handler t0 open; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select * from t0; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +handler t1 open; +commit; +handler t1 close; +# --> connection con2 +# Reaping 'rename table ...'... +# --> connection default +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 close; +drop table t0; +# +# Originally there was a deadlock error in this test. +# With implementation of deadlock detector +# we no longer deadlock, but block and wait on a lock. +# The HANDLER is auto-closed as soon as the connection +# sees a pending conflicting lock against it. +# +create table t2 (a int, key a (a)); +handler t1 open; +# --> connection con1 +lock tables t2 read; +# --> connection con2 +# Sending 'drop table t2'... +drop table t2; +# --> connection con1 +# Waiting for 'drop table t2' to get blocked... +# --> connection default +# Sending 'select * from t2' +select * from t2; +# --> connection con1 +# Waiting for 'select * from t2' to get blocked... +unlock tables; +# --> connection con2 +# Reaping 'drop table t2'... +# --> connection default +# Reaping 'select * from t2' +ERROR 42S02: Table 'test.t2' doesn't exist +handler t1 close; +# +# ROLLBACK TO SAVEPOINT releases transactional locks, +# but has no effect on open HANDLERs +# +create table t2 like t1; +create table t3 like t1; +begin; +# Have something before the savepoint +select * from t3; +a +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +drop table t3; +# +# A few special cases when using SAVEPOINT/ROLLBACK TO +# SAVEPOINT and HANDLER. +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction doesn't release mdl lock on +# the HANDLER that was opened later. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +begin; +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction works properly (no valgrind warnins, etc), +# even though it's done after the HANDLER mdl lock that was there +# at the beginning is released and added again. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +create table t3 like t1; +insert into t3 (a) select a from t1; +begin; +handler t1 open; +savepoint sv; +handler t1 read a first; +a +1 +select * from t2; +a +handler t1 close; +handler t3 open; +handler t3 read a first; +a +1 +rollback to savepoint sv; +# --> connection con1 +drop table t1, t2; +# Sending: +drop table t3; +# Let DROP TABLE statement sync in. +# --> connection con2 +# Waiting for 'drop table t3' to get blocked... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t3 read a next; +a +2 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t3 close; +# connection con1 +# Reaping 'drop table t3'... +# --> connection default +commit; +# +# If we have to wait on an exclusive locks while having +# an open HANDLER, ER_LOCK_DEADLOCK is reported. +# +create table t1 (a int, key a(a)); +create table t2 like t1; +handler t1 open; +# --> connection con1 +lock table t1 write, t2 write; +# --> connection default +drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default +# Demonstrate that there is no deadlock with FLUSH TABLE, +# even though it is waiting for the other table to go away +create table t2 like t1; +# Sending: +flush table t2; +# --> connection con2 +drop table t1; +# --> connection con1 +unlock tables; +# --> connection default +# Reaping 'flush table t2'... +drop table t2; +# +# Bug #46224 HANDLER statements within a transaction might +# lead to deadlocks +# +create table t1 (a int, key using btree (a)); +insert into t1 values (1), (2); +# --> connection default +begin; +select * from t1; +a +1 +2 +handler t1 open; +# --> connection con1 +# Sending: +lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. +# --> connection default +# Sending: +handler t1 read a next; +# --> connection con1 +# Waiting for 'handler t1 read a next' to get blocked... +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. +drop table t1; +unlock tables; +# --> connection default +# Reaping 'handler t1 read a next'... +ERROR 42S02: Table 'test.t1' doesn't exist +handler t1 close; +# --> connection con1 +# --> connection con2 +# --> connection con3 +# +# A temporary table test. +# Check that we don't loose positions of HANDLER opened +# against a temporary table. +# +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create temporary table t2 (a int, b int, key using btree (a)); +insert into t2 (a) select a from t1; +handler t1 open; +handler t1 read a next; +a b +1 NULL +handler t2 open; +handler t2 read a next; +a b +1 NULL +flush table t1; +handler t2 read a next; +a b +2 NULL +# Sic: the position is lost +handler t1 read a next; +a b +1 NULL +select * from t1; +a b +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL +# Sic: the position is not lost +handler t2 read a next; +a b +3 NULL +select * from t2; +ERROR HY000: Can't reopen table: 't2' +handler t2 read a next; +a b +4 NULL +drop table t1; +drop temporary table t2; +# +# A test for lock_table_names()/unlock_table_names() function. +# It should work properly in presence of open HANDLER. +# +create table t1 (a int, b int, key a (a)); +create table t2 like t1; +create table t3 like t1; +create table t4 like t1; +handler t1 open; +handler t2 open; +rename table t4 to t5, t3 to t4, t5 to t3; +handler t1 read first; +a b +handler t2 read first; +a b +drop table t1, t2, t3, t4; +# +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +a b +7 7 +commit; +# -> connection con1 +# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +# -> connection default +handler t1 read a prev; +a b +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default +# +# A test for Bug#50555 "handler commands crash server in +# my_hash_first()". +# +handler no_such_table read no_such_index first; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +handler no_such_table close; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +# +# Bug#50907 Assertion `hash_tables->table->next == __null' on +# HANDLER OPEN +# +DROP TABLE IF EXISTS t1, t2; +CREATE TEMPORARY TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); +HANDLER t2 OPEN; +HANDLER t2 READ FIRST; +i +HANDLER t2 CLOSE; +DROP TABLE t1, t2; +# +# Bug#50912 Assertion `ticket->m_type >= mdl_request->type' +# failed on HANDLER + I_S +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (id INT); +HANDLER t1 OPEN; +SELECT table_name, table_comment FROM information_schema.tables +WHERE table_schema= 'test' AND table_name= 't1'; +table_name table_comment +t1 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# Test for bug #50908 "Assertion `handler_tables_hash.records == 0' +# failed in enter_locked_tables_mode". +# +drop tables if exists t1, t2; +drop function if exists f1; +create table t1 (i int); +insert into t1 values (1), (2); +create table t2 (j int); +insert into t2 values (1); +create function f1() returns int return (select count(*) from t2); +# Check that open HANDLER survives statement executed in +# prelocked mode. +handler t1 open; +handler t1 read next; +i +1 +# The below statement were aborted due to an assertion failure. +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +handler t1 close; +# Check that the same happens under GLOBAL READ LOCK. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +unlock tables; +handler t1 close; +# Now, check that the same happens if LOCK TABLES is executed. +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Finally, check scenario with GRL and LOCK TABLES. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +# This unlocks both tables and GRL. +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Clean-up. +drop function f1; +drop tables t1, t2; +# +# Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + +# HANDLER + LOCK + SP". +# Also see additional coverage for this bug in flush.test. +# +drop tables if exists t1, t2; +create table t1 (i int); +create temporary table t2 (j int); +handler t1 open; +lock table t2 read; +# This commit should not release any MDL locks. +commit; +unlock tables; +# The below statement crashed before the bug fix as it +# has attempted to release metadata lock which was +# already released by commit. +handler t1 close; +drop tables t1, t2; +# +# Bug#51355 handler stmt cause assertion in +# bool MDL_context::try_acquire_lock(MDL_request*) +# +DROP TABLE IF EXISTS t1; +# Connection default +CREATE TABLE t1(id INT, KEY id(id)); +HANDLER t1 OPEN; +# Connection con51355 +# Sending: +DROP TABLE t1; +# Connection default +# This I_S query will cause the handler table to be closed and +# the metadata lock to be released. This will allow DROP TABLE +# to proceed. Waiting for the table to be removed. +# Connection con51355 +# Reaping: DROP TABLE t1 +# Connection default +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 CLOSE; +# Connection con51355 +# Connection default +# +# Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +# +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +SELECT 1 FROM t2 INTO @a; +RETURN 1; +END| +SELECT f1(); +ERROR 42S02: Table 'test.t2' doesn't exist +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# +# Bug#54920 Stored functions are allowed in HANDLER statements, +# but broken. +# +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# # BUG#51877 - HANDLER interface causes invalid memory read # -CREATE TABLE t1(a INT, KEY (a)); +CREATE TABLE t1(a INT, KEY using btree (a)); HANDLER t1 OPEN; HANDLER t1 READ a FIRST; a diff --git a/mysql-test/suite/handler/interface.result b/mysql-test/suite/handler/interface.result index 04fcb1b9002..923277914bd 100644 --- a/mysql-test/suite/handler/interface.result +++ b/mysql-test/suite/handler/interface.result @@ -2,7 +2,7 @@ drop table if exists t1,t3,t4,t5; drop database if exists test_test; SET SESSION STORAGE_ENGINE = MyISAM; drop table if exists t1,t3,t4,t5; -create table t1 (a int, b char(10), key a (a), key b (a,b)); +create table t1 (a int, b char(10), key a using btree (a), key b using btree (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), @@ -174,7 +174,8 @@ handler t1 read first; c1 1 connection: flush -flush tables;; +flush tables; +connection: waiter connection: default handler t2 open; handler t2 read first; @@ -186,6 +187,36 @@ c1 handler t1 close; handler t2 close; drop table t1,t2; +drop table if exists t1, t0; +create table t1 (c1 int); +connection: default +handler t1 open; +handler t1 read first; +c1 +connection: flush +rename table t1 to t0; +connection: waiter +connection: default +# +# RENAME placed two pending locks and waits. +# When HANDLER t0 OPEN does open_tables(), it calls +# mysql_ha_flush(), which in turn closes the open HANDLER for t1. +# RENAME TABLE gets unblocked. If it gets scheduled quickly +# and manages to complete before open_tables() +# of HANDLER t0 OPEN, open_tables() and therefore the whole +# HANDLER t0 OPEN succeeds. Otherwise open_tables() +# notices a pending or active exclusive metadata lock on t2 +# and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK +# error. +# +handler t0 open; +handler t0 close; +connection: flush +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 close; +ERROR 42S02: Unknown table 't1' in HANDLER +drop table t0; create table t1 (a int); handler t1 open as t1_alias; drop table t1; @@ -204,23 +235,7 @@ a drop table t1; handler t1_alias read next; ERROR 42S02: Unknown table 't1_alias' in HANDLER -create table t1 (c1 int); -connection: default -handler t1 open; -handler t1 read first; -c1 -connection: flush -rename table t1 to t2;; -connection: default -handler t2 open; -handler t2 read first; -c1 -handler t1 read next; -ERROR 42S02: Table 'test.t1' doesn't exist -handler t1 close; -handler t2 close; -drop table t2; -create table t1 (a int, b char(1), key a (a), key b (a,b)); +create table t1 (a int, b char(1), key a (a), key b (a,b)); insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); handler t1 open; @@ -246,16 +261,36 @@ drop table t1; handler t1 read a next; ERROR 42S02: Unknown table 't1' in HANDLER drop table if exists t1; +# First test case which is supposed trigger the execution +# path on which problem was discovered. create table t1 (a int not null); insert into t1 values (1); handler t1 open; +lock table t1 write; alter table t1 engine=csv; handler t1 read a next; ERROR HY000: Table storage engine for 't1' doesn't have this option handler t1 close; +unlock tables; +drop table t1; +# Now test case which was reported originally but which no longer +# triggers execution path which has caused the problem. +create table t1 (a int not null); +insert into t1 values (1); +handler t1 open; +alter table t1 engine=csv; +# Since S metadata lock was already acquired at HANDLER OPEN time +# and TL_READ lock requested by HANDLER READ is compatible with +# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed +# without waiting. The old version of table should be used in it. +handler t1 read next; +a +1 +handler t1 close; drop table t1; USE information_schema; HANDLER COLUMNS OPEN; ERROR HY000: Incorrect usage of HANDLER OPEN and information_schema +USE test; PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST'; ERROR 42S02: Unknown table 't1' in HANDLER diff --git a/mysql-test/suite/handler/interface.test b/mysql-test/suite/handler/interface.test index e03f2ed777c..e4e854b2515 100644 --- a/mysql-test/suite/handler/interface.test +++ b/mysql-test/suite/handler/interface.test @@ -181,13 +181,16 @@ handler t1 read first; connect (flush,localhost,root,,); connection flush; --echo connection: flush ---send flush tables; -connection default; ---echo connection: default +send flush tables; +connect (waiter,localhost,root,,); +connection waiter; +--echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table flush"; --source include/wait_condition.inc +connection default; +--echo connection: default handler t2 open; handler t2 read first; handler t1 read next; @@ -200,6 +203,63 @@ drop table t1,t2; disconnect flush; # +# Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements +# + +--disable_warnings +drop table if exists t1, t0; +--enable_warnings +create table t1 (c1 int); +--echo connection: default +handler t1 open; +handler t1 read first; +connect (flush,localhost,root,,); +connection flush; +--echo connection: flush +send rename table t1 to t0; +connection waiter; +--echo connection: waiter +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t0"; +--source include/wait_condition.inc +connection default; +--echo connection: default +--echo # +--echo # RENAME placed two pending locks and waits. +--echo # When HANDLER t0 OPEN does open_tables(), it calls +--echo # mysql_ha_flush(), which in turn closes the open HANDLER for t1. +--echo # RENAME TABLE gets unblocked. If it gets scheduled quickly +--echo # and manages to complete before open_tables() +--echo # of HANDLER t0 OPEN, open_tables() and therefore the whole +--echo # HANDLER t0 OPEN succeeds. Otherwise open_tables() +--echo # notices a pending or active exclusive metadata lock on t2 +--echo # and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK +--echo # error. +--echo # +--error 0, ER_LOCK_DEADLOCK +handler t0 open; +--error 0, ER_UNKNOWN_TABLE +handler t0 close; +--echo connection: flush +connection flush; +reap; +--error ER_UNKNOWN_TABLE +handler t1 read next; +--error ER_UNKNOWN_TABLE +handler t1 close; +connection default; +drop table t0; +connection flush; +disconnect flush; +--source include/wait_until_disconnected.inc +connection waiter; +disconnect waiter; +--source include/wait_until_disconnected.inc +connection default; + +# # Bug#31397 Inconsistent drop table behavior of handler tables. # @@ -221,40 +281,9 @@ drop table t1; --error ER_UNKNOWN_TABLE handler t1_alias read next; -# -# Bug#31409 RENAME TABLE causes server crash or deadlock when used with -# HANDLER statements -# - -create table t1 (c1 int); ---echo connection: default -handler t1 open; -handler t1 read first; -connect (flush,localhost,root,,); -connection flush; ---echo connection: flush ---send rename table t1 to t2; -connection default; ---echo 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 -handler t2 open; -handler t2 read first; ---error ER_NO_SUCH_TABLE -handler t1 read next; -handler t1 close; -handler t2 close; -connection flush; -reap; -connection default; -drop table t2; -disconnect flush; - # Flush tables causes handlers reopen -eval create table t1 (a int, b char(1), key a $key_type (a), key b $key_type (a,b)); +create table t1 (a int, b char(1), key a (a), key b (a,b)); insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); handler t1 open; @@ -275,39 +304,76 @@ 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 +--echo # First test case which is supposed trigger the execution +--echo # path on which problem was discovered. create table t1 (a int not null); insert into t1 values (1); handler t1 open; -connect(con1,localhost,root,,); +connection con1; +lock table t1 write; send alter table t1 engine=csv; -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=csv"; + where state = "Waiting for table metadata lock" and + info = "alter table t1 engine=csv"; --source include/wait_condition.inc +connection default; --error ER_ILLEGAL_HA handler t1 read a next; handler t1 close; connection con1; --reap +unlock tables; +drop table t1; +--echo # Now test case which was reported originally but which no longer +--echo # triggers execution path which has caused the problem. +connection default; +create table t1 (a int not null); +insert into t1 values (1); +handler t1 open; +connection con1; +send alter table t1 engine=csv; +connection con2; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 engine=csv"; +--source include/wait_condition.inc +connection default; +--echo # Since S metadata lock was already acquired at HANDLER OPEN time +--echo # and TL_READ lock requested by HANDLER READ is compatible with +--echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed +--echo # without waiting. The old version of table should be used in it. +handler t1 read next; +handler t1 close; +connection con1; +--reap # Since last in this connection was a send drop table t1; disconnect con1; --source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc connection default; # # Bug#44151 using handler commands on information_schema tables crashes server # - USE information_schema; --error ER_WRONG_USAGE HANDLER COLUMNS OPEN; +USE test; # -# LP#697622 Assertion `! is_set()' failed when preparing a HANDLER statement +# lp:697622 Assertion `! is_set()' failed when preparing a HANDLER statement # --error ER_UNKNOWN_TABLE PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST'; diff --git a/mysql-test/suite/handler/myisam.result b/mysql-test/suite/handler/myisam.result index 321811d7339..96e67038db5 100644 --- a/mysql-test/suite/handler/myisam.result +++ b/mysql-test/suite/handler/myisam.result @@ -1,6 +1,6 @@ SET SESSION STORAGE_ENGINE = MyISAM; drop table if exists t1,t3,t4,t5; -create table t1 (a int, b char(10), key a (a), key b (a,b)); +create table t1 (a int, b char(10), key a using btree (a), key b using btree (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), @@ -362,7 +362,8 @@ prepare stmt from 'handler t1 read a=(?)'; flush tables; set @a=17; execute stmt using @a; -ERROR HY000: Prepared statement needs to be re-prepared +a b c +17 ddd NULL deallocate prepare stmt; handler t1 close; handler t1 open as t2; @@ -372,7 +373,7 @@ insert into t1 values (17); handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open as t2; -alter table t1 engine=CSV; +alter table t1 engine=csv; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER drop table t1; @@ -395,7 +396,7 @@ handler t1 read first; a 6 drop table t1; -create table t1(a int, index (a)); +create table t1(a int, index using btree (a)); insert into t1 values (1), (2), (3); handler t1 open; handler t1 read a=(W); @@ -421,7 +422,7 @@ Ok handler t close; use test; drop table t1; -create table t1 ( a int, b int, INDEX a (a) ); +create table t1 ( a int, b int, INDEX a using btree (a) ); insert into t1 values (1,2), (2,1); handler t1 open; handler t1 read a=(1) where b=2; @@ -549,7 +550,7 @@ Table Op Msg_type Msg_text test.t1 optimize status OK proceed with the normal connection drop table t1; -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)); +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY using btree (no1,no2)); INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); HANDLER t1 OPEN; HANDLER t1 READ `primary` = (1, 1000); @@ -582,7 +583,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1; Warnings: Note 1051 Unknown table 't1' -create table t1 (a int not null) ENGINE=CSV; +create table t1 (a int not null) ENGINE=csv; --> client 2 handler t1 open; ERROR HY000: Table storage engine for 't1' doesn't have this option @@ -600,7 +601,7 @@ handler t1_alias READ a next where inexistent > 0; ERROR 42S22: Unknown column 'inexistent' in 'field list' handler t1_alias close; drop table t1; -create temporary table t1 (a int, b char(1), key a (a), key b (a,b)); +create temporary table t1 (a int, b char(1), key a using btree (a), key b using btree (a,b)); insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"),(9,'k'); select a,b from t1; @@ -671,7 +672,7 @@ a b handler a2 close; drop table t1; create table t1 (a int); -create temporary table t2 (a int, key (a)); +create temporary table t2 (a int, key using btree (a)); handler t1 open as a1; handler t2 open as a2; handler a2 read a first; @@ -681,7 +682,7 @@ handler a2 read a next; ERROR 42S02: Unknown table 'a2' in HANDLER handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -694,7 +695,7 @@ handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); handler t1 open as a1; handler a1 read a first; a @@ -702,7 +703,7 @@ rename table t1 to t2; handler a1 read a first; ERROR 42S02: Unknown table 'a1' in HANDLER drop table t2; -create table t1 (a int, key (a)); +create table t1 (a int, key using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; @@ -718,9 +719,972 @@ ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; # +# Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL. +# +drop table if exists t1, t2, t3; +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 (a int, key a (a)) select * from t1; +create temporary table t3 (a int, key a (a)) select * from t2; +handler t1 open; +handler t2 open; +handler t3 open; +# +# No HANDLER sql is allowed under LOCK TABLES. +# But it does not implicitly closes all handlers. +# +lock table t1 read; +handler t1 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t2 close; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +handler t3 open; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# After UNLOCK TABLES handlers should be around and +# we should be able to continue reading through them. +unlock tables; +handler t1 read next; +a +1 +handler t1 close; +handler t2 read next; +a +1 +handler t2 close; +handler t3 read next; +a +1 +handler t3 close; +drop temporary table t3; +# +# Other operations that implicitly close handler: +# +# TRUNCATE +# +handler t1 open; +truncate table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER +# +create trigger t1_ai after insert on t1 for each row set @a=1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER +# +handler t1 open; +drop trigger t1_ai; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE +# +handler t1 open; +alter table t1 add column b int; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE +# +handler t1 open; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE +# +handler t1 open; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE +# +handler t1 open; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE, naturally. +# +handler t1 open; +drop table t1; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)) select a from t2; +# +# RENAME TABLE, naturally +# +handler t1 open; +rename table t1 to t3; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# CREATE TABLE (even with IF NOT EXISTS clause, +# and the table exists). +# +handler t2 open; +create table if not exists t2 (a int); +Warnings: +Note 1050 Table 't2' already exists +handler t2 read next; +ERROR 42S02: Unknown table 't2' in HANDLER +rename table t3 to t1; +drop table t2; +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush table t1; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +# +# FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE. +# +handler t1 open; +handler t1 read a prev; +b a +NULL 5 +flush tables with read lock; +handler t1 read a prev; +b a +NULL 5 +handler t1 close; +unlock tables; +# +# Let us also check that these operations behave in similar +# way under LOCK TABLES. +# +# TRUNCATE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +truncate table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +handler t1 open; +# +# CREATE TRIGGER under LOCK TABLES. +# +lock tables t1 write; +create trigger t1_ai after insert on t1 for each row set @a=1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TRIGGER under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +drop trigger t1_ai; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ALTER TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +alter table t1 drop column b; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# ANALYZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# OPTIMIZE TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# REPAIR TABLE under LOCK TABLES. +# +handler t1 open; +lock tables t1 write; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +# +# DROP TABLE under LOCK TABLES, naturally. +# +handler t1 open; +lock tables t1 write; +drop table t1; +unlock tables; +handler t1 read next; +ERROR 42S02: Unknown table 't1' in HANDLER +create table t1 (a int, b int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +# +# FLUSH TABLE doesn't close the table but loses the position +# +handler t1 open; +handler t1 read a prev; +a b +5 NULL +lock tables t1 write; +flush table t1; +unlock tables; +handler t1 read a prev; +a b +5 NULL +handler t1 close; +# +# Explore the effect of HANDLER locks on concurrent DDL +# +handler t1 open; +# Establishing auxiliary connections con1, con2, con3 +# --> connection con1; +# Sending: +drop table t1 ; +# We can't use connection 'default' as wait_condition will +# autoclose handlers. +# --> connection con2 +# Waitng for 'drop table t1' to get blocked... +# --> connection default +handler t1 read a prev; +a b +5 NULL +handler t1 read a prev; +a b +4 NULL +handler t1 close; +# --> connection con1 +# Reaping 'drop table t1'... +# --> connection default +# +# Explore the effect of HANDLER locks in parallel with SELECT +# +create table t1 (a int, key a using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 close; +# --> connection con1; +# Sending: +drop table t1 ; +# --> connection con2 +# Waiting for 'drop table t1' to get blocked... +# --> connection default +# We can still use the table, it's part of the transaction +select * from t1; +a +1 +2 +3 +4 +5 +# Such are the circumstances that t1 is a part of transaction, +# thus we can reopen it in the handler +handler t1 open; +# We can commit the transaction, it doesn't close the handler +# and doesn't let DROP to proceed. +commit; +handler t1 read a prev; +a +5 +handler t1 read a prev; +a +4 +handler t1 read a prev; +a +3 +handler t1 close; +# --> connection con1 +# Now drop can proceed +# Reaping 'drop table t1'... +# --> connection default +# +# Demonstrate that HANDLER locks and transaction locks +# reside in the same context, and we don't back-off +# when have transaction or handler locks. +# +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t0 (a int, key a using btree (a)); +insert into t0 (a) values (1), (2), (3), (4), (5); +begin; +select * from t1; +a +1 +2 +3 +4 +5 +# --> connection con2 +# Sending: +rename table t0 to t3, t1 to t0, t3 to t1; +# --> connection con1 +# Waiting for 'rename table ...' to get blocked... +# --> connection default +handler t0 open; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +select * from t0; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +handler t1 open; +commit; +handler t1 close; +# --> connection con2 +# Reaping 'rename table ...'... +# --> connection default +handler t1 open; +handler t1 read a prev; +a +5 +handler t1 close; +drop table t0; +# +# Originally there was a deadlock error in this test. +# With implementation of deadlock detector +# we no longer deadlock, but block and wait on a lock. +# The HANDLER is auto-closed as soon as the connection +# sees a pending conflicting lock against it. +# +create table t2 (a int, key a (a)); +handler t1 open; +# --> connection con1 +lock tables t2 read; +# --> connection con2 +# Sending 'drop table t2'... +drop table t2; +# --> connection con1 +# Waiting for 'drop table t2' to get blocked... +# --> connection default +# Sending 'select * from t2' +select * from t2; +# --> connection con1 +# Waiting for 'select * from t2' to get blocked... +unlock tables; +# --> connection con2 +# Reaping 'drop table t2'... +# --> connection default +# Reaping 'select * from t2' +ERROR 42S02: Table 'test.t2' doesn't exist +handler t1 close; +# +# ROLLBACK TO SAVEPOINT releases transactional locks, +# but has no effect on open HANDLERs +# +create table t2 like t1; +create table t3 like t1; +begin; +# Have something before the savepoint +select * from t3; +a +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +drop table t3; +# +# A few special cases when using SAVEPOINT/ROLLBACK TO +# SAVEPOINT and HANDLER. +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction doesn't release mdl lock on +# the HANDLER that was opened later. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +begin; +savepoint sv; +handler t1 open; +handler t1 read a first; +a +1 +handler t1 read a next; +a +2 +select * from t2; +a +# --> connection con1 +# Sending: +drop table t1; +# --> connection con2 +# Sending: +drop table t2; +# --> connection default +# Let DROP TABLE statements sync in. We must use +# a separate connection for that, because otherwise SELECT +# will auto-close the HANDLERs, becaues there are pending +# exclusive locks against them. +# --> connection con3 +# Waiting for 'drop table t1' to get blocked... +# Waiting for 'drop table t2' to get blocked... +# Demonstrate that t2 lock was released and t2 was dropped +# after ROLLBACK TO SAVEPOINT +# --> connection default +rollback to savepoint sv; +# --> connection con2 +# Reaping 'drop table t2'... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t1 read a next; +a +3 +handler t1 read a next; +a +4 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t1 close; +# connection con1 +# Reaping 'drop table t1'... +# --> connection default +commit; +# +# Show that rollback to the savepoint taken in the beginning +# of the transaction works properly (no valgrind warnins, etc), +# even though it's done after the HANDLER mdl lock that was there +# at the beginning is released and added again. +# +create table t1 (a int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create table t2 like t1; +create table t3 like t1; +insert into t3 (a) select a from t1; +begin; +handler t1 open; +savepoint sv; +handler t1 read a first; +a +1 +select * from t2; +a +handler t1 close; +handler t3 open; +handler t3 read a first; +a +1 +rollback to savepoint sv; +# --> connection con1 +drop table t1, t2; +# Sending: +drop table t3; +# Let DROP TABLE statement sync in. +# --> connection con2 +# Waiting for 'drop table t3' to get blocked... +# Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +# lock. +# --> connection default +handler t3 read a next; +a +2 +# Demonstrate that the drop will go through as soon as we close the +# HANDLER +handler t3 close; +# connection con1 +# Reaping 'drop table t3'... +# --> connection default +commit; +# +# If we have to wait on an exclusive locks while having +# an open HANDLER, ER_LOCK_DEADLOCK is reported. +# +create table t1 (a int, key a(a)); +create table t2 like t1; +handler t1 open; +# --> connection con1 +lock table t1 write, t2 write; +# --> connection default +drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default +# Demonstrate that there is no deadlock with FLUSH TABLE, +# even though it is waiting for the other table to go away +create table t2 like t1; +# Sending: +flush table t2; +# --> connection con2 +drop table t1; +# --> connection con1 +unlock tables; +# --> connection default +# Reaping 'flush table t2'... +drop table t2; +# +# Bug #46224 HANDLER statements within a transaction might +# lead to deadlocks +# +create table t1 (a int, key using btree (a)); +insert into t1 values (1), (2); +# --> connection default +begin; +select * from t1; +a +1 +2 +handler t1 open; +# --> connection con1 +# Sending: +lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. +# --> connection default +# Sending: +handler t1 read a next; +# --> connection con1 +# Waiting for 'handler t1 read a next' to get blocked... +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. +drop table t1; +unlock tables; +# --> connection default +# Reaping 'handler t1 read a next'... +ERROR 42S02: Table 'test.t1' doesn't exist +handler t1 close; +# --> connection con1 +# --> connection con2 +# --> connection con3 +# +# A temporary table test. +# Check that we don't loose positions of HANDLER opened +# against a temporary table. +# +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a) values (1), (2), (3), (4), (5); +create temporary table t2 (a int, b int, key using btree (a)); +insert into t2 (a) select a from t1; +handler t1 open; +handler t1 read a next; +a b +1 NULL +handler t2 open; +handler t2 read a next; +a b +1 NULL +flush table t1; +handler t2 read a next; +a b +2 NULL +# Sic: the position is lost +handler t1 read a next; +a b +1 NULL +select * from t1; +a b +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL +# Sic: the position is not lost +handler t2 read a next; +a b +3 NULL +select * from t2; +ERROR HY000: Can't reopen table: 't2' +handler t2 read a next; +a b +4 NULL +drop table t1; +drop temporary table t2; +# +# A test for lock_table_names()/unlock_table_names() function. +# It should work properly in presence of open HANDLER. +# +create table t1 (a int, b int, key a (a)); +create table t2 like t1; +create table t3 like t1; +create table t4 like t1; +handler t1 open; +handler t2 open; +rename table t4 to t5, t3 to t4, t5 to t3; +handler t1 read first; +a b +handler t2 read first; +a b +drop table t1, t2, t3, t4; +# +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key using btree (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +a b +7 7 +commit; +# -> connection con1 +# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +# -> connection default +handler t1 read a prev; +a b +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default +# +# A test for Bug#50555 "handler commands crash server in +# my_hash_first()". +# +handler no_such_table read no_such_index first; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +handler no_such_table close; +ERROR 42S02: Unknown table 'no_such_table' in HANDLER +# +# Bug#50907 Assertion `hash_tables->table->next == __null' on +# HANDLER OPEN +# +DROP TABLE IF EXISTS t1, t2; +CREATE TEMPORARY TABLE t1 (i INT); +CREATE TEMPORARY TABLE t2 (i INT); +HANDLER t2 OPEN; +HANDLER t2 READ FIRST; +i +HANDLER t2 CLOSE; +DROP TABLE t1, t2; +# +# Bug#50912 Assertion `ticket->m_type >= mdl_request->type' +# failed on HANDLER + I_S +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (id INT); +HANDLER t1 OPEN; +SELECT table_name, table_comment FROM information_schema.tables +WHERE table_schema= 'test' AND table_name= 't1'; +table_name table_comment +t1 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# Test for bug #50908 "Assertion `handler_tables_hash.records == 0' +# failed in enter_locked_tables_mode". +# +drop tables if exists t1, t2; +drop function if exists f1; +create table t1 (i int); +insert into t1 values (1), (2); +create table t2 (j int); +insert into t2 values (1); +create function f1() returns int return (select count(*) from t2); +# Check that open HANDLER survives statement executed in +# prelocked mode. +handler t1 open; +handler t1 read next; +i +1 +# The below statement were aborted due to an assertion failure. +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +handler t1 close; +# Check that the same happens under GLOBAL READ LOCK. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +select f1() from t2; +f1() +1 +handler t1 read next; +i +2 +unlock tables; +handler t1 close; +# Now, check that the same happens if LOCK TABLES is executed. +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Finally, check scenario with GRL and LOCK TABLES. +flush tables with read lock; +handler t1 open; +handler t1 read next; +i +1 +lock table t2 read; +select * from t2; +j +1 +# This unlocks both tables and GRL. +unlock tables; +handler t1 read next; +i +2 +handler t1 close; +# Clean-up. +drop function f1; +drop tables t1, t2; +# +# Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + +# HANDLER + LOCK + SP". +# Also see additional coverage for this bug in flush.test. +# +drop tables if exists t1, t2; +create table t1 (i int); +create temporary table t2 (j int); +handler t1 open; +lock table t2 read; +# This commit should not release any MDL locks. +commit; +unlock tables; +# The below statement crashed before the bug fix as it +# has attempted to release metadata lock which was +# already released by commit. +handler t1 close; +drop tables t1, t2; +# +# Bug#51355 handler stmt cause assertion in +# bool MDL_context::try_acquire_lock(MDL_request*) +# +DROP TABLE IF EXISTS t1; +# Connection default +CREATE TABLE t1(id INT, KEY id(id)); +HANDLER t1 OPEN; +# Connection con51355 +# Sending: +DROP TABLE t1; +# Connection default +# This I_S query will cause the handler table to be closed and +# the metadata lock to be released. This will allow DROP TABLE +# to proceed. Waiting for the table to be removed. +# Connection con51355 +# Reaping: DROP TABLE t1 +# Connection default +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 READ id NEXT; +ERROR 42S02: Table 'test.t1' doesn't exist +HANDLER t1 CLOSE; +# Connection con51355 +# Connection default +# +# Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +# +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +SELECT 1 FROM t2 INTO @a; +RETURN 1; +END| +SELECT f1(); +ERROR 42S02: Table 'test.t2' doesn't exist +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# +# Bug#54920 Stored functions are allowed in HANDLER statements, +# but broken. +# +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: This version of MySQL doesn't yet support 'stored functions in HANDLER ... READ' +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# # BUG#51877 - HANDLER interface causes invalid memory read # -CREATE TABLE t1(a INT, KEY (a)); +CREATE TABLE t1(a INT, KEY using btree (a)); HANDLER t1 OPEN; HANDLER t1 READ a FIRST; a |