Debian Codegraph @

mysql-test/suite/innodb/t/partition_autoinc.test

raw
1 --source include/have_innodb_max_16k.inc
2
3 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
4
5 let $file_name_checking = SELECT LOWER(path) FROM information_schema.innodb_tablespaces AS `t`, information_schema.innodb_datafiles AS `df`
6 WHERE t.space = df.space
7 AND t.space_type = 'Single'
8 AND df.path NOT LIKE './mysql/%'
9 AND df.path NOT LIKE '%undo%'
10 AND df.path NOT LIKE '._mysql_%'
11 ORDER BY t.name;
12
13 CREATE TABLE tr (
14 id INT NOT NULL AUTO_INCREMENT,
15 name VARCHAR(50),
16 purchased DATE,
17 KEY(id));
18
19 INSERT INTO tr VALUES
20 (1, 'desk organiser', '2003-10-15'),
21 (2, 'CD player', '1993-11-05'),
22 (3, 'TV set', '1996-03-10'),
23 (4, 'bookcase', '1982-01-10'),
24 (5, 'exercise bike', '2004-05-09'),
25 (6, 'sofa', '1987-06-05');
26
27 ###############################
28 --echo # Test for ADD PARTITION
29 ###############################
30
31 CREATE TABLE t1 (
32 id INT NOT NULL AUTO_INCREMENT,
33 name VARCHAR(50),
34 purchased DATE,
35 KEY(id))
36 PARTITION BY RANGE( YEAR(purchased) ) (
37 PARTITION p0 VALUES LESS THAN (1990),
38 PARTITION p1 VALUES LESS THAN (1995),
39 PARTITION p2 VALUES LESS THAN (2000),
40 PARTITION p3 VALUES LESS THAN (2005)
41 );
42
43 --disable_warnings
44 INSERT INTO t1 SELECT * FROM tr;
45 --enable_warnings
46
47 SHOW CREATE TABLE t1;
48
49 DELETE FROM t1 WHERE id = 6;
50
51 SELECT MAX(id) AS `Expect 5` FROM t1;
52
53 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010), PARTITION p5 VALUES LESS THAN (2015));
54
55 --eval $file_name_checking
56
57 SHOW CREATE TABLE t1;
58
59 INSERT INTO t1 VALUES (0, 'popcorn maker', '2010-11-22');
60
61 SELECT MAX(id) AS `Expect 7` FROM t1;
62
63 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION (PARTITION p6 VALUES LESS THAN (2018), PARTITION p7 VALUES LESS THAN (2020));
64
65 --eval $file_name_checking
66
67 SHOW CREATE TABLE t1;
68
69 --source include/restart_mysqld.inc
70
71 SHOW CREATE TABLE t1;
72
73 INSERT INTO t1 VALUES
74 (0, 'aquarium', '2019-08-04'),
75 (0, 'study desk', '2016-06-18');
76
77 SELECT MAX(id) AS `Expect 9` FROM t1;
78
79 DROP TABLE t1;
80
81 ###############################################################
82 --echo # Test for DROP PARTITION, along with some ADD PARTITION
83 ###############################################################
84
85 CREATE TABLE t1 (
86 id INT NOT NULL AUTO_INCREMENT,
87 name VARCHAR(50),
88 purchased DATE,
89 KEY(id))
90 PARTITION BY RANGE( YEAR(purchased) ) (
91 PARTITION p0 VALUES LESS THAN (1990),
92 PARTITION p1 VALUES LESS THAN (1995),
93 PARTITION p2 VALUES LESS THAN (2000),
94 PARTITION p3 VALUES LESS THAN (2005)
95 );
96
97 --disable_warnings
98 INSERT INTO t1 SELECT * FROM tr;
99 --enable_warnings
100
101 SHOW CREATE TABLE t1;
102
103 SELECT id FROM t1 PARTITION(p0);
104 SELECT id FROM t1 PARTITION(p1);
105 SELECT id FROM t1 PARTITION(p2);
106 SELECT id FROM t1 PARTITION(p3);
107
108 --echo # This doesn't delete the row containing max(id) in the table
109 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p2;
110
111 --eval $file_name_checking
112
113 SHOW CREATE TABLE t1;
114
115 INSERT INTO t1 VALUES (0, 'aquarium', '2001-07-02');
116
117 SELECT MAX(id) AS `Expect 7` FROM t1;
118
119 --echo # This will delete the row containing max(id) in the table
120 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p3;
121
122 --eval $file_name_checking
123
124 SELECT MAX(id) AS `Expect 6` FROM t1;
125
126 SHOW CREATE TABLE t1;
127
128 INSERT INTO t1 VALUES(0, 'study desk', '1988-09-16');
129
130 SELECT MAX(id) AS `Expect 8` FROM t1;
131
132 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION (PARTITION p3 VALUES LESS THAN (2008));
133
134 --eval $file_name_checking
135
136 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p0;
137
138 --eval $file_name_checking
139
140 SHOW CREATE TABLE t1;
141
142 --source include/restart_mysqld.inc
143
144 SHOW CREATE TABLE t1;
145
146 INSERT INTO t1 VALUES(0, 'lava lamp', '2006-01-02');
147
148 SELECT MAX(id) AS `Expect 9` FROM t1;
149
150 DELETE FROM t1 WHERE id >= 0;
151
152 ALTER TABLE t1 ALGORITHM = INPLACE, DROP PARTITION p3;
153
154 --eval $file_name_checking
155
156 SHOW CREATE TABLE t1;
157
158 INSERT INTO t1 VALUES(0, 'lava lamp', '1992-01-15');
159
160 SELECT MAX(id) AS `Expect 10` FROM t1;
161
162 DROP TABLE t1;
163
164 #####################################################
165 --echo # Test for ADD PARTITION for HASH PARTITIONING
166 #####################################################
167
168 CREATE TABLE t1 (
169 id INT NOT NULL AUTO_INCREMENT,
170 name VARCHAR(50),
171 purchased DATE,
172 KEY(id))
173 PARTITION BY HASH( YEAR(purchased) ) PARTITIONS 4;
174
175 --disable_warnings
176 INSERT INTO t1 SELECT * FROM tr;
177 --enable_warnings
178
179 SHOW CREATE TABLE t1;
180
181 DELETE FROM t1 WHERE id = 6;
182
183 SELECT MAX(id) AS `Expect 5` FROM t1;
184
185 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION PARTITIONS 2;
186
187 --eval $file_name_checking
188
189 SHOW CREATE TABLE t1;
190
191 INSERT INTO t1 VALUES (0, 'popcorn maker', '2010-11-22');
192
193 SELECT MAX(id) AS `Expect 7` FROM t1;
194
195 ALTER TABLE t1 ALGORITHM = INPLACE, ADD PARTITION PARTITIONS 2;
196
197 --eval $file_name_checking
198
199 SHOW CREATE TABLE t1;
200
201 --source include/restart_mysqld.inc
202
203 SHOW CREATE TABLE t1;
204
205 INSERT INTO t1 VALUES
206 (0, 'aquarium', '2019-08-04'),
207 (0, 'study desk', '2016-06-18');
208
209 SELECT MAX(id) AS `Expect 9` FROM t1;
210
211 DROP TABLE t1;
212
213 #######################################################
214 --echo # Test for ADD PARTITION for HASH PARTITIONINNG,
215 --echo # along with COALESCE PARTITION
216 #######################################################
217
218 CREATE TABLE t1 (
219 id INT NOT NULL AUTO_INCREMENT,
220 name VARCHAR(50),
221 purchased DATE,
222 KEY(id))
223 PARTITION BY HASH( YEAR(purchased) ) PARTITIONS 8;
224
225 --disable_warnings
226 INSERT INTO t1 SELECT * FROM tr;
227 --enable_warnings
228
229 SHOW CREATE TABLE t1;
230
231 DELETE FROM t1 WHERE id = 6;
232
233 SELECT MAX(id) AS `Expect 5` FROM t1;
234
235 ALTER TABLE t1 ALGORITHM = INPLACE, COALESCE PARTITION 2;
236
237 --eval $file_name_checking
238
239 SHOW CREATE TABLE t1;
240
241 INSERT INTO t1 VALUES (0, 'popcorn maker', '2010-11-22');
242
243 SELECT MAX(id) AS `Expect 7` FROM t1;
244
245 SHOW CREATE TABLE t1;
246
247 ALTER TABLE t1 ALGORITHM = INPLACE, COALESCE PARTITION 4;
248
249 --eval $file_name_checking
250
251 --source include/restart_mysqld.inc
252
253 SHOW CREATE TABLE t1;
254
255 INSERT INTO t1 VALUES
256 (0, 'aquarium', '2019-08-04'),
257 (0, 'study desk', '2016-06-18');
258
259 SELECT MAX(id) AS `Expect 9` FROM t1;
260
261 SHOW CREATE TABLE t1;
262
263 DROP TABLE t1;
264
265 ######################################
266 --echo # Test for REORGANIZE PARTITION
267 ######################################
268
269 CREATE TABLE t1 (
270 id INT NOT NULL AUTO_INCREMENT,
271 name VARCHAR(50),
272 purchased DATE,
273 KEY(id))
274 PARTITION BY RANGE( YEAR(purchased) ) (
275 PARTITION p0 VALUES LESS THAN (1990),
276 PARTITION p1 VALUES LESS THAN (1995),
277 PARTITION p2 VALUES LESS THAN (2000),
278 PARTITION p3 VALUES LESS THAN (2005)
279 );
280
281 --disable_warnings
282 INSERT INTO t1 SELECT * FROM tr;
283 --enable_warnings
284
285 SHOW CREATE TABLE t1;
286
287 DELETE FROM t1 WHERE id = 6;
288
289 SELECT MAX(id) AS `Expect 5` FROM t1;
290
291 ALTER TABLE t1 ALGORITHM = INPLACE, REORGANIZE PARTITION p0, p1 INTO
292 (PARTITION p01 VALUES LESS THAN(1985), PARTITION p02 VALUES LESS THAN(1990),
293 PARTITION p11 VALUES LESS THAN(1993), PARTITION p12 VALUES LESS THAN(1995));
294
295 --eval $file_name_checking
296
297 SHOW CREATE TABLE t1;
298
299 INSERT INTO t1 VALUES (0, 'popcorn maker', '2001-11-22');
300
301 SELECT MAX(id) AS `Expect 7` FROM t1;
302
303 SHOW CREATE TABLE t1;
304
305 ALTER TABLE t1 ALGORITHM = INPLACE, REORGANIZE PARTITION p2, p3 INTO
306 (PARTITION p21 VALUES LESS THAN(1997), PARTITION p22 VALUES LESS THAN(2000),
307 PARTITION p31 VALUES LESS THAN(2002), PARTITION p32 VALUES LESS THAN(2005));
308
309 --eval $file_name_checking
310
311 SHOW CREATE TABLE t1;
312
313 INSERT INTO t1 VALUES
314 (0, 'aquarium', '2002-08-04'),
315 (0, 'study desk', '2004-06-18');
316
317 SELECT MAX(id) AS `Expect 9` FROM t1;
318
319 SHOW CREATE TABLE t1;
320
321 ###################################
322 --echo # Test for REBUILD PARTITION
323 ###################################
324
325 ALTER TABLE t1 ALGORITHM = INPLACE, REBUILD PARTITION p01, p02, p31, p32;
326
327 --eval $file_name_checking
328
329 SHOW CREATE TABLE t1;
330
331 INSERT INTO t1 VALUES (0, 'lava lamp', '2000-12-10');
332
333 SHOW CREATE TABLE t1;
334
335 SELECT MAX(id) AS `Expect 10` FROM t1;
336
337 DROP TABLE t1;
338
339 ####################################
340 --echo # Test for EXCHANGE PARTITION
341 ####################################
342
343 CREATE TABLE t1 (
344 id INT NOT NULL AUTO_INCREMENT,
345 name VARCHAR(50),
346 purchased DATE,
347 KEY(id))
348 PARTITION BY RANGE( YEAR(purchased) ) (
349 PARTITION p0 VALUES LESS THAN (1990),
350 PARTITION p1 VALUES LESS THAN (1995),
351 PARTITION p2 VALUES LESS THAN (2000),
352 PARTITION p3 VALUES LESS THAN (2005)
353 );
354
355 --disable_warnings
356 INSERT INTO t1 SELECT * FROM tr;
357 --enable_warnings
358
359 SHOW CREATE TABLE t1;
360
361 DELETE FROM t1 WHERE id = 6;
362
363 SELECT MAX(id) AS `Expect 5` FROM t1;
364
365 CREATE TABLE tp (
366 id INT NOT NULL AUTO_INCREMENT,
367 name VARCHAR(50),
368 purchased DATE,
369 KEY(id)) ENGINE = InnoDB;
370
371 INSERT INTO tp VALUES (100, 'DVD player', '2002-03-21');
372
373 ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE tp;
374
375 --eval $file_name_checking
376
377 SHOW CREATE TABLE tp;
378 SHOW CREATE TABLE t1;
379
380 INSERT INTO tp VALUES(0, 'DVD player', '2002-03-22');
381 INSERT INTO t1 VALUES(0, 'DVD player', '2002-03-23');
382
383 SELECT MAX(id) AS `Expect 101` FROM t1;
384 SELECT MAX(id) AS `Expect 101` FROM tp;
385
386 ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE tp;
387
388 --eval $file_name_checking
389
390 SHOW CREATE TABLE tp;
391 SHOW CREATE TABLE t1;
392
393 INSERT INTO tp VALUES(0, 'DVD player', '2002-03-24');
394 INSERT INTO t1 VALUES(0, 'DVD player', '2002-03-25');
395
396 SELECT MAX(id) AS `Expect 102` FROM t1;
397 SELECT MAX(id) AS `Expect 102` FROM tp;
398
399 DROP TABLE tp, t1;
400
401 #################################
402 --echo # Test for subpartitioning
403 #################################
404
405 CREATE TABLE t1 (
406 id INT NOT NULL AUTO_INCREMENT,
407 name VARCHAR(50),
408 purchased DATE,
409 KEY(id))
410 PARTITION BY RANGE( YEAR(purchased) )
411 SUBPARTITION BY HASH ( YEAR(purchased) )
412 (PARTITION p0 VALUES LESS THAN (1990)
413 (SUBPARTITION sp00, SUBPARTITION sp01),
414 PARTITION p1 VALUES LESS THAN (2000)
415 (SUBPARTITION sp10, SUBPARTITION sp11),
416 PARTITION p2 VALUES LESS THAN (2010)
417 (SUBPARTITION sp20, SUBPARTITION sp21));
418
419 --disable_warnings
420 INSERT INTO t1 SELECT * FROM tr;
421 --enable_warnings
422
423 SHOW CREATE TABLE t1;
424
425 DELETE FROM t1 WHERE id = 6;
426
427 SELECT MAX(id) AS `Expect 5` FROM t1;
428
429 INSERT INTO t1 VALUES
430 (0, 'popcorn maker', '2008-07-05'),
431 (0, 'lava lamp', '2009-01-23');
432
433 SELECT MAX(id) AS `Expect 8` FROM t1;
434
435 SHOW CREATE TABLE t1;
436
437 ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(2015) (SUBPARTITION sp30, SUBPARTITION sp31));
438
439 --eval $file_name_checking
440
441 SHOW CREATE TABLE t1;
442
443 INSERT INTO t1 VALUES
444 (0, 'study desk', '2012-02-20');
445
446 SELECT MAX(id) AS `Expect 9` FROM t1;
447
448 DELETE FROM t1 WHERE id = 9;
449
450 SHOW CREATE TABLE t1;
451
452 ALTER TABLE t1 DROP PARTITION p3;
453
454 --eval $file_name_checking
455
456 SHOW CREATE TABLE t1;
457
458 INSERT INTO t1 VALUES
459 (0, 'study desk', '2009-05-06');
460
461 SELECT MAX(id) AS `Expect 10` FROM t1;
462
463 INSERT INTO t1 VALUES
464 (0, 'lava lamp', '2007-12-25'),
465 (0, 'lava lamp', '2007-12-26'),
466 (0, 'lava lamp', '2003-12-27');
467
468 SHOW CREATE TABLE t1;
469
470 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO
471 (PARTITION p21 VALUES LESS THAN (2005)
472 (SUBPARTITION sp210, SUBPARTITION sp211),
473 PARTITION p22 VALUES LESS THAN (2010)
474 (SUBPARTITION sp220, SUBPARTITION sp221));
475
476 --eval $file_name_checking
477
478 SHOW CREATE TABLE t1;
479
480 INSERT INTO t1 VALUES(0, 'lava lamp', '2002-12-28');
481
482 SELECT MAX(id) AS `Expect 14` FROM t1;
483
484 ALTER TABLE t1 DROP PARTITION p21, p0;
485
486 --eval $file_name_checking
487
488 SHOW CREATE TABLE t1;
489
490 INSERT INTO t1 VALUES
491 (0, 'lava lamp', '2005-12-30'),
492 (0, 'lava lamp', '2005-12-31');
493
494 SELECT MAX(id) AS `Expect 16` FROM t1;
495
496 SHOW CREATE TABLE t1;
497
498 DROP TABLE t1;
499
500 DROP TABLE tr;