Debian Codegraph @

mysql-test/suite/innodb/t/innodb-import-partition.test

raw
1 --source include/no_valgrind_without_big.inc
2
3
4 let $partitioning= 1;
5
6 --source suite/innodb/include/innodb_import.inc
7
8 let MYSQLD_DATADIR =`SELECT @@datadir`;
9
10 --echo #
11 --echo # DISCARD/IMPORT selected partitions only
12 --echo #
13 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
14 PARTITION BY HASH (c1) PARTITIONS 3;
15 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
16 SELECT c1 FROM t1 ORDER BY c1;
17 FLUSH TABLES t1 FOR EXPORT;
18 SELECT * FROM t1 ORDER BY c1;
19
20 perl;
21 require 'include/innodb-util.inc';
22 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
23 EOF
24
25 UNLOCK TABLES;
26
27 DROP TABLE t1;
28 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
29 PARTITION BY HASH (c1) PARTITIONS 3;
30 INSERT INTO t1 VALUES (31), (32), (33);
31 ALTER TABLE t1 DISCARD PARTITION p0,p2 TABLESPACE;
32
33 perl;
34 require 'include/innodb-util.inc';
35 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p2");
36 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p2");
37 EOF
38
39 ALTER TABLE t1 IMPORT PARTITION p0, p2 TABLESPACE;
40 SELECT * FROM t1 ORDER BY c1;
41
42 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
43
44 perl;
45 require 'include/innodb-util.inc';
46 ib_discard_tablespaces("test", "t1#p#p1");
47 ib_restore_tablespaces("test", "t1#p#p1");
48 EOF
49
50 ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
51 SELECT * FROM t1 ORDER BY c1;
52 INSERT INTO t1 VALUES (31), (32), (33);
53 SELECT * FROM t1 ORDER BY c1;
54 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
55
56 perl;
57 require 'include/innodb-util.inc';
58 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
59 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
60 EOF
61
62 ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
63 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
64 INSERT INTO t1 PARTITION (p1) VALUES (61), (64);
65 FLUSH TABLES;
66 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
67 --error ER_TABLESPACE_DISCARDED
68 SELECT * FROM t1 ORDER BY c1;
69 --error ER_PARSE_ERROR
70 ALTER TABLE t1 DISCARD PARTITION TABLESPACE;
71 --error ER_PARSE_ERROR
72 ALTER TABLE t1 IMPORT PARTITION TABLESPACE;
73 --error ER_UNKNOWN_PARTITION
74 ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
75 --error ER_UNKNOWN_PARTITION
76 ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
77 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
78
79 perl;
80 require 'include/innodb-util.inc';
81 ib_discard_tablespaces("test", "t1#p#p1");
82 ib_restore_tablespaces("test", "t1#p#p1");
83 EOF
84
85 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
86 SELECT * FROM t1 ORDER BY c1;
87
88 DROP TABLE t1;
89
90 --echo #
91 --echo # DISCARD/IMPORT selected subpartitions only
92 --echo #
93 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
94 PARTITION BY RANGE (c1)
95 SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
96 (PARTITION p0 VALUES LESS THAN (100),
97 PARTITION p1 VALUES LESS THAN MAXVALUE);
98 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
99 INSERT INTO t1 VALUES (101),(102),(103),(104),(105),(106);
100 SELECT c1 FROM t1 ORDER BY c1;
101 FLUSH TABLES t1 FOR EXPORT;
102 SELECT * FROM t1 ORDER BY c1;
103 perl;
104 require 'include/innodb-util.inc';
105 ib_backup_tablespaces("test",
106 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
107 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
108 EOF
109
110 UNLOCK TABLES;
111
112 DROP TABLE t1;
113 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
114 PARTITION BY RANGE (c1)
115 SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
116 (PARTITION p0 VALUES LESS THAN (100),
117 PARTITION p1 VALUES LESS THAN MAXVALUE);
118 INSERT INTO t1 VALUES (31), (32), (33);
119 INSERT INTO t1 VALUES (131), (132), (133);
120 ALTER TABLE t1 DISCARD PARTITION p0sp1,p1sp0 TABLESPACE;
121
122 perl;
123 require 'include/innodb-util.inc';
124 ib_discard_tablespaces("test", "t1#p#p0#sp#p0sp1", "t1#p#p1#sp#p1sp0");
125 ib_restore_tablespaces("test", "t1#p#p0#sp#p0sp1", "t1#p#p1#sp#p1sp0");
126 EOF
127
128 ALTER TABLE t1 IMPORT PARTITION p0sp1, p1sp0 TABLESPACE;
129 SELECT * FROM t1 ORDER BY c1;
130
131 ALTER TABLE t1 DISCARD PARTITION p1, p0sp2 TABLESPACE;
132
133 perl;
134 require 'include/innodb-util.inc';
135 ib_discard_tablespaces("test",
136 "t1#p#p0#sp#p0sp2",
137 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
138 ib_restore_tablespaces("test",
139 "t1#p#p0#sp#p0sp2",
140 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
141 EOF
142
143 ALTER TABLE t1 IMPORT PARTITION p0sp2, p1 TABLESPACE;
144 SELECT * FROM t1 ORDER BY c1;
145 INSERT INTO t1 VALUES (31), (32), (33);
146 SELECT * FROM t1 ORDER BY c1;
147 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
148
149 perl;
150 require 'include/innodb-util.inc';
151 ib_discard_tablespaces("test",
152 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
153 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
154 ib_restore_tablespaces("test",
155 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
156 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
157 EOF
158
159 ALTER TABLE t1 IMPORT PARTITION p1sp2 TABLESPACE;
160 SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
161 INSERT INTO t1 PARTITION (p1sp2) VALUES (161), (164);
162 FLUSH TABLES;
163 SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
164 --error ER_TABLESPACE_DISCARDED
165 SELECT * FROM t1 ORDER BY c1;
166 ALTER TABLE t1 DISCARD PARTITION p1sp2 TABLESPACE;
167
168 perl;
169 require 'include/innodb-util.inc';
170 ib_discard_tablespaces("test", "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp2");
171 ib_restore_tablespaces("test", "t1#p#p1#sp#p1sp2");
172 EOF
173
174 --replace_regex /#p#/#P#/ /#sp#/#SP#/
175 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
176
177 perl;
178 require 'include/innodb-util.inc';
179 ib_discard_tablespaces("test",
180 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
181 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
182 ib_restore_tablespaces("test",
183 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1",
184 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
185 EOF
186
187 --echo # Removing p0sp2's .ibd file
188 --remove_file $MYSQLD_DATADIR/test/t1#p#p0#sp#p0sp2.ibd
189 --error ER_INTERNAL_ERROR
190 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
191 perl;
192 require 'include/innodb-util.inc';
193 ib_restore_tablespaces("test", "t1#p#p0#sp#p0sp2");
194 EOF
195 --replace_regex /#p#/#P#/ /#sp#/#SP#/
196 --error ER_TABLESPACE_EXISTS
197 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
198 ALTER TABLE t1 IMPORT PARTITION p0sp2 TABLESPACE;
199 --replace_regex /#p#/#P#/ /#sp#/#SP#/
200 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
201 perl;
202 require 'include/innodb-util.inc';
203 ib_discard_tablespaces("test",
204 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
205 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
206 ib_restore_tablespaces("test",
207 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
208 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
209 EOF
210 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
211 SELECT * FROM t1 ORDER BY c1;
212
213 DROP TABLE t1;
214
215
216
217 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
218 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
219 ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
220 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
221 ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
222 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
223 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
224
225 DROP TABLE t1;
226
227
228 --echo # discard partition one by one
229 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
230 PARTITION BY RANGE (c1)
231 SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
232 (PARTITION p0 VALUES LESS THAN (100),
233 PARTITION p1 VALUES LESS THAN MAXVALUE);
234 ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
235 perl;
236 require 'include/innodb-util.inc';
237 ib_discard_tablespaces("test","t1#p#p0#sp#p0sp0");
238 ib_restore_tablespaces("test","t1#p#p0#sp#p0sp0");
239 EOF
240 ALTER TABLE t1 IMPORT PARTITION p0sp0 TABLESPACE;
241 SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
242 SELECT * FROM t1 ORDER BY c1;
243 ALTER TABLE t1 REBUILD PARTITION p0;
244 SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
245 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
246 perl;
247 require 'include/innodb-util.inc';
248 ib_discard_tablespaces("test",
249 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
250 ib_restore_tablespaces("test",
251 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
252 EOF
253 ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
254 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
255 ALTER TABLE t1 OPTIMIZE PARTITION p1;
256 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
257 ALTER TABLE t1 CHECK PARTITION p1;
258 ALTER TABLE t1 ANALYZE PARTITION p1;
259 ALTER TABLE t1 REPAIR PARTITION p0sp0,p1;
260 SELECT * FROM t1 ORDER BY c1;
261 ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
262 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
263 ALTER TABLE t1 DISCARD PARTITION p0sp1 TABLESPACE;
264 ALTER TABLE t1 DISCARD PARTITION p0sp2 TABLESPACE;
265 perl;
266 require 'include/innodb-util.inc';
267 ib_discard_tablespaces("test",
268 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
269 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
270 ib_restore_tablespaces("test",
271 "t1#p#p0#sp#p0sp0", "t1#p#p0#sp#p0sp1", "t1#p#p0#sp#p0sp2",
272 "t1#p#p1#sp#p1sp0", "t1#p#p1#sp#p1sp1", "t1#p#p1#sp#p1sp2");
273 EOF
274 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
275 SELECT * FROM t1 ORDER BY c1;
276
277 DROP TABLE t1;
278
279
280
281 # Test EXPORT/IMPORT with instant ADD COLUMN
282 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;
283
284 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
285
286 # Instant ADD COLUMN
287 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
288
289 SELECT * FROM t1;
290
291 INSERT INTO t1(a, b) VALUES(5, 5), (6, 6), (28, 28);
292
293 # Truncate one partition, so it's totally new
294 ALTER TABLE t1 TRUNCATE PARTITION p2;
295
296 SELECT * FROM t1;
297
298 # Another instant ADD COLUMN
299 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 200;
300
301 # Truncate another partition, so it's totally new
302 ALTER TABLE t1 TRUNCATE PARTITION p1;
303
304 SELECT * FROM t1;
305
306 INSERT INTO t1(a, b) VALUES(3, 3), (4, 4);
307
308 SELECT * FROM t1;
309
310 CHECK TABLE t1;
311
312 FLUSH TABLES t1 FOR EXPORT;
313
314 SELECT * FROM t1;
315
316 perl;
317 require 'include/innodb-util.inc';
318 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
319 EOF
320
321 UNLOCK TABLES;
322
323 DROP TABLE t1;
324
325 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT, d INT) PARTITION BY HASH (a) PARTITIONS 3;
326 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
327
328 perl;
329 require 'include/innodb-util.inc';
330 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
331 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
332 EOF
333
334 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
335 CHECK TABLE t1;
336 SELECT * FROM t1;
337
338 INSERT INTO t1(a, b) VALUES(8, 8), (9, 9), (13, 13), (14, 14), (23, 23), (24, 24);
339
340 SELECT * FROM t1;
341
342 UPDATE t1 SET c = 90 WHERE a < 10;
343
344 SELECT * FROM t1;
345
346 UPDATE t1 SET c = 110 WHERE a > 20;
347
348 SELECT * FROM t1;
349
350 CHECK TABLE t1;
351
352 DROP TABLE t1;
353
354
355 --disable_query_log
356 call mtr.add_suppression("Cannot calculate statistics for table .* because the \.ibd file is missing");
357 call mtr.add_suppression("Cannot delete tablespace .* in DISCARD TABLESPACE. Tablespace not found");
358 call mtr.add_suppression("Cannot delete tablespace .* because it is not found in the tablespace memory cache.");
359 call mtr.add_suppression("Trying to import a tablespace, but could not open the tablespace file");
360 call mtr.add_suppression("Operating system error number 2 in a file operation.");
361 call mtr.add_suppression("The error means the system cannot find the path specified.");
362 call mtr.add_suppression("Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
363 call mtr.add_suppression("Missing \.ibd file for table .test...t1. .. Partition");
364 --enable_query_log
365
366 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.ibd
367 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.cfg