1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
|
set @save_alter_algorithm= @@session.alter_algorithm;
SET SESSION alter_algorithm=4;
CREATE TABLE t1(a INT) engine=myisam PARTITION BY RANGE(a) SUBPARTITION BY KEY(a) (PARTITION p0 VALUES LESS THAN (10) (SUBPARTITION s0,SUBPARTITION s1), PARTITION p1 VALUES LESS THAN (20) (SUBPARTITION s2,SUBPARTITION s3));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`a`)
SUBPARTITION BY KEY (`a`)
(PARTITION `p0` VALUES LESS THAN (10)
(SUBPARTITION `s0` ENGINE = MyISAM,
SUBPARTITION `s1` ENGINE = MyISAM),
PARTITION `p1` VALUES LESS THAN (20)
(SUBPARTITION `s2` ENGINE = MyISAM,
SUBPARTITION `s3` ENGINE = MyISAM))
ALTER TABLE t1 ADD COLUMN c INT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY
DROP table if exists t1;
set @@session.alter_algorithm= @save_alter_algorithm;
CREATE TABLE t1 (a INT) PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (PARTITION p VALUES LESS THAN (5) (SUBPARTITION sp, SUBPARTITION sp1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION sp2, SUBPARTITION sp3));
ALTER TABLE t1 DROP PARTITION p;
DROP TABLE if exists t1;
CREATE TABLE t1 (i INT);
CREATE VIEW v1 as SELECT * FROM t1;
CREATE TABLE t2 (i INT);
ALTER TABLE v1 EXCHANGE PARTITION p2 WITH TABLE t2 ;
ERROR 42000: Can't open table
DROP VIEW v1;
DROP TABLE t1, t2;
#
# MDEV-22165 CONVERT PARTITION: move in partition from existing table
#
create or replace table tp1 (a int);
create or replace table t1 (a int)
partition by hash (a) partitions 2;
alter table t1 convert table tp1 to partition p2;
ERROR HY000: CONVERT TABLE TO PARTITION can only be used on RANGE/LIST partitions
create or replace table t1 (a int)
partition by range (a)
(partition p0 values less than (0));
alter table t1 convert table non_existent to partition p1 values less than (10);
ERROR 42S02: Table 'test.non_existent' doesn't exist
alter table t1 convert table tp1 to partition p1 values less than (10);
show create table tp1;
ERROR 42S02: Table 'test.tp1' doesn't exist
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (0) ENGINE = X,
PARTITION `p1` VALUES LESS THAN (10) ENGINE = X)
create table tp2 (x int);
alter table t1 convert table tp2 to partition p2 values less than (20);
ERROR HY000: Tables have different definitions
show create table tp2;
Table Create Table
tp2 CREATE TABLE `tp2` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (0) ENGINE = X,
PARTITION `p1` VALUES LESS THAN (10) ENGINE = X)
create or replace table tp2 (a int);
insert tp2 values (1), (15), (17);
alter table t1 convert table tp2 to partition p2 values less than (20);
ERROR HY000: Found a row that does not match the partition
delete from tp2;
insert tp2 values (15), (1), (17);
alter table t1 convert table tp2 to partition p2 values less than (20);
ERROR HY000: Found a row that does not match the partition
delete from tp2;
insert tp2 values (15), (17), (1);
alter table t1 convert table tp2 to partition p2 values less than (20);
ERROR HY000: Found a row that does not match the partition
delete from tp2;
insert tp2 values (15), (17);
alter table t1 convert table tp2 to partition p2 values less than (20);
show create table tp2;
ERROR 42S02: Table 'test.tp2' doesn't exist
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (0) ENGINE = X,
PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
PARTITION `p2` VALUES LESS THAN (20) ENGINE = X)
select * from t1 partition (p2);
a
15
17
create or replace table t1 (a int)
partition by range (a) (
p0 values less than (0),
pn values less than (30));
insert into t1 values (1);
create or replace table tp1 (a int);
insert into tp1 values (2);
alter table t1 convert table tp1 to partition p1 values less than (10);
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
drop tables t1, tp1;
#
# MDEV-22166 CONVERT PARTITION: move out partition into a table
#
create or replace table t1 (x int);
alter table t1 convert partition p1 to table tp1;
ERROR HY000: Partition management on a not partitioned table is not possible
create or replace table t1 (x int)
partition by hash(x) partitions 2;
alter table t1 convert partition p1 to table tp1;
ERROR HY000: CONVERT PARTITION can only be used on RANGE/LIST partitions
create or replace table t1 (x int)
partition by key(x) partitions 2;
alter table t1 convert partition p1 to table tp1;
ERROR HY000: CONVERT PARTITION can only be used on RANGE/LIST partitions
create or replace table t1 (x int)
partition by range(x)
subpartition by hash(x) subpartitions 3 (
partition p1 values less than (10),
partition pn values less than maxvalue);
alter table t1 convert partition p1 to table p1;
ERROR HY000: Convert partition is not supported for subpartitioned table.
alter table t1 convert partition p1sp0 to table p1;
ERROR HY000: Wrong partition name or partition list
create or replace table t1 (x int)
partition by range(x) (
partition p1 values less than (10));
alter table t1 convert partition p1 to table tp1;
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
create or replace temporary table t1 (x int)
partition by range(x) (
partition p0 values less than (10),
partition pn values less than maxvalue);
ERROR HY000: Partitioned tables do not support CREATE TEMPORARY TABLE
create or replace table t1 (x int)
partition by range(x) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40),
partition p5 values less than (50),
partition pn values less than maxvalue);
insert into t1 values (2), (12), (22), (32), (42), (52);
create or replace table tp2 (y int);
insert tp2 values (88);
alter table t1 convert partition p2 to table tp2, drop partition p3;
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 ' drop partition p3' at line 1
alter table t1 convert partition p00 to table tp00;
ERROR HY000: Wrong partition name or partition list
alter table t1 convert partition p00 to table tp2;
ERROR 42S01: Table 'tp2' already exists
alter table t1 convert partition p2 to table tp2;
ERROR 42S01: Table 'tp2' already exists
create trigger tr1 before update on t1 for each row
begin
alter table t1 convert partition p2 to table tp2;
end$
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
create function f1() returns int
begin
alter table t1 convert partition p2 to table tp2;
end$
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
select * from tp2;
y
88
drop table tp2;
alter table t1 convert partition p2 to table tp2;
show create table tp2;
Table Create Table
tp2 CREATE TABLE `tp2` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select * from tp2;
x
12
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`x`)
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
PARTITION `p3` VALUES LESS THAN (30) ENGINE = X,
PARTITION `p4` VALUES LESS THAN (40) ENGINE = X,
PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
select * from t1 order by x;
x
2
22
32
42
52
alter table t1 convert partition p3 to table inexistent.tp3;
ERROR 42000: Unknown database 'inexistent'
create database EXISTENT;
alter table t1 convert partition p3 to table EXISTENT.TP3;
show create table EXISTENT.TP3;
Table Create Table
TP3 CREATE TABLE `TP3`-ok (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select * from EXISTENT.TP3 order by x;
x
22
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`x`)
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
PARTITION `p4` VALUES LESS THAN (40) ENGINE = X,
PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
select * from t1 order by x;
x
2
32
42
52
# LOCK TABLES
lock tables t1 write;
alter table t1 convert partition p4 to table tp4;
show create table tp4;
ERROR HY000: Table 'tp4' was not locked with LOCK TABLES
unlock tables;
show create table tp4;
Table Create Table
tp4 CREATE TABLE `tp4` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select * from tp4;
x
32
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`x`)
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
select * from t1 order by x;
x
2
42
52
# PS
prepare stmt from 'alter table t1 convert partition p5 to table tp5';
execute stmt;
show create table tp5;
Table Create Table
tp5 CREATE TABLE `tp5` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select * from tp5;
x
42
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`x` int(11) DEFAULT NULL
) ENGINE=X DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`x`)
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
select * from t1 order by x;
x
2
52
drop table tp5;
execute stmt;
ERROR HY000: Wrong partition name or partition list
execute stmt;
ERROR HY000: Wrong partition name or partition list
drop prepare stmt;
# Privileges
create user alan;
grant usage on *.* to alan;
grant create, insert, drop on test.* to alan;
connect alan,localhost,alan,,test;
show grants for current_user;
Grants for alan@%
GRANT USAGE ON *.* TO `alan`@`%`
GRANT INSERT, CREATE, DROP ON `test`.* TO `alan`@`%`
alter table t1 convert partition p1 to table tp1;
ERROR 42000: ALTER command denied to user 'alan'@'localhost' for table 't1'
connection default;
revoke all on test.* from alan;
grant create, insert, alter on test.* to alan;
connection alan;
use test;
show grants for current_user;
Grants for alan@%
GRANT USAGE ON *.* TO `alan`@`%`
GRANT INSERT, CREATE, ALTER ON `test`.* TO `alan`@`%`
alter table t1 convert partition p1 to table tp1;
ERROR 42000: DROP command denied to user 'alan'@'localhost' for table 't1'
connection default;
revoke all on test.* from alan;
grant create, drop, alter on test.* to alan;
connection alan;
use test;
show grants for current_user;
Grants for alan@%
GRANT USAGE ON *.* TO `alan`@`%`
GRANT CREATE, DROP, ALTER ON `test`.* TO `alan`@`%`
alter table t1 convert partition p1 to table tp1;
ERROR 42000: INSERT command denied to user 'alan'@'localhost' for table 'tp1'
connection default;
revoke all on test.* from alan;
grant insert, drop, alter on test.* to alan;
connection alan;
use test;
show grants for current_user;
Grants for alan@%
GRANT USAGE ON *.* TO `alan`@`%`
GRANT INSERT, DROP, ALTER ON `test`.* TO `alan`@`%`
alter table t1 convert partition p1 to table tp1;
ERROR 42000: CREATE command denied to user 'alan'@'localhost' for table 'tp1'
connection default;
grant create, insert, drop, alter on test.* to alan;
connection alan;
use test;
show grants for current_user;
Grants for alan@%
GRANT USAGE ON *.* TO `alan`@`%`
GRANT INSERT, CREATE, DROP, ALTER ON `test`.* TO `alan`@`%`
alter table t1 convert partition p1 to table tp1;
disconnect alan;
connection default;
drop database EXISTENT;
drop user alan;
drop tables t1, tp1, tp2, tp4;
|
