diff options
Diffstat (limited to 'mysql-test/suite/parts/r/partition_alter3_innodb.result')
| -rw-r--r-- | mysql-test/suite/parts/r/partition_alter3_innodb.result | 813 |
1 files changed, 813 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/r/partition_alter3_innodb.result b/mysql-test/suite/parts/r/partition_alter3_innodb.result new file mode 100644 index 00000000..4d4fe8cd --- /dev/null +++ b/mysql-test/suite/parts/r/partition_alter3_innodb.result @@ -0,0 +1,813 @@ +SET @max_row = 20; +SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=0; +SET @@session.default_storage_engine = 'InnoDB'; + +#------------------------------------------------------------------------ +# 0. Setting of auxiliary variables + Creation of an auxiliary tables +# needed in many testcases +#------------------------------------------------------------------------ +SELECT @max_row DIV 2 INTO @max_row_div2; +SELECT @max_row DIV 3 INTO @max_row_div3; +SELECT @max_row DIV 4 INTO @max_row_div4; +SET @max_int_4 = 2147483647; +DROP TABLE IF EXISTS t0_template; +CREATE TABLE t0_template ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +f_char1 CHAR(20), +f_char2 CHAR(20), +f_charbig VARCHAR(1000) , +PRIMARY KEY(f_int1)) +ENGINE = MEMORY; +# Logging of <max_row> INSERTs into t0_template suppressed +DROP TABLE IF EXISTS t0_definition; +CREATE TABLE t0_definition ( +state CHAR(3), +create_command VARBINARY(5000), +file_list VARBINARY(10000), +PRIMARY KEY (state) +) ENGINE = MEMORY; +DROP TABLE IF EXISTS t0_aux; +CREATE TABLE t0_aux ( f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +f_char1 CHAR(20), +f_char2 CHAR(20), +f_charbig VARCHAR(1000) ) +ENGINE = MEMORY; +SET AUTOCOMMIT= 1; +SET @@session.sql_mode= ''; +# End of basic preparations needed for all tests +#----------------------------------------------- + +#======================================================================== +# 1. Partition management commands on HASH partitioned table +# column in partitioning function is of type DATE +#======================================================================== +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30)); +INSERT INTO t1 (f_date, f_varchar) +SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR) +FROM t0_template +WHERE f_int1 + 999 BETWEEN 1000 AND 9999; +SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1) +INTO @exp_row_count; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +t1.frm +t1.ibd +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +#------------------------------------------------------------------------ +# 1.1 Increase number of PARTITIONS +#------------------------------------------------------------------------ +# 1.1.1 ADD PARTITION to not partitioned table --> must fail +ALTER TABLE t1 ADD PARTITION (PARTITION part2); +ERROR HY000: Partition management on a not partitioned table is not possible +# 1.1.2 Assign HASH partitioning +ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +t1#P#p0.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 1.1.3 Assign other HASH partitioning to already partitioned table +# + test and switch back + test +ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (dayofyear(`f_date`)) +t1#P#p0.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +t1#P#p0.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 1.1.4 Add PARTITIONS not fitting to HASH --> must fail +ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0)); +ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition +ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0)); +ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition +# 1.1.5 Add two named partitions + test +ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 1.1.6 Add two named partitions, name clash --> must fail +ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); +ERROR HY000: Duplicate partition name part1 +# 1.1.7 Add one named partition + test +ALTER TABLE t1 ADD PARTITION (PARTITION part2); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 1.1.8 Add four not named partitions + test +ALTER TABLE t1 ADD PARTITION PARTITIONS 4; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB, + PARTITION `p5` ENGINE = InnoDB, + PARTITION `p6` ENGINE = InnoDB, + PARTITION `p7` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#p5.ibd +t1#P#p6.ibd +t1#P#p7.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +#------------------------------------------------------------------------ +# 1.2 Decrease number of PARTITIONS +#------------------------------------------------------------------------ +# 1.2.1 DROP PARTITION is not supported for HASH --> must fail +ALTER TABLE t1 DROP PARTITION part1; +ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions +# 1.2.2 COALESCE PARTITION partitionname is not supported +ALTER TABLE t1 COALESCE PARTITION part1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'part1' at line 1 +# 1.2.3 Decrease by 0 is non sense --> must fail +ALTER TABLE t1 COALESCE PARTITION 0; +ERROR HY000: At least one partition must be coalesced +# 1.2.4 COALESCE one partition + test loop +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB, + PARTITION `p5` ENGINE = InnoDB, + PARTITION `p6` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#p5.ibd +t1#P#p6.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB, + PARTITION `p5` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#p5.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY HASH (year(`f_date`)) +(PARTITION `p0` ENGINE = InnoDB) +t1#P#p0.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 1.2.5 COALESCE of last partition --> must fail +ALTER TABLE t1 COALESCE PARTITION 1; +ERROR HY000: Cannot remove all partitions, use DROP TABLE instead +# 1.2.6 Remove partitioning +ALTER TABLE t1 REMOVE PARTITIONING; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_date` date DEFAULT NULL, + `f_varchar` varchar(30) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +t1.frm +t1.ibd +EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 1.2.7 Remove partitioning from not partitioned table --> ???? +ALTER TABLE t1 REMOVE PARTITIONING; +ERROR HY000: Partition management on a not partitioned table is not possible +DROP TABLE t1; +# Attention: There are unused files. +# Either the DROP TABLE or a preceding ALTER TABLE +# <alter partitioning> worked incomplete. +# We found: +unified filelist +--- not determined --- + +#======================================================================== +# 2. Partition management commands on KEY partitioned table +#======================================================================== +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +f_int1 INTEGER DEFAULT 0, +f_int2 INTEGER DEFAULT 0, +f_char1 CHAR(20), +f_char2 CHAR(20), +f_charbig VARCHAR(1000) +); +INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) +SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +t1.frm +t1.ibd +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +#------------------------------------------------------------------------ +# 2.1 Increase number of PARTITIONS +# Some negative testcases are omitted (already checked with HASH). +#------------------------------------------------------------------------ +# 2.1.1 Assign KEY partitioning +ALTER TABLE t1 PARTITION BY KEY(f_int1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +t1#P#p0.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 2.1.2 Add PARTITIONS not fitting to KEY --> must fail +ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0)); +ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition +ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0)); +ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition +# 2.1.3 Add two named partitions + test +ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 2.1.4 Add one named partition + test +ALTER TABLE t1 ADD PARTITION (PARTITION part2); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 2.1.5 Add four not named partitions + test +ALTER TABLE t1 ADD PARTITION PARTITIONS 4; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB, + PARTITION `p5` ENGINE = InnoDB, + PARTITION `p6` ENGINE = InnoDB, + PARTITION `p7` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#p5.ibd +t1#P#p6.ibd +t1#P#p7.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +#------------------------------------------------------------------------ +# 2.2 Decrease number of PARTITIONS +# Some negative testcases are omitted (already checked with HASH). +#------------------------------------------------------------------------ +# 2.2.1 DROP PARTITION is not supported for KEY --> must fail +ALTER TABLE t1 DROP PARTITION part1; +ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions +# 2.2.4 COALESCE one partition + test loop +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB, + PARTITION `p5` ENGINE = InnoDB, + PARTITION `p6` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#p5.ibd +t1#P#p6.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB, + PARTITION `p5` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#p5.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB, + PARTITION `p4` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#p4.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB, + PARTITION `part2` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part2.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB, + PARTITION `part7` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1#P#part7.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB, + PARTITION `part1` ENGINE = InnoDB) +t1#P#p0.ibd +t1#P#part1.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +ALTER TABLE t1 COALESCE PARTITION 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + PARTITION BY KEY (`f_int1`) +(PARTITION `p0` ENGINE = InnoDB) +t1#P#p0.ibd +t1.frm +t1.par +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 2.2.5 COALESCE of last partition --> must fail +ALTER TABLE t1 COALESCE PARTITION 1; +ERROR HY000: Cannot remove all partitions, use DROP TABLE instead +# 2.2.6 Remove partitioning +ALTER TABLE t1 REMOVE PARTITIONING; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f_int1` int(11) DEFAULT 0, + `f_int2` int(11) DEFAULT 0, + `f_char1` char(20) DEFAULT NULL, + `f_char2` char(20) DEFAULT NULL, + `f_charbig` varchar(1000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +t1.frm +t1.ibd +EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where +# check read single success: 1 +# check read all success: 1 +# check read row by row success: 1 +# 2.2.7 Remove partitioning from not partitioned table --> ???? +ALTER TABLE t1 REMOVE PARTITIONING; +ERROR HY000: Partition management on a not partitioned table is not possible +DROP TABLE t1; +# Attention: There are unused files. +# Either the DROP TABLE or a preceding ALTER TABLE +# <alter partitioning> worked incomplete. +# We found: +unified filelist +--- not determined --- +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t0_aux; +DROP TABLE IF EXISTS t0_definition; +DROP TABLE IF EXISTS t0_template; +SET GLOBAL innodb_stats_persistent=@save_stats_persistent; |