Edit File: handler_innodb.result
SET SESSION DEFAULT_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)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), (20,"ggg"),(21,"hhh"),(22,"iii"); handler t1 open as t2; handler t2 read a=(SELECT 1); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1)' at line 1 handler t2 read a first; a b 14 aaa handler t2 read a next; a b 15 bbb handler t2 read a next; a b 16 ccc handler t2 read a prev; a b 15 bbb handler t2 read a last; a b 22 iii handler t2 read a prev; a b 21 hhh handler t2 read a prev; a b 20 ggg handler t2 read a first; a b 14 aaa handler t2 read a prev; a b handler t2 read a last; a b 22 iii handler t2 read a prev; a b 21 hhh handler t2 read a next; a b 22 iii handler t2 read a next; a b handler t2 read a=(15); a b 15 bbb handler t2 read a=(16); a b 16 ccc 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 a=(18); a b 18 eee handler t2 read a>=(18); a b 18 eee handler t2 read a>(18); a b 19 fff handler t2 read a<=(18); a b 18 eee handler t2 read a<(18); a b 17 ddd handler t2 read a first limit 5; a b 14 aaa 15 bbb 16 ccc 16 xxx 17 ddd handler t2 read a next limit 3; a b 18 eee 19 fff 19 yyy handler t2 read a prev limit 10; a b 19 fff 18 eee 17 ddd 16 xxx 16 ccc 15 bbb 14 aaa handler t2 read a>=(16) limit 4; a b 16 ccc 16 xxx 17 ddd 18 eee handler t2 read a>=(16) limit 2,2; a b 17 ddd 18 eee handler t2 read a last limit 3; a b 22 iii 21 hhh 20 ggg handler t2 read a=(19); a b 19 fff handler t2 read a=(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 read last; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 handler t2 close; handler t1 open; handler t1 read a next; a b 14 aaa handler t1 read a next; a b 15 bbb handler t1 close; handler t1 open; handler t1 read a prev; a b 22 iii handler t1 read a prev; a b 21 hhh 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 as t2; drop table t1; create table t1 (a int); 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=MEMORY; 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(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 (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; drop database if exists test_test; create database test_test; use test_test; create table t1(table_id char(20) primary key); insert into t1 values ('test_test.t1'); insert into t1 values (''); handler t1 open; handler t1 read first limit 9; table_id test_test.t1 create table t2(table_id char(20) primary key); insert into t2 values ('test_test.t2'); insert into t2 values (''); handler t2 open; handler t2 read first limit 9; table_id test_test.t2 use test; drop table if exists t1; create table t1(table_id char(20) primary key); insert into t1 values ('test.t1'); insert into t1 values (''); handler t1 open; ERROR 42000: Not unique table/alias: 't1' use test; handler test.t1 read first limit 9; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1 handler test_test.t1 read first limit 9; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1 handler t1 read first limit 9; table_id test_test.t1 handler test_test.t2 read first limit 9; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1 handler t2 read first limit 9; table_id test_test.t2 handler test_test.t1 close; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1 handler t1 close; drop table test_test.t1; handler test_test.t2 close; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1 handler t2 close; drop table test_test.t2; drop database test_test; use test; handler test.t1 close; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1 handler t1 close; ERROR 42S02: Unknown table 't1' in HANDLER drop table test.t1; drop database if exists test_test; drop table if exists t1; drop table if exists t2; drop table if exists t3; create database test_test; use test_test; create table t1 (c1 char(20)); insert into t1 values ('test_test.t1'); create table t3 (c1 char(20)); insert into t3 values ('test_test.t3'); handler t1 open; handler t1 read first limit 9; c1 test_test.t1 handler t1 open h1; handler h1 read first limit 9; c1 test_test.t1 use test; create table t1 (c1 char(20)); create table t2 (c1 char(20)); create table t3 (c1 char(20)); insert into t1 values ('t1'); insert into t2 values ('t2'); insert into t3 values ('t3'); handler t1 open; ERROR 42000: Not unique table/alias: 't1' handler t2 open t1; ERROR 42000: Not unique table/alias: 't1' handler t3 open t1; ERROR 42000: Not unique table/alias: 't1' handler t1 read first limit 9; c1 test_test.t1 handler test.t1 close; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1 handler test.t1 open h1; ERROR 42000: Not unique table/alias: 'h1' handler test_test.t1 open h1; ERROR 42000: Not unique table/alias: 'h1' handler test_test.t3 open h3; handler test.t1 open h2; handler t1 read first limit 9; c1 test_test.t1 handler h1 read first limit 9; c1 test_test.t1 handler h2 read first limit 9; c1 t1 handler h3 read first limit 9; c1 test_test.t3 handler h2 read first limit 9; c1 t1 handler test.h1 close; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close' at line 1 handler t1 close; handler h1 close; handler h2 close; handler t1 read first limit 9; ERROR 42S02: Unknown table 't1' in HANDLER handler h1 read first limit 9; ERROR 42S02: Unknown table 'h1' in HANDLER handler h2 read first limit 9; ERROR 42S02: Unknown table 'h2' in HANDLER handler h3 read first limit 9; c1 test_test.t3 handler h3 read first limit 9; c1 test_test.t3 use test_test; handler h3 read first limit 9; c1 test_test.t3 handler test.h3 read first limit 9; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read first limit 9' at line 1 handler h3 close; use test; drop table t3; drop table t2; drop table t1; drop database test_test; 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 send the below to another connection, do not wait for the result optimize table t1; proceed with the normal connection handler t1 read next; c1 1 handler t1 close; read the result from the other connection Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 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)); 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 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 send the below to another connection, do not wait for the result drop table t1; proceed with the normal connection select * from t1; c1 14397 unlock tables; read the result from the other connection proceed with the normal connection select * from t1; ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1; Warnings: Note 1051 Unknown table 'test.t1' drop table if exists t1; create table t1 (a int) ENGINE=MEMORY; --> client 2 handler t1 open; ERROR HY000: Table storage engine for 't1' doesn't have this option --> client 1 drop table t1; drop table if exists t1; 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; drop table if exists t1,t2; create table t1 (c1 int); create table t2 (c1 int); insert into t1 values (1); insert into t2 values (2); connection: default handler t1 open; handler t1 read first; c1 1 connection: flush flush tables;; connection: waiter connection: default handler t2 open; handler t2 read first; c1 2 handler t1 read next; c1 1 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; drop table if exists t1; create temporary 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"); 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 handler t1 open as a1; handler a1 read a first; a b 0 a handler a1 read a next; a b 1 b handler a1 read a next; a b 2 c select a,b from t1; ERROR HY000: Can't reopen table: 'a1' handler a1 read a prev; a b 1 b handler a1 read a prev; a b 0 a 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 handler t1 open as a2; handler a2 read a first; a b 0 a handler a2 read a last; a b 9 j handler a2 read a prev; a b 8 i handler a2 close; drop table t1; drop table if exists t1,t2; create table t1 (a int); handler t1 open as t1_alias; drop table t1; create table t1 (a int); handler t1 open as t1_alias; flush tables; drop table t1; create table t1 (a int); handler t1 open as t1_alias; handler t1_alias close; drop table t1; create table t1 (a int); handler t1 open as t1_alias; handler t1_alias read first; a drop table t1; handler t1_alias read next; ERROR 42S02: Unknown table 't1_alias' in HANDLER create table t1 (a int); create temporary table t2 (a int, key(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(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(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(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; 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; handler t1 read a first; a b 0 a handler t1 read a next; a b 1 b flush tables; handler t1 read a next; a b 0 a handler t1 read a next; a b 1 b flush tables with read lock; handler t1 read a next; a b 0 a unlock tables; 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); insert into t1 values (1); handler t1 open; lock table t1 write; alter table t1 engine=memory; 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, key(a)); insert into t1 values (1); handler t1 open; alter table t1 engine=memory; # 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 a 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; # # 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; create temporary table t4 like t3; 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 handler t4 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 tables t3, t4; # # 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 (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 (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 (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 (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; # # 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 a(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 a(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 a(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 a (a)); insert into t1 (a) values (1), (2), (3), (4), (5); create temporary table t2 (a int, b int, key a (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 a (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#13008220 HANDLER SQL STATEMENT CAN MISS TO INITIALIZE # FOR RANDOM READ # # A handler can only have one active 'cursor' at a time, # so switching between index and/or random should restart the cursor. CREATE TABLE t1(a INT, b INT, KEY b(b), KEY ab(a, b)); INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); HANDLER t1 OPEN; HANDLER t1 READ b FIRST; a b 1 10 HANDLER t1 READ NEXT; a b 2 20 HANDLER t1 READ NEXT; a b 1 10 HANDLER t1 READ b FIRST; a b 1 10 HANDLER t1 READ b NEXT; a b 2 20 HANDLER t1 READ b NEXT; a b 3 30 HANDLER t1 READ FIRST; a b 2 20 HANDLER t1 READ b FIRST; a b 1 10 HANDLER t1 READ NEXT; a b 2 20 HANDLER t1 READ NEXT; a b 1 10 HANDLER t1 READ NEXT; a b 4 40 HANDLER t1 READ NEXT; a b 3 30 HANDLER t1 READ NEXT; a b HANDLER t1 READ b NEXT; a b 1 10 HANDLER t1 READ b NEXT; a b 2 20 HANDLER t1 READ b NEXT; a b 3 30 HANDLER t1 READ b NEXT; a b 4 40 HANDLER t1 READ b NEXT; a b HANDLER t1 READ NEXT; a b 2 20 HANDLER t1 READ b NEXT; a b 1 10 HANDLER t1 READ FIRST; a b 2 20 HANDLER t1 READ b PREV; a b 4 40 HANDLER t1 READ b LAST; a b 4 40 HANDLER t1 READ NEXT; a b 2 20 HANDLER t1 READ ab FIRST; a b 1 10 HANDLER t1 READ b NEXT; a b 1 10 HANDLER t1 READ ab LAST; a b 4 40 HANDLER t1 READ b PREV; a b 4 40 HANDLER t1 CLOSE; DROP TABLE t1;