# # 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); --echo connection: default handler t1 open; handler t1 read first; connect (flush,localhost,root,,); connection flush; --echo connection: flush send flush tables; connect (waiter,localhost,root,,); connection waiter; --echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table flush"; --source include/wait_condition.inc connection default; --echo connection: default handler t2 open; handler t2 read first; handler t1 read next; 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); --echo connection: default handler t1 open; handler t1 read first; connect (flush,localhost,root,,); connection flush; --echo connection: flush send rename table t1 to t0; connection waiter; --echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table metadata lock" and info = "rename table t1 to t0"; --source include/wait_condition.inc connection default; --echo connection: default --echo # --echo # RENAME placed two pending locks and waits. --echo # When HANDLER t0 OPEN does open_tables(), it calls --echo # mysql_ha_flush(), which in turn closes the open HANDLER for t1. --echo # RENAME TABLE gets unblocked. If it gets scheduled quickly --echo # and manages to complete before open_tables() --echo # of HANDLER t0 OPEN, open_tables() and therefore the whole --echo # HANDLER t0 OPEN succeeds. Otherwise open_tables() --echo # notices a pending or active exclusive metadata lock on t2 --echo # and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK --echo # error. --echo # --error 0, ER_LOCK_DEADLOCK handler t0 open; --error 0, ER_UNKNOWN_TABLE handler t0 close; --echo connection: flush connection flush; reap; --error ER_UNKNOWN_TABLE handler t1 read next; --error ER_UNKNOWN_TABLE handler t1 close; connection default; drop table t0; connection flush; disconnect flush; --source include/wait_until_disconnected.inc connection waiter; disconnect waiter; --source include/wait_until_disconnected.inc connection default; # # Bug#31397 Inconsistent drop table behavior of handler tables. # 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; 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,,); connection default; --disable_warnings drop table if exists t1; --enable_warnings --echo # First test case which is supposed trigger the execution --echo # path on which problem was discovered. create table t1 (a int not null); insert into t1 values (1); handler t1 open; connection con1; lock table t1 write; 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; --error ER_ILLEGAL_HA,ER_KEY_DOES_NOT_EXITS handler t1 read a next; handler t1 close; connection con1; --reap unlock tables; drop table t1; --echo # Now test case which was reported originally but which no longer --echo # triggers execution path which has caused the problem. connection default; create table t1 (a int not null); insert into t1 values (1); handler t1 open; connection con1; send alter table t1 engine=csv; connection con2; let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table metadata lock" and info = "alter table t1 engine=csv"; --source include/wait_condition.inc connection default; --echo # Since S metadata lock was already acquired at HANDLER OPEN time --echo # and TL_READ lock requested by HANDLER READ is compatible with --echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed --echo # without waiting. The old version of table should be used in it. handler t1 read next; handler t1 close; connection con1; --reap # Since last in this connection was a send drop table t1; disconnect con1; --source include/wait_until_disconnected.inc connection con2; disconnect con2; --source include/wait_until_disconnected.inc connection default; # # Bug#44151 using handler commands on information_schema tables crashes server # USE information_schema; --error ER_WRONG_USAGE HANDLER COLUMNS OPEN; USE test; # # lp:697622 Assertion `! is_set()' failed when preparing a HANDLER statement # --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;