Debian Codegraph @

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

raw
1 # This test case is introduced to test the persisted autoinc, basic
2 # autoinc features are not the main part of this one.
3 # Pre-create several tables
4 CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
5 INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
6 (20), (30), (31);
7 SELECT * FROM t1;
8 a
9 -10
10 -1
11 1
12 2
13 3
14 4
15 5
16 20
17 30
18 31
19 CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
20 INSERT INTO t2 VALUES(-5);
21 ERROR 22003: Out of range value for column 'a' at row 1
22 INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
23 (20), (30), (31);
24 SELECT * FROM t2;
25 a
26 1
27 2
28 3
29 4
30 8
31 10
32 11
33 20
34 30
35 31
36 CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
37 INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
38 (20), (30), (31), (1024), (4096);
39 SELECT * FROM t3;
40 a
41 -10
42 -1
43 1
44 2
45 3
46 4
47 5
48 20
49 30
50 31
51 1024
52 4096
53 CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
54 INSERT INTO t4 VALUES(-5);
55 ERROR 22003: Out of range value for column 'a' at row 1
56 INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
57 (20), (30), (31), (1024), (4096);
58 SELECT * FROM t4;
59 a
60 1
61 2
62 3
63 4
64 8
65 10
66 11
67 20
68 30
69 31
70 1024
71 4096
72 CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
73 INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
74 (20), (30), (31), (1000000), (1000005);
75 SELECT * FROM t5;
76 a
77 -10
78 -1
79 1
80 2
81 3
82 4
83 5
84 20
85 30
86 31
87 1000000
88 1000005
89 CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
90 INSERT INTO t6 VALUES(-5);
91 ERROR 22003: Out of range value for column 'a' at row 1
92 INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
93 (20), (30), (31), (1000000), (1000005);
94 SELECT * FROM t6;
95 a
96 1
97 2
98 3
99 4
100 8
101 10
102 11
103 20
104 30
105 31
106 1000000
107 1000005
108 CREATE TABLE t7(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB;
109 INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
110 (20), (30), (31), (100000000), (100000008);
111 SELECT * FROM t7;
112 a
113 -10
114 -1
115 1
116 2
117 3
118 4
119 5
120 20
121 30
122 31
123 100000000
124 100000008
125 CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
126 INSERT INTO t8 VALUES(-5);
127 ERROR 22003: Out of range value for column 'a' at row 1
128 INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
129 (20), (30), (31), (100000000), (100000008);
130 SELECT * FROM t8;
131 a
132 1
133 2
134 3
135 4
136 8
137 10
138 11
139 20
140 30
141 31
142 100000000
143 100000008
144 CREATE TABLE t9(a BIGINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
145 INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
146 (20), (30), (31), (100000000000), (100000000006);
147 SELECT * FROM t9;
148 a
149 -10
150 -1
151 1
152 2
153 3
154 4
155 5
156 20
157 30
158 31
159 100000000000
160 100000000006
161 CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
162 INSERT INTO t10 VALUES(-5);
163 ERROR 22003: Out of range value for column 'a' at row 1
164 INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
165 (20), (30), (31), (100000000000), (100000000006);
166 SELECT * FROM t10;
167 a
168 1
169 2
170 3
171 4
172 8
173 10
174 11
175 20
176 30
177 31
178 100000000000
179 100000000006
180 CREATE TABLE t11(a FLOAT AUTO_INCREMENT KEY) ENGINE = InnoDB;
181 Warnings:
182 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'a'.
183 INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0),
184 (20), (30), (31);
185 SELECT * FROM t11;
186 a
187 -10
188 -1
189 1
190 2
191 3
192 4
193 5
194 20
195 30
196 31
197 set global innodb_flush_log_at_trx_commit=1;
198 CREATE TABLE t12(a DOUBLE AUTO_INCREMENT KEY) ENGINE = InnoDB;
199 Warnings:
200 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'a'.
201 INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0),
202 (20), (30), (31);
203 SELECT * FROM t12;
204 a
205 -10
206 -1
207 1
208 2
209 3
210 4
211 5
212 20
213 30
214 31
215 CREATE TABLE t13(a INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB,
216 AUTO_INCREMENT = 1234;
217 # Scenario 1: Normal restart, to test if the counters are persisted
218 # restart
219 # We expect these results should be equal to above SELECTs
220 SELECT * FROM t1;
221 a
222 -10
223 -1
224 1
225 2
226 3
227 4
228 5
229 20
230 30
231 31
232 SELECT * FROM t2;
233 a
234 1
235 2
236 3
237 4
238 8
239 10
240 11
241 20
242 30
243 31
244 SELECT * FROM t3;
245 a
246 -10
247 -1
248 1
249 2
250 3
251 4
252 5
253 20
254 30
255 31
256 1024
257 4096
258 SELECT * FROM t4;
259 a
260 1
261 2
262 3
263 4
264 8
265 10
266 11
267 20
268 30
269 31
270 1024
271 4096
272 SELECT * FROM t5;
273 a
274 -10
275 -1
276 1
277 2
278 3
279 4
280 5
281 20
282 30
283 31
284 1000000
285 1000005
286 SELECT * FROM t6;
287 a
288 1
289 2
290 3
291 4
292 8
293 10
294 11
295 20
296 30
297 31
298 1000000
299 1000005
300 SELECT * FROM t7;
301 a
302 -10
303 -1
304 1
305 2
306 3
307 4
308 5
309 20
310 30
311 31
312 100000000
313 100000008
314 SELECT * FROM t8;
315 a
316 1
317 2
318 3
319 4
320 8
321 10
322 11
323 20
324 30
325 31
326 100000000
327 100000008
328 SELECT * FROM t9;
329 a
330 -10
331 -1
332 1
333 2
334 3
335 4
336 5
337 20
338 30
339 31
340 100000000000
341 100000000006
342 SELECT * FROM t10;
343 a
344 1
345 2
346 3
347 4
348 8
349 10
350 11
351 20
352 30
353 31
354 100000000000
355 100000000006
356 SELECT * FROM t11;
357 a
358 -10
359 -1
360 1
361 2
362 3
363 4
364 5
365 20
366 30
367 31
368 SELECT * FROM t12;
369 a
370 -10
371 -1
372 1
373 2
374 3
375 4
376 5
377 20
378 30
379 31
380 SELECT * FROM t13;
381 a
382 SHOW CREATE TABLE t13;
383 Table Create Table
384 t13 CREATE TABLE `t13` (
385 `a` int NOT NULL AUTO_INCREMENT,
386 PRIMARY KEY (`a`)
387 ) ENGINE=InnoDB AUTO_INCREMENT=1234 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
388 INSERT INTO t13 VALUES(0);
389 SELECT a AS `Expect 1234` FROM t13;
390 Expect 1234
391 1234
392 # Scenario 2: Delete some values, to test the counters should not be the
393 # one which is the largest in current table
394 set global innodb_flush_log_at_trx_commit=1;
395 DELETE FROM t1 WHERE a > 30;
396 SELECT MAX(a) AS `Expect 30` FROM t1;
397 Expect 30
398 30
399 DELETE FROM t3 WHERE a > 2000;
400 SELECT MAX(a) AS `Expect 2000` FROM t3;
401 Expect 2000
402 1024
403 DELETE FROM t5 WHERE a > 1000000;
404 SELECT MAX(a) AS `Expect 1000000` FROM t5;
405 Expect 1000000
406 1000000
407 DELETE FROM t7 WHERE a > 100000000;
408 SELECT MAX(a) AS `Expect 100000000` FROM t7;
409 Expect 100000000
410 100000000
411 DELETE FROM t9 WHERE a > 100000000000;
412 SELECT MAX(a) AS `Expect 100000000000` FROM t9;
413 Expect 100000000000
414 100000000000
415 # restart
416 INSERT INTO t1 VALUES(0), (0);
417 SELECT MAX(a) AS `Expect 33` FROM t1;
418 Expect 33
419 33
420 INSERT INTO t3 VALUES(0), (0);
421 SELECT MAX(a) AS `Expect 4098` FROM t3;
422 Expect 4098
423 4098
424 INSERT INTO t5 VALUES(0), (0);
425 SELECT MAX(a) AS `Expect 1000007` FROM t5;
426 Expect 1000007
427 1000007
428 INSERT INTO t7 VALUES(0), (0);
429 SELECT MAX(a) AS `Expect 100000010` FROM t7;
430 Expect 100000010
431 100000010
432 INSERT INTO t9 VALUES(0), (0);
433 SELECT MAX(a) AS `Expect 100000000008` FROM t9;
434 Expect 100000000008
435 100000000008
436 # Scenario 3: Insert some bigger counters, the next counter should start
437 # from there
438 INSERT INTO t1 VALUES(40), (0);
439 INSERT INTO t1 VALUES(42), (0);
440 SELECT a AS `Expect 43, 42` FROM t1 ORDER BY a DESC LIMIT 4;
441 Expect 43, 42
442 43
443 42
444 41
445 40
446 INSERT INTO t3 VALUES(5000), (0);
447 INSERT INTO t3 VALUES(5010), (0);
448 SELECT a AS `Expect 5011, 5010` FROM t3 ORDER BY a DESC LIMIT 4;
449 Expect 5011, 5010
450 5011
451 5010
452 5001
453 5000
454 INSERT INTO t5 VALUES(1000010), (0);
455 INSERT INTO t5 VALUES(1000020), (0);
456 SELECT a AS `Expect 1000021, 1000020` FROM t5 ORDER BY a DESC LIMIT 4;
457 Expect 1000021, 1000020
458 1000021
459 1000020
460 1000011
461 1000010
462 INSERT INTO t7 VALUES(100000020), (0);
463 INSERT INTO t7 VALUES(100000030), (0);
464 SELECT a AS `Expect 100000031, 100000030` FROM t7 ORDER BY a DESC LIMIT 4;
465 Expect 100000031, 100000030
466 100000031
467 100000030
468 100000021
469 100000020
470 INSERT INTO t9 VALUES(100000000010), (0);
471 INSERT INTO t9 VALUES(100000000020), (0);
472 SELECT a AS `Expect 100000000021, 100000000020` FROM t9 ORDER BY a DESC LIMIT 4;
473 Expect 100000000021, 100000000020
474 100000000021
475 100000000020
476 100000000011
477 100000000010
478 # Scenario 4: Update some values, to test the counters should be updated
479 # to the bigger value, but not smaller value.
480 INSERT INTO t1 VALUES(50), (55);
481 UPDATE t1 SET a = 105 WHERE a = 5;
482 UPDATE t1 SET a = 100 WHERE a = 55;
483 # This should insert 102, 106, 107, and make next counter 109.
484 INSERT INTO t1 VALUES(102), (0), (0);
485 SELECT a AS `Expect 107, 106` FROM t1 ORDER BY a DESC LIMIT 2;
486 Expect 107, 106
487 107
488 106
489 DELETE FROM t1 WHERE a > 105;
490 INSERT INTO t1 VALUES(0);
491 SELECT MAX(a) AS `Expect 109` FROM t1;
492 Expect 109
493 109
494 # Test the same things on t3, t5, t7, t9, to test if DDTableBuffer would
495 # be updated accordingly
496 INSERT INTO t3 VALUES(60), (65);
497 UPDATE t3 SET a = 6005 WHERE a = 5;
498 UPDATE t3 SET a = 6000 WHERE a = 60;
499 # This should insert 6002, 6006, 6007, and make next counter 6009.
500 INSERT INTO t3 VALUES(6002), (0), (0);
501 SELECT a AS `Expect 6007, 6006` FROM t3 ORDER BY a DESC LIMIT 2;
502 Expect 6007, 6006
503 6007
504 6006
505 DELETE FROM t3 WHERE a > 6005;
506 INSERT INTO t3 VALUES(0);
507 SELECT MAX(a) AS `Expect 6009` FROM t3;
508 Expect 6009
509 6009
510 INSERT INTO t5 VALUES(100), (200);
511 UPDATE t5 SET a = 1000105 WHERE a = 5;
512 UPDATE t5 SET a = 1000100 WHERE a = 100;
513 # This should insert 1000102, 1000106, 1000107, and make next counter
514 # 1000109.
515 INSERT INTO t5 VALUES(1000102), (0), (0);
516 SELECT a AS `Expect 1000107, 1000106` FROM t5 ORDER BY a DESC LIMIT 2;
517 Expect 1000107, 1000106
518 1000107
519 1000106
520 DELETE FROM t5 WHERE a > 1000105;
521 INSERT INTO t5 VALUES(0);
522 SELECT MAX(a) AS `Expect 1000109` FROM t5;
523 Expect 1000109
524 1000109
525 INSERT INTO t7 VALUES(100), (200);
526 UPDATE t7 SET a = 100000105 WHERE a = 5;
527 UPDATE t7 SET a = 100000100 WHERE a = 100;
528 # This should insert 100000102, 1100000106, 100000107, and make next
529 # counter 100000109.
530 INSERT INTO t7 VALUES(100000102), (0), (0);
531 SELECT a AS `Expect 100000107, 100000106` FROM t7 ORDER BY a DESC LIMIT 2;
532 Expect 100000107, 100000106
533 100000107
534 100000106
535 DELETE FROM t7 WHERE a > 100000105;
536 INSERT INTO t7 VALUES(0);
537 SELECT MAX(a) AS `Expect 100000109` FROM t7;
538 Expect 100000109
539 100000109
540 set global innodb_flush_log_at_trx_commit=1;
541 INSERT INTO t9 VALUES(100), (200);
542 UPDATE t9 SET a = 100000000105 WHERE a = 5;
543 UPDATE t9 SET a = 100000000100 WHERE a = 100;
544 # This should insert 100000000102, 100000000106, 100000000107, and make
545 # next counter 100000000109.
546 INSERT INTO t9 VALUES(100000000102), (0), (0);
547 SELECT a AS `Expect 100000000107, 100000000106` FROM t9 ORDER BY a DESC LIMIT 2;
548 Expect 100000000107, 100000000106
549 100000000107
550 100000000106
551 DELETE FROM t9 WHERE a > 100000000105;
552 INSERT INTO t9 VALUES(0);
553 SELECT MAX(a) AS `Expect 100000000109` FROM t9;
554 Expect 100000000109
555 100000000109
556 # restart
557 INSERT INTO t1 VALUES(0), (0);
558 SELECT a AS `Expect 110, 111` FROM t1 ORDER BY a DESC LIMIT 2;
559 Expect 110, 111
560 111
561 110
562 INSERT INTO t3 VALUES(0), (0);
563 SELECT a AS `Expect 6010, 6011` FROM t3 ORDER BY a DESC LIMIT 2;
564 Expect 6010, 6011
565 6011
566 6010
567 INSERT INTO t5 VALUES(0), (0);
568 SELECT a AS `Expect 1100111, 1100110` FROM t5 ORDER BY a DESC LIMIT 2;
569 Expect 1100111, 1100110
570 1000111
571 1000110
572 INSERT INTO t7 VALUES(0), (0);
573 SELECT a AS `Expect 100000111, 100000110` FROM t7 ORDER BY a DESC LIMIT 2;
574 Expect 100000111, 100000110
575 100000111
576 100000110
577 INSERT INTO t9 VALUES(0), (0);
578 SELECT a AS `Expect 100000000111, 100000000110` FROM t9 ORDER BY a DESC LIMIT 2;
579 Expect 100000000111, 100000000110
580 100000000111
581 100000000110
582 # Scenario 5: Test kill the server
583 INSERT INTO t1 VALUES(125);
584 DELETE FROM t1 WHERE a = 125;
585 INSERT INTO t3 VALUES(6100);
586 DELETE FROM t3 WHERE a = 6100;
587 INSERT INTO t5 VALUES(1100200);
588 DELETE FROM t5 WHERE a = 1100200;
589 INSERT INTO t7 VALUES(100000200);
590 DELETE FROM t7 WHERE a = 100000200;
591 set global innodb_flush_log_at_trx_commit=1;
592 INSERT INTO t9 VALUES(100000000200);
593 DELETE FROM t9 WHERE a = 100000000200;
594 # Kill and restart
595 INSERT INTO t1 VALUES(0);
596 SELECT a AS `Expect 126` FROM t1 ORDER BY a DESC LIMIT 1;
597 Expect 126
598 126
599 INSERT INTO t3 VALUES(0);
600 SELECT a AS `Expect 6101` FROM t3 ORDER BY a DESC LIMIT 1;
601 Expect 6101
602 6101
603 INSERT INTO t5 VALUES(0);
604 SELECT a AS `Expect 1100201` FROM t5 ORDER BY a DESC LIMIT 1;
605 Expect 1100201
606 1100201
607 INSERT INTO t7 VALUES(0);
608 SELECT a AS `Expect 100000201` FROM t7 ORDER BY a DESC LIMIT 1;
609 Expect 100000201
610 100000201
611 INSERT INTO t9 VALUES(0);
612 SELECT a AS `Expect 100000000201` FROM t9 ORDER BY a DESC LIMIT 1;
613 Expect 100000000201
614 100000000201
615 # Scenario 6: Test truncate will reset the counters to 0
616 TRUNCATE TABLE t1;
617 TRUNCATE TABLE t3;
618 TRUNCATE TABLE t5;
619 TRUNCATE TABLE t7;
620 TRUNCATE TABLE t9;
621 INSERT INTO t1 VALUES(0), (0);
622 SELECT * FROM t1;
623 a
624 1
625 2
626 INSERT INTO t3 VALUES(0), (0);
627 SELECT * FROM t3;
628 a
629 1
630 2
631 INSERT INTO t5 VALUES(0), (0);
632 SELECT * FROM t5;
633 a
634 1
635 2
636 INSERT INTO t7 VALUES(0), (0);
637 SELECT * FROM t7;
638 a
639 1
640 2
641 INSERT INTO t9 VALUES(0), (0);
642 SELECT * FROM t9;
643 a
644 1
645 2
646 set global innodb_flush_log_at_trx_commit=1;
647 TRUNCATE TABLE t1;
648 TRUNCATE TABLE t3;
649 TRUNCATE TABLE t5;
650 TRUNCATE TABLE t7;
651 TRUNCATE TABLE t9;
652 # Kill and restart
653 INSERT INTO t1 VALUES(0), (0);
654 SELECT * FROM t1;
655 a
656 1
657 2
658 INSERT INTO t3 VALUES(0), (0);
659 SELECT * FROM t3;
660 a
661 1
662 2
663 INSERT INTO t5 VALUES(0), (0);
664 SELECT * FROM t5;
665 a
666 1
667 2
668 INSERT INTO t7 VALUES(0), (0);
669 SELECT * FROM t7;
670 a
671 1
672 2
673 INSERT INTO t9 VALUES(0), (0);
674 SELECT * FROM t9;
675 a
676 1
677 2
678 # Scenario 7: Test explicit rename table won't change the counter
679 set global innodb_flush_log_at_trx_commit=1;
680 RENAME TABLE t9 to t19;
681 INSERT INTO t19 VALUES(0), (0);
682 SELECT * FROM t19;
683 a
684 1
685 2
686 3
687 4
688 DELETE FROM t19 WHERE a = 4;
689 # Kill and restart
690 RENAME TABLE t19 to t9;
691 INSERT INTO t9 VALUES(0), (0);
692 SELECT * FROM t9;
693 a
694 1
695 2
696 3
697 5
698 6
699 TRUNCATE TABLE t9;
700 INSERT INTO t9 VALUES(0), (0);
701 SELECT * FROM t9;
702 a
703 1
704 2
705 # Scenario 8: Test ALTER TABLE operations
706 INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
707 SELECT * FROM t3;
708 a
709 1
710 2
711 3
712 4
713 100
714 200
715 1000
716 DELETE FROM t3 WHERE a > 300;
717 SELECT MAX(a) AS `Expect 200` FROM t3;
718 Expect 200
719 200
720 # This will not change the counter to 150, but to 201, which is the next
721 # of current max counter in the table
722 ALTER TABLE t3 AUTO_INCREMENT = 150;
723 SHOW CREATE TABLE t3;
724 Table Create Table
725 t3 CREATE TABLE `t3` (
726 `a` smallint NOT NULL AUTO_INCREMENT,
727 PRIMARY KEY (`a`)
728 ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
729 INSERT INTO t3 VALUES(0);
730 SELECT MAX(a) AS `Expect 201` FROM t3;
731 Expect 201
732 201
733 # This will change the counter to 500, which is bigger than any counter
734 # in the table
735 ALTER TABLE t3 AUTO_INCREMENT = 500;
736 SHOW CREATE TABLE t3;
737 Table Create Table
738 t3 CREATE TABLE `t3` (
739 `a` smallint NOT NULL AUTO_INCREMENT,
740 PRIMARY KEY (`a`)
741 ) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
742 INSERT INTO t3 VALUES(0);
743 SELECT MAX(a) AS `Expect 500` FROM t3;
744 Expect 500
745 500
746 TRUNCATE TABLE t3;
747 ALTER TABLE t3 AUTO_INCREMENT = 100;
748 SHOW CREATE TABLE t3;
749 Table Create Table
750 t3 CREATE TABLE `t3` (
751 `a` smallint NOT NULL AUTO_INCREMENT,
752 PRIMARY KEY (`a`)
753 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
754 INSERT INTO t3 VALUES(0), (0);
755 SELECT * FROM t3;
756 a
757 100
758 101
759 INSERT INTO t3 VALUES(150), (180);
760 UPDATE t3 SET a = 200 WHERE a = 150;
761 INSERT INTO t3 VALUES(220);
762 # This still fails to set to 120, but just 221
763 ALTER TABLE t3 AUTO_INCREMENT = 120;
764 SHOW CREATE TABLE t3;
765 Table Create Table
766 t3 CREATE TABLE `t3` (
767 `a` smallint NOT NULL AUTO_INCREMENT,
768 PRIMARY KEY (`a`)
769 ) ENGINE=InnoDB AUTO_INCREMENT=221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
770 INSERT INTO t3 VALUES(0);
771 SELECT MAX(a) AS `Expect 221` FROM t3;
772 Expect 221
773 221
774 DELETE FROM t3 WHERE a > 120;
775 ALTER TABLE t3 AUTO_INCREMENT = 120;
776 SHOW CREATE TABLE t3;
777 Table Create Table
778 t3 CREATE TABLE `t3` (
779 `a` smallint NOT NULL AUTO_INCREMENT,
780 PRIMARY KEY (`a`)
781 ) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
782 # Kill and restart
783 INSERT INTO t3 VALUES(0);
784 SELECT MAX(a) AS `Expect 120` FROM t3;
785 Expect 120
786 120
787 set global innodb_flush_log_at_trx_commit=1;
788 INSERT INTO t3 VALUES(0), (0), (200), (210);
789 # Test the different algorithms in ALTER TABLE
790 CREATE TABLE t_inplace LIKE t3;
791 INSERT INTO t_inplace SELECT * FROM t3;
792 SELECT * FROM t_inplace;
793 a
794 100
795 101
796 120
797 121
798 122
799 200
800 210
801 SHOW CREATE TABLE t_inplace;
802 Table Create Table
803 t_inplace CREATE TABLE `t_inplace` (
804 `a` smallint NOT NULL AUTO_INCREMENT,
805 PRIMARY KEY (`a`)
806 ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
807 # This will keep the autoinc counter
808 ALTER TABLE t_inplace AUTO_INCREMENT = 250, ALGORITHM = INPLACE;
809 # We expect the counter to be 250
810 SHOW CREATE TABLE t_inplace;
811 Table Create Table
812 t_inplace CREATE TABLE `t_inplace` (
813 `a` smallint NOT NULL AUTO_INCREMENT,
814 PRIMARY KEY (`a`)
815 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
816 # This should keep the autoinc counter as well
817 ALTER TABLE t_inplace ADD COLUMN b INT, ALGORITHM = INPLACE;
818 # We expect the counter to be 250
819 SHOW CREATE TABLE t_inplace;
820 Table Create Table
821 t_inplace CREATE TABLE `t_inplace` (
822 `a` smallint NOT NULL AUTO_INCREMENT,
823 `b` int DEFAULT NULL,
824 PRIMARY KEY (`a`)
825 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
826 DELETE FROM t_inplace WHERE a > 150;
827 SELECT * FROM t_inplace;
828 a b
829 100 NULL
830 101 NULL
831 120 NULL
832 121 NULL
833 122 NULL
834 # This should reset the autoinc counter to the one specified
835 # Since it's smaller than current one but bigger than existing
836 # biggest counter in the table
837 ALTER TABLE t_inplace AUTO_INCREMENT = 180, ALGORITHM = INPLACE;
838 # We expect the counter to be 180
839 SHOW CREATE TABLE t_inplace;
840 Table Create Table
841 t_inplace CREATE TABLE `t_inplace` (
842 `a` smallint NOT NULL AUTO_INCREMENT,
843 `b` int DEFAULT NULL,
844 PRIMARY KEY (`a`)
845 ) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
846 # This should reset the autoinc counter to the next value of
847 # current max counter in the table, since the specified value
848 # is smaller than the existing biggest value(50 < 123)
849 ALTER TABLE t_inplace DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = INPLACE;
850 # We expect the counter to be 123
851 SHOW CREATE TABLE t_inplace;
852 Table Create Table
853 t_inplace CREATE TABLE `t_inplace` (
854 `a` smallint NOT NULL AUTO_INCREMENT,
855 PRIMARY KEY (`a`)
856 ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
857 INSERT INTO t_inplace VALUES(0), (0);
858 SELECT MAX(a) AS `Expect 124` FROM t_inplace;
859 Expect 124
860 124
861 OPTIMIZE TABLE t_inplace;
862 Table Op Msg_type Msg_text
863 test.t_inplace optimize note Table does not support optimize, doing recreate + analyze instead
864 test.t_inplace optimize status OK
865 SHOW CREATE TABLE t_inplace;
866 Table Create Table
867 t_inplace CREATE TABLE `t_inplace` (
868 `a` smallint NOT NULL AUTO_INCREMENT,
869 PRIMARY KEY (`a`)
870 ) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
871 # restart
872 # We expect the counter to still be 125
873 SHOW CREATE TABLE t_inplace;
874 Table Create Table
875 t_inplace CREATE TABLE `t_inplace` (
876 `a` smallint NOT NULL AUTO_INCREMENT,
877 PRIMARY KEY (`a`)
878 ) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
879 DELETE FROM t_inplace WHERE a >= 123;
880 CREATE UNIQUE INDEX idx_aa ON t_inplace(a);
881 # restart
882 INSERT INTO t_inplace VALUES(0), (0);
883 SELECT MAX(a) AS `Expect 126` FROM t_inplace;
884 Expect 126
885 126
886 DROP TABLE t_inplace;
887 CREATE TABLE t_copy LIKE t3;
888 INSERT INTO t_copy SELECT * FROM t3;
889 SELECT * FROM t_copy;
890 a
891 100
892 101
893 120
894 121
895 122
896 200
897 210
898 SHOW CREATE TABLE t_copy;
899 Table Create Table
900 t_copy CREATE TABLE `t_copy` (
901 `a` smallint NOT NULL AUTO_INCREMENT,
902 PRIMARY KEY (`a`)
903 ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
904 # This will keep the autoinc counter
905 ALTER TABLE t_copy AUTO_INCREMENT = 250, ALGORITHM = COPY;
906 # We expect the counter to be 250
907 SHOW CREATE TABLE t_copy;
908 Table Create Table
909 t_copy CREATE TABLE `t_copy` (
910 `a` smallint NOT NULL AUTO_INCREMENT,
911 PRIMARY KEY (`a`)
912 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
913 # This should keep the autoinc counter as well
914 ALTER TABLE t_copy ADD COLUMN b INT, ALGORITHM = COPY;
915 # We expect the counter to be 250
916 SHOW CREATE TABLE t_copy;
917 Table Create Table
918 t_copy CREATE TABLE `t_copy` (
919 `a` smallint NOT NULL AUTO_INCREMENT,
920 `b` int DEFAULT NULL,
921 PRIMARY KEY (`a`)
922 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
923 DELETE FROM t_copy WHERE a > 150;
924 SELECT * FROM t_copy;
925 a b
926 100 NULL
927 101 NULL
928 120 NULL
929 121 NULL
930 122 NULL
931 # This should reset the autoinc counter to the one specified
932 # Since it's smaller than current one but bigger than existing
933 # biggest counter in the table
934 ALTER TABLE t_copy AUTO_INCREMENT = 180, ALGORITHM = COPY;
935 # We expect the counter to be 180
936 SHOW CREATE TABLE t_copy;
937 Table Create Table
938 t_copy CREATE TABLE `t_copy` (
939 `a` smallint NOT NULL AUTO_INCREMENT,
940 `b` int DEFAULT NULL,
941 PRIMARY KEY (`a`)
942 ) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
943 # This should reset the autoinc counter to the next value of
944 # current max counter in the table, since the specified value
945 # is smaller than the existing biggest value(50 < 123)
946 ALTER TABLE t_copy DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = COPY;
947 # We expect the counter to be 123
948 SHOW CREATE TABLE t_copy;
949 Table Create Table
950 t_copy CREATE TABLE `t_copy` (
951 `a` smallint NOT NULL AUTO_INCREMENT,
952 PRIMARY KEY (`a`)
953 ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
954 INSERT INTO t_copy VALUES(0), (0);
955 SELECT MAX(a) AS `Expect 124` FROM t_copy;
956 Expect 124
957 124
958 OPTIMIZE TABLE t_copy;
959 Table Op Msg_type Msg_text
960 test.t_copy optimize note Table does not support optimize, doing recreate + analyze instead
961 test.t_copy optimize status OK
962 SHOW CREATE TABLE t_copy;
963 Table Create Table
964 t_copy CREATE TABLE `t_copy` (
965 `a` smallint NOT NULL AUTO_INCREMENT,
966 PRIMARY KEY (`a`)
967 ) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
968 # restart
969 # We expect the counter to still be 125
970 SHOW CREATE TABLE t_copy;
971 Table Create Table
972 t_copy CREATE TABLE `t_copy` (
973 `a` smallint NOT NULL AUTO_INCREMENT,
974 PRIMARY KEY (`a`)
975 ) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
976 DELETE FROM t_copy WHERE a >= 123;
977 CREATE UNIQUE INDEX idx_aa ON t_copy(a);
978 # restart
979 INSERT INTO t_copy VALUES(0), (0);
980 SELECT MAX(a) AS `Expect 126` FROM t_copy;
981 Expect 126
982 126
983 DROP TABLE t_copy;
984 # Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
985 CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, key(b)) ENGINE = InnoDB;
986 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
987 INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
988 INSERT INTO t30(b) VALUES(4), (5), (6), (7);
989 SELECT * FROM t30 ORDER BY b;
990 a b
991 1 1
992 200 2
993 0 3
994 201 4
995 202 5
996 203 6
997 204 7
998 ALTER TABLE t30 MODIFY b MEDIUMINT;
999 SELECT * FROM t30 ORDER BY b;
1000 a b
1001 1 1
1002 200 2
1003 0 3
1004 201 4
1005 202 5
1006 203 6
1007 204 7
1008 set global innodb_flush_log_at_trx_commit=1;
1009 CREATE TABLE t31 (a INT) ENGINE = InnoDB;
1010 INSERT INTO t31 VALUES(1), (2);
1011 ALTER TABLE t31 ADD b INT AUTO_INCREMENT PRIMARY KEY;
1012 INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
1013 INSERT INTO t31 VALUES(6, 0);
1014 ERROR 23000: Duplicate entry '0' for key 't31.PRIMARY'
1015 SELECT * FROM t31;
1016 a b
1017 3 0
1018 1 1
1019 2 2
1020 4 3
1021 5 4
1022 # Kill and restart
1023 # This will not insert 0
1024 INSERT INTO t31(a) VALUES(6), (0);
1025 SELECT * FROM t31;
1026 a b
1027 3 0
1028 1 1
1029 2 2
1030 4 3
1031 5 4
1032 6 5
1033 0 6
1034 DROP TABLE t31;
1035 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
1036 DELETE FROM t30 WHERE a = 0;
1037 UPDATE t30 set a = 0 where b = 5;
1038 SELECT * FROM t30 ORDER BY b;
1039 a b
1040 1 1
1041 200 2
1042 201 4
1043 0 5
1044 203 6
1045 204 7
1046 DELETE FROM t30 WHERE a = 0;
1047 UPDATE t30 SET a = NULL WHERE b = 6;
1048 Warnings:
1049 Warning 1048 Column 'a' cannot be null
1050 UPDATE t30 SET a = 300 WHERE b = 7;
1051 SELECT * FROM t30 ORDER BY b;
1052 a b
1053 1 1
1054 200 2
1055 201 4
1056 0 6
1057 300 7
1058 SET SQL_MODE = 0;
1059 # Scenario 10: Rollback would not rollback the counter
1060 CREATE TABLE t32 (
1061 a BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
1062 INSERT INTO t32 VALUES(0), (0);
1063 set global innodb_flush_log_at_trx_commit=1;
1064 START TRANSACTION;
1065 INSERT INTO t32 VALUES(0), (0);
1066 SELECT MAX(a) AS `Expect 4` FROM t32;
1067 Expect 4
1068 4
1069 DELETE FROM t32 WHERE a >= 2;
1070 ROLLBACK;
1071 # Kill and restart
1072 SELECT MAX(a) AS `Expect 2` FROM t32;
1073 Expect 2
1074 2
1075 INSERT INTO t32 VALUES(0), (0);
1076 SELECT MAX(a) AS `Expect 6` FROM t32;
1077 Expect 6
1078 6
1079 # Scenario 11: Test duplicate primary key/secondary key will not stop
1080 # increasing the counter
1081 CREATE TABLE t33 (
1082 a BIGINT NOT NULL PRIMARY KEY,
1083 b BIGINT NOT NULL AUTO_INCREMENT,
1084 KEY(b)) ENGINE = InnoDB;
1085 INSERT INTO t33 VALUES(1, NULL);
1086 INSERT INTO t33 VALUES(2, NULL);
1087 INSERT INTO t33 VALUES(2, NULL);
1088 ERROR 23000: Duplicate entry '2' for key 't33.PRIMARY'
1089 INSERT INTO t33 VALUES(3, NULL);
1090 SELECT MAX(b) AS `Expect 4` FROM t33;
1091 Expect 4
1092 4
1093 TRUNCATE TABLE t33;
1094 INSERT INTO t33 VALUES(1, NULL);
1095 INSERT INTO t33 VALUES(2, NULL);
1096 # To make sure the redo logs for autoinc counter must have been flushed
1097 # after commit, we start the transaction manually and do some other
1098 # operations to generate some redo logs. Or else the redo logs in the
1099 # single INSERT statement would not be flushed since the INSERT would fail
1100 set global innodb_flush_log_at_trx_commit=1;
1101 START TRANSACTION;
1102 UPDATE t33 SET a = 10 WHERE a = 1;
1103 INSERT INTO t33 VALUES(2, NULL);
1104 ERROR 23000: Duplicate entry '2' for key 't33.PRIMARY'
1105 COMMIT;
1106 # Kill and restart
1107 INSERT INTO t33 VALUES(3, NULL);
1108 SELECT MAX(b) AS `Expect 4` FROM t33;
1109 Expect 4
1110 4
1111 # Scenario 12: This should work with virtual column. We should make sure
1112 # that the column position of autoinc counter is calculated correctly
1113 CREATE TABLE v1 (
1114 a INT NOT NULL,
1115 d INT NOT NULL,
1116 b BLOB NOT NULL,
1117 c TEXT,
1118 vadcol INT AS (a + length(d)) STORED,
1119 cbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL,
1120 vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL,
1121 pkey INT NOT NULL AUTO_INCREMENT,
1122 leader INT NOT NULL,
1123 leaderpoint POINT NOT NULL,
1124 PRIMARY KEY(pkey)) ENGINE = InnoDB;
1125 INSERT INTO v1(a, d, b, c, pkey, leader, leaderpoint) VALUES
1126 (1, 2, 'abcdefg', 'qwertyu', 20, 5, ST_PointFromText('POINT(1 2)'));
1127 INSERT INTO v1(a, d, b, c, leader, leaderpoint) VALUES
1128 (1, 2, 'abcdefg', 'qwertyu', 5, ST_PointFromText('POINT(1 2)'));
1129 SELECT pkey, a, d, b, c FROM v1;
1130 pkey a d b c
1131 20 1 2 abcdefg qwertyu
1132 21 1 2 abcdefg qwertyu
1133 CREATE TABLE v2 (
1134 a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1135 c CHAR(5) NOT NULL,
1136 vaccol INT AS (length(c) * 2) VIRTUAL) ENGINE = InnoDB;
1137 INSERT INTO v2(a, c) VALUES(5, 'ABC');
1138 SELECT * FROM v2;
1139 a c vaccol
1140 5 ABC 6
1141 CREATE TABLE v3 (
1142 a INT NOT NULL,
1143 b INT NOT NULL,
1144 c INT NOT NULL,
1145 d VARCHAR(100) NOT NULL,
1146 vacol INT AS (a + b) VIRTUAL,
1147 vadcol CHAR(3) AS (substr(d, 1, 3)) STORED,
1148 pkey INT NOT NULL AUTO_INCREMENT,
1149 e CHAR(10) NOT NULL,
1150 vbecol CHAR(2) AS (substr(e, 2, 1)) VIRTUAL,
1151 g POINT NOT NULL,
1152 PRIMARY KEY(pkey)) ENGINE = InnoDB;
1153 INSERT INTO v3(a, b, c, d, pkey, e, g) VALUES
1154 (1, 2, 3, 'This is a test', 10, 'MySQL', ST_PointFromText('POINT(10 20)'));
1155 SELECT a, pkey, d FROM v3;
1156 a pkey d
1157 1 10 This is a test
1158 ALTER TABLE v3 DROP COLUMN c;
1159 INSERT INTO v3(a, b, d, pkey, e, g) VALUES
1160 (1, 2, 'This is a test', 0, 'MySQL', ST_PointFromText('POINT(10 20)'));
1161 SELECT a, pkey, d FROM v3;
1162 a pkey d
1163 1 10 This is a test
1164 1 11 This is a test
1165 DROP TABLE v1, v2, v3;
1166 DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t30, t32, t33;
1167 CREATE TABLE t1 (
1168 a INT NOT NULL,
1169 d VARCHAR(15) NOT NULL,
1170 b VARCHAR(198) NOT NULL,
1171 c CHAR(187),
1172 vadcol INT AS (a + length(d)) STORED,
1173 vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL,
1174 vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL,
1175 INDEX(d),
1176 INDEX(a),
1177 INDEX(vbidxcol),
1178 INDEX(a,vbidxcol),
1179 INDEX(vbidxcol,d)) ENGINE=InnoDB;
1180 INSERT INTO t1(a, d, b, c) VALUES (1, 'database', 'storage', 'engine');
1181 ALTER TABLE t1 DROP COLUMN vbcol, ADD COLUMN nc07226 BIGINT AUTO_INCREMENT NOT NULL, ADD UNIQUE KEY auto_nc07226(nc07226), ADD COLUMN nc05019 BOOL NOT NULL FIRST, LOCK=EXCLUSIVE;
1182 UPDATE t1 SET b = repeat('dbms', 3);
1183 INSERT INTO t1(a, d, b, c, nc07226, nc05019) VALUES (2, 'database', 'storage', 'engine', 0, false);
1184 UPDATE t1 SET c = repeat('engine', 3);
1185 SELECT * FROM t1;
1186 nc05019 a d b c vadcol vbidxcol nc07226
1187 0 1 database dbmsdbmsdbms engineengineengine 9 dbm 1
1188 0 2 database storage engineengineengine 10 sto 2
1189 DROP TABLE t1;
1190 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE = InnoDB;
1191 INSERT INTO t1 VALUES(0, 1), (2, 4), (3, 6);
1192 ALTER TABLE t1 CHANGE COLUMN b b INTEGER, ALGORITHM DEFAULT;
1193 SHOW CREATE TABLE t1;
1194 Table Create Table
1195 t1 CREATE TABLE `t1` (
1196 `a` int NOT NULL AUTO_INCREMENT,
1197 `b` int DEFAULT NULL,
1198 PRIMARY KEY (`a`)
1199 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1200 ALTER TABLE t1 AUTO_INCREMENT = 20, CHANGE COLUMN b b INT, ALGORITHM DEFAULT;
1201 SHOW CREATE TABLE t1;
1202 Table Create Table
1203 t1 CREATE TABLE `t1` (
1204 `a` int NOT NULL AUTO_INCREMENT,
1205 `b` int DEFAULT NULL,
1206 PRIMARY KEY (`a`)
1207 ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1208 INSERT INTO t1 VALUES(NULL, 10);
1209 SELECT MAX(a) FROM t1;
1210 MAX(a)
1211 20
1212 ALTER TABLE t1 AUTO_INCREMENT = 10, CHANGE COLUMN b b INTEGER, ALGORITHM DEFAULT;
1213 SHOW CREATE TABLE t1;
1214 Table Create Table
1215 t1 CREATE TABLE `t1` (
1216 `a` int NOT NULL AUTO_INCREMENT,
1217 `b` int DEFAULT NULL,
1218 PRIMARY KEY (`a`)
1219 ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1220 INSERT INTO t1 VALUES(NULL, 12);
1221 SELECT MAX(a) FROM t1;
1222 MAX(a)
1223 21
1224 DROP TABLE t1;