SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; drop table if exists t1,t3,t4,t5; 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"), (20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); handler t1 open as t2; handler t2 read b first; a b 14 aaa handler t2 read b next; a b 16 ccc handler t2 read b next; a b 16 xxx handler t2 read b prev; a b 16 ccc handler t2 read b last; a b 25 xxx handler t2 read b prev; a b 24 xxx handler t2 read b prev; a b 23 xxx handler t2 read b first; a b 14 aaa handler t2 read b prev; a b handler t2 read b last; a b 25 xxx handler t2 read b prev; a b 24 xxx handler t2 read b next; a b 25 xxx handler t2 read b next; a b handler t2 read a=(15); a b handler t2 read a=(21); a b 21 hhh handler t2 read a=(19,"fff"); ERROR 42000: Too many key parts specified; max 1 parts allowed handler t2 read b=(19,"fff"); a b 19 fff handler t2 read b=(19,"yyy"); a b 19 yyy handler t2 read b=(19); a b 19 fff handler t1 read a last; ERROR 42S02: Unknown table 't1' in HANDLER handler t2 read a=(11); a b handler t2 read a>=(11); a b 14 aaa handler t2 read b=(18); a b 18 eee handler t2 read b>=(18); a b 18 eee handler t2 read b>(18); a b 19 fff handler t2 read b<=(18); a b 18 eee handler t2 read b<(18); a b 17 ddd handler t2 read a=(15); a b handler t2 read a>=(15) limit 2; a b 16 ccc 16 xxx handler t2 read a>(15) limit 2; a b 16 ccc 16 xxx handler t2 read a<=(15); a b 14 aaa handler t2 read a<(15); a b 14 aaa handler t2 read a=(54); a b handler t2 read a>=(54); a b handler t2 read a>(54); a b handler t2 read a<=(54); a b 25 xxx handler t2 read a<(54); a b 25 xxx handler t2 read a=(1); a b handler t2 read a>=(1); a b 14 aaa handler t2 read a>(1); a b 14 aaa handler t2 read a<=(1); a b handler t2 read a<(1); a b handler t2 read b first limit 5; a b 14 aaa 16 ccc 16 xxx 17 ddd 18 eee handler t2 read b next limit 3; a b 19 fff 19 yyy 20 ggg handler t2 read b prev limit 10; a b 19 yyy 19 fff 18 eee 17 ddd 16 xxx 16 ccc 14 aaa handler t2 read b>=(16) limit 4; a b 16 ccc 16 xxx 17 ddd 18 eee handler t2 read b>=(16) limit 2,2; a b 17 ddd 18 eee select * from t1 where a>=16 order by a,b limit 2,2; a b 17 ddd 18 eee handler t2 read a last limit 3; a b 25 xxx 24 xxx 23 xxx handler t2 read b=(16) limit 1,3; a b 16 xxx handler t2 read b=(19); a b 19 fff handler t2 read b=(19) where b="yyy"; a b 19 yyy handler t2 read first; a b 17 ddd handler t2 read next; a b 18 eee handler t2 read next; a b 19 fff handler t2 close; handler t1 open; handler t1 read b next; a b 14 aaa handler t1 read b next; a b 16 ccc handler t1 close; handler t1 open; handler t1 read a prev; a b 25 xxx handler t1 read a prev; a b 24 xxx handler t1 close; handler t1 open as t2; handler t2 read first; a b 17 ddd alter table t1 engine = InnoDB; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open; handler t1 read a=(20) limit 1,3; a b flush tables; handler t1 read a=(20) limit 1,3; a b handler t1 close; handler t1 open; handler t1 read a=(25); a b 25 xxx handler t1 read a next; a b handler t1 read a next; a b handler t1 read a next; a b handler t1 read a prev; a b 25 xxx handler t1 read a=(1000); a b handler t1 read a next; a b handler t1 read a prev; a b 25 xxx handler t1 read a=(1000); a b handler t1 read a prev; a b 25 xxx handler t1 read a=(14); a b 14 aaa handler t1 read a prev; a b handler t1 read a prev; a b handler t1 read a next; a b 14 aaa handler t1 read a=(1); a b handler t1 read a prev; a b handler t1 read a next; a b 14 aaa handler t1 read a=(1); a b handler t1 read a next; a b 14 aaa handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; set @a=20,@b=1,@c=100; execute stmt using @a,@b,@c; a b set @a=20,@b=2,@c=1; execute stmt using @a,@b,@c; a b set @a=20,@b=0,@c=2; execute stmt using @a,@b,@c; a b 20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a next limit ?'; handler t1 read a>=(21); a b 21 hhh set @a=3; execute stmt using @a; a b 22 iii 23 xxx 24 xxx execute stmt using @a; a b 25 xxx execute stmt using @a; a b deallocate prepare stmt; prepare stmt from 'handler t1 read b prev limit ?'; execute stmt using @a; a b 25 xxx 24 xxx 23 xxx execute stmt using @a; a b 22 iii 21 hhh 20 ggg execute stmt using @a; a b 19 yyy 19 fff 18 eee execute stmt using @a; a b 17 ddd 16 xxx 16 ccc deallocate prepare stmt; prepare stmt from 'handler t1 read b=(?,?)'; set @a=14, @b='aaa'; execute stmt using @a,@b; a b 14 aaa set @a=14, @b='not found'; execute stmt using @a,@b; a b deallocate prepare stmt; prepare stmt from 'handler t1 read b=(1+?) limit 10'; set @a=15; execute stmt using @a; a b 16 ccc 16 xxx execute stmt using @a; a b 16 ccc 16 xxx deallocate prepare stmt; prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; set @a=17, @b=24; execute stmt using @a,@b; a b 17 ddd 18 eee 19 fff 19 yyy 20 ggg execute stmt using @a,@b; a b 17 ddd 18 eee 19 fff 19 yyy 20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a=(?)'; set @a=17; execute stmt using @a; a b 17 ddd alter table t1 add c int; execute stmt using @a; ERROR 42S02: Unknown table 't1' in HANDLER deallocate prepare stmt; handler t1 close; ERROR 42S02: Unknown table 't1' in HANDLER handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; set @a=17; execute stmt using @a; a b c 17 ddd NULL deallocate prepare stmt; handler t1 close; handler t1 open as t2; drop table t1; create table t1 (a int not null); 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; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3),(4),(5),(6); delete from t1 limit 2; handler t1 open; handler t1 read first; a 3 handler t1 read first limit 1,1; a 4 handler t1 read first limit 2,2; a 5 6 delete from t1 limit 3; handler t1 read first; a 6 drop table t1; create table t1(a int, index using btree (a)); insert into t1 values (1), (2), (3); handler t1 open; handler t1 read a=(W); ERROR 42S22: Unknown column 'W' in 'field list' handler t1 read a=(a); ERROR HY000: Incorrect arguments to HANDLER ... READ drop table t1; create table t1 (a char(5)); insert into t1 values ("Ok"); handler t1 open as t; handler t read first; a Ok use mysql; handler t read first; a Ok handler t close; handler test.t1 open as t; handler t read first; a Ok handler t close; use test; drop table t1; 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; a b 1 2 handler t1 read a=(1) where b=3; a b handler t1 read a=(1) where b=1; a b handler t1 close; drop table t1; create table t1 (c1 char(20)); insert into t1 values ("t1"); handler t1 open as h1; handler h1 read first limit 9; c1 t1 create table t2 (c1 char(20)); insert into t2 values ("t2"); handler t2 open as h2; handler h2 read first limit 9; c1 t2 create table t3 (c1 char(20)); insert into t3 values ("t3"); handler t3 open as h3; handler h3 read first limit 9; c1 t3 create table t4 (c1 char(20)); insert into t4 values ("t4"); handler t4 open as h4; handler h4 read first limit 9; c1 t4 create table t5 (c1 char(20)); insert into t5 values ("t5"); handler t5 open as h5; handler h5 read first limit 9; c1 t5 alter table t1 engine=MyISAM; handler h1 read first limit 9; ERROR 42S02: Unknown table 'h1' in HANDLER handler h2 read first limit 9; c1 t2 handler h3 read first limit 9; c1 t3 handler h4 read first limit 9; c1 t4 handler h5 read first limit 9; c1 t5 alter table t5 engine=MyISAM; handler h1 read first limit 9; ERROR 42S02: Unknown table 'h1' in HANDLER handler h2 read first limit 9; c1 t2 handler h3 read first limit 9; c1 t3 handler h4 read first limit 9; c1 t4 handler h5 read first limit 9; ERROR 42S02: Unknown table 'h5' in HANDLER alter table t3 engine=MyISAM; handler h1 read first limit 9; ERROR 42S02: Unknown table 'h1' in HANDLER handler h2 read first limit 9; c1 t2 handler h3 read first limit 9; ERROR 42S02: Unknown table 'h3' in HANDLER handler h4 read first limit 9; c1 t4 handler h5 read first limit 9; ERROR 42S02: Unknown table 'h5' in HANDLER handler h2 close; handler h4 close; handler t1 open as h1_1; handler t1 open as h1_2; handler t1 open as h1_3; handler h1_1 read first limit 9; c1 t1 handler h1_2 read first limit 9; c1 t1 handler h1_3 read first limit 9; c1 t1 alter table t1 engine=InnoDB; handler h1_1 read first limit 9; ERROR 42S02: Unknown table 'h1_1' in HANDLER handler h1_2 read first limit 9; ERROR 42S02: Unknown table 'h1_2' in HANDLER handler h1_3 read first limit 9; ERROR 42S02: Unknown table 'h1_3' in HANDLER drop table t1; drop table t2; drop table t3; drop table t4; drop table t5; create table t1 (c1 int); insert into t1 values (1); handler t1 open; handler t1 read first; c1 1 connect con2,localhost,root,,; connection con2; send the below to another connection, do not wait for the result optimize table t1; connection default; handler t1 read next; c1 handler t1 close; connection con2; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK connection default; drop table t1; 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); no1 no2 HANDLER t1 READ `primary` PREV; no1 no2 1 275 HANDLER t1 READ `primary` = (1, 1000); no1 no2 HANDLER t1 READ `primary` NEXT; no1 no2 2 6 DROP TABLE t1; create table t1 (c1 int); insert into t1 values (14397); flush tables with read lock; drop table t1; ERROR HY000: Can't execute the query because you have a conflicting read lock connection con2; drop table t1; connection default; select * from t1; c1 14397 unlock tables; connection con2; read the result from the other connection connection default; select * from t1; ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1; Warnings: Note 1051 Unknown table 'test.t1' create table t1 (a int not null) ENGINE=csv; connection con2; handler t1 open; ERROR HY000: Storage engine CSV of the table `test`.`t1` doesn't have this option connection default; drop table t1; disconnect con2; create table t1 (a int); handler t1 open as t1_alias; handler t1_alias read a next; ERROR 42000: Key 'a' doesn't exist in table 't1_alias' handler t1_alias READ a next where inexistent > 0; ERROR 42S22: Unknown column 'inexistent' in 'field list' handler t1_alias read a next; ERROR 42000: Key 'a' doesn't exist in table 't1_alias' 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 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; a b 0 a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 9 k handler t1 open as a1; handler a1 read a=(1); a b 1 b handler a1 read a next; a b 2 c handler a1 read a next; a b 3 d select a,b from t1; a b 0 a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 9 k handler a1 read a prev; a b 2 c handler a1 read a prev; a b 1 b handler a1 read a=(6) where b="g"; a b 6 g handler a1 close; select a,b from t1; a b 0 a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 9 k handler t1 open as a2; handler a2 read b=(9); a b 9 j handler a2 read b next; a b 9 k handler a2 read b prev limit 2; a b 9 j 8 i handler a2 read b last; a b 9 k handler a2 read b prev; a b 9 j handler a2 close; drop table t1; create table t1 (a int); 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; a drop table t1, t2; 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 using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; handler a1 read a first; a handler a2 read a first; a alter table t1 add b int; handler a1 close; ERROR 42S02: Unknown table 'a1' in HANDLER handler a2 close; drop table t1, t2; create table t1 (a int, key using btree (a)); handler t1 open as a1; handler a1 read a first; a 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 using btree (a)); create table t2 like t1; handler t1 open as a1; handler t2 open as a2; handler a1 read a first; a handler a2 read a first; a 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 a1 close; 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 Engine-independent statistics collected 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 Engine-independent statistics collected 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; connect con1, localhost, root,,; connect con2, localhost, root,,; connect con3, localhost, root,,; 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. # 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; # We back-off on hitting deadlock condition. handler t0 open; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction select * from t0; a 1 2 3 4 5 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; drop table t1; # # 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)); handler t1 open; connection con1; select get_lock('lock1', 10); get_lock('lock1', 10) 1 connection default; select get_lock('lock1', 10); connection con2; # Waiting for 'select get_lock('lock1', 10)' to get blocked... connection con1; drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction select release_lock('lock1'); release_lock('lock1') 1 connection default; get_lock('lock1', 10) 1 select release_lock('lock1'); release_lock('lock1') 1 # 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'. select * from t1; a 1 2 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; disconnect con1; connection con2; disconnect con2; connection con3; disconnect con3; connection default; # # 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; a b 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 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 2 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. # connect con1,localhost,root,,; 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 default; handler t1 read a prev; a b 6 6 handler t1 close; # Cleanup. drop table t1; connection con1; disconnect 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; connect con51355, localhost, root; 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; disconnect 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| Warnings: Warning 1287 ' INTO FROM...' instead 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 MariaDB 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 MariaDB 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)); HANDLER t1 OPEN; HANDLER t1 READ a FIRST; a INSERT INTO t1 VALUES(1); HANDLER t1 READ a NEXT; a HANDLER t1 CLOSE; DROP TABLE t1; # # Testing savepoints with handlers that supports it # connect con1, localhost, root,,; connect con2, localhost, root,,; connect con3, localhost, root,,; connection default; # # ROLLBACK TO SAVEPOINT releases transactional locks, # but has no effect on open HANDLERs # create table t1 (a int, key a (a)); insert into t1 (a) values (1), (2), (3), (4), (5); 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; # # Cleanup for savepoint.inc # connection con1; disconnect con1; connection con2; disconnect con2; connection con3; disconnect con3; connection default; CREATE TABLE t1 (f1 integer, f2 integer, primary key (f1), key (f2)) engine=innodb; INSERT INTO t1 VALUES (1,1),(2,2),(3,3); HANDLER t1 OPEN; HANDLER t1 READ FIRST WHERE f2 <= 1; f1 f2 1 1 HANDLER t1 READ `PRIMARY` PREV; f1 f2 3 3 DROP TABLE t1; # # 10.2 Test # # MDEV-20207: Assertion `! is_set()' failed in # Diagnostics_area::set_eof_status upon HANDLER READ # DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 'test.t1' CREATE TABLE t1 (a POINT, KEY(a)); HANDLER t1 OPEN h; HANDLER h READ a = (0); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field HANDLER h CLOSE; DROP TABLE t1; # End of 10.2 Test