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
|
# Creating database MySQL_TEST_DB
CREATE DATABASE MySQL_Test_DB;
USE MySQL_Test_DB;
# 1.0 KEY partitioning mgm
# Creating KEY partitioned table
CREATE TABLE TableA (a INT)
ENGINE = 'NDBCluster'
PARTITION BY KEY (a)
(PARTITION parta ,
PARTITION partB ,
PARTITION Partc ,
PARTITION PartD );
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
# Test of ADD/COALESCE PARTITIONS
# expecting duplicate partition name
ALTER TABLE TableA ADD PARTITION
(PARTITION partA,
PARTITION Parta,
PARTITION PartA);
ERROR HY000: Duplicate partition name parta
ALTER TABLE TableA ADD PARTITION
(PARTITION partE,
PARTITION Partf,
PARTITION PartG);
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table Create Table
TableA CREATE TABLE `tablea` (
`a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION parta ENGINE = ndbcluster,
PARTITION partB ENGINE = ndbcluster,
PARTITION Partc ENGINE = ndbcluster,
PARTITION PartD ENGINE = ndbcluster,
PARTITION partE ENGINE = ndbcluster,
PARTITION Partf ENGINE = ndbcluster,
PARTITION PartG ENGINE = ndbcluster) */
ALTER TABLE TableA COALESCE PARTITION 4;
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table Create Table
TableA CREATE TABLE `tablea` (
`a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION parta ENGINE = ndbcluster,
PARTITION partB ENGINE = ndbcluster,
PARTITION Partc ENGINE = ndbcluster) */
# Test of REORGANIZE PARTITIONS
# Should not work on HASH/KEY
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
(PARTITION PARTA ,
PARTITION partc );
ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
(PARTITION partB ,
PARTITION parta );
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
(PARTITION partB COMMENT="Previusly named parta",
PARTITION parta COMMENT="Previusly named partB");
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table Create Table
TableA CREATE TABLE `tablea` (
`a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ndbcluster,
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ndbcluster,
PARTITION Partc ENGINE = ndbcluster) */
# Test of RENAME TABLE
RENAME TABLE TableA to TableB;
SELECT * FROM TableB;
a
1
10
11
12
2
3
4
5
6
7
8
9
RENAME TABLE TableB to TableA;
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
# Checking name comparision Upper vs Lower case
# Error if lower_case_table_names != 0
# lower_case_table_names: 1
CREATE TABLE tablea (a INT)
ENGINE = 'NDBCluster'
PARTITION BY KEY (a)
(PARTITION parta ,
PARTITION partB ,
PARTITION Partc ,
PARTITION PartD );
ERROR 42S01: Table 'tablea' already exists
SHOW TABLES;
Tables_in_mysql_test_db
tablea
RENAME TABLE TableA to tablea;
ERROR 42S01: Table 'tablea' already exists
RENAME TABLE tablea to TableA;
ERROR 42S01: Table 'tablea' already exists
SELECT * FROM tablea;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE tablea;
Table Create Table
tablea CREATE TABLE `tablea` (
`a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ndbcluster,
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ndbcluster,
PARTITION Partc ENGINE = ndbcluster) */
# Test of REMOVE PARTITIONING
ALTER TABLE TableA REMOVE PARTITIONING;
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table Create Table
TableA CREATE TABLE `tablea` (
`a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
# Cleaning up after KEY PARTITIONING test
DROP TABLE TableA;
# Verify that TRUNCATE PARTITION gives error
CREATE TABLE t1
(a BIGINT AUTO_INCREMENT PRIMARY KEY,
b VARCHAR(255))
ENGINE = 'NDBCluster'
PARTITION BY KEY (a)
(PARTITION LT1000,
PARTITION LT2000,
PARTITION MAX);
INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
ALTER TABLE t1 TRUNCATE PARTITION MAX;
Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
|
