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
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
|
--source include/no_valgrind_without_big.inc
let $partitioning= 1;
--source suite/innodb/include/innodb_import.inc
let MYSQLD_DATADIR =`SELECT @@datadir`;
--echo #
--echo # DISCARD/IMPORT selected partitions only
--echo #
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY HASH (c1) PARTITIONS 3;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
SELECT c1 FROM t1 ORDER BY c1;
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
perl;
require 'include/innodb-util.inc';
ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
EOF
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY HASH (c1) PARTITIONS 3;
INSERT INTO t1 VALUES (31), (32), (33);
ALTER TABLE t1 DISCARD PARTITION p0,p2 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p2");
ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p2");
EOF
ALTER TABLE t1 IMPORT PARTITION p0, p2 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p1");
ib_restore_tablespaces("test", "t1#p#p1");
EOF
ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
INSERT INTO t1 VALUES (31), (32), (33);
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
EOF
ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
INSERT INTO t1 PARTITION (p1) VALUES (61), (64);
FLUSH TABLES;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t1 ORDER BY c1;
--error ER_PARSE_ERROR
ALTER TABLE t1 DISCARD PARTITION TABLESPACE;
--error ER_PARSE_ERROR
ALTER TABLE t1 IMPORT PARTITION TABLESPACE;
--error ER_UNKNOWN_PARTITION
ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
--error ER_UNKNOWN_PARTITION
ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p1");
ib_restore_tablespaces("test", "t1#p#p1");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
--echo #
--echo # DISCARD/IMPORT selected subpartitions only
--echo #
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
INSERT INTO t1 VALUES (101),(102),(103),(104),(105),(106);
SELECT c1 FROM t1 ORDER BY c1;
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
perl;
require 'include/innodb-util.inc';
ib_backup_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (31), (32), (33);
INSERT INTO t1 VALUES (131), (132), (133);
ALTER TABLE t1 DISCARD PARTITION p0sp1,p1sp0 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p0#sp#p0sp1", "t1#p#p1#sp#p1sp0");
ib_restore_tablespaces("test", "t1#p#p0#sp#p0sp1", "t1#p#p1#sp#p1sp0");
EOF
ALTER TABLE t1 IMPORT PARTITION p0sp1, p1sp0 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1, p0sp2 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test",
"t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION p0sp2, p1 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
INSERT INTO t1 VALUES (31), (32), (33);
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION p1sp2 TABLESPACE;
SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
INSERT INTO t1 PARTITION (p1sp2) VALUES (161), (164);
FLUSH TABLES;
SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1sp2 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test", "t1#p#p1#sp#p1sp2");
EOF
--replace_regex /#p#/#P#/ /#sp#/#SP#/
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
--echo # Removing p0sp2's .ibd file
--remove_file $MYSQLD_DATADIR/test/t1#p#p0#sp#p0sp2.ibd
--error ER_INTERNAL_ERROR
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_restore_tablespaces("test", "t1#p#p0#sp#p0sp2");
EOF
--replace_regex /#p#/#P#/ /#sp#/#SP#/
--error ER_TABLESPACE_EXISTS
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
ALTER TABLE t1 IMPORT PARTITION p0sp2 TABLESPACE;
--replace_regex /#p#/#P#/ /#sp#/#SP#/
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
DROP TABLE t1;
--echo # discard partition one by one
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test","t1#p#p0#sp#p0sp0");
ib_restore_tablespaces("test","t1#p#p0#sp#p0sp0");
EOF
ALTER TABLE t1 IMPORT PARTITION p0sp0 TABLESPACE;
SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 REBUILD PARTITION p0;
SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
ALTER TABLE t1 OPTIMIZE PARTITION p1;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
ALTER TABLE t1 CHECK PARTITION p1;
ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 REPAIR PARTITION p0sp0,p1;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p0sp1 TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p0sp2 TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
ib_restore_tablespaces("test",
"t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
"t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
# Test EXPORT/IMPORT with instant ADD COLUMN
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
# Instant ADD COLUMN
ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
SELECT * FROM t1;
INSERT INTO t1(a, b) VALUES(5, 5), (6, 6), (28, 28);
# Truncate one partition, so it's totally new
ALTER TABLE t1 TRUNCATE PARTITION p2;
SELECT * FROM t1;
# Another instant ADD COLUMN
ALTER TABLE t1 ADD COLUMN d INT DEFAULT 200;
# Truncate another partition, so it's totally new
ALTER TABLE t1 TRUNCATE PARTITION p1;
SELECT * FROM t1;
INSERT INTO t1(a, b) VALUES(3, 3), (4, 4);
SELECT * FROM t1;
CHECK TABLE t1;
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1;
perl;
require 'include/innodb-util.inc';
ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
EOF
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT, d INT) PARTITION BY HASH (a) PARTITIONS 3;
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
require 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
CHECK TABLE t1;
SELECT * FROM t1;
INSERT INTO t1(a, b) VALUES(8, 8), (9, 9), (13, 13), (14, 14), (23, 23), (24, 24);
SELECT * FROM t1;
UPDATE t1 SET c = 90 WHERE a < 10;
SELECT * FROM t1;
UPDATE t1 SET c = 110 WHERE a > 20;
SELECT * FROM t1;
CHECK TABLE t1;
DROP TABLE t1;
--disable_query_log
call mtr.add_suppression("Cannot calculate statistics for table .* because the \.ibd file is missing");
call mtr.add_suppression("Cannot delete tablespace .* in DISCARD TABLESPACE. Tablespace not found");
call mtr.add_suppression("Cannot delete tablespace .* because it is not found in the tablespace memory cache.");
call mtr.add_suppression("Trying to import a tablespace, but could not open the tablespace file");
call mtr.add_suppression("Operating system error number 2 in a file operation.");
call mtr.add_suppression("The error means the system cannot find the path specified.");
call mtr.add_suppression("Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
call mtr.add_suppression("Missing \.ibd file for table .test...t1. .. Partition");
--enable_query_log
--remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.ibd
--remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.cfg
|
