diff options
Diffstat (limited to 'mysql-test/suite/handler')
| -rw-r--r-- | mysql-test/suite/handler/aria.result | 1708 | ||||
| -rw-r--r-- | mysql-test/suite/handler/aria.test | 102 | ||||
| -rw-r--r-- | mysql-test/suite/handler/disconnect_4480.result | 7 | ||||
| -rw-r--r-- | mysql-test/suite/handler/disconnect_4480.test | 10 | ||||
| -rw-r--r-- | mysql-test/suite/handler/handler.inc | 1355 | ||||
| -rw-r--r-- | mysql-test/suite/handler/heap.result | 1706 | ||||
| -rw-r--r-- | mysql-test/suite/handler/heap.test | 105 | ||||
| -rw-r--r-- | mysql-test/suite/handler/init.inc | 31 | ||||
| -rw-r--r-- | mysql-test/suite/handler/innodb.result | 1790 | ||||
| -rw-r--r-- | mysql-test/suite/handler/innodb.test | 49 | ||||
| -rw-r--r-- | mysql-test/suite/handler/interface.result | 354 | ||||
| -rw-r--r-- | mysql-test/suite/handler/interface.test | 403 | ||||
| -rw-r--r-- | mysql-test/suite/handler/myisam.result | 1792 | ||||
| -rw-r--r-- | mysql-test/suite/handler/myisam.test | 191 | ||||
| -rw-r--r-- | mysql-test/suite/handler/ps.result | 9 | ||||
| -rw-r--r-- | mysql-test/suite/handler/ps.test | 13 | ||||
| -rw-r--r-- | mysql-test/suite/handler/savepoint.inc | 182 |
17 files changed, 9807 insertions, 0 deletions
diff --git a/mysql-test/suite/handler/aria.result b/mysql-test/suite/handler/aria.result new file mode 100644 index 00000000..b0a4a173 --- /dev/null +++ b/mysql-test/suite/handler/aria.result @@ -0,0 +1,1708 @@ +SET SESSION DEFAULT_STORAGE_ENGINE = Aria; +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 = Aria; +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=Aria; +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 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 status Table is already up to date +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 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 Engine-independent statistics collected +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; +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 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> 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 +1 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash +# +CREATE TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +DROP TABLE t1; +# +# Bug #54007: assert in ha_myisam::index_next , HANDLER +# +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a), KEY b(b), KEY ab(a, b)); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +HANDLER t1 READ `PRIMARY` NEXT; +a b +HANDLER t1 READ ab NEXT; +a b +HANDLER t1 READ b NEXT; +a b +HANDLER t1 READ NEXT; +a b +HANDLER t1 CLOSE; +INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ NEXT; +a b +1 10 +HANDLER t1 READ `PRIMARY` NEXT; +a b +1 10 +HANDLER t1 READ `PRIMARY` NEXT; +a b +2 20 +HANDLER t1 READ ab NEXT; +a b +1 10 +HANDLER t1 READ ab NEXT; +a b +2 20 +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 NEXT; +a b +1 10 +HANDLER t1 READ NEXT; +a b +4 40 +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ `PRIMARY` PREV; +a b +4 40 +HANDLER t1 READ `PRIMARY` PREV; +a b +3 30 +HANDLER t1 READ b PREV; +a b +4 40 +HANDLER t1 READ b PREV; +a b +3 30 +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ `PRIMARY` PREV LIMIT 3; +a b +4 40 +3 30 +2 20 +HANDLER t1 READ b NEXT LIMIT 5; +a b +1 10 +2 20 +3 30 +4 40 +HANDLER t1 CLOSE; +DROP TABLE t1; +End of 5.1 tests +# +# 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 diff --git a/mysql-test/suite/handler/aria.test b/mysql-test/suite/handler/aria.test new file mode 100644 index 00000000..912a9e89 --- /dev/null +++ b/mysql-test/suite/handler/aria.test @@ -0,0 +1,102 @@ +# t/handler_innodb.test +# +# test of HANDLER ... +# +# Last update: +# 2006-07-31 ML test refactored (MySQL 5.1) +# code of t/handler.test and t/innodb_handler.test united +# main testing code put into handler.inc +# rename t/innodb_handler.test to t/handler_innodb.test +# + +--source include/have_maria.inc +let $engine_type= Aria; + +--source init.inc +--source handler.inc + +--echo # +--echo # BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash +--echo # +CREATE TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; +DROP TABLE t1; + +--echo # +--echo # Bug #54007: assert in ha_myisam::index_next , HANDLER +--echo # +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a), KEY b(b), KEY ab(a, b)); + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 CLOSE; + +INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ NEXT; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 CLOSE; + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` PREV; +HANDLER t1 READ `PRIMARY` PREV; +HANDLER t1 READ b PREV; +HANDLER t1 READ b PREV; +HANDLER t1 CLOSE; + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` PREV LIMIT 3; +HANDLER t1 READ b NEXT LIMIT 5; +HANDLER t1 CLOSE; + +DROP TABLE t1; + +--echo End of 5.1 tests + +--echo # +--echo # 10.2 Test +--echo # +--echo # MDEV-20207: Assertion `! is_set()' failed in +--echo # Diagnostics_area::set_eof_status upon HANDLER READ +--echo # + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 (a POINT, KEY(a)); +HANDLER t1 OPEN h; + +--error ER_CANT_CREATE_GEOMETRY_OBJECT +HANDLER h READ a = (0); + +HANDLER h CLOSE; +DROP TABLE t1; + +--echo # End of 10.2 Test diff --git a/mysql-test/suite/handler/disconnect_4480.result b/mysql-test/suite/handler/disconnect_4480.result new file mode 100644 index 00000000..3e21a9ac --- /dev/null +++ b/mysql-test/suite/handler/disconnect_4480.result @@ -0,0 +1,7 @@ +connect con1,localhost,root,,; +create temporary table t1 as select 1; +handler t1 open; +handler t1 read next; +1 +1 +disconnect con1; diff --git a/mysql-test/suite/handler/disconnect_4480.test b/mysql-test/suite/handler/disconnect_4480.test new file mode 100644 index 00000000..507249bd --- /dev/null +++ b/mysql-test/suite/handler/disconnect_4480.test @@ -0,0 +1,10 @@ +# +# MDEV-4480 Assertion `inited == NONE' fails on closing a connection with open handler on temporary table +# + +--connect (con1,localhost,root,,) +create temporary table t1 as select 1; +handler t1 open; +handler t1 read next; +--disconnect con1 + diff --git a/mysql-test/suite/handler/handler.inc b/mysql-test/suite/handler/handler.inc new file mode 100644 index 00000000..9ad9e8d9 --- /dev/null +++ b/mysql-test/suite/handler/handler.inc @@ -0,0 +1,1355 @@ +# handler.inc +# +# See init.inc for setup of variables for this script +# +# The variables +# $engine_type -- storage engine to be tested +# $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 +# time available storage engine +# 2006-08 MySQL 5.1 MyISAM and MEMORY only +# +# test of HANDLER ... +# +# Last update: +# 2006-07-31 ML test refactored (MySQL 5.1) +# code of t/handler.test and t/innodb_handler.test united +# main testing code put into handler.inc +# + +source include/have_csv.inc; + +# +# Start testing the table created in init.inc +# +handler t1 open as t2; +handler t2 read b first; +handler t2 read b next; +handler t2 read b next; +handler t2 read b prev; +handler t2 read b last; +handler t2 read b prev; +handler t2 read b prev; + +handler t2 read b first; +handler t2 read b prev; + +handler t2 read b last; +handler t2 read b prev; +handler t2 read b next; +handler t2 read b next; + +handler t2 read a=(15); +handler t2 read a=(21); + +--error 1070 +handler t2 read a=(19,"fff"); + +handler t2 read b=(19,"fff"); +handler t2 read b=(19,"yyy"); +handler t2 read b=(19); + +--error 1109 +handler t1 read a last; + +handler t2 read a=(11); +handler t2 read a>=(11); + +# Search on something we can find +handler t2 read b=(18); +handler t2 read b>=(18); +handler t2 read b>(18); +handler t2 read b<=(18); +handler t2 read b<(18); + +# Search on something we can't find +--sorted_result +handler t2 read a=(15); +--sorted_result +handler t2 read a>=(15) limit 2; +--sorted_result +handler t2 read a>(15) limit 2; +handler t2 read a<=(15); +handler t2 read a<(15); + +# Search from upper end +handler t2 read a=(54); +handler t2 read a>=(54); +handler t2 read a>(54); +handler t2 read a<=(54); +handler t2 read a<(54); + +# Search from lower end +handler t2 read a=(1); +handler t2 read a>=(1); +handler t2 read a>(1); +handler t2 read a<=(1); +handler t2 read a<(1); + +handler t2 read b first limit 5; +handler t2 read b next limit 3; +handler t2 read b prev limit 10; + +handler t2 read b>=(16) limit 4; +handler t2 read b>=(16) limit 2,2; +select * from t1 where a>=16 order by a,b limit 2,2; +handler t2 read a last limit 3; +handler t2 read b=(16) limit 1,3; +handler t2 read b=(19); +handler t2 read b=(19) where b="yyy"; + +handler t2 read first; +handler t2 read next; +handler t2 read next; +handler t2 close; + +handler t1 open; +handler t1 read b next; # this used to crash as a bug#5373 +handler t1 read b next; +handler t1 close; + +handler t1 open; +handler t1 read a prev; # this used to crash as a bug#5373 +handler t1 read a prev; +handler t1 close; + +handler t1 open as t2; +handler t2 read first; +eval alter table t1 engine = $engine_type; +--error 1109 +handler t2 read first; + +handler t1 open; +handler t1 read a=(20) limit 1,3; +flush tables; +handler t1 read a=(20) limit 1,3; +handler t1 close; + +# +# Search after end and before start of index +# + +handler t1 open; +handler t1 read a=(25); +handler t1 read a next; +handler t1 read a next; +handler t1 read a next; +handler t1 read a prev; +handler t1 read a=(1000); +handler t1 read a next; +handler t1 read a prev; +handler t1 read a=(1000); +handler t1 read a prev; + +handler t1 read a=(14); +handler t1 read a prev; +handler t1 read a prev; +handler t1 read a next; +handler t1 read a=(1); +handler t1 read a prev; +handler t1 read a next; +handler t1 read a=(1); +handler t1 read a next; + +handler t1 close; + +# +# Test with prepared statements +# + +handler t1 open; +prepare stmt from 'handler t1 read a=(?) limit ?,?'; +set @a=20,@b=1,@c=100; +execute stmt using @a,@b,@c; +set @a=20,@b=2,@c=1; +execute stmt using @a,@b,@c; +set @a=20,@b=0,@c=2; +execute stmt using @a,@b,@c; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read a next limit ?'; +handler t1 read a>=(21); +set @a=3; +execute stmt using @a; +execute stmt using @a; +execute stmt using @a; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read b prev limit ?'; +execute stmt using @a; +execute stmt using @a; +execute stmt using @a; +execute stmt using @a; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read b=(?,?)'; +set @a=14, @b='aaa'; +execute stmt using @a,@b; +set @a=14, @b='not found'; +execute stmt using @a,@b; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read b=(1+?) limit 10'; +set @a=15; +execute stmt using @a; +execute stmt using @a; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; +set @a=17, @b=24; +execute stmt using @a,@b; +execute stmt using @a,@b; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read a=(?)'; +set @a=17; +execute stmt using @a; +alter table t1 add c int; +--error 1109 +execute stmt using @a; +deallocate prepare stmt; +--error 1109 +handler t1 close; + +handler t1 open; +prepare stmt from 'handler t1 read a=(?)'; +flush tables; +set @a=17; +execute stmt using @a; +deallocate prepare stmt; +handler t1 close; + +# +# DROP TABLE / ALTER TABLE +# +handler t1 open as t2; +drop table t1; +create table t1 (a int not null); +insert into t1 values (17); +--error 1109 +handler t2 read first; +handler t1 open as t2; +alter table t1 engine=csv; +--error 1109 +handler t2 read first; +drop table t1; + +# +# Test case for the bug #787 +# +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; +handler t1 read first limit 1,1; +handler t1 read first limit 2,2; +delete from t1 limit 3; +handler t1 read first; +drop table t1; + +# +# Test for #751 +# +create table t1(a int, index using btree (a)); +insert into t1 values (1), (2), (3); +handler t1 open; +--error 1054 +handler t1 read a=(W); +--error 1210 +handler t1 read a=(a); +drop table t1; +# +# BUG#2304 +# +create table t1 (a char(5)); +insert into t1 values ("Ok"); +handler t1 open as t; +handler t read first; +use mysql; +handler t read first; +handler t close; +handler test.t1 open as t; +handler t read first; +handler t close; +use test; +drop table t1; + +# +# BUG#3649 +# +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; +handler t1 read a=(1) where b=3; +handler t1 read a=(1) where b=1; +handler t1 close; +drop table t1; + +# +# Test if fix for BUG#4286 correctly closes handler tables. +# +create table t1 (c1 char(20)); +insert into t1 values ("t1"); +handler t1 open as h1; +handler h1 read first limit 9; +create table t2 (c1 char(20)); +insert into t2 values ("t2"); +handler t2 open as h2; +handler h2 read first limit 9; +create table t3 (c1 char(20)); +insert into t3 values ("t3"); +handler t3 open as h3; +handler h3 read first limit 9; +create table t4 (c1 char(20)); +insert into t4 values ("t4"); +handler t4 open as h4; +handler h4 read first limit 9; +create table t5 (c1 char(20)); +insert into t5 values ("t5"); +handler t5 open as h5; +handler h5 read first limit 9; +# close first +eval alter table t1 engine=$other_handler_engine_type; +--error 1109 +handler h1 read first limit 9; +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h4 read first limit 9; +handler h5 read first limit 9; +# close last +eval alter table t5 engine=$other_handler_engine_type; +--error 1109 +handler h1 read first limit 9; +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h4 read first limit 9; +--error 1109 +handler h5 read first limit 9; +# close middle +eval alter table t3 engine=$other_handler_engine_type; +--error 1109 +handler h1 read first limit 9; +handler h2 read first limit 9; +--error 1109 +handler h3 read first limit 9; +handler h4 read first limit 9; +--error 1109 +handler h5 read first limit 9; +handler h2 close; +handler h4 close; +# close all depending handler tables +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; +handler h1_2 read first limit 9; +handler h1_3 read first limit 9; +eval alter table t1 engine=$engine_type; +--error 1109 +handler h1_1 read first limit 9; +--error 1109 +handler h1_2 read first limit 9; +--error 1109 +handler h1_3 read first limit 9; +drop table t1; +drop table t2; +drop table t3; +drop table t4; +drop table t5; + +# +# Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash +# +create table t1 (c1 int); +insert into t1 values (1); +handler t1 open; +handler t1 read first; +connect (con2,localhost,root,,); +connection con2; +--echo send the below to another connection, do not wait for the result +send optimize table t1; +--sleep 1 +connection default; +--disable_ps_protocol +handler t1 read next; +--enable_ps_protocol +handler t1 close; +connection con2; +reap; +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); +HANDLER t1 READ `primary` PREV; +HANDLER t1 READ `primary` = (1, 1000); +HANDLER t1 READ `primary` NEXT; +DROP TABLE t1; + +# End of 4.1 tests + +# +# Addendum to Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash +# Show that DROP TABLE can no longer deadlock against +# FLUSH TABLES WITH READ LOCK. This is a 5.0 issue. +# +create table t1 (c1 int); +insert into t1 values (14397); +flush tables with read lock; +# The thread with the global read lock cannot drop the table itself: +--error 1223 +drop table t1; +# +# We need a second connection to try the drop. +# 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; +send drop table t1; +--sleep 1 +# +# Now we need something that wants LOCK_open. A simple table access which +# opens the table does the trick. +connection default; +# This would hang on LOCK_open without the 5.0 addendum fix. +select * from t1; +# Release the read lock. This should make the DROP go through. +unlock tables; +# +# Read the result of the drop command. +connection con2; +--echo read the result from the other connection +reap; +# +# Now back to normal operation. The table should not exist any more. +connection default; +--error 1146 +select * from t1; +# Just to be sure and not confuse the next test case writer. +drop table if exists t1; + +# +# Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one +# +create table t1 (a int not null) ENGINE=csv; +connection con2; +--error 1031 +handler t1 open; +connection default; +drop table t1; +disconnect con2; + +# +# Bug#30632 HANDLER read failure causes hang +# +create table t1 (a int); +handler t1 open as t1_alias; +--error 1176 +handler t1_alias read a next; +--error 1054 +handler t1_alias READ a next where inexistent > 0; +--error 1176 +handler t1_alias read a next; +--error 1054 +handler t1_alias READ a next where inexistent > 0; +handler t1_alias close; +drop table t1; + +# +# Bug#30882 Dropping a temporary table inside a stored function may cause a server crash +# + +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; +handler t1 open as a1; +handler a1 read a=(1); +handler a1 read a next; +handler a1 read a next; +select a,b from t1; +handler a1 read a prev; +handler a1 read a prev; +handler a1 read a=(6) where b="g"; +handler a1 close; +select a,b from t1; +handler t1 open as a2; +handler a2 read b=(9); +handler a2 read b next; +handler a2 read b prev limit 2; +--error 0,1031 +handler a2 read b last; +handler a2 read b prev; +handler a2 close; +drop table t1; + +# Test that temporary tables associated with handlers are properly dropped. + +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; +drop table t1, t2; +--error ER_UNKNOWN_TABLE +handler a2 read a next; +--error ER_UNKNOWN_TABLE +handler a1 close; + +# Alter table drop handlers + +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; +handler a2 read a first; +alter table t1 add b int; +--error ER_UNKNOWN_TABLE +handler a1 close; +handler a2 close; +drop table t1, t2; + +# Rename table drop handlers + +create table t1 (a int, key using btree (a)); +handler t1 open as a1; +handler a1 read a first; +rename table t1 to t2; +--error ER_UNKNOWN_TABLE +handler a1 read a first; +drop table t2; + +# Optimize table drop handlers + +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; +handler a2 read a first; +optimize table t1; +--error ER_UNKNOWN_TABLE +handler a1 close; +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; +connect(con1, localhost, root,,); +connect(con2, localhost, root,,); +connect(con3, localhost, root,,); +connection con1; +--echo # Sending: +--send drop table t1 +--echo # We can't use connection 'default' as wait_condition will +--echo # autoclose handlers. +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 +connection default; +handler t1 read a prev; +handler t1 read a prev; +handler t1 close; +connection con1; +--echo # Reaping 'drop table t1'... +--reap +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; +connection con1; +--echo # Sending: +--send drop table t1 +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 +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; +connection con1; +--echo # Now drop can proceed +--echo # Reaping 'drop table t1'... +--reap +connection default; +--echo # +--echo # Demonstrate that HANDLER locks and transaction locks +--echo # reside in the same context. +--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; +connection con2; +--echo # Sending: +send rename table t0 to t3, t1 to t0, t3 to t1; +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 +connection default; +--echo # We back-off on hitting deadlock condition. +--error ER_LOCK_DEADLOCK +handler t0 open; +select * from t0; +handler t1 open; +commit; +handler t1 close; +connection con2; +--echo # Reaping 'rename table ...'... +--reap +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; +connection con1; +lock tables t2 read; +connection con2; +--echo # Sending 'drop table t2'... +--send drop table t2 +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 +connection default; +--echo # Sending 'select * from t2' +send select * from t2; +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; +connection con2; +--echo # Reaping 'drop table t2'... +--reap +connection default; +--echo # Reaping 'select * from t2' +--error ER_NO_SUCH_TABLE +reap; +handler t1 close; +drop table t1; + +--echo # +--echo # If we have to wait on an exclusive locks while having +--echo # an open HANDLER, ER_LOCK_DEADLOCK is reported. +--echo # +--disable_ps2_protocol +create table t1 (a int, key a(a)); +handler t1 open; +connection con1; +select get_lock('lock1', 10); +connection default; +send select get_lock('lock1', 10); +connection con2; +--echo # Waiting for 'select get_lock('lock1', 10)' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='User lock' and + info='select get_lock(\'lock1\', 10)'; +--source include/wait_condition.inc +connection con1; +--error ER_LOCK_DEADLOCK +drop table t1; +select release_lock('lock1'); +connection default; +reap; +select release_lock('lock1'); +--enable_ps2_protocol + +--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 +connection con2; +drop table t1; +connection con1; +unlock tables; +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); + +connection default; +begin; +select * from t1; +handler t1 open; + +connection con1; +--echo # Sending: +--send lock tables t1 write + +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 + +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'. +select * from t1; # Release MDL_SHARED_READ held by HANDLER +commit; + +connection con1; +--echo # Reap 'lock tables t1 write'. +--reap + +connection default; +--echo # Sending: +--send handler t1 read a next + +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 metadata 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; + +connection default; +--echo # Reaping 'handler t1 read a next'... +--error ER_NO_SUCH_TABLE +--reap +handler t1 close; + +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +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; +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 # +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; +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +commit; +connection default; +handler t1 read a prev; +handler t1 close; +--echo # Cleanup. +drop table t1; +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +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); + +connection default; +CREATE TABLE t1(id INT, KEY id(id)); +HANDLER t1 OPEN; + +connection con51355; +--echo # Sending: +--send DROP TABLE t1 + +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 + +connection con51355; +--echo # Reaping: DROP TABLE t1 +--reap + +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; +connection con51355; +disconnect con51355; +--source include/wait_until_disconnected.inc +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 + +--enable_prepare_warnings +delimiter |; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN + SELECT 1 FROM t2 INTO @a; + RETURN 1; +END| +delimiter ;| +--disable_prepare_warnings + +# 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 # +CREATE TABLE t1(a INT, KEY using btree (a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +INSERT INTO t1 VALUES(1); +--error 0,ER_CHECKREAD +HANDLER t1 READ a NEXT; +HANDLER t1 CLOSE; +DROP TABLE t1; diff --git a/mysql-test/suite/handler/heap.result b/mysql-test/suite/handler/heap.result new file mode 100644 index 00000000..e66bccb1 --- /dev/null +++ b/mysql-test/suite/handler/heap.result @@ -0,0 +1,1706 @@ +SET SESSION DEFAULT_STORAGE_ENGINE = MEMORY; +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 = MEMORY; +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=MEMORY; +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 The storage engine for the table doesn't support optimize +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 The storage engine for the table doesn't support optimize +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 Operation failed +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 status Engine-independent statistics collected +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; +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 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> 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; +HANDLER t1 CLOSE; +DROP TABLE t1; +connect con1,localhost,root,,; +connection default; +CREATE TABLE t1(a INT, b INT, KEY(a), KEY b using btree (b), KEY ab using btree(a, b)) engine=memory; +INSERT INTO t1 VALUES (2, 20), (2,20), (1, 10), (4, 40), (3, 30), (5,50), (6,50); +HANDLER t1 OPEN; +HANDLER t1 READ a=(2) limit 3; +a b +2 20 +2 20 +HANDLER t1 READ a PREV; +a b +2 20 +HANDLER t1 READ a PREV; +a b +2 20 +HANDLER t1 READ a PREV; +a b +HANDLER t1 READ b>=(20) limit 3; +a b +2 20 +2 20 +3 30 +HANDLER t1 READ b PREV; +a b +2 20 +HANDLER t1 READ b PREV LIMIT 2; +a b +2 20 +1 10 +HANDLER t1 READ ab=(3,30) limit 3; +a b +3 30 +HANDLER t1 READ ab>=(3,30) limit 3; +a b +3 30 +4 40 +5 50 +HANDLER t1 READ a FIRST; +ERROR HY000: Storage engine MEMORY of the table `test`.`t1` doesn't have this option +HANDLER t1 READ a LAST; +ERROR HY000: Storage engine MEMORY of the table `test`.`t1` doesn't have this option +HANDLER t1 READ b FIRST LIMIT 2; +a b +1 10 +2 20 +HANDLER t1 READ ab LAST LIMIT 2; +a b +6 50 +5 50 +HANDLER t1 READ FIRST LIMIT 10; +a b +2 20 +2 20 +1 10 +4 40 +3 30 +5 50 +6 50 +HANDLER t1 READ b FIRST; +a b +1 10 +insert into t1 values (7,50); +HANDLER t1 READ b NEXT; +ERROR HY000: Record has changed since last read in table 't1' +HANDLER t1 READ b FIRST; +a b +1 10 +connection con1; +insert into t1 values (7,50); +connection default; +HANDLER t1 READ b NEXT; +ERROR HY000: Record has changed since last read in table 't1' +HANDLER t1 READ FIRST; +a b +2 20 +connection con1; +insert into t1 values (8,50); +connection default; +HANDLER t1 READ NEXT; +a b +2 20 +connection con1; +delete from t1 where a=3; +connection default; +HANDLER t1 READ NEXT LIMIT 2; +a b +1 10 +4 40 +connection con1; +delete from t1; +connection default; +HANDLER t1 READ NEXT LIMIT 2; +ERROR HY000: Record has changed since last read in table 't1' +HANDLER t1 CLOSE; +DROP TABLE t1; +disconnect con1; +create table t1 (f1 integer not null, key (f1)) engine=Memory; +insert into t1 values (1); +HANDLER t1 OPEN; +HANDLER t1 READ f1 NEXT; +ERROR HY000: Storage engine MEMORY of the table `test`.`t1` doesn't have this option +HANDLER t1 READ f1 NEXT; +ERROR HY000: Storage engine MEMORY of the table `test`.`t1` doesn't have this option +HANDLER t1 READ f1 NEXT; +ERROR HY000: Storage engine MEMORY of the table `test`.`t1` doesn't have this option +HANDLER t1 CLOSE; +DROP TABLE t1; +End of 5.3 tests +# +# MDEV-15813 ASAN use-after-poison in hp_hashnr upon +# HANDLER READ on a versioned HEAP table +# +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, CONSTRAINT PRIMARY KEY (a, b), UNIQUE ba(b, a) USING HASH) ENGINE=HEAP; +INSERT INTO t1 VALUES (1, 10), (2, 20), (3,30), (4,40); +HANDLER t1 OPEN AS m; +HANDLER m READ `PRIMARY`= (3,30); +a b +3 30 +HANDLER m READ `PRIMARY`> (3,30); +ERROR HY000: HASH index `PRIMARY` does not support this operation +HANDLER m READ `ba`= (30,3); +a b +3 30 +HANDLER m READ `ba`= (30); +ERROR HY000: HASH index `ba` does not support this operation +HANDLER m CLOSE; +DROP TABLE t1; diff --git a/mysql-test/suite/handler/heap.test b/mysql-test/suite/handler/heap.test new file mode 100644 index 00000000..d60f92da --- /dev/null +++ b/mysql-test/suite/handler/heap.test @@ -0,0 +1,105 @@ +# test of HANDLER with HEAP tables +# + +let $engine_type= MEMORY; + +--source init.inc +--source handler.inc + +# +# Test what happens if table is changed (Unique test for HEAP) +# + +connect (con1,localhost,root,,); +connection default; + +CREATE TABLE t1(a INT, b INT, KEY(a), KEY b using btree (b), KEY ab using btree(a, b)) engine=memory; + +INSERT INTO t1 VALUES (2, 20), (2,20), (1, 10), (4, 40), (3, 30), (5,50), (6,50); + +HANDLER t1 OPEN; +HANDLER t1 READ a=(2) limit 3; +HANDLER t1 READ a PREV; +HANDLER t1 READ a PREV; +HANDLER t1 READ a PREV; +HANDLER t1 READ b>=(20) limit 3; +HANDLER t1 READ b PREV; +HANDLER t1 READ b PREV LIMIT 2; +HANDLER t1 READ ab=(3,30) limit 3; +HANDLER t1 READ ab>=(3,30) limit 3; + +# Test FIRST/LAST on hash and btree keys +--error ER_ILLEGAL_HA +HANDLER t1 READ a FIRST; +--error ER_ILLEGAL_HA +HANDLER t1 READ a LAST; +HANDLER t1 READ b FIRST LIMIT 2; +HANDLER t1 READ ab LAST LIMIT 2; + +# Table scan +HANDLER t1 READ FIRST LIMIT 10; +# Index scan +HANDLER t1 READ b FIRST; +insert into t1 values (7,50); +--error ER_CHECKREAD +HANDLER t1 READ b NEXT; + +HANDLER t1 READ b FIRST; +connection con1; +insert into t1 values (7,50); +connection default; +--error ER_CHECKREAD +HANDLER t1 READ b NEXT; + +HANDLER t1 READ FIRST; +connection con1; +insert into t1 values (8,50); +connection default; +HANDLER t1 READ NEXT; +connection con1; +delete from t1 where a=3; +connection default; +HANDLER t1 READ NEXT LIMIT 2; +connection con1; +delete from t1; +connection default; +--error ER_CHECKREAD +HANDLER t1 READ NEXT LIMIT 2; +HANDLER t1 CLOSE; +DROP TABLE t1; +disconnect con1; + +# +# LP#702786 Two handler read f1 next gives different errors +# +create table t1 (f1 integer not null, key (f1)) engine=Memory; +insert into t1 values (1); +HANDLER t1 OPEN; +--error 1031 +HANDLER t1 READ f1 NEXT; +--error 1031 +HANDLER t1 READ f1 NEXT; +--error 1031 +HANDLER t1 READ f1 NEXT; +HANDLER t1 CLOSE; +DROP TABLE t1; + +--echo End of 5.3 tests + +--echo # +--echo # MDEV-15813 ASAN use-after-poison in hp_hashnr upon +--echo # HANDLER READ on a versioned HEAP table +--echo # + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, CONSTRAINT PRIMARY KEY (a, b), UNIQUE ba(b, a) USING HASH) ENGINE=HEAP; +INSERT INTO t1 VALUES (1, 10), (2, 20), (3,30), (4,40); +HANDLER t1 OPEN AS m; +HANDLER m READ `PRIMARY`= (3,30); +--error ER_KEY_DOESNT_SUPPORT +HANDLER m READ `PRIMARY`> (3,30); +HANDLER m READ `ba`= (30,3); +--error ER_KEY_DOESNT_SUPPORT +HANDLER m READ `ba`= (30); +HANDLER m CLOSE; +DROP TABLE t1; + diff --git a/mysql-test/suite/handler/init.inc b/mysql-test/suite/handler/init.inc new file mode 100644 index 00000000..7418bbb1 --- /dev/null +++ b/mysql-test/suite/handler/init.inc @@ -0,0 +1,31 @@ +# Setup things for handler.inc +# +# Input variables +# $engine_type -- storage engine to be tested +# using btree -- set if you want a non standard key type +# +# This scripts sets up default values for: +# $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 +# time available storage engine +# have to be set before sourcing this script. +# +# Handler tests don't work with embedded server +# +-- source include/not_embedded.inc + +eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type; +let $other_handler_engine_type= MyISAM; + +--disable_warnings +drop table if exists t1,t3,t4,t5; +--enable_warnings + +# Create default test table + +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"); diff --git a/mysql-test/suite/handler/innodb.result b/mysql-test/suite/handler/innodb.result new file mode 100644 index 00000000..5d44642d --- /dev/null +++ b/mysql-test/suite/handler/innodb.result @@ -0,0 +1,1790 @@ +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 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> 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 diff --git a/mysql-test/suite/handler/innodb.test b/mysql-test/suite/handler/innodb.test new file mode 100644 index 00000000..8c984474 --- /dev/null +++ b/mysql-test/suite/handler/innodb.test @@ -0,0 +1,49 @@ +# t/handler_innodb.test +# +# test of HANDLER ... +# +# Last update: +# 2006-07-31 ML test refactored (MySQL 5.1) +# code of t/handler.test and t/innodb_handler.test united +# main testing code put into handler.inc +# rename t/innodb_handler.test to t/handler_innodb.test +# + +--source include/have_innodb.inc + +let $engine_type= InnoDB; + +--source init.inc +--source handler.inc +--source savepoint.inc + +# +# LP#697610 ha_index_prev(uchar*): Assertion `inited==INDEX' +# + +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; +HANDLER t1 READ `PRIMARY` PREV; +DROP TABLE t1; + +--echo # +--echo # 10.2 Test +--echo # +--echo # MDEV-20207: Assertion `! is_set()' failed in +--echo # Diagnostics_area::set_eof_status upon HANDLER READ +--echo # + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 (a POINT, KEY(a)); +HANDLER t1 OPEN h; + +--error ER_CANT_CREATE_GEOMETRY_OBJECT +HANDLER h READ a = (0); + +HANDLER h CLOSE; +DROP TABLE t1; + +--echo # End of 10.2 Test diff --git a/mysql-test/suite/handler/interface.result b/mysql-test/suite/handler/interface.result new file mode 100644 index 00000000..37584003 --- /dev/null +++ b/mysql-test/suite/handler/interface.result @@ -0,0 +1,354 @@ +drop table if exists t1,t3,t4,t5; +drop database if exists test_test; +SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; +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; +handler t1 read a=(SELECT 1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1)' at line 1 +handler t1 read a=(1) FIRST; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIRST' at line 1 +handler t1 read a=(1) NEXT; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NEXT' at line 1 +handler t1 read last; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +handler t1 close; +drop table t1; +CREATE TABLE t1(a INT, PRIMARY KEY(a)); +insert into t1 values(1),(2); +handler t1 open; +handler t1 read primary=(1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary=(1)' at line 1 +handler t1 read `primary`=(1); +a +1 +handler t1 close; +drop table t1; +create database test_test; +use test_test; +create table t1(table_id char(20), primary key (table_id)); +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 (table_id)); +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; +create table t1(table_id char(20), primary key (table_id)); +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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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; +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 MariaDB 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 MariaDB 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 MariaDB 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 int); +create table t2 (c1 int); +insert into t1 values (1); +insert into t2 values (2); +handler t1 open; +handler t1 read first; +c1 +1 +connect flush,localhost,root,,; +connection flush; +flush tables; +flush table t1; +connect waiter,localhost,root,,; +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; +connection flush; +connection default; +drop table t1,t2; +disconnect flush; +drop table if exists t1, t0; +create table t1 (c1 int); +handler t1 open; +handler t1 read first; +c1 +connect flush,localhost,root,,; +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 +connection default; +drop table t0; +connection flush; +disconnect flush; +connection waiter; +disconnect waiter; +connection default; +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, 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 +2 c +flush tables t1; +handler t1 read a next; +a b +0 a +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 +connect con1,localhost,root,,; +connect con2,localhost,root,,; +# Now test case which was reported originally but which no longer +# 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; +alter table t1 engine=csv; +connection con2; +connection default; +# 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; +connection con1; +drop table t1; +disconnect con1; +connection con2; +disconnect con2; +connection default; +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 +create view v as select 1; +create temporary table v as select 2; +handler v open; +prepare stmt from 'create table if not exists v as select 3'; +execute stmt; +Warnings: +Note 1050 Table 'v' already exists +handler v read next; +ERROR 42S02: Unknown table 'v' in HANDLER +drop view v; +# +# 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 +# +# MDEV-15813 ASAN use-after-poison in hp_hashnr upon +# HANDLER READ on a versioned HEAP table +# +CREATE TABLE t1 (g GEOMETRY NOT NULL, SPATIAL gi(g)); +INSERT INTO t1 VALUES (POINT(0,0)); +HANDLER t1 OPEN AS h; +HANDLER h READ `gi`= (10); +ERROR HY000: SPATIAL index `gi` does not support this operation +HANDLER h READ `gi`> (10); +ERROR HY000: SPATIAL index `gi` does not support this operation +HANDLER h CLOSE; +DROP TABLE t1; +CREATE TABLE t1 (w VARCHAR(100), FULLTEXT fk(w)); +INSERT INTO t1 VALUES ('one two three'); +HANDLER t1 OPEN AS h; +HANDLER h READ `fk`= (10); +ERROR HY000: FULLTEXT index `fk` does not support this operation +HANDLER h READ `fk`> (10); +ERROR HY000: FULLTEXT index `fk` does not support this operation +HANDLER h CLOSE; +DROP TABLE t1; diff --git a/mysql-test/suite/handler/interface.test b/mysql-test/suite/handler/interface.test new file mode 100644 index 00000000..a4ed640d --- /dev/null +++ b/mysql-test/suite/handler/interface.test @@ -0,0 +1,403 @@ +# +# Tests of handler interface that are system independent +# +# Handler tests don't work yet with embedded server +# +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1,t3,t4,t5; +drop database if exists test_test; +--enable_warnings + +# Run tests with myisam (any engine should be ok) + +let $engine_type= MyISAM; + +--source init.inc + +# +# Do some syntax checking +# + +handler t1 open; +--error ER_PARSE_ERROR +handler t1 read a=(SELECT 1); +--error ER_PARSE_ERROR +handler t1 read a=(1) FIRST; +--error ER_PARSE_ERROR +handler t1 read a=(1) NEXT; +--error ER_PARSE_ERROR +handler t1 read last; +handler t1 close; +drop table t1; + +CREATE TABLE t1(a INT, PRIMARY KEY(a)); +insert into t1 values(1),(2); +handler t1 open; +--error ER_PARSE_ERROR +handler t1 read primary=(1); +handler t1 read `primary`=(1); +handler t1 close; +drop table t1; + +# +# Check if two database names beginning the same are seen as different. +# +# This database begins like the usual 'test' database. +# +create database test_test; +use test_test; +eval create table t1(table_id char(20), primary key $key_type (table_id)); +insert into t1 values ('test_test.t1'); +insert into t1 values (''); +handler t1 open; +handler t1 read first limit 9; +eval create table t2(table_id char(20), primary key $key_type (table_id)); +insert into t2 values ('test_test.t2'); +insert into t2 values (''); +handler t2 open; +handler t2 read first limit 9; +# +# This is the usual 'test' database. +# +use test; +eval create table t1(table_id char(20), primary key $key_type (table_id)); +insert into t1 values ('test.t1'); +insert into t1 values (''); +--error 1066 +handler t1 open; +# +# Check accessibility of all the tables. +# +use test; +--error 1064 +handler test.t1 read first limit 9; +--error 1064 +handler test_test.t1 read first limit 9; +handler t1 read first limit 9; +--error 1064 +handler test_test.t2 read first limit 9; +handler t2 read first limit 9; + +# +# Cleanup. +# + +--error 1064 +handler test_test.t1 close; +handler t1 close; +drop table test_test.t1; +--error 1064 +handler test_test.t2 close; +handler t2 close; +drop table test_test.t2; +drop database test_test; + +# +use test; +--error 1064 +handler test.t1 close; +--error 1109 +handler t1 close; +drop table test.t1; + +# +# BUG#4335 one name can be handler open'ed many times +# + +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; +handler t1 open h1; +handler h1 read first limit 9; +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'); +--error 1066 +handler t1 open; +--error 1066 +handler t2 open t1; +--error 1066 +handler t3 open t1; +handler t1 read first limit 9; +--error 1064 +handler test.t1 close; +--error 1066 +handler test.t1 open h1; +--error 1066 +handler test_test.t1 open h1; +handler test_test.t3 open h3; +handler test.t1 open h2; +handler t1 read first limit 9; +handler h1 read first limit 9; +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h2 read first limit 9; +--error 1064 +handler test.h1 close; +handler t1 close; +handler h1 close; +handler h2 close; +--error 1109 +handler t1 read first limit 9; +--error 1109 +handler h1 read first limit 9; +--error 1109 +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h3 read first limit 9; +use test_test; +handler h3 read first limit 9; +--error 1064 +handler test.h3 read first limit 9; +handler h3 close; +use test; +drop table t3; +drop table t2; +drop table t1; +drop database test_test; + +# +# Bug#21587 FLUSH TABLES causes server crash when used with HANDLER statements +# + +create table t1 (c1 int); +create table t2 (c1 int); +insert into t1 values (1); +insert into t2 values (2); +handler t1 open; +handler t1 read first; +connect (flush,localhost,root,,); +connection flush; +flush tables; +send flush table t1; +connect (waiter,localhost,root,,); +connection waiter; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock"; +--source include/wait_condition.inc +connection default; +handler t2 open; +handler t2 read first; +handler t1 read next; +handler t1 close; +handler t2 close; +connection flush; +reap; +connection default; +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); +handler t1 open; +handler t1 read first; +connect (flush,localhost,root,,); +connection flush; +send rename table t1 to t0; +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 # +--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; +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. +# + +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; +drop table t1; +--error ER_UNKNOWN_TABLE +handler t1_alias read next; + +# Flush tables causes handlers reopen + +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; +handler t1 read a next; +flush tables; +handler t1 read a next; +flush tables t1; +handler t1 read a next; +flush tables with read lock; +handler t1 read a next; +unlock tables; +drop table t1; +--error ER_UNKNOWN_TABLE +handler t1 read a next; + +# +# Bug#41110: crash with handler command when used concurrently with alter table +# Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table +# + +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); + +--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 +# +--error ER_UNKNOWN_TABLE +PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST'; + +# +# MySQL Bug#16385711: HANDLER, CREATE TABLE IF NOT EXISTS, PROBLEM AFTER MYSQL_HA_FIND +# +create view v as select 1; +create temporary table v as select 2; +handler v open; +prepare stmt from 'create table if not exists v as select 3'; +execute stmt; +--error ER_UNKNOWN_TABLE +handler v read next; +drop view v; + +--echo # +--echo # 10.2 Test +--echo # +--echo # MDEV-20207: Assertion `! is_set()' failed in +--echo # Diagnostics_area::set_eof_status upon HANDLER READ +--echo # + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 (a POINT, KEY(a)); +HANDLER t1 OPEN h; + +--error ER_CANT_CREATE_GEOMETRY_OBJECT +HANDLER h READ a = (0); + +HANDLER h CLOSE; +DROP TABLE t1; + +--echo # End of 10.2 Test + +--echo # +--echo # MDEV-15813 ASAN use-after-poison in hp_hashnr upon +--echo # HANDLER READ on a versioned HEAP table +--echo # + +CREATE TABLE t1 (g GEOMETRY NOT NULL, SPATIAL gi(g)); +INSERT INTO t1 VALUES (POINT(0,0)); +HANDLER t1 OPEN AS h; +--error ER_KEY_DOESNT_SUPPORT +HANDLER h READ `gi`= (10); +--error ER_KEY_DOESNT_SUPPORT +HANDLER h READ `gi`> (10); +HANDLER h CLOSE; +DROP TABLE t1; + +CREATE TABLE t1 (w VARCHAR(100), FULLTEXT fk(w)); +INSERT INTO t1 VALUES ('one two three'); +HANDLER t1 OPEN AS h; +--error ER_KEY_DOESNT_SUPPORT +HANDLER h READ `fk`= (10); +--error ER_KEY_DOESNT_SUPPORT +HANDLER h READ `fk`> (10); +HANDLER h CLOSE; +DROP TABLE t1; diff --git a/mysql-test/suite/handler/myisam.result b/mysql-test/suite/handler/myisam.result new file mode 100644 index 00000000..cc817ccd --- /dev/null +++ b/mysql-test/suite/handler/myisam.result @@ -0,0 +1,1792 @@ +SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; +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 = MyISAM; +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=MyISAM; +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 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 status Table is already up to date +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 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 Engine-independent statistics collected +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; +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 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> 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 +1 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash +# +CREATE TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +DROP TABLE t1; +# +# Bug #54007: assert in ha_myisam::index_next , HANDLER +# +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a), KEY b(b), KEY ab(a, b)); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +HANDLER t1 READ `PRIMARY` NEXT; +a b +HANDLER t1 READ ab NEXT; +a b +HANDLER t1 READ b NEXT; +a b +HANDLER t1 READ NEXT; +a b +HANDLER t1 CLOSE; +INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ NEXT; +a b +1 10 +HANDLER t1 READ `PRIMARY` NEXT; +a b +1 10 +HANDLER t1 READ `PRIMARY` NEXT; +a b +2 20 +HANDLER t1 READ ab NEXT; +a b +1 10 +HANDLER t1 READ ab NEXT; +a b +2 20 +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 NEXT; +a b +1 10 +HANDLER t1 READ NEXT; +a b +4 40 +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ `PRIMARY` PREV; +a b +4 40 +HANDLER t1 READ `PRIMARY` PREV; +a b +3 30 +HANDLER t1 READ b PREV; +a b +4 40 +HANDLER t1 READ b PREV; +a b +3 30 +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ `PRIMARY` PREV LIMIT 3; +a b +4 40 +3 30 +2 20 +HANDLER t1 READ b NEXT LIMIT 5; +a b +1 10 +2 20 +3 30 +4 40 +HANDLER t1 CLOSE; +DROP TABLE t1; +# +# Additional coverage for refactoring which is made as part +# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +# to allow temp table operations". +# +# Check that DDL on temporary table properly closes HANDLER cursors +# for this table belonging to the same connection. +CREATE TEMPORARY TABLE t1 AS SELECT 1 AS f1; +# -- CREATE TABLE +HANDLER t1 OPEN; +CREATE TEMPORARY TABLE IF NOT EXISTS t1 SELECT 1 AS f1; +Warnings: +Note 1050 Table 't1' already exists +HANDLER t1 READ FIRST; +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 FIRST; +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 FIRST; +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 FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- CHECK TABLE +HANDLER t1 OPEN; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- ALTER TABLE +HANDLER t1 OPEN; +ALTER TABLE t1 ADD COLUMN b INT; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- CREATE INDEX +HANDLER t1 OPEN; +CREATE INDEX b ON t1 (b); +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- DROP INDEX +HANDLER t1 OPEN; +DROP INDEX b ON t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- TRUNCATE TABLE +HANDLER t1 OPEN; +TRUNCATE TABLE t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- DROP TABLE +HANDLER t1 OPEN; +DROP TABLE t1; +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +CREATE TEMPORARY TABLE t1(a INT, b INT, INDEX i(a)); +set global keycache1.key_cache_block_size=2048; +set global keycache1.key_buffer_size=1*1024*1024; +set global keycache1.key_buffer_size=1024*1024; +# -- CACHE INDEX +HANDLER t1 OPEN; +CACHE INDEX t1 IN keycache1; +Table Op Msg_type Msg_text +test.t1 assign_to_keycache status OK +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +# -- LOAD INDEX +HANDLER t1 OPEN; +LOAD INDEX INTO CACHE t1; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +HANDLER t1 READ FIRST; +ERROR 42S02: Unknown table 't1' in HANDLER +End of 5.1 tests +# +# 10.2 Test +# +# MDEV-20207: Assertion `! is_set()' failed in +# Diagnostics_area::set_eof_status upon HANDLER READ +# +DROP TABLE IF EXISTS 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 diff --git a/mysql-test/suite/handler/myisam.test b/mysql-test/suite/handler/myisam.test new file mode 100644 index 00000000..2fce8548 --- /dev/null +++ b/mysql-test/suite/handler/myisam.test @@ -0,0 +1,191 @@ +# t/handler_myisam.test +# +# test of HANDLER ... +# +# Last update: +# 2006-07-31 ML test refactored (MySQL 5.1) +# code of t/handler.test and t/innodb_handler.test united +# main testing code put into handler.inc +# rename t/handler.test to t/handler_myisam.test +# + +let $engine_type= MyISAM; + +--source init.inc +--source handler.inc + +--echo # +--echo # BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash +--echo # +CREATE TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 AS SELECT 1 AS f1; +HANDLER t1 OPEN; +TRUNCATE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; +DROP TABLE t1; + +--echo # +--echo # Bug #54007: assert in ha_myisam::index_next , HANDLER +--echo # +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a), KEY b(b), KEY ab(a, b)); + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 CLOSE; + +INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ NEXT; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 CLOSE; + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` PREV; +HANDLER t1 READ `PRIMARY` PREV; +HANDLER t1 READ b PREV; +HANDLER t1 READ b PREV; +HANDLER t1 CLOSE; + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` PREV LIMIT 3; +HANDLER t1 READ b NEXT LIMIT 5; +HANDLER t1 CLOSE; + +DROP TABLE t1; + + +--echo # +--echo # Additional coverage for refactoring which is made as part +--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +--echo # to allow temp table operations". +--echo # +--echo # Check that DDL on temporary table properly closes HANDLER cursors +--echo # for this table belonging to the same connection. + +CREATE TEMPORARY TABLE t1 AS SELECT 1 AS f1; + +--echo # -- CREATE TABLE +HANDLER t1 OPEN; +CREATE TEMPORARY TABLE IF NOT EXISTS t1 SELECT 1 AS f1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- REPAIR TABLE +HANDLER t1 OPEN; +REPAIR TABLE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- ANALYZE TABLE +HANDLER t1 OPEN; +ANALYZE TABLE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- OPTIMIZE TABLE +HANDLER t1 OPEN; +OPTIMIZE TABLE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- CHECK TABLE +HANDLER t1 OPEN; +CHECK TABLE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- ALTER TABLE +HANDLER t1 OPEN; +ALTER TABLE t1 ADD COLUMN b INT; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- CREATE INDEX +HANDLER t1 OPEN; +CREATE INDEX b ON t1 (b); +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- DROP INDEX +HANDLER t1 OPEN; +DROP INDEX b ON t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- TRUNCATE TABLE +HANDLER t1 OPEN; +TRUNCATE TABLE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- DROP TABLE +HANDLER t1 OPEN; +DROP TABLE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +CREATE TEMPORARY TABLE t1(a INT, b INT, INDEX i(a)); + +set global keycache1.key_cache_block_size=2048; +set global keycache1.key_buffer_size=1*1024*1024; +set global keycache1.key_buffer_size=1024*1024; + +--echo # -- CACHE INDEX +HANDLER t1 OPEN; +CACHE INDEX t1 IN keycache1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + +--echo # -- LOAD INDEX +HANDLER t1 OPEN; +LOAD INDEX INTO CACHE t1; +--error ER_UNKNOWN_TABLE +HANDLER t1 READ FIRST; + + +--echo End of 5.1 tests + +--echo # +--echo # 10.2 Test +--echo # +--echo # MDEV-20207: Assertion `! is_set()' failed in +--echo # Diagnostics_area::set_eof_status upon HANDLER READ +--echo # + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 (a POINT, KEY(a)); +HANDLER t1 OPEN h; + +--error ER_CANT_CREATE_GEOMETRY_OBJECT +HANDLER h READ a = (0); + +HANDLER h CLOSE; +DROP TABLE t1; + +--echo # End of 10.2 Test diff --git a/mysql-test/suite/handler/ps.result b/mysql-test/suite/handler/ps.result new file mode 100644 index 00000000..54685f91 --- /dev/null +++ b/mysql-test/suite/handler/ps.result @@ -0,0 +1,9 @@ +create table t1 (i int); +handler test.t1 open handler_a; +flush status; +handler handler_a read first; +i +show status like 'Com_stmt_prepare%'; +Variable_name Value +Com_stmt_prepare OK +drop table t1; diff --git a/mysql-test/suite/handler/ps.test b/mysql-test/suite/handler/ps.test new file mode 100644 index 00000000..783d8624 --- /dev/null +++ b/mysql-test/suite/handler/ps.test @@ -0,0 +1,13 @@ +# +# MDEV-15729 Server crashes in Field::make_field upon HANDLER READ executed with PS protocol +# +create table t1 (i int); +handler test.t1 open handler_a; +flush status; +handler handler_a read first; +# handler...read must be prepared in --ps-protocol mode +--replace_result $PS_PROTOCOL OK +--disable_ps_protocol +show status like 'Com_stmt_prepare%'; +--enable_ps_protocol +drop table t1; diff --git a/mysql-test/suite/handler/savepoint.inc b/mysql-test/suite/handler/savepoint.inc new file mode 100644 index 00000000..a237183e --- /dev/null +++ b/mysql-test/suite/handler/savepoint.inc @@ -0,0 +1,182 @@ +--echo # +--echo # Testing savepoints with handlers that supports it +--echo # +connect(con1, localhost, root,,); +connect(con2, localhost, root,,); +connect(con3, localhost, root,,); +connection default; +--echo # +--echo # ROLLBACK TO SAVEPOINT releases transactional locks, +--echo # but has no effect on open HANDLERs +--echo # +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; +--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; +connection con1; +--echo # Sending: +--send drop table t1 +connection con2; +--echo # Sending: +--send drop table t2 +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. +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 +connection default; +rollback to savepoint sv; +connection con2; +--echo # Reaping 'drop table t2'... +--reap +--echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +--echo # lock. +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; +connection con1; +--echo # Reaping 'drop table t1'... +--reap +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; +connection con1; +--echo # Sending: +--send drop table t1 +connection con2; +--echo # Sending: +--send drop table t2 +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. +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 +connection default; +rollback to savepoint sv; +connection con2; +--echo # Reaping 'drop table t2'... +--reap +--echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler +--echo # lock. +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; +connection con1; +--echo # Reaping 'drop table t1'... +--reap +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; +connection con1; +drop table t1, t2; +--echo # Sending: +--send drop table t3 +--echo # Let DROP TABLE statement sync in. +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. +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; +connection con1; +--echo # Reaping 'drop table t3'... +--reap +connection default; +commit; + +--echo # +--echo # Cleanup for savepoint.inc +--echo # +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +connection con3; +disconnect con3; +--source include/wait_until_disconnected.inc +connection default; |