Debian Codegraph @

mysql-test/suite/innodb/r/partition_autoinc.result

raw
1 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
2 CREATE TABLE tr (
3 id INT NOT NULL AUTO_INCREMENT,
4 name VARCHAR(50),
5 purchased DATE,
6 KEY(id));
7 INSERT INTO tr VALUES
8 (1, 'desk organiser', '2003-10-15'),
9 (2, 'CD player', '1993-11-05'),
10 (3, 'TV set', '1996-03-10'),
11 (4, 'bookcase', '1982-01-10'),
12 (5, 'exercise bike', '2004-05-09'),
13 (6, 'sofa', '1987-06-05');
14 # Test for ADD PARTITION
15 CREATE TABLE t1 (
16 id INT NOT NULL AUTO_INCREMENT,
17 name VARCHAR(50),
18 purchased DATE,
19 KEY(id))
20 PARTITION BY RANGE( YEAR(purchased) ) (
21 PARTITION p0 VALUES LESS THAN (1990),
22 PARTITION p1 VALUES LESS THAN (1995),
23 PARTITION p2 VALUES LESS THAN (2000),
24 PARTITION p3 VALUES LESS THAN (2005)
25 );
26 INSERT INTO t1 SELECT * FROM tr;
27 SHOW CREATE TABLE t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `id` int NOT NULL AUTO_INCREMENT,
31 `name` varchar(50) DEFAULT NULL,
32 `purchased` date DEFAULT NULL,
33 KEY `id` (`id`)
34 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
35 /*!50100 PARTITION BY RANGE (year(`purchased`))
36 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
37 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
38 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
39 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
40 DELETE FROM t1 WHERE id = 6;
41 SELECT MAX(id) AS `Expect 5` FROM t1;
42 Expect 5
43 5
44 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010), PARTITION p5 VALUES LESS THAN (2015));
45 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
46 WHERE t.space = df.space
47 AND t.space_type = 'Single'
48 AND df.path NOT LIKE './mysql/%'
49 AND df.path NOT LIKE '%undo%'
50 AND df.path NOT LIKE '._mysql_%'
51 ORDER BY t.name;
52 LOWER(path)
53 ./mtr/asserted_test_suppressions.ibd
54 ./mtr/global_suppressions.ibd
55 ./mtr/test_ignored_global_suppressions.ibd
56 ./mtr/test_suppressions.ibd
57 ./sys/sys_config.ibd
58 ./test/t1#p#p0.ibd
59 ./test/t1#p#p1.ibd
60 ./test/t1#p#p2.ibd
61 ./test/t1#p#p3.ibd
62 ./test/t1#p#p4.ibd
63 ./test/t1#p#p5.ibd
64 ./test/tr.ibd
65 SHOW CREATE TABLE t1;
66 Table Create Table
67 t1 CREATE TABLE `t1` (
68 `id` int NOT NULL AUTO_INCREMENT,
69 `name` varchar(50) DEFAULT NULL,
70 `purchased` date DEFAULT NULL,
71 KEY `id` (`id`)
72 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
73 /*!50100 PARTITION BY RANGE (year(`purchased`))
74 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
75 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
76 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
77 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
78 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
79 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
80 INSERT INTO t1 VALUES (0, 'popcorn maker', '2010-11-22');
81 SELECT MAX(id) AS `Expect 7` FROM t1;
82 Expect 7
83 7
84 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION (PARTITION p6 VALUES LESS THAN (2018), PARTITION p7 VALUES LESS THAN (2020));
85 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
86 WHERE t.space = df.space
87 AND t.space_type = 'Single'
88 AND df.path NOT LIKE './mysql/%'
89 AND df.path NOT LIKE '%undo%'
90 AND df.path NOT LIKE '._mysql_%'
91 ORDER BY t.name;
92 LOWER(path)
93 ./mtr/asserted_test_suppressions.ibd
94 ./mtr/global_suppressions.ibd
95 ./mtr/test_ignored_global_suppressions.ibd
96 ./mtr/test_suppressions.ibd
97 ./sys/sys_config.ibd
98 ./test/t1#p#p0.ibd
99 ./test/t1#p#p1.ibd
100 ./test/t1#p#p2.ibd
101 ./test/t1#p#p3.ibd
102 ./test/t1#p#p4.ibd
103 ./test/t1#p#p5.ibd
104 ./test/t1#p#p6.ibd
105 ./test/t1#p#p7.ibd
106 ./test/tr.ibd
107 SHOW CREATE TABLE t1;
108 Table Create Table
109 t1 CREATE TABLE `t1` (
110 `id` int NOT NULL AUTO_INCREMENT,
111 `name` varchar(50) DEFAULT NULL,
112 `purchased` date DEFAULT NULL,
113 KEY `id` (`id`)
114 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
115 /*!50100 PARTITION BY RANGE (year(`purchased`))
116 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
117 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
118 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
119 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
120 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
121 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
122 PARTITION p6 VALUES LESS THAN (2018) ENGINE = InnoDB,
123 PARTITION p7 VALUES LESS THAN (2020) ENGINE = InnoDB) */
124 # restart
125 SHOW CREATE TABLE t1;
126 Table Create Table
127 t1 CREATE TABLE `t1` (
128 `id` int NOT NULL AUTO_INCREMENT,
129 `name` varchar(50) DEFAULT NULL,
130 `purchased` date DEFAULT NULL,
131 KEY `id` (`id`)
132 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
133 /*!50100 PARTITION BY RANGE (year(`purchased`))
134 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
135 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
136 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
137 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
138 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
139 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
140 PARTITION p6 VALUES LESS THAN (2018) ENGINE = InnoDB,
141 PARTITION p7 VALUES LESS THAN (2020) ENGINE = InnoDB) */
142 INSERT INTO t1 VALUES
143 (0, 'aquarium', '2019-08-04'),
144 (0, 'study desk', '2016-06-18');
145 SELECT MAX(id) AS `Expect 9` FROM t1;
146 Expect 9
147 9
148 DROP TABLE t1;
149 # Test for DROP PARTITION, along with some ADD PARTITION
150 CREATE TABLE t1 (
151 id INT NOT NULL AUTO_INCREMENT,
152 name VARCHAR(50),
153 purchased DATE,
154 KEY(id))
155 PARTITION BY RANGE( YEAR(purchased) ) (
156 PARTITION p0 VALUES LESS THAN (1990),
157 PARTITION p1 VALUES LESS THAN (1995),
158 PARTITION p2 VALUES LESS THAN (2000),
159 PARTITION p3 VALUES LESS THAN (2005)
160 );
161 INSERT INTO t1 SELECT * FROM tr;
162 SHOW CREATE TABLE t1;
163 Table Create Table
164 t1 CREATE TABLE `t1` (
165 `id` int NOT NULL AUTO_INCREMENT,
166 `name` varchar(50) DEFAULT NULL,
167 `purchased` date DEFAULT NULL,
168 KEY `id` (`id`)
169 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
170 /*!50100 PARTITION BY RANGE (year(`purchased`))
171 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
172 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
173 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
174 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
175 SELECT id FROM t1 PARTITION(p0);
176 id
177 4
178 6
179 SELECT id FROM t1 PARTITION(p1);
180 id
181 2
182 SELECT id FROM t1 PARTITION(p2);
183 id
184 3
185 SELECT id FROM t1 PARTITION(p3);
186 id
187 1
188 5
189 # This doesn't delete the row containing max(id) in the table
190 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p2;
191 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
192 WHERE t.space = df.space
193 AND t.space_type = 'Single'
194 AND df.path NOT LIKE './mysql/%'
195 AND df.path NOT LIKE '%undo%'
196 AND df.path NOT LIKE '._mysql_%'
197 ORDER BY t.name;
198 LOWER(path)
199 ./mtr/asserted_test_suppressions.ibd
200 ./mtr/global_suppressions.ibd
201 ./mtr/test_ignored_global_suppressions.ibd
202 ./mtr/test_suppressions.ibd
203 ./sys/sys_config.ibd
204 ./test/t1#p#p0.ibd
205 ./test/t1#p#p1.ibd
206 ./test/t1#p#p3.ibd
207 ./test/tr.ibd
208 SHOW CREATE TABLE t1;
209 Table Create Table
210 t1 CREATE TABLE `t1` (
211 `id` int NOT NULL AUTO_INCREMENT,
212 `name` varchar(50) DEFAULT NULL,
213 `purchased` date DEFAULT NULL,
214 KEY `id` (`id`)
215 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
216 /*!50100 PARTITION BY RANGE (year(`purchased`))
217 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
218 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
219 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
220 INSERT INTO t1 VALUES (0, 'aquarium', '2001-07-02');
221 SELECT MAX(id) AS `Expect 7` FROM t1;
222 Expect 7
223 7
224 # This will delete the row containing max(id) in the table
225 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p3;
226 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
227 WHERE t.space = df.space
228 AND t.space_type = 'Single'
229 AND df.path NOT LIKE './mysql/%'
230 AND df.path NOT LIKE '%undo%'
231 AND df.path NOT LIKE '._mysql_%'
232 ORDER BY t.name;
233 LOWER(path)
234 ./mtr/asserted_test_suppressions.ibd
235 ./mtr/global_suppressions.ibd
236 ./mtr/test_ignored_global_suppressions.ibd
237 ./mtr/test_suppressions.ibd
238 ./sys/sys_config.ibd
239 ./test/t1#p#p0.ibd
240 ./test/t1#p#p1.ibd
241 ./test/tr.ibd
242 SELECT MAX(id) AS `Expect 6` FROM t1;
243 Expect 6
244 6
245 SHOW CREATE TABLE t1;
246 Table Create Table
247 t1 CREATE TABLE `t1` (
248 `id` int NOT NULL AUTO_INCREMENT,
249 `name` varchar(50) DEFAULT NULL,
250 `purchased` date DEFAULT NULL,
251 KEY `id` (`id`)
252 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
253 /*!50100 PARTITION BY RANGE (year(`purchased`))
254 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
255 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB) */
256 INSERT INTO t1 VALUES(0, 'study desk', '1988-09-16');
257 SELECT MAX(id) AS `Expect 8` FROM t1;
258 Expect 8
259 8
260 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION (PARTITION p3 VALUES LESS THAN (2008));
261 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
262 WHERE t.space = df.space
263 AND t.space_type = 'Single'
264 AND df.path NOT LIKE './mysql/%'
265 AND df.path NOT LIKE '%undo%'
266 AND df.path NOT LIKE '._mysql_%'
267 ORDER BY t.name;
268 LOWER(path)
269 ./mtr/asserted_test_suppressions.ibd
270 ./mtr/global_suppressions.ibd
271 ./mtr/test_ignored_global_suppressions.ibd
272 ./mtr/test_suppressions.ibd
273 ./sys/sys_config.ibd
274 ./test/t1#p#p0.ibd
275 ./test/t1#p#p1.ibd
276 ./test/t1#p#p3.ibd
277 ./test/tr.ibd
278 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p0;
279 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
280 WHERE t.space = df.space
281 AND t.space_type = 'Single'
282 AND df.path NOT LIKE './mysql/%'
283 AND df.path NOT LIKE '%undo%'
284 AND df.path NOT LIKE '._mysql_%'
285 ORDER BY t.name;
286 LOWER(path)
287 ./mtr/asserted_test_suppressions.ibd
288 ./mtr/global_suppressions.ibd
289 ./mtr/test_ignored_global_suppressions.ibd
290 ./mtr/test_suppressions.ibd
291 ./sys/sys_config.ibd
292 ./test/t1#p#p1.ibd
293 ./test/t1#p#p3.ibd
294 ./test/tr.ibd
295 SHOW CREATE TABLE t1;
296 Table Create Table
297 t1 CREATE TABLE `t1` (
298 `id` int NOT NULL AUTO_INCREMENT,
299 `name` varchar(50) DEFAULT NULL,
300 `purchased` date DEFAULT NULL,
301 KEY `id` (`id`)
302 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
303 /*!50100 PARTITION BY RANGE (year(`purchased`))
304 (PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
305 PARTITION p3 VALUES LESS THAN (2008) ENGINE = InnoDB) */
306 # restart
307 SHOW CREATE TABLE t1;
308 Table Create Table
309 t1 CREATE TABLE `t1` (
310 `id` int NOT NULL AUTO_INCREMENT,
311 `name` varchar(50) DEFAULT NULL,
312 `purchased` date DEFAULT NULL,
313 KEY `id` (`id`)
314 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
315 /*!50100 PARTITION BY RANGE (year(`purchased`))
316 (PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
317 PARTITION p3 VALUES LESS THAN (2008) ENGINE = InnoDB) */
318 INSERT INTO t1 VALUES(0, 'lava lamp', '2006-01-02');
319 SELECT MAX(id) AS `Expect 9` FROM t1;
320 Expect 9
321 9
322 DELETE FROM t1 WHERE id >= 0;
323 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p3;
324 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
325 WHERE t.space = df.space
326 AND t.space_type = 'Single'
327 AND df.path NOT LIKE './mysql/%'
328 AND df.path NOT LIKE '%undo%'
329 AND df.path NOT LIKE '._mysql_%'
330 ORDER BY t.name;
331 LOWER(path)
332 ./mtr/asserted_test_suppressions.ibd
333 ./mtr/global_suppressions.ibd
334 ./mtr/test_ignored_global_suppressions.ibd
335 ./mtr/test_suppressions.ibd
336 ./sys/sys_config.ibd
337 ./test/t1#p#p1.ibd
338 ./test/tr.ibd
339 SHOW CREATE TABLE t1;
340 Table Create Table
341 t1 CREATE TABLE `t1` (
342 `id` int NOT NULL AUTO_INCREMENT,
343 `name` varchar(50) DEFAULT NULL,
344 `purchased` date DEFAULT NULL,
345 KEY `id` (`id`)
346 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
347 /*!50100 PARTITION BY RANGE (year(`purchased`))
348 (PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB) */
349 INSERT INTO t1 VALUES(0, 'lava lamp', '1992-01-15');
350 SELECT MAX(id) AS `Expect 10` FROM t1;
351 Expect 10
352 10
353 DROP TABLE t1;
354 # Test for ADD PARTITION for HASH PARTITIONING
355 CREATE TABLE t1 (
356 id INT NOT NULL AUTO_INCREMENT,
357 name VARCHAR(50),
358 purchased DATE,
359 KEY(id))
360 PARTITION BY HASH( YEAR(purchased) ) PARTITIONS 4;
361 INSERT INTO t1 SELECT * FROM tr;
362 SHOW CREATE TABLE t1;
363 Table Create Table
364 t1 CREATE TABLE `t1` (
365 `id` int NOT NULL AUTO_INCREMENT,
366 `name` varchar(50) DEFAULT NULL,
367 `purchased` date DEFAULT NULL,
368 KEY `id` (`id`)
369 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
370 /*!50100 PARTITION BY HASH (year(`purchased`))
371 PARTITIONS 4 */
372 DELETE FROM t1 WHERE id = 6;
373 SELECT MAX(id) AS `Expect 5` FROM t1;
374 Expect 5
375 5
376 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION PARTITIONS 2;
377 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
378 WHERE t.space = df.space
379 AND t.space_type = 'Single'
380 AND df.path NOT LIKE './mysql/%'
381 AND df.path NOT LIKE '%undo%'
382 AND df.path NOT LIKE '._mysql_%'
383 ORDER BY t.name;
384 LOWER(path)
385 ./mtr/asserted_test_suppressions.ibd
386 ./mtr/global_suppressions.ibd
387 ./mtr/test_ignored_global_suppressions.ibd
388 ./mtr/test_suppressions.ibd
389 ./sys/sys_config.ibd
390 ./test/t1#p#p0.ibd
391 ./test/t1#p#p1.ibd
392 ./test/t1#p#p2.ibd
393 ./test/t1#p#p3.ibd
394 ./test/t1#p#p4.ibd
395 ./test/t1#p#p5.ibd
396 ./test/tr.ibd
397 SHOW CREATE TABLE t1;
398 Table Create Table
399 t1 CREATE TABLE `t1` (
400 `id` int NOT NULL AUTO_INCREMENT,
401 `name` varchar(50) DEFAULT NULL,
402 `purchased` date DEFAULT NULL,
403 KEY `id` (`id`)
404 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
405 /*!50100 PARTITION BY HASH (year(`purchased`))
406 PARTITIONS 6 */
407 INSERT INTO t1 VALUES (0, 'popcorn maker', '2010-11-22');
408 SELECT MAX(id) AS `Expect 7` FROM t1;
409 Expect 7
410 7
411 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION PARTITIONS 2;
412 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
413 WHERE t.space = df.space
414 AND t.space_type = 'Single'
415 AND df.path NOT LIKE './mysql/%'
416 AND df.path NOT LIKE '%undo%'
417 AND df.path NOT LIKE '._mysql_%'
418 ORDER BY t.name;
419 LOWER(path)
420 ./mtr/asserted_test_suppressions.ibd
421 ./mtr/global_suppressions.ibd
422 ./mtr/test_ignored_global_suppressions.ibd
423 ./mtr/test_suppressions.ibd
424 ./sys/sys_config.ibd
425 ./test/t1#p#p0.ibd
426 ./test/t1#p#p1.ibd
427 ./test/t1#p#p2.ibd
428 ./test/t1#p#p3.ibd
429 ./test/t1#p#p4.ibd
430 ./test/t1#p#p5.ibd
431 ./test/t1#p#p6.ibd
432 ./test/t1#p#p7.ibd
433 ./test/tr.ibd
434 SHOW CREATE TABLE t1;
435 Table Create Table
436 t1 CREATE TABLE `t1` (
437 `id` int NOT NULL AUTO_INCREMENT,
438 `name` varchar(50) DEFAULT NULL,
439 `purchased` date DEFAULT NULL,
440 KEY `id` (`id`)
441 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
442 /*!50100 PARTITION BY HASH (year(`purchased`))
443 PARTITIONS 8 */
444 # restart
445 SHOW CREATE TABLE t1;
446 Table Create Table
447 t1 CREATE TABLE `t1` (
448 `id` int NOT NULL AUTO_INCREMENT,
449 `name` varchar(50) DEFAULT NULL,
450 `purchased` date DEFAULT NULL,
451 KEY `id` (`id`)
452 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
453 /*!50100 PARTITION BY HASH (year(`purchased`))
454 PARTITIONS 8 */
455 INSERT INTO t1 VALUES
456 (0, 'aquarium', '2019-08-04'),
457 (0, 'study desk', '2016-06-18');
458 SELECT MAX(id) AS `Expect 9` FROM t1;
459 Expect 9
460 9
461 DROP TABLE t1;
462 # Test for ADD PARTITION for HASH PARTITIONINNG,
463 # along with COALESCE PARTITION
464 CREATE TABLE t1 (
465 id INT NOT NULL AUTO_INCREMENT,
466 name VARCHAR(50),
467 purchased DATE,
468 KEY(id))
469 PARTITION BY HASH( YEAR(purchased) ) PARTITIONS 8;
470 INSERT INTO t1 SELECT * FROM tr;
471 SHOW CREATE TABLE t1;
472 Table Create Table
473 t1 CREATE TABLE `t1` (
474 `id` int NOT NULL AUTO_INCREMENT,
475 `name` varchar(50) DEFAULT NULL,
476 `purchased` date DEFAULT NULL,
477 KEY `id` (`id`)
478 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
479 /*!50100 PARTITION BY HASH (year(`purchased`))
480 PARTITIONS 8 */
481 DELETE FROM t1 WHERE id = 6;
482 SELECT MAX(id) AS `Expect 5` FROM t1;
483 Expect 5
484 5
485 ALTER TABLE t1 ALGORITHM = INPLACE, COALESCE PARTITION 2;
486 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
487 WHERE t.space = df.space
488 AND t.space_type = 'Single'
489 AND df.path NOT LIKE './mysql/%'
490 AND df.path NOT LIKE '%undo%'
491 AND df.path NOT LIKE '._mysql_%'
492 ORDER BY t.name;
493 LOWER(path)
494 ./mtr/asserted_test_suppressions.ibd
495 ./mtr/global_suppressions.ibd
496 ./mtr/test_ignored_global_suppressions.ibd
497 ./mtr/test_suppressions.ibd
498 ./sys/sys_config.ibd
499 ./test/t1#p#p0.ibd
500 ./test/t1#p#p1.ibd
501 ./test/t1#p#p2.ibd
502 ./test/t1#p#p3.ibd
503 ./test/t1#p#p4.ibd
504 ./test/t1#p#p5.ibd
505 ./test/tr.ibd
506 SHOW CREATE TABLE t1;
507 Table Create Table
508 t1 CREATE TABLE `t1` (
509 `id` int NOT NULL AUTO_INCREMENT,
510 `name` varchar(50) DEFAULT NULL,
511 `purchased` date DEFAULT NULL,
512 KEY `id` (`id`)
513 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
514 /*!50100 PARTITION BY HASH (year(`purchased`))
515 PARTITIONS 6 */
516 INSERT INTO t1 VALUES (0, 'popcorn maker', '2010-11-22');
517 SELECT MAX(id) AS `Expect 7` FROM t1;
518 Expect 7
519 7
520 SHOW CREATE TABLE t1;
521 Table Create Table
522 t1 CREATE TABLE `t1` (
523 `id` int NOT NULL AUTO_INCREMENT,
524 `name` varchar(50) DEFAULT NULL,
525 `purchased` date DEFAULT NULL,
526 KEY `id` (`id`)
527 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
528 /*!50100 PARTITION BY HASH (year(`purchased`))
529 PARTITIONS 6 */
530 ALTER TABLE t1 ALGORITHM = INPLACE, COALESCE PARTITION 4;
531 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
532 WHERE t.space = df.space
533 AND t.space_type = 'Single'
534 AND df.path NOT LIKE './mysql/%'
535 AND df.path NOT LIKE '%undo%'
536 AND df.path NOT LIKE '._mysql_%'
537 ORDER BY t.name;
538 LOWER(path)
539 ./mtr/asserted_test_suppressions.ibd
540 ./mtr/global_suppressions.ibd
541 ./mtr/test_ignored_global_suppressions.ibd
542 ./mtr/test_suppressions.ibd
543 ./sys/sys_config.ibd
544 ./test/t1#p#p0.ibd
545 ./test/t1#p#p1.ibd
546 ./test/tr.ibd
547 # restart
548 SHOW CREATE TABLE t1;
549 Table Create Table
550 t1 CREATE TABLE `t1` (
551 `id` int NOT NULL AUTO_INCREMENT,
552 `name` varchar(50) DEFAULT NULL,
553 `purchased` date DEFAULT NULL,
554 KEY `id` (`id`)
555 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
556 /*!50100 PARTITION BY HASH (year(`purchased`))
557 PARTITIONS 2 */
558 INSERT INTO t1 VALUES
559 (0, 'aquarium', '2019-08-04'),
560 (0, 'study desk', '2016-06-18');
561 SELECT MAX(id) AS `Expect 9` FROM t1;
562 Expect 9
563 9
564 SHOW CREATE TABLE t1;
565 Table Create Table
566 t1 CREATE TABLE `t1` (
567 `id` int NOT NULL AUTO_INCREMENT,
568 `name` varchar(50) DEFAULT NULL,
569 `purchased` date DEFAULT NULL,
570 KEY `id` (`id`)
571 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
572 /*!50100 PARTITION BY HASH (year(`purchased`))
573 PARTITIONS 2 */
574 DROP TABLE t1;
575 # Test for REORGANIZE PARTITION
576 CREATE TABLE t1 (
577 id INT NOT NULL AUTO_INCREMENT,
578 name VARCHAR(50),
579 purchased DATE,
580 KEY(id))
581 PARTITION BY RANGE( YEAR(purchased) ) (
582 PARTITION p0 VALUES LESS THAN (1990),
583 PARTITION p1 VALUES LESS THAN (1995),
584 PARTITION p2 VALUES LESS THAN (2000),
585 PARTITION p3 VALUES LESS THAN (2005)
586 );
587 INSERT INTO t1 SELECT * FROM tr;
588 SHOW CREATE TABLE t1;
589 Table Create Table
590 t1 CREATE TABLE `t1` (
591 `id` int NOT NULL AUTO_INCREMENT,
592 `name` varchar(50) DEFAULT NULL,
593 `purchased` date DEFAULT NULL,
594 KEY `id` (`id`)
595 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
596 /*!50100 PARTITION BY RANGE (year(`purchased`))
597 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
598 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
599 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
600 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
601 DELETE FROM t1 WHERE id = 6;
602 SELECT MAX(id) AS `Expect 5` FROM t1;
603 Expect 5
604 5
605 ALTER TABLE t1 ALGORITHM = INPLACE, REORGANIZE PARTITION p0, p1 INTO
606 (PARTITION p01 VALUES LESS THAN(1985), PARTITION p02 VALUES LESS THAN(1990),
607 PARTITION p11 VALUES LESS THAN(1993), PARTITION p12 VALUES LESS THAN(1995));
608 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
609 WHERE t.space = df.space
610 AND t.space_type = 'Single'
611 AND df.path NOT LIKE './mysql/%'
612 AND df.path NOT LIKE '%undo%'
613 AND df.path NOT LIKE '._mysql_%'
614 ORDER BY t.name;
615 LOWER(path)
616 ./mtr/asserted_test_suppressions.ibd
617 ./mtr/global_suppressions.ibd
618 ./mtr/test_ignored_global_suppressions.ibd
619 ./mtr/test_suppressions.ibd
620 ./sys/sys_config.ibd
621 ./test/t1#p#p01.ibd
622 ./test/t1#p#p02.ibd
623 ./test/t1#p#p11.ibd
624 ./test/t1#p#p12.ibd
625 ./test/t1#p#p2.ibd
626 ./test/t1#p#p3.ibd
627 ./test/tr.ibd
628 SHOW CREATE TABLE t1;
629 Table Create Table
630 t1 CREATE TABLE `t1` (
631 `id` int NOT NULL AUTO_INCREMENT,
632 `name` varchar(50) DEFAULT NULL,
633 `purchased` date DEFAULT NULL,
634 KEY `id` (`id`)
635 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
636 /*!50100 PARTITION BY RANGE (year(`purchased`))
637 (PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
638 PARTITION p02 VALUES LESS THAN (1990) ENGINE = InnoDB,
639 PARTITION p11 VALUES LESS THAN (1993) ENGINE = InnoDB,
640 PARTITION p12 VALUES LESS THAN (1995) ENGINE = InnoDB,
641 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
642 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
643 INSERT INTO t1 VALUES (0, 'popcorn maker', '2001-11-22');
644 SELECT MAX(id) AS `Expect 7` FROM t1;
645 Expect 7
646 7
647 SHOW CREATE TABLE t1;
648 Table Create Table
649 t1 CREATE TABLE `t1` (
650 `id` int NOT NULL AUTO_INCREMENT,
651 `name` varchar(50) DEFAULT NULL,
652 `purchased` date DEFAULT NULL,
653 KEY `id` (`id`)
654 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
655 /*!50100 PARTITION BY RANGE (year(`purchased`))
656 (PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
657 PARTITION p02 VALUES LESS THAN (1990) ENGINE = InnoDB,
658 PARTITION p11 VALUES LESS THAN (1993) ENGINE = InnoDB,
659 PARTITION p12 VALUES LESS THAN (1995) ENGINE = InnoDB,
660 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
661 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
662 ALTER TABLE t1 ALGORITHM = INPLACE, REORGANIZE PARTITION p2, p3 INTO
663 (PARTITION p21 VALUES LESS THAN(1997), PARTITION p22 VALUES LESS THAN(2000),
664 PARTITION p31 VALUES LESS THAN(2002), PARTITION p32 VALUES LESS THAN(2005));
665 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
666 WHERE t.space = df.space
667 AND t.space_type = 'Single'
668 AND df.path NOT LIKE './mysql/%'
669 AND df.path NOT LIKE '%undo%'
670 AND df.path NOT LIKE '._mysql_%'
671 ORDER BY t.name;
672 LOWER(path)
673 ./mtr/asserted_test_suppressions.ibd
674 ./mtr/global_suppressions.ibd
675 ./mtr/test_ignored_global_suppressions.ibd
676 ./mtr/test_suppressions.ibd
677 ./sys/sys_config.ibd
678 ./test/t1#p#p01.ibd
679 ./test/t1#p#p02.ibd
680 ./test/t1#p#p11.ibd
681 ./test/t1#p#p12.ibd
682 ./test/t1#p#p21.ibd
683 ./test/t1#p#p22.ibd
684 ./test/t1#p#p31.ibd
685 ./test/t1#p#p32.ibd
686 ./test/tr.ibd
687 SHOW CREATE TABLE t1;
688 Table Create Table
689 t1 CREATE TABLE `t1` (
690 `id` int NOT NULL AUTO_INCREMENT,
691 `name` varchar(50) DEFAULT NULL,
692 `purchased` date DEFAULT NULL,
693 KEY `id` (`id`)
694 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
695 /*!50100 PARTITION BY RANGE (year(`purchased`))
696 (PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
697 PARTITION p02 VALUES LESS THAN (1990) ENGINE = InnoDB,
698 PARTITION p11 VALUES LESS THAN (1993) ENGINE = InnoDB,
699 PARTITION p12 VALUES LESS THAN (1995) ENGINE = InnoDB,
700 PARTITION p21 VALUES LESS THAN (1997) ENGINE = InnoDB,
701 PARTITION p22 VALUES LESS THAN (2000) ENGINE = InnoDB,
702 PARTITION p31 VALUES LESS THAN (2002) ENGINE = InnoDB,
703 PARTITION p32 VALUES LESS THAN (2005) ENGINE = InnoDB) */
704 INSERT INTO t1 VALUES
705 (0, 'aquarium', '2002-08-04'),
706 (0, 'study desk', '2004-06-18');
707 SELECT MAX(id) AS `Expect 9` FROM t1;
708 Expect 9
709 9
710 SHOW CREATE TABLE t1;
711 Table Create Table
712 t1 CREATE TABLE `t1` (
713 `id` int NOT NULL AUTO_INCREMENT,
714 `name` varchar(50) DEFAULT NULL,
715 `purchased` date DEFAULT NULL,
716 KEY `id` (`id`)
717 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
718 /*!50100 PARTITION BY RANGE (year(`purchased`))
719 (PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
720 PARTITION p02 VALUES LESS THAN (1990) ENGINE = InnoDB,
721 PARTITION p11 VALUES LESS THAN (1993) ENGINE = InnoDB,
722 PARTITION p12 VALUES LESS THAN (1995) ENGINE = InnoDB,
723 PARTITION p21 VALUES LESS THAN (1997) ENGINE = InnoDB,
724 PARTITION p22 VALUES LESS THAN (2000) ENGINE = InnoDB,
725 PARTITION p31 VALUES LESS THAN (2002) ENGINE = InnoDB,
726 PARTITION p32 VALUES LESS THAN (2005) ENGINE = InnoDB) */
727 # Test for REBUILD PARTITION
728 ALTER TABLE t1 ALGORITHM = INPLACE, REBUILD PARTITION p01, p02, p31, p32;
729 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
730 WHERE t.space = df.space
731 AND t.space_type = 'Single'
732 AND df.path NOT LIKE './mysql/%'
733 AND df.path NOT LIKE '%undo%'
734 AND df.path NOT LIKE '._mysql_%'
735 ORDER BY t.name;
736 LOWER(path)
737 ./mtr/asserted_test_suppressions.ibd
738 ./mtr/global_suppressions.ibd
739 ./mtr/test_ignored_global_suppressions.ibd
740 ./mtr/test_suppressions.ibd
741 ./sys/sys_config.ibd
742 ./test/t1#p#p01.ibd
743 ./test/t1#p#p02.ibd
744 ./test/t1#p#p11.ibd
745 ./test/t1#p#p12.ibd
746 ./test/t1#p#p21.ibd
747 ./test/t1#p#p22.ibd
748 ./test/t1#p#p31.ibd
749 ./test/t1#p#p32.ibd
750 ./test/tr.ibd
751 SHOW CREATE TABLE t1;
752 Table Create Table
753 t1 CREATE TABLE `t1` (
754 `id` int NOT NULL AUTO_INCREMENT,
755 `name` varchar(50) DEFAULT NULL,
756 `purchased` date DEFAULT NULL,
757 KEY `id` (`id`)
758 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
759 /*!50100 PARTITION BY RANGE (year(`purchased`))
760 (PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
761 PARTITION p02 VALUES LESS THAN (1990) ENGINE = InnoDB,
762 PARTITION p11 VALUES LESS THAN (1993) ENGINE = InnoDB,
763 PARTITION p12 VALUES LESS THAN (1995) ENGINE = InnoDB,
764 PARTITION p21 VALUES LESS THAN (1997) ENGINE = InnoDB,
765 PARTITION p22 VALUES LESS THAN (2000) ENGINE = InnoDB,
766 PARTITION p31 VALUES LESS THAN (2002) ENGINE = InnoDB,
767 PARTITION p32 VALUES LESS THAN (2005) ENGINE = InnoDB) */
768 INSERT INTO t1 VALUES (0, 'lava lamp', '2000-12-10');
769 SHOW CREATE TABLE t1;
770 Table Create Table
771 t1 CREATE TABLE `t1` (
772 `id` int NOT NULL AUTO_INCREMENT,
773 `name` varchar(50) DEFAULT NULL,
774 `purchased` date DEFAULT NULL,
775 KEY `id` (`id`)
776 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
777 /*!50100 PARTITION BY RANGE (year(`purchased`))
778 (PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
779 PARTITION p02 VALUES LESS THAN (1990) ENGINE = InnoDB,
780 PARTITION p11 VALUES LESS THAN (1993) ENGINE = InnoDB,
781 PARTITION p12 VALUES LESS THAN (1995) ENGINE = InnoDB,
782 PARTITION p21 VALUES LESS THAN (1997) ENGINE = InnoDB,
783 PARTITION p22 VALUES LESS THAN (2000) ENGINE = InnoDB,
784 PARTITION p31 VALUES LESS THAN (2002) ENGINE = InnoDB,
785 PARTITION p32 VALUES LESS THAN (2005) ENGINE = InnoDB) */
786 SELECT MAX(id) AS `Expect 10` FROM t1;
787 Expect 10
788 10
789 DROP TABLE t1;
790 # Test for EXCHANGE PARTITION
791 CREATE TABLE t1 (
792 id INT NOT NULL AUTO_INCREMENT,
793 name VARCHAR(50),
794 purchased DATE,
795 KEY(id))
796 PARTITION BY RANGE( YEAR(purchased) ) (
797 PARTITION p0 VALUES LESS THAN (1990),
798 PARTITION p1 VALUES LESS THAN (1995),
799 PARTITION p2 VALUES LESS THAN (2000),
800 PARTITION p3 VALUES LESS THAN (2005)
801 );
802 INSERT INTO t1 SELECT * FROM tr;
803 SHOW CREATE TABLE t1;
804 Table Create Table
805 t1 CREATE TABLE `t1` (
806 `id` int NOT NULL AUTO_INCREMENT,
807 `name` varchar(50) DEFAULT NULL,
808 `purchased` date DEFAULT NULL,
809 KEY `id` (`id`)
810 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
811 /*!50100 PARTITION BY RANGE (year(`purchased`))
812 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
813 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
814 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
815 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
816 DELETE FROM t1 WHERE id = 6;
817 SELECT MAX(id) AS `Expect 5` FROM t1;
818 Expect 5
819 5
820 CREATE TABLE tp (
821 id INT NOT NULL AUTO_INCREMENT,
822 name VARCHAR(50),
823 purchased DATE,
824 KEY(id)) ENGINE = InnoDB;
825 INSERT INTO tp VALUES (100, 'DVD player', '2002-03-21');
826 ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE tp;
827 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
828 WHERE t.space = df.space
829 AND t.space_type = 'Single'
830 AND df.path NOT LIKE './mysql/%'
831 AND df.path NOT LIKE '%undo%'
832 AND df.path NOT LIKE '._mysql_%'
833 ORDER BY t.name;
834 LOWER(path)
835 ./mtr/asserted_test_suppressions.ibd
836 ./mtr/global_suppressions.ibd
837 ./mtr/test_ignored_global_suppressions.ibd
838 ./mtr/test_suppressions.ibd
839 ./sys/sys_config.ibd
840 ./test/t1#p#p0.ibd
841 ./test/t1#p#p1.ibd
842 ./test/t1#p#p2.ibd
843 ./test/t1#p#p3.ibd
844 ./test/tp.ibd
845 ./test/tr.ibd
846 SHOW CREATE TABLE tp;
847 Table Create Table
848 tp CREATE TABLE `tp` (
849 `id` int NOT NULL AUTO_INCREMENT,
850 `name` varchar(50) DEFAULT NULL,
851 `purchased` date DEFAULT NULL,
852 KEY `id` (`id`)
853 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
854 SHOW CREATE TABLE t1;
855 Table Create Table
856 t1 CREATE TABLE `t1` (
857 `id` int NOT NULL AUTO_INCREMENT,
858 `name` varchar(50) DEFAULT NULL,
859 `purchased` date DEFAULT NULL,
860 KEY `id` (`id`)
861 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
862 /*!50100 PARTITION BY RANGE (year(`purchased`))
863 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
864 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
865 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
866 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
867 INSERT INTO tp VALUES(0, 'DVD player', '2002-03-22');
868 INSERT INTO t1 VALUES(0, 'DVD player', '2002-03-23');
869 SELECT MAX(id) AS `Expect 101` FROM t1;
870 Expect 101
871 101
872 SELECT MAX(id) AS `Expect 101` FROM tp;
873 Expect 101
874 101
875 ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE tp;
876 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
877 WHERE t.space = df.space
878 AND t.space_type = 'Single'
879 AND df.path NOT LIKE './mysql/%'
880 AND df.path NOT LIKE '%undo%'
881 AND df.path NOT LIKE '._mysql_%'
882 ORDER BY t.name;
883 LOWER(path)
884 ./mtr/asserted_test_suppressions.ibd
885 ./mtr/global_suppressions.ibd
886 ./mtr/test_ignored_global_suppressions.ibd
887 ./mtr/test_suppressions.ibd
888 ./sys/sys_config.ibd
889 ./test/t1#p#p0.ibd
890 ./test/t1#p#p1.ibd
891 ./test/t1#p#p2.ibd
892 ./test/t1#p#p3.ibd
893 ./test/tp.ibd
894 ./test/tr.ibd
895 SHOW CREATE TABLE tp;
896 Table Create Table
897 tp CREATE TABLE `tp` (
898 `id` int NOT NULL AUTO_INCREMENT,
899 `name` varchar(50) DEFAULT NULL,
900 `purchased` date DEFAULT NULL,
901 KEY `id` (`id`)
902 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
903 SHOW CREATE TABLE t1;
904 Table Create Table
905 t1 CREATE TABLE `t1` (
906 `id` int NOT NULL AUTO_INCREMENT,
907 `name` varchar(50) DEFAULT NULL,
908 `purchased` date DEFAULT NULL,
909 KEY `id` (`id`)
910 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
911 /*!50100 PARTITION BY RANGE (year(`purchased`))
912 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
913 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
914 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
915 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
916 INSERT INTO tp VALUES(0, 'DVD player', '2002-03-24');
917 INSERT INTO t1 VALUES(0, 'DVD player', '2002-03-25');
918 SELECT MAX(id) AS `Expect 102` FROM t1;
919 Expect 102
920 102
921 SELECT MAX(id) AS `Expect 102` FROM tp;
922 Expect 102
923 102
924 DROP TABLE tp, t1;
925 # Test for subpartitioning
926 CREATE TABLE t1 (
927 id INT NOT NULL AUTO_INCREMENT,
928 name VARCHAR(50),
929 purchased DATE,
930 KEY(id))
931 PARTITION BY RANGE( YEAR(purchased) )
932 SUBPARTITION BY HASH ( YEAR(purchased) )
933 (PARTITION p0 VALUES LESS THAN (1990)
934 (SUBPARTITION sp00, SUBPARTITION sp01),
935 PARTITION p1 VALUES LESS THAN (2000)
936 (SUBPARTITION sp10, SUBPARTITION sp11),
937 PARTITION p2 VALUES LESS THAN (2010)
938 (SUBPARTITION sp20, SUBPARTITION sp21));
939 INSERT INTO t1 SELECT * FROM tr;
940 SHOW CREATE TABLE t1;
941 Table Create Table
942 t1 CREATE TABLE `t1` (
943 `id` int NOT NULL AUTO_INCREMENT,
944 `name` varchar(50) DEFAULT NULL,
945 `purchased` date DEFAULT NULL,
946 KEY `id` (`id`)
947 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
948 /*!50100 PARTITION BY RANGE (year(`purchased`))
949 SUBPARTITION BY HASH (year(`purchased`))
950 (PARTITION p0 VALUES LESS THAN (1990)
951 (SUBPARTITION sp00 ENGINE = InnoDB,
952 SUBPARTITION sp01 ENGINE = InnoDB),
953 PARTITION p1 VALUES LESS THAN (2000)
954 (SUBPARTITION sp10 ENGINE = InnoDB,
955 SUBPARTITION sp11 ENGINE = InnoDB),
956 PARTITION p2 VALUES LESS THAN (2010)
957 (SUBPARTITION sp20 ENGINE = InnoDB,
958 SUBPARTITION sp21 ENGINE = InnoDB)) */
959 DELETE FROM t1 WHERE id = 6;
960 SELECT MAX(id) AS `Expect 5` FROM t1;
961 Expect 5
962 5
963 INSERT INTO t1 VALUES
964 (0, 'popcorn maker', '2008-07-05'),
965 (0, 'lava lamp', '2009-01-23');
966 SELECT MAX(id) AS `Expect 8` FROM t1;
967 Expect 8
968 8
969 SHOW CREATE TABLE t1;
970 Table Create Table
971 t1 CREATE TABLE `t1` (
972 `id` int NOT NULL AUTO_INCREMENT,
973 `name` varchar(50) DEFAULT NULL,
974 `purchased` date DEFAULT NULL,
975 KEY `id` (`id`)
976 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
977 /*!50100 PARTITION BY RANGE (year(`purchased`))
978 SUBPARTITION BY HASH (year(`purchased`))
979 (PARTITION p0 VALUES LESS THAN (1990)
980 (SUBPARTITION sp00 ENGINE = InnoDB,
981 SUBPARTITION sp01 ENGINE = InnoDB),
982 PARTITION p1 VALUES LESS THAN (2000)
983 (SUBPARTITION sp10 ENGINE = InnoDB,
984 SUBPARTITION sp11 ENGINE = InnoDB),
985 PARTITION p2 VALUES LESS THAN (2010)
986 (SUBPARTITION sp20 ENGINE = InnoDB,
987 SUBPARTITION sp21 ENGINE = InnoDB)) */
988 ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(2015) (SUBPARTITION sp30, SUBPARTITION sp31));
989 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
990 WHERE t.space = df.space
991 AND t.space_type = 'Single'
992 AND df.path NOT LIKE './mysql/%'
993 AND df.path NOT LIKE '%undo%'
994 AND df.path NOT LIKE '._mysql_%'
995 ORDER BY t.name;
996 LOWER(path)
997 ./mtr/asserted_test_suppressions.ibd
998 ./mtr/global_suppressions.ibd
999 ./mtr/test_ignored_global_suppressions.ibd
1000 ./mtr/test_suppressions.ibd
1001 ./sys/sys_config.ibd
1002 ./test/t1#p#p0#sp#sp00.ibd
1003 ./test/t1#p#p0#sp#sp01.ibd
1004 ./test/t1#p#p1#sp#sp10.ibd
1005 ./test/t1#p#p1#sp#sp11.ibd
1006 ./test/t1#p#p2#sp#sp20.ibd
1007 ./test/t1#p#p2#sp#sp21.ibd
1008 ./test/t1#p#p3#sp#sp30.ibd
1009 ./test/t1#p#p3#sp#sp31.ibd
1010 ./test/tr.ibd
1011 SHOW CREATE TABLE t1;
1012 Table Create Table
1013 t1 CREATE TABLE `t1` (
1014 `id` int NOT NULL AUTO_INCREMENT,
1015 `name` varchar(50) DEFAULT NULL,
1016 `purchased` date DEFAULT NULL,
1017 KEY `id` (`id`)
1018 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1019 /*!50100 PARTITION BY RANGE (year(`purchased`))
1020 SUBPARTITION BY HASH (year(`purchased`))
1021 (PARTITION p0 VALUES LESS THAN (1990)
1022 (SUBPARTITION sp00 ENGINE = InnoDB,
1023 SUBPARTITION sp01 ENGINE = InnoDB),
1024 PARTITION p1 VALUES LESS THAN (2000)
1025 (SUBPARTITION sp10 ENGINE = InnoDB,
1026 SUBPARTITION sp11 ENGINE = InnoDB),
1027 PARTITION p2 VALUES LESS THAN (2010)
1028 (SUBPARTITION sp20 ENGINE = InnoDB,
1029 SUBPARTITION sp21 ENGINE = InnoDB),
1030 PARTITION p3 VALUES LESS THAN (2015)
1031 (SUBPARTITION sp30 ENGINE = InnoDB,
1032 SUBPARTITION sp31 ENGINE = InnoDB)) */
1033 INSERT INTO t1 VALUES
1034 (0, 'study desk', '2012-02-20');
1035 SELECT MAX(id) AS `Expect 9` FROM t1;
1036 Expect 9
1037 9
1038 DELETE FROM t1 WHERE id = 9;
1039 SHOW CREATE TABLE t1;
1040 Table Create Table
1041 t1 CREATE TABLE `t1` (
1042 `id` int NOT NULL AUTO_INCREMENT,
1043 `name` varchar(50) DEFAULT NULL,
1044 `purchased` date DEFAULT NULL,
1045 KEY `id` (`id`)
1046 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1047 /*!50100 PARTITION BY RANGE (year(`purchased`))
1048 SUBPARTITION BY HASH (year(`purchased`))
1049 (PARTITION p0 VALUES LESS THAN (1990)
1050 (SUBPARTITION sp00 ENGINE = InnoDB,
1051 SUBPARTITION sp01 ENGINE = InnoDB),
1052 PARTITION p1 VALUES LESS THAN (2000)
1053 (SUBPARTITION sp10 ENGINE = InnoDB,
1054 SUBPARTITION sp11 ENGINE = InnoDB),
1055 PARTITION p2 VALUES LESS THAN (2010)
1056 (SUBPARTITION sp20 ENGINE = InnoDB,
1057 SUBPARTITION sp21 ENGINE = InnoDB),
1058 PARTITION p3 VALUES LESS THAN (2015)
1059 (SUBPARTITION sp30 ENGINE = InnoDB,
1060 SUBPARTITION sp31 ENGINE = InnoDB)) */
1061 ALTER TABLE t1 DROP PARTITION p3;
1062 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
1063 WHERE t.space = df.space
1064 AND t.space_type = 'Single'
1065 AND df.path NOT LIKE './mysql/%'
1066 AND df.path NOT LIKE '%undo%'
1067 AND df.path NOT LIKE '._mysql_%'
1068 ORDER BY t.name;
1069 LOWER(path)
1070 ./mtr/asserted_test_suppressions.ibd
1071 ./mtr/global_suppressions.ibd
1072 ./mtr/test_ignored_global_suppressions.ibd
1073 ./mtr/test_suppressions.ibd
1074 ./sys/sys_config.ibd
1075 ./test/t1#p#p0#sp#sp00.ibd
1076 ./test/t1#p#p0#sp#sp01.ibd
1077 ./test/t1#p#p1#sp#sp10.ibd
1078 ./test/t1#p#p1#sp#sp11.ibd
1079 ./test/t1#p#p2#sp#sp20.ibd
1080 ./test/t1#p#p2#sp#sp21.ibd
1081 ./test/tr.ibd
1082 SHOW CREATE TABLE t1;
1083 Table Create Table
1084 t1 CREATE TABLE `t1` (
1085 `id` int NOT NULL AUTO_INCREMENT,
1086 `name` varchar(50) DEFAULT NULL,
1087 `purchased` date DEFAULT NULL,
1088 KEY `id` (`id`)
1089 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1090 /*!50100 PARTITION BY RANGE (year(`purchased`))
1091 SUBPARTITION BY HASH (year(`purchased`))
1092 (PARTITION p0 VALUES LESS THAN (1990)
1093 (SUBPARTITION sp00 ENGINE = InnoDB,
1094 SUBPARTITION sp01 ENGINE = InnoDB),
1095 PARTITION p1 VALUES LESS THAN (2000)
1096 (SUBPARTITION sp10 ENGINE = InnoDB,
1097 SUBPARTITION sp11 ENGINE = InnoDB),
1098 PARTITION p2 VALUES LESS THAN (2010)
1099 (SUBPARTITION sp20 ENGINE = InnoDB,
1100 SUBPARTITION sp21 ENGINE = InnoDB)) */
1101 INSERT INTO t1 VALUES
1102 (0, 'study desk', '2009-05-06');
1103 SELECT MAX(id) AS `Expect 10` FROM t1;
1104 Expect 10
1105 10
1106 INSERT INTO t1 VALUES
1107 (0, 'lava lamp', '2007-12-25'),
1108 (0, 'lava lamp', '2007-12-26'),
1109 (0, 'lava lamp', '2003-12-27');
1110 SHOW CREATE TABLE t1;
1111 Table Create Table
1112 t1 CREATE TABLE `t1` (
1113 `id` int NOT NULL AUTO_INCREMENT,
1114 `name` varchar(50) DEFAULT NULL,
1115 `purchased` date DEFAULT NULL,
1116 KEY `id` (`id`)
1117 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1118 /*!50100 PARTITION BY RANGE (year(`purchased`))
1119 SUBPARTITION BY HASH (year(`purchased`))
1120 (PARTITION p0 VALUES LESS THAN (1990)
1121 (SUBPARTITION sp00 ENGINE = InnoDB,
1122 SUBPARTITION sp01 ENGINE = InnoDB),
1123 PARTITION p1 VALUES LESS THAN (2000)
1124 (SUBPARTITION sp10 ENGINE = InnoDB,
1125 SUBPARTITION sp11 ENGINE = InnoDB),
1126 PARTITION p2 VALUES LESS THAN (2010)
1127 (SUBPARTITION sp20 ENGINE = InnoDB,
1128 SUBPARTITION sp21 ENGINE = InnoDB)) */
1129 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO
1130 (PARTITION p21 VALUES LESS THAN (2005)
1131 (SUBPARTITION sp210, SUBPARTITION sp211),
1132 PARTITION p22 VALUES LESS THAN (2010)
1133 (SUBPARTITION sp220, SUBPARTITION sp221));
1134 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
1135 WHERE t.space = df.space
1136 AND t.space_type = 'Single'
1137 AND df.path NOT LIKE './mysql/%'
1138 AND df.path NOT LIKE '%undo%'
1139 AND df.path NOT LIKE '._mysql_%'
1140 ORDER BY t.name;
1141 LOWER(path)
1142 ./mtr/asserted_test_suppressions.ibd
1143 ./mtr/global_suppressions.ibd
1144 ./mtr/test_ignored_global_suppressions.ibd
1145 ./mtr/test_suppressions.ibd
1146 ./sys/sys_config.ibd
1147 ./test/t1#p#p0#sp#sp00.ibd
1148 ./test/t1#p#p0#sp#sp01.ibd
1149 ./test/t1#p#p1#sp#sp10.ibd
1150 ./test/t1#p#p1#sp#sp11.ibd
1151 ./test/t1#p#p21#sp#sp210.ibd
1152 ./test/t1#p#p21#sp#sp211.ibd
1153 ./test/t1#p#p22#sp#sp220.ibd
1154 ./test/t1#p#p22#sp#sp221.ibd
1155 ./test/tr.ibd
1156 SHOW CREATE TABLE t1;
1157 Table Create Table
1158 t1 CREATE TABLE `t1` (
1159 `id` int NOT NULL AUTO_INCREMENT,
1160 `name` varchar(50) DEFAULT NULL,
1161 `purchased` date DEFAULT NULL,
1162 KEY `id` (`id`)
1163 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1164 /*!50100 PARTITION BY RANGE (year(`purchased`))
1165 SUBPARTITION BY HASH (year(`purchased`))
1166 (PARTITION p0 VALUES LESS THAN (1990)
1167 (SUBPARTITION sp00 ENGINE = InnoDB,
1168 SUBPARTITION sp01 ENGINE = InnoDB),
1169 PARTITION p1 VALUES LESS THAN (2000)
1170 (SUBPARTITION sp10 ENGINE = InnoDB,
1171 SUBPARTITION sp11 ENGINE = InnoDB),
1172 PARTITION p21 VALUES LESS THAN (2005)
1173 (SUBPARTITION sp210 ENGINE = InnoDB,
1174 SUBPARTITION sp211 ENGINE = InnoDB),
1175 PARTITION p22 VALUES LESS THAN (2010)
1176 (SUBPARTITION sp220 ENGINE = InnoDB,
1177 SUBPARTITION sp221 ENGINE = InnoDB)) */
1178 INSERT INTO t1 VALUES(0, 'lava lamp', '2002-12-28');
1179 SELECT MAX(id) AS `Expect 14` FROM t1;
1180 Expect 14
1181 14
1182 ALTER TABLE t1 DROP PARTITION p21, p0;
1183 SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
1184 WHERE t.space = df.space
1185 AND t.space_type = 'Single'
1186 AND df.path NOT LIKE './mysql/%'
1187 AND df.path NOT LIKE '%undo%'
1188 AND df.path NOT LIKE '._mysql_%'
1189 ORDER BY t.name;
1190 LOWER(path)
1191 ./mtr/asserted_test_suppressions.ibd
1192 ./mtr/global_suppressions.ibd
1193 ./mtr/test_ignored_global_suppressions.ibd
1194 ./mtr/test_suppressions.ibd
1195 ./sys/sys_config.ibd
1196 ./test/t1#p#p1#sp#sp10.ibd
1197 ./test/t1#p#p1#sp#sp11.ibd
1198 ./test/t1#p#p22#sp#sp220.ibd
1199 ./test/t1#p#p22#sp#sp221.ibd
1200 ./test/tr.ibd
1201 SHOW CREATE TABLE t1;
1202 Table Create Table
1203 t1 CREATE TABLE `t1` (
1204 `id` int NOT NULL AUTO_INCREMENT,
1205 `name` varchar(50) DEFAULT NULL,
1206 `purchased` date DEFAULT NULL,
1207 KEY `id` (`id`)
1208 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1209 /*!50100 PARTITION BY RANGE (year(`purchased`))
1210 SUBPARTITION BY HASH (year(`purchased`))
1211 (PARTITION p1 VALUES LESS THAN (2000)
1212 (SUBPARTITION sp10 ENGINE = InnoDB,
1213 SUBPARTITION sp11 ENGINE = InnoDB),
1214 PARTITION p22 VALUES LESS THAN (2010)
1215 (SUBPARTITION sp220 ENGINE = InnoDB,
1216 SUBPARTITION sp221 ENGINE = InnoDB)) */
1217 INSERT INTO t1 VALUES
1218 (0, 'lava lamp', '2005-12-30'),
1219 (0, 'lava lamp', '2005-12-31');
1220 SELECT MAX(id) AS `Expect 16` FROM t1;
1221 Expect 16
1222 16
1223 SHOW CREATE TABLE t1;
1224 Table Create Table
1225 t1 CREATE TABLE `t1` (
1226 `id` int NOT NULL AUTO_INCREMENT,
1227 `name` varchar(50) DEFAULT NULL,
1228 `purchased` date DEFAULT NULL,
1229 KEY `id` (`id`)
1230 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1231 /*!50100 PARTITION BY RANGE (year(`purchased`))
1232 SUBPARTITION BY HASH (year(`purchased`))
1233 (PARTITION p1 VALUES LESS THAN (2000)
1234 (SUBPARTITION sp10 ENGINE = InnoDB,
1235 SUBPARTITION sp11 ENGINE = InnoDB),
1236 PARTITION p22 VALUES LESS THAN (2010)
1237 (SUBPARTITION sp220 ENGINE = InnoDB,
1238 SUBPARTITION sp221 ENGINE = InnoDB)) */
1239 DROP TABLE t1;
1240 DROP TABLE tr;