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
|
# Test for COALESCE PARTITION, ALTER TABLE and ADD PARTITIONS
# for tables with HASH partitions
CREATE TABLE t1 (
c1 INT DEFAULT NULL
) ENGINE=Aria
PARTITION BY HASH (c1)
PARTITIONS 3;
INSERT INTO t1 VALUE (1), (2), (101), (102), (201), (202);
ALTER TABLE t1 ENGINE=S3;
SELECT count(*) FROM t1;
count(*)
6
SHOW TABLES;
Tables_in_s3
t1
ALTER TABLE t1 COALESCE PARTITION 2;
ERROR 42000: Table 't1' uses an extension that doesn't exist in this MariaDB version
SHOW WARNINGS;
Level Code Message
Error 1112 Table 't1' uses an extension that doesn't exist in this MariaDB version
ALTER TABLE t1 ADD PARTITION PARTITIONS 6;
SELECT count(*) FROM t1;
count(*)
6
ALTER TABLE t1 ADD COLUMN c INT;
SELECT count(*) FROM t1;
count(*)
6
DROP TABLE t1;
# Test for simple change engine to S3
CREATE TABLE t1 (
c1 int DEFAULT NULL,
c2 int DEFAULT NULL
) ENGINE=Aria
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH(c2)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (300));
INSERT INTO t1 VALUE (1,1), (2,2), (101,101), (102,102), (201,201), (202,202);
ALTER TABLE t1 ENGINE=S3;
SELECT count(*) FROM t1;
count(*)
6
# Test for rename table
RENAME TABLE t1 TO t2;
SELECT count(*) FROM t2;
count(*)
6
# Test for TRUNCATE, ANALYZE, CHECK, REBUILD, OPTIMIZE, REPAIR,
# ADD, DROP, REORGANIZE partition
ALTER TABLE t2 TRUNCATE PARTITION p3;
ERROR HY000: Table 't2' is read only
ALTER TABLE t2 ANALYZE PARTITION p3;
Table Op Msg_type Msg_text
s3.t2 analyze status Table 's3.t2' is read only
s3.t2 analyze status Engine-independent statistics collected
s3.t2 analyze status OK
SELECT count(*) FROM t2;
count(*)
6
ALTER TABLE t2 CHECK PARTITION p3;
Table Op Msg_type Msg_text
s3.t2 check status OK
SELECT count(*) FROM t2;
count(*)
6
ALTER TABLE t2 REBUILD PARTITION p0, p1;
ERROR 42000: Table 't2' uses an extension that doesn't exist in this MariaDB version
ALTER TABLE t2 OPTIMIZE PARTITION p0, p1;
Table Op Msg_type Msg_text
s3.t2 optimize Error Table 't2' is read only
s3.t2 optimize status Operation failed
SELECT count(*) FROM t2;
count(*)
6
ALTER TABLE t2 REPAIR PARTITION p0, p1;
Table Op Msg_type Msg_text
s3.t2 repair Error Table 't2' is read only
s3.t2 repair status Operation failed
SELECT count(*) FROM t2;
count(*)
6
ALTER TABLE t2 ADD PARTITION (PARTITION p4 VALUES LESS THAN (400));
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`c1`)
SUBPARTITION BY HASH (`c2`)
SUBPARTITIONS 2
(PARTITION `p0` VALUES LESS THAN (100) ENGINE = S3,
PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
PARTITION `p3` VALUES LESS THAN (300) ENGINE = S3,
PARTITION `p4` VALUES LESS THAN (400) ENGINE = S3)
ALTER TABLE t2
REORGANIZE PARTITION p4 INTO (
PARTITION n0 VALUES LESS THAN (500),
PARTITION n1 VALUES LESS THAN (600)
);
ERROR 42000: Table 't2' uses an extension that doesn't exist in this MariaDB version
ALTER TABLE t2 DROP PARTITION p3;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (`c1`)
SUBPARTITION BY HASH (`c2`)
SUBPARTITIONS 2
(PARTITION `p0` VALUES LESS THAN (100) ENGINE = S3,
PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
PARTITION `p4` VALUES LESS THAN (400) ENGINE = S3)
SELECT count(*) from t2;
count(*)
4
# Test for ALTER TABLE
ALTER TABLE t2 ADD COLUMN c INT;
SELECT count(*) FROM t2;
count(*)
4
ALTER TABLE t2 DROP COLUMN c;
SELECT count(*) FROM t2;
count(*)
4
# Test for REMOVE PARTITIONING
ALTER TABLE t2 REMOVE PARTITIONING;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
SELECT count(*) FROM t2;
count(*)
4
DROP TABLE t2;
# Test for EXCHANGE PARTITION
CREATE TABLE t1 (
c1 int DEFAULT NULL
) ENGINE=Aria
PARTITION BY RANGE (c1)
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200));
INSERT INTO t1 VALUE (1), (2), (101), (102);
ALTER TABLE t1 ENGINE=S3;
CREATE TABLE t_part (
c1 int DEFAULT NULL
) ENGINE=Aria;
INSERT INTO t_part VALUE (120), (130), (140);
ALTER TABLE t_part ENGINE=S3;
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t_part;
SELECT count(*) FROM t_part;
count(*)
2
SELECT count(*) FROM t1;
count(*)
5
DROP TABLE t1;
DROP TABLE t_part;
|
