2
# Simple test for the erroneos create statements using the
3
# partition storage engine
5
-- source include/have_partition.inc
8
drop table if exists t1;
12
# Try faulty DROP PARTITION and COALESCE PARTITION
14
CREATE TABLE t1 (a int, b int)
15
PARTITION BY RANGE (a)
16
(PARTITION x0 VALUES LESS THAN (2),
17
PARTITION x1 VALUES LESS THAN (4),
18
PARTITION x2 VALUES LESS THAN (6),
19
PARTITION x3 VALUES LESS THAN (8),
20
PARTITION x4 VALUES LESS THAN (10),
21
PARTITION x5 VALUES LESS THAN (12),
22
PARTITION x6 VALUES LESS THAN (14),
23
PARTITION x7 VALUES LESS THAN (16),
24
PARTITION x8 VALUES LESS THAN (18),
25
PARTITION x9 VALUES LESS THAN (20));
27
--error ER_REORG_OUTSIDE_RANGE
28
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
29
(PARTITION x01 VALUES LESS THAN (2),
30
PARTITION x11 VALUES LESS THAN (5));
32
--error ER_DROP_PARTITION_NON_EXISTENT
33
ALTER TABLE t1 DROP PARTITION x0, x1, x2, x3, x3;
35
--error ER_DROP_PARTITION_NON_EXISTENT
36
ALTER TABLE t1 DROP PARTITION x0, x1, x2, x10;
38
--error ER_DROP_PARTITION_NON_EXISTENT
39
ALTER TABLE t1 DROP PARTITION x10, x1, x2, x1;
41
--error ER_DROP_PARTITION_NON_EXISTENT
42
ALTER TABLE t1 DROP PARTITION x10, x1, x2, x3;
44
--error ER_REORG_PARTITION_NOT_EXIST
45
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO
46
(PARTITION x11 VALUES LESS THAN (22));
48
--error ER_SAME_NAME_PARTITION
49
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
50
(PARTITION x3 VALUES LESS THAN (6));
52
--error ER_CONSECUTIVE_REORG_PARTITIONS
53
ALTER TABLE t1 REORGANIZE PARTITION x0, x2 INTO
54
(PARTITION x11 VALUES LESS THAN (2));
56
--error ER_DROP_PARTITION_NON_EXISTENT
57
ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO
58
(PARTITION x11 VALUES LESS THAN (4));
60
--error ER_REORG_OUTSIDE_RANGE
61
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
62
(PARTITION x01 VALUES LESS THAN (5));
64
--error ER_REORG_OUTSIDE_RANGE
65
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
66
(PARTITION x01 VALUES LESS THAN (4),
67
PARTITION x11 VALUES LESS THAN (2));
69
--error ER_RANGE_NOT_INCREASING_ERROR
70
ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
71
(PARTITION x01 VALUES LESS THAN (6),
72
PARTITION x11 VALUES LESS THAN (4));
76
CREATE TABLE t1 (a int)
80
--error ER_SAME_NAME_PARTITION
81
ALTER TABLE t1 ADD PARTITION (PARTITION p1);
85
--error ER_SAME_NAME_PARTITION
86
CREATE TABLE t1 (a int)
88
(PARTITION x0, PARTITION x1, PARTITION x2, PARTITION x3, PARTITION x3);
90
CREATE TABLE t1 (a int)
91
PARTITION BY RANGE (a)
92
SUBPARTITION BY KEY (a)
94
(PARTITION x0 VALUES LESS THAN (4),
95
PARTITION x1 VALUES LESS THAN (8));
97
--error ER_RANGE_NOT_INCREASING_ERROR
98
ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (5)
99
(SUBPARTITION sp0, SUBPARTITION sp1));
101
--error ER_ADD_PARTITION_SUBPART_ERROR
102
ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (12)
103
(SUBPARTITION sp0, SUBPARTITION sp1, SUBPARTITION sp2));
107
CREATE TABLE t1 (a int)
108
PARTITION BY LIST (a)
109
(PARTITION x0 VALUES IN (1,2,3),
110
PARTITION x1 VALUES IN (4,5,6));
112
--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
113
ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES IN (3,4));
117
CREATE TABLE t1 (a int);
119
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
120
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
122
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
123
ALTER TABLE t1 DROP PARTITION x1;
125
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
126
ALTER TABLE t1 COALESCE PARTITION 1;
128
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
129
ALTER TABLE t1 ANALYZE PARTITION p1;
131
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
132
ALTER TABLE t1 CHECK PARTITION p1;
134
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
135
ALTER TABLE t1 OPTIMIZE PARTITION p1;
137
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
138
ALTER TABLE t1 REPAIR PARTITION p1;
142
CREATE TABLE t1 (a int)
144
(PARTITION x0, PARTITION x1);
146
--error ER_ADD_PARTITION_NO_NEW_PARTITION
147
ALTER TABLE t1 ADD PARTITION PARTITIONS 0;
149
--error ER_TOO_MANY_PARTITIONS_ERROR
150
ALTER TABLE t1 ADD PARTITION PARTITIONS 1024;
152
--error ER_ONLY_ON_RANGE_LIST_PARTITION
153
ALTER TABLE t1 DROP PARTITION x0;
155
ALTER TABLE t1 COALESCE PARTITION 1;
157
--error ER_DROP_LAST_PARTITION
158
ALTER TABLE t1 COALESCE PARTITION 1;
162
CREATE TABLE t1 (a int)
163
PARTITION BY RANGE (a)
164
(PARTITION x0 VALUES LESS THAN (4),
165
PARTITION x1 VALUES LESS THAN (8));
167
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
168
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
170
--error ER_DROP_PARTITION_NON_EXISTENT
171
ALTER TABLE t1 DROP PARTITION x2;
173
--error ER_COALESCE_ONLY_ON_HASH_PARTITION
174
ALTER TABLE t1 COALESCE PARTITION 1;
176
ALTER TABLE t1 DROP PARTITION x1;
178
--error ER_DROP_LAST_PARTITION
179
ALTER TABLE t1 DROP PARTITION x0;
184
# Bug# 16534 - Trying to add multiple partitions crashes server
186
CREATE TABLE t1 ( id INT NOT NULL,
187
fname VARCHAR(50) NOT NULL,
188
lname VARCHAR(50) NOT NULL,
189
hired DATE NOT NULL )
190
PARTITION BY RANGE(YEAR(hired)) (
191
PARTITION p1 VALUES LESS THAN (1991),
192
PARTITION p2 VALUES LESS THAN (1996),
193
PARTITION p3 VALUES LESS THAN (2001),
194
PARTITION p4 VALUES LESS THAN (2005));
195
ALTER TABLE t1 ADD PARTITION (
196
PARTITION p5 VALUES LESS THAN (2010),
197
PARTITION p6 VALUES LESS THAN MAXVALUE);
201
#BUG 15523 ALTER TABLE ... PARTITION BY does not work
203
CREATE TABLE t1 (a INT);
204
SHOW CREATE TABLE t1;
205
ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 2;
206
SHOW CREATE TABLE t1;
210
#BUG 15820 create table with 1 partition, doing ALTER TABLE ADD PARTITION fails
212
CREATE TABLE t1 (a INT) PARTITION BY HASH(a);
213
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
217
#BUG 15408: Partitions: subpartition names are not unique
219
--error ER_SAME_NAME_PARTITION
220
CREATE TABLE t1 (s1 int, s2 int)
221
PARTITION BY LIST (s1)
222
SUBPARTITION BY KEY (s2) (
223
PARTITION p1 VALUES IN (0) (SUBPARTITION p1b),
224
PARTITION p2 VALUES IN (2) (SUBPARTITION p1b)