Debian Codegraph @

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

raw
1 ##########################################################################################
2 # The aim of this test is to improve test coverage for auto_increment in InnoDB. #
3 # #
4 # The variable innodb_autoinc_lock_mode has 3 values #
5 # #
6 # 1. innodb_autoinc_lock_mode = 0 : Traditional lock mode #
7 # 2. innodb_autoinc_lock_mode = 1 : Consecutive lock mode #
8 # 3. innodb_autoinc_lock_mode = 2 : Interleaved lock mode (Default mode) #
9 # #
10 # The following scenarios are tested for each of the innodb_autoinc_lock_modes : #
11 # #
12 # 1. Simple inserts #
13 # 2. Changing auto_increment_offset and auto_increment_increment #
14 # 3. Bulk inserts #
15 # 4. Mixed mode inserts #
16 # 5. Concurrency testing with different insert types #
17 # 6. Transactions #
18 # #
19 # Creation Date : 2015-12-14 #
20 # Author : Deepa Dixit #
21 # #
22 # #
23 ##########################################################################################
24
25 # innodb_autoinc_lock_mode is not a dynamic variable and server needs to be restarted
26 # to change its value
27
28 # Valgrind can hang or return spurious messages on DBUG_SUICIDE
29 --source include/not_valgrind.inc
30
31 # Avoid CrashReporter popup on Mac
32 --source include/not_crashrep.inc
33 --source include/have_debug.inc
34
35 ##########################################################################################
36 # Interleaved lock mode (innodb_autoinc_lock_mode = 2 [default]) #
37 ##########################################################################################
38 SET @default_innodb_autoinc_lock_mode = @@global.innodb_autoinc_lock_mode;
39
40 SELECT @@global.innodb_autoinc_lock_mode;
41
42 SET @default_auto_increment_offset = @@global.auto_increment_offset;
43 SET @default_auto_increment_increment = @@global.auto_increment_increment;
44
45 --echo # Bulk inserts in interleaved lock mode
46 CREATE TABLE t3(cc INT);
47 INSERT INTO t3 VALUES (10),(20),(30),(40),(50),(60),(70),(80);
48 CREATE TABLE t4(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
49 INSERT INTO t4(cc) SELECT * FROM t3;
50
51 SHOW CREATE TABLE t4;
52
53 INSERT INTO t4(cc) SELECT * FROM t3;
54
55 # Gaps expected between successive bulk insert statements
56 SHOW CREATE TABLE t4;
57
58 DROP TABLE t4;
59 DROP TABLE t3;
60
61 #########################
62 # Concurrency testing #
63 #########################
64 connect (conn1,localhost,root,,);
65 connect (conn2,localhost,root,,);
66
67
68 --echo # Concurrent simple inserts in interleaved lock mode
69 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
70
71 DELIMITER //;
72
73 CREATE PROCEDURE POP1()
74 BEGIN
75 DECLARE i INT DEFAULT 1;
76 WHILE (i<= 10) DO
77 INSERT INTO t1 VALUES(NULL,i);
78 IF ((i%2) =0 ) THEN
79 DELETE FROM t1 WHERE bb=i;
80 END IF;
81 SET i = i+1;
82 END WHILE;
83 END //
84 DELIMITER ;//
85
86 # connect to connection 1
87 --connection conn1
88
89 --send CALL POP1()
90
91 # connect to connection 2
92 --connection conn2
93
94 CALL POP1();
95
96 # Switch back to connection 1
97 --connection conn1
98 --reap
99
100 --connection conn2
101 SELECT COUNT(*) FROM t1;
102
103 --connection default
104 --disconnect conn1
105 --disconnect conn2
106
107 # Test with auto increment offset
108
109 SET @@global.auto_increment_offset = 9;
110 SET @@global.auto_increment_increment = 10;
111
112 TRUNCATE TABLE t1;
113
114 connect (conn1,localhost,root,,);
115 connect (conn2,localhost,root,,);
116
117 # connect to connection 1
118 --connection conn1
119
120 --send CALL POP1()
121
122 # connect to connection 2
123 --connection conn2
124
125 CALL POP1();
126
127 # Switch back to connection 1
128 --connection conn1
129 --reap
130
131 --connection conn2
132 SELECT COUNT(*) FROM t1;
133
134 --connection default
135 --disconnect conn1
136 --disconnect conn2
137 SET @@global.auto_increment_offset = @default_auto_increment_offset;
138 SET @@global.auto_increment_increment = @default_auto_increment_increment;
139 DROP PROCEDURE POP1;
140 DROP TABLE t1;
141
142
143 --echo # Concurrent bulk inserts in interleaved lock mode
144
145
146 connect (conn1,localhost,root,,);
147 connect (conn2,localhost,root,,);
148 CREATE TABLE t2 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
149 CREATE TABLE t1 (aa INT);
150
151 DELIMITER //;
152
153 CREATE PROCEDURE POP()
154 BEGIN
155 DECLARE i INT DEFAULT 1;
156 WHILE (i <= 10) DO
157 INSERT INTO t1 VALUES(i);
158 SET i = i +1;
159 END WHILE;
160 INSERT INTO t2(aa) SELECT * FROM t1;
161
162 END //
163 DELIMITER ;//
164
165
166 # Connect to connection 1
167 --connection conn1
168
169 --send CALL POP()
170
171
172 # Connect to connection 2
173 --connection conn2
174
175 CALL POP();
176
177 # Switch back to connection 1
178 --connection conn1
179 --reap
180
181 # Non-deterministic results
182
183
184 --connection default
185 --disconnect conn1
186 --disconnect conn2
187
188 # Test with auto increment offset
189
190 SET @@global.auto_increment_offset = 7;
191 SET @@global.auto_increment_increment = 15;
192
193 TRUNCATE TABLE t1;
194 TRUNCATE TABLE t2;
195
196 connect (conn1,localhost,root,,);
197 connect (conn2,localhost,root,,);
198
199 # Connect to connection 1
200 --connection conn1
201
202 --send CALL POP()
203
204
205 # Connect to connection 2
206 --connection conn2
207
208 CALL POP();
209
210 # Switch back to connection 1
211 --connection conn1
212 --reap
213
214 # Non-deterministic results
215
216 --connection default
217 --disconnect conn1
218 --disconnect conn2
219 SET @@global.auto_increment_increment = @default_auto_increment_increment;
220 SET @@global.auto_increment_offset = @default_auto_increment_offset;
221 DROP TABLE t1;
222 DROP TABLE t2;
223 DROP PROCEDURE POP;
224
225 --echo # Concurrent mixed mode inserts in interleaved lock mode
226
227
228 connect (conn1,localhost,root,,);
229 connect (conn2,localhost,root,,);
230 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
231
232 DELIMITER //;
233
234 CREATE PROCEDURE POP2(IN j INT)
235 BEGIN
236 DECLARE i INT DEFAULT 1;
237 WHILE (i<=5) DO
238 INSERT INTO t1(cc) VALUES(i);
239 SET i=i+1;
240 END WHILE;
241 INSERT INTO t1 VALUES(100+j,20);
242 WHILE(i>=5 && i<=10) DO
243 INSERT INTO t1(cc) VALUES(i);
244 SET i=i+1;
245 END WHILE;
246 INSERT INTO t1 VALUES (200+j,25);
247 WHILE(i>=10 && i<=15) DO
248 INSERT INTO t1(cc) VALUES(i);
249 SET i=i+1;
250 END WHILE;
251
252 END //
253
254 DELIMITER ;//
255
256 # Connect to connection 1
257 --connection conn1
258
259 --send CALL POP2(1)
260
261 # Connect to connection 2
262 --connection conn2
263 CALL POP2(120);
264
265 # Switch back to connection 1
266 --connection conn1
267 --reap
268
269 # Gaps in the auto increment values expected and values may vary
270 SELECT COUNT(*) FROM t1;
271
272 --connection default
273 --disconnect conn1
274 --disconnect conn2
275
276 # Test with auto increment offset
277
278 SET @@global.auto_increment_offset = 13;
279 SET @@global.auto_increment_increment = 43;
280
281 TRUNCATE TABLE t1;
282
283 connect (conn1,localhost,root,,);
284 connect (conn2,localhost,root,,);
285
286 # Connect to connection 1
287 --connection conn1
288
289 --send CALL POP2(1)
290
291 # Connect to connection 2
292 --connection conn2
293 CALL POP2(280);
294
295 # Switch back to connection 1
296 --connection conn1
297 --reap
298
299 # Gaps in the auto increment values expected and values may vary
300 SELECT COUNT(*) FROM t1;
301
302 --connection default
303 --disconnect conn1
304 --disconnect conn2
305 SET @@global.auto_increment_offset = @default_auto_increment_offset;
306 SET @@global.auto_increment_increment = @default_auto_increment_increment;
307 DROP PROCEDURE POP2;
308 DROP TABLE t1;
309
310
311 #########################
312 # Transactions #
313 #########################
314
315 --echo # Transactions in interleaved lock mode
316
317 # Simple inserts
318 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
319
320 START TRANSACTION;
321 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
322 ROLLBACK;
323
324 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
325 # Gaps expected in the sequence of autoinc numbers
326 SELECT * FROM t1;
327
328 DROP TABLE t1;
329
330 # Bulk inserts
331
332 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
333 CREATE TABLE t2(aa INT);
334
335 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
336 START TRANSACTION;
337 INSERT INTO t1(aa) SELECT * FROM t2;
338
339 SELECT * FROM t1;
340
341 ROLLBACK;
342
343 INSERT INTO t1(aa) SELECT * FROM t2;
344
345 SELECT * FROM t1;
346
347 DROP TABLE t1;
348 DROP TABLE t2;
349
350 # Mixed mode inserts
351
352 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
353
354 START TRANSACTION;
355 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
356 INSERT INTO t1 VALUES (23),(24);
357 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
358 INSERT INTO t1 VALUES (34);
359
360 SELECT * FROM t1;
361 ROLLBACK;
362
363 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
364 INSERT INTO t1 VALUES (23),(24);
365 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
366 INSERT INTO t1 VALUES (34);
367
368 SELECT * FROM t1;
369
370 DROP TABLE t1;
371
372 --echo # Simple, Mixed and Bulk inserts with auto increment offset in interleaved locking mode
373
374 SET @@auto_increment_offset = 17;
375 SET @@auto_increment_increment = 40;
376
377 # Simple inserts with auto increment offset
378 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
379
380 START TRANSACTION;
381 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
382 SELECT * FROM t1;
383 ROLLBACK;
384
385 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
386
387 # Gaps expected in the sequence of autoinc numbers
388 SELECT * FROM t1;
389
390 DROP TABLE t1;
391
392 # Bulk inserts with auto increment offset
393
394 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
395 CREATE TABLE t2(aa INT);
396
397 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
398 START TRANSACTION;
399 INSERT INTO t1(aa) SELECT * FROM t2;
400
401 SELECT * FROM t1;
402
403 ROLLBACK;
404
405 INSERT INTO t1(aa) SELECT * FROM t2;
406
407 SELECT * FROM t1;
408
409 DROP TABLE t1;
410 DROP TABLE t2;
411
412 # Mixed mode inserts with auto increment offset
413
414 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
415
416 START TRANSACTION;
417 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
418 INSERT INTO t1 VALUES (23),(24);
419 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
420 INSERT INTO t1 VALUES (34);
421
422 SELECT * FROM t1;
423 ROLLBACK;
424
425 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
426 INSERT INTO t1 VALUES (23),(24);
427 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
428 INSERT INTO t1 VALUES (34);
429
430 SELECT * FROM t1;
431
432 DROP TABLE t1;
433
434 SET @@auto_increment_offset = @default_auto_increment_offset;
435 SET @@auto_increment_increment = @default_auto_increment_increment;
436
437 --echo # Transactions with concurrency in interleaved locking mode
438
439 # Simple inserts with concurrency
440
441 connect (conn1,localhost,root,,);
442 connect (conn2,localhost,root,,);
443
444 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
445
446 DELIMITER //;
447
448 CREATE PROCEDURE POP1()
449 BEGIN
450 DECLARE i INT DEFAULT 1;
451 WHILE (i<= 10) DO
452 INSERT INTO t1 VALUES(NULL,i);
453 SET i = i+1;
454 END WHILE;
455 END //
456 DELIMITER ;//
457
458 # connect to connection 1
459 --connection conn1
460
461 START TRANSACTION;
462 --send CALL POP1()
463
464 # connect to connection 2
465 --connection conn2
466
467 CALL POP1();
468
469 # Switch back to connection 1
470 --connection conn1
471 --reap
472
473 # Results may vary
474 SELECT COUNT(*) FROM t1;
475
476 ROLLBACK;
477
478 --send CALL POP1()
479
480 # connect to connection 2
481 --connection conn2
482
483 CALL POP1();
484
485 # Switch back to connection 1
486 --connection conn1
487 --reap
488
489 SELECT COUNT(*) FROM t1;
490
491 --connection default
492 --disconnect conn1
493 --disconnect conn2
494
495 DROP PROCEDURE POP1;
496 DROP TABLE t1;
497
498
499 # Mixed mode inserts with concurrency
500
501 connect (conn1,localhost,root,,);
502 connect (conn2,localhost,root,,);
503
504 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
505
506 DELIMITER //;
507
508 CREATE PROCEDURE POP2(IN j INT)
509 BEGIN
510 DECLARE i INT DEFAULT 1;
511 WHILE (i<=5) DO
512 INSERT INTO t1(cc) VALUES(i);
513 SET i=i+1;
514 END WHILE;
515 INSERT INTO t1 VALUES(100+j,20);
516 WHILE(i>=5 && i<=10) DO
517 INSERT INTO t1(cc) VALUES(i);
518 SET i=i+1;
519 END WHILE;
520 INSERT INTO t1 VALUES (200+j,25);
521 WHILE(i>=10 && i<=15) DO
522 INSERT INTO t1(cc) VALUES(i);
523 SET i=i+1;
524 END WHILE;
525
526 END //
527
528 DELIMITER ;//
529
530 # Connect to connection 1
531 --connection conn1
532
533 START TRANSACTION;
534 --send CALL POP2(1)
535
536 # Connect to connection 2
537 --connection conn2
538
539 CALL POP2(60);
540
541 # Switch back to connection 1
542 --connection conn1
543 --reap
544
545 SELECT COUNT(*) FROM t1;
546
547 ROLLBACK;
548
549 --send CALL POP2(1)
550
551 # Connect to connection 2
552 --connection conn2
553
554 CALL POP2(280);
555
556 # Switch back to connection 1
557 --connection conn1
558 --reap
559
560 SELECT COUNT(*) FROM t1;
561
562 --connection default
563 --disconnect conn1
564 --disconnect conn2
565
566 DROP PROCEDURE POP2;
567 DROP TABLE t1;
568
569 #########################
570 # Crash Recovery #
571 #########################
572 --echo # Crash server in interleaved lock mode
573
574 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
575
576 DELIMITER //;
577
578 CREATE PROCEDURE POP()
579 BEGIN
580 DECLARE i INT DEFAULT 1;
581 WHILE (i <= 10) DO
582 INSERT INTO t1(aa) VALUES(i);
583 SET i = i +1;
584 END WHILE;
585
586 END //
587
588 DELIMITER ;//
589
590
591 START TRANSACTION;
592
593 CALL POP();
594
595 # Server will crash on next commit
596 --source include/expect_crash.inc
597 SET SESSION DEBUG="+d,crash_commit_after";
598 --echo CR_SERVER_LOST
599 --error 2013
600
601 COMMIT;
602
603 --source include/start_mysqld.inc
604
605 # Results may vary
606 SELECT COUNT(*) FROM t1;
607
608 START TRANSACTION;
609
610 CALL POP();
611
612
613 # Server will crash on next commit
614 --source include/expect_crash.inc
615 SET SESSION DEBUG="+d,crash_commit_before";
616 --echo CR_SERVER_LOST
617 --error 2013
618
619 COMMIT;
620
621 --source include/start_mysqld.inc
622
623 # Results may vary
624 SELECT COUNT(*) FROM t1;
625
626 TRUNCATE TABLE t1;
627
628 # Test with auto increment offset
629
630 SET @@auto_increment_offset = 20;
631 SET @@auto_increment_increment = 40;
632
633
634 START TRANSACTION;
635
636 CALL POP();
637
638
639 # Server will crash on next commit
640 --source include/expect_crash.inc
641 SET SESSION DEBUG="+d,crash_commit_after";
642 --echo CR_SERVER_LOST
643 --error 2013
644
645 COMMIT;
646
647 --source include/start_mysqld.inc
648
649 # Results may vary
650 SELECT COUNT(*) FROM t1;
651
652 START TRANSACTION;
653
654 CALL POP();
655
656 # Server will crash on next commit
657 --source include/expect_crash.inc
658 SET SESSION DEBUG="+d,crash_commit_before";
659 --echo CR_SERVER_LOST
660 --error 2013
661
662 COMMIT;
663
664 --source include/start_mysqld.inc
665
666 # Results may vary
667 SELECT COUNT(*) FROM t1;
668
669 DROP TABLE t1;
670 DROP PROCEDURE POP;
671
672 #######################################
673 # Load Testing with concurrency #
674 #######################################
675 connect (conn1,localhost,root,,);
676 connect (conn2,localhost,root,,);
677
678 --echo # Concurrent load inserts in interleaved lock mode
679 CREATE TABLE loads ( pkey INT PRIMARY KEY AUTO_INCREMENT, ww CHAR(50));
680
681 # Connect to connection 1
682 --connection conn1
683
684 --send LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww)
685
686 # Connect to connection 2
687 --connection conn2
688
689 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
690
691 # Switch back to connection 1
692 --connection conn1
693
694 --reap
695
696 # Order of the values may vary
697 SELECT MAX(pkey) FROM loads;
698
699 --connection default
700 --disconnect conn1
701 --disconnect conn2
702
703 # Test with auto increment offset
704
705 SET @@global.auto_increment_offset = 12;
706 SET @@global.auto_increment_increment = 50;
707
708 TRUNCATE TABLE loads;
709
710 connect (conn1,localhost,root,,);
711 connect (conn2,localhost,root,,);
712
713 # Connect to connection 1
714 --connection conn1
715
716 --send LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww)
717
718 # Connect to connection 2
719 --connection conn2
720
721
722
723 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
724
725 # Switch back to connection 1
726 --connection conn1
727
728 --reap
729
730 # Order of the values may vary
731 SELECT MAX(pkey) FROM loads;
732
733 --connection default
734 --disconnect conn1
735 --disconnect conn2
736
737 DROP TABLE loads;
738 ##########################################################################################
739 # Consecutive lock mode (innodb_autoinc_lock_mode = 1) #
740 ##########################################################################################
741 --echo # Restart server with innodb_autoinc_lock_mode = 1
742 let $restart_parameters=restart: --innodb_autoinc_lock_mode=1;
743 --source include/restart_mysqld.inc
744
745 # Check whether the lock mode has been changed
746 SELECT @@global.innodb_autoinc_lock_mode;
747
748 SET @default_auto_increment_offset = @@global.auto_increment_offset;
749 SET @default_auto_increment_increment = @@global.auto_increment_increment;
750
751 # Create a table with auto_increment key and insert values
752 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT);
753 INSERT INTO t1 values (NULL,3),
754 (NULL,6),
755 (NULL,9),
756 (NULL,12),
757 (NULL,15);
758 SELECT * FROM t1;
759
760 # Update last row in the table
761 UPDATE t1 SET pkey = 6
762 WHERE a = 15;
763
764 SELECT * FROM t1;
765
766 # As of MySQL Version 5.8.0 and above there is no duplicate entry error
767 INSERT INTO t1 VALUES (NULL,18);
768 SELECT * FROM t1;
769
770 # Delete a row from table
771 DELETE FROM t1 WHERE a = 18;
772
773 # Insert another row
774 INSERT INTO t1 VALUES (NULL,18);
775
776 SELECT * FROM t1;
777
778 # Change auto_increment_offset and auto_increment_increment
779 SET @default_auto_increment_increment = @@auto_increment_increment;
780 SET @@auto_increment_increment=10;
781 SELECT @@session.auto_increment_increment;
782 INSERT INTO t1 VALUES (NULL,21),
783 (NULL,24),
784 (NULL,27);
785
786 SELECT * FROM t1;
787
788 SET @default_auto_increment_offset = @@auto_increment_offset;
789 SET @@auto_increment_offset=5;
790 SELECT @@auto_increment_offset;
791
792 INSERT INTO t1 VALUES (NULL,30),
793 (NULL,33),
794 (NULL,36);
795
796 SELECT * FROM t1;
797
798 # If auto_increment_offset is greater than the auto_increment_increment
799 # the auto_increment_offset value is ignored
800
801 SET @@auto_increment_offset =150;
802 SELECT @@auto_increment_offset;
803
804 INSERT INTO t1 VALUES (NULL,39),
805 (NULL,42),
806 (NULL,45);
807
808 SELECT * FROM t1;
809
810 SET @@auto_increment_increment = @default_auto_increment_increment;
811 SET @@auto_increment_offset = @default_auto_increment_offset;
812 DROP TABLE t1;
813
814 --echo # Mixed mode inserts in consecutive lock mode
815 CREATE TABLE t2( pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT );
816 INSERT INTO t2 VALUES (NULL,10),
817 (12,20),
818 (NULL,30),
819 (NULL,40),
820 (40,50),
821 (NULL,60),
822 (NULL,70);
823 SELECT * FROM t2;
824 DROP TABLE t2;
825
826 --echo # Bulk inserts in consecutive lock mode
827 # No gaps in the autoinc values till the end of statement
828 # Gaps may occur between two insert statements
829 CREATE TABLE t3(cc INT);
830 INSERT INTO t3 VALUES (10),(20),(30),(40),(50),(60),(70),(80);
831 CREATE TABLE t4(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
832 INSERT INTO t4(cc) SELECT * FROM t3;
833 SHOW CREATE TABLE t4;
834
835 INSERT INTO t4(cc) SELECT * FROM t3;
836 SHOW CREATE TABLE t4;
837
838 DROP TABLE t4;
839 DROP TABLE t3;
840
841 #########################
842 # Concurrency testing #
843 #########################
844 connect (conn1,localhost,root,,);
845 connect (conn2,localhost,root,,);
846
847 --echo # Concurrent simple inserts in consecutive lock mode
848 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
849
850 DELIMITER //;
851
852 CREATE PROCEDURE POP1()
853 BEGIN
854 DECLARE i INT DEFAULT 1;
855 WHILE (i<= 10) DO
856 INSERT INTO t1 VALUES(NULL,i);
857 IF ( (i%2) = 0 ) THEN
858 DELETE FROM t1 WHERE bb=i;
859 END IF;
860 SET i = i+1;
861 END WHILE;
862 END //
863 DELIMITER ;//
864
865 # connect to connection 1
866 --connection conn1
867
868 --send CALL POP1()
869
870 # connect to connection 2
871 --connection conn2
872
873 CALL POP1();
874
875 # Switch back to connection 1
876 --connection conn1
877 --reap
878
879 --connection conn2
880 # Results may vary due to different rows being deleted
881 SELECT COUNT(*) FROM t1;
882
883 # Inserts with auto increment offset
884 --connection default
885 --disconnect conn1
886 --disconnect conn2
887 SET @@global.auto_increment_offset=10;
888 SET @@global.auto_increment_increment=25;
889
890 TRUNCATE TABLE t1;
891
892
893 connect (conn1,localhost,root,,);
894 connect (conn2,localhost,root,,);
895 # connect to connection1
896 --connection conn1
897
898 --send CALL POP1()
899
900 # connect to connection 2
901 --connection conn2
902
903 CALL POP1();
904
905 # Switch back to connection 1
906 --connection conn1
907 --reap
908
909 --connection conn2
910 SELECT COUNT(*) FROM t1;
911
912
913 --connection default
914 --disconnect conn1
915 --disconnect conn2
916 SET @@global.auto_increment_offset = @default_auto_increment_offset;
917 SET @@global.auto_increment_increment = @default_auto_increment_increment;
918 DROP PROCEDURE POP1;
919 DROP TABLE t1;
920
921 --echo # Concurrent bulk inserts in consecutive lock mode
922
923 connect (conn1,localhost,root,,);
924 connect (conn2,localhost,root,,);
925 CREATE TABLE t2 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
926 CREATE TABLE t1 (aa INT);
927
928
929 DELIMITER //;
930
931 CREATE PROCEDURE POP()
932 BEGIN
933 DECLARE i INT DEFAULT 1;
934 WHILE (i <= 10) DO
935 INSERT INTO t1 VALUES(i);
936 SET i = i +1;
937 END WHILE;
938 INSERT INTO t2(aa) SELECT * FROM t1;
939
940 END //
941
942 DELIMITER ;//
943
944 # Connect to connection 1
945 --connection conn1
946
947 --send CALL POP()
948
949
950 # Connect to connection 2
951 --connection conn2
952
953 CALL POP();
954
955 # Switch back to connection1
956 --connection conn1
957 --reap
958
959 # Non-deterministic results
960
961 --connection default
962 --disconnect conn1
963 --disconnect conn2
964
965 # Test with auto_increment_offset too
966 SET @@global.auto_increment_offset = 16;
967 SET @@global.auto_increment_increment = 32;
968
969 TRUNCATE TABLE t1;
970 TRUNCATE TABLE t2;
971
972 connect (conn1,localhost,root,,);
973 connect (conn2,localhost,root,,);
974
975 # Connect to connection 1
976 --connection conn1
977
978 --send CALL POP()
979
980
981 # Connect to connection 2
982 --connection conn2
983
984 CALL POP();
985
986 # Switch back to connection1
987 --connection conn1
988 --reap
989
990 # Non-deterministic result
991
992 --connection default
993 --disconnect conn1
994 --disconnect conn2
995
996 SET @@global.auto_increment_offset = @default_auto_increment_offset;
997 SET @@global.auto_increment_increment = @default_auto_increment_increment;
998 DROP PROCEDURE POP;
999 DROP TABLE t1;
1000 DROP TABLE t2;
1001
1002 --echo # Concurrent mixed mode inserts in consecutive lock mode
1003
1004
1005 connect (conn1,localhost,root,,);
1006 connect (conn2,localhost,root,,);
1007
1008 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1009
1010 DELIMITER //;
1011
1012 CREATE PROCEDURE POP2(IN j INT)
1013 BEGIN
1014 DECLARE i INT DEFAULT 1;
1015 WHILE (i<=5) DO
1016 INSERT INTO t1(cc) VALUES(i);
1017 SET i=i+1;
1018 END WHILE;
1019 INSERT INTO t1 VALUES(100+j,20);
1020 WHILE(i>=5 && i<=10) DO
1021 INSERT INTO t1(cc) VALUES(i);
1022 SET i=i+1;
1023 END WHILE;
1024 INSERT INTO t1 VALUES (200+j,25);
1025 WHILE(i>=10 && i<=15) DO
1026 INSERT INTO t1(cc) VALUES(i);
1027 SET i=i+1;
1028 END WHILE;
1029
1030 END //
1031
1032 DELIMITER ;//
1033
1034 # Connect to connection 1
1035 --connection conn1
1036
1037 --send CALL POP2(1)
1038
1039 # Connect to connection 2
1040 --connection conn2
1041
1042 CALL POP2(70);
1043
1044 # Switch back to connection 1
1045 --connection conn1
1046 --reap
1047
1048
1049 --connection conn2
1050 SELECT COUNT(*) FROM t1;
1051
1052 --connection default
1053 --disconnect conn1
1054 --disconnect conn2
1055
1056 # Test with auto increment offset
1057 SET @@global.auto_increment_offset = 3;
1058 SET @@global.auto_increment_increment = 4;
1059 TRUNCATE TABLE t1;
1060
1061 connect (conn1,localhost,root,,);
1062 connect (conn2,localhost,root,,);
1063
1064 # Connect to connection 1
1065 --connection conn1
1066
1067 --send CALL POP2(1)
1068
1069 # Connect to connection 2
1070 --connection conn2
1071
1072 CALL POP2(80);
1073
1074 # Switch back to connection 1
1075 --connection conn1
1076 --reap
1077
1078
1079 --connection conn2
1080 SELECT COUNT(*) FROM t1;
1081
1082 --connection default
1083 --disconnect conn1
1084 --disconnect conn2
1085 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1086 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1087 DROP PROCEDURE POP2;
1088 DROP TABLE t1;
1089
1090
1091 #########################
1092 # Transactions #
1093 #########################
1094
1095 --echo # Transactions in consecutive lock mode
1096
1097 # Simple inserts
1098 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1099
1100 START TRANSACTION;
1101 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1102 SELECT * FROM t1;
1103 ROLLBACK;
1104
1105 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1106
1107 # Gaps expected in the sequence of autoinc numbers
1108 SELECT * FROM t1;
1109
1110 DROP TABLE t1;
1111
1112 # Bulk inserts
1113
1114 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1115 CREATE TABLE t2(aa INT);
1116
1117 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
1118 START TRANSACTION;
1119 INSERT INTO t1(aa) SELECT * FROM t2;
1120
1121 SELECT * FROM t1;
1122
1123 ROLLBACK;
1124
1125 INSERT INTO t1(aa) SELECT * FROM t2;
1126
1127 SELECT * FROM t1;
1128
1129 DROP TABLE t1;
1130 DROP TABLE t2;
1131
1132 # Mixed mode inserts
1133
1134 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1135
1136 START TRANSACTION;
1137 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1138 INSERT INTO t1 VALUES (23),(24);
1139 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1140 INSERT INTO t1 VALUES (34);
1141
1142 SELECT * FROM t1;
1143 ROLLBACK;
1144
1145 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1146 INSERT INTO t1 VALUES (23),(24);
1147 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1148 INSERT INTO t1 VALUES (34);
1149
1150 SELECT * FROM t1;
1151
1152 DROP TABLE t1;
1153
1154 --echo # Simple, Mixed and Bulk inserts with auto increment offset in default locking mode
1155
1156 SET @@auto_increment_offset = 17;
1157 SET @@auto_increment_increment = 40;
1158
1159 # Simple inserts with auto increment offset
1160 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1161
1162 START TRANSACTION;
1163 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1164 SELECT * FROM t1;
1165 ROLLBACK;
1166
1167 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1168
1169 # Gaps expected in the sequence of autoinc numbers
1170 SELECT * FROM t1;
1171
1172 DROP TABLE t1;
1173
1174 # Bulk inserts with auto increment offset
1175
1176 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1177 CREATE TABLE t2(aa INT);
1178
1179 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
1180 START TRANSACTION;
1181 INSERT INTO t1(aa) SELECT * FROM t2;
1182
1183 SELECT * FROM t1;
1184
1185 ROLLBACK;
1186
1187 INSERT INTO t1(aa) SELECT * FROM t2;
1188
1189 SELECT * FROM t1;
1190
1191 DROP TABLE t1;
1192 DROP TABLE t2;
1193
1194 # Mixed mode inserts with auto increment offset
1195
1196 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1197
1198 START TRANSACTION;
1199 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1200 INSERT INTO t1 VALUES (23),(24);
1201 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1202 INSERT INTO t1 VALUES (34);
1203
1204 SELECT * FROM t1;
1205 ROLLBACK;
1206
1207 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1208 INSERT INTO t1 VALUES (23),(24);
1209 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1210 INSERT INTO t1 VALUES (34);
1211
1212 SELECT * FROM t1;
1213
1214 DROP TABLE t1;
1215
1216 SET @@auto_increment_offset = @default_auto_increment_offset;
1217 SET @@auto_increment_increment = @default_auto_increment_increment;
1218
1219 --echo # Transactions with concurrency in consecutive locking mode
1220
1221 # Simple inserts with concurrency
1222
1223 connect (conn1,localhost,root,,);
1224 connect (conn2,localhost,root,,);
1225
1226 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1227
1228 DELIMITER //;
1229
1230 CREATE PROCEDURE POP1()
1231 BEGIN
1232 DECLARE i INT DEFAULT 1;
1233 WHILE (i<= 10) DO
1234 INSERT INTO t1 VALUES(NULL,i);
1235 SET i = i+1;
1236 END WHILE;
1237 END //
1238 DELIMITER ;//
1239
1240 # connect to connection 1
1241 --connection conn1
1242
1243 START TRANSACTION;
1244 --send CALL POP1()
1245
1246 # connect to connection 2
1247 --connection conn2
1248
1249 CALL POP1();
1250
1251 # Switch back to connection 1
1252 --connection conn1
1253 --reap
1254
1255 # Results may vary
1256 SELECT COUNT(*) FROM t1;
1257
1258 ROLLBACK;
1259
1260 --send CALL POP1()
1261
1262 # connect to connection 2
1263 --connection conn2
1264
1265 CALL POP1();
1266
1267 # Switch back to connection 1
1268 --connection conn1
1269 --reap
1270
1271 SELECT COUNT(*) FROM t1;
1272
1273 --connection default
1274 --disconnect conn1
1275 --disconnect conn2
1276
1277 DROP PROCEDURE POP1;
1278 DROP TABLE t1;
1279
1280
1281 # Mixed mode inserts with concurrency
1282
1283 connect (conn1,localhost,root,,);
1284 connect (conn2,localhost,root,,);
1285
1286 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1287
1288 DELIMITER //;
1289
1290 CREATE PROCEDURE POP2(IN j INT)
1291 BEGIN
1292 DECLARE i INT DEFAULT 1;
1293 WHILE (i<=5) DO
1294 INSERT INTO t1(cc) VALUES(i);
1295 SET i=i+1;
1296 END WHILE;
1297 INSERT INTO t1 VALUES(100+j,20);
1298 WHILE(i>=5 && i<=10) DO
1299 INSERT INTO t1(cc) VALUES(i);
1300 SET i=i+1;
1301 END WHILE;
1302 INSERT INTO t1 VALUES (200+j,25);
1303 WHILE(i>=10 && i<=15) DO
1304 INSERT INTO t1(cc) VALUES(i);
1305 SET i=i+1;
1306 END WHILE;
1307
1308 END //
1309
1310 DELIMITER ;//
1311
1312 # Connect to connection 1
1313 --connection conn1
1314
1315 START TRANSACTION;
1316 --send CALL POP2(1)
1317
1318 # Connect to connection 2
1319 --connection conn2
1320
1321 CALL POP2(51);
1322
1323 # Switch back to connection 1
1324 --connection conn1
1325 --reap
1326
1327 SELECT COUNT(*) FROM t1;
1328
1329 ROLLBACK;
1330
1331 --send CALL POP2(1)
1332
1333 # Connect to connection 2
1334 --connection conn2
1335
1336 CALL POP2(150);
1337
1338 # Switch back to connection 1
1339 --connection conn1
1340 --reap
1341
1342 SELECT COUNT(*) FROM t1;
1343
1344 --connection default
1345 --disconnect conn1
1346 --disconnect conn2
1347
1348 DROP PROCEDURE POP2;
1349 DROP TABLE t1;
1350
1351
1352 #########################
1353 # Crash Recovery #
1354 #########################
1355 --echo # Crash server in default locking mode
1356
1357 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1358
1359 DELIMITER //;
1360
1361 CREATE PROCEDURE POP()
1362 BEGIN
1363 DECLARE i INT DEFAULT 1;
1364 WHILE (i <= 10) DO
1365 INSERT INTO t1(aa) VALUES(i);
1366 SET i = i +1;
1367 END WHILE;
1368
1369 END //
1370
1371 DELIMITER ;//
1372
1373
1374 START TRANSACTION;
1375
1376 CALL POP();
1377
1378
1379 # Server will crash on next commit
1380 --source include/expect_crash.inc
1381 SET SESSION DEBUG="+d,crash_commit_after";
1382 --echo CR_SERVER_LOST
1383 --error 2013
1384
1385 COMMIT;
1386
1387 --source include/start_mysqld.inc
1388
1389 # Results may vary
1390 SELECT COUNT(*) FROM t1;
1391
1392 START TRANSACTION;
1393
1394 CALL POP();
1395
1396
1397 # Server will crash on next commit
1398 --source include/expect_crash.inc
1399 SET SESSION DEBUG="+d,crash_commit_before";
1400 --echo CR_SERVER_LOST
1401 --error 2013
1402
1403 COMMIT;
1404
1405 --source include/start_mysqld.inc
1406
1407 # Results may vary
1408 SELECT COUNT(*) FROM t1;
1409
1410 TRUNCATE TABLE t1;
1411
1412
1413 # Test with auto increment offset
1414
1415 SET @@auto_increment_offset = 8;
1416 SET @@auto_increment_increment = 13;
1417
1418
1419 START TRANSACTION;
1420
1421 CALL POP();
1422
1423 # Server will crash on next commit
1424 --source include/expect_crash.inc
1425 SET SESSION DEBUG="+d,crash_commit_after";
1426 --echo CR_SERVER_LOST
1427 --error 2013
1428
1429 COMMIT;
1430
1431 --source include/start_mysqld.inc
1432
1433 # Results may vary
1434 SELECT COUNT(*) FROM t1;
1435
1436
1437 START TRANSACTION;
1438
1439 CALL POP();
1440
1441 # Server will crash on next commit
1442 --source include/expect_crash.inc
1443 SET SESSION DEBUG="+d,crash_commit_before";
1444 --echo CR_SERVER_LOST
1445 --error 2013
1446
1447 COMMIT;
1448
1449 --source include/start_mysqld.inc
1450 # Results may vary
1451 SELECT COUNT(*) FROM t1;
1452
1453 DROP TABLE t1;
1454 DROP PROCEDURE POP;
1455
1456
1457 #######################################
1458 # Load Testing with concurrency #
1459 #######################################
1460 connect (conn1,localhost,root,,);
1461 connect (conn2,localhost,root,,);
1462
1463 --echo # Concurrent load inserts in consecutive lock mode
1464 CREATE TABLE loads ( pkey INT PRIMARY KEY AUTO_INCREMENT, ww CHAR(50));
1465
1466 # Connect to connection 1
1467 --connection conn1
1468
1469 --send LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww)
1470
1471 # Connect to connection 2
1472 --connection conn2
1473
1474
1475 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1476
1477 # Switch back to connection 1
1478 --connection conn1
1479
1480 --reap
1481
1482 SELECT * FROM loads;
1483
1484 --connection default
1485 --disconnect conn1
1486 --disconnect conn2
1487
1488 # Test with auto increment offset
1489
1490 SET @@global.auto_increment_offset = 12;
1491 SET @@global.auto_increment_increment = 50;
1492
1493 TRUNCATE TABLE loads;
1494
1495 connect (conn1,localhost,root,,);
1496 connect (conn2,localhost,root,,);
1497
1498 # Connect to connection 1
1499 --connection conn1
1500
1501 --send LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww)
1502
1503 # Connect to connection 2
1504 --connection conn2
1505
1506 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
1507
1508 # Switch back to connection 1
1509 --connection conn1
1510
1511 --reap
1512
1513 SELECT * FROM loads;
1514
1515 --connection default
1516 --disconnect conn1
1517 --disconnect conn2
1518
1519 DROP TABLE loads;
1520
1521
1522
1523 ##########################################################################################
1524 # Traditional locking mode (innodb_autoinc_lock_mode = 0) #
1525 ##########################################################################################
1526 --echo # Restart server with innodb_autoinc_lock_mode = 0
1527 let $restart_parameters=restart: --innodb_autoinc_lock_mode=0;
1528 --source include/restart_mysqld.inc
1529
1530 SET @default_auto_increment_offset = @@global.auto_increment_offset;
1531 SET @default_auto_increment_increment = @@global.auto_increment_increment;
1532
1533
1534 # Check whether the value has been changed
1535 SELECT @@global.innodb_autoinc_lock_mode;
1536
1537
1538 --echo # Bulk inserts in traditional lock mode
1539 CREATE TABLE t3(cc INT);
1540 INSERT INTO t3 VALUES (10),(20),(30),(40),(50),(60),(70),(80);
1541 CREATE TABLE t4(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1542 INSERT INTO t4(cc) SELECT * FROM t3;
1543 SHOW CREATE TABLE t4;
1544
1545 # No gaps in the auto increment values
1546 INSERT INTO t4(cc) SELECT * FROM t3;
1547 SHOW CREATE TABLE t4;
1548
1549 SELECT * FROM t4;
1550
1551 DROP TABLE t4;
1552 DROP TABLE t3;
1553
1554 #########################
1555 # Concurrency testing #
1556 #########################
1557 connect (conn1,localhost,root,,);
1558 connect (conn2,localhost,root,,);
1559
1560 --echo # Concurrent simple inserts in traditional lock mode
1561 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1562
1563 DELIMITER //;
1564
1565 CREATE PROCEDURE POP1()
1566 BEGIN
1567 DECLARE i INT DEFAULT 1;
1568 WHILE (i<= 10) DO
1569 INSERT INTO t1 VALUES(NULL,i);
1570 IF ((i%2) = 0) THEN
1571 DELETE FROM t1 WHERE bb=i;
1572 END IF;
1573 SET i = i+1;
1574 END WHILE;
1575 END //
1576 DELIMITER ;//
1577
1578 # connect to connection 1
1579 --connection conn1
1580
1581 --send CALL POP1()
1582
1583 # connect to connection 2
1584 --connection conn2
1585
1586 CALL POP1();
1587
1588 # Switch back to connection 1
1589 --connection conn1
1590 --reap
1591
1592 --connection conn2
1593 SELECT COUNT(*) FROM t1;
1594
1595 --connection default
1596 --disconnect conn1
1597 --disconnect conn2
1598 # Test with auto increment offset
1599 SET @@global.auto_increment_offset = 30;
1600 SET @@global.auto_increment_increment = 48;
1601
1602 TRUNCATE TABLE t1;
1603
1604 connect (conn1,localhost,root,,);
1605 connect (conn2,localhost,root,,);
1606
1607 # connect to connection 1
1608 --connection conn1
1609
1610 --send CALL POP1()
1611
1612 # connect to connection 2
1613 --connection conn2
1614
1615 CALL POP1();
1616
1617 # Switch back to connection 1
1618 --connection conn1
1619 --reap
1620
1621 --connection conn2
1622
1623 SELECT COUNT(*) FROM t1;
1624
1625 --connection default
1626 --disconnect conn1
1627 --disconnect conn2
1628
1629 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1630 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1631 DROP PROCEDURE POP1;
1632 DROP TABLE t1;
1633
1634
1635 --echo # Concurrent bulk inserts in traditional lock mode
1636
1637 connect (conn1,localhost,root,,);
1638 connect (conn2,localhost,root,,);
1639 CREATE TABLE t2 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1640 CREATE TABLE t1 (aa INT);
1641
1642 DELIMITER //;
1643
1644 CREATE PROCEDURE POP()
1645 BEGIN
1646 DECLARE i INT DEFAULT 1;
1647 WHILE (i <= 10) DO
1648 INSERT INTO t1 VALUES(i);
1649 SET i = i +1;
1650 END WHILE;
1651 INSERT INTO t2(aa) SELECT * FROM t1;
1652
1653 END //
1654
1655 DELIMITER ;//
1656
1657 # Connect to connection 1
1658 --connection conn1
1659
1660 --send CALL POP()
1661
1662 # Connect to connection 2
1663 --connection conn2
1664
1665 CALL POP();
1666
1667 # Switch back to connection1
1668 --connection conn1
1669 --reap
1670
1671 # Non-deterministic result
1672
1673 --connection default
1674 --disconnect conn1
1675 --disconnect conn2
1676
1677 # Test with auto increment offset
1678 SET @@global.auto_increment_offset = 12;
1679 SET @@global.auto_increment_increment = 50;
1680
1681 TRUNCATE TABLE t1;
1682 TRUNCATE TABLE t2;
1683
1684 connect (conn1,localhost,root,,);
1685 connect (conn2,localhost,root,,);
1686
1687 # Connect to connection 1
1688 --connection conn1
1689
1690 --send CALL POP()
1691
1692 # Connect to connection 2
1693 --connection conn2
1694
1695 CALL POP();
1696
1697 # Switch back to connection1
1698 --connection conn1
1699 --reap
1700
1701
1702 # Non-deterministic result
1703
1704 --connection default
1705 --disconnect conn1
1706 --disconnect conn2
1707 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1708 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1709 DROP PROCEDURE POP;
1710 DROP TABLE t1;
1711 DROP TABLE t2;
1712
1713 --echo # Concurrent mixed mode inserts in traditonal lock mode
1714
1715
1716 connect (conn1,localhost,root,,);
1717 connect (conn2,localhost,root,,);
1718
1719 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1720
1721 DELIMITER //;
1722
1723 CREATE PROCEDURE POP2(IN j INT)
1724 BEGIN
1725 DECLARE i INT DEFAULT 1;
1726 WHILE (i<=5) DO
1727 INSERT INTO t1(cc) VALUES(i);
1728 SET i=i+1;
1729 END WHILE;
1730 INSERT INTO t1 VALUES(100+j,20);
1731 WHILE(i>=5 && i<=10) DO
1732 INSERT INTO t1(cc) VALUES(i);
1733 SET i=i+1;
1734 END WHILE;
1735 INSERT INTO t1 VALUES (200+j,25);
1736 WHILE(i>=10 && i<=15) DO
1737 INSERT INTO t1(cc) VALUES(i);
1738 SET i=i+1;
1739 END WHILE;
1740
1741 END //
1742
1743 DELIMITER ;//
1744
1745 # Connect to connection 1
1746 --connection conn1
1747
1748 --send CALL POP2(1)
1749
1750 # Connect to connection 2
1751 --connection conn2
1752 CALL POP2(50);
1753
1754 # Switch back to connection 1
1755 --connection conn1
1756 --reap
1757
1758 --connection conn2
1759 SELECT COUNT(*) FROM t1;
1760
1761 --connection default
1762 --disconnect conn1
1763 --disconnect conn2
1764
1765 # Test with auto increment offset
1766 SET @@global.auto_increment_offset = 32;
1767 SET @@global.auto_increment_increment = 100;
1768
1769 TRUNCATE TABLE t1;
1770
1771 connect (conn1,localhost,root,,);
1772 connect (conn2,localhost,root,,);
1773
1774 # Connect to connection 1
1775 --connection conn1
1776
1777 --send CALL POP2(1)
1778
1779 # Connect to connection 2
1780 --connection conn2
1781 CALL POP2(180);
1782
1783 # Switch back to connection 1
1784 --connection conn1
1785 --reap
1786
1787 # Results may vary
1788 SELECT COUNT(*) FROM t1;
1789
1790
1791 --connection default
1792 --disconnect conn1
1793 --disconnect conn2
1794 SET @@global.auto_increment_offset = @default_auto_increment_offset;
1795 SET @@global.auto_increment_increment = @default_auto_increment_increment;
1796 DROP PROCEDURE POP2;
1797 DROP TABLE t1;
1798
1799 #########################
1800 # Transactions #
1801 #########################
1802
1803 --echo # Transactions in traditional lock mode
1804
1805 # Simple inserts
1806 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1807
1808 START TRANSACTION;
1809 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1810 ROLLBACK;
1811
1812 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1813
1814 # Gaps expected in the sequence of autoinc numbers
1815 SELECT * FROM t1;
1816
1817 DROP TABLE t1;
1818
1819 # Bulk inserts
1820
1821 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1822 CREATE TABLE t2(aa INT);
1823
1824 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
1825 START TRANSACTION;
1826 INSERT INTO t1(aa) SELECT * FROM t2;
1827
1828 SELECT * FROM t1;
1829
1830 ROLLBACK;
1831
1832 INSERT INTO t1(aa) SELECT * FROM t2;
1833
1834 SELECT * FROM t1;
1835
1836 DROP TABLE t1;
1837 DROP TABLE t2;
1838
1839 # Mixed mode inserts
1840
1841 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1842
1843 START TRANSACTION;
1844 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1845 INSERT INTO t1 VALUES (23),(24);
1846 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1847 INSERT INTO t1 VALUES (34);
1848
1849 SELECT * FROM t1;
1850 ROLLBACK;
1851
1852 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1853 INSERT INTO t1 VALUES (23),(24);
1854 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1855 INSERT INTO t1 VALUES (34);
1856
1857 SELECT * FROM t1;
1858
1859 DROP TABLE t1;
1860
1861 --echo # Simple, Mixed and Bulk inserts with auto increment offset in traditional locking mode
1862
1863 SET @@auto_increment_offset = 17;
1864 SET @@auto_increment_increment = 40;
1865
1866 # Simple inserts with auto increment offset
1867 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1868
1869 START TRANSACTION;
1870 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1871 SELECT * FROM t1;
1872 ROLLBACK;
1873
1874 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1875
1876 # Gaps expected in the sequence of autoinc numbers
1877 SELECT * FROM t1;
1878
1879 DROP TABLE t1;
1880
1881 # Bulk inserts with auto increment offset
1882
1883 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1884 CREATE TABLE t2(aa INT);
1885
1886 INSERT INTO t2 VALUES(1),(2),(3),(4),(5),(6),(7);
1887 START TRANSACTION;
1888 INSERT INTO t1(aa) SELECT * FROM t2;
1889
1890 SELECT * FROM t1;
1891
1892 ROLLBACK;
1893
1894 INSERT INTO t1(aa) SELECT * FROM t2;
1895
1896 SELECT * FROM t1;
1897
1898 DROP TABLE t1;
1899 DROP TABLE t2;
1900
1901 # Mixed mode inserts with auto increment offset
1902
1903 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
1904
1905 START TRANSACTION;
1906 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1907 INSERT INTO t1 VALUES (23),(24);
1908 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1909 INSERT INTO t1 VALUES (34);
1910
1911 SELECT * FROM t1;
1912 ROLLBACK;
1913
1914 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL);
1915 INSERT INTO t1 VALUES (23),(24);
1916 INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
1917 INSERT INTO t1 VALUES (34);
1918
1919 SELECT * FROM t1;
1920
1921 DROP TABLE t1;
1922
1923 SET @@auto_increment_offset = @default_auto_increment_offset;
1924 SET @@auto_increment_increment = @default_auto_increment_increment;
1925
1926 --echo # Transactions with concurrency in traditional locking mode
1927
1928 # Simple inserts with concurrency
1929
1930 connect (conn1,localhost,root,,);
1931 connect (conn2,localhost,root,,);
1932
1933 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1934
1935 DELIMITER //;
1936
1937 CREATE PROCEDURE POP1()
1938 BEGIN
1939 DECLARE i INT DEFAULT 1;
1940 WHILE (i<= 10) DO
1941 INSERT INTO t1 VALUES(NULL,i);
1942 SET i = i+1;
1943 END WHILE;
1944 END //
1945 DELIMITER ;//
1946
1947 # connect to connection 1
1948 --connection conn1
1949
1950 START TRANSACTION;
1951 --send CALL POP1()
1952
1953 # connect to connection 2
1954 --connection conn2
1955
1956 CALL POP1();
1957
1958 # Switch back to connection 1
1959 --connection conn1
1960 --reap
1961
1962 SELECT COUNT(*) FROM t1;
1963
1964 ROLLBACK;
1965
1966 --send CALL POP1()
1967
1968 # connect to connection 2
1969 --connection conn2
1970
1971 CALL POP1();
1972
1973 # Switch back to connection 1
1974 --connection conn1
1975 --reap
1976
1977 SELECT COUNT(*) FROM t1;
1978
1979 --connection default
1980 --disconnect conn1
1981 --disconnect conn2
1982
1983 DROP PROCEDURE POP1;
1984 DROP TABLE t1;
1985
1986
1987 # Mixed mode inserts with concurrency
1988
1989 connect (conn1,localhost,root,,);
1990 connect (conn2,localhost,root,,);
1991
1992 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cc INT);
1993
1994 DELIMITER //;
1995
1996 CREATE PROCEDURE POP2(IN j INT)
1997 BEGIN
1998 DECLARE i INT DEFAULT 1;
1999 WHILE (i<=5) DO
2000 INSERT INTO t1(cc) VALUES(i);
2001 SET i=i+1;
2002 END WHILE;
2003 INSERT INTO t1 VALUES(100+j,20);
2004 WHILE(i>=5 && i<=10) DO
2005 INSERT INTO t1(cc) VALUES(i);
2006 SET i=i+1;
2007 END WHILE;
2008 INSERT INTO t1 VALUES (200+j,25);
2009 WHILE(i>=10 && i<=15) DO
2010 INSERT INTO t1(cc) VALUES(i);
2011 SET i=i+1;
2012 END WHILE;
2013
2014 END //
2015
2016 DELIMITER ;//
2017
2018 # Connect to connection 1
2019 --connection conn1
2020
2021 START TRANSACTION;
2022 --send CALL POP2(1)
2023
2024 # Connect to connection 2
2025 --connection conn2
2026
2027 CALL POP2(120);
2028
2029 # Switch back to connection 1
2030 --connection conn1
2031 --reap
2032
2033 SELECT COUNT(*) FROM t1;
2034
2035 ROLLBACK;
2036
2037 --send CALL POP2(1)
2038
2039 # Connect to connection 2
2040 --connection conn2
2041
2042 CALL POP2(280);
2043
2044 # Switch back to connection 1
2045 --connection conn1
2046 --reap
2047
2048 SELECT COUNT(*) FROM t1;
2049
2050 --connection default
2051 --disconnect conn1
2052 --disconnect conn2
2053
2054 DROP PROCEDURE POP2;
2055 DROP TABLE t1;
2056
2057
2058
2059 #########################
2060 # Crash Recovery #
2061 #########################
2062 --echo # Crash server in traditional locking mode
2063
2064 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
2065
2066 DELIMITER //;
2067
2068 CREATE PROCEDURE POP()
2069 BEGIN
2070 DECLARE i INT DEFAULT 1;
2071 WHILE (i <= 10) DO
2072 INSERT INTO t1(aa) VALUES(i);
2073 SET i = i +1;
2074 END WHILE;
2075
2076 END //
2077
2078 DELIMITER ;//
2079
2080
2081 START TRANSACTION;
2082
2083 --send CALL POP()
2084
2085
2086 --echo # Connection 1
2087 connect(conn1, localhost,root,,);
2088
2089 INSERT INTO t1(aa) VALUES (20);
2090
2091 --echo # Switch back to default connection
2092 --connection default
2093
2094 --reap
2095
2096
2097 # Server will crash on next commit
2098 --source include/expect_crash.inc
2099 SET SESSION DEBUG="+d,crash_commit_after";
2100 --echo CR_SERVER_LOST
2101 --error 2013
2102
2103 COMMIT;
2104
2105 --source include/start_mysqld.inc
2106
2107 # Results may vary
2108 SELECT COUNT(*) FROM t1;
2109
2110 --disconnect conn1
2111
2112 START TRANSACTION;
2113
2114 --send CALL POP()
2115
2116 --echo # Connection 1
2117 connect(conn1,localhost,root,,);
2118
2119 INSERT INTO t1(aa) VALUES (20);
2120
2121 --echo # Switch back to default connection
2122 --connection default
2123
2124 --reap
2125
2126 # Server will crash on next commit
2127 --source include/expect_crash.inc
2128 SET SESSION DEBUG="+d,crash_commit_before";
2129 --echo CR_SERVER_LOST
2130 --error 2013
2131
2132 COMMIT;
2133
2134 --source include/start_mysqld.inc
2135
2136 # Results may vary
2137 SELECT COUNT(*) FROM t1;
2138
2139 TRUNCATE TABLE t1;
2140
2141 --disconnect conn1
2142
2143 # Test with auto increment offset
2144
2145 SET @@auto_increment_offset = 15;
2146 SET @@auto_increment_increment = 30;
2147
2148
2149 START TRANSACTION;
2150
2151 --send CALL POP()
2152
2153 --echo # Connection 1
2154 connect(conn1, localhost,root,,);
2155
2156 SET @@auto_increment_offset = 15;
2157 SET @@auto_increment_increment = 30;
2158
2159 INSERT INTO t1(aa) VALUES (20);
2160
2161 --echo # Switch back to default connection
2162 --connection default
2163
2164 --reap
2165
2166
2167 # Server will crash on next commit
2168 --source include/expect_crash.inc
2169 SET SESSION DEBUG="+d,crash_commit_after";
2170 --echo CR_SERVER_LOST
2171 --error 2013
2172
2173 COMMIT;
2174
2175 --source include/start_mysqld.inc
2176
2177 # Results may vary
2178 SELECT COUNT(*) FROM t1;
2179
2180 --connection default
2181 --disconnect conn1
2182
2183 SET @@auto_increment_offset = 15;
2184 SET @@auto_increment_increment = 30;
2185
2186 START TRANSACTION;
2187
2188 --send CALL POP()
2189
2190 --echo # Connection 1
2191 connect(conn1,localhost,root,,);
2192
2193 SET @@auto_increment_offset = 15;
2194 SET @@auto_increment_increment = 30;
2195
2196
2197 INSERT INTO t1(aa) VALUES (20);
2198
2199 --echo # Switch back to default connection
2200 --connection default
2201
2202 --reap
2203
2204 # Server will crash on next commit
2205 --source include/expect_crash.inc
2206 SET SESSION DEBUG="+d,crash_commit_before";
2207 --echo CR_SERVER_LOST
2208 --error 2013
2209
2210 COMMIT;
2211
2212 --source include/start_mysqld.inc
2213
2214 # Results may vary
2215 SELECT COUNT(*) FROM t1;
2216
2217 DROP TABLE t1;
2218 DROP PROCEDURE POP;
2219
2220
2221 --disconnect conn1
2222
2223
2224 #######################################
2225 # Load Testing with concurrency #
2226 #######################################
2227 connect (conn1,localhost,root,,);
2228 connect (conn2,localhost,root,,);
2229
2230 --echo # Concurrent load inserts in traditional lock mode
2231 CREATE TABLE loads ( pkey INT PRIMARY KEY AUTO_INCREMENT, ww CHAR(50));
2232
2233 # Connect to connection 1
2234 --connection conn1
2235
2236 --send LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww)
2237
2238 # Connect to connection 2
2239 --connection conn2
2240
2241
2242 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
2243
2244 # Switch back to connection 1
2245 --connection conn1
2246
2247 --reap
2248
2249 SELECT * FROM loads;
2250
2251 --connection default
2252 --disconnect conn1
2253 --disconnect conn2
2254
2255 # Test with auto increment offset
2256
2257 SET @@global.auto_increment_offset = 12;
2258 SET @@global.auto_increment_increment = 50;
2259
2260 TRUNCATE TABLE loads;
2261
2262 connect (conn1,localhost,root,,);
2263 connect (conn2,localhost,root,,);
2264
2265 # Connect to connection 1
2266 --connection conn1
2267
2268 --send LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww)
2269
2270 # Connect to connection 2
2271 --connection conn2
2272
2273 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE loads(ww);
2274
2275 # Switch back to connection 1
2276 --connection conn1
2277
2278 --reap
2279
2280 SELECT * FROM loads;
2281
2282 --connection default
2283 --disconnect conn1
2284 --disconnect conn2
2285
2286 DROP TABLE loads;
2287
2288 --let $restart_parameters=restart:
2289 --source include/restart_mysqld.inc
2290
2291 --echo #
2292 --echo # Bug#30765952 AUTO INCREMENT VALUE DIDN'T RESET CORRECTLY
2293 --echo #
2294
2295 SET SESSION information_schema_stats_expiry=0;
2296
2297 CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT DEFAULT 0, PRIMARY KEY (a))
2298 ENGINE=InnoDB;
2299 INSERT INTO t1(`b`) VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
2300
2301 ALTER TABLE t1 MODIFY a INT;
2302
2303 ALTER TABLE t1 DROP INDEX `PRIMARY`, ADD COLUMN c INT NOT NULL AUTO_INCREMENT,
2304 ADD KEY (c);
2305
2306 DROP TABLE t1;
2307
2308 SET @@SESSION.information_schema_stats_expiry = DEFAULT;
2309
2310 --echo #
2311 --echo # Bug#33419246 AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1
2312 --echo # Bug#34418341 AUTOINC counter resets to 1 after ALTER TABLE following by restart
2313 --echo # Bug#34951259 alter table... auto_increment=1 does not work as expected
2314 --echo #
2315
2316 #Test 1
2317 #This test will check whether after modifying existing column to auto increment column,
2318 #The auto increment value is persisted even after the restart of server
2319 #Also whether Auto increment value from information_schema matches the auto increment value of table
2320 #Also if the insert after restart works fine
2321
2322 CREATE TABLE `t1` (`id` INT NOT NULL PRIMARY KEY);
2323 INSERT INTO `t1` (`id`) VALUES (1), (2), (3);
2324 ALTER TABLE `t1` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
2325 SHOW CREATE TABLE `t1`;
2326 select `AUTO_INCREMENT` from information_schema.tables where table_name='t1';
2327
2328 --source include/restart_mysqld.inc
2329 --source include/wait_until_connected_again.inc
2330
2331 SHOW CREATE TABLE `t1`;
2332 select `AUTO_INCREMENT` from information_schema.tables where table_name='t1';
2333
2334 SET SESSION information_schema_stats_expiry=0;
2335 INSERT INTO `t1` (`id`) VALUES (NULL);
2336 SELECT * FROM `t1`;
2337
2338 SHOW CREATE TABLE `t1`;
2339 select `AUTO_INCREMENT` from information_schema.tables where table_name='t1';
2340
2341 SET SESSION information_schema_stats_expiry=DEFAULT;
2342
2343 DROP TABLE t1;
2344
2345 #Test 2
2346 #This test will check if after the existing column is modified to auto increment column,
2347 #whether the auto increment forced to lower value than the existing is ignored
2348 #And whether the insert into table after this ignore works fine
2349
2350 CREATE TABLE `t1` (`id` INT NOT NULL PRIMARY KEY);
2351 INSERT INTO `t1` (`id`) VALUES (1), (2), (3);
2352 ALTER TABLE `t1` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
2353 ALTER TABLE `t1` AUTO_INCREMENT=1;
2354 SHOW CREATE TABLE `t1`;
2355 INSERT INTO `t1` (`id`) VALUES (NULL);
2356 DROP TABLE t1;
2357
2358 #Test 3
2359 #This test will check if the recovery works fine or it has any adverse effects
2360 #After the alter table statement which is modifying the existing column to be an auto increment column,
2361 #We are crashing the server to verify if the alter table will not have any effect, which is the expected behaviour
2362
2363 CREATE TABLE `t1` (`id` INT NOT NULL PRIMARY KEY);
2364 INSERT INTO `t1` (`id`) VALUES (1), (2), (3);
2365 show create table t1;
2366 SET SESSION debug= "+d, crash_create_after_autoinc_persisted_update";
2367
2368 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
2369 --error 2013
2370
2371 ALTER TABLE `t1` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
2372
2373 --enable_reconnect
2374 --source include/wait_until_connected_again.inc
2375
2376 SHOW CREATE TABLE `t1`;
2377 DROP TABLE t1;
2378
2379 #
2380 # Bug#34752625 Persistent Table Metadata from various versions is not aggregated properly
2381 #
2382
2383 CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
2384 set global innodb_checkpoint_disabled=1;
2385 INSERT INTO t1 VALUES (110000);
2386 INSERT INTO t1 VALUES ();
2387 SHOW CREATE TABLE t1;
2388 DELETE FROM t1;
2389 ALTER TABLE t1 AUTO_INCREMENT=100000;
2390 INSERT INTO t1 VALUES ();
2391 # expecting the new lower value, 100001
2392 SHOW CREATE TABLE t1;
2393 --source include/kill_mysqld.inc
2394 --source include/start_mysqld.inc
2395 # expecting the new lower value again, 100001
2396 SHOW CREATE TABLE t1;
2397 DROP TABLE t1;