1 files changed, 379 insertions, 0 deletions
diff --git a/mysql-test/suite/handler/interface.test b/mysql-test/suite/handler/interface.test
new file mode 100644
index 00000000000..e4e854b2515
--- /dev/null
+++ b/mysql-test/suite/handler/interface.test
@@ -0,0 +1,379 @@
+#
+# 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
+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';