Debian Codegraph @

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

raw
1 SET @default_innodb_autoinc_lock_mode = @@global.innodb_autoinc_lock_mode;
2 SELECT @@global.innodb_autoinc_lock_mode;
3 @@global.innodb_autoinc_lock_mode
4 2
5 SET @default_auto_increment_offset = @@global.auto_increment_offset;
6 SET @default_auto_increment_increment = @@global.auto_increment_increment;
7 # Bulk inserts in interleaved lock mode
8 CREATE TABLE t3(cc INT);
9 INSERT INTO t3 VALUES (10),(20),(30),(40),(50),(60),(70),(80);
10 CREATE TABLE t4(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
11 INSERT INTO t4(cc) SELECT * FROM t3;
12 SHOW CREATE TABLE t4;
13 Table Create Table
14 t4 CREATE TABLE `t4` (
15 `pkey` int NOT NULL AUTO_INCREMENT,
16 `cc` int DEFAULT NULL,
17 PRIMARY KEY (`pkey`)
18 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
19 INSERT INTO t4(cc) SELECT * FROM t3;
20 SHOW CREATE TABLE t4;
21 Table Create Table
22 t4 CREATE TABLE `t4` (
23 `pkey` int NOT NULL AUTO_INCREMENT,
24 `cc` int DEFAULT NULL,
25 PRIMARY KEY (`pkey`)
26 ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
27 DROP TABLE t4;
28 DROP TABLE t3;
29 # Concurrent simple inserts in interleaved lock mode
30 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
31 CREATE PROCEDURE POP1()
32 BEGIN
33 DECLARE i INT DEFAULT 1;
34 WHILE (i<= 10) DO
35 INSERT INTO t1 VALUES(NULL,i);
36 IF ((i%2) =0 ) THEN
37 DELETE FROM t1 WHERE bb=i;
38 END IF;
39 SET i = i+1;
40 END WHILE;
41 END //
42 CALL POP1();
43 CALL POP1();
44 SELECT COUNT(*) FROM t1;
45 COUNT(*)
46 10
47 SET @@global.auto_increment_offset = 9;
48 SET @@global.auto_increment_increment = 10;
49 TRUNCATE TABLE t1;
50 CALL POP1();
51 CALL POP1();
52 SELECT COUNT(*) FROM t1;
53 COUNT(*)
54 10
55 SET @@global.auto_increment_offset = @default_auto_increment_offset;
56 SET @@global.auto_increment_increment = @default_auto_increment_increment;
57 DROP PROCEDURE POP1;
58 DROP TABLE t1;
59 # Concurrent bulk inserts in interleaved lock mode
60 CREATE TABLE t2 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
61 CREATE TABLE t1 (aa INT);
62 CREATE PROCEDURE POP()
63 BEGIN
64 DECLARE i INT DEFAULT 1;
65 WHILE (i <= 10) DO
66 INSERT INTO t1 VALUES(i);
67 SET i = i +1;
68 END WHILE;
69 INSERT INTO t2(aa) SELECT * FROM t1;
70 END //
71 CALL POP();
72 CALL POP();
73 SET @@global.auto_increment_offset = 7;
74 SET @@global.auto_increment_increment = 15;
75 TRUNCATE TABLE t1;
76 TRUNCATE TABLE t2;
77 CALL POP();
78 CALL POP();
79 SET @@global.auto_increment_increment = @default_auto_increment_increment;
80 SET @@global.auto_increment_offset = @default_auto_increment_offset;
81 DROP TABLE t1;
82 DROP TABLE t2;
83 DROP PROCEDURE POP;
84 # Concurrent mixed mode inserts in interleaved lock mode
85 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
86 CREATE PROCEDURE POP2(IN j INT)
87 BEGIN
88 DECLARE i INT DEFAULT 1;
89 WHILE (i<=5) DO
90 INSERT INTO t1(cc) VALUES(i);
91 SET i=i+1;
92 END WHILE;
93 INSERT INTO t1 VALUES(100+j,20);
94 WHILE(i>=5 && i<=10) DO
95 INSERT INTO t1(cc) VALUES(i);
96 SET i=i+1;
97 END WHILE;
98 INSERT INTO t1 VALUES (200+j,25);
99 WHILE(i>=10 && i<=15) DO
100 INSERT INTO t1(cc) VALUES(i);
101 SET i=i+1;
102 END WHILE;
103 END //
104 Warnings:
105 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
106 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
107 CALL POP2(1);
108 CALL POP2(120);
109 SELECT COUNT(*) FROM t1;
110 COUNT(*)
111 34
112 SET @@global.auto_increment_offset = 13;
113 SET @@global.auto_increment_increment = 43;
114 TRUNCATE TABLE t1;
115 CALL POP2(1);
116 CALL POP2(280);
117 SELECT COUNT(*) FROM t1;
118 COUNT(*)
119 34
120 SET @@global.auto_increment_offset = @default_auto_increment_offset;
121 SET @@global.auto_increment_increment = @default_auto_increment_increment;
122 DROP PROCEDURE POP2;
123 DROP TABLE t1;
124 # Transactions in interleaved lock mode
125 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
126 START TRANSACTION;
127 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
128 ROLLBACK;
129 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
130 SELECT * FROM t1;
131 pkey
132 4
133 5
134 6
135 DROP TABLE t1;
136 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
137 CREATE TABLE t2(aa INT);
138 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
139 START TRANSACTION;
140 INSERT INTO t1(aa) SELECT * FROM t2;
141 SELECT * FROM t1;
142 pkey aa
143 1 1
144 2 2
145 3 3
146 4 4
147 5 5
148 6 6
149 7 7
150 ROLLBACK;
151 INSERT INTO t1(aa) SELECT * FROM t2;
152 SELECT * FROM t1;
153 pkey aa
154 8 1
155 9 2
156 10 3
157 11 4
158 12 5
159 13 6
160 14 7
161 DROP TABLE t1;
162 DROP TABLE t2;
163 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
164 START TRANSACTION;
165 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
166 INSERT INTO t1 VALUES (23),(24);
167 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
168 INSERT INTO t1 VALUES (34);
169 SELECT * FROM t1;
170 pkey
171 1
172 2
173 3
174 4
175 23
176 24
177 25
178 26
179 27
180 34
181 ROLLBACK;
182 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
183 INSERT INTO t1 VALUES (23),(24);
184 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
185 INSERT INTO t1 VALUES (34);
186 SELECT * FROM t1;
187 pkey
188 23
189 24
190 34
191 35
192 36
193 37
194 38
195 39
196 40
197 41
198 DROP TABLE t1;
199 # Simple, Mixed and Bulk inserts with auto increment offset in interleaved locking mode
200 SET @@auto_increment_offset = 17;
201 SET @@auto_increment_increment = 40;
202 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
203 START TRANSACTION;
204 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
205 SELECT * FROM t1;
206 pkey
207 17
208 57
209 97
210 ROLLBACK;
211 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
212 SELECT * FROM t1;
213 pkey
214 137
215 177
216 217
217 DROP TABLE t1;
218 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
219 CREATE TABLE t2(aa INT);
220 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
221 START TRANSACTION;
222 INSERT INTO t1(aa) SELECT * FROM t2;
223 SELECT * FROM t1;
224 pkey aa
225 17 1
226 57 2
227 97 3
228 137 4
229 177 5
230 217 6
231 257 7
232 ROLLBACK;
233 INSERT INTO t1(aa) SELECT * FROM t2;
234 SELECT * FROM t1;
235 pkey aa
236 297 1
237 337 2
238 377 3
239 417 4
240 457 5
241 497 6
242 537 7
243 DROP TABLE t1;
244 DROP TABLE t2;
245 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
246 START TRANSACTION;
247 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
248 INSERT INTO t1 VALUES (23),(24);
249 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
250 INSERT INTO t1 VALUES (34);
251 SELECT * FROM t1;
252 pkey
253 17
254 23
255 24
256 34
257 57
258 97
259 137
260 177
261 217
262 257
263 ROLLBACK;
264 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
265 INSERT INTO t1 VALUES (23),(24);
266 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
267 INSERT INTO t1 VALUES (34);
268 SELECT * FROM t1;
269 pkey
270 23
271 24
272 34
273 297
274 337
275 377
276 417
277 457
278 497
279 537
280 DROP TABLE t1;
281 SET @@auto_increment_offset = @default_auto_increment_offset;
282 SET @@auto_increment_increment = @default_auto_increment_increment;
283 # Transactions with concurrency in interleaved locking mode
284 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
285 CREATE PROCEDURE POP1()
286 BEGIN
287 DECLARE i INT DEFAULT 1;
288 WHILE (i<= 10) DO
289 INSERT INTO t1 VALUES(NULL,i);
290 SET i = i+1;
291 END WHILE;
292 END //
293 START TRANSACTION;
294 CALL POP1();
295 CALL POP1();
296 SELECT COUNT(*) FROM t1;
297 COUNT(*)
298 20
299 ROLLBACK;
300 CALL POP1();
301 CALL POP1();
302 SELECT COUNT(*) FROM t1;
303 COUNT(*)
304 30
305 DROP PROCEDURE POP1;
306 DROP TABLE t1;
307 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
308 CREATE PROCEDURE POP2(IN j INT)
309 BEGIN
310 DECLARE i INT DEFAULT 1;
311 WHILE (i<=5) DO
312 INSERT INTO t1(cc) VALUES(i);
313 SET i=i+1;
314 END WHILE;
315 INSERT INTO t1 VALUES(100+j,20);
316 WHILE(i>=5 && i<=10) DO
317 INSERT INTO t1(cc) VALUES(i);
318 SET i=i+1;
319 END WHILE;
320 INSERT INTO t1 VALUES (200+j,25);
321 WHILE(i>=10 && i<=15) DO
322 INSERT INTO t1(cc) VALUES(i);
323 SET i=i+1;
324 END WHILE;
325 END //
326 Warnings:
327 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
328 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
329 START TRANSACTION;
330 CALL POP2(1);
331 CALL POP2(60);
332 SELECT COUNT(*) FROM t1;
333 COUNT(*)
334 34
335 ROLLBACK;
336 CALL POP2(1);
337 CALL POP2(280);
338 SELECT COUNT(*) FROM t1;
339 COUNT(*)
340 51
341 DROP PROCEDURE POP2;
342 DROP TABLE t1;
343 # Crash server in interleaved lock mode
344 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
345 CREATE PROCEDURE POP()
346 BEGIN
347 DECLARE i INT DEFAULT 1;
348 WHILE (i <= 10) DO
349 INSERT INTO t1(aa) VALUES(i);
350 SET i = i +1;
351 END WHILE;
352 END //
353 START TRANSACTION;
354 CALL POP();
355 SET SESSION DEBUG="+d,crash_commit_after";
356 CR_SERVER_LOST
357 COMMIT;
358 ERROR HY000: Lost connection to MySQL server during query
359 # restart
360 SELECT COUNT(*) FROM t1;
361 COUNT(*)
362 10
363 START TRANSACTION;
364 CALL POP();
365 SET SESSION DEBUG="+d,crash_commit_before";
366 CR_SERVER_LOST
367 COMMIT;
368 ERROR HY000: Lost connection to MySQL server during query
369 # restart
370 SELECT COUNT(*) FROM t1;
371 COUNT(*)
372 10
373 TRUNCATE TABLE t1;
374 SET @@auto_increment_offset = 20;
375 SET @@auto_increment_increment = 40;
376 START TRANSACTION;
377 CALL POP();
378 SET SESSION DEBUG="+d,crash_commit_after";
379 CR_SERVER_LOST
380 COMMIT;
381 ERROR HY000: Lost connection to MySQL server during query
382 # restart
383 SELECT COUNT(*) FROM t1;
384 COUNT(*)
385 10
386 START TRANSACTION;
387 CALL POP();
388 SET SESSION DEBUG="+d,crash_commit_before";
389 CR_SERVER_LOST
390 COMMIT;
391 ERROR HY000: Lost connection to MySQL server during query
392 # restart
393 SELECT COUNT(*) FROM t1;
394 COUNT(*)
395 10
396 DROP TABLE t1;
397 DROP PROCEDURE POP;
398 # Concurrent load inserts in interleaved lock mode
399 CREATE TABLE loads ( pkey INT PRIMARY KEY AUTO_INCREMENT, ww CHAR(50));
400 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
401 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
402 SELECT MAX(pkey) FROM loads;
403 MAX(pkey)
404 197
405 SET @@global.auto_increment_offset = 12;
406 SET @@global.auto_increment_increment = 50;
407 TRUNCATE TABLE loads;
408 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
409 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
410 SELECT MAX(pkey) FROM loads;
411 MAX(pkey)
412 9812
413 DROP TABLE loads;
414 # Restart server with innodb_autoinc_lock_mode = 1
415 # restart: --innodb_autoinc_lock_mode=1
416 SELECT @@global.innodb_autoinc_lock_mode;
417 @@global.innodb_autoinc_lock_mode
418 1
419 SET @default_auto_increment_offset = @@global.auto_increment_offset;
420 SET @default_auto_increment_increment = @@global.auto_increment_increment;
421 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT);
422 INSERT INTO t1 values (NULL,3),
423 (NULL,6),
424 (NULL,9),
425 (NULL,12),
426 (NULL,15);
427 SELECT * FROM t1;
428 pkey a
429 1 3
430 2 6
431 3 9
432 4 12
433 5 15
434 UPDATE t1 SET pkey = 6
435 WHERE a = 15;
436 SELECT * FROM t1;
437 pkey a
438 1 3
439 2 6
440 3 9
441 4 12
442 6 15
443 INSERT INTO t1 VALUES (NULL,18);
444 SELECT * FROM t1;
445 pkey a
446 1 3
447 2 6
448 3 9
449 4 12
450 6 15
451 7 18
452 DELETE FROM t1 WHERE a = 18;
453 INSERT INTO t1 VALUES (NULL,18);
454 SELECT * FROM t1;
455 pkey a
456 1 3
457 2 6
458 3 9
459 4 12
460 6 15
461 8 18
462 SET @default_auto_increment_increment = @@auto_increment_increment;
463 SET @@auto_increment_increment=10;
464 SELECT @@session.auto_increment_increment;
465 @@session.auto_increment_increment
466 10
467 INSERT INTO t1 VALUES (NULL,21),
468 (NULL,24),
469 (NULL,27);
470 SELECT * FROM t1;
471 pkey a
472 1 3
473 2 6
474 3 9
475 4 12
476 6 15
477 8 18
478 11 21
479 21 24
480 31 27
481 SET @default_auto_increment_offset = @@auto_increment_offset;
482 SET @@auto_increment_offset=5;
483 SELECT @@auto_increment_offset;
484 @@auto_increment_offset
485 5
486 INSERT INTO t1 VALUES (NULL,30),
487 (NULL,33),
488 (NULL,36);
489 SELECT * FROM t1;
490 pkey a
491 1 3
492 2 6
493 3 9
494 4 12
495 6 15
496 8 18
497 11 21
498 21 24
499 31 27
500 45 30
501 55 33
502 65 36
503 SET @@auto_increment_offset =150;
504 SELECT @@auto_increment_offset;
505 @@auto_increment_offset
506 150
507 INSERT INTO t1 VALUES (NULL,39),
508 (NULL,42),
509 (NULL,45);
510 SELECT * FROM t1;
511 pkey a
512 1 3
513 2 6
514 3 9
515 4 12
516 6 15
517 8 18
518 11 21
519 21 24
520 31 27
521 45 30
522 55 33
523 65 36
524 84 39
525 94 42
526 104 45
527 SET @@auto_increment_increment = @default_auto_increment_increment;
528 SET @@auto_increment_offset = @default_auto_increment_offset;
529 DROP TABLE t1;
530 # Mixed mode inserts in consecutive lock mode
531 CREATE TABLE t2( pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT );
532 INSERT INTO t2 VALUES (NULL,10),
533 (12,20),
534 (NULL,30),
535 (NULL,40),
536 (40,50),
537 (NULL,60),
538 (NULL,70);
539 SELECT * FROM t2;
540 pkey bb
541 1 10
542 12 20
543 13 30
544 14 40
545 40 50
546 41 60
547 42 70
548 DROP TABLE t2;
549 # Bulk inserts in consecutive lock mode
550 CREATE TABLE t3(cc INT);
551 INSERT INTO t3 VALUES (10),(20),(30),(40),(50),(60),(70),(80);
552 CREATE TABLE t4(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
553 INSERT INTO t4(cc) SELECT * FROM t3;
554 SHOW CREATE TABLE t4;
555 Table Create Table
556 t4 CREATE TABLE `t4` (
557 `pkey` int NOT NULL AUTO_INCREMENT,
558 `cc` int DEFAULT NULL,
559 PRIMARY KEY (`pkey`)
560 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
561 INSERT INTO t4(cc) SELECT * FROM t3;
562 SHOW CREATE TABLE t4;
563 Table Create Table
564 t4 CREATE TABLE `t4` (
565 `pkey` int NOT NULL AUTO_INCREMENT,
566 `cc` int DEFAULT NULL,
567 PRIMARY KEY (`pkey`)
568 ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
569 DROP TABLE t4;
570 DROP TABLE t3;
571 # Concurrent simple inserts in consecutive lock mode
572 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
573 CREATE PROCEDURE POP1()
574 BEGIN
575 DECLARE i INT DEFAULT 1;
576 WHILE (i<= 10) DO
577 INSERT INTO t1 VALUES(NULL,i);
578 IF ( (i%2) = 0 ) THEN
579 DELETE FROM t1 WHERE bb=i;
580 END IF;
581 SET i = i+1;
582 END WHILE;
583 END //
584 CALL POP1();
585 CALL POP1();
586 SELECT COUNT(*) FROM t1;
587 COUNT(*)
588 10
589 SET @@global.auto_increment_offset=10;
590 SET @@global.auto_increment_increment=25;
591 TRUNCATE TABLE t1;
592 CALL POP1();
593 CALL POP1();
594 SELECT COUNT(*) FROM t1;
595 COUNT(*)
596 10
597 SET @@global.auto_increment_offset = @default_auto_increment_offset;
598 SET @@global.auto_increment_increment = @default_auto_increment_increment;
599 DROP PROCEDURE POP1;
600 DROP TABLE t1;
601 # Concurrent bulk inserts in consecutive lock mode
602 CREATE TABLE t2 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
603 CREATE TABLE t1 (aa INT);
604 CREATE PROCEDURE POP()
605 BEGIN
606 DECLARE i INT DEFAULT 1;
607 WHILE (i <= 10) DO
608 INSERT INTO t1 VALUES(i);
609 SET i = i +1;
610 END WHILE;
611 INSERT INTO t2(aa) SELECT * FROM t1;
612 END //
613 CALL POP();
614 CALL POP();
615 SET @@global.auto_increment_offset = 16;
616 SET @@global.auto_increment_increment = 32;
617 TRUNCATE TABLE t1;
618 TRUNCATE TABLE t2;
619 CALL POP();
620 CALL POP();
621 SET @@global.auto_increment_offset = @default_auto_increment_offset;
622 SET @@global.auto_increment_increment = @default_auto_increment_increment;
623 DROP PROCEDURE POP;
624 DROP TABLE t1;
625 DROP TABLE t2;
626 # Concurrent mixed mode inserts in consecutive lock mode
627 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
628 CREATE PROCEDURE POP2(IN j INT)
629 BEGIN
630 DECLARE i INT DEFAULT 1;
631 WHILE (i<=5) DO
632 INSERT INTO t1(cc) VALUES(i);
633 SET i=i+1;
634 END WHILE;
635 INSERT INTO t1 VALUES(100+j,20);
636 WHILE(i>=5 && i<=10) DO
637 INSERT INTO t1(cc) VALUES(i);
638 SET i=i+1;
639 END WHILE;
640 INSERT INTO t1 VALUES (200+j,25);
641 WHILE(i>=10 && i<=15) DO
642 INSERT INTO t1(cc) VALUES(i);
643 SET i=i+1;
644 END WHILE;
645 END //
646 Warnings:
647 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
648 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
649 CALL POP2(1);
650 CALL POP2(70);
651 SELECT COUNT(*) FROM t1;
652 COUNT(*)
653 34
654 SET @@global.auto_increment_offset = 3;
655 SET @@global.auto_increment_increment = 4;
656 TRUNCATE TABLE t1;
657 CALL POP2(1);
658 CALL POP2(80);
659 SELECT COUNT(*) FROM t1;
660 COUNT(*)
661 34
662 SET @@global.auto_increment_offset = @default_auto_increment_offset;
663 SET @@global.auto_increment_increment = @default_auto_increment_increment;
664 DROP PROCEDURE POP2;
665 DROP TABLE t1;
666 # Transactions in consecutive lock mode
667 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
668 START TRANSACTION;
669 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
670 SELECT * FROM t1;
671 pkey
672 1
673 2
674 3
675 ROLLBACK;
676 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
677 SELECT * FROM t1;
678 pkey
679 4
680 5
681 6
682 DROP TABLE t1;
683 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
684 CREATE TABLE t2(aa INT);
685 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
686 START TRANSACTION;
687 INSERT INTO t1(aa) SELECT * FROM t2;
688 SELECT * FROM t1;
689 pkey aa
690 1 1
691 2 2
692 3 3
693 4 4
694 5 5
695 6 6
696 7 7
697 ROLLBACK;
698 INSERT INTO t1(aa) SELECT * FROM t2;
699 SELECT * FROM t1;
700 pkey aa
701 8 1
702 9 2
703 10 3
704 11 4
705 12 5
706 13 6
707 14 7
708 DROP TABLE t1;
709 DROP TABLE t2;
710 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
711 START TRANSACTION;
712 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
713 INSERT INTO t1 VALUES (23),(24);
714 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
715 INSERT INTO t1 VALUES (34);
716 SELECT * FROM t1;
717 pkey
718 1
719 2
720 3
721 4
722 23
723 24
724 25
725 26
726 27
727 34
728 ROLLBACK;
729 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
730 INSERT INTO t1 VALUES (23),(24);
731 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
732 INSERT INTO t1 VALUES (34);
733 SELECT * FROM t1;
734 pkey
735 23
736 24
737 34
738 35
739 36
740 37
741 38
742 39
743 40
744 41
745 DROP TABLE t1;
746 # Simple, Mixed and Bulk inserts with auto increment offset in default locking mode
747 SET @@auto_increment_offset = 17;
748 SET @@auto_increment_increment = 40;
749 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
750 START TRANSACTION;
751 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
752 SELECT * FROM t1;
753 pkey
754 17
755 57
756 97
757 ROLLBACK;
758 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
759 SELECT * FROM t1;
760 pkey
761 137
762 177
763 217
764 DROP TABLE t1;
765 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
766 CREATE TABLE t2(aa INT);
767 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
768 START TRANSACTION;
769 INSERT INTO t1(aa) SELECT * FROM t2;
770 SELECT * FROM t1;
771 pkey aa
772 17 1
773 57 2
774 97 3
775 137 4
776 177 5
777 217 6
778 257 7
779 ROLLBACK;
780 INSERT INTO t1(aa) SELECT * FROM t2;
781 SELECT * FROM t1;
782 pkey aa
783 297 1
784 337 2
785 377 3
786 417 4
787 457 5
788 497 6
789 537 7
790 DROP TABLE t1;
791 DROP TABLE t2;
792 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
793 START TRANSACTION;
794 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
795 INSERT INTO t1 VALUES (23),(24);
796 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
797 INSERT INTO t1 VALUES (34);
798 SELECT * FROM t1;
799 pkey
800 17
801 23
802 24
803 34
804 57
805 97
806 137
807 177
808 217
809 257
810 ROLLBACK;
811 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
812 INSERT INTO t1 VALUES (23),(24);
813 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
814 INSERT INTO t1 VALUES (34);
815 SELECT * FROM t1;
816 pkey
817 23
818 24
819 34
820 297
821 337
822 377
823 417
824 457
825 497
826 537
827 DROP TABLE t1;
828 SET @@auto_increment_offset = @default_auto_increment_offset;
829 SET @@auto_increment_increment = @default_auto_increment_increment;
830 # Transactions with concurrency in consecutive locking mode
831 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
832 CREATE PROCEDURE POP1()
833 BEGIN
834 DECLARE i INT DEFAULT 1;
835 WHILE (i<= 10) DO
836 INSERT INTO t1 VALUES(NULL,i);
837 SET i = i+1;
838 END WHILE;
839 END //
840 START TRANSACTION;
841 CALL POP1();
842 CALL POP1();
843 SELECT COUNT(*) FROM t1;
844 COUNT(*)
845 20
846 ROLLBACK;
847 CALL POP1();
848 CALL POP1();
849 SELECT COUNT(*) FROM t1;
850 COUNT(*)
851 30
852 DROP PROCEDURE POP1;
853 DROP TABLE t1;
854 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
855 CREATE PROCEDURE POP2(IN j INT)
856 BEGIN
857 DECLARE i INT DEFAULT 1;
858 WHILE (i<=5) DO
859 INSERT INTO t1(cc) VALUES(i);
860 SET i=i+1;
861 END WHILE;
862 INSERT INTO t1 VALUES(100+j,20);
863 WHILE(i>=5 && i<=10) DO
864 INSERT INTO t1(cc) VALUES(i);
865 SET i=i+1;
866 END WHILE;
867 INSERT INTO t1 VALUES (200+j,25);
868 WHILE(i>=10 && i<=15) DO
869 INSERT INTO t1(cc) VALUES(i);
870 SET i=i+1;
871 END WHILE;
872 END //
873 Warnings:
874 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
875 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
876 START TRANSACTION;
877 CALL POP2(1);
878 CALL POP2(51);
879 SELECT COUNT(*) FROM t1;
880 COUNT(*)
881 34
882 ROLLBACK;
883 CALL POP2(1);
884 CALL POP2(150);
885 SELECT COUNT(*) FROM t1;
886 COUNT(*)
887 51
888 DROP PROCEDURE POP2;
889 DROP TABLE t1;
890 # Crash server in default locking mode
891 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
892 CREATE PROCEDURE POP()
893 BEGIN
894 DECLARE i INT DEFAULT 1;
895 WHILE (i <= 10) DO
896 INSERT INTO t1(aa) VALUES(i);
897 SET i = i +1;
898 END WHILE;
899 END //
900 START TRANSACTION;
901 CALL POP();
902 SET SESSION DEBUG="+d,crash_commit_after";
903 CR_SERVER_LOST
904 COMMIT;
905 ERROR HY000: Lost connection to MySQL server during query
906 # restart: --innodb_autoinc_lock_mode=1
907 SELECT COUNT(*) FROM t1;
908 COUNT(*)
909 10
910 START TRANSACTION;
911 CALL POP();
912 SET SESSION DEBUG="+d,crash_commit_before";
913 CR_SERVER_LOST
914 COMMIT;
915 ERROR HY000: Lost connection to MySQL server during query
916 # restart: --innodb_autoinc_lock_mode=1
917 SELECT COUNT(*) FROM t1;
918 COUNT(*)
919 10
920 TRUNCATE TABLE t1;
921 SET @@auto_increment_offset = 8;
922 SET @@auto_increment_increment = 13;
923 START TRANSACTION;
924 CALL POP();
925 SET SESSION DEBUG="+d,crash_commit_after";
926 CR_SERVER_LOST
927 COMMIT;
928 ERROR HY000: Lost connection to MySQL server during query
929 # restart: --innodb_autoinc_lock_mode=1
930 SELECT COUNT(*) FROM t1;
931 COUNT(*)
932 10
933 START TRANSACTION;
934 CALL POP();
935 SET SESSION DEBUG="+d,crash_commit_before";
936 CR_SERVER_LOST
937 COMMIT;
938 ERROR HY000: Lost connection to MySQL server during query
939 # restart: --innodb_autoinc_lock_mode=1
940 SELECT COUNT(*) FROM t1;
941 COUNT(*)
942 10
943 DROP TABLE t1;
944 DROP PROCEDURE POP;
945 # Concurrent load inserts in consecutive lock mode
946 CREATE TABLE loads ( pkey INT PRIMARY KEY AUTO_INCREMENT, ww CHAR(50));
947 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
948 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
949 SELECT * FROM loads;
950 pkey ww
951 1 Aarhus
952 2 Aaron
953 3 Ababa
954 4 aback
955 5 abaft
956 6 abandon
957 7 abandoned
958 8 abandoning
959 9 abandonment
960 10 abandons
961 11 Aarhus
962 12 Aaron
963 13 Ababa
964 14 aback
965 15 abaft
966 16 abandon
967 17 abandoned
968 18 abandoning
969 19 abandonment
970 20 abandons
971 21 abase
972 22 abased
973 23 abasement
974 24 abasements
975 25 abases
976 26 abash
977 27 abashed
978 28 abashes
979 29 abashing
980 30 abasing
981 31 abate
982 32 abated
983 33 abatement
984 34 abatements
985 35 abater
986 36 abates
987 37 abating
988 38 Abba
989 39 abbe
990 40 abbey
991 41 abbeys
992 42 abbot
993 43 abbots
994 44 Abbott
995 45 abbreviate
996 46 abbreviated
997 47 abbreviates
998 48 abbreviating
999 49 abbreviation
1000 50 abbreviations
1001 51 Abby
1002 52 abdomen
1003 53 abdomens
1004 54 abdominal
1005 55 abduct
1006 56 abducted
1007 57 abduction
1008 58 abductions
1009 59 abductor
1010 60 abductors
1011 61 abducts
1012 62 Abe
1013 63 abed
1014 64 Abel
1015 65 Abelian
1016 66 Abelson
1017 67 Aberdeen
1018 68 Abernathy
1019 69 aberrant
1020 70 aberration
1021 128 Aarhus
1022 129 Aaron
1023 130 Ababa
1024 131 aback
1025 132 abaft
1026 133 abandon
1027 134 abandoned
1028 135 abandoning
1029 136 abandonment
1030 137 abandons
1031 138 Aarhus
1032 139 Aaron
1033 140 Ababa
1034 141 aback
1035 142 abaft
1036 143 abandon
1037 144 abandoned
1038 145 abandoning
1039 146 abandonment
1040 147 abandons
1041 148 abase
1042 149 abased
1043 150 abasement
1044 151 abasements
1045 152 abases
1046 153 abash
1047 154 abashed
1048 155 abashes
1049 156 abashing
1050 157 abasing
1051 158 abate
1052 159 abated
1053 160 abatement
1054 161 abatements
1055 162 abater
1056 163 abates
1057 164 abating
1058 165 Abba
1059 166 abbe
1060 167 abbey
1061 168 abbeys
1062 169 abbot
1063 170 abbots
1064 171 Abbott
1065 172 abbreviate
1066 173 abbreviated
1067 174 abbreviates
1068 175 abbreviating
1069 176 abbreviation
1070 177 abbreviations
1071 178 Abby
1072 179 abdomen
1073 180 abdomens
1074 181 abdominal
1075 182 abduct
1076 183 abducted
1077 184 abduction
1078 185 abductions
1079 186 abductor
1080 187 abductors
1081 188 abducts
1082 189 Abe
1083 190 abed
1084 191 Abel
1085 192 Abelian
1086 193 Abelson
1087 194 Aberdeen
1088 195 Abernathy
1089 196 aberrant
1090 197 aberration
1091 SET @@global.auto_increment_offset = 12;
1092 SET @@global.auto_increment_increment = 50;
1093 TRUNCATE TABLE loads;
1094 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1095 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1096 SELECT * FROM loads;
1097 pkey ww
1098 12 Aarhus
1099 62 Aaron
1100 112 Ababa
1101 162 aback
1102 212 abaft
1103 262 abandon
1104 312 abandoned
1105 362 abandoning
1106 412 abandonment
1107 462 abandons
1108 512 Aarhus
1109 562 Aaron
1110 612 Ababa
1111 662 aback
1112 712 abaft
1113 762 abandon
1114 812 abandoned
1115 862 abandoning
1116 912 abandonment
1117 962 abandons
1118 1012 abase
1119 1062 abased
1120 1112 abasement
1121 1162 abasements
1122 1212 abases
1123 1262 abash
1124 1312 abashed
1125 1362 abashes
1126 1412 abashing
1127 1462 abasing
1128 1512 abate
1129 1562 abated
1130 1612 abatement
1131 1662 abatements
1132 1712 abater
1133 1762 abates
1134 1812 abating
1135 1862 Abba
1136 1912 abbe
1137 1962 abbey
1138 2012 abbeys
1139 2062 abbot
1140 2112 abbots
1141 2162 Abbott
1142 2212 abbreviate
1143 2262 abbreviated
1144 2312 abbreviates
1145 2362 abbreviating
1146 2412 abbreviation
1147 2462 abbreviations
1148 2512 Abby
1149 2562 abdomen
1150 2612 abdomens
1151 2662 abdominal
1152 2712 abduct
1153 2762 abducted
1154 2812 abduction
1155 2862 abductions
1156 2912 abductor
1157 2962 abductors
1158 3012 abducts
1159 3062 Abe
1160 3112 abed
1161 3162 Abel
1162 3212 Abelian
1163 3262 Abelson
1164 3312 Aberdeen
1165 3362 Abernathy
1166 3412 aberrant
1167 3462 aberration
1168 6362 Aarhus
1169 6412 Aaron
1170 6462 Ababa
1171 6512 aback
1172 6562 abaft
1173 6612 abandon
1174 6662 abandoned
1175 6712 abandoning
1176 6762 abandonment
1177 6812 abandons
1178 6862 Aarhus
1179 6912 Aaron
1180 6962 Ababa
1181 7012 aback
1182 7062 abaft
1183 7112 abandon
1184 7162 abandoned
1185 7212 abandoning
1186 7262 abandonment
1187 7312 abandons
1188 7362 abase
1189 7412 abased
1190 7462 abasement
1191 7512 abasements
1192 7562 abases
1193 7612 abash
1194 7662 abashed
1195 7712 abashes
1196 7762 abashing
1197 7812 abasing
1198 7862 abate
1199 7912 abated
1200 7962 abatement
1201 8012 abatements
1202 8062 abater
1203 8112 abates
1204 8162 abating
1205 8212 Abba
1206 8262 abbe
1207 8312 abbey
1208 8362 abbeys
1209 8412 abbot
1210 8462 abbots
1211 8512 Abbott
1212 8562 abbreviate
1213 8612 abbreviated
1214 8662 abbreviates
1215 8712 abbreviating
1216 8762 abbreviation
1217 8812 abbreviations
1218 8862 Abby
1219 8912 abdomen
1220 8962 abdomens
1221 9012 abdominal
1222 9062 abduct
1223 9112 abducted
1224 9162 abduction
1225 9212 abductions
1226 9262 abductor
1227 9312 abductors
1228 9362 abducts
1229 9412 Abe
1230 9462 abed
1231 9512 Abel
1232 9562 Abelian
1233 9612 Abelson
1234 9662 Aberdeen
1235 9712 Abernathy
1236 9762 aberrant
1237 9812 aberration
1238 DROP TABLE loads;
1239 # Restart server with innodb_autoinc_lock_mode = 0
1240 # restart: --innodb_autoinc_lock_mode=0
1241 SET @default_auto_increment_offset = @@global.auto_increment_offset;
1242 SET @default_auto_increment_increment = @@global.auto_increment_increment;
1243 SELECT @@global.innodb_autoinc_lock_mode;
1244 @@global.innodb_autoinc_lock_mode
1245 0
1246 # Bulk inserts in traditional lock mode
1247 CREATE TABLE t3(cc INT);
1248 INSERT INTO t3 VALUES (10),(20),(30),(40),(50),(60),(70),(80);
1249 CREATE TABLE t4(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1250 INSERT INTO t4(cc) SELECT * FROM t3;
1251 SHOW CREATE TABLE t4;
1252 Table Create Table
1253 t4 CREATE TABLE `t4` (
1254 `pkey` int NOT NULL AUTO_INCREMENT,
1255 `cc` int DEFAULT NULL,
1256 PRIMARY KEY (`pkey`)
1257 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1258 INSERT INTO t4(cc) SELECT * FROM t3;
1259 SHOW CREATE TABLE t4;
1260 Table Create Table
1261 t4 CREATE TABLE `t4` (
1262 `pkey` int NOT NULL AUTO_INCREMENT,
1263 `cc` int DEFAULT NULL,
1264 PRIMARY KEY (`pkey`)
1265 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1266 SELECT * FROM t4;
1267 pkey cc
1268 1 10
1269 2 20
1270 3 30
1271 4 40
1272 5 50
1273 6 60
1274 7 70
1275 8 80
1276 9 10
1277 10 20
1278 11 30
1279 12 40
1280 13 50
1281 14 60
1282 15 70
1283 16 80
1284 DROP TABLE t4;
1285 DROP TABLE t3;
1286 # Concurrent simple inserts in traditional lock mode
1287 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1288 CREATE PROCEDURE POP1()
1289 BEGIN
1290 DECLARE i INT DEFAULT 1;
1291 WHILE (i<= 10) DO
1292 INSERT INTO t1 VALUES(NULL,i);
1293 IF ((i%2) = 0) THEN
1294 DELETE FROM t1 WHERE bb=i;
1295 END IF;
1296 SET i = i+1;
1297 END WHILE;
1298 END //
1299 CALL POP1();
1300 CALL POP1();
1301 SELECT COUNT(*) FROM t1;
1302 COUNT(*)
1303 10
1304 SET @@global.auto_increment_offset = 30;
1305 SET @@global.auto_increment_increment = 48;
1306 TRUNCATE TABLE t1;
1307 CALL POP1();
1308 CALL POP1();
1309 SELECT COUNT(*) FROM t1;
1310 COUNT(*)
1311 10
1312 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1313 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1314 DROP PROCEDURE POP1;
1315 DROP TABLE t1;
1316 # Concurrent bulk inserts in traditional lock mode
1317 CREATE TABLE t2 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1318 CREATE TABLE t1 (aa INT);
1319 CREATE PROCEDURE POP()
1320 BEGIN
1321 DECLARE i INT DEFAULT 1;
1322 WHILE (i <= 10) DO
1323 INSERT INTO t1 VALUES(i);
1324 SET i = i +1;
1325 END WHILE;
1326 INSERT INTO t2(aa) SELECT * FROM t1;
1327 END //
1328 CALL POP();
1329 CALL POP();
1330 SET @@global.auto_increment_offset = 12;
1331 SET @@global.auto_increment_increment = 50;
1332 TRUNCATE TABLE t1;
1333 TRUNCATE TABLE t2;
1334 CALL POP();
1335 CALL POP();
1336 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1337 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1338 DROP PROCEDURE POP;
1339 DROP TABLE t1;
1340 DROP TABLE t2;
1341 # Concurrent mixed mode inserts in traditonal lock mode
1342 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1343 CREATE PROCEDURE POP2(IN j INT)
1344 BEGIN
1345 DECLARE i INT DEFAULT 1;
1346 WHILE (i<=5) DO
1347 INSERT INTO t1(cc) VALUES(i);
1348 SET i=i+1;
1349 END WHILE;
1350 INSERT INTO t1 VALUES(100+j,20);
1351 WHILE(i>=5 && i<=10) DO
1352 INSERT INTO t1(cc) VALUES(i);
1353 SET i=i+1;
1354 END WHILE;
1355 INSERT INTO t1 VALUES (200+j,25);
1356 WHILE(i>=10 && i<=15) DO
1357 INSERT INTO t1(cc) VALUES(i);
1358 SET i=i+1;
1359 END WHILE;
1360 END //
1361 Warnings:
1362 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
1363 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
1364 CALL POP2(1);
1365 CALL POP2(50);
1366 SELECT COUNT(*) FROM t1;
1367 COUNT(*)
1368 34
1369 SET @@global.auto_increment_offset = 32;
1370 SET @@global.auto_increment_increment = 100;
1371 TRUNCATE TABLE t1;
1372 CALL POP2(1);
1373 CALL POP2(180);
1374 SELECT COUNT(*) FROM t1;
1375 COUNT(*)
1376 34
1377 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1378 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1379 DROP PROCEDURE POP2;
1380 DROP TABLE t1;
1381 # Transactions in traditional lock mode
1382 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1383 START TRANSACTION;
1384 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1385 ROLLBACK;
1386 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1387 SELECT * FROM t1;
1388 pkey
1389 4
1390 5
1391 6
1392 DROP TABLE t1;
1393 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1394 CREATE TABLE t2(aa INT);
1395 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
1396 START TRANSACTION;
1397 INSERT INTO t1(aa) SELECT * FROM t2;
1398 SELECT * FROM t1;
1399 pkey aa
1400 1 1
1401 2 2
1402 3 3
1403 4 4
1404 5 5
1405 6 6
1406 7 7
1407 ROLLBACK;
1408 INSERT INTO t1(aa) SELECT * FROM t2;
1409 SELECT * FROM t1;
1410 pkey aa
1411 8 1
1412 9 2
1413 10 3
1414 11 4
1415 12 5
1416 13 6
1417 14 7
1418 DROP TABLE t1;
1419 DROP TABLE t2;
1420 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1421 START TRANSACTION;
1422 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1423 INSERT INTO t1 VALUES (23),(24);
1424 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1425 INSERT INTO t1 VALUES (34);
1426 SELECT * FROM t1;
1427 pkey
1428 1
1429 2
1430 3
1431 4
1432 23
1433 24
1434 25
1435 26
1436 27
1437 34
1438 ROLLBACK;
1439 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1440 INSERT INTO t1 VALUES (23),(24);
1441 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1442 INSERT INTO t1 VALUES (34);
1443 SELECT * FROM t1;
1444 pkey
1445 23
1446 24
1447 34
1448 35
1449 36
1450 37
1451 38
1452 39
1453 40
1454 41
1455 DROP TABLE t1;
1456 # Simple, Mixed and Bulk inserts with auto increment offset in traditional locking mode
1457 SET @@auto_increment_offset = 17;
1458 SET @@auto_increment_increment = 40;
1459 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1460 START TRANSACTION;
1461 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1462 SELECT * FROM t1;
1463 pkey
1464 17
1465 57
1466 97
1467 ROLLBACK;
1468 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1469 SELECT * FROM t1;
1470 pkey
1471 137
1472 177
1473 217
1474 DROP TABLE t1;
1475 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1476 CREATE TABLE t2(aa INT);
1477 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
1478 START TRANSACTION;
1479 INSERT INTO t1(aa) SELECT * FROM t2;
1480 SELECT * FROM t1;
1481 pkey aa
1482 17 1
1483 57 2
1484 97 3
1485 137 4
1486 177 5
1487 217 6
1488 257 7
1489 ROLLBACK;
1490 INSERT INTO t1(aa) SELECT * FROM t2;
1491 SELECT * FROM t1;
1492 pkey aa
1493 297 1
1494 337 2
1495 377 3
1496 417 4
1497 457 5
1498 497 6
1499 537 7
1500 DROP TABLE t1;
1501 DROP TABLE t2;
1502 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1503 START TRANSACTION;
1504 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1505 INSERT INTO t1 VALUES (23),(24);
1506 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1507 INSERT INTO t1 VALUES (34);
1508 SELECT * FROM t1;
1509 pkey
1510 17
1511 23
1512 24
1513 34
1514 57
1515 97
1516 137
1517 177
1518 217
1519 257
1520 ROLLBACK;
1521 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1522 INSERT INTO t1 VALUES (23),(24);
1523 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1524 INSERT INTO t1 VALUES (34);
1525 SELECT * FROM t1;
1526 pkey
1527 23
1528 24
1529 34
1530 297
1531 337
1532 377
1533 417
1534 457
1535 497
1536 537
1537 DROP TABLE t1;
1538 SET @@auto_increment_offset = @default_auto_increment_offset;
1539 SET @@auto_increment_increment = @default_auto_increment_increment;
1540 # Transactions with concurrency in traditional locking mode
1541 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1542 CREATE PROCEDURE POP1()
1543 BEGIN
1544 DECLARE i INT DEFAULT 1;
1545 WHILE (i<= 10) DO
1546 INSERT INTO t1 VALUES(NULL,i);
1547 SET i = i+1;
1548 END WHILE;
1549 END //
1550 START TRANSACTION;
1551 CALL POP1();
1552 CALL POP1();
1553 SELECT COUNT(*) FROM t1;
1554 COUNT(*)
1555 20
1556 ROLLBACK;
1557 CALL POP1();
1558 CALL POP1();
1559 SELECT COUNT(*) FROM t1;
1560 COUNT(*)
1561 30
1562 DROP PROCEDURE POP1;
1563 DROP TABLE t1;
1564 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1565 CREATE PROCEDURE POP2(IN j INT)
1566 BEGIN
1567 DECLARE i INT DEFAULT 1;
1568 WHILE (i<=5) DO
1569 INSERT INTO t1(cc) VALUES(i);
1570 SET i=i+1;
1571 END WHILE;
1572 INSERT INTO t1 VALUES(100+j,20);
1573 WHILE(i>=5 && i<=10) DO
1574 INSERT INTO t1(cc) VALUES(i);
1575 SET i=i+1;
1576 END WHILE;
1577 INSERT INTO t1 VALUES (200+j,25);
1578 WHILE(i>=10 && i<=15) DO
1579 INSERT INTO t1(cc) VALUES(i);
1580 SET i=i+1;
1581 END WHILE;
1582 END //
1583 Warnings:
1584 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
1585 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
1586 START TRANSACTION;
1587 CALL POP2(1);
1588 CALL POP2(120);
1589 SELECT COUNT(*) FROM t1;
1590 COUNT(*)
1591 34
1592 ROLLBACK;
1593 CALL POP2(1);
1594 CALL POP2(280);
1595 SELECT COUNT(*) FROM t1;
1596 COUNT(*)
1597 51
1598 DROP PROCEDURE POP2;
1599 DROP TABLE t1;
1600 # Crash server in traditional locking mode
1601 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1602 CREATE PROCEDURE POP()
1603 BEGIN
1604 DECLARE i INT DEFAULT 1;
1605 WHILE (i <= 10) DO
1606 INSERT INTO t1(aa) VALUES(i);
1607 SET i = i +1;
1608 END WHILE;
1609 END //
1610 START TRANSACTION;
1611 CALL POP();
1612 # Connection 1
1613 INSERT INTO t1(aa) VALUES (20);
1614 # Switch back to default connection
1615 SET SESSION DEBUG="+d,crash_commit_after";
1616 CR_SERVER_LOST
1617 COMMIT;
1618 ERROR HY000: Lost connection to MySQL server during query
1619 # restart: --innodb_autoinc_lock_mode=0
1620 SELECT COUNT(*) FROM t1;
1621 COUNT(*)
1622 11
1623 START TRANSACTION;
1624 CALL POP();
1625 # Connection 1
1626 INSERT INTO t1(aa) VALUES (20);
1627 # Switch back to default connection
1628 SET SESSION DEBUG="+d,crash_commit_before";
1629 CR_SERVER_LOST
1630 COMMIT;
1631 ERROR HY000: Lost connection to MySQL server during query
1632 # restart: --innodb_autoinc_lock_mode=0
1633 SELECT COUNT(*) FROM t1;
1634 COUNT(*)
1635 12
1636 TRUNCATE TABLE t1;
1637 SET @@auto_increment_offset = 15;
1638 SET @@auto_increment_increment = 30;
1639 START TRANSACTION;
1640 CALL POP();
1641 # Connection 1
1642 SET @@auto_increment_offset = 15;
1643 SET @@auto_increment_increment = 30;
1644 INSERT INTO t1(aa) VALUES (20);
1645 # Switch back to default connection
1646 SET SESSION DEBUG="+d,crash_commit_after";
1647 CR_SERVER_LOST
1648 COMMIT;
1649 ERROR HY000: Lost connection to MySQL server during query
1650 # restart: --innodb_autoinc_lock_mode=0
1651 SELECT COUNT(*) FROM t1;
1652 COUNT(*)
1653 11
1654 SET @@auto_increment_offset = 15;
1655 SET @@auto_increment_increment = 30;
1656 START TRANSACTION;
1657 CALL POP();
1658 # Connection 1
1659 SET @@auto_increment_offset = 15;
1660 SET @@auto_increment_increment = 30;
1661 INSERT INTO t1(aa) VALUES (20);
1662 # Switch back to default connection
1663 SET SESSION DEBUG="+d,crash_commit_before";
1664 CR_SERVER_LOST
1665 COMMIT;
1666 ERROR HY000: Lost connection to MySQL server during query
1667 # restart: --innodb_autoinc_lock_mode=0
1668 SELECT COUNT(*) FROM t1;
1669 COUNT(*)
1670 12
1671 DROP TABLE t1;
1672 DROP PROCEDURE POP;
1673 # Concurrent load inserts in traditional lock mode
1674 CREATE TABLE loads ( pkey INT PRIMARY KEY AUTO_INCREMENT, ww CHAR(50));
1675 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1676 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1677 SELECT * FROM loads;
1678 pkey ww
1679 1 Aarhus
1680 2 Aaron
1681 3 Ababa
1682 4 aback
1683 5 abaft
1684 6 abandon
1685 7 abandoned
1686 8 abandoning
1687 9 abandonment
1688 10 abandons
1689 11 Aarhus
1690 12 Aaron
1691 13 Ababa
1692 14 aback
1693 15 abaft
1694 16 abandon
1695 17 abandoned
1696 18 abandoning
1697 19 abandonment
1698 20 abandons
1699 21 abase
1700 22 abased
1701 23 abasement
1702 24 abasements
1703 25 abases
1704 26 abash
1705 27 abashed
1706 28 abashes
1707 29 abashing
1708 30 abasing
1709 31 abate
1710 32 abated
1711 33 abatement
1712 34 abatements
1713 35 abater
1714 36 abates
1715 37 abating
1716 38 Abba
1717 39 abbe
1718 40 abbey
1719 41 abbeys
1720 42 abbot
1721 43 abbots
1722 44 Abbott
1723 45 abbreviate
1724 46 abbreviated
1725 47 abbreviates
1726 48 abbreviating
1727 49 abbreviation
1728 50 abbreviations
1729 51 Abby
1730 52 abdomen
1731 53 abdomens
1732 54 abdominal
1733 55 abduct
1734 56 abducted
1735 57 abduction
1736 58 abductions
1737 59 abductor
1738 60 abductors
1739 61 abducts
1740 62 Abe
1741 63 abed
1742 64 Abel
1743 65 Abelian
1744 66 Abelson
1745 67 Aberdeen
1746 68 Abernathy
1747 69 aberrant
1748 70 aberration
1749 71 Aarhus
1750 72 Aaron
1751 73 Ababa
1752 74 aback
1753 75 abaft
1754 76 abandon
1755 77 abandoned
1756 78 abandoning
1757 79 abandonment
1758 80 abandons
1759 81 Aarhus
1760 82 Aaron
1761 83 Ababa
1762 84 aback
1763 85 abaft
1764 86 abandon
1765 87 abandoned
1766 88 abandoning
1767 89 abandonment
1768 90 abandons
1769 91 abase
1770 92 abased
1771 93 abasement
1772 94 abasements
1773 95 abases
1774 96 abash
1775 97 abashed
1776 98 abashes
1777 99 abashing
1778 100 abasing
1779 101 abate
1780 102 abated
1781 103 abatement
1782 104 abatements
1783 105 abater
1784 106 abates
1785 107 abating
1786 108 Abba
1787 109 abbe
1788 110 abbey
1789 111 abbeys
1790 112 abbot
1791 113 abbots
1792 114 Abbott
1793 115 abbreviate
1794 116 abbreviated
1795 117 abbreviates
1796 118 abbreviating
1797 119 abbreviation
1798 120 abbreviations
1799 121 Abby
1800 122 abdomen
1801 123 abdomens
1802 124 abdominal
1803 125 abduct
1804 126 abducted
1805 127 abduction
1806 128 abductions
1807 129 abductor
1808 130 abductors
1809 131 abducts
1810 132 Abe
1811 133 abed
1812 134 Abel
1813 135 Abelian
1814 136 Abelson
1815 137 Aberdeen
1816 138 Abernathy
1817 139 aberrant
1818 140 aberration
1819 SET @@global.auto_increment_offset = 12;
1820 SET @@global.auto_increment_increment = 50;
1821 TRUNCATE TABLE loads;
1822 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1823 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1824 SELECT * FROM loads;
1825 pkey ww
1826 12 Aarhus
1827 62 Aaron
1828 112 Ababa
1829 162 aback
1830 212 abaft
1831 262 abandon
1832 312 abandoned
1833 362 abandoning
1834 412 abandonment
1835 462 abandons
1836 512 Aarhus
1837 562 Aaron
1838 612 Ababa
1839 662 aback
1840 712 abaft
1841 762 abandon
1842 812 abandoned
1843 862 abandoning
1844 912 abandonment
1845 962 abandons
1846 1012 abase
1847 1062 abased
1848 1112 abasement
1849 1162 abasements
1850 1212 abases
1851 1262 abash
1852 1312 abashed
1853 1362 abashes
1854 1412 abashing
1855 1462 abasing
1856 1512 abate
1857 1562 abated
1858 1612 abatement
1859 1662 abatements
1860 1712 abater
1861 1762 abates
1862 1812 abating
1863 1862 Abba
1864 1912 abbe
1865 1962 abbey
1866 2012 abbeys
1867 2062 abbot
1868 2112 abbots
1869 2162 Abbott
1870 2212 abbreviate
1871 2262 abbreviated
1872 2312 abbreviates
1873 2362 abbreviating
1874 2412 abbreviation
1875 2462 abbreviations
1876 2512 Abby
1877 2562 abdomen
1878 2612 abdomens
1879 2662 abdominal
1880 2712 abduct
1881 2762 abducted
1882 2812 abduction
1883 2862 abductions
1884 2912 abductor
1885 2962 abductors
1886 3012 abducts
1887 3062 Abe
1888 3112 abed
1889 3162 Abel
1890 3212 Abelian
1891 3262 Abelson
1892 3312 Aberdeen
1893 3362 Abernathy
1894 3412 aberrant
1895 3462 aberration
1896 3512 Aarhus
1897 3562 Aaron
1898 3612 Ababa
1899 3662 aback
1900 3712 abaft
1901 3762 abandon
1902 3812 abandoned
1903 3862 abandoning
1904 3912 abandonment
1905 3962 abandons
1906 4012 Aarhus
1907 4062 Aaron
1908 4112 Ababa
1909 4162 aback
1910 4212 abaft
1911 4262 abandon
1912 4312 abandoned
1913 4362 abandoning
1914 4412 abandonment
1915 4462 abandons
1916 4512 abase
1917 4562 abased
1918 4612 abasement
1919 4662 abasements
1920 4712 abases
1921 4762 abash
1922 4812 abashed
1923 4862 abashes
1924 4912 abashing
1925 4962 abasing
1926 5012 abate
1927 5062 abated
1928 5112 abatement
1929 5162 abatements
1930 5212 abater
1931 5262 abates
1932 5312 abating
1933 5362 Abba
1934 5412 abbe
1935 5462 abbey
1936 5512 abbeys
1937 5562 abbot
1938 5612 abbots
1939 5662 Abbott
1940 5712 abbreviate
1941 5762 abbreviated
1942 5812 abbreviates
1943 5862 abbreviating
1944 5912 abbreviation
1945 5962 abbreviations
1946 6012 Abby
1947 6062 abdomen
1948 6112 abdomens
1949 6162 abdominal
1950 6212 abduct
1951 6262 abducted
1952 6312 abduction
1953 6362 abductions
1954 6412 abductor
1955 6462 abductors
1956 6512 abducts
1957 6562 Abe
1958 6612 abed
1959 6662 Abel
1960 6712 Abelian
1961 6762 Abelson
1962 6812 Aberdeen
1963 6862 Abernathy
1964 6912 aberrant
1965 6962 aberration
1966 DROP TABLE loads;
1967 # restart:
1968 #
1969 # Bug#30765952 AUTO INCREMENT VALUE DIDN'T RESET CORRECTLY
1970 #
1971 SET SESSION information_schema_stats_expiry=0;
1972 CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT DEFAULT 0, PRIMARY KEY (a))
1973 ENGINE=InnoDB;
1974 INSERT INTO t1(`b`) VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
1975 ALTER TABLE t1 MODIFY a INT;
1976 ALTER TABLE t1 DROP INDEX `PRIMARY`, ADD COLUMN c INT NOT NULL AUTO_INCREMENT,
1977 ADD KEY (c);
1978 DROP TABLE t1;
1979 SET @@SESSION.information_schema_stats_expiry = DEFAULT;
1980 #
1981 # Bug#33419246 AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1
1982 # Bug#34418341 AUTOINC counter resets to 1 after ALTER TABLE following by restart
1983 # Bug#34951259 alter table... auto_increment=1 does not work as expected
1984 #
1985 CREATE TABLE `t1` (`id` INT NOT NULL PRIMARY KEY);
1986 INSERT INTO `t1` (`id`) VALUES (1), (2), (3);
1987 ALTER TABLE `t1` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
1988 SHOW CREATE TABLE `t1`;
1989 Table Create Table
1990 t1 CREATE TABLE `t1` (
1991 `id` int NOT NULL AUTO_INCREMENT,
1992 PRIMARY KEY (`id`)
1993 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1994 select `AUTO_INCREMENT` from information_schema.tables where table_name='t1';
1995 AUTO_INCREMENT
1996 4
1997 # restart:
1998 SHOW CREATE TABLE `t1`;
1999 Table Create Table
2000 t1 CREATE TABLE `t1` (
2001 `id` int NOT NULL AUTO_INCREMENT,
2002 PRIMARY KEY (`id`)
2003 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2004 select `AUTO_INCREMENT` from information_schema.tables where table_name='t1';
2005 AUTO_INCREMENT
2006 4
2007 SET SESSION information_schema_stats_expiry=0;
2008 INSERT INTO `t1` (`id`) VALUES (NULL);
2009 SELECT * FROM `t1`;
2010 id
2011 1
2012 2
2013 3
2014 4
2015 SHOW CREATE TABLE `t1`;
2016 Table Create Table
2017 t1 CREATE TABLE `t1` (
2018 `id` int NOT NULL AUTO_INCREMENT,
2019 PRIMARY KEY (`id`)
2020 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2021 select `AUTO_INCREMENT` from information_schema.tables where table_name='t1';
2022 AUTO_INCREMENT
2023 5
2024 SET SESSION information_schema_stats_expiry=DEFAULT;
2025 DROP TABLE t1;
2026 CREATE TABLE `t1` (`id` INT NOT NULL PRIMARY KEY);
2027 INSERT INTO `t1` (`id`) VALUES (1), (2), (3);
2028 ALTER TABLE `t1` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
2029 ALTER TABLE `t1` AUTO_INCREMENT=1;
2030 SHOW CREATE TABLE `t1`;
2031 Table Create Table
2032 t1 CREATE TABLE `t1` (
2033 `id` int NOT NULL AUTO_INCREMENT,
2034 PRIMARY KEY (`id`)
2035 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2036 INSERT INTO `t1` (`id`) VALUES (NULL);
2037 DROP TABLE t1;
2038 CREATE TABLE `t1` (`id` INT NOT NULL PRIMARY KEY);
2039 INSERT INTO `t1` (`id`) VALUES (1), (2), (3);
2040 show create table t1;
2041 Table Create Table
2042 t1 CREATE TABLE `t1` (
2043 `id` int NOT NULL,
2044 PRIMARY KEY (`id`)
2045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2046 SET SESSION debug= "+d, crash_create_after_autoinc_persisted_update";
2047 ALTER TABLE `t1` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
2048 ERROR HY000: Lost connection to MySQL server during query
2049 SHOW CREATE TABLE `t1`;
2050 Table Create Table
2051 t1 CREATE TABLE `t1` (
2052 `id` int NOT NULL,
2053 PRIMARY KEY (`id`)
2054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2055 DROP TABLE t1;
2056 CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
2057 set global innodb_checkpoint_disabled=1;
2058 INSERT INTO t1 VALUES (110000);
2059 INSERT INTO t1 VALUES ();
2060 SHOW CREATE TABLE t1;
2061 Table Create Table
2062 t1 CREATE TABLE `t1` (
2063 `id` int NOT NULL AUTO_INCREMENT,
2064 PRIMARY KEY (`id`)
2065 ) ENGINE=InnoDB AUTO_INCREMENT=110002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2066 DELETE FROM t1;
2067 ALTER TABLE t1 AUTO_INCREMENT=100000;
2068 INSERT INTO t1 VALUES ();
2069 SHOW CREATE TABLE t1;
2070 Table Create Table
2071 t1 CREATE TABLE `t1` (
2072 `id` int NOT NULL AUTO_INCREMENT,
2073 PRIMARY KEY (`id`)
2074 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2075 # Kill the server
2076 # restart:
2077 SHOW CREATE TABLE t1;
2078 Table Create Table
2079 t1 CREATE TABLE `t1` (
2080 `id` int NOT NULL AUTO_INCREMENT,
2081 PRIMARY KEY (`id`)
2082 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2083 DROP TABLE t1;