Edit File: partition_alter3_myisam.result
SET @max_row = 20; SET @@session.storage_engine = 'MyISAM'; #------------------------------------------------------------------------ # 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, f_int2 INTEGER, 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, f_int2 INTEGER, 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=MyISAM DEFAULT CHARSET=latin1 t1.MYD t1.MYI t1.frm 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) */ t1#P#p0.MYD t1#P#p0.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */ t1#P#p0.MYD t1#P#p0.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) */ t1#P#p0.MYD t1#P#p0.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM, PARTITION p5 ENGINE = MyISAM, PARTITION p6 ENGINE = MyISAM, PARTITION p7 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#p5.MYD t1#P#p5.MYI t1#P#p6.MYD t1#P#p6.MYI t1#P#p7.MYD t1#P#p7.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 MySQL 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM, PARTITION p5 ENGINE = MyISAM, PARTITION p6 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#p5.MYD t1#P#p5.MYI t1#P#p6.MYD t1#P#p6.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM, PARTITION p5 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#p5.MYD t1#P#p5.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part7.MYD t1#P#part7.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI 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=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (YEAR(f_date)) (PARTITION p0 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI 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=MyISAM DEFAULT CHARSET=latin1 t1.MYD t1.MYI t1.frm 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; DROP TABLE t1; #======================================================================== # 2. Partition management commands on KEY partitioned table #======================================================================== DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER, 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 t1.MYD t1.MYI t1.frm 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) */ t1#P#p0.MYD t1#P#p0.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM, PARTITION p5 ENGINE = MyISAM, PARTITION p6 ENGINE = MyISAM, PARTITION p7 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#p5.MYD t1#P#p5.MYI t1#P#p6.MYD t1#P#p6.MYI t1#P#p7.MYD t1#P#p7.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM, PARTITION p5 ENGINE = MyISAM, PARTITION p6 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#p5.MYD t1#P#p5.MYI t1#P#p6.MYD t1#P#p6.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM, PARTITION p5 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#p5.MYD t1#P#p5.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM, PARTITION p4 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#p4.MYD t1#P#p4.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part2.MYD t1#P#part2.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM, PARTITION part7 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI t1#P#part7.MYD t1#P#part7.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM, PARTITION part1 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI t1#P#part1.MYD t1#P#part1.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (f_int1) (PARTITION p0 ENGINE = MyISAM) */ t1#P#p0.MYD t1#P#p0.MYI 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 NULL, `f_int2` int(11) DEFAULT NULL, `f_char1` char(20) DEFAULT NULL, `f_char2` char(20) DEFAULT NULL, `f_charbig` varchar(1000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 t1.MYD t1.MYI t1.frm 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; DROP TABLE t1; 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;
Back to File Manager