Debian Codegraph @

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

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 #
3 # MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
4 #
5 # WL#6204 InnoDB persistent max value for autoinc columns
6 #
7 # Most of this test case is copied from the test innodb.autoinc_persist
8 # that was introduced in MySQL 8.0.0. The observable behaviour
9 # of MDEV-6076 is equivalent to WL#6204, with the exception that
10 # there is less buffering taking place and redo log checkpoints
11 # are not being treated specially.
12 # Due to less buffering, there is no debug instrumentation testing
13 # for MDEV-6076.
14 #
15 # Pre-create several tables
16 SET SQL_MODE='STRICT_ALL_TABLES';
17 CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
18 INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
19 (20), (30), (31);
20 SELECT * FROM t1;
21 a
22 -10
23 -1
24 1
25 2
26 3
27 4
28 5
29 20
30 30
31 31
32 CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
33 INSERT INTO t2 VALUES(-5);
34 ERROR 22003: Out of range value for column 'a' at row 1
35 INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
36 (20), (30), (31);
37 SELECT * FROM t2;
38 a
39 1
40 2
41 3
42 4
43 8
44 10
45 11
46 20
47 30
48 31
49 CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
50 INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
51 (20), (30), (31), (1024), (4096);
52 SELECT * FROM t3;
53 a
54 -10
55 -1
56 1
57 2
58 3
59 4
60 5
61 20
62 30
63 31
64 1024
65 4096
66 CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
67 INSERT INTO t4 VALUES(-5);
68 ERROR 22003: Out of range value for column 'a' at row 1
69 INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
70 (20), (30), (31), (1024), (4096);
71 SELECT * FROM t4;
72 a
73 1
74 2
75 3
76 4
77 8
78 10
79 11
80 20
81 30
82 31
83 1024
84 4096
85 CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
86 INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
87 (20), (30), (31), (1000000), (1000005);
88 SELECT * FROM t5;
89 a
90 -10
91 -1
92 1
93 2
94 3
95 4
96 5
97 20
98 30
99 31
100 1000000
101 1000005
102 CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
103 INSERT INTO t6 VALUES(-5);
104 ERROR 22003: Out of range value for column 'a' at row 1
105 INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
106 (20), (30), (31), (1000000), (1000005);
107 SELECT * FROM t6;
108 a
109 1
110 2
111 3
112 4
113 8
114 10
115 11
116 20
117 30
118 31
119 1000000
120 1000005
121 CREATE TABLE t7(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB;
122 INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
123 (20), (30), (31), (100000000), (100000008);
124 SELECT * FROM t7;
125 a
126 -10
127 -1
128 1
129 2
130 3
131 4
132 5
133 20
134 30
135 31
136 100000000
137 100000008
138 CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
139 INSERT INTO t8 VALUES(-5);
140 ERROR 22003: Out of range value for column 'a' at row 1
141 INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
142 (20), (30), (31), (100000000), (100000008);
143 SELECT * FROM t8;
144 a
145 1
146 2
147 3
148 4
149 8
150 10
151 11
152 20
153 30
154 31
155 100000000
156 100000008
157 CREATE TABLE t9(a BIGINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
158 INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
159 (20), (30), (31), (100000000000), (100000000006);
160 SELECT * FROM t9;
161 a
162 -10
163 -1
164 1
165 2
166 3
167 4
168 5
169 20
170 30
171 31
172 100000000000
173 100000000006
174 CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
175 INSERT INTO t10 VALUES(-5);
176 ERROR 22003: Out of range value for column 'a' at row 1
177 INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
178 (20), (30), (31), (100000000000), (100000000006);
179 SELECT * FROM t10;
180 a
181 1
182 2
183 3
184 4
185 8
186 10
187 11
188 20
189 30
190 31
191 100000000000
192 100000000006
193 CREATE TABLE t11(a FLOAT AUTO_INCREMENT KEY) ENGINE = InnoDB;
194 INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
195 SELECT * FROM t11;
196 a
197 -10
198 -1
199 1
200 2
201 3
202 4
203 5
204 20
205 30
206 31
207 CREATE TABLE t11u(a FLOAT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
208 INSERT INTO t11u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
209 ERROR 22003: Out of range value for column 'a' at row 5
210 INSERT INTO t11u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
211 SELECT * FROM t11u;
212 a
213 11
214 12
215 13
216 14
217 15
218 20
219 30
220 31
221 CREATE TABLE t12(a DOUBLE AUTO_INCREMENT KEY) ENGINE = InnoDB;
222 INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
223 SELECT * FROM t12;
224 a
225 -10
226 -1
227 1
228 2
229 3
230 4
231 5
232 20
233 30
234 31
235 CREATE TABLE t12u(a DOUBLE UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
236 INSERT INTO t12u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
237 ERROR 22003: Out of range value for column 'a' at row 5
238 INSERT INTO t12u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
239 SELECT * FROM t12u;
240 a
241 11
242 12
243 13
244 14
245 15
246 20
247 30
248 31
249 # Scenario 1: Normal restart, to test if the counters are persisted
250 # Scenario 2: Delete some values, to test the counters should not be the
251 # one which is the largest in current table
252 DELETE FROM t1 WHERE a > 30;
253 SELECT MAX(a) AS `Expect 30` FROM t1;
254 Expect 30
255 30
256 DELETE FROM t3 WHERE a > 2000;
257 SELECT MAX(a) AS `Expect 2000` FROM t3;
258 Expect 2000
259 1024
260 DELETE FROM t5 WHERE a > 1000000;
261 SELECT MAX(a) AS `Expect 1000000` FROM t5;
262 Expect 1000000
263 1000000
264 DELETE FROM t7 WHERE a > 100000000;
265 SELECT MAX(a) AS `Expect 100000000` FROM t7;
266 Expect 100000000
267 100000000
268 DELETE FROM t9 WHERE a > 100000000000;
269 SELECT MAX(a) AS `Expect 100000000000` FROM t9;
270 Expect 100000000000
271 100000000000
272 CREATE TABLE t13(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB,
273 AUTO_INCREMENT = 1234;
274 # restart
275 SHOW CREATE TABLE t13;
276 Table Create Table
277 t13 CREATE TABLE `t13` (
278 `a` int(11) NOT NULL AUTO_INCREMENT,
279 PRIMARY KEY (`a`)
280 ) ENGINE=InnoDB AUTO_INCREMENT=1234 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
281 INSERT INTO t13 VALUES(0);
282 SELECT a AS `Expect 1234` FROM t13;
283 Expect 1234
284 1234
285 DROP TABLE t13;
286 INSERT INTO t1 VALUES(0), (0);
287 SELECT MAX(a) AS `Expect 33` FROM t1;
288 Expect 33
289 33
290 INSERT INTO t3 VALUES(0), (0);
291 SELECT MAX(a) AS `Expect 4098` FROM t3;
292 Expect 4098
293 4098
294 INSERT INTO t5 VALUES(0), (0);
295 SELECT MAX(a) AS `Expect 1000007` FROM t5;
296 Expect 1000007
297 1000007
298 INSERT INTO t7 VALUES(0), (0);
299 SELECT MAX(a) AS `Expect 100000010` FROM t7;
300 Expect 100000010
301 100000010
302 INSERT INTO t9 VALUES(0), (0);
303 SELECT MAX(a) AS `Expect 100000000008` FROM t9;
304 Expect 100000000008
305 100000000008
306 # Scenario 3: Insert some bigger counters, the next counter should start
307 # from there
308 INSERT INTO t1 VALUES(40), (0);
309 INSERT INTO t1 VALUES(42), (0);
310 SELECT a AS `Expect 43, 42` FROM t1 ORDER BY a DESC LIMIT 4;
311 Expect 43, 42
312 43
313 42
314 41
315 40
316 INSERT INTO t3 VALUES(5000), (0);
317 INSERT INTO t3 VALUES(5010), (0);
318 SELECT a AS `Expect 5011, 5010` FROM t3 ORDER BY a DESC LIMIT 4;
319 Expect 5011, 5010
320 5011
321 5010
322 5001
323 5000
324 INSERT INTO t5 VALUES(1000010), (0);
325 INSERT INTO t5 VALUES(1000020), (0);
326 SELECT a AS `Expect 1000021, 1000020` FROM t5 ORDER BY a DESC LIMIT 4;
327 Expect 1000021, 1000020
328 1000021
329 1000020
330 1000011
331 1000010
332 INSERT INTO t7 VALUES(100000020), (0);
333 INSERT INTO t7 VALUES(100000030), (0);
334 SELECT a AS `Expect 100000031, 100000030` FROM t7 ORDER BY a DESC LIMIT 4;
335 Expect 100000031, 100000030
336 100000031
337 100000030
338 100000021
339 100000020
340 INSERT INTO t9 VALUES(100000000010), (0);
341 INSERT INTO t9 VALUES(100000000020), (0);
342 SELECT a AS `Expect 100000000021, 100000000020` FROM t9 ORDER BY a DESC LIMIT 4;
343 Expect 100000000021, 100000000020
344 100000000021
345 100000000020
346 100000000011
347 100000000010
348 # Scenario 4: Update some values, to test the counters should be updated
349 # to the bigger value, but not smaller value.
350 INSERT INTO t1 VALUES(50), (55);
351 UPDATE t1 SET a = 105 WHERE a = 5;
352 UPDATE t1 SET a = 100 WHERE a = 55;
353 # This should insert 102, 106, 107, and make next counter 109.
354 INSERT INTO t1 VALUES(102), (0), (0);
355 SELECT a AS `Expect 107, 106` FROM t1 ORDER BY a DESC LIMIT 2;
356 Expect 107, 106
357 107
358 106
359 DELETE FROM t1 WHERE a > 105;
360 INSERT INTO t1 VALUES(0);
361 SELECT MAX(a) AS `Expect 109` FROM t1;
362 Expect 109
363 109
364 # Test the same things on t3, t5, t7, t9, to test if DDTableBuffer would
365 # be updated accordingly
366 INSERT INTO t3 VALUES(60), (65);
367 UPDATE t3 SET a = 6005 WHERE a = 5;
368 UPDATE t3 SET a = 6000 WHERE a = 60;
369 # This should insert 6002, 6006, 6007, and make next counter 6009.
370 INSERT INTO t3 VALUES(6002), (0), (0);
371 SELECT a AS `Expect 6007, 6006` FROM t3 ORDER BY a DESC LIMIT 2;
372 Expect 6007, 6006
373 6007
374 6006
375 DELETE FROM t3 WHERE a > 6005;
376 INSERT INTO t3 VALUES(0);
377 SELECT MAX(a) AS `Expect 6009` FROM t3;
378 Expect 6009
379 6009
380 INSERT INTO t5 VALUES(100), (200);
381 UPDATE t5 SET a = 1000105 WHERE a = 5;
382 UPDATE t5 SET a = 1000100 WHERE a = 100;
383 # This should insert 1000102, 1000106, 1000107, and make next counter
384 # 1000109.
385 INSERT INTO t5 VALUES(1000102), (0), (0);
386 SELECT a AS `Expect 1000107, 1000106` FROM t5 ORDER BY a DESC LIMIT 2;
387 Expect 1000107, 1000106
388 1000107
389 1000106
390 DELETE FROM t5 WHERE a > 1000105;
391 INSERT INTO t5 VALUES(0);
392 SELECT MAX(a) AS `Expect 1000109` FROM t5;
393 Expect 1000109
394 1000109
395 INSERT INTO t7 VALUES(100), (200);
396 UPDATE t7 SET a = 100000105 WHERE a = 5;
397 UPDATE t7 SET a = 100000100 WHERE a = 100;
398 # This should insert 100000102, 1100000106, 100000107, and make next
399 # counter 100000109.
400 INSERT INTO t7 VALUES(100000102), (0), (0);
401 SELECT a AS `Expect 100000107, 100000106` FROM t7 ORDER BY a DESC LIMIT 2;
402 Expect 100000107, 100000106
403 100000107
404 100000106
405 DELETE FROM t7 WHERE a > 100000105;
406 INSERT INTO t7 VALUES(0);
407 SELECT MAX(a) AS `Expect 100000109` FROM t7;
408 Expect 100000109
409 100000109
410 INSERT INTO t9 VALUES(100), (200);
411 UPDATE t9 SET a = 100000000105 WHERE a = 5;
412 UPDATE t9 SET a = 100000000100 WHERE a = 100;
413 # This should insert 100000000102, 100000000106, 100000000107, and make
414 # next counter 100000000109.
415 INSERT INTO t9 VALUES(100000000102), (0), (0);
416 SELECT a AS `Expect 100000000107, 100000000106` FROM t9 ORDER BY a DESC LIMIT 2;
417 Expect 100000000107, 100000000106
418 100000000107
419 100000000106
420 DELETE FROM t9 WHERE a > 100000000105;
421 INSERT INTO t9 VALUES(0);
422 SELECT MAX(a) AS `Expect 100000000109` FROM t9;
423 Expect 100000000109
424 100000000109
425 # restart
426 INSERT INTO t1 VALUES(0), (0);
427 SELECT a AS `Expect 110, 111` FROM t1 ORDER BY a DESC LIMIT 2;
428 Expect 110, 111
429 111
430 110
431 INSERT INTO t3 VALUES(0), (0);
432 SELECT a AS `Expect 6010, 6011` FROM t3 ORDER BY a DESC LIMIT 2;
433 Expect 6010, 6011
434 6011
435 6010
436 INSERT INTO t5 VALUES(0), (0);
437 SELECT a AS `Expect 1100111, 1100110` FROM t5 ORDER BY a DESC LIMIT 2;
438 Expect 1100111, 1100110
439 1000111
440 1000110
441 INSERT INTO t7 VALUES(0), (0);
442 SELECT a AS `Expect 100000111, 100000110` FROM t7 ORDER BY a DESC LIMIT 2;
443 Expect 100000111, 100000110
444 100000111
445 100000110
446 INSERT INTO t9 VALUES(0), (0);
447 SELECT a AS `Expect 100000000111, 100000000110` FROM t9 ORDER BY a DESC LIMIT 2;
448 Expect 100000000111, 100000000110
449 100000000111
450 100000000110
451 # Scenario 5: Test kill the server
452 INSERT INTO t1 VALUES(125);
453 DELETE FROM t1 WHERE a = 125;
454 INSERT INTO t3 VALUES(6100);
455 DELETE FROM t3 WHERE a = 6100;
456 INSERT INTO t5 VALUES(1100200);
457 DELETE FROM t5 WHERE a = 1100200;
458 INSERT INTO t7 VALUES(100000200);
459 DELETE FROM t7 WHERE a = 100000200;
460 # Ensure that all changes before the server is killed are persisted.
461 set global innodb_flush_log_at_trx_commit=1;
462 INSERT INTO t9 VALUES(100000000200);
463 DELETE FROM t9 WHERE a = 100000000200;
464 # restart
465 INSERT INTO t1 VALUES(0);
466 SELECT a AS `Expect 126` FROM t1 ORDER BY a DESC LIMIT 1;
467 Expect 126
468 126
469 INSERT INTO t3 VALUES(0);
470 SELECT a AS `Expect 6101` FROM t3 ORDER BY a DESC LIMIT 1;
471 Expect 6101
472 6101
473 INSERT INTO t5 VALUES(0);
474 SELECT a AS `Expect 1100201` FROM t5 ORDER BY a DESC LIMIT 1;
475 Expect 1100201
476 1100201
477 INSERT INTO t7 VALUES(0);
478 SELECT a AS `Expect 100000201` FROM t7 ORDER BY a DESC LIMIT 1;
479 Expect 100000201
480 100000201
481 INSERT INTO t9 VALUES(0);
482 SELECT a AS `Expect 100000000201` FROM t9 ORDER BY a DESC LIMIT 1;
483 Expect 100000000201
484 100000000201
485 # Scenario 6: Test truncate will reset the counters to 0
486 TRUNCATE TABLE t1;
487 TRUNCATE TABLE t3;
488 TRUNCATE TABLE t5;
489 TRUNCATE TABLE t7;
490 TRUNCATE TABLE t9;
491 INSERT INTO t1 VALUES(0), (0);
492 SELECT * FROM t1;
493 a
494 1
495 2
496 INSERT INTO t3 VALUES(0), (0);
497 SELECT * FROM t3;
498 a
499 1
500 2
501 INSERT INTO t5 VALUES(0), (0);
502 SELECT * FROM t5;
503 a
504 1
505 2
506 INSERT INTO t7 VALUES(0), (0);
507 SELECT * FROM t7;
508 a
509 1
510 2
511 INSERT INTO t9 VALUES(0), (0);
512 SELECT * FROM t9;
513 a
514 1
515 2
516 # Ensure that all changes before the server is killed are persisted.
517 set global innodb_flush_log_at_trx_commit=1;
518 TRUNCATE TABLE t1;
519 TRUNCATE TABLE t3;
520 TRUNCATE TABLE t5;
521 TRUNCATE TABLE t7;
522 TRUNCATE TABLE t9;
523 # Scenario 7: Test explicit rename table won't change the counter
524 RENAME TABLE t9 to t19;
525 INSERT INTO t19 VALUES(0), (0);
526 SELECT * FROM t19;
527 a
528 1
529 2
530 # restart
531 INSERT INTO t1 VALUES(0), (0);
532 SELECT * FROM t1;
533 a
534 1
535 2
536 INSERT INTO t3 VALUES(0), (0);
537 SELECT * FROM t3;
538 a
539 1
540 2
541 INSERT INTO t5 VALUES(0), (0);
542 SELECT * FROM t5;
543 a
544 1
545 2
546 INSERT INTO t7 VALUES(0), (0);
547 SELECT * FROM t7;
548 a
549 1
550 2
551 INSERT INTO t19 VALUES(0), (0);
552 SELECT * FROM t19;
553 a
554 1
555 2
556 3
557 4
558 DELETE FROM t19 WHERE a = 4;
559 RENAME TABLE t19 to t9;
560 INSERT INTO t9 VALUES(0), (0);
561 SELECT * FROM t9;
562 a
563 1
564 2
565 3
566 5
567 6
568 TRUNCATE TABLE t9;
569 INSERT INTO t9 VALUES(0), (0);
570 SELECT * FROM t9;
571 a
572 1
573 2
574 # Scenario 8: Test ALTER TABLE operations
575 INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
576 SELECT * FROM t3;
577 a
578 1
579 2
580 3
581 4
582 100
583 200
584 1000
585 DELETE FROM t3 WHERE a > 300;
586 SELECT MAX(a) AS `Expect 200` FROM t3;
587 Expect 200
588 200
589 # This will not change the counter to 150, but to 201, which is the next
590 # of current max counter in the table
591 ALTER TABLE t3 AUTO_INCREMENT = 150;
592 SHOW CREATE TABLE t3;
593 Table Create Table
594 t3 CREATE TABLE `t3` (
595 `a` smallint(6) NOT NULL AUTO_INCREMENT,
596 PRIMARY KEY (`a`)
597 ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
598 INSERT INTO t3 VALUES(0);
599 SELECT MAX(a) AS `Expect 201` FROM t3;
600 Expect 201
601 201
602 # This will change the counter to 500, which is bigger than any counter
603 # in the table
604 ALTER TABLE t3 AUTO_INCREMENT = 500;
605 SHOW CREATE TABLE t3;
606 Table Create Table
607 t3 CREATE TABLE `t3` (
608 `a` smallint(6) NOT NULL AUTO_INCREMENT,
609 PRIMARY KEY (`a`)
610 ) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
611 INSERT INTO t3 VALUES(0);
612 SELECT MAX(a) AS `Expect 500` FROM t3;
613 Expect 500
614 500
615 TRUNCATE TABLE t3;
616 ALTER TABLE t3 AUTO_INCREMENT = 100;
617 SHOW CREATE TABLE t3;
618 Table Create Table
619 t3 CREATE TABLE `t3` (
620 `a` smallint(6) NOT NULL AUTO_INCREMENT,
621 PRIMARY KEY (`a`)
622 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
623 INSERT INTO t3 VALUES(0), (0);
624 SELECT * FROM t3;
625 a
626 100
627 101
628 INSERT INTO t3 VALUES(150), (180);
629 UPDATE t3 SET a = 200 WHERE a = 150;
630 INSERT INTO t3 VALUES(220);
631 # This still fails to set to 120, but just 221
632 ALTER TABLE t3 AUTO_INCREMENT = 120;
633 SHOW CREATE TABLE t3;
634 Table Create Table
635 t3 CREATE TABLE `t3` (
636 `a` smallint(6) NOT NULL AUTO_INCREMENT,
637 PRIMARY KEY (`a`)
638 ) ENGINE=InnoDB AUTO_INCREMENT=221 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
639 INSERT INTO t3 VALUES(0);
640 SELECT MAX(a) AS `Expect 221` FROM t3;
641 Expect 221
642 221
643 DELETE FROM t3 WHERE a > 120;
644 ALTER TABLE t3 AUTO_INCREMENT = 120;
645 SHOW CREATE TABLE t3;
646 Table Create Table
647 t3 CREATE TABLE `t3` (
648 `a` smallint(6) NOT NULL AUTO_INCREMENT,
649 PRIMARY KEY (`a`)
650 ) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
651 # MDEV-6076: Test adding an AUTO_INCREMENT COLUMN
652 CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB;
653 INSERT INTO mdev6076a VALUES(2),(1);
654 CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB;
655 INSERT INTO mdev6076b VALUES(2),(1);
656 ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE, LOCK=NONE;
657 ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED
658 ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE;
659 ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100,
660 ALGORITHM=INPLACE;
661 # MDEV-6076: Test root page split and page_create_empty()
662 CREATE TABLE mdev6076empty (b SERIAL, pad CHAR(255) NOT NULL DEFAULT '')
663 ENGINE=InnoDB;
664 BEGIN;
665 # Insert records in descending order of AUTO_INCREMENT,
666 # causing a page split on the very last insert.
667 # Without the fix in btr_page_empty() this would lose the counter value.
668 # Without the fix in page_create_empty() the counter value would be lost
669 # when ROLLBACK deletes the last row.
670 ROLLBACK;
671 # restart
672 INSERT INTO t3 VALUES(0);
673 SELECT MAX(a) AS `Expect 120` FROM t3;
674 Expect 120
675 120
676 INSERT INTO mdev6076a SET b=NULL;
677 SELECT * FROM mdev6076a;
678 a b
679 1 2
680 2 1
681 3 NULL
682 INSERT INTO mdev6076b SET b=NULL;
683 SELECT * FROM mdev6076b;
684 a b
685 100 2
686 101 1
687 102 NULL
688 INSERT INTO mdev6076empty SET b=NULL;
689 SELECT * FROM mdev6076empty;
690 b pad
691 56
692 DROP TABLE mdev6076a, mdev6076b, mdev6076empty;
693 INSERT INTO t3 VALUES(0), (0), (200), (210);
694 # Test the different algorithms in ALTER TABLE
695 CREATE TABLE t_inplace LIKE t3;
696 INSERT INTO t_inplace SELECT * FROM t3;
697 SELECT * FROM t_inplace;
698 a
699 100
700 101
701 120
702 121
703 122
704 200
705 210
706 SHOW CREATE TABLE t_inplace;
707 Table Create Table
708 t_inplace CREATE TABLE `t_inplace` (
709 `a` smallint(6) NOT NULL AUTO_INCREMENT,
710 PRIMARY KEY (`a`)
711 ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
712 # This will keep the autoinc counter
713 ALTER TABLE t_inplace AUTO_INCREMENT = 250, ALGORITHM = INPLACE;
714 # We expect the counter to be 250
715 SHOW CREATE TABLE t_inplace;
716 Table Create Table
717 t_inplace CREATE TABLE `t_inplace` (
718 `a` smallint(6) NOT NULL AUTO_INCREMENT,
719 PRIMARY KEY (`a`)
720 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
721 # This should keep the autoinc counter as well
722 ALTER TABLE t_inplace ADD COLUMN b INT, ALGORITHM = INPLACE;
723 # We expect the counter to be 250
724 SHOW CREATE TABLE t_inplace;
725 Table Create Table
726 t_inplace CREATE TABLE `t_inplace` (
727 `a` smallint(6) NOT NULL AUTO_INCREMENT,
728 `b` int(11) DEFAULT NULL,
729 PRIMARY KEY (`a`)
730 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
731 DELETE FROM t_inplace WHERE a > 150;
732 SELECT * FROM t_inplace;
733 a b
734 100 NULL
735 101 NULL
736 120 NULL
737 121 NULL
738 122 NULL
739 # This should reset the autoinc counter to the one specified
740 # Since it's smaller than current one but bigger than existing
741 # biggest counter in the table
742 ALTER TABLE t_inplace AUTO_INCREMENT = 180, ALGORITHM = INPLACE;
743 # We expect the counter to be 180
744 SHOW CREATE TABLE t_inplace;
745 Table Create Table
746 t_inplace CREATE TABLE `t_inplace` (
747 `a` smallint(6) NOT NULL AUTO_INCREMENT,
748 `b` int(11) DEFAULT NULL,
749 PRIMARY KEY (`a`)
750 ) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
751 # This should reset the autoinc counter to the next value of
752 # current max counter in the table, since the specified value
753 # is smaller than the existing biggest value(50 < 123)
754 ALTER TABLE t_inplace DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = INPLACE;
755 # We expect the counter to be 123
756 SHOW CREATE TABLE t_inplace;
757 Table Create Table
758 t_inplace CREATE TABLE `t_inplace` (
759 `a` smallint(6) NOT NULL AUTO_INCREMENT,
760 PRIMARY KEY (`a`)
761 ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
762 INSERT INTO t_inplace VALUES(0), (0);
763 SELECT MAX(a) AS `Expect 124` FROM t_inplace;
764 Expect 124
765 124
766 OPTIMIZE TABLE t_inplace;
767 Table Op Msg_type Msg_text
768 test.t_inplace optimize note Table does not support optimize, doing recreate + analyze instead
769 test.t_inplace optimize status OK
770 DELETE FROM t_inplace WHERE a >= 123;
771 CREATE TABLE it_inplace(a INT AUTO_INCREMENT, INDEX(a)) AUTO_INCREMENT=125 ENGINE=InnoDB;
772 CREATE UNIQUE INDEX idx_aa ON it_inplace(a);
773 # restart
774 INSERT INTO t_inplace VALUES(0), (0);
775 INSERT INTO it_inplace VALUES(0), (0);
776 SELECT MAX(a) AS `Expect 126` FROM t_inplace;
777 Expect 126
778 126
779 SELECT MAX(a) AS `Expect 126` FROM it_inplace;
780 Expect 126
781 126
782 DROP TABLE t_inplace, it_inplace;
783 CREATE TABLE t_copy LIKE t3;
784 INSERT INTO t_copy SELECT * FROM t3;
785 SELECT * FROM t_copy;
786 a
787 100
788 101
789 120
790 121
791 122
792 200
793 210
794 SHOW CREATE TABLE t_copy;
795 Table Create Table
796 t_copy CREATE TABLE `t_copy` (
797 `a` smallint(6) NOT NULL AUTO_INCREMENT,
798 PRIMARY KEY (`a`)
799 ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
800 # This will keep the autoinc counter
801 ALTER TABLE t_copy AUTO_INCREMENT = 250, ALGORITHM = COPY;
802 # We expect the counter to be 250
803 SHOW CREATE TABLE t_copy;
804 Table Create Table
805 t_copy CREATE TABLE `t_copy` (
806 `a` smallint(6) NOT NULL AUTO_INCREMENT,
807 PRIMARY KEY (`a`)
808 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
809 # This should keep the autoinc counter as well
810 ALTER TABLE t_copy ADD COLUMN b INT, ALGORITHM = COPY;
811 # We expect the counter to be 250
812 SHOW CREATE TABLE t_copy;
813 Table Create Table
814 t_copy CREATE TABLE `t_copy` (
815 `a` smallint(6) NOT NULL AUTO_INCREMENT,
816 `b` int(11) DEFAULT NULL,
817 PRIMARY KEY (`a`)
818 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
819 DELETE FROM t_copy WHERE a > 150;
820 SELECT * FROM t_copy;
821 a b
822 100 NULL
823 101 NULL
824 120 NULL
825 121 NULL
826 122 NULL
827 # This should reset the autoinc counter to the one specified
828 # Since it's smaller than current one but bigger than existing
829 # biggest counter in the table
830 ALTER TABLE t_copy AUTO_INCREMENT = 180, ALGORITHM = COPY;
831 # We expect the counter to be 180
832 SHOW CREATE TABLE t_copy;
833 Table Create Table
834 t_copy CREATE TABLE `t_copy` (
835 `a` smallint(6) NOT NULL AUTO_INCREMENT,
836 `b` int(11) DEFAULT NULL,
837 PRIMARY KEY (`a`)
838 ) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
839 # This should reset the autoinc counter to the next value of
840 # current max counter in the table, since the specified value
841 # is smaller than the existing biggest value(50 < 123)
842 ALTER TABLE t_copy DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = COPY;
843 # We expect the counter to be 123
844 SHOW CREATE TABLE t_copy;
845 Table Create Table
846 t_copy CREATE TABLE `t_copy` (
847 `a` smallint(6) NOT NULL AUTO_INCREMENT,
848 PRIMARY KEY (`a`)
849 ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
850 INSERT INTO t_copy VALUES(0), (0);
851 SELECT MAX(a) AS `Expect 124` FROM t_copy;
852 Expect 124
853 124
854 OPTIMIZE TABLE t_copy;
855 Table Op Msg_type Msg_text
856 test.t_copy optimize note Table does not support optimize, doing recreate + analyze instead
857 test.t_copy optimize status OK
858 DELETE FROM t_copy WHERE a >= 123;
859 CREATE TABLE it_copy(a INT AUTO_INCREMENT, INDEX(a)) AUTO_INCREMENT=125 ENGINE=InnoDB;
860 CREATE UNIQUE INDEX idx_aa ON it_copy(a);
861 # restart
862 INSERT INTO t_copy VALUES(0), (0);
863 INSERT INTO it_copy VALUES(0), (0);
864 SELECT MAX(a) AS `Expect 126` FROM t_copy;
865 Expect 126
866 126
867 SELECT MAX(a) AS `Expect 126` FROM it_copy;
868 Expect 126
869 126
870 DROP TABLE t_copy, it_copy;
871 # Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
872 CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, key(b)) ENGINE = InnoDB;
873 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
874 INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
875 INSERT INTO t30(b) VALUES(4), (5), (6), (7);
876 SELECT * FROM t30 ORDER BY b;
877 a b
878 1 1
879 200 2
880 0 3
881 201 4
882 202 5
883 203 6
884 204 7
885 ALTER TABLE t30 MODIFY b MEDIUMINT;
886 SELECT * FROM t30 ORDER BY b;
887 a b
888 1 1
889 200 2
890 0 3
891 201 4
892 202 5
893 203 6
894 204 7
895 # Ensure that all changes before the server is killed are persisted.
896 set global innodb_flush_log_at_trx_commit=1;
897 CREATE TABLE t31 (a INT) ENGINE = InnoDB;
898 INSERT INTO t31 VALUES(1), (2);
899 ALTER TABLE t31 ADD b INT AUTO_INCREMENT PRIMARY KEY;
900 INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
901 INSERT INTO t31 VALUES(6, 0);
902 ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
903 SELECT * FROM t31;
904 a b
905 3 0
906 1 1
907 2 2
908 4 3
909 5 4
910 SET SQL_MODE = 0;
911 # Scenario 10: Rollback would not rollback the counter
912 CREATE TABLE t32 (a BIGINT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
913 INSERT INTO t32 VALUES(0), (0);
914 # Ensure that all changes before the server is killed are persisted.
915 set global innodb_flush_log_at_trx_commit=1;
916 START TRANSACTION;
917 INSERT INTO t32 VALUES(0), (0);
918 SELECT MAX(a) AS `Expect 4` FROM t32;
919 Expect 4
920 4
921 DELETE FROM t32 WHERE a >= 2;
922 ROLLBACK;
923 # Scenario 11: Test duplicate primary key/secondary key will not stop
924 # increasing the counter
925 CREATE TABLE t33 (
926 a BIGINT NOT NULL PRIMARY KEY,
927 b BIGINT NOT NULL AUTO_INCREMENT, KEY(b)) ENGINE = InnoDB;
928 INSERT INTO t33 VALUES(1, NULL);
929 INSERT INTO t33 VALUES(2, NULL);
930 INSERT INTO t33 VALUES(2, NULL);
931 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
932 INSERT INTO t33 VALUES(3, NULL);
933 SELECT MAX(b) AS `Expect 4` FROM t33;
934 Expect 4
935 4
936 TRUNCATE TABLE t33;
937 INSERT INTO t33 VALUES(1, NULL);
938 INSERT INTO t33 VALUES(2, NULL);
939 set global innodb_flush_log_at_trx_commit=1;
940 START TRANSACTION;
941 UPDATE t33 SET a = 10 WHERE a = 1;
942 INSERT INTO t33 VALUES(2, NULL);
943 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
944 COMMIT;
945 # restart
946 # This will not insert 0
947 INSERT INTO t31(a) VALUES(6), (0);
948 SELECT * FROM t31;
949 a b
950 3 0
951 1 1
952 2 2
953 4 3
954 5 4
955 6 5
956 0 6
957 DROP TABLE t31;
958 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
959 DELETE FROM t30 WHERE a = 0;
960 UPDATE t30 set a = 0 where b = 5;
961 SELECT * FROM t30 ORDER BY b;
962 a b
963 1 1
964 200 2
965 201 4
966 0 5
967 203 6
968 204 7
969 DELETE FROM t30 WHERE a = 0;
970 UPDATE t30 SET a = NULL WHERE b = 6;
971 Warnings:
972 Warning 1048 Column 'a' cannot be null
973 UPDATE t30 SET a = 300 WHERE b = 7;
974 SELECT * FROM t30 ORDER BY b;
975 a b
976 1 1
977 200 2
978 201 4
979 0 6
980 300 7
981 SET SQL_MODE = 0;
982 SELECT MAX(a) AS `Expect 2` FROM t32;
983 Expect 2
984 2
985 INSERT INTO t32 VALUES(0), (0);
986 SELECT MAX(a) AS `Expect 6` FROM t32;
987 Expect 6
988 6
989 FLUSH TABLES t33 FOR EXPORT;
990 backup: t33
991 UNLOCK TABLES;
992 DROP TABLE t33;
993 CREATE TABLE t33 (
994 a BIGINT NOT NULL PRIMARY KEY,
995 b BIGINT NOT NULL AUTO_INCREMENT, KEY(b)) ENGINE = InnoDB;
996 ALTER TABLE t33 DISCARD TABLESPACE;
997 restore: t33 .ibd and .cfg files
998 ALTER TABLE t33 IMPORT TABLESPACE;
999 INSERT INTO t33 VALUES(3, NULL);
1000 SELECT MAX(b) AS `Expect 4` FROM t33;
1001 Expect 4
1002 4
1003 SELECT * FROM t33;
1004 a b
1005 10 1
1006 2 2
1007 3 4
1008 DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t11u, t12u,
1009 t30, t32, t33;
1010 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;