1
drop table if exists t1;
2
CREATE TABLE t1 (a int, b int)
4
(PARTITION x0 VALUES LESS THAN (2),
5
PARTITION x1 VALUES LESS THAN (4),
6
PARTITION x2 VALUES LESS THAN (6),
7
PARTITION x3 VALUES LESS THAN (8),
8
PARTITION x4 VALUES LESS THAN (10),
9
PARTITION x5 VALUES LESS THAN (12),
10
PARTITION x6 VALUES LESS THAN (14),
11
PARTITION x7 VALUES LESS THAN (16),
12
PARTITION x8 VALUES LESS THAN (18),
13
PARTITION x9 VALUES LESS THAN (20));
14
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
15
(PARTITION x01 VALUES LESS THAN (2),
16
PARTITION x11 VALUES LESS THAN (5));
17
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
18
ALTER TABLE t1 DROP PARTITION x0, x1, x2, x3, x3;
19
ERROR HY000: Error in list of partitions to DROP
20
ALTER TABLE t1 DROP PARTITION x0, x1, x2, x10;
21
ERROR HY000: Error in list of partitions to DROP
22
ALTER TABLE t1 DROP PARTITION x10, x1, x2, x1;
23
ERROR HY000: Error in list of partitions to DROP
24
ALTER TABLE t1 DROP PARTITION x10, x1, x2, x3;
25
ERROR HY000: Error in list of partitions to DROP
26
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO
27
(PARTITION x11 VALUES LESS THAN (22));
28
ERROR HY000: More partitions to reorganize than there are partitions
29
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
30
(PARTITION x3 VALUES LESS THAN (6));
31
ERROR HY000: Duplicate partition name x3
32
ALTER TABLE t1 REORGANIZE PARTITION x0, x2 INTO
33
(PARTITION x11 VALUES LESS THAN (2));
34
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
35
ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO
36
(PARTITION x11 VALUES LESS THAN (4));
37
ERROR HY000: Error in list of partitions to REORGANIZE
38
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
39
(PARTITION x01 VALUES LESS THAN (5));
40
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
41
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
42
(PARTITION x01 VALUES LESS THAN (4),
43
PARTITION x11 VALUES LESS THAN (2));
44
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
45
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
46
(PARTITION x01 VALUES LESS THAN (6),
47
PARTITION x11 VALUES LESS THAN (4));
48
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
50
CREATE TABLE t1 (a int)
53
ALTER TABLE t1 ADD PARTITION (PARTITION p1);
54
ERROR HY000: Duplicate partition name p1
56
CREATE TABLE t1 (a int)
58
(PARTITION x0, PARTITION x1, PARTITION x2, PARTITION x3, PARTITION x3);
59
ERROR HY000: Duplicate partition name x3
60
CREATE TABLE t1 (a int)
61
PARTITION BY RANGE (a)
62
SUBPARTITION BY KEY (a)
64
(PARTITION x0 VALUES LESS THAN (4),
65
PARTITION x1 VALUES LESS THAN (8));
66
ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (5)
67
(SUBPARTITION sp0, SUBPARTITION sp1));
68
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
69
ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (12)
70
(SUBPARTITION sp0, SUBPARTITION sp1, SUBPARTITION sp2));
71
ERROR HY000: Trying to Add partition(s) with wrong number of subpartitions
73
CREATE TABLE t1 (a int)
75
(PARTITION x0 VALUES IN (1,2,3),
76
PARTITION x1 VALUES IN (4,5,6));
77
ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES IN (3,4));
78
ERROR HY000: Multiple definition of same constant in list partitioning
80
CREATE TABLE t1 (a int);
81
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
82
ERROR HY000: Partition management on a not partitioned table is not possible
83
ALTER TABLE t1 DROP PARTITION x1;
84
ERROR HY000: Partition management on a not partitioned table is not possible
85
ALTER TABLE t1 COALESCE PARTITION 1;
86
ERROR HY000: Partition management on a not partitioned table is not possible
87
ALTER TABLE t1 ANALYZE PARTITION p1;
88
ERROR HY000: Partition management on a not partitioned table is not possible
89
ALTER TABLE t1 CHECK PARTITION p1;
90
ERROR HY000: Partition management on a not partitioned table is not possible
91
ALTER TABLE t1 OPTIMIZE PARTITION p1;
92
ERROR HY000: Partition management on a not partitioned table is not possible
93
ALTER TABLE t1 REPAIR PARTITION p1;
94
ERROR HY000: Partition management on a not partitioned table is not possible
96
CREATE TABLE t1 (a int)
98
(PARTITION x0, PARTITION x1);
99
ALTER TABLE t1 ADD PARTITION PARTITIONS 0;
100
ERROR HY000: At least one partition must be added
101
ALTER TABLE t1 ADD PARTITION PARTITIONS 1024;
102
ERROR HY000: Too many partitions (including subpartitions) were defined
103
ALTER TABLE t1 DROP PARTITION x0;
104
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
105
ALTER TABLE t1 COALESCE PARTITION 1;
106
ALTER TABLE t1 COALESCE PARTITION 1;
107
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
109
CREATE TABLE t1 (a int)
110
PARTITION BY RANGE (a)
111
(PARTITION x0 VALUES LESS THAN (4),
112
PARTITION x1 VALUES LESS THAN (8));
113
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
114
ERROR HY000: For RANGE partitions each partition must be defined
115
ALTER TABLE t1 DROP PARTITION x2;
116
ERROR HY000: Error in list of partitions to DROP
117
ALTER TABLE t1 COALESCE PARTITION 1;
118
ERROR HY000: COALESCE PARTITION can only be used on HASH/KEY partitions
119
ALTER TABLE t1 DROP PARTITION x1;
120
ALTER TABLE t1 DROP PARTITION x0;
121
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
123
CREATE TABLE t1 ( id INT NOT NULL,
124
fname VARCHAR(50) NOT NULL,
125
lname VARCHAR(50) NOT NULL,
126
hired DATE NOT NULL )
127
PARTITION BY RANGE(YEAR(hired)) (
128
PARTITION p1 VALUES LESS THAN (1991),
129
PARTITION p2 VALUES LESS THAN (1996),
130
PARTITION p3 VALUES LESS THAN (2001),
131
PARTITION p4 VALUES LESS THAN (2005));
132
ALTER TABLE t1 ADD PARTITION (
133
PARTITION p5 VALUES LESS THAN (2010),
134
PARTITION p6 VALUES LESS THAN MAXVALUE);
136
CREATE TABLE t1 (a INT);
137
SHOW CREATE TABLE t1;
139
t1 CREATE TABLE `t1` (
140
`a` int(11) DEFAULT NULL
141
) ENGINE=MyISAM DEFAULT CHARSET=latin1
142
ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 2;
143
SHOW CREATE TABLE t1;
145
t1 CREATE TABLE `t1` (
146
`a` int(11) DEFAULT NULL
147
) ENGINE=MyISAM DEFAULT CHARSET=latin1
148
/*!50100 PARTITION BY KEY (a)
151
CREATE TABLE t1 (a INT) PARTITION BY HASH(a);
152
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
154
CREATE TABLE t1 (s1 int, s2 int)
155
PARTITION BY LIST (s1)
156
SUBPARTITION BY KEY (s2) (
157
PARTITION p1 VALUES IN (0) (SUBPARTITION p1b),
158
PARTITION p2 VALUES IN (2) (SUBPARTITION p1b)
160
ERROR HY000: Duplicate partition name p1b