mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
892 lines
29 KiB
Text
892 lines
29 KiB
Text
SET @max_row = 20;
|
|
SET @@session.default_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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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 COLLATE=latin1_swedish_ci
|
|
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;
|
|
ERROR HY000: Partition management on a not partitioned table is not possible
|
|
DROP TABLE t1;
|
|
|
|
#========================================================================
|
|
# 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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;
|
|
ERROR HY000: Partition management on a not partitioned table is not possible
|
|
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;
|