diff options
Diffstat (limited to 'mysql-test/suite/parts/t')
30 files changed, 936 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/t/partition_debug.test b/mysql-test/suite/parts/t/partition_debug.test new file mode 100644 index 00000000000..aa943e5fbe1 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_debug.test @@ -0,0 +1,33 @@ +# Partitioning test that require debug features +# including crashing tests. + +--source include/have_debug.inc +--source include/have_partition.inc +# Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc +# Crash tests don't work with embedded +--source include/not_embedded.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--let $DATADIR= `SELECT @@datadir;` + +--echo # +--echo # WL#4445: EXCHANGE PARTITION WITH TABLE +--echo # Verify ddl_log in case of crashing. +call mtr.add_suppression("Attempting backtrace. You can use the following information to find out"); +call mtr.add_suppression('InnoDB: The log sequence numbers [0-9]+ and [0-9]+ in ibdata files do not match the log sequence number [0-9]+ in the ib_logfiles!'); + +let $create_statement= CREATE TABLE t1 (a INT, b VARCHAR(64)) PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN MAXVALUE); +let $insert_statement= INSERT INTO t1 VALUES (1, "Original from partition p0"), (2, "Original from partition p0"), (3, "Original from partition p0"), (4, "Original from partition p0"), (11, "Original from partition p1"), (12, "Original from partition p1"), (13, "Original from partition p1"), (14, "Original from partition p1"), (21, "Original from partition p1"), (22, "Original from partition p1"), (23, "Original from partition p1"), (24, "Original from partition p1"); + +let $create_statement2= CREATE TABLE t2 (a INT, b VARCHAR(64)); +let $insert_statement2= INSERT INTO t2 VALUES (5, "Original from table t2"), (6, "Original from table t2"), (7, "Original from table t2"), (8, "Original from table t2"); +let $crash_statement= ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; +--source suite/parts/inc/partition_crash_exchange.inc +let $fail_statement= $crash_statement; +--source suite/parts/inc/partition_fail_exchange.inc diff --git a/mysql-test/suite/parts/t/partition_exch_innodb.test b/mysql-test/suite/parts/t/partition_exch_innodb.test new file mode 100644 index 00000000000..cb666a426bd --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_innodb.test @@ -0,0 +1,12 @@ +# Author: Horst Hunger +# Created: 2010-07-05 + +--source include/have_innodb.inc +--source include/have_partition.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--source suite/parts/inc/part_exch_qa.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_myisam.test b/mysql-test/suite/parts/t/partition_exch_myisam.test new file mode 100644 index 00000000000..c768040959f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_myisam.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-05 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test new file mode 100644 index 00000000000..fa956f19aec --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test @@ -0,0 +1,21 @@ +# Author: Horst Hunger +# Created: 2010-07-05 + +--source include/have_innodb.inc +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +--error 1497 +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa.test b/mysql-test/suite/parts/t/partition_exch_qa.test new file mode 100644 index 00000000000..c768040959f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-05 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_10.test b/mysql-test/suite/parts/t/partition_exch_qa_10.test new file mode 100644 index 00000000000..4f569605f5f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_10.test @@ -0,0 +1,69 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/not_embedded.inc +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +# 17) Exchanges in Procedure, triggers and events. +DELIMITER |; +CREATE PROCEDURE test_p1 () +BEGIN +ALTER TABLE t_10 ADD UNIQUE INDEX USING BTREE (a); +ALTER TABLE tp ADD UNIQUE INDEX USING BTREE (a); +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +ALTER TABLE tp DROP INDEX a; +ALTER TABLE t_10 DROP INDEX a; +END| +DELIMITER ;| +CALL test_p1; +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM tp WHERE a BETWEEN 0 AND 9; +DROP PROCEDURE test_p1; + +SET @save_autocommit= @@autocommit; +SET @@autocommit= OFF; +SHOW VARIABLES LIKE '%autocommit%'; +DELIMITER |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER test_trg_1 BEFORE UPDATE ON tp FOR EACH ROW +BEGIN +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +END| +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION test_f_1() RETURNS int +BEGIN +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +END| +DELIMITER ;| +SET @@autocommit= @save_autocommit; + +SET @save_event_scheduler= @@global.event_scheduler; +SET @@global.event_scheduler= ON; +CREATE EVENT test_ev_1 + ON SCHEDULE AT CURRENT_TIMESTAMP + DO + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +sleep 1; +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM tp WHERE a BETWEEN 0 AND 9; +SET @@global.event_scheduler= @save_event_scheduler; + +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_11.test b/mysql-test/suite/parts/t/partition_exch_qa_11.test new file mode 100644 index 00000000000..3283c65966b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_11.test @@ -0,0 +1,37 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +# 13) Exchange with dynamic variables. +SET @part= 'p0'; +SET @part_tab= 'tp'; +SET @table= 't_10'; +SET @s= CONCAT('ALTER TABLE ',@part_tab,' EXCHANGE PARTITION ',@part,' WITH TABLE ',@table); +PREPARE test_stmt1 FROM @s; +EXECUTE test_stmt1; +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM tp; +DEALLOCATE PREPARE test_stmt1; + +SET @part_tab= 'tp'; +SET @s= CONCAT('ALTER TABLE ',@part_tab,' EXCHANGE PARTITION ? WITH TABLE ?'); +--error ER_PARSE_ERROR +PREPARE test_stmt2 FROM @s; + +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_12.test b/mysql-test/suite/parts/t/partition_exch_qa_12.test new file mode 100644 index 00000000000..7e048f79017 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_12.test @@ -0,0 +1,178 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +use test; + +--disable_result_log +--disable_query_log +--disable_warnings +DROP TABLE IF EXISTS t_10; +DROP TABLE IF EXISTS t_100; +DROP TABLE IF EXISTS t_1000; +DROP TABLE IF EXISTS tp; +DROP TABLE IF EXISTS tsp; +DROP TABLE IF EXISTS t_empty; +DROP TABLE IF EXISTS t_null; +--enable_warnings + +eval CREATE TABLE t_10 (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +CHECKSUM= 1, +ENGINE = $engine_table; + +eval CREATE TABLE t_100 (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +COMMENT= 'comment', +ROW_FORMAT= COMPRESSED, +ENGINE = $engine_table; + +eval CREATE TABLE t_1000 (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +MIN_ROWS= 1, +MAX_ROWS= 200, +ENGINE = $engine_table; + +eval CREATE TABLE t_empty (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +ENGINE = $engine_table; + +eval CREATE TABLE t_null (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +ENGINE = $engine_table; + +eval CREATE TABLE tp (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +ENGINE = $engine_part +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (100), + PARTITION p2 VALUES LESS THAN (1000)); + +eval CREATE TABLE tsp (a INT, + b VARCHAR(55), + PRIMARY KEY (a)) +ENGINE = $engine_subpart +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) + (SUBPARTITION sp00, + SUBPARTITION sp01, + SUBPARTITION sp02, + SUBPARTITION sp03, + SUBPARTITION sp04), + PARTITION p1 VALUES LESS THAN (100) + (SUBPARTITION sp10, + SUBPARTITION sp11, + SUBPARTITION sp12, + SUBPARTITION sp13, + SUBPARTITION sp14), + PARTITION p2 VALUES LESS THAN (1000) + (SUBPARTITION sp20, + SUBPARTITION sp21, + SUBPARTITION sp22, + SUBPARTITION sp23, + SUBPARTITION sp24)); + +# Values t_10 (not partitioned) +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); + +# Values t_100 (not partitioned) +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); + +# Values t_1000 (not partitioned) +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); + +# Values t_null (not partitioned) +INSERT INTO t_null VALUES (1, "NULL"); + +# Values tp (partitions) +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); + +# Values tps (subpartitions) +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); + +eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table + AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table + AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table + AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table + AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table + AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; + +SHOW CREATE TABLE t_10; +SHOW CREATE TABLE t_100; +SHOW CREATE TABLE t_1000; +SHOW CREATE TABLE tp; +SHOW CREATE TABLE tsp; + +--enable_result_log +--enable_query_log + +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM t_100; +--sorted_result +SELECT * FROM t_1000; +--sorted_result +SELECT * FROM tp; +--sorted_result +SELECT * FROM tsp; +--sorted_result +SELECT * FROM tsp_00; +--sorted_result +SELECT * FROM tsp_01; +--sorted_result +SELECT * FROM tsp_02; +--sorted_result +SELECT * FROM tsp_03; +--sorted_result +SELECT * FROM tsp_04; + +# 13) Exchanges with different table options. +# IGNORE was removed in bug#57708. +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +#--error ER_TABLES_DIFFERENT_METADATA +#ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE; +SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' +AND TABLE_NAME IN ('tp', 't_100'); +ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100; +#--error ER_TABLES_DIFFERENT_METADATA +#ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100 IGNORE; +--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION +ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000; +#--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION +#ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000 IGNORE; + +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_13.test b/mysql-test/suite/parts/t/partition_exch_qa_13.test new file mode 100644 index 00000000000..c830ee61dad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_13.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa_13.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_14.test b/mysql-test/suite/parts/t/partition_exch_qa_14.test new file mode 100644 index 00000000000..7c6699a0a72 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_14.test @@ -0,0 +1,94 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/not_windows.inc +--source include/have_partition.inc +--source include/have_symlink.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +# DATA DIRECTORY +# Make directory for partition data +let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; +--mkdir $data_dir_path +let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; +let $data_directory= DATA DIRECTORY = '$data_dir_path'; + +# INDEX DIRECTORY +# Make directory for partition index +let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; +--mkdir $idx_dir_path +let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; +let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + +--source suite/parts/inc/part_exch_drop_tabs.inc +--rmdir $data_dir_path +--rmdir $idx_dir_path + +# DATA DIRECTORY +# Make directory for partition data +let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; +--mkdir $data_dir_path +let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + +# INDEX DIRECTORY +# Make directory for partition index +let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; +--mkdir $idx_dir_path +let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + +--source suite/parts/inc/part_exch_drop_tabs.inc +--rmdir $data_dir_path +--rmdir $idx_dir_path + +# DATA DIRECTORY +# Make directory for partition data +let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; +--mkdir $data_dir_path +let $data_directory= DATA DIRECTORY = '$data_dir_path'; + +# INDEX DIRECTORY +# Make directory for partition index +let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; +--mkdir $idx_dir_path +let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + +--source suite/parts/inc/part_exch_drop_tabs.inc +--rmdir $data_dir_path +--rmdir $idx_dir_path + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_15.test b/mysql-test/suite/parts/t/partition_exch_qa_15.test new file mode 100644 index 00000000000..51d09be5ed9 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_15.test @@ -0,0 +1,31 @@ +# Author: Horst Hunger +# Created: 2010-07-15 + +--source include/have_innodb.inc +--source include/have_partition.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +# 21) Foreign Key. +# Exchange of partition with table differing in structure. +CREATE TABLE t_11 (a INT, b VARCHAR(55), + FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE) + ENGINE= InnoDB; +#--error ER_TABLES_DIFFERENT_METADATA +--error ER_PARTITION_EXCHANGE_FOREIGN_KEY +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; +--disable_warnings +DROP TABLE IF EXISTS t_11; +--enable_warnings +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test b/mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test new file mode 100644 index 00000000000..210791515dc --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_1_innodb.test @@ -0,0 +1,12 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--source suite/parts/inc/part_exch_qa_1.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test b/mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test new file mode 100644 index 00000000000..db3a428b01b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_1_myisam.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-13 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa_1.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_2.test b/mysql-test/suite/parts/t/partition_exch_qa_2.test new file mode 100644 index 00000000000..83dc0a81fca --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_2.test @@ -0,0 +1,80 @@ +# Author: Horst Hunger +# Created: 2010-07-05 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM t_100; +--sorted_result +SELECT * FROM t_1000; +--sorted_result +SELECT * FROM tp; +--sorted_result +SELECT * FROM tsp; +--sorted_result +SELECT * FROM tsp_00; +--sorted_result +SELECT * FROM tsp_01; +--sorted_result +SELECT * FROM tsp_02; +--sorted_result +SELECT * FROM tsp_03; +--sorted_result +SELECT * FROM tsp_04; + +# 3) Invalid exchanges. +# Exchange of partition with table differing in structure. +CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10; +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; +--disable_warnings +DROP TABLE IF EXISTS t_11; +--enable_warnings +eval CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table SELECT * FROM t_10; +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; +--disable_warnings +DROP TABLE IF EXISTS t_11; +CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10; +--error ER_MIX_HANDLER_ERROR +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; +--disable_warnings +DROP TABLE IF EXISTS t_11; +--enable_warnings +# Exchange of partition with partitioned table. +eval CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table + PARTITION BY KEY() AS SELECT * FROM t_10; +--error ER_PARTITION_EXCHANGE_PART_TABLE +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; +--disable_warnings +DROP TABLE IF EXISTS t_11; +--enable_warnings +# Exchange of subpartition with partitioned table. +--error ER_PARTITION_EXCHANGE_PART_TABLE +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp; +# Exchange of subpartitioned partition with table. +--error ER_PARTITION_INSTEAD_OF_SUBPARTITION +ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10; +# Exchange of values in partition not fitting the hash. +--error ER_ROW_DOES_NOT_MATCH_PARTITION +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_100; +# Exchange of values in subpartition not fitting the hash. +--error ER_ROW_DOES_NOT_MATCH_PARTITION +ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_10; + +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_3.test b/mysql-test/suite/parts/t/partition_exch_qa_3.test new file mode 100644 index 00000000000..aa79e97adb6 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_3.test @@ -0,0 +1,47 @@ +# Author: Horst Hunger +# Created: 2010-07-05 + +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= MYISAM; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +use test; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc + +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM t_100; +--sorted_result +SELECT * FROM t_1000; +--sorted_result +SELECT * FROM tp; +--sorted_result +SELECT * FROM tsp; +--sorted_result +SELECT * FROM tsp_00; +--sorted_result +SELECT * FROM tsp_01; +--sorted_result +SELECT * FROM tsp_02; +--sorted_result +SELECT * FROM tsp_03; +--sorted_result +SELECT * FROM tsp_04; +--enable_result_log +--enable_query_log + +# 5) Exchanges with different engines. +--error ER_MIX_HANDLER_ERROR +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +--error ER_MIX_HANDLER_ERROR +ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; + +--source suite/parts/inc/part_exch_drop_tabs.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test b/mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test new file mode 100644 index 00000000000..ea38337f3aa --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_4_innodb.test @@ -0,0 +1,12 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--source suite/parts/inc/part_exch_qa_4.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test b/mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test new file mode 100644 index 00000000000..f668164630b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_4_myisam.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa_4.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test b/mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test new file mode 100644 index 00000000000..b1daa070412 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_5_innodb.test @@ -0,0 +1,12 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--source suite/parts/inc/part_exch_qa_5.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test b/mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test new file mode 100644 index 00000000000..8fcf43b8148 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_5_myisam.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa_5.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_6.test b/mysql-test/suite/parts/t/partition_exch_qa_6.test new file mode 100644 index 00000000000..bad1e134af7 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_6.test @@ -0,0 +1,102 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/not_embedded.inc +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +CREATE USER test2@localhost; + +--disable_result_log +--disable_query_log +--source suite/parts/inc/part_exch_tabs.inc +--enable_result_log +--enable_query_log + +GRANT USAGE ON *.* TO test2@localhost; +GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost; + +--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK); +connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +USE test; +SHOW GRANTS FOR CURRENT_USER; +# 9) Exchanges with different owner. +# Privilege for ALTER and SELECT +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM tp WHERE a BETWEEN 0 AND 10; +# Back to former values. +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +--sorted_result +SELECT * FROM t_10; +--sorted_result +SELECT * FROM tp WHERE a BETWEEN 0 AND 10; +ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; +--sorted_result +SELECT * FROM tsp_00; +--sorted_result +SELECT * FROM tsp WHERE a BETWEEN 0 AND 10; +# Back to former values. +ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; +--sorted_result +SELECT * FROM tsp_00; +--sorted_result +SELECT * FROM tsp WHERE a BETWEEN 0 AND 10; +--echo disconnect test2; +disconnect test2; + +--echo connection default; +connection default; +REVOKE INSERT ON test.* FROM test2@localhost; + +--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK); +connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +USE test; +SHOW GRANTS FOR CURRENT_USER; +# Privilege for ALTER and SELECT +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +--echo disconnect test2; +disconnect test2; + +--echo connection default; +connection default; +GRANT INSERT ON test.* TO test2@localhost; +REVOKE CREATE ON test.* FROM test2@localhost; + +--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK); +connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +USE test; +SHOW GRANTS FOR CURRENT_USER; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; +--echo disconnect test2; +disconnect test2; + +--echo connection default; +connection default; +GRANT CREATE ON test.* TO test2@localhost; +REVOKE DROP ON test.* FROM test2@localhost; + +--echo connect (test2,localhost,test2,,test,MASTER_MYPORT,MASTER_MYSOCK); +connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +USE test; +SHOW GRANTS FOR CURRENT_USER; +# Privilege for ALTER and SELECT +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +--echo disconnect test2; +disconnect test2; + +--echo connection default; +connection default; + +--source suite/parts/inc/part_exch_drop_tabs.inc + +DROP USER test2@localhost; + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test b/mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test new file mode 100644 index 00000000000..e23790672ab --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_7_innodb.test @@ -0,0 +1,12 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--source suite/parts/inc/part_exch_qa_7.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test b/mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test new file mode 100644 index 00000000000..e1bbd2b5815 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_7_myisam.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-06 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa_7.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test b/mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test new file mode 100644 index 00000000000..58fecdbc66d --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_8_innodb.test @@ -0,0 +1,12 @@ +# Author: Horst Hunger +# Created: 2010-07-07 + +--source include/have_partition.inc +--source include/have_innodb.inc + +let $engine_table= InnoDB; +let $engine_part= InnoDB; +let $engine_subpart= InnoDB; + +--source suite/parts/inc/part_exch_qa_8.inc + diff --git a/mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test b/mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test new file mode 100644 index 00000000000..b8573a6aabf --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_8_myisam.test @@ -0,0 +1,11 @@ +# Author: Horst Hunger +# Created: 2010-07-07 + +--source include/have_partition.inc + +let $engine_table= MYISAM; +let $engine_part= MYISAM; +let $engine_subpart= MYISAM; + +--source suite/parts/inc/part_exch_qa_8.inc + diff --git a/mysql-test/suite/parts/t/partition_exchange_archive.test b/mysql-test/suite/parts/t/partition_exchange_archive.test new file mode 100644 index 00000000000..3376e2ff884 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exchange_archive.test @@ -0,0 +1,11 @@ +--source include/have_archive.inc +--source include/have_partition.inc +--source include/have_debug_sync.inc + + +--echo # Archive does not support keys, update or delete +let $engine= 'Archive'; +let $no_keys= 1; +let $no_update= 1; +let $no_delete= 1; +--source suite/parts/inc/partition_exchange.inc diff --git a/mysql-test/suite/parts/t/partition_exchange_blackhole.test b/mysql-test/suite/parts/t/partition_exchange_blackhole.test new file mode 100644 index 00000000000..e58bccfb1a8 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exchange_blackhole.test @@ -0,0 +1,18 @@ +--source include/have_blackhole.inc +--source include/have_partition.inc +--source include/have_debug_sync.inc + +let $engine= 'Blackhole'; +--echo # Blackhole has not implemented check_if_incompatible_data, +--echo # so it cannot support EXCHANGE PARTITION +CREATE TABLE t (a int, b varchar(55)) ENGINE = 'Blackhole'; +CREATE TABLE tp (a int, b varchar(55)) ENGINE = 'Blackhole' +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (100), + PARTITION p1 VALUES LESS THAN MAXVALUE); +--error ER_TABLES_DIFFERENT_METADATA +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t; +DROP TABLE t, tp; + +# to use when check_if_incompatible_data is implemented in ha_blackhole +#--source suite/parts/inc/partition_exchange.inc diff --git a/mysql-test/suite/parts/t/partition_exchange_innodb.test b/mysql-test/suite/parts/t/partition_exchange_innodb.test new file mode 100644 index 00000000000..31ea61bbb7b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exchange_innodb.test @@ -0,0 +1,6 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_debug_sync.inc + +let $engine= 'InnoDB'; +--source suite/parts/inc/partition_exchange.inc diff --git a/mysql-test/suite/parts/t/partition_exchange_memory.test b/mysql-test/suite/parts/t/partition_exchange_memory.test new file mode 100644 index 00000000000..7384a49f829 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exchange_memory.test @@ -0,0 +1,5 @@ +--source include/have_partition.inc +--source include/have_debug_sync.inc + +let $engine= 'Memory'; +--source suite/parts/inc/partition_exchange.inc diff --git a/mysql-test/suite/parts/t/partition_exchange_myisam.test b/mysql-test/suite/parts/t/partition_exchange_myisam.test new file mode 100644 index 00000000000..68e55090a9c --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exchange_myisam.test @@ -0,0 +1,5 @@ +--source include/have_partition.inc +--source include/have_debug_sync.inc + +let $engine= 'MyISAM'; +--source suite/parts/inc/partition_exchange.inc diff --git a/mysql-test/suite/parts/t/partition_repair_myisam.test b/mysql-test/suite/parts/t/partition_repair_myisam.test index 91c8ed57580..f7c503a8544 100644 --- a/mysql-test/suite/parts/t/partition_repair_myisam.test +++ b/mysql-test/suite/parts/t/partition_repair_myisam.test @@ -5,6 +5,8 @@ drop table if exists t1_will_crash; call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*root Checking table"); +call mtr.add_suppression("is marked as crashed and should be repaired"); + --enable_query_log --enable_warnings @@ -12,6 +14,43 @@ call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*root Checki let $MYSQLD_DATADIR= `select @@datadir`; +--echo # Test what happens if we exchange a crashed partition with a table +SHOW VARIABLES LIKE 'myisam_recover_options'; +CREATE TABLE t (a INT, KEY (a)) ENGINE=MyISAM; +CREATE TABLE tp (a INT, KEY (a)) ENGINE=MyISAM +PARTITION BY RANGE (a) +(PARTITION pCrashed VALUES LESS THAN (15), + PARTITION pMAX VALUES LESS THAN MAXVALUE); +INSERT INTO t VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +INSERT INTO tp VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +FLUSH TABLES; +--echo # replacing tp#P#pCrashed.MYI with a corrupt + unclosed one created by doing: +--echo # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI +--remove_file $MYSQLD_DATADIR/test/tp#P#pCrashed.MYI +--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/test/tp#P#pCrashed.MYI +CHECK TABLE tp; +--replace_regex /[^']*test[^']*t/.\/test\/t/ +--error 145 +ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; +REPAIR TABLE tp; +CHECK TABLE tp; +ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; +CHECK TABLE t; +CHECK TABLE tp; +FLUSH TABLES; +--remove_file $MYSQLD_DATADIR/test/t.MYI +--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/test/t.MYI +CHECK TABLE t; +--replace_regex /[^']*test[^']*t/.\/test\/t/ +--error 145 +ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; +REPAIR TABLE t; +CHECK TABLE t; +ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; +CHECK TABLE tp; +CHECK TABLE t; +DROP TABLE t, tp; + --echo # test of non partitioned myisam for reference CREATE TABLE t1_will_crash (a INT, KEY (a)) ENGINE=MyISAM; INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); |