diff options
Diffstat (limited to 'mysql-test/suite/parts/t')
160 files changed, 7965 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/t/alter_data_directory_innodb.test b/mysql-test/suite/parts/t/alter_data_directory_innodb.test new file mode 100644 index 00000000..def04e14 --- /dev/null +++ b/mysql-test/suite/parts/t/alter_data_directory_innodb.test @@ -0,0 +1,47 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_symlink.inc + +--echo # +--echo # MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir +--echo # + +mkdir $MYSQLTEST_VARDIR/tmp/partitions_here; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t ( + a INT NOT NULL +) ENGINE=INNODB +PARTITION BY HASH (a) ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB, + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB +); +INSERT INTO t VALUES (1); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +ALTER TABLE t DROP PRIMARY KEY, ALGORITHM=COPY; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; +SET GLOBAL INNODB_FILE_PER_TABLE=OFF; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +DROP TABLE t; + +rmdir $MYSQLTEST_VARDIR/tmp/partitions_here/test; +rmdir $MYSQLTEST_VARDIR/tmp/partitions_here; diff --git a/mysql-test/suite/parts/t/alter_table.combinations b/mysql-test/suite/parts/t/alter_table.combinations new file mode 100644 index 00000000..d4feae10 --- /dev/null +++ b/mysql-test/suite/parts/t/alter_table.combinations @@ -0,0 +1,2 @@ +[range] +[list] diff --git a/mysql-test/suite/parts/t/alter_table.test b/mysql-test/suite/parts/t/alter_table.test new file mode 100644 index 00000000..9b75add3 --- /dev/null +++ b/mysql-test/suite/parts/t/alter_table.test @@ -0,0 +1,321 @@ +# Permissions don't work with embedded +--source include/not_embedded.inc +--source include/have_partition.inc +--source include/lcase_names.inc +--source suite/parts/inc/engines.inc + +# +# MDEV-22649 SIGSEGV in ha_partition::create_partitioning_metadata on ALTER +# + +set @save_alter_algorithm= @@session.alter_algorithm; +SET SESSION alter_algorithm=4; +CREATE TABLE t1(a INT) engine=myisam PARTITION BY RANGE(a) SUBPARTITION BY KEY(a) (PARTITION p0 VALUES LESS THAN (10) (SUBPARTITION s0,SUBPARTITION s1), PARTITION p1 VALUES LESS THAN (20) (SUBPARTITION s2,SUBPARTITION s3)); +show create table t1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 ADD COLUMN c INT; +DROP table if exists t1; +set @@session.alter_algorithm= @save_alter_algorithm; + + +# +# MDEV-22804 SIGSEGV in ha_partition::create_partitioning_metadata | +# ERROR 1507 (HY000): Error in list of partitions to DROP +# + +CREATE TABLE t1 (a INT) PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (PARTITION p VALUES LESS THAN (5) (SUBPARTITION sp, SUBPARTITION sp1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION sp2, SUBPARTITION sp3)); +ALTER TABLE t1 DROP PARTITION p; +DROP TABLE if exists t1; + +# +# MDEV-23357 Server crashes in Sql_cmd_alter_table_exchange_partition::exchange_partition +# +CREATE TABLE t1 (i INT); +CREATE VIEW v1 as SELECT * FROM t1; +CREATE TABLE t2 (i INT); +--error ER_CHECK_NO_SUCH_TABLE +ALTER TABLE v1 EXCHANGE PARTITION p2 WITH TABLE t2 ; +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-22165 CONVERT PARTITION: move in partition from existing table +--echo # +create or replace table tp1 (a int); +create or replace table t1 (a int) +partition by hash (a) partitions 2; +--error ER_ONLY_ON_RANGE_LIST_PARTITION +alter table t1 convert table tp1 to partition p2; + +create or replace table t1 (a int) +partition by range (a) +(partition p0 values less than (0)); + +--error ER_NO_SUCH_TABLE +alter table t1 convert table non_existent to partition p1 values less than (10); +alter table t1 convert table tp1 to partition p1 values less than (10); +--error ER_NO_SUCH_TABLE +show create table tp1; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; + +create table tp2 (x int); +--error ER_TABLES_DIFFERENT_METADATA +alter table t1 convert table tp2 to partition p2 values less than (20); +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table tp2; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; + +create or replace table tp2 (a int); +insert tp2 values (1), (15), (17); +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table t1 convert table tp2 to partition p2 values less than (20); +delete from tp2; +insert tp2 values (15), (1), (17); +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table t1 convert table tp2 to partition p2 values less than (20); +delete from tp2; +insert tp2 values (15), (17), (1); +--error ER_ROW_DOES_NOT_MATCH_PARTITION +alter table t1 convert table tp2 to partition p2 values less than (20); +delete from tp2; +insert tp2 values (15), (17); +alter table t1 convert table tp2 to partition p2 values less than (20); +--error ER_NO_SUCH_TABLE +show create table tp2; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; +select * from t1 partition (p2); + +create or replace table t1 (a int) +partition by range (a) ( +p0 values less than (0), +pn values less than (30)); +insert into t1 values (1); +create or replace table tp1 (a int); +insert into tp1 values (2); +# TODO: would be good to automatically detect order of partitions, +# as well as move the data from succeeding partitions (ADD PARTITION FR). +--error ER_RANGE_NOT_INCREASING_ERROR +alter table t1 convert table tp1 to partition p1 values less than (10); + +drop tables t1, tp1; + + +--echo # +--echo # MDEV-22166 CONVERT PARTITION: move out partition into a table +--echo # + +create or replace table t1 (x int); +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +alter table t1 convert partition p1 to table tp1; + +create or replace table t1 (x int) +partition by hash(x) partitions 2; +--error ER_ONLY_ON_RANGE_LIST_PARTITION +alter table t1 convert partition p1 to table tp1; + +create or replace table t1 (x int) +partition by key(x) partitions 2; +--error ER_ONLY_ON_RANGE_LIST_PARTITION +alter table t1 convert partition p1 to table tp1; + +create or replace table t1 (x int) +partition by range(x) +subpartition by hash(x) subpartitions 3 ( + partition p1 values less than (10), + partition pn values less than maxvalue); +--error ER_PARTITION_CONVERT_SUBPARTITIONED +alter table t1 convert partition p1 to table p1; +--error ER_PARTITION_DOES_NOT_EXIST +alter table t1 convert partition p1sp0 to table p1; + +create or replace table t1 (x int) +partition by range(x) ( + partition p1 values less than (10)); +--error ER_DROP_LAST_PARTITION +alter table t1 convert partition p1 to table tp1; + +--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING +create or replace temporary table t1 (x int) +partition by range(x) ( + partition p0 values less than (10), + partition pn values less than maxvalue); + +if ($MTR_COMBINATION_RANGE) +{ + create or replace table t1 (x int) + partition by range(x) ( + partition p1 values less than (10), + partition p2 values less than (20), + partition p3 values less than (30), + partition p4 values less than (40), + partition p5 values less than (50), + partition pn values less than maxvalue); +} + +if ($MTR_COMBINATION_LIST) +{ + create or replace table t1 (x int) + partition by list(x) ( + partition p1 values in (2, 3, 4), + partition p2 values in (12, 13, 14), + partition p3 values in (22, 23, 24), + partition p4 values in (32, 33, 34), + partition p5 values in (42, 43, 44), + partition pn values in (52, 53, 54)); +} + +insert into t1 values (2), (12), (22), (32), (42), (52); + +create or replace table tp2 (y int); +insert tp2 values (88); +# Multiple ALTER PARTITION statements are not possible +--error ER_PARSE_ERROR +alter table t1 convert partition p2 to table tp2, drop partition p3; +# TODO: probably no need in such specific codes, should be ER_PARTITION_NON_EXISTENT +--error ER_PARTITION_DOES_NOT_EXIST +alter table t1 convert partition p00 to table tp00; +# Better error here is ER_PARTITION_DOES_NOT_EXIST, +# but mysql_alter_table() works checks new table before anything else. +# So, looks like no big reason to change anything here. +--error ER_TABLE_EXISTS_ERROR +alter table t1 convert partition p00 to table tp2; +--error ER_TABLE_EXISTS_ERROR +alter table t1 convert partition p2 to table tp2; +--delimiter $ +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger tr1 before update on t1 for each row +begin + alter table t1 convert partition p2 to table tp2; +end$ +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function f1() returns int +begin + alter table t1 convert partition p2 to table tp2; +end$ +--delimiter ; +select * from tp2; +drop table tp2; +alter table t1 convert partition p2 to table tp2; + +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table tp2; +select * from tp2; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; +select * from t1 order by x; + +--error ER_BAD_DB_ERROR +alter table t1 convert partition p3 to table inexistent.tp3; +create database EXISTENT; +alter table t1 convert partition p3 to table EXISTENT.TP3; + +# The only way to put `` into var... +--let $tp3=`select '`TP3`'` +if ($MTR_COMBINATION_LCASE1) +{ + --let $tp3= `select '`tp3`'` +} +--replace_result $engine X ' PAGE_CHECKSUM=1' '' $tp3 `TP3`-ok +show create table EXISTENT.TP3; +select * from EXISTENT.TP3 order by x; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; +select * from t1 order by x; + +--echo # LOCK TABLES +lock tables t1 write; +alter table t1 convert partition p4 to table tp4; +# TODO: lock table tp4 in ALTER TABLE, otherwise there is no +# guarantee in data consistency between t1 and tp4 +--error ER_TABLE_NOT_LOCKED +show create table tp4; + +unlock tables; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table tp4; +select * from tp4; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; +select * from t1 order by x; + +--echo # PS +prepare stmt from 'alter table t1 convert partition p5 to table tp5'; +execute stmt; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table tp5; +select * from tp5; +--replace_result $engine X ' PAGE_CHECKSUM=1' '' +show create table t1; +select * from t1 order by x; +drop table tp5; +--error ER_PARTITION_DOES_NOT_EXIST +execute stmt; +--error ER_PARTITION_DOES_NOT_EXIST +execute stmt; +drop prepare stmt; + +--echo # Privileges +create user alan; +grant usage on *.* to alan; +grant create, insert, drop on test.* to alan; +--connect alan,localhost,alan,,test +show grants for current_user; +--error ER_TABLEACCESS_DENIED_ERROR +alter table t1 convert partition p1 to table tp1; +--connection default +revoke all on test.* from alan; +grant create, insert, alter on test.* to alan; +--connection alan +use test; +show grants for current_user; +--error ER_TABLEACCESS_DENIED_ERROR +alter table t1 convert partition p1 to table tp1; +--connection default +revoke all on test.* from alan; +grant create, drop, alter on test.* to alan; +--connection alan +use test; +show grants for current_user; +--error ER_TABLEACCESS_DENIED_ERROR +alter table t1 convert partition p1 to table tp1; +--connection default +revoke all on test.* from alan; +grant insert, drop, alter on test.* to alan; +--connection alan +use test; +show grants for current_user; +--error ER_TABLEACCESS_DENIED_ERROR +alter table t1 convert partition p1 to table tp1; +--connection default +grant create, insert, drop, alter on test.* to alan; +--connection alan +use test; +show grants for current_user; +alter table t1 convert partition p1 to table tp1; +--disconnect alan +--connection default + +drop database EXISTENT; +drop user alan; +drop tables t1, tp1, tp2, tp4; + +--echo # +--echo # MDEV-31014 Database privileges are insufficient for CONVERT TABLE TO PARTITION +--echo # +create database db; +create user u@localhost; +grant all on db.* to u@localhost; + +--connect (con1,localhost,u,,db) +create table t1 (a int) partition by range(a) (p1 values less than (100), p2 values less than (1000)); +alter table t1 convert partition p2 to table tp; +alter table t1 convert table tp to partition p2 values less than (1000); + +# Cleanup +--disconnect con1 +--connection default +drop user u@localhost; +drop database db; diff --git a/mysql-test/suite/parts/t/backup_log.test b/mysql-test/suite/parts/t/backup_log.test new file mode 100644 index 00000000..50e3cadd --- /dev/null +++ b/mysql-test/suite/parts/t/backup_log.test @@ -0,0 +1,63 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/not_embedded.inc + +connect (con1,localhost,root,,); +BACKUP STAGE START; +connection default; + +--let $part_int=PARTITION BY HASH(a) PARTITIONS 2 +--let $part_date=PARTITION BY HASH(YEAR(a)) PARTITIONS 2 +--source main/backup_log.inc + +--echo # +--echo # Testing backup ddl log for partitioned tables +--echo # + +CREATE TABLE t200(a INT, b INT) ENGINE ARIA TRANSACTIONAL = 1 PAGE_CHECKSUM = 1; +INSERT INTO t200 VALUES (5, 5), (15, 15), (25, 25); +ALTER TABLE t200 PARTITION BY RANGE( a ) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (20), + PARTITION p2 VALUES LESS THAN (30) +); +CREATE TABLE t210(a INT, b INT) ENGINE ARIA TRANSACTIONAL = 1 PAGE_CHECKSUM = 1; +--echo #TODO: echange partitions have not logged yet +ALTER TABLE t200 EXCHANGE PARTITION p2 WITH TABLE t210; +ALTER TABLE t200 DROP PARTITION p0; +ALTER TABLE t200 ADD PARTITION (PARTITION p3 VALUES LESS THAN (40)); +ALTER TABLE t200 + REORGANIZE PARTITION p3 INTO ( + PARTITION n0 VALUES LESS THAN (35), + PARTITION n1 VALUES LESS THAN (45) +); +ALTER TABLE t200 PARTITION BY KEY(a) PARTITIONS 2; +ALTER TABLE t200 PARTITION BY HASH(a) PARTITIONS 8; +ALTER TABLE t200 COALESCE PARTITION 4; +ALTER TABLE t200 + PARTITION BY RANGE (b) + SUBPARTITION BY KEY (a) + SUBPARTITIONS 2 ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (20), + PARTITION p2 VALUES LESS THAN (30), + PARTITION p3 VALUES LESS THAN (MAXVALUE) + ); +ALTER TABLE t200 REMOVE PARTITIONING; +DROP TABLE t200, t210; + +--echo # Test for the case when TDC contains TABLE_SHARE object for the +--echo # certain table, but the table is not opened +CREATE TABLE t220(a INT) ENGINE ARIA PARTITION BY HASH(a) PARTITIONS 2; +--disable_result_log +SELECT VERSION FROM INFORMATION_SCHEMA.tables WHERE + TABLE_SCHEMA = 'test' AND TABLE_NAME = 't220'; +--enable_result_log +DROP TABLE t220; + +--source include/print_ddl_log.inc + +--echo # +--echo # Cleanup +--echo # +disconnect con1; diff --git a/mysql-test/suite/parts/t/backup_log_rocksdb.opt b/mysql-test/suite/parts/t/backup_log_rocksdb.opt new file mode 100644 index 00000000..df675545 --- /dev/null +++ b/mysql-test/suite/parts/t/backup_log_rocksdb.opt @@ -0,0 +1 @@ +--plugin-load=$HA_ROCKSDB_SO diff --git a/mysql-test/suite/parts/t/backup_log_rocksdb.test b/mysql-test/suite/parts/t/backup_log_rocksdb.test new file mode 100644 index 00000000..c5acfa76 --- /dev/null +++ b/mysql-test/suite/parts/t/backup_log_rocksdb.test @@ -0,0 +1,24 @@ +--source include/have_partition.inc +--source include/have_rocksdb.inc +--source include/not_embedded.inc + +# +# Test that dynmaic loaded storage engines also works with partition and logging +# This is will access code in dd_frm_type() that is not acccessed by other tests +# + +connect (con1,localhost,root,,); +BACKUP STAGE START; +connection default; + +--echo # Test partition engine read from .frm +CREATE TABLE t220 (a INT) ENGINE ROCKSDB PARTITION BY KEY(a) PARTITIONS 2; +DROP TABLE t220; + +--source include/print_ddl_log.inc + +--echo # +--echo # Cleanup +--echo # + +disconnect con1; diff --git a/mysql-test/suite/parts/t/cache.test b/mysql-test/suite/parts/t/cache.test new file mode 100644 index 00000000..fcf2ba5d --- /dev/null +++ b/mysql-test/suite/parts/t/cache.test @@ -0,0 +1,22 @@ +# +# Test cases related to row cache +# + +# The server must support partitioning. +--source include/have_partition.inc + +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, f4 INT, KEY (f4), + KEY (f1,f4,f3,f2) +) PARTITION BY RANGE(f1) ( PARTITION p VALUES LESS THAN MAXVALUE ); + +INSERT IGNORE INTO t1 VALUES +(140,0,0,7),(143,92,NULL,0),(0,0,NULL,154),(NULL,255,117,197),(0,0,NULL,0),(60,0,0,1); + +CREATE TABLE t2 (f INT); +INSERT INTO t2 VALUES (NULL),(35),(NULL),(2); + +--sorted_result +SELECT * FROM t1, t2 WHERE f4 >= f; + +# Cleanup +DROP TABLE t1, t2; diff --git a/mysql-test/suite/parts/t/debug_innodb_crash-master.opt b/mysql-test/suite/parts/t/debug_innodb_crash-master.opt new file mode 100644 index 00000000..b7f94e14 --- /dev/null +++ b/mysql-test/suite/parts/t/debug_innodb_crash-master.opt @@ -0,0 +1 @@ +--loose-innodb-file-per-table=1 --loose-skip-stack-trace --skip-core-file --loose-innodb-buffer-pool-size=32M diff --git a/mysql-test/suite/parts/t/debug_innodb_crash.test b/mysql-test/suite/parts/t/debug_innodb_crash.test new file mode 100644 index 00000000..8b71f486 --- /dev/null +++ b/mysql-test/suite/parts/t/debug_innodb_crash.test @@ -0,0 +1,22 @@ +# Partitioning test that require debug features and InnoDB + +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/not_valgrind.inc +--source include/not_embedded.inc + +# Checking with #innodb what this is... +call mtr.add_suppression("InnoDB: Warning: allocated tablespace .*, old maximum was"); +# If there is a crash or failure between the ddl_log is written and the +# operation is completed, mysql will try to drop a not yet created partition +call mtr.add_suppression("table .* does not exist in the InnoDB internal"); +call mtr.add_suppression("InnoDB: Warning: MariaDB is trying to drop table "); + +--let $DATADIR= `SELECT @@datadir;` + +let $engine= 'InnoDB'; + +--echo # Test crash recovery in fast_alter_partition_table. +let $do_crash=1; +--source suite/parts/inc/partition_mgm_crash.inc diff --git a/mysql-test/suite/parts/t/debug_innodb_fail-master.opt b/mysql-test/suite/parts/t/debug_innodb_fail-master.opt new file mode 100644 index 00000000..8f8c9e5f --- /dev/null +++ b/mysql-test/suite/parts/t/debug_innodb_fail-master.opt @@ -0,0 +1 @@ +--loose-innodb-file-per-table=1 --loose-innodb-buffer-pool-size=32M diff --git a/mysql-test/suite/parts/t/debug_innodb_fail.test b/mysql-test/suite/parts/t/debug_innodb_fail.test new file mode 100644 index 00000000..a6922f59 --- /dev/null +++ b/mysql-test/suite/parts/t/debug_innodb_fail.test @@ -0,0 +1,22 @@ +# Partitioning test that require debug features and InnoDB + +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/not_embedded.inc + +# Checking with #innodb what this is... +call mtr.add_suppression("InnoDB: Warning: allocated tablespace .*, old maximum was"); +# If there is a crash or failure between the ddl_log is written and the +# operation is completed, mysql will try to drop a not yet created partition +call mtr.add_suppression("table .* does not exist in the InnoDB internal"); +call mtr.add_suppression("InnoDB: Warning: MariaDB is trying to drop table "); + + +--let $DATADIR= `SELECT @@datadir;` + +let $engine= 'InnoDB'; + +--echo # Test failure recovery in fast_alter_partition_table. +let $do_fail=1; +--source suite/parts/inc/partition_mgm_crash.inc diff --git a/mysql-test/suite/parts/t/debug_myisam_crash-master.opt b/mysql-test/suite/parts/t/debug_myisam_crash-master.opt new file mode 100644 index 00000000..826d7c97 --- /dev/null +++ b/mysql-test/suite/parts/t/debug_myisam_crash-master.opt @@ -0,0 +1 @@ +--loose-skip-stack-trace --skip-core-file --myisam-recover-options=off diff --git a/mysql-test/suite/parts/t/debug_myisam_crash.test b/mysql-test/suite/parts/t/debug_myisam_crash.test new file mode 100644 index 00000000..0b074800 --- /dev/null +++ b/mysql-test/suite/parts/t/debug_myisam_crash.test @@ -0,0 +1,15 @@ +# Partitioning test that require debug features +# including crashing tests. + +--source include/have_debug.inc +--source include/have_partition.inc +--source include/not_valgrind.inc +--source include/not_embedded.inc + +--let $DATADIR= `SELECT @@datadir;` + +let $engine= 'MyISAM'; + +--echo # Test crash in fast_alter_partition_table. +let $do_crash=1; +--source suite/parts/inc/partition_mgm_crash.inc diff --git a/mysql-test/suite/parts/t/debug_myisam_fail-master.opt b/mysql-test/suite/parts/t/debug_myisam_fail-master.opt new file mode 100644 index 00000000..c5f16ccf --- /dev/null +++ b/mysql-test/suite/parts/t/debug_myisam_fail-master.opt @@ -0,0 +1 @@ +--myisam-recover-options=off diff --git a/mysql-test/suite/parts/t/debug_myisam_fail.test b/mysql-test/suite/parts/t/debug_myisam_fail.test new file mode 100644 index 00000000..d4b9a587 --- /dev/null +++ b/mysql-test/suite/parts/t/debug_myisam_fail.test @@ -0,0 +1,14 @@ +# Partitioning test that require debug features +# including crashing tests. + +--source include/have_debug.inc +--source include/have_partition.inc +--source include/not_embedded.inc + +--let $DATADIR= `SELECT @@datadir;` + +let $engine= 'MyISAM'; + +--echo # Test failures in fast_alter_partition_table. +let $do_fail=1; +--source suite/parts/inc/partition_mgm_crash.inc diff --git a/mysql-test/suite/parts/t/engine_defined_part_attributes.test b/mysql-test/suite/parts/t/engine_defined_part_attributes.test new file mode 100644 index 00000000..12450663 --- /dev/null +++ b/mysql-test/suite/parts/t/engine_defined_part_attributes.test @@ -0,0 +1,221 @@ +--echo # +--echo # MDEV-5271 Support engine-defined attributes per partition +--echo # + +--source include/have_partition.inc +--source include/have_innodb.inc + +--echo # partitioned tables + +CREATE TABLE `t1` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ENCRYPTION_KEY_ID=1, + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" ENCRYPTION_KEY_ID=1 +); +SHOW CREATE TABLE `t1`; + +INSERT INTO t1 VALUES (1), (2), (3); +DELETE FROM t1 WHERE id = 1; +UPDATE t1 SET id = 4 WHERE id = 3; +SELECT * FROM t1 WHERE id IN (2, 3); + +DROP TABLE `t1`; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t2` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="YES" ENCRYPTION_KEY_ID=2 PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100), + PARTITION pt2 VALUES LESS THAN MAXVALUE +); + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t3` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2, + PARTITION pt2 VALUES LESS THAN MAXVALUE +); + +CREATE TABLE `t4` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="NO"; +SHOW CREATE TABLE `t4`; + +ALTER TABLE `t4` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO", + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" +); +SHOW CREATE TABLE `t4`; + +ALTER TABLE `t4` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100), + PARTITION pt2 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t4`; + +--error ER_CANT_CREATE_TABLE +ALTER TABLE `t4` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2, + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" +); + +DROP TABLE `t4`; + +--echo # subpartitioned tables + +CREATE TABLE `t5` ( + `id` INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( + PARTITION pt1 VALUES LESS THAN (100), + PARTITION pt2 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t5`; + +DROP TABLE `t5`; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t6` ( + `id` INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) +SUBPARTITIONS 2 ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES", + PARTITION pt2 VALUES LESS THAN MAXVALUE +); + +--error ER_PARSE_ERROR +CREATE TABLE `t7` ( + id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100)( + SUBPARTITION spt1 ENCRYPTED="NO", + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); + +CREATE TABLE `t8` ( + id INT +) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); +SHOW CREATE TABLE `t8`; + +DROP TABLE `t8`; + +CREATE TABLE `t9` ( + id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); +SHOW CREATE TABLE `t9`; + +DROP TABLE `t9`; + +--error ER_CANT_CREATE_TABLE +CREATE TABLE `t10` ( + id INT +) ENGINE=InnoDB PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); + +CREATE TABLE `t11` ( + id INT +) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) +SUBPARTITION BY HASH(id) ( + PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ( + SUBPARTITION spt1, + SUBPARTITION spt2 + ), + PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="NO" ( + SUBPARTITION spt3, + SUBPARTITION spt4 + ) +); +SHOW CREATE TABLE `t11`; + +DROP TABLE `t11`; + +--echo # +--echo # MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values +--echo # + +--let $restart_parameters= --innodb-sys-tablespaces +--source include/restart_mysqld.inc + +CREATE TABLE `t12` ( + id INT +) ENGINE=InnoDB PARTITION BY HASH(id) +( + pt1 PAGE_COMPRESSED=0 +); +--sorted_result +SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%'; + +ALTER TABLE `t12` ADD PARTITION ( + PARTITION pt2 PAGE_COMPRESSED=1 +); +--sorted_result +SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%'; + +ALTER TABLE `t12` ADD PARTITION ( + PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3 +); +--sorted_result +SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%'; + +DROP TABLE `t12`; + +CREATE TABLE `t13` ( + `id` INT +) ENGINE=InnoDB PAGE_COMPRESSED=1 PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=0, + PARTITION pt2 VALUES LESS THAN (200) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3, + PARTITION pt3 VALUES LESS THAN MAXVALUE +); +SHOW CREATE TABLE `t13`; +--sorted_result +SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%'; + +ALTER TABLE `t13` PARTITION BY RANGE(id) ( + PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3, + PARTITION pt2 VALUES LESS THAN (200), + PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0 +); +SHOW CREATE TABLE `t13`; +--sorted_result +SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%'; + +DROP TABLE `t13`; diff --git a/mysql-test/suite/parts/t/fulltext.test b/mysql-test/suite/parts/t/fulltext.test new file mode 100644 index 00000000..c591e660 --- /dev/null +++ b/mysql-test/suite/parts/t/fulltext.test @@ -0,0 +1,9 @@ +--source include/have_partition.inc + +# +# MDEV-17627 Assertion `inited==RND' failed in handler::ha_rnd_end() upon actions on partitioned table with FTS +# +create table t1 (i int, f1 varchar(512), f2 varchar(512), fulltext (f1)) engine=myisam partition by hash (i); +select * from t1 where match (f1) against ('foo'); +select * from t1 where match (f2) against ('bar' in boolean mode) ; +drop table t1; diff --git a/mysql-test/suite/parts/t/insert_ignore-5421.test b/mysql-test/suite/parts/t/insert_ignore-5421.test new file mode 100644 index 00000000..889f2cca --- /dev/null +++ b/mysql-test/suite/parts/t/insert_ignore-5421.test @@ -0,0 +1,12 @@ +# +# MDEV-5421 Assertion `! is_set()' fails on INSERT IGNORE when a table has no partition for a value +# + +--source include/have_partition.inc +CREATE TABLE t1 (i INT) ENGINE=MyISAM +PARTITION BY RANGE (i) ( + PARTITION p00 VALUES LESS THAN (1), + PARTITION p01 VALUES LESS THAN (2) +); +INSERT IGNORE INTO t1 VALUES (3); +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/longname.test b/mysql-test/suite/parts/t/longname.test new file mode 100644 index 00000000..ab6137d5 --- /dev/null +++ b/mysql-test/suite/parts/t/longname.test @@ -0,0 +1,66 @@ +source include/have_innodb.inc; +source include/have_partition.inc; +# The absolute path names in the embedded server hit the limit earlier. +source include/not_embedded.inc; +set names utf8; + +create database mysqltest1; +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 ( + id int(10) unsigned NOT NULL, + id2 int(10) unsigned NOT NULL, + PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) + SUBPARTITION BY HASH ( id2 ) + SUBPARTITIONS 2 ( + PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); + +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; + +--error ER_PATH_LENGTH +CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé ( + id int(10) unsigned NOT NULL, + id2 int(10) unsigned NOT NULL, + PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) + SUBPARTITION BY HASH ( id2 ) + SUBPARTITIONS 2 ( + PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); + +SET @file_per_table=@@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table=0; + +CREATE TABLE mysqltest1.t1 (a INT) ENGINE=INNODB +PARTITION BY RANGE (a) SUBPARTITION BY HASH(a) +(PARTITION `$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$` + VALUES LESS THAN (10) + (SUBPARTITION + `--------------------------abcdef0123456789abcdef0123456789abcdef`, + SUBPARTITION + `0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef`) +); + +SET GLOBAL innodb_file_per_table=@file_per_table; + +SHOW CREATE TABLE mysqltest1.t1; +INSERT INTO mysqltest1.t1 VALUES(1); + +--error ER_WRONG_TABLE_NAME +DROP TABLE mysqltest1.`#mysql50#t1#P#@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024@0024#SP#0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef`; + +--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING +ALTER TABLE mysqltest1.t1 ADD FOREIGN KEY (a) REFERENCES +mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890; +--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING +ALTER TABLE +mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 +ADD FOREIGN KEY (a) REFERENCES mysqltest1.t1; + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE NAME LIKE 'mysqltest1%'; + +drop database mysqltest1; diff --git a/mysql-test/suite/parts/t/optimizer.test b/mysql-test/suite/parts/t/optimizer.test new file mode 100644 index 00000000..0cc326da --- /dev/null +++ b/mysql-test/suite/parts/t/optimizer.test @@ -0,0 +1,49 @@ +# The server must support partitioning. +--source include/have_partition.inc + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +--echo # +--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range +--echo # endpoints +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY ( a, b ) +) PARTITION BY HASH (a) PARTITIONS 1; + +CREATE TABLE t2 ( + a INT, + b INT, + KEY ( a, b ) +); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); + +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; +INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; +INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; + +INSERT INTO t2 SELECT * FROM t1; + +--echo # plans should be identical +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; + +--disable_ps2_protocol +FLUSH status; +SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; + +FLUSH status; +SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; +--enable_ps2_protocol + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/parts/t/part_blocked_sql_func_innodb.test b/mysql-test/suite/parts/t/part_blocked_sql_func_innodb.test new file mode 100644 index 00000000..12f5aabf --- /dev/null +++ b/mysql-test/suite/parts/t/part_blocked_sql_func_innodb.test @@ -0,0 +1,42 @@ +################################################################################ +# t/partition_blocked_sql_funcs_innodb.test # +# # +# Purpose: # +# Tests around sql functions # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-11-23 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'INNODB'; + +#------------------------------------------------------------------------------# +--source suite/parts/inc/part_blocked_sql_funcs_main.inc diff --git a/mysql-test/suite/parts/t/part_blocked_sql_func_myisam.test b/mysql-test/suite/parts/t/part_blocked_sql_func_myisam.test new file mode 100644 index 00000000..6478f8c9 --- /dev/null +++ b/mysql-test/suite/parts/t/part_blocked_sql_func_myisam.test @@ -0,0 +1,41 @@ +################################################################################ +# t/partition_sql_funcs_myisam.test # +# # +# Purpose: # +# Tests around sql functions # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-11-22 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; + +#------------------------------------------------------------------------------# +--source suite/parts/inc/part_blocked_sql_funcs_main.inc diff --git a/mysql-test/suite/parts/t/part_ctype_tis620.test b/mysql-test/suite/parts/t/part_ctype_tis620.test new file mode 100644 index 00000000..2e5bcd65 --- /dev/null +++ b/mysql-test/suite/parts/t/part_ctype_tis620.test @@ -0,0 +1,52 @@ +--source include/have_partition.inc +--source include/have_tis620.inc + +--echo # +--echo # MDEV-27768 MDEV-25440: Assertion `(cs->state & 0x20000) == 0' failed in my_strnncollsp_nchars_generic_8bit +--echo # + +--error ER_RANGE_NOT_INCREASING_ERROR +CREATE TABLE t1 ( + a CHAR +) COLLATE=tis620_thai_nopad_ci +PARTITION BY RANGE COLUMNS (a) +( + PARTITION p0 VALUES LESS THAN (''), + PARTITION p VALUES LESS THAN ('') +); + +--error ER_RANGE_NOT_INCREASING_ERROR +CREATE TABLE t1 ( + a CHAR +) COLLATE=tis620_thai_nopad_ci +PARTITION BY RANGE COLUMNS (a) +( + PARTITION p0 VALUES LESS THAN (''), + PARTITION p VALUES LESS THAN (' ') +); + +--error ER_RANGE_NOT_INCREASING_ERROR +CREATE TABLE t1 ( + a CHAR +) COLLATE=tis620_thai_nopad_ci +PARTITION BY RANGE COLUMNS (a) +( + PARTITION p0 VALUES LESS THAN (' '), + PARTITION p VALUES LESS THAN ('') +); + + +CREATE TABLE t1 +( + id INT NOT NULL, + data VARCHAR(2), + KEY data_id (data(1),id) +) COLLATE tis620_thai_nopad_ci ENGINE=MyISAM +PARTITION BY RANGE (id) +( + PARTITION p10 VALUES LESS THAN (10), + PARTITION p20 VALUES LESS THAN (20) +); +INSERT INTO t1 VALUES (4, 'ab'), (14, 'ab'), (19,''),(9,'') ; +SELECT id FROM t1 WHERE data='' ORDER BY id; +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/part_ctype_utf32.test b/mysql-test/suite/parts/t/part_ctype_utf32.test new file mode 100644 index 00000000..12aa1fb3 --- /dev/null +++ b/mysql-test/suite/parts/t/part_ctype_utf32.test @@ -0,0 +1,28 @@ +################################################################################ +# t/partition_ctype_utf32.test # +# # +# Purpose: # +# Tests for partitions + UTF32 # +# # +#------------------------------------------------------------------------------# +# Original Author: Alexander Barkov # +# Original Date: 2010-08-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +--source include/have_partition.inc +--source include/have_utf32.inc + +--echo # +--echo # Bug#52121 partition by key on utf32 enum field cause debug assertion: (length % 4) == 0 +--echo # + +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( + a enum('a') CHARACTER SET utf32 COLLATE utf32_spanish2_ci +) ENGINE=MYISAM PARTITION BY KEY(a) PARTITIONS 2; +INSERT INTO t1 VALUES ('a'); +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/part_supported_sql_func_innodb.test b/mysql-test/suite/parts/t/part_supported_sql_func_innodb.test new file mode 100644 index 00000000..20348cc3 --- /dev/null +++ b/mysql-test/suite/parts/t/part_supported_sql_func_innodb.test @@ -0,0 +1,43 @@ +################################################################################ +# t/part_supported_sql_funcs_innodb.test # +# # +# Purpose: # +# Tests which SQL functions are allowed in partinioning clauses with # +# INNODB. # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-11-23 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $do_long_tests= 1; + +# +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#The server nust support the engine +--source include/have_innodb.inc +let $engine= 'INNODB'; + +#------------------------------------------------------------------------------# +--source suite/parts/inc/part_supported_sql_funcs_main.inc diff --git a/mysql-test/suite/parts/t/part_supported_sql_func_myisam.test b/mysql-test/suite/parts/t/part_supported_sql_func_myisam.test new file mode 100644 index 00000000..4fb1e532 --- /dev/null +++ b/mysql-test/suite/parts/t/part_supported_sql_func_myisam.test @@ -0,0 +1,42 @@ +################################################################################ +# t/part_supported_sql_funcs_myisam.test # +# # +# Purpose: # +# Tests which SQL functions are allowed in partinioning clauses with # +# MYISAM. # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-11-22 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $do_long_tests= 1; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; + +#------------------------------------------------------------------------------# +--source suite/parts/inc/part_supported_sql_funcs_main.inc diff --git a/mysql-test/suite/parts/t/partition_alter1_1_2_innodb.test b/mysql-test/suite/parts/t/partition_alter1_1_2_innodb.test new file mode 100644 index 00000000..5a6488df --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter1_1_2_innodb.test @@ -0,0 +1,91 @@ +################################################################################ +# t/partition_alter1_1_2_innodb.test # +# # +# Purpose: # +# Tests around ADD/DROP PRIMARY KEY and/or UNIQUE INDEX # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: pcrews # +# Change Date: 2008-05-05 # +# Change: Split up original partition_alter1.test file to better accommodate # +# PushBuild machines' workloads. Total run time for all components # +# is essentially the same, but max. single run time is significantly # +# reduced # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# Slow running test +--source include/big_test.inc +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter1_1_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter1_1_2_myisam.test b/mysql-test/suite/parts/t/partition_alter1_1_2_myisam.test new file mode 100644 index 00000000..12baa861 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter1_1_2_myisam.test @@ -0,0 +1,84 @@ +################################################################################ +# t/partition_alter1_1_2_myisam.test # +# # +# Purpose: # +# Tests around ADD/DROP PRIMARY KEY and/or UNIQUE INDEX # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: pcrews # +# Change Date: 2008-05-05 # +# Change: Split up original partition_alter1.test file to better accommodate # +# PushBuild machines' workloads. Total run time for all components # +# is essentially the same, but max. single run time is significantly # +# reduced # +################################################################################ +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter1_1_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter1_1_innodb.test b/mysql-test/suite/parts/t/partition_alter1_1_innodb.test new file mode 100644 index 00000000..7f768e57 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter1_1_innodb.test @@ -0,0 +1,87 @@ +# This test takes long time, so only run it with the --big-test mtr-flag. +--source include/big_test.inc +################################################################################ +# t/partition_alter1_1_innodb.test # +# # +# Purpose: # +# Tests around ADD/DROP PRIMARY KEY and/or UNIQUE INDEX # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: pcrews # +# Change Date: 2008-05-05 # +# Change: Split up original partition_alter1.test file to better accommodate # +# PushBuild machines' workloads. Total run time for all components # +# is essentially the same, but max. single run time is significantly # +# reduced # +################################################################################ +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter1_1.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter1_1_myisam.test b/mysql-test/suite/parts/t/partition_alter1_1_myisam.test new file mode 100644 index 00000000..43fa0885 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter1_1_myisam.test @@ -0,0 +1,85 @@ +################################################################################ +# t/partition_alter1_1_myisam.test # +# # +# Purpose: # +# Tests around ADD/DROP PRIMARY KEY and/or UNIQUE INDEX # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: pcrews # +# Change Date: 2008-05-05 # +# Change: Split up original partition_alter1.test file to better accommodate # +# PushBuild machines' workloads. Total run time for all components # +# is essentially the same, but max. single run time is significantly # +# reduced # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter1_1.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter1_2_innodb.test b/mysql-test/suite/parts/t/partition_alter1_2_innodb.test new file mode 100644 index 00000000..5293e547 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter1_2_innodb.test @@ -0,0 +1,94 @@ +################################################################################ +# t/partition_alter1_2_innodb.test # +# # +# Purpose: # +# Tests around ADD/DROP PRIMARY KEY and/or UNIQUE INDEX # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: pcrews # +# Change Date: 2008-05-05 # +# Change: Split up original partition_alter1.test file to better accommodate # +# PushBuild machines' workloads. Total run time for all components # +# is essentially the same, but max. single run time is significantly # +# reduced # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=0; + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter1_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc + +SET GLOBAL innodb_stats_persistent=@save_stats_persistent; diff --git a/mysql-test/suite/parts/t/partition_alter1_2_myisam.test b/mysql-test/suite/parts/t/partition_alter1_2_myisam.test new file mode 100644 index 00000000..8df05fdc --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter1_2_myisam.test @@ -0,0 +1,85 @@ +################################################################################ +# t/partition_alter1_2_myisam.test # +# # +# Purpose: # +# Tests around ADD/DROP PRIMARY KEY and/or UNIQUE INDEX # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: pcrews # +# Change Date: 2008-05-05 # +# Change: Split up original partition_alter1.test file to better accommodate # +# PushBuild machines' workloads. Total run time for all components # +# is essentially the same, but max. single run time is significantly # +# reduced # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter1_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_1_1_innodb.test b/mysql-test/suite/parts/t/partition_alter2_1_1_innodb.test new file mode 100644 index 00000000..a772122c --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_1_1_innodb.test @@ -0,0 +1,90 @@ +################################################################################ +# t/partition_alter2_1_1_innodb.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; +# only do the first part +let $only_part_1= 1; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_1.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_1_2_innodb.test b/mysql-test/suite/parts/t/partition_alter2_1_2_innodb.test new file mode 100644 index 00000000..6448a8ac --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_1_2_innodb.test @@ -0,0 +1,85 @@ +################################################################################ +# t/partition_alter2_1_2_innodb.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; +# only do the second part +let $only_part_2= 1; + +# The server must support partitioning. +--source include/have_partition.inc + +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_1.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_1_maria.test b/mysql-test/suite/parts/t/partition_alter2_1_maria.test new file mode 100644 index 00000000..47f6c4cb --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_1_maria.test @@ -0,0 +1,86 @@ +################################################################################ +# t/partition_alter2_1_maria.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# Aria branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT Aria SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +# Lots of ALTER TABLE, slow in Maria (disk syncs), takes ~15 minutes +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_maria.inc +let $engine= 'Aria'; + +##### Execute the test of "table" files +# Aria has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK Aria treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_1.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_1_myisam.test b/mysql-test/suite/parts/t/partition_alter2_1_myisam.test new file mode 100644 index 00000000..11ec9b51 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_1_myisam.test @@ -0,0 +1,84 @@ +################################################################################ +# t/partition_alter2_1_myisam.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_1.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_2_1_innodb.test b/mysql-test/suite/parts/t/partition_alter2_2_1_innodb.test new file mode 100644 index 00000000..4ddfb84d --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_2_1_innodb.test @@ -0,0 +1,87 @@ +################################################################################ +# t/partition_alter2_2_1_innodb.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; +# only do the first part +let $only_part_1= 1; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_2_2_innodb.test b/mysql-test/suite/parts/t/partition_alter2_2_2_innodb.test new file mode 100644 index 00000000..e3544c38 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_2_2_innodb.test @@ -0,0 +1,85 @@ +################################################################################ +# t/partition_alter2_2_2_innodb.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; +# only do the second part +let $only_part_2= 1; + +# The server must support partitioning. +--source include/have_partition.inc + +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_2_maria.test b/mysql-test/suite/parts/t/partition_alter2_2_maria.test new file mode 100644 index 00000000..e8172de3 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_2_maria.test @@ -0,0 +1,86 @@ +################################################################################ +# t/partition_alter2_2_maria.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# Aria branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT Aria SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +# Lots of ALTER TABLE, slow in Maria (disk syncs), takes ~15 minutes +--source include/big_test.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_maria.inc +let $engine= 'Aria'; + +##### Execute the test of "table" files +# MAARIA has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK Aria treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter2_2_myisam.test b/mysql-test/suite/parts/t/partition_alter2_2_myisam.test new file mode 100644 index 00000000..8fbb943a --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter2_2_myisam.test @@ -0,0 +1,84 @@ +################################################################################ +# t/partition_alter2_2_myisam.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter2_2.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter3_innodb.test b/mysql-test/suite/parts/t/partition_alter3_innodb.test new file mode 100644 index 00000000..d86c0e11 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter3_innodb.test @@ -0,0 +1,86 @@ +################################################################################ +# t/partition_alter3_innodb.test # +# # +# Purpose: # +# Tests around Alter column used in partitioning function # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-04-24 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on --list_files, which doesn't work correctly in embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=0; + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter3.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc + +SET GLOBAL innodb_stats_persistent=@save_stats_persistent; diff --git a/mysql-test/suite/parts/t/partition_alter3_myisam.test b/mysql-test/suite/parts/t/partition_alter3_myisam.test new file mode 100644 index 00000000..37271698 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter3_myisam.test @@ -0,0 +1,80 @@ +################################################################################ +# t/partition_alter3_myisam.test # +# # +# Purpose: # +# Tests partition management commands on HASH and KEY partitioned table # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-04-11 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on --list_files, which doesn't work correctly in embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter3.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter4_innodb.test b/mysql-test/suite/parts/t/partition_alter4_innodb.test new file mode 100644 index 00000000..dcbab787 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter4_innodb.test @@ -0,0 +1,89 @@ +################################################################################ +# t/partition_alter4_innodb.test # +# # +# Purpose: # +# Tests of ALTER ... ANALYZE/OPTIMIZE/... PARTITION # +# in InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-07-27 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +# This test takes long time, so only run it with the --big mtr-flag. +--source include/big_test.inc + +# Valgrind is to slow for this test +--source include/not_valgrind.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter4.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter4_myisam.test b/mysql-test/suite/parts/t/partition_alter4_myisam.test new file mode 100644 index 00000000..652bf801 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter4_myisam.test @@ -0,0 +1,84 @@ +################################################################################ +# t/partition_alter4_myisam.test # +# # +# Purpose: # +# Tests of ALTER ... ANALYZE/OPTIMIZE/... PARTITION # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-07-27 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# Slow running test +--source include/big_test.inc +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_alter4.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_alter_innodb.test b/mysql-test/suite/parts/t/partition_alter_innodb.test new file mode 100644 index 00000000..844b2084 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter_innodb.test @@ -0,0 +1,19 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--let $engine=InnoDB +--disable_query_log +SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; +SET GLOBAL innodb_read_only_compressed=OFF; +--enable_query_log +--source inc/part_alter_values.inc +--disable_query_log +SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; +--enable_query_log +--echo # +--echo # MDEV-28079 Shutdown hangs after altering innodb partition fts table +--echo # +CREATE TABLE t1(f1 INT, f2 CHAR(100))ENGINE=InnoDB PARTITION BY HASH(f1) PARTITIONS 2; +ALTER TABLE t1 ADD FULLTEXT(f2); +--source ../innodb/include/wait_all_purged.inc +DROP TABLE t1; +--echo # End of 10.6 tests diff --git a/mysql-test/suite/parts/t/partition_alter_instant.test b/mysql-test/suite/parts/t/partition_alter_instant.test new file mode 100644 index 00000000..3d798791 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter_instant.test @@ -0,0 +1,43 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/have_innodb_16k.inc + +# MDEV-21832 FORCE all partition to rebuild if any one of the +# partition does rebuild +CREATE TABLE t1 ( + id INT NOT NULL, + name VARCHAR(30))ENGINE=InnoDB ROW_FORMAT=COMPACT CHARACTER SET=latin1 + PARTITION BY RANGE (id) ( + PARTITION p0 VALUES LESS THAN (50), + PARTITION p1 VALUES LESS THAN (MAXVALUE) +); + +INSERT INTO t1(id, name) VALUES(16, 'Me'), (337, 'ROFL'); + +--echo # Add and drop 31 Instant columns +--disable_query_log +let $i = 1; +while ($i < 32) { +--eval ALTER TABLE t1 ADD COLUMN col$i VARCHAR(255) NOT NULL DEFAULT repeat('a', 255); +inc $i; +} + +let $i = 31; +while ($i > 0) { +--eval ALTER TABLE t1 DROP COLUMN col$i +dec $i; +} +--enable_query_log + +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +ALTER TABLE t2 REMOVE PARTITIONING; +ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; +SHOW CREATE TABLE t2; +SET ALTER_ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 ADD COLUMN col1 VARCHAR(255) NOT NULL DEFAULT repeat('a', 255); +SET ALTER_ALGORITHM=INPLACE; +ALTER TABLE t1 ADD COLUMN col1 VARCHAR(255) NOT NULL DEFAULT repeat('a', 255); +DROP TABLE t1, t2; +SET ALTER_ALGORITHM=DEFAULT; diff --git a/mysql-test/suite/parts/t/partition_alter_maria.test b/mysql-test/suite/parts/t/partition_alter_maria.test new file mode 100644 index 00000000..e0b92563 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter_maria.test @@ -0,0 +1,28 @@ +# +# MDEV-13937 Aria engine: Internal Error 160 after partition handling +# +source include/have_partition.inc; +create table t1 ( + pk bigint not null auto_increment, + dt datetime default null, + unique (pk, dt) +) engine=aria row_format=dynamic + partition by range columns(dt) ( + partition `p20171231` values less than ('2017-12-31'), + partition `p20181231` values less than ('2018-12-31') +); +insert into t1 values (1,'2017-09-28 15:12:00'); +select * from t1; +alter table t1 drop partition p20181231; +select * from t1; +drop table t1; + +# +# MDEV-13982 Server crashes in in ha_partition::engine_name +# +create table t1 (a int) engine=Aria transactional=1 partition by hash(a) partitions 2; +show create table t1; +drop table t1; + +--let $engine=Aria +--source inc/part_alter_values.inc diff --git a/mysql-test/suite/parts/t/partition_alter_myisam.test b/mysql-test/suite/parts/t/partition_alter_myisam.test new file mode 100644 index 00000000..d3abb884 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter_myisam.test @@ -0,0 +1,24 @@ +--source include/have_partition.inc +--let $engine=MyISAM +--source inc/part_alter_values.inc + +# +# MDEV-14026 ALTER TABLE ... DELAY_KEY_WRITE=1 creates table copy for partitioned MyISAM table with DATA DIRECTORY/INDEX DIRECTORY options +# + +replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR; +--disable_warnings +eval create table t1 ( c1 int, c2 int, c3 varchar(100)) delay_key_write=1 + partition by key(c1) ( + partition p01 data directory = '$MYSQL_TMP_DIR' + index directory = '$MYSQL_TMP_DIR', + partition p02 data directory = '$MYSQL_TMP_DIR' + index directory = '$MYSQL_TMP_DIR'); +--enable_warnings +insert into t1 values (1, 1, repeat('a', 100)); +insert into t1 select rand()*1000, rand()*1000, repeat('b', 100) from t1; +insert into t1 select rand()*1000, rand()*1000, repeat('b', 100) from t1; +insert into t1 select rand()*1000, rand()*1000, repeat('b', 100) from t1; +alter online table t1 delay_key_write=0; +alter online table t1 delay_key_write=1; +drop table t1; diff --git a/mysql-test/suite/parts/t/partition_auto_increment_archive.test b/mysql-test/suite/parts/t/partition_auto_increment_archive.test new file mode 100644 index 00000000..e99a0b23 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test @@ -0,0 +1,43 @@ +################################################################################ +# t/partition_auto_increment_archive.test # +# # +# Purpose: # +# Tests around auto increment column # +# Archive branch # +# # +#------------------------------------------------------------------------------# +# Original Author: MattiasJ # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements +--source include/have_archive.inc +# Archve does not support delete +let $skip_delete= 1; +let $skip_truncate= 1; +let $skip_update= 1; +let $only_ai_pk= 1; +# Bug#45823 Assertion failure in file row/row0mysql.c line 1386 +# Archive does not handle negative autoincrement values correctly +let $skip_negative_auto_inc= 1; + +##### Storage engine to be tested +let $engine= 'Archive'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_auto_increment.inc + diff --git a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test new file mode 100644 index 00000000..f92dc33f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_auto_increment_blackhole.test # +# # +# Purpose: # +# Tests around auto increment column # +# Blackhole branch # +# # +#------------------------------------------------------------------------------# +# Original Author: MattiasJ # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements +--source include/have_blackhole.inc +# Bug#45823 Assertion failure in file row/row0mysql.c line 1386 +# Blackhole does not handle negative autoincrement values correctly +let $skip_negative_auto_inc= 1; + +##### Storage engine to be tested +let $engine= 'Blackhole'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_auto_increment.inc + diff --git a/mysql-test/suite/parts/t/partition_auto_increment_innodb.test b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test new file mode 100644 index 00000000..4e968d87 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test @@ -0,0 +1,35 @@ +################################################################################ +# t/partition_auto_increment_innodb.test # +# # +# Purpose: # +# Tests around auto increment column # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: MattiasJ # +# Original Date: 2008-02-12 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'InnoDB'; +--source include/have_innodb.inc + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_auto_increment.inc + diff --git a/mysql-test/suite/parts/t/partition_auto_increment_maria.test b/mysql-test/suite/parts/t/partition_auto_increment_maria.test new file mode 100644 index 00000000..2a77a9e4 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_maria.test @@ -0,0 +1,35 @@ +################################################################################ +# t/partition_auto_increment_maria.test # +# # +# Purpose: # +# Tests around auto increment column # +# MARIA branch # +# # +#------------------------------------------------------------------------------# +# Original Author: MattiasJ # +# Original Date: 2008-02-12 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MARIA SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_maria.inc +let $engine= 'Aria'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_auto_increment.inc + diff --git a/mysql-test/suite/parts/t/partition_auto_increment_max.test b/mysql-test/suite/parts/t/partition_auto_increment_max.test new file mode 100644 index 00000000..74e61391 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_max.test @@ -0,0 +1,12 @@ +--source include/have_partition.inc +--source include/have_log_bin.inc + +CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 2; +INSERT INTO t1 VALUES (NULL),(NULL); + +--error ER_DUP_ENTRY +UPDATE t1 SET pk = 2147483647; +--error HA_ERR_AUTOINC_ERANGE +REPLACE INTO t1 VALUES (NULL); + +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/partition_auto_increment_memory.test b/mysql-test/suite/parts/t/partition_auto_increment_memory.test new file mode 100644 index 00000000..585a75cc --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_memory.test @@ -0,0 +1,34 @@ +################################################################################ +# t/partition_auto_increment_memory.test # +# # +# Purpose: # +# Tests around auto increment column # +# Memory branch # +# # +#------------------------------------------------------------------------------# +# Original Author: MattiasJ # +# Original Date: 2008-02-12 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'Memory'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_auto_increment.inc + diff --git a/mysql-test/suite/parts/t/partition_auto_increment_myisam.test b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test new file mode 100644 index 00000000..2e3f49d5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test @@ -0,0 +1,34 @@ +################################################################################ +# t/partition_auto_increment_myisam.test # +# # +# Purpose: # +# Tests around auto increment column # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: MattiasJ # +# Original Date: 2008-02-12 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_auto_increment.inc + diff --git a/mysql-test/suite/parts/t/partition_basic_innodb.test b/mysql-test/suite/parts/t/partition_basic_innodb.test new file mode 100644 index 00000000..032db636 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_basic_innodb.test @@ -0,0 +1,87 @@ +#Considering the time taken for this test on slow solaris platforms making it a big test +--source include/big_test.inc +# Skiping this test from Valgrind execution as per Bug-14627884 +--source include/not_valgrind.inc +################################################################################ +# t/partition_basic_innodb.test # +# # +# Purpose: # +# Tests around Create Partitioned table # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +# Does not work with --embedded +--source include/not_embedded.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... But only .frm file +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_basic.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_basic_myisam.test b/mysql-test/suite/parts/t/partition_basic_myisam.test new file mode 100644 index 00000000..c1dffb3b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_basic_myisam.test @@ -0,0 +1,82 @@ +################################################################################ +# t/partition_basic_myisam.test # +# # +# Purpose: # +# Tests around Create Partitioned table # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_basic.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test b/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test new file mode 100644 index 00000000..c0e3c21a --- /dev/null +++ b/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test @@ -0,0 +1,293 @@ +################################################################################ +# t/partition_basic_innodb.test # +# # +# Purpose: # +# Tests around Create Partitioned table using DATA/INDEX DIR # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: mattiasj # +# Change Date: 2008-02-05 # +# Change: copied it from partition_basic_innodb.test and kept DATA DIR # +# Change Author: mattiasj # +# Change Date: 2008-03-16 # +# Change: Replaced all test with alter -> myisam, since innodb does not support# +# Change Author: Kevin lewis # +# Change Date: 2012-03-02 # +# Change: WL5980 activates DATA DIRECTORY for InnoDB # +################################################################################ + +# NOTE: As of WL5980, InnoDB supports DATA DIRECTORY, but not INDEX DIRECTORY. +# See innodb.innodb-tablespace for tests using partition engine, innodb +# and DATADIRECTORY. The purpose of this test is to show that a +# partitioned table remembers the DATA/INDEX DIR and it is used if +# altered to MyISAM +# +--echo # +--echo # Verify that the DATA/INDEX DIR is stored and used if ALTER to MyISAM. +--echo # +--source include/have_innodb.inc +# The server must support partitioning. +--source include/have_partition.inc +# The server must support symlink for DATA/INDEX DIRECTORY. +--source include/have_symlink.inc +# windows does not support symlink for DATA/INDEX DIRECTORY. +--source include/not_windows.inc + +# Does not work with --embedded +--source include/not_embedded.inc + +let $MYSQLD_DATADIR= `select @@datadir`; + +# These values can change during the test +SET @file_per_table= @@GLOBAL.innodb_file_per_table; +SET @strict_mode= @@SESSION.innodb_strict_mode; + +--mkdir $MYSQLTEST_VARDIR/mysql-test-data-dir +--mkdir $MYSQLTEST_VARDIR/mysql-test-idx-dir + +SET SESSION innodb_strict_mode = ON; + +--echo # +--echo # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON +--echo # +SET GLOBAL innodb_file_per_table = OFF; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error ER_CANT_CREATE_TABLE +eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB +PARTITION BY HASH (c1) ( + PARTITION p0 + DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir', + PARTITION p1 + DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir' +); +--replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR +SHOW WARNINGS; + +--echo # +--echo # InnoDB is different from MyISAM in that it uses a text file +--echo # with an '.isl' extension instead of a symbolic link so that +--echo # the tablespace can be re-located on any OS. Also, instead of +--echo # putting the file directly into the DATA DIRECTORY, +--echo # it adds a folder under it with the name of the database. +--echo # Since strict mode is off, InnoDB ignores the INDEX DIRECTORY +--echo # and it is no longer part of the definition. +--echo # +SET SESSION innodb_strict_mode = OFF; +SET GLOBAL innodb_file_per_table = ON; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB +PARTITION BY HASH (c1) +(PARTITION p0 + DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir', + PARTITION p1 + DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir' +); +SHOW WARNINGS; + +--echo # Verifying .frm, .par, .isl & .ibd files +--echo ---- MYSQLD_DATADIR/test +--list_files $MYSQLD_DATADIR/test +--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test +--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir/test +--echo # The ibd tablespaces should not be directly under the DATA DIRECTORY +--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir +--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir +--echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir +--list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir + +FLUSH TABLES; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; + +--echo # +--echo # Verify that the DATA/INDEX DIRECTORY is stored and used if we +--echo # ALTER TABLE to MyISAM. +--echo # +ALTER TABLE t1 engine=MyISAM; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +--echo # Verifying .frm, .par and MyISAM files (.MYD, MYI) +--list_files $MYSQLD_DATADIR/test +--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir +--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir +--echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir +--list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir + +--echo # +--echo # Now verify that the DATA DIRECTORY is used again if we +--echo # ALTER TABLE back to InnoDB. +--echo # +SET SESSION innodb_strict_mode = ON; +ALTER TABLE t1 engine=InnoDB; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +--echo # Verifying .frm, .par, .isl and InnoDB .ibd files +--echo ---- MYSQLD_DATADIR/test +--list_files $MYSQLD_DATADIR/test +--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir +--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir +--echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir +--list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir +--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test +--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir/test + +DROP TABLE t1; + +--echo # +--echo # MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work +--echo # properly when used with DATA DIRECTORY +--echo # +let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; +let $alt_data_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; +SET GLOBAL innodb_file_per_table = ON; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB PARTITION BY KEY (myid) + ( + PARTITION p0001 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0002 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0003 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0004 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB + ); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t2 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB DATA DIRECTORY = '$data_dir_path'; + +ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +DROP TABLE t1, t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB PARTITION BY RANGE (myid) + ( + PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB + ); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t2 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path'; + +insert into t1 values (1, 'one'); +insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four'); + +select * from t1; +ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t2; +select * from t1; +select * from t2; +DROP TABLE t1, t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB PARTITION BY RANGE (myid) + ( + PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB, + PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB + ); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t2 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB; + +insert into t1 values (1, 'one'); +insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four'); + +select * from t1; +ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t2; +select * from t1; +select * from t2; +DROP TABLE t1, t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB PARTITION BY RANGE (myid) + ( + PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB, + PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB, + PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB, + PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB + ); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t2 +( + myid INT(11) NOT NULL, + myval VARCHAR(10), + PRIMARY KEY (myid) +) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path'; + +insert into t1 values (1, 'one'); +insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four'); + +select * from t1; +ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t2; +select * from t1; +select * from t2; +DROP TABLE t1, t2; +--echo # +--echo # Cleanup +--echo # + +--rmdir $MYSQLTEST_VARDIR/mysql-test-data-dir/test +--rmdir $MYSQLTEST_VARDIR/mysql-test-data-dir +--rmdir $MYSQLTEST_VARDIR/mysql-test-idx-dir + +SET GLOBAL innodb_file_per_table=@file_per_table; +SET SESSION innodb_strict_mode=@strict_mode; diff --git a/mysql-test/suite/parts/t/partition_basic_symlink_myisam.test b/mysql-test/suite/parts/t/partition_basic_symlink_myisam.test new file mode 100644 index 00000000..0a25d1a1 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_basic_symlink_myisam.test @@ -0,0 +1,89 @@ +################################################################################ +# t/partition_basic_myisam.test # +# # +# Purpose: # +# Tests around Create Partitioned table using DATA/INDEX DIR # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: mattiasj # +# Change Date: 2008-02-05 # +# Change: copied it from partition_basic_myisam.test and kept DATA DIR # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc +# The server must support symlink for DATA/INDEX DIRECTORY. +--source include/have_symlink.inc +# windows does not support symlink for DATA/INDEX DIRECTORY. +--source include/not_windows.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; +# The server uses in case of MyISAM symlinking (if available) and the expected +# results fit to symlinking support. +--source include/have_symlink.inc + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_basic_symlink.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_bigint_innodb.test b/mysql-test/suite/parts/t/partition_bigint_innodb.test new file mode 100644 index 00000000..348ee0ad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_bigint_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_bigint_innodb.test # +# # +# Purpose: # +# Tests around integer type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_innodb # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_bigint.inc diff --git a/mysql-test/suite/parts/t/partition_bigint_myisam.test b/mysql-test/suite/parts/t/partition_bigint_myisam.test new file mode 100644 index 00000000..f427ffce --- /dev/null +++ b/mysql-test/suite/parts/t/partition_bigint_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_bigint_myisam.test # +# # +# Purpose: # +# Tests around integer type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_myisam # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### number of rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_bigint.inc diff --git a/mysql-test/suite/parts/t/partition_bit_innodb.test b/mysql-test/suite/parts/t/partition_bit_innodb.test new file mode 100644 index 00000000..9b8eb90b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_bit_innodb.test @@ -0,0 +1,55 @@ +################################################################################ +# t/partition_bit.test # +# # +# Purpose: # +# Tests around bit type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'INNODB'; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_bit.inc diff --git a/mysql-test/suite/parts/t/partition_bit_myisam.test b/mysql-test/suite/parts/t/partition_bit_myisam.test new file mode 100644 index 00000000..d2503c49 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_bit_myisam.test @@ -0,0 +1,55 @@ +################################################################################ +# t/partition_bit.test # +# # +# Purpose: # +# Tests around bit type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; +let $with_partitioning= 1; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_bit.inc + diff --git a/mysql-test/suite/parts/t/partition_char_innodb.test b/mysql-test/suite/parts/t/partition_char_innodb.test new file mode 100644 index 00000000..0fd74dc4 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_char_innodb.test @@ -0,0 +1,53 @@ +################################################################################ +# t/partition_char_innodb.test # +# # +# Purpose: # +# Tests around character types # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_char.inc +--source suite/parts/inc/partition_binary.inc +--source suite/parts/inc/partition_varchar.inc +--source suite/parts/inc/partition_varbinary.inc +--source suite/parts/inc/partition_enum.inc +--source suite/parts/inc/partition_set.inc +--source suite/parts/inc/partition_blob.inc +--source suite/parts/inc/partition_text.inc diff --git a/mysql-test/suite/parts/t/partition_char_myisam.test b/mysql-test/suite/parts/t/partition_char_myisam.test new file mode 100644 index 00000000..63f478ec --- /dev/null +++ b/mysql-test/suite/parts/t/partition_char_myisam.test @@ -0,0 +1,52 @@ +################################################################################ +# t/partition_char_myisam.test # +# # +# Purpose: # +# Tests around character types # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### max rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_char.inc +--source suite/parts/inc/partition_binary.inc +--source suite/parts/inc/partition_varchar.inc +--source suite/parts/inc/partition_varbinary.inc +--source suite/parts/inc/partition_enum.inc +--source suite/parts/inc/partition_set.inc +--source suite/parts/inc/partition_blob.inc +--source suite/parts/inc/partition_text.inc diff --git a/mysql-test/suite/parts/t/partition_datetime_innodb.test b/mysql-test/suite/parts/t/partition_datetime_innodb.test new file mode 100644 index 00000000..ec292fa0 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_datetime_innodb.test @@ -0,0 +1,50 @@ +################################################################################ +# t/partition_datetime_innodb.test # +# # +# Purpose: # +# Tests around integer type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_timestamp.inc +--source suite/parts/inc/partition_date.inc +--source suite/parts/inc/partition_time.inc +--source suite/parts/inc/partition_datetime.inc +--source suite/parts/inc/partition_year.inc diff --git a/mysql-test/suite/parts/t/partition_datetime_myisam.test b/mysql-test/suite/parts/t/partition_datetime_myisam.test new file mode 100644 index 00000000..25e304d5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_datetime_myisam.test @@ -0,0 +1,49 @@ +################################################################################ +# t/partition_datetime_myisam.test # +# # +# Purpose: # +# Tests around integer type # +# MYISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### max rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_timestamp.inc +--source suite/parts/inc/partition_date.inc +--source suite/parts/inc/partition_time.inc +--source suite/parts/inc/partition_datetime.inc +--source suite/parts/inc/partition_year.inc 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 00000000..2f899470 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_debug.test @@ -0,0 +1,163 @@ +# 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 + +# Make sure system tables are not open, as the test will kill the server +# and it will cause corruption errors in the log +FLUSH TABLES; + +# Partitioning test that require debug features + +--echo # +--echo # Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT +--echo # Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(64), KEY(b,a)) + PARTITION BY HASH (a) PARTITIONS 3; + INSERT INTO t1 VALUES (1, "1"), (2, "2"), (3, "3"), (4, "Four"), (5, "Five"), + (6, "Six"), (7, "Seven"), (8, "Eight"), (9, "Nine"); +SET @saved_dbug=@@debug_dbug; +SET SESSION debug_dbug="+d,ha_partition_fail_index_init"; +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +SELECT * FROM t1 WHERE b = "Seven"; +SET SESSION debug_dbug=@saved_dbug; +SELECT * FROM t1 WHERE b = "Seven"; +DROP TABLE t1; + + +--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 + +--echo # +--echo # MDEV-22165 CONVERT PARTITION: move in partition from existing table +--echo # +let $create_statement= create or replace table t1 (x int primary key) + partition by range(x) ( + partition p1 values less than (10), + partition p2 values less than (20), + partition p3 values less than (30)); + +let $create_statement2= create or replace table t2 (x int primary key); +let $insert_statement= insert into t1 values (2), (12), (22); +let $insert_statement2= insert into t2 values (32), (42), (52); + +let $fail_statement= alter table t1 convert table t2 to partition pn values less than maxvalue; + +set @save_dbug=@@debug_dbug; +set session debug_dbug="+d,fail_convert_partition_1"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_2"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_3"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_4"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_5"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_6"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_7"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_8"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_9"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_11"; +--source suite/parts/inc/partition_fail_t2.inc +set session debug_dbug=@save_dbug; + + +--echo # +--echo # MDEV-22166 CONVERT PARTITION: move out partition into a table +--echo # +let $create_statement= create or replace table t1 (x int primary key) + partition by range(x) ( + partition p1 values less than (10), + partition p2 values less than (20), + partition p3 values less than (30), + partition p4 values less than (40), + partition p5 values less than (50), + partition pn values less than maxvalue); + +let $insert_statement= insert into t1 values (2), (12), (22), (32), (42), (52); +let $fail_statement= alter table t1 convert partition p2 to table tp2; + +set @save_dbug=@@debug_dbug; +set session debug_dbug="+d,fail_create_before_create_frm"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_alter_partition_after_write_frm"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,error_convert_partition_00"; +--source suite/parts/inc/partition_fail.inc +#set session debug_dbug=@save_dbug; +#set session debug_dbug="+d,fail_convert_partition_01"; +#--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_1"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_2"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_3"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_4"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_5"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_6"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_7"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_8"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_9"; +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +set session debug_dbug="+d,fail_convert_partition_11"; +--echo # Already finished DDL logging, so tp2 now exists: +--source suite/parts/inc/partition_fail.inc +set session debug_dbug=@save_dbug; +show create table tp2; +select * from tp2; +drop table tp2; diff --git a/mysql-test/suite/parts/t/partition_debug_innodb-master.opt b/mysql-test/suite/parts/t/partition_debug_innodb-master.opt new file mode 100644 index 00000000..b7f94e14 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_debug_innodb-master.opt @@ -0,0 +1 @@ +--loose-innodb-file-per-table=1 --loose-skip-stack-trace --skip-core-file --loose-innodb-buffer-pool-size=32M diff --git a/mysql-test/suite/parts/t/partition_debug_innodb.test b/mysql-test/suite/parts/t/partition_debug_innodb.test new file mode 100644 index 00000000..1a408cef --- /dev/null +++ b/mysql-test/suite/parts/t/partition_debug_innodb.test @@ -0,0 +1,87 @@ +# Partitioning test that require debug features and InnoDB +# including crashing tests. + +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/have_partition.inc +# Don't test this under valgrind, memory leaks will occur. Also SIGKILL may +# not get trough and the test will hang. +--source include/not_valgrind.inc +# Crash tests don't work with embedded +--source include/not_embedded.inc + +--let $DATADIR= `SELECT @@datadir;` + +# Waiting for wl#6723 +if (0) +{ +--echo # +--echo # Bug#12696518/Bug#11766879/60106:DIFF BETWEEN # OF INDEXES IN MYSQL +--echo # VS INNODB, PARTITONING, ON INDEX CREATE +--echo # +CREATE TABLE t1 +(a INT PRIMARY KEY, + b VARCHAR(64)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 3; +INSERT INTO t1 VALUES (0, 'first row'), (1, 'second row'), (2, 'Third row'); +INSERT INTO t1 VALUES (3, 'row id 3'), (4, '4 row'), (5, 'row5'); +INSERT INTO t1 VALUES (6, 'X 6 row'), (7, 'Seventh row'), (8, 'Last row'); + +ALTER TABLE t1 ADD INDEX new_b_index (b); +ALTER TABLE t1 DROP INDEX new_b_index; + +SET @saved_dbug=@@debug_dbug; +SET SESSION debug_dbug = "+d,ha_partition_fail_final_add_index"; + +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +ALTER TABLE t1 ADD INDEX (b); +SHOW CREATE TABLE t1; +--sorted_result +SELECT * FROM t1; + +FLUSH TABLES; +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +CREATE INDEX new_index ON t1 (b); +SHOW CREATE TABLE t1; +--sorted_result +SELECT * FROM t1; + +SET SESSION debug_dbug = @saved_dbug; +SHOW CREATE TABLE t1; +DROP TABLE t1; +} + +# Checking with #innodb what this is... +call mtr.add_suppression("InnoDB: Warning: allocated tablespace .*, old maximum was"); +# If there is a crash or failure between the ddl_log is written and the +# operation is completed, mysql will try to drop a not yet created partition +call mtr.add_suppression("InnoDB: Error: table .* does not exist in the InnoDB internal"); +call mtr.add_suppression("InnoDB: Warning: MariaDB is trying to drop table "); +call mtr.add_suppression("table .* does not exist in the InnoDB internal"); + + +let $engine= 'InnoDB'; + +--echo # +--echo # WL#4445: EXCHANGE PARTITION WITH TABLE +--echo # Verify ddl_log and InnoDB in case of crashing. +# Investigating if this warning is OK when crash testing. +call mtr.add_suppression("InnoDB: Warning: allocated tablespace .*, old maximum was "); +# +call mtr.add_suppression("Attempting backtrace. You can use the following information to find out"); +call mtr.add_suppression("table .* does not exist in the InnoDB internal"); + +let $create_statement= CREATE TABLE t1 (a INT, b VARCHAR(64)) +ENGINE = InnoDB +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)) ENGINE = InnoDB; +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_debug_sync_innodb-master.opt b/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt new file mode 100644 index 00000000..115a0ba2 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt @@ -0,0 +1 @@ +--loose-innodb_file_per_table diff --git a/mysql-test/suite/parts/t/partition_debug_sync_innodb.test b/mysql-test/suite/parts/t/partition_debug_sync_innodb.test new file mode 100644 index 00000000..9d2557a6 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_debug_sync_innodb.test @@ -0,0 +1,86 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_debug_sync.inc + +let $MYSQLD_DATADIR=`SELECT @@datadir`; + +--echo # +--echo # Bug#49907: ALTER TABLE ... TRUNCATE PARTITION +--echo # does not wait for locks on the table +--echo # +CREATE TABLE t1 (a INT) +ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (15), + PARTITION pMax VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1), (11), (21), (33); +BEGIN; +DELETE FROM t1 WHERE a = 11; +--sorted_result +SELECT * FROM t1; + +--connect (con1, localhost, root,,) +--send +ALTER TABLE t1 TRUNCATE PARTITION pMax; + +--connection default +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE STATE = "Waiting for table metadata lock" AND + INFO = "ALTER TABLE t1 TRUNCATE PARTITION pMax"; +--source include/wait_condition.inc +--sorted_result +SELECT * FROM t1; +--echo # Commit will allow the TRUNCATE to finish +COMMIT; + +--connection con1 +--reap +--disconnect con1 +--connection default +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug#50561: ALTER PARTITIONS does not have adequate lock, breaks with +--echo # concurrent I_S query +create table t1 (a int) +engine = innodb stats_persistent=0 +partition by range (a) +(partition p0 values less than MAXVALUE); +insert into t1 values (1), (11), (21), (33); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +--replace_result #p# #P# #sp# #SP# +--list_files $MYSQLD_DATADIR/test + +SET DEBUG_SYNC='before_open_in_get_all_tables SIGNAL parked WAIT_FOR open'; +SET DEBUG_SYNC='partition_open_error SIGNAL alter WAIT_FOR finish'; +send +SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, + PARTITION_DESCRIPTION, TABLE_ROWS +FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'; + +connect (con1, localhost, root,,); +SET DEBUG_SYNC = 'now WAIT_FOR parked'; +--echo # When waiting for the name lock in get_all_tables in sql_show.cc +--echo # this will not be concurrent any more, thus the TIMEOUT +SET DEBUG_SYNC = 'before_rename_partitions SIGNAL open WAIT_FOR alter TIMEOUT 1'; +--echo # Needs to be executed twice, since first is this 'SET DEBUG_SYNC ...' +SET DEBUG_SYNC = 'before_close_thread_tables SIGNAL finish EXECUTE 2'; +--error 0,ER_TABLE_EXISTS_ERROR +ALTER TABLE t1 REORGANIZE PARTITION p0 INTO +(PARTITION p0 VALUES LESS THAN (10), + PARTITION p10 VALUES LESS THAN MAXVALUE); + +disconnect con1; +connection default; +--reap +--replace_result #p# #P# #sp# #SP# +--list_files $MYSQLD_DATADIR/test +SHOW CREATE TABLE t1; +SELECT * FROM t1; +drop table t1; +--list_files $MYSQLD_DATADIR/test +SET DEBUG_SYNC = 'RESET'; diff --git a/mysql-test/suite/parts/t/partition_decimal_innodb.test b/mysql-test/suite/parts/t/partition_decimal_innodb.test new file mode 100644 index 00000000..5be42fb8 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_decimal_innodb.test @@ -0,0 +1,47 @@ +################################################################################ +# t/partition_decimal_innodb.test # +# # +# Purpose: # +# Tests around decimal type # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +--source include/big_test.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### number of rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_decimal.inc diff --git a/mysql-test/suite/parts/t/partition_decimal_myisam.test b/mysql-test/suite/parts/t/partition_decimal_myisam.test new file mode 100644 index 00000000..2715f2b9 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_decimal_myisam.test @@ -0,0 +1,45 @@ +################################################################################ +# t/partition_decimal_myisam.test # +# # +# Purpose: # +# Tests around decimal type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +--source include/big_test.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### number of rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_decimal.inc diff --git a/mysql-test/suite/parts/t/partition_double_innodb.test b/mysql-test/suite/parts/t/partition_double_innodb.test new file mode 100644 index 00000000..e31f7049 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_double_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_double_innodb.test # +# # +# Purpose: # +# Tests around float type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_float_innodb # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Number of row to be inserted. +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_double.inc diff --git a/mysql-test/suite/parts/t/partition_double_myisam.test b/mysql-test/suite/parts/t/partition_double_myisam.test new file mode 100644 index 00000000..6228d657 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_double_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_double_myisam.test # +# # +# Purpose: # +# Tests around float type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_float_myisam # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### Number of row to be inserted. +let $maxrows=16384; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_double.inc diff --git a/mysql-test/suite/parts/t/partition_engine_innodb.test b/mysql-test/suite/parts/t/partition_engine_innodb.test new file mode 100644 index 00000000..bd3e593b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_engine_innodb.test @@ -0,0 +1,82 @@ +################################################################################ +# t/partition_engine_innodb.test # +# # +# Purpose: # +# Tests around Create/Alter partitioned tables and storage engine settings # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_engine.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_engine_myisam.test b/mysql-test/suite/parts/t/partition_engine_myisam.test new file mode 100644 index 00000000..17fb5926 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_engine_myisam.test @@ -0,0 +1,81 @@ +################################################################################ +# t/partition_engine_myisam.test # +# # +# Purpose: # +# Tests around Create/Alter partitioned tables and storage engine settings # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_engine.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.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 00000000..cb666a42 --- /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 00000000..c7680409 --- /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 00000000..c625ad93 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test @@ -0,0 +1,17 @@ +# 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; + +source suite/parts/inc/part_exch_tabs.inc; + +error ER_MIX_HANDLER_ERROR; +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 00000000..c7680409 --- /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 00000000..a87d658c --- /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 00000000..3283c659 --- /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 00000000..80fd7ebb --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_12.test @@ -0,0 +1,179 @@ +# 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; +--sorted_result +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 00000000..c830ee61 --- /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 00000000..8d9f201f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_14.test @@ -0,0 +1,66 @@ +# 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; +let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; +let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; +mkdir $data_dir_path; +mkdir $idx_dir_path; + +echo # === Data/Index directories are identical; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === partition has directories, the table does not; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= ; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= ; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === the table has directories, partition does not; + let $p_data_directory= ; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= ; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === data directory differs; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$idx_dir_path'; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === index directory differs; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= DATA DIRECTORY = '$data_dir_path'; + let $index_directory= DATA DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + 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 $idx_dir_path; +rmdir $data_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 00000000..8ea641c8 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_15.test @@ -0,0 +1,25 @@ +# 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; + +source suite/parts/inc/part_exch_tabs.inc; + +# 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; +DROP TABLE t_11; +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 00000000..21079151 --- /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 00000000..db3a428b --- /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 00000000..1858131c --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_2.test @@ -0,0 +1,69 @@ +# 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; + +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; + +# 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; +DROP TABLE t_11; +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; +DROP TABLE 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; +DROP TABLE t_11; +# 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; +DROP TABLE t_11; +# 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 00000000..fc49eb1d --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_3.test @@ -0,0 +1,43 @@ +# 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; + +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; + +# 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 00000000..ea38337f --- /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 00000000..f6681646 --- /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 00000000..b1daa070 --- /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 00000000..8fcf43b8 --- /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 00000000..ace13f26 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_exch_qa_6.test @@ -0,0 +1,83 @@ +# 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; + +source suite/parts/inc/part_exch_tabs.inc; + +GRANT USAGE ON *.* TO test2@localhost; +GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost; + +connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +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; +disconnect test2; + +connection default; +REVOKE INSERT ON test.* FROM test2@localhost; + +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; +disconnect test2; + +connection default; +GRANT INSERT ON test.* TO test2@localhost; +REVOKE CREATE ON test.* FROM test2@localhost; + +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; +disconnect test2; + +connection default; +GRANT CREATE ON test.* TO test2@localhost; +REVOKE DROP ON test.* FROM test2@localhost; + +connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +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; +disconnect test2; + +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 00000000..e2379067 --- /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 00000000..e1bbd2b5 --- /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 00000000..58fecdbc --- /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 00000000..b8573a6a --- /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 00000000..3376e2ff --- /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 00000000..e58bccfb --- /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 00000000..31ea61bb --- /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 00000000..7384a49f --- /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 00000000..68e55090 --- /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_float_innodb.test b/mysql-test/suite/parts/t/partition_float_innodb.test new file mode 100644 index 00000000..8d96eafb --- /dev/null +++ b/mysql-test/suite/parts/t/partition_float_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_float_innodb.test # +# # +# Purpose: # +# Tests around float type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test for double type has been spawned into a separate test file # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Number of row to be inserted. +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_float.inc diff --git a/mysql-test/suite/parts/t/partition_float_myisam.test b/mysql-test/suite/parts/t/partition_float_myisam.test new file mode 100644 index 00000000..bdc0edd4 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_float_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_float_myisam.test # +# # +# Purpose: # +# Tests around float type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test for double type has been spawned into a separate test file # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### Number of row to be inserted. +let $maxrows=16384; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_float.inc diff --git a/mysql-test/suite/parts/t/partition_innodb_status_file-master.opt b/mysql-test/suite/parts/t/partition_innodb_status_file-master.opt new file mode 100644 index 00000000..57552ac0 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_innodb_status_file-master.opt @@ -0,0 +1 @@ +--loose-innodb-status-file=1 diff --git a/mysql-test/suite/parts/t/partition_innodb_status_file.test b/mysql-test/suite/parts/t/partition_innodb_status_file.test new file mode 100644 index 00000000..c5793d10 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_innodb_status_file.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE t1 (a INT) ENGINE = InnoDB PARTITION BY HASH(a); +INSERT INTO t1 VALUES (0), (1), (2); +START TRANSACTION; +UPDATE t1 SET a = 5 WHERE a = 1; +connect (con1, localhost, root,,); +--echo # InnoDB lock timeout and monitor thread runs every 15 seconds +SET innodb_lock_wait_timeout = 20; +START TRANSACTION; +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET a = 3 WHERE a = 1; +COMMIT; +disconnect con1; +connection default; +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/partition_int_innodb.test b/mysql-test/suite/parts/t/partition_int_innodb.test new file mode 100644 index 00000000..fd00e6a0 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_int_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_int_innodb.test # +# # +# Purpose: # +# Tests around integer type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: Int subtypes (tinyint etc.) have been spawned into separate tests # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_int.inc diff --git a/mysql-test/suite/parts/t/partition_int_myisam.test b/mysql-test/suite/parts/t/partition_int_myisam.test new file mode 100644 index 00000000..e8de09f1 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_int_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_int_myisam.test # +# # +# Purpose: # +# Tests around integer type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: Int subtypes (tinyint etc.) have been spawned into separate tests # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### number of rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_int.inc diff --git a/mysql-test/suite/parts/t/partition_mdev6067.test b/mysql-test/suite/parts/t/partition_mdev6067.test new file mode 100644 index 00000000..7d64de2e --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mdev6067.test @@ -0,0 +1,32 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_binlog_format_statement.inc + +# Test binlogging of some transactions on a partitioned InnoDB table. +# The bug was seen as one of the inserts being logged with a COMMIT query +# event instead of the correct XID event. + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB + PARTITION BY KEY (a) PARTITIONS 2; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +INSERT INTO t1 VALUES (1,1); +INSERT INTO t1 VALUES (2,2), (3,8); +INSERT INTO t1 VALUES (4,16); + +--source include/show_binlog_events.inc + +# Same thing in row mode. +DROP TABLE t1; +SET binlog_format='row'; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB + PARTITION BY KEY (a) PARTITIONS 2; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +INSERT INTO t1 VALUES (1,1); +INSERT INTO t1 VALUES (2,2), (3,8); +INSERT INTO t1 VALUES (4,16); + +--source include/show_binlog_events.inc + +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/partition_mediumint_innodb.test b/mysql-test/suite/parts/t/partition_mediumint_innodb.test new file mode 100644 index 00000000..9218b55f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mediumint_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_mediumint_innodb.test # +# # +# Purpose: # +# Tests around integer type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_innodb # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mediumint.inc diff --git a/mysql-test/suite/parts/t/partition_mediumint_myisam.test b/mysql-test/suite/parts/t/partition_mediumint_myisam.test new file mode 100644 index 00000000..bbf1775b --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mediumint_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_mediumint_myisam.test # +# # +# Purpose: # +# Tests around integer type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_myisam # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### number of rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mediumint.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test b/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test new file mode 100644 index 00000000..313da329 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test @@ -0,0 +1,42 @@ +################################################################################ +# t/partition_mgm_lc0_archive.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# Archive branch + lower_case_table_names = 0 # +# (usually Unix like, apart from Mac OS X) # +# Also requires lower_case_file_system OFF # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase0.inc +--source include/have_case_sensitive_file_system.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_archive.inc +let $engine= 'Archive'; +let $no_truncate= 1; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc0_innodb.test b/mysql-test/suite/parts/t/partition_mgm_lc0_innodb.test new file mode 100644 index 00000000..ff218dcb --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc0_innodb.test @@ -0,0 +1,41 @@ +################################################################################ +# t/partition_mgm_lc0_innodb.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# InnoDB branch + lower_case_table_names = 0 # +# (usually Unix like, apart from Mac OS X) # +# Also requires lower_case_file_system OFF # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase0.inc +--source include/have_case_sensitive_file_system.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc0_memory.test b/mysql-test/suite/parts/t/partition_mgm_lc0_memory.test new file mode 100644 index 00000000..209387a5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc0_memory.test @@ -0,0 +1,41 @@ +################################################################################ +# t/partition_mgm_lc0_memory.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# Memory branch + lower_case_table_names = 0 # +# (usually Unix like, apart from Mac OS X) # +# Also requires lower_case_file_system OFF # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase0.inc +--source include/have_case_sensitive_file_system.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#--source include/have_memory.inc +let $engine= 'Memory'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc0_myisam.test b/mysql-test/suite/parts/t/partition_mgm_lc0_myisam.test new file mode 100644 index 00000000..31ed4776 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc0_myisam.test @@ -0,0 +1,41 @@ +################################################################################ +# t/partition_mgm_lc0_myisam.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# MyISAM branch + lower_case_table_names = 0 # +# (usually Unix like, apart from Mac OS X) # +# Also requires lower_case_file_system OFF # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase0.inc +--source include/have_case_sensitive_file_system.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#--source include/have_myisam.inc +let $engine= 'MyISAM'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_archive-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc1_archive-master.opt new file mode 100644 index 00000000..62ab6dad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_archive-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test b/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test new file mode 100644 index 00000000..58eef828 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test @@ -0,0 +1,39 @@ +################################################################################ +# t/partition_mgm_lc1_archive.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# Archive branch + lower_case_table_names = 1 (usually Windows) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase1.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_archive.inc +let $engine= 'Archive'; +let $no_truncate= 1; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_innodb-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc1_innodb-master.opt new file mode 100644 index 00000000..62ab6dad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_innodb-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_innodb.test b/mysql-test/suite/parts/t/partition_mgm_lc1_innodb.test new file mode 100644 index 00000000..f14012d2 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_innodb.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_mgm_lc1_innodb.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# InnoDB branch + lower_case_table_names = 1 (usually Windows) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase1.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_memory-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc1_memory-master.opt new file mode 100644 index 00000000..62ab6dad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_memory-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_memory.test b/mysql-test/suite/parts/t/partition_mgm_lc1_memory.test new file mode 100644 index 00000000..35d933d2 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_memory.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_mgm_lc1_memory.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# Memory branch + lower_case_table_names = 1 (usually Windows) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase1.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#--source include/have_memory.inc +let $engine= 'Memory'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_myisam-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc1_myisam-master.opt new file mode 100644 index 00000000..62ab6dad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_myisam-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc1_myisam.test b/mysql-test/suite/parts/t/partition_mgm_lc1_myisam.test new file mode 100644 index 00000000..78b51da0 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc1_myisam.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_mgm_lc1_myisam.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# MyISAM branch + lower_case_table_names = 1 (usually Windows) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase1.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#--source include/have_myisam.inc +let $engine= 'MyISAM'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_archive-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc2_archive-master.opt new file mode 100644 index 00000000..f18979f5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_archive-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=2 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test b/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test new file mode 100644 index 00000000..92036178 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test @@ -0,0 +1,39 @@ +################################################################################ +# t/partition_mgm_lc2_archive.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# Archive branch + lower_case_table_names = 2 (usually Mac OS X) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase2.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_archive.inc +let $engine= 'Archive'; +let $no_truncate= 1; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_innodb-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc2_innodb-master.opt new file mode 100644 index 00000000..f18979f5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_innodb-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=2 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_innodb.test b/mysql-test/suite/parts/t/partition_mgm_lc2_innodb.test new file mode 100644 index 00000000..edcce4f9 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_innodb.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_mgm_lc2_innodb.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# InnoDB branch + lower_case_table_names = 2 (usually Mac OS X) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase2.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_memory-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc2_memory-master.opt new file mode 100644 index 00000000..f18979f5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_memory-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=2 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_memory.test b/mysql-test/suite/parts/t/partition_mgm_lc2_memory.test new file mode 100644 index 00000000..5bed32b9 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_memory.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_mgm_lc2_memory.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# Memory branch + lower_case_table_names = 2 (usually Mac OS X) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase2.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#--source include/have_memory.inc +let $engine= 'Memory'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_myisam-master.opt b/mysql-test/suite/parts/t/partition_mgm_lc2_myisam-master.opt new file mode 100644 index 00000000..f18979f5 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_myisam-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=2 diff --git a/mysql-test/suite/parts/t/partition_mgm_lc2_myisam.test b/mysql-test/suite/parts/t/partition_mgm_lc2_myisam.test new file mode 100644 index 00000000..83d35050 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_mgm_lc2_myisam.test @@ -0,0 +1,38 @@ +################################################################################ +# t/partition_mgm_lc2_myisam.test # +# # +# Purpose: # +# Test of partitioning management functions (incl upper/lower case names): # +# MyISAM branch + lower_case_table_names = 2 (usually Mac OS X) # +# # +#------------------------------------------------------------------------------# +# Original Author: mattiasj # +# Original Date: 2008-06-27 # +################################################################################ + +# The server must support partitioning. +--source include/have_partition.inc + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source include/have_lowercase2.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +#--source include/have_myisam.inc +let $engine= 'MyISAM'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_mgm.inc diff --git a/mysql-test/suite/parts/t/partition_open.test b/mysql-test/suite/parts/t/partition_open.test new file mode 100644 index 00000000..de6873aa --- /dev/null +++ b/mysql-test/suite/parts/t/partition_open.test @@ -0,0 +1,24 @@ +# +# MDEV-11084 Select statement with partition selection against MyISAM table opens all partitions. +# +--source include/have_partition.inc + +let $datadir=`select @@datadir`; + +# Table declared as having 2 partitions +# create table t1 (x int) egine=myisam +# partition by range columns (x) +# ( partition p0 values less than (100), partition p1 values less than (1000)); +# +# But we copy only second partition. So the 'p0' can't be opened. + +copy_file std_data/mdev11084.frm $datadir/test/t1.frm; +copy_file std_data/mdev11084.par $datadir/test/t1.par; +copy_file std_data/mdev11084.part1.MYD $datadir/test/t1#P#p1.MYD; +copy_file std_data/mdev11084.part1.MYI $datadir/test/t1#P#p1.MYI; +select * from t1 partition (p1); +--replace_result $datadir ./ +--error ER_FILE_NOT_FOUND +select * from t1 partition (p0); +--replace_result $datadir ./ +drop table t1; diff --git a/mysql-test/suite/parts/t/partition_purge.opt b/mysql-test/suite/parts/t/partition_purge.opt new file mode 100644 index 00000000..a39e5228 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_purge.opt @@ -0,0 +1 @@ +--innodb_purge_threads=1 diff --git a/mysql-test/suite/parts/t/partition_purge.test b/mysql-test/suite/parts/t/partition_purge.test new file mode 100644 index 00000000..2df81b0e --- /dev/null +++ b/mysql-test/suite/parts/t/partition_purge.test @@ -0,0 +1,37 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1(f1 INT, f2 INT, INDEX(f1))ENGINE=InnoDB + PARTITION BY LIST(f1) ( + PARTITION p1 VALUES in (1, 2, 3), + PARTITION p2 VALUES in (4, 5, 6)); +INSERT INTO t1 VALUES(1, 1), (1, 1), (6, 1); +connect(con1,localhost,root,,,); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connect(con2,localhost,root,,,); +SET DEBUG_SYNC="innodb_rollback_inplace_alter_table SIGNAL default_resume WAIT_FOR alter_resume"; +send ALTER TABLE t1 ADD UNIQUE INDEX(f1); + +connection default; +set DEBUG_SYNC="now WAIT_FOR default_resume"; +SET DEBUG_SYNC="innodb_row_update_for_mysql_begin SIGNAL alter_resume WAIT_FOR alter_finish"; +send DELETE FROM t1; + +connection con2; +--error ER_DUP_ENTRY +reap; +SET DEBUG_SYNC="now SIGNAL alter_finish"; + +connection default; +reap; +connection con1; +commit; +connection default; +disconnect con1; +disconnect con2; +--source ../../innodb/include/wait_all_purged.inc +drop table t1; +SET DEBUG_SYNC=reset; diff --git a/mysql-test/suite/parts/t/partition_recover_myisam-master.opt b/mysql-test/suite/parts/t/partition_recover_myisam-master.opt new file mode 100644 index 00000000..ddbd7a0a --- /dev/null +++ b/mysql-test/suite/parts/t/partition_recover_myisam-master.opt @@ -0,0 +1 @@ +--myisam-recover-options diff --git a/mysql-test/suite/parts/t/partition_recover_myisam.test b/mysql-test/suite/parts/t/partition_recover_myisam.test new file mode 100644 index 00000000..91a14a51 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_recover_myisam.test @@ -0,0 +1,50 @@ +# test the auto-recover (--myisam-recover) of partitioned myisam tables + +--disable_query_log +call mtr.add_suppression("..test.t1_will_crash"); +call mtr.add_suppression("Got an error from unknown thread"); +call mtr.add_suppression("Table 't1_will_crash' is marked as crashed and should be repaired"); +--enable_query_log + +--source include/have_partition.inc +--disable_warnings +--disable_query_log +drop table if exists t1_will_crash; +--enable_query_log +--enable_warnings + + +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); +FLUSH TABLES; + +--echo # replacing t1.MYI with a corrupt + unclosed one created by doing: +--echo # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI +let $MYSQLD_DATADIR= `select @@datadir`; +--remove_file $MYSQLD_DATADIR/test/t1_will_crash.MYI +--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/test/t1_will_crash.MYI +--enable_prepare_warnings +# Embedded server doesn't chdir to data directory +--replace_regex /Table '.*t1_will_crash/Table 't1_will_crash/ +--enable_prepare_warnings +SELECT * FROM t1_will_crash; +--disable_prepare_warnings +DROP TABLE t1_will_crash; +CREATE TABLE t1_will_crash (a INT, KEY (a)) +ENGINE=MyISAM +PARTITION BY HASH(a) +PARTITIONS 3; +INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +FLUSH TABLES; +--echo # replacing t1#P#p1.MYI with a corrupt + unclosed one created by doing: +--echo # 'create table t1 (a int key(a)) partition by hash (a) partitions 3' +--echo # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +--enable_prepare_warnings +# Embedded server doesn't chdir to data directory +--replace_regex /Table '.*t1_will_crash/Table 't1_will_crash/ +--enable_prepare_warnings +SELECT * FROM t1_will_crash; +--disable_prepare_warnings +DROP TABLE t1_will_crash; diff --git a/mysql-test/suite/parts/t/partition_repair_myisam-master.opt b/mysql-test/suite/parts/t/partition_repair_myisam-master.opt new file mode 100644 index 00000000..c5f16ccf --- /dev/null +++ b/mysql-test/suite/parts/t/partition_repair_myisam-master.opt @@ -0,0 +1 @@ +--myisam-recover-options=off diff --git a/mysql-test/suite/parts/t/partition_repair_myisam.test b/mysql-test/suite/parts/t/partition_repair_myisam.test new file mode 100644 index 00000000..c4c05cc0 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_repair_myisam.test @@ -0,0 +1,288 @@ +# test of check/repair of partitioned myisam tables +--source include/have_partition.inc +--disable_warnings +--disable_query_log +drop table if exists t1_will_crash; +call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); +call mtr.add_suppression("MariaDB 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 + +--echo # REPAIR USE_FRM is not implemented for partitioned tables. + +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); +FLUSH TABLES; +--echo # replacing t1.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/t1_will_crash.MYI +--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/test/t1_will_crash.MYI +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; + +--echo # test of check/repair of a damaged partition's MYI-file +CREATE TABLE t1_will_crash (a INT, KEY (a)) +ENGINE=MyISAM +PARTITION BY HASH (a) +PARTITIONS 3; +INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +FLUSH TABLES; +--echo # test with CHECK/REPAIR TABLE +--echo # replacing t1#P#p1.MYI with a corrupt + unclosed one created by doing: +--echo # 'create table t1 (a int key(a)) partition by hash (a) partitions 3' +--echo # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT * FROM t1_will_crash; +FLUSH TABLES; +--echo # test with ALTER TABLE ... CHECK/REPAIR PARTITION +--echo # replacing t1_will_crash#P#p1.MYI with a corrupt + unclosed one +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +ALTER TABLE t1_will_crash CHECK PARTITION p0, p2; +ALTER TABLE t1_will_crash CHECK PARTITION p0, p1; +ALTER TABLE t1_will_crash CHECK PARTITION p1, p2; +ALTER TABLE t1_will_crash REPAIR PARTITION p0, p2; +ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; + +--echo # test of check/repair of a damaged subpartition's MYI-file +CREATE TABLE t1_will_crash (a INT, KEY (a)) +ENGINE=MyISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +SUBPARTITIONS 2 +(PARTITION p0 VALUES LESS THAN (7), + PARTITION p1 VALUES LESS THAN MAXVALUE); +INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +SELECT * FROM t1_will_crash; +FLUSH TABLES; +--echo # test with CHECK/REPAIR TABLE +--echo # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT * FROM t1_will_crash; +FLUSH TABLES; +--echo # test with ALTER TABLE ... CHECK/REPAIR PARTITION +--echo # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI +ALTER TABLE t1_will_crash CHECK PARTITION p0; +ALTER TABLE t1_will_crash CHECK PARTITION all; +ALTER TABLE t1_will_crash CHECK PARTITION p1; +ALTER TABLE t1_will_crash REPAIR PARTITION p0; +ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; + +--echo # test of check/repair of crashed partitions in variuos states +CREATE TABLE t1_will_crash ( + a VARCHAR(255), + b INT, + c LONGTEXT, + PRIMARY KEY (a, b)) +ENGINE=MyISAM +PARTITION BY HASH (b) +PARTITIONS 7; + +# creating a longer string for for filling the records +let $i= 3; +let $lt= longtext; +while ($i) +{ + let $lt= $lt$lt; + dec $i; +} + +# Tests (mapped to partition) +# Partition +# 0 - truncated datafile (size = 0 bytes) +# 1 - head -c 1024 of datafile (simulates crashed write) +# 2 - after _mi_mark_file_changed (only marked index as opened) +# 3 - after write_record (updated datafile + not closed/updated index) +# 4 - after flush_cached_blocks (updated index/datafiles, not closed index) +# 5 - (Not used) after mi_state_info_write (fully updated/closed index file) +# (this was verified to be a harmless crash, since everything was written) +# 6 - partly updated datafile (insert 6 small records, delete 5,3,1, +# insert one larger record (2.5 X small) and break in gdb before it has +# been completely written (in write_dynamic_record) +# (done with 3 different MYD files, since it also affects +# the delete-linked-list) + +--disable_query_log +eval INSERT INTO t1_will_crash VALUES + ('abc', 1, '$lt'), ('def', 2, '$lt'), ('ghi', 3, '$lt'), ('jkl', 6, '$lt'), + ('mno', 5, '$lt'), ('pqr', 4, '$lt'), ('tuw', 8, '$lt'), ('vxy', 9, '$lt'), + ('z lost', 7, '$lt'), ('aaa', 10, '$lt'), ('bbb', 11, '$lt'), + ('zzzzzZzzzzz', 97, '$lt'), ('a', 89, '$lt'), (' ', 83, '$lt'), + ('ccc', 79, '$lt'), ('ddd', 73, '$lt'), ('eee', 71, '$lt'), + ('fff', 67, '$lt'), ('ooo', 13, '$lt'), ('nnn', 17, '$lt'), + ('mmm', 19, '$lt'), ('lll', 23, '$lt'), ('kkkkkkkkKkk', 29, '$lt'), + (' lost', 0, '$lt'), ('1 broken when head -c1024 on datafile', 71, + '$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt'), + ('3 crashed after write_record', 24, '$lt'); +eval INSERT INTO t1_will_crash VALUES + ('2 crashed after _mi_mark_changed', 30, '$lt'); +# if crashed here, part p5 would need to be repaired before next statement +# but since we use pre fabricated crashed files, we can skip that here. +eval INSERT INTO t1_will_crash VALUES + ('5 still here since crash in next row in multirow insert?', 40, '$lt'), + ('4 crashed after flush_cached_blocks', 18, '$lt'); +# There is no write after mi_state_info_write, so this is not tested. +#eval INSERT INTO t1_will_crash VALUES +# ('5 crashed after mi_state_info_write', 12, '$lt'); +eval INSERT INTO t1_will_crash VALUES + ('6 row 1', 27, '$lt'), ('6 row 2', 34, '$lt'), + ('6 row 3', 41, '$lt'), ('6 row 4', 48, '$lt'), + ('6 row 5', 55, '$lt'), ('6 row 6', 62, '$lt'); +DELETE FROM t1_will_crash WHERE b in (27, 55); +DELETE FROM t1_will_crash WHERE b = 41; +eval INSERT INTO t1_will_crash VALUES + ('6 row 7 (crash before completely written to datafile)', 27, '$lt$lt'); +--enable_query_log +SELECT COUNT(*) FROM t1_will_crash; +SELECT (b % 7) AS `partition`, COUNT(*) AS rows_cnt FROM t1_will_crash GROUP BY (b % 7); +SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash ORDER BY `partition`, b, a; +FLUSH TABLES; +# testing p0, p1, p3, p6(1) +--echo # truncating p0 to simulate an empty datafile (not recovered!) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p0.MYD +--write_file $MYSQLD_DATADIR/test/t1_will_crash#P#p0.MYD +EOF + +--echo # replacing p1 with only the first 1024 bytes (not recovered!) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYD +--copy_file std_data/parts/t1_will_crash#P#p1_first_1024.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYD + +--echo # replacing p3 with a crashed one at the last row in first insert +--echo # (crashed right after *share->write_record()) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p3.MYI +--copy_file std_data/parts/t1_will_crash#P#p3.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p3.MYI + +--echo # replacing p6 with a crashed MYD file (1) (splitted dynamic record) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD +--copy_file std_data/parts/t1_will_crash#P#p6.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD +ANALYZE TABLE t1_will_crash; +OPTIMIZE TABLE t1_will_crash; +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT COUNT(*) FROM t1_will_crash; +SELECT (b % 7) AS `partition`, COUNT(*) AS rows_cnt FROM t1_will_crash GROUP BY (b % 7); +SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash ORDER BY `partition`, b, a; +FLUSH TABLES; + +# testing p2, p4, p6(2, 3) +--echo # +--echo # replacing p2 with crashed files (after _mi_mark_changed) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYI +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYD +--copy_file std_data/parts/t1_will_crash#P#p2.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYI +--copy_file std_data/parts/t1_will_crash#P#p2.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYD +ALTER TABLE t1_will_crash CHECK PARTITION p2; +--echo # crash was when index only marked as opened, no real corruption +ALTER TABLE t1_will_crash CHECK PARTITION p2; +FLUSH TABLES; + +--echo # +--echo # replacing p4 with updated but not closed index file +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p4.MYI +--copy_file std_data/parts/t1_will_crash#P#p4.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p4.MYI +#SHOW TABLE STATUS like 't1_will_crash'; +#ALTER TABLE t1_will_crash ANALYZE PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +ALTER TABLE t1_will_crash OPTIMIZE PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +ALTER TABLE t1_will_crash CHECK PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +ALTER TABLE t1_will_crash REPAIR PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +FLUSH TABLES; + +--echo # +--echo # replacing p6 with a crashed MYD file (2) (splitted dynamic record) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD +--copy_file std_data/parts/t1_will_crash#P#p6_2.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD +#ALTER TABLE t1_will_crash OPTIMIZE PARTITION p6; +ALTER TABLE t1_will_crash CHECK PARTITION p6; +ALTER TABLE t1_will_crash REPAIR PARTITION p6; +SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash +WHERE (b % 7) = 6 +ORDER BY `partition`, b, a; +FLUSH TABLES; + +--echo # +--echo # replacing p6 with a crashed MYD file (3) (splitted dynamic record) +--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD +--copy_file std_data/parts/t1_will_crash#P#p6_3.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD +--echo # Different results from the corrupt table, which can lead to dropping +--echo # of the not completely written rows when using REBUILD on a corrupt +--echo # table, depending if one reads via index or direct on datafile. +--echo # Since crash when reuse of deleted row space, CHECK MEDIUM or EXTENDED +--echo # is required (MEDIUM is default) to verify correct behavior! +SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash +WHERE (b % 7) = 6 +ORDER BY `partition`, b, a; +SET @save_optimizer_switch= @@optimizer_switch; +SET @@optimizer_switch='derived_merge=off'; +SELECT (b % 7) AS `partition`, b, a FROM (SELECT b,a FROM t1_will_crash) q +WHERE (b % 7) = 6 +ORDER BY `partition`, b, a; +SET @@optimizer_switch=@save_optimizer_switch; +# NOTE: REBUILD PARTITION without CHECK before, 2 + (1) records will be lost! +#ALTER TABLE t1_will_crash REBUILD PARTITION p6; +ALTER TABLE t1_will_crash CHECK PARTITION p6; +ALTER TABLE t1_will_crash REPAIR PARTITION p6; +SELECT COUNT(*) FROM t1_will_crash; +SELECT (b % 7) AS `partition`, COUNT(*) AS rows_cnt FROM t1_will_crash GROUP BY (b % 7); +SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash ORDER BY `partition`, b, a; +ALTER TABLE t1_will_crash CHECK PARTITION all EXTENDED; +DROP TABLE t1_will_crash; diff --git a/mysql-test/suite/parts/t/partition_smallint_innodb.test b/mysql-test/suite/parts/t/partition_smallint_innodb.test new file mode 100644 index 00000000..22d16cf9 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_smallint_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_smallint_innodb.test # +# # +# Purpose: # +# Tests around integer type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_innodb # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_smallint.inc diff --git a/mysql-test/suite/parts/t/partition_smallint_myisam.test b/mysql-test/suite/parts/t/partition_smallint_myisam.test new file mode 100644 index 00000000..f473a677 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_smallint_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_smallint_myisam.test # +# # +# Purpose: # +# Tests around integer type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_myisam # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### number of rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_smallint.inc diff --git a/mysql-test/suite/parts/t/partition_special_innodb.test b/mysql-test/suite/parts/t/partition_special_innodb.test new file mode 100644 index 00000000..836b7048 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_special_innodb.test @@ -0,0 +1,234 @@ +################################################################################ +# t/partition_special_innodb.test # +# # +# Purpose: # +# different Tests # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: MattiasJ # +# Change Date: 2008-08-20 # +# Change: added test for bug#34604 # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_key_4col.inc +--source suite/parts/inc/partition_key_8col.inc +--source suite/parts/inc/partition_key_16col.inc +--source suite/parts/inc/partition_key_32col.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +--echo # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end + +CREATE TABLE t1 ( + a INT AUTO_INCREMENT, + b VARCHAR(255), + PRIMARY KEY (a)) +ENGINE = InnoDB +PARTITION BY HASH (a) +PARTITIONS 2; + +connect (con1, localhost, root,,); +SET autocommit=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES (NULL, 'first row t2'); + +connect (con2, localhost, root,,); +SET autocommit=OFF; +SET SESSION lock_wait_timeout= 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 AUTO_INCREMENT = 10; + +--disconnect con2 +--disconnect con1 +--connection default +DROP TABLE t1; + +--echo # +--echo # Bug#53676: Unexpected errors and possible table corruption on +--echo # ADD PARTITION and LOCK TABLE +CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; +INSERT INTO t1 VALUES (2, 2), (3, 3), (4, 4), (5, 5); + +LOCK TABLE t1 READ; + +--connect (con2,localhost,root,,) +SET lock_wait_timeout = 0; +--echo # First attempt: lock wait timeout (as expected) +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +--echo # Second attempt: says that partition already exists +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +--echo # Check that we only can select, not insert/update/delete. +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11); +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i = 5 WHERE f = 2; +--error ER_LOCK_WAIT_TIMEOUT +DELETE FROM t1 WHERE i = 10; +--sorted_result +SELECT * FROM t1; + +--connection default +UNLOCK TABLES; +LOCK TABLE t1 READ; + +--connection con2 +--echo # Third attempt: says that the table does not exist +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +--echo # Check table returns the same (not after fixing bug#56172!) +CHECK TABLE t1; + +--connection default +UNLOCK TABLES; +# Avoid a MDL conflict between purge and the DROP TABLE below +SET GLOBAL innodb_max_purge_lag_wait=0; + +--connection con2 +DROP TABLE t1; + +# End of Test1 + +# Test2 + +--connection default + +CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; + +LOCK TABLE t2 READ; + +--connection con2 +SET lock_wait_timeout = 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t2 ADD PARTITION PARTITIONS 2; +SET lock_wait_timeout = 2; +send ALTER TABLE t2 ADD PARTITION PARTITIONS 2; + +--connection default +UNLOCK TABLES; + +--connection con2 +--reap + +--connect (con3,localhost,root,,) +CHECK TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +# End of Test2 +# Test #3 + +--connection default + +CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; + +LOCK TABLE t3 READ; + +--connection con2 +SET lock_wait_timeout = 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t3 ADD PARTITION PARTITIONS 2; + +--connection con3 +SET lock_wait_timeout = 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t3 ADD PARTITION PARTITIONS 2; + +--connect (con4,localhost,root,,) +send RENAME TABLE t3 TO t4; + +--connection default +UNLOCK TABLES; + +--connection con4 +--reap + +--connect (con5,localhost,root,,) +--echo # SHOW TABLES returns the table (not renamed): +SHOW TABLES; +--echo # Connection 5 attempts to read from the table (table does not exist): +--error ER_NO_SUCH_TABLE +SELECT * FROM t3; +DROP TABLE t4; + +--disconnect con5 +--disconnect con4 +--disconnect con3 +--disconnect con2 +--connection default +# End of Test #3 + +# MDEV-21693 ALGORITHM=INSTANT does not work for partitioned tables +CREATE TABLE t1( + f1 INT, f2 VARCHAR(10) CHARSET ascii, + f3 CHAR(150) CHARSET ascii, + f4 TEXT CHARSET ascii)ENGINE=InnoDB + PARTITION BY RANGE(f1) (PARTITION p1 VALUES LESS THAN(10), + PARTITION p2 VALUES LESS THAN (100)); +ALTER TABLE t1 convert to charset ascii collate ascii_bin, ALGORITHM=INSTANT; +DROP TABLE t1; + +--echo # Test WRITE LOCK. +CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; +INSERT INTO t1 VALUES (3, 3), (4, 4); + +LOCK TABLE t1 WRITE; + +--connect (con2,localhost,root,,) +SET lock_wait_timeout = 0; +--echo # Check that we only can select, not insert/update/delete. +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11); +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i = 5 WHERE f = 2; +--error ER_LOCK_WAIT_TIMEOUT +DELETE FROM t1 WHERE i = 10; +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; + +--connection default +UNLOCK TABLES; +# Avoid a MDL conflict between purge and the DROP TABLE below +SET GLOBAL innodb_max_purge_lag_wait=0; + +--connection con2 +DROP TABLE t1; + +--disconnect con2 +--connection default diff --git a/mysql-test/suite/parts/t/partition_special_myisam.test b/mysql-test/suite/parts/t/partition_special_myisam.test new file mode 100644 index 00000000..b6f25484 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_special_myisam.test @@ -0,0 +1,82 @@ +################################################################################ +# t/partition_special_myisam.test # +# # +# Purpose: # +# different Tests # +# MYISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_key_4col.inc +--source suite/parts/inc/partition_key_8col.inc +--source suite/parts/inc/partition_key_16col.inc +--source suite/parts/inc/partition_key_32col.inc + +--echo # +--echo # Bug#53770: Server crash at handler.cc:2076 on LOAD DATA +--echo # after timed out COALESCE PARTITION +CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = MyISAM PARTITION BY HASH(i) PARTITIONS 3; + +START TRANSACTION; +SELECT * FROM t1; + +--connect (con2,localhost,root,,) +SET lock_wait_timeout = 2; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 COALESCE PARTITION 2; + +--connect (con3,localhost,root,,) + +--let $MYSQLD_DATADIR= `SELECT @@datadir` +--write_file $MYSQLD_DATADIR/test/load.in +1 +2 +3 +EOF +send LOAD DATA INFILE 'load.in' INTO TABLE t1 (f); + +--connection default +--real_sleep 1 +COMMIT; + +--connection con3 +--reap +--remove_file $MYSQLD_DATADIR/test/load.in +DROP TABLE t1; + +--disconnect con3 +--disconnect con2 +--connection default diff --git a/mysql-test/suite/parts/t/partition_syntax_innodb.test b/mysql-test/suite/parts/t/partition_syntax_innodb.test new file mode 100644 index 00000000..db52c5dc --- /dev/null +++ b/mysql-test/suite/parts/t/partition_syntax_innodb.test @@ -0,0 +1,82 @@ +################################################################################ +# t/partition_syntax_innodb.test # +# # +# Purpose: # +# Tests around Create Partitioned table syntax # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the data following the PRIMARY KEY. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_syntax.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_syntax_myisam.test b/mysql-test/suite/parts/t/partition_syntax_myisam.test new file mode 100644 index 00000000..ba98062d --- /dev/null +++ b/mysql-test/suite/parts/t/partition_syntax_myisam.test @@ -0,0 +1,81 @@ +################################################################################ +# t/partition_syntax_myisam.test # +# # +# Purpose: # +# Tests around Create Partitioned table syntax # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# This test relies on connecting externally from mysqltest, doesn't +# work with embedded. +--source include/not_embedded.inc +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_syntax.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_tinyint_innodb.test b/mysql-test/suite/parts/t/partition_tinyint_innodb.test new file mode 100644 index 00000000..aec10c1a --- /dev/null +++ b/mysql-test/suite/parts/t/partition_tinyint_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_tinyint_innodb.test # +# # +# Purpose: # +# Tests around integer type # +# INNODB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_innodb # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### max rows to be inserted +let $maxrows=1024; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_tinyint.inc diff --git a/mysql-test/suite/parts/t/partition_tinyint_myisam.test b/mysql-test/suite/parts/t/partition_tinyint_myisam.test new file mode 100644 index 00000000..9807bffb --- /dev/null +++ b/mysql-test/suite/parts/t/partition_tinyint_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/partition_tinyint_myisam.test # +# # +# Purpose: # +# Tests around integer type # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: Elena Stepanova # +# Change Date: 2017-02-18 # +# Change: The test file is spawned from the mega-test partition_int_myisam # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +--source include/long_test.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MYISAM'; +##### number of rows to be inserted +let $maxrows=65535; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_tinyint.inc diff --git a/mysql-test/suite/parts/t/partition_value_innodb.test b/mysql-test/suite/parts/t/partition_value_innodb.test new file mode 100644 index 00000000..fe47f533 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_value_innodb.test @@ -0,0 +1,85 @@ +################################################################################ +# t/partition_value_innodb.test # +# # +# Purpose: # +# Tests around "exotic" values calculated by the partitioning function # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-04-11 # +# Change Author: mleich # +# Change Date: 2008-12-08 # +# Change: Remove test from disabled.def + change test that it gets skipped # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +# +# CAST() within the partitioning function si no more supported, but we get +# this functionality probably soon again. Therefor we do not delete this test. +--skip # CAST() in partitioning function is currently not supported. + + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +##### Execute the test of "table" files +# InnoDB has no files per PK, UI, ... +let $do_file_tests= 0; + +##### Execute PRIMARY KEY tests ##### +# AFAIK InnoDB clusters the table around PRIMARY KEYs. +let $do_pk_tests= 1; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_value.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/partition_value_myisam.test b/mysql-test/suite/parts/t/partition_value_myisam.test new file mode 100644 index 00000000..026ad57f --- /dev/null +++ b/mysql-test/suite/parts/t/partition_value_myisam.test @@ -0,0 +1,84 @@ +################################################################################ +# t/partition_value_myisam.test # +# # +# Purpose: # +# Tests around "exotic" values calculated by the partitioning function # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: mleich # +# Original Date: 2006-04-11 # +# Change Author: mleich # +# Change Date: 2008-12-08 # +# Change: Remove test from disabled.def + change test that it gets skipped # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +# +# CAST() within the partitioning function si no more supported, but we get +# this functionality probably soon again. Therefor we do not delete this test. +--skip # CAST() in partitioning function is currently not supported. + + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +##### Option, for displaying files ##### +let $ls= 1; + +##### Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments ##### +# on partioned tables +SET @max_row = 20; + +##### Execute more tests ##### +let $more_trigger_tests= 0; +let $more_pk_ui_tests= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +let $engine= 'MyISAM'; + +##### Execute the test of "table" files +# MyISAM has files per PK, UI, ... +let $do_file_tests= 1; + +##### Execute PRIMARY KEY tests ##### +# AFAIK MyISAM treats PRIMARY KEYs like UNIQUE INDEXes +let $do_pk_tests= 0; + +##### Assign a big number smaller than the maximum value for partitions ##### +# and smaller than the maximum value of SIGNED INTEGER +let $MAX_VALUE= (2147483646); + +# Generate the prerequisites ($variables, @variables, tables) needed +--source suite/parts/inc/partition.pre + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_value.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/parts/inc/partition_cleanup.inc diff --git a/mysql-test/suite/parts/t/percona_nonflushing_analyze_debug.test b/mysql-test/suite/parts/t/percona_nonflushing_analyze_debug.test new file mode 100644 index 00000000..61c0a278 --- /dev/null +++ b/mysql-test/suite/parts/t/percona_nonflushing_analyze_debug.test @@ -0,0 +1,29 @@ +--source include/have_debug_sync.inc +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB + PARTITION BY RANGE (a) ( + PARTITION p0 VALUES LESS THAN (3), + PARTITION p1 VALUES LESS THAN (10)); + +INSERT INTO t1 VALUES (1), (2), (3), (4); + +--let $percona_nonflushing_analyze_table= t1 +--source include/percona_nonflushing_analyze_debug.inc + +DROP TABLE t1; + +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB + PARTITION BY RANGE (a) + SUBPARTITION BY HASH (A) + SUBPARTITIONS 2 ( + PARTITION p0 VALUES LESS THAN (3), + PARTITION p1 VALUES LESS THAN (10)); + +INSERT INTO t2 VALUES (1), (2), (3), (4); + +--let $percona_nonflushing_analyze_table= t2 +--source include/percona_nonflushing_analyze_debug.inc + +DROP TABLE t2; diff --git a/mysql-test/suite/parts/t/print_error.test b/mysql-test/suite/parts/t/print_error.test new file mode 100644 index 00000000..79da0d87 --- /dev/null +++ b/mysql-test/suite/parts/t/print_error.test @@ -0,0 +1,30 @@ +# +# MDEV-15336 Server crashes in handler::print_error / ha_partition::print_error upon query timeout +# +--source include/have_innodb.inc +--source include/not_embedded.inc +--source include/have_partition.inc + +CREATE TABLE t1 (i INT) ENGINE=InnoDB PARTITION BY HASH (i) PARTITIONS 2; +XA START 'xid'; +INSERT INTO t1 VALUES (1),(2),(3),(4); + +--connect (con1,localhost,root,,test) +--send CREATE TABLE t2 SELECT * FROM t1; + +--connect (con2,localhost,root,,test) +SET max_statement_time= 1; +--error ER_STATEMENT_TIMEOUT +DELETE FROM t1 PARTITION (p1) ORDER BY i LIMIT 2; + +# Cleanup +--disconnect con2 +--connection default +XA END 'xid'; +XA ROLLBACK 'xid'; +--connection con1 +--reap +--disconnect con1 +--connection default +call mtr.add_suppression('Sort aborted'); +DROP TABLE IF EXISTS t2, t1; diff --git a/mysql-test/suite/parts/t/reorganize.test b/mysql-test/suite/parts/t/reorganize.test new file mode 100644 index 00000000..66641254 --- /dev/null +++ b/mysql-test/suite/parts/t/reorganize.test @@ -0,0 +1,20 @@ +--source include/have_sequence.inc +--source include/have_partition.inc +--source include/have_debug.inc + +--echo # +--echo # MDEV-23248 Server crashes in mi_extra / +--echo # ha_partition::loop_extra_alter upon REORGANIZE +--echo # + +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 70 (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 SELECT 4, 6 FROM seq_1_to_131072; +UPDATE t1 SET a = 7; + +set @org_debug=@@debug_dbug; +set @@debug_dbug="+d,debug_abort_copy_partitions"; +--error ER_UNSUPPORTED_EXTENSION +ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (PARTITION p1 VALUES LESS THAN (5), PARTITION p2 VALUES LESS THAN MAXVALUE); +set @@debug_dbug=@org_debug; + +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/reorganize_partition_innodb.test b/mysql-test/suite/parts/t/reorganize_partition_innodb.test new file mode 100644 index 00000000..77109c38 --- /dev/null +++ b/mysql-test/suite/parts/t/reorganize_partition_innodb.test @@ -0,0 +1,104 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_symlink.inc + +--echo # +--echo # MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir +--echo # + +mkdir $MYSQLTEST_VARDIR/tmp/partitions_here; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t ( + a INT NOT NULL + ) ENGINE=INNODB +PARTITION BY HASH (a) ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB, + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB +); +INSERT INTO t VALUES (1); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +ALTER TABLE t DROP PRIMARY KEY, ALGORITHM=COPY; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; +SET GLOBAL INNODB_FILE_PER_TABLE=OFF; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/', + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, + PARTITION p2 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +DROP TABLE t; + +SET @strict = @@innodb_strict_mode; +SET innodb_strict_mode=OFF; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t ( + a INT NOT NULL + ) ENGINE=INNODB +PARTITION BY RANGE (a) + SUBPARTITION BY HASH (a) + SUBPARTITIONS 2 + ( + PARTITION p1 VALUES LESS THAN (7) + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + ENGINE = INNODB, + PARTITION p2 VALUES LESS THAN MAXVALUE + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + ENGINE = INNODB + ); +SET innodb_strict_mode=@strict; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t +REORGANIZE PARTITION p1,p2 INTO +( + PARTITION p1 VALUES LESS THAN (7) + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + ENGINE = INNODB, + PARTITION p2 VALUES LESS THAN MAXVALUE + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + ENGINE = INNODB +); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +DROP TABLE t; + +rmdir $MYSQLTEST_VARDIR/tmp/partitions_here/test; +rmdir $MYSQLTEST_VARDIR/tmp/partitions_here; + diff --git a/mysql-test/suite/parts/t/rpl_partition.test b/mysql-test/suite/parts/t/rpl_partition.test new file mode 100644 index 00000000..30493343 --- /dev/null +++ b/mysql-test/suite/parts/t/rpl_partition.test @@ -0,0 +1,174 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/big_test.inc +--source include/master-slave.inc + +--vertical_results + +let $engine_type= 'innodb'; + +set @old_global_binlog_format= @@global.binlog_format; +set @old_session_binlog_format= @@session.binlog_format; +SET GLOBAL binlog_format = 'ROW'; +SET SESSION binlog_format = 'ROW'; +select @@global.binlog_format, @@session.binlog_format; +connection slave; +set @old_global_binlog_format= @@global.binlog_format; +set @old_session_binlog_format= @@session.binlog_format; +SET GLOBAL binlog_format = 'ROW'; +SET SESSION binlog_format = 'ROW'; +select @@global.binlog_format, @@session.binlog_format; +# restart slave so that slave sql thread's binlog format is re-read +# from @@global.binlog_format +--source include/stop_slave.inc +--source include/start_slave.inc +connection master; + +eval CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT, + dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, + fkid MEDIUMINT, filler VARCHAR(255), + PRIMARY KEY(id)) ENGINE=$engine_type; + +eval CREATE TABLE t2(id MEDIUMINT NOT NULL AUTO_INCREMENT, + dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, + fkid MEDIUMINT, filler VARCHAR(255), + PRIMARY KEY(id)) ENGINE=$engine_type + PARTITION BY KEY(id) partitions 5; + +eval CREATE TABLE t3(id MEDIUMINT NOT NULL AUTO_INCREMENT, + dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, + fkid MEDIUMINT, filler VARCHAR(255), + PRIMARY KEY(id)) ENGINE=$engine_type + PARTITION BY RANGE(id) + SUBPARTITION BY hash(id) subpartitions 2 + (PARTITION pa1 values less than (10), + PARTITION pa2 values less than (20), + PARTITION pa3 values less than (30), + PARTITION pa4 values less than (40), + PARTITION pa5 values less than (50), + PARTITION pa6 values less than (60), + PARTITION pa7 values less than (70), + PARTITION pa8 values less than (80), + PARTITION pa9 values less than (90), + PARTITION pa10 values less than (100), + PARTITION pa11 values less than MAXVALUE); + +######## Create SPs, Functions, Views and Triggers Section ############## + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE ins_count INT DEFAULT 1000; + DECLARE del_count INT; + DECLARE cur_user VARCHAR(255); + DECLARE local_uuid VARCHAR(255); + DECLARE local_time TIMESTAMP; + + SET local_time= NOW(); + SET cur_user= CURRENT_USER(); + SET local_uuid= UUID(); + + WHILE ins_count > 0 DO + INSERT INTO t1 VALUES (NULL, NOW(), USER() , UUID(), + ins_count,'Going to test MBR for MySQL'); + SET ins_count = ins_count - 1; + END WHILE; + + SELECT MAX(id) INTO del_count FROM t1; + WHILE del_count > 0 DO + DELETE FROM t1 WHERE id = del_count; + SET del_count = del_count - 2; + END WHILE; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE ins_count INT DEFAULT 1000; + DECLARE del_count INT; + DECLARE cur_user VARCHAR(255); + DECLARE local_uuid VARCHAR(255); + DECLARE local_time TIMESTAMP; + + SET local_time= NOW(); + SET cur_user= CURRENT_USER(); + SET local_uuid= UUID(); + + WHILE ins_count > 0 DO + INSERT INTO t2 VALUES (NULL, NOW(), USER() , UUID(), + ins_count,'Going to test MBR for MySQL'); + SET ins_count = ins_count - 1; + END WHILE; + + SELECT MAX(id) INTO del_count FROM t2; + WHILE del_count > 0 DO + DELETE FROM t2 WHERE id = del_count; + SET del_count = del_count - 2; + END WHILE; +END| + +CREATE PROCEDURE p3() +BEGIN + DECLARE ins_count INT DEFAULT 1000; + DECLARE del_count INT; + DECLARE cur_user VARCHAR(255); + DECLARE local_uuid VARCHAR(255); + DECLARE local_time TIMESTAMP; + + SET local_time= NOW(); + SET cur_user = CURRENT_USER(); + SET local_uuid=UUID(); + + WHILE ins_count > 0 DO + INSERT INTO t3 VALUES (NULL, NOW(), USER(), UUID(), + ins_count,'Going to test MBR for MySQL'); + SET ins_count = ins_count - 1; + END WHILE; + + SELECT MAX(id) INTO del_count FROM t3; + WHILE del_count > 0 DO + DELETE FROM t3 WHERE id = del_count; + SET del_count = del_count - 2; + END WHILE; +END| + +delimiter ;| + +############ Finish Setup Section ################### + + +############ Test Section ################### + +begin; +CALL p1(); +commit; +SELECT count(*) as "Master regular" FROM t1; +begin; +CALL p2(); +commit; +SELECT count(*) as "Master bykey" FROM t2; +begin; +CALL p3(); +commit; +SELECT count(*) as "Master byrange" FROM t3; + +--sync_slave_with_master +connection slave; +show create table t3; +--source include/check_slave_is_running.inc +SELECT count(*) "Slave norm" FROM t1; +SELECT count(*) "Slave bykey" FROM t2; +SELECT count(*) "Slave byrange" FROM t3; + +connection master; +set @@global.binlog_format= @old_global_binlog_format; +set @@session.binlog_format= @old_session_binlog_format; +DROP TABLE t1, t2, t3; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +sync_slave_with_master; +set @@global.binlog_format= @old_global_binlog_format; +set @@session.binlog_format= @old_session_binlog_format; + +# End of 5.1 tests +--source include/rpl_end.inc diff --git a/mysql-test/suite/parts/t/show_create.test b/mysql-test/suite/parts/t/show_create.test new file mode 100644 index 00000000..3f62ee11 --- /dev/null +++ b/mysql-test/suite/parts/t/show_create.test @@ -0,0 +1,52 @@ +# +# MDEV-13089 identifier quoting in partitioning +# +source include/have_partition.inc; +set sql_mode=ansi_quotes; +create table t1 ("select" int) partition by range ("select") (partition "select" values less than maxvalue); +create table t2 (f1 int) partition by range (f1) (partition p1 values less than maxvalue); +# "select", "f1", "p1" +show create table t1; +show create table t2; +set sql_quote_show_create=0; +# "select", f1, p1 +show create table t1; +show create table t2; +set sql_mode=default; +# `select`, f1, p1 +show create table t1; +show create table t2; +set sql_quote_show_create=1; +# `select`, `f1`, `p1` +show create table t1; +show create table t2; +# re-parse +flush tables; +# `select`, `f1`, `p1` +show create table t1; +show create table t2; +set sql_mode=ansi_quotes; +# "select", "f1", "p1" +show create table t1; +show create table t2; +drop table t1, t2; +set sql_mode=default; + +# +# MDEV-14750 Valgrind Invalid read, ASAN heap-use-after-free in Item_ident::print upon SHOW CREATE on partitioned table +# +create table t_partition (f1 int) partition by hash(f1) partitions 2; +select * from t_partition as tbl; +show create table t_partition; +drop table t_partition; + +# +# MDEV-16792 Assertion `m_status == DA_ERROR' failed in Diagnostics_area::sql_errno or wrong result upon SHOW TABLE STATUS after adding partition under ANSI_QUOTES +# +create table t1 (a int) partition by range(a) (partition p0 values less than (5)); +set sql_mode='ansi_quotes'; +alter table t1 add partition (partition p1 values less than (10)); +set sql_mode= default; +--replace_column 12 X 13 X +show table status; +drop table t1; diff --git a/mysql-test/suite/parts/t/truncate_locked.test b/mysql-test/suite/parts/t/truncate_locked.test new file mode 100644 index 00000000..4ff1016f --- /dev/null +++ b/mysql-test/suite/parts/t/truncate_locked.test @@ -0,0 +1,10 @@ +# +# MDEV-15551 Server hangs or assertion `strcmp(share->unique_file_name,filename) || share->last_version' fails in test_if_reopen or unexpected ER_LOCK_DEADLOCK +# +--source include/have_partition.inc +create table t1 (i int) engine=myisam partition by hash(i) partitions 2 ; +lock table t1 write; +truncate table t1; +desc t1; +drop table t1; + diff --git a/mysql-test/suite/parts/t/update_and_cache.test b/mysql-test/suite/parts/t/update_and_cache.test new file mode 100644 index 00000000..08ade807 --- /dev/null +++ b/mysql-test/suite/parts/t/update_and_cache.test @@ -0,0 +1,12 @@ +--source include/have_partition.inc + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); + +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); + +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; + +DROP TABLE t1, t2; |