From 31a78529bc5c4431865eba06762e6cc66359f759 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 31 Dec 2010 10:39:14 +0100 Subject: virtual columns: * move a capability from a virtual handler method to table_flags() * rephrase error messages to avoid hard-coded English parts * admit in test cases that they need xtradb, not innodb mysql-test/suite/vcol/t/rpl_vcol.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_column_def_options_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_handler_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_ins_upd_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_keys_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_non_stored_columns_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_partition_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_select_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_supported_sql_funcs_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_trigger_sp_innodb.test: this test needs xtradb, it will fail with innodb mysql-test/suite/vcol/t/vcol_view_innodb.test: this test needs xtradb, it will fail with innodb sql/ha_partition.h: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS sql/handler.h: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS sql/share/errmsg.txt: no hard-coded english parts in the error messages (ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN) sql/sql_table.cc: no hard-coded english parts in the error messages sql/table.cc: * check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS * no "csv workaround" is needed * no hard-coded english parts in the error messages storage/maria/ha_maria.cc: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS storage/maria/ha_maria.h: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS storage/myisam/ha_myisam.cc: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS storage/myisam/ha_myisam.h: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS storage/xtradb/handler/ha_innodb.cc: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS storage/xtradb/handler/ha_innodb.h: check_if_supported_virtual_columns() -> HA_CAN_VIRTUAL_COLUMNS --- mysql-test/include/have_xtradb.inc | 9 +++++---- mysql-test/include/have_xtradb.opt | 2 ++ 2 files changed, 7 insertions(+), 4 deletions(-) create mode 100644 mysql-test/include/have_xtradb.opt (limited to 'mysql-test/include') diff --git a/mysql-test/include/have_xtradb.inc b/mysql-test/include/have_xtradb.inc index 6c2fc5155a9..3f3893e5642 100644 --- a/mysql-test/include/have_xtradb.inc +++ b/mysql-test/include/have_xtradb.inc @@ -1,4 +1,5 @@ -disable_query_log; ---require r/true.require -SELECT (plugin_description LIKE '%xtradb%') AS `TRUE` FROM information_schema.plugins WHERE LOWER(plugin_name) = 'innodb' AND LOWER(plugin_status) = 'active'; -enable_query_log; +if (!`SELECT count(*) FROM information_schema.plugins WHERE + plugin_name = 'innodb' AND plugin_status = 'active' AND + plugin_description LIKE '%xtradb%'`){ + skip Need XtraDB engine; +} diff --git a/mysql-test/include/have_xtradb.opt b/mysql-test/include/have_xtradb.opt new file mode 100644 index 00000000000..4fb96229a7b --- /dev/null +++ b/mysql-test/include/have_xtradb.opt @@ -0,0 +1,2 @@ +--loose-innodb +--plugin-load=$HA_XTRADB_SO -- cgit v1.2.1 From 92fc42638661bf574009c4a3cd6a812d948db8e9 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 3 Jan 2011 15:33:39 +0100 Subject: Speed up `mtr --parallel=` by scheduling some slow tests earlier. The patch also fixes a race in rpl_stop_slave.test. On machines with lots of CPU and memory, something like `mtr --parallel=10` can speed up the test suite enormously. However, we have a few test cases that run for long (several minutes), and if we are unlucky and happen to schedule those towards the end of the test suite, we end up with most workers idle while waiting for the last slow test to end, significantly delaying the finish of the entire suite. Improve this by marking the offending tests as taking "long", and trying to schedule those tests early. This reduces the time towards the end of the test suite run where some workers are waiting with nothing to do for the remaining workers each to finish their last test. Also, the rpl_stop_slave test had a race which could cause it to take a 300 seconds debug_sync timeout; this is fixed. Testing on a 4-core 8GB machine, this patch speeds up the test suite with around 30% for --parallel=10 (debug build), allowing to run the entire suite in 5 minutes. --- mysql-test/include/long_test.inc | 4 ++++ 1 file changed, 4 insertions(+) create mode 100644 mysql-test/include/long_test.inc (limited to 'mysql-test/include') diff --git a/mysql-test/include/long_test.inc b/mysql-test/include/long_test.inc new file mode 100644 index 00000000000..d9a3b338229 --- /dev/null +++ b/mysql-test/include/long_test.inc @@ -0,0 +1,4 @@ +# We use this --source include to mark a test as taking long to run. +# We can use this to schedule such test early (to not be left with +# only one or two long tests running, and rests of works idle), or to +# run a quick test skipping long-running test cases. -- cgit v1.2.1 From e63b5546c597f65696868eaf69159107bc4a8e44 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Tue, 4 Jan 2011 00:55:41 +0200 Subject: Implementation of MWL#172: Add support for prepared statements to HANDLER READ It includes speed optimizations for HANDLER READ by caching as much as possible in HANDLER OPEN Other things: - Added mysqld option --disable-thr-alarm to be able to benchmark things without thr_alarm - Changed 'Locked' state to 'System lock' and 'Table lock' (these where used in the code but never shown to end user) - Better error message if mysql_install_db.sh fails - Moved handler function prototypes to sql_handler.h - Remove not anymore used 'thd->locked' member include/thr_alarm.h: Added my_disable_thr_alarm include/thr_lock.h: Add new member to THR_LOCK_DATA to remember original lock type state. This is needed as thr_unlock() resets type to TL_UNLOCK. mysql-test/include/check_no_concurrent_insert.inc: Locked -> Table lock mysql-test/include/handler.inc: Locked -> Table lock mysql-test/r/handler_innodb.result: Updated results for new tests mysql-test/r/handler_myisam.result: Updated results for new tests mysql-test/r/sp-threads.result: Locked -> Table lock mysql-test/suite/binlog/t/binlog_stm_row.test: Locked -> Table lock mysql-test/suite/funcs_1/datadict/processlist_val.inc: Locked -> Table lock mysql-test/suite/pbxt/t/lock_multi.test: Locked -> Table lock mysql-test/suite/sys_vars/r/concurrent_insert_func.result: Locked -> Table lock mysql-test/suite/sys_vars/t/concurrent_insert_func.test: Locked -> Table lock mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test: Locked -> Table lock mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test: Locked -> Table lock mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test: Locked -> Table lock mysql-test/t/insert_notembedded.test: Locked -> Table lock mysql-test/t/lock_multi.test: Locked -> Table lock mysql-test/t/merge-big.test: Locked -> Table lock mysql-test/t/multi_update.test: Locked -> Table lock mysql-test/t/query_cache_28249.test: Locked -> Table lock mysql-test/t/sp_notembedded.test: Locked -> Table lock mysql-test/t/sp_sync.test: Locked -> Table lock mysql-test/t/status.test: Locked -> Table lock mysql-test/t/trigger_notembedded.test: Locked -> Table lock mysys/thr_alarm.c: Added option to disable thr_alarm mysys/thr_lock.c: Detect loops scripts/mysql_install_db.sh: Give better error message if something goes wrong sql/Makefile.am: Added sql_handler.h sql/lock.cc: Split functions to allow one to cache value if store_lock() (for HANDLER functions). - Split mysql_lock_tables() into two functions, where first one allocates MYSQL_LOCK and other other one uses it. - Made get_lock_data() an external function. - Added argument to mysql_unlock_tables() to not free sql_lock. - Added argument to reset_lock_data() to reset lock structure to initial state (as after get_lock_data()) sql/mysql_priv.h: Moved handler function prototypes to sql_handler.h Added new lock functions. sql/mysqld.cc: Added --thread-alarm startup option sql/net_serv.cc: Don't call vio_blocking() if not needed sql/sql_base.cc: include sql_handler.h sql/sql_class.cc: include sql_handler.h Remove not anymore used 'thd->locked' member sql/sql_class.h: Remove not anymore used 'thd->locked' member sql/sql_db.cc: include sql_handler.h sql/sql_delete.cc: include sql_handler.h sql/sql_handler.cc: Rewrote all code to use SQL_HANDLER instead of TABLE_LIST (original interface) Rewrote mysql_ha_open() to cache all things from TABLE_LIST and items for field list, where etc. In mysql_ha_open() also cache MYSQL_LOCK structure from get_lock_data(). Split functions into smaller sub functions (needed to be able to implement mysql_ha_read_prepare()) Added mysql_ha_read_prepare() to allow one to prepare HANDLER READ. sql/sql_handler.h: Interface to sql_handler.cc sql/sql_parse.cc: include sql_handler.h sql/sql_prepare.cc: Added mysql_test_handler_read(), prepare for HANDLER READ sql/sql_rename.cc: include sql_handler.h sql/sql_show.cc: Removed usage of thd->locked sql/sql_table.cc: include sql_handler.h sql/sql_trigger.cc: include sql_handler.h --- mysql-test/include/check_no_concurrent_insert.inc | 2 +- mysql-test/include/handler.inc | 76 ++++++++++++++++++++++- 2 files changed, 76 insertions(+), 2 deletions(-) (limited to 'mysql-test/include') diff --git a/mysql-test/include/check_no_concurrent_insert.inc b/mysql-test/include/check_no_concurrent_insert.inc index 6938c53fd16..f6a3d2052f5 100644 --- a/mysql-test/include/check_no_concurrent_insert.inc +++ b/mysql-test/include/check_no_concurrent_insert.inc @@ -43,7 +43,7 @@ connection default; # of our statement. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Locked" and info = "insert into $table (i) values (0)"; + where state = "Table lock" and info = "insert into $table (i) values (0)"; --source include/wait_condition.inc --disable_result_log diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc index 6e7f53ba9b2..e8aeeeefcb2 100644 --- a/mysql-test/include/handler.inc +++ b/mysql-test/include/handler.inc @@ -77,8 +77,9 @@ handler t2 read a prev limit 10; handler t2 read a>=(16) limit 4; handler t2 read a>=(16) limit 2,2; +select * from t1 where a>=16 limit 2,2; handler t2 read a last limit 3; - +handler t2 read a=(16) limit 1,3; handler t2 read a=(19); handler t2 read a=(19) where b="yyy"; @@ -105,6 +106,79 @@ eval alter table t1 engine = $engine_type; --error 1109 handler t2 read first; +handler t1 open; +handler t1 read a=(16) limit 1,3; +flush tables; +handler t1 read a=(16) limit 1,3; +handler t1 close; + +# +# Test with prepared statements +# + +handler t1 open; +prepare stmt from 'handler t1 read a=(?) limit ?,?'; +set @a=16,@b=1,@c=100; +execute stmt using @a,@b,@c; +set @a=16,@b=2,@c=1; +execute stmt using @a,@b,@c; +set @a=16,@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>=(11); +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 a>=(?) where a < ? limit 5'; +set @a=15, @b=20; +execute stmt using @a,@b; +execute stmt using @a,@b; +deallocate prepare stmt; + +prepare stmt from 'handler t1 read a=(?)'; +set @a=16; +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=16; +--error ER_NEED_REPREPARE +execute stmt using @a; +deallocate prepare stmt; +handler t1 close; + # # DROP TABLE / ALTER TABLE # -- cgit v1.2.1 From 505c663a1e19af4c8ee726b305691414ab5fc999 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Mon, 10 Jan 2011 23:22:40 +0200 Subject: - Fixed that Aria works with HANDLER commands - Added test case for Aria - Tested HANDLER with HEAP (changes to HEAP code will be pushed in 5.3) - Moved all HANDLER test to suite/handler. mysql-test/Makefile.am: Added suite/handler mysql-test/mysql-test-run.pl: Added suite/handler mysql-test/r/lock_multi.result: Remove test that is already in handler test suite mysql-test/suite/handler/aria.result: Test for HANDLER with Aria storage engine mysql-test/suite/handler/aria.test: Test for HANDLER with Aria storage engine mysql-test/suite/handler/handler.inc: Extended the general handler test Moved interface testing to 'interface.test' mysql-test/suite/handler/init.inc: Common init for handler tests. mysql-test/suite/handler/innodb.result: New results mysql-test/suite/handler/innodb.test: Update to use new include files mysql-test/suite/handler/interface.result: Test of HANDLER interface (not storage engine dependent parts) mysql-test/suite/handler/interface.test: Test of HANDLER interface (not storage engine dependent parts) mysql-test/suite/handler/myisam.result: New results mysql-test/suite/handler/myisam.test: Update to use new include files mysql-test/t/lock_multi.test: Remove test that is already in handler test suite mysys/tree.c: Added missing handling of read previous (showed up in HEAP testing) sql/handler.cc: Don't marka 'HA_ERR_RECORD_CHANGED' as fatal (can be used with HANDLER READ, especially with MEMORY ENGINE) sql/handler.h: Added prototype for can_continue_handler_scan() sql/sql_handler.cc: Re-initialize search if we switch from key to table search. Check if handler can continue searching between calls (via can_continue_handler_scan()) Don't write common not fatal errors to log storage/maria/ma_extra.c: Don't set index 0 as default. This forces call to ma_check_index() to set up index variables. storage/maria/ma_ft_boolean_search.c: Ensure that info->last_key.keyinfo is set storage/maria/ma_open.c: Don't set index 0 as default. This forces call to ma_check_index() to set up index variables. storage/maria/ma_rkey.c: Trivial optimization storage/maria/ma_rnext.c: Added missing code from mi_rnext.c to ensure that handler next/prev works. storage/maria/ma_rsame.c: Simple optimizations storage/maria/ma_search.c: Initialize info->last_key once and for all when we change keys. storage/maria/ma_unique.c: Ensure that info->last_key.keyinfo is up to date. --- mysql-test/include/handler.inc | 731 ----------------------------------------- 1 file changed, 731 deletions(-) delete mode 100644 mysql-test/include/handler.inc (limited to 'mysql-test/include') diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc deleted file mode 100644 index 6e7f53ba9b2..00000000000 --- a/mysql-test/include/handler.inc +++ /dev/null @@ -1,731 +0,0 @@ -# include/handler.inc -# -# The variables -# $engine_type -- storage engine to be tested -# $other_engine_type -- storage engine <> $engine_type -# $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 -# have to be set before sourcing this script. --- source include/not_embedded.inc -# -# 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 include/handler.inc -# - -eval SET SESSION STORAGE_ENGINE = $engine_type; - ---disable_warnings -drop table if exists t1,t3,t4,t5; ---enable_warnings - -create table t1 (a int, b char(10), key a(a), key b(a,b)); -insert into t1 values -(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), -(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); -handler t1 open as t2; --- error 1064 -handler t2 read a=(SELECT 1); -handler t2 read a first; -handler t2 read a next; -handler t2 read a next; -handler t2 read a prev; -handler t2 read a last; -handler t2 read a prev; -handler t2 read a prev; - -handler t2 read a first; -handler t2 read a prev; - -handler t2 read a last; -handler t2 read a prev; -handler t2 read a next; -handler t2 read a next; - -handler t2 read a=(15); -handler t2 read a=(16); - ---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); - -handler t2 read a=(18); -handler t2 read a>=(18); -handler t2 read a>(18); -handler t2 read a<=(18); -handler t2 read a<(18); - -handler t2 read a first limit 5; -handler t2 read a next limit 3; -handler t2 read a prev limit 10; - -handler t2 read a>=(16) limit 4; -handler t2 read a>=(16) limit 2,2; -handler t2 read a last limit 3; - -handler t2 read a=(19); -handler t2 read a=(19) where b="yyy"; - -handler t2 read first; -handler t2 read next; -handler t2 read next; ---error 1064 -handler t2 read last; -handler t2 close; - -handler t1 open; -handler t1 read a next; # this used to crash as a bug#5373 -handler t1 read a 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; - -# -# DROP TABLE / ALTER TABLE -# -handler t1 open as t2; -drop table t1; -create table t1 (a int); -insert into t1 values (17); ---error 1109 -handler t2 read first; -handler t1 open as t2; -eval alter table t1 engine=$other_engine_type; ---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(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 (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; - -# -# Check if two database names beginning the same are seen as different. -# -# This database begins like the usual 'test' database. -# ---disable_warnings -drop database if exists test_test; ---enable_warnings -create database test_test; -use test_test; -create table t1(table_id char(20) primary key); -insert into t1 values ('test_test.t1'); -insert into t1 values (''); -handler t1 open; -handler t1 read first limit 9; -create table t2(table_id char(20) primary key); -insert into t2 values ('test_test.t2'); -insert into t2 values (''); -handler t2 open; -handler t2 read first limit 9; -# -# This is the usual 'test' database. -# -use test; ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1(table_id char(20) primary key); -insert into t1 values ('test.t1'); -insert into t1 values (''); ---error 1066 -handler t1 open; -# -# Check accesibility 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 -# ---disable_warnings -drop database if exists test_test; -drop table if exists t1; -drop table if exists t2; -drop table if exists t3; ---enable_warnings -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; - -# -# 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); -# client 1 -handler t1 open; -handler t1 read first; -# client 2 -connect (con2,localhost,root,,); -connection con2; ---exec echo send the below to another connection, do not wait for the result -send optimize table t1; ---sleep 1 -# client 1 ---exec echo proceed with the normal connection -connection default; -handler t1 read next; -handler t1 close; -# client 2 ---exec echo read the result from the other connection -connection con2; -reap; -# client 1 ---exec echo proceed with the normal connection -connection default; -drop table t1; - -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)); -INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); -HANDLER t1 OPEN; -HANDLER t1 READ `primary` = (1, 1000); -HANDLER t1 READ `primary` PREV; -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; -# -# client 2 -# 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; ---exec echo send the below to another connection, do not wait for the result -send drop table t1; ---sleep 1 -# -# client 1 -# Now we need something that wants LOCK_open. A simple table access which -# opens the table does the trick. ---exec echo proceed with the normal connection -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; -# -# client 2 -# Read the result of the drop command. -connection con2; ---exec echo read the result from the other connection -reap; -# -# client 1 -# Now back to normal operation. The table should not exist any more. ---exec echo proceed with the normal connection -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 -# ---disable_warnings -drop table if exists t1; ---enable_warnings -eval create table t1 (a int) ENGINE=$other_engine_type; ---echo --> client 2 -connection con2; ---error 1031 -handler t1 open; ---echo --> client 1 -connection default; -drop table t1; -disconnect con2; - -# -# Bug#30632 HANDLER read failure causes hang -# ---disable_warnings -drop table if exists t1; ---enable_warnings -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#21587 FLUSH TABLES causes server crash when used with HANDLER statements -# - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings -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; -connection default; ---echo connection: default -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; ---source include/wait_condition.inc -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,t2; ---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 t2; -connection default; ---echo connection: default -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "rename table t1 to t2"; ---source include/wait_condition.inc -handler t2 open; -handler t2 read first; ---error ER_NO_SUCH_TABLE -handler t1 read next; -handler t1 close; -handler t2 close; -connection flush; -reap; -connection default; -drop table t2; -disconnect flush; - -# -# Bug#30882 Dropping a temporary table inside a stored function may cause a server crash -# -# Test HANDLER statements in conjunction with temporary tables. While the temporary table -# is open by a HANDLER, no other statement can access it. -# - ---disable_warnings -drop table if exists t1; ---enable_warnings -create temporary table t1 (a int, b char(1), key a(a), key b(a,b)); -insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), - (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); -select a,b from t1; -handler t1 open as a1; -handler a1 read a first; -handler a1 read a next; -handler a1 read a next; ---error ER_CANT_REOPEN_TABLE -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 a first; -handler a2 read a last; -handler a2 read a prev; -handler a2 close; -drop table t1; - -# -# Bug#31397 Inconsistent drop table behavior of handler tables. -# - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings -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; - -# Test that temporary tables associated with handlers are properly dropped. - -create table t1 (a int); -create temporary table t2 (a int, key(a)); -handler t1 open as a1; -handler t2 open as a2; -handler a2 read a first; -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(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(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(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; - -# 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 -# - ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1 (a int); -insert into t1 values (1); -handler t1 open; -connect(con1,localhost,root,,); -send alter table t1 engine=memory; -connection default; -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "rename result table" and info = "alter table t1 engine=memory"; ---source include/wait_condition.inc ---error ER_ILLEGAL_HA -handler t1 read a next; -handler t1 close; -connection con1; ---reap -drop table t1; -disconnect con1; ---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; -- cgit v1.2.1