Debian Codegraph @

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

raw
1 drop table if exists t1;
2 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
3 INSERT INTO t1 VALUES (9223372036854775807, null);
4 INSERT INTO t1 (c2) VALUES ('innodb');
5 Got one of the listed errors
6 SELECT * FROM t1;
7 c1 c2
8 9223372036854775807 NULL
9 DROP TABLE t1;
10 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
11 INSERT INTO t1 VALUES (127, null);
12 INSERT INTO t1 (c2) VALUES ('innodb');
13 Got one of the listed errors
14 SELECT * FROM t1;
15 c1 c2
16 127 NULL
17 DROP TABLE t1;
18 CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
19 INSERT INTO t1 VALUES (255, null);
20 INSERT INTO t1 (c2) VALUES ('innodb');
21 Got one of the listed errors
22 SELECT * FROM t1;
23 c1 c2
24 255 NULL
25 DROP TABLE t1;
26 CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
27 INSERT INTO t1 VALUES (32767, null);
28 INSERT INTO t1 (c2) VALUES ('innodb');
29 Got one of the listed errors
30 SELECT * FROM t1;
31 c1 c2
32 32767 NULL
33 DROP TABLE t1;
34 CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
35 INSERT INTO t1 VALUES (65535, null);
36 INSERT INTO t1 (c2) VALUES ('innodb');
37 Got one of the listed errors
38 SELECT * FROM t1;
39 c1 c2
40 65535 NULL
41 DROP TABLE t1;
42 CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
43 INSERT INTO t1 VALUES (8388607, null);
44 INSERT INTO t1 (c2) VALUES ('innodb');
45 Got one of the listed errors
46 SELECT * FROM t1;
47 c1 c2
48 8388607 NULL
49 DROP TABLE t1;
50 CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
51 INSERT INTO t1 VALUES (16777215, null);
52 INSERT INTO t1 (c2) VALUES ('innodb');
53 Got one of the listed errors
54 SELECT * FROM t1;
55 c1 c2
56 16777215 NULL
57 DROP TABLE t1;
58 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
59 INSERT INTO t1 VALUES (2147483647, null);
60 INSERT INTO t1 (c2) VALUES ('innodb');
61 Got one of the listed errors
62 SELECT * FROM t1;
63 c1 c2
64 2147483647 NULL
65 DROP TABLE t1;
66 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
67 INSERT INTO t1 VALUES (4294967295, null);
68 INSERT INTO t1 (c2) VALUES ('innodb');
69 Got one of the listed errors
70 SELECT * FROM t1;
71 c1 c2
72 4294967295 NULL
73 DROP TABLE t1;
74 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
75 INSERT INTO t1 VALUES (9223372036854775807, null);
76 INSERT INTO t1 (c2) VALUES ('innodb');
77 Got one of the listed errors
78 SELECT * FROM t1;
79 c1 c2
80 9223372036854775807 NULL
81 DROP TABLE t1;
82 CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
83 INSERT INTO t1 VALUES (18446744073709551615, null);
84 INSERT INTO t1 (c2) VALUES ('innodb');
85 Got one of the listed errors
86 SELECT * FROM t1;
87 c1 c2
88 18446744073709551615 NULL
89 DROP TABLE t1;
90 CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
91 INSERT INTO t1 VALUES (1), (2), (3);
92 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
93 SELECT c1 FROM t1;
94 c1
95 1
96 2
97 3
98 4
99 5
100 6
101 SHOW CREATE TABLE t1;
102 Table Create Table
103 t1 CREATE TABLE `t1` (
104 `c1` int NOT NULL AUTO_INCREMENT,
105 PRIMARY KEY (`c1`)
106 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
107 TRUNCATE TABLE t1;
108 SHOW CREATE TABLE t1;
109 Table Create Table
110 t1 CREATE TABLE `t1` (
111 `c1` int NOT NULL AUTO_INCREMENT,
112 PRIMARY KEY (`c1`)
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
114 INSERT INTO t1 VALUES (1), (2), (3);
115 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
116 SELECT c1 FROM t1;
117 c1
118 1
119 2
120 3
121 4
122 5
123 6
124 SHOW CREATE TABLE t1;
125 Table Create Table
126 t1 CREATE TABLE `t1` (
127 `c1` int NOT NULL AUTO_INCREMENT,
128 PRIMARY KEY (`c1`)
129 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
130 DROP TABLE t1;
131 CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
132 INSERT INTO t1 VALUES (1), (2), (3);
133 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
134 SELECT c1 FROM t1;
135 c1
136 1
137 2
138 3
139 4
140 5
141 6
142 SHOW CREATE TABLE t1;
143 Table Create Table
144 t1 CREATE TABLE `t1` (
145 `c1` int NOT NULL AUTO_INCREMENT,
146 PRIMARY KEY (`c1`)
147 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
148 DELETE FROM t1;
149 SHOW CREATE TABLE t1;
150 Table Create Table
151 t1 CREATE TABLE `t1` (
152 `c1` int NOT NULL AUTO_INCREMENT,
153 PRIMARY KEY (`c1`)
154 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
155 INSERT INTO t1 VALUES (1), (2), (3);
156 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
157 SELECT c1 FROM t1;
158 c1
159 1
160 2
161 3
162 7
163 8
164 9
165 SHOW CREATE TABLE t1;
166 Table Create Table
167 t1 CREATE TABLE `t1` (
168 `c1` int NOT NULL AUTO_INCREMENT,
169 PRIMARY KEY (`c1`)
170 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
171 DROP TABLE t1;
172 DROP TABLE IF EXISTS t1;
173 Warnings:
174 Note 1051 Unknown table 'test.t1'
175 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB;
176 INSERT INTO t1 VALUES (NULL, 1);
177 DELETE FROM t1 WHERE c1 = 1;
178 INSERT INTO t1 VALUES (2,1);
179 INSERT INTO t1 VALUES (NULL,8);
180 SELECT * FROM t1;
181 c1 c2
182 2 1
183 3 8
184 DROP TABLE t1;
185 DROP TABLE IF EXISTS t1;
186 Warnings:
187 Note 1051 Unknown table 'test.t1'
188 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB;
189 INSERT INTO t1 VALUES (NULL, 1);
190 DELETE FROM t1 WHERE c1 = 1;
191 INSERT INTO t1 VALUES (2,1), (NULL, 8);
192 INSERT INTO t1 VALUES (NULL,9);
193 SELECT * FROM t1;
194 c1 c2
195 2 1
196 3 8
197 5 9
198 DROP TABLE t1;
199 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
200 SHOW VARIABLES LIKE "%auto_inc%";
201 Variable_name Value
202 auto_increment_increment 100
203 auto_increment_offset 10
204 DROP TABLE IF EXISTS t1;
205 Warnings:
206 Note 1051 Unknown table 'test.t1'
207 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
208 INSERT INTO t1 VALUES (NULL),(5),(NULL);
209 INSERT INTO t1 VALUES (250),(NULL);
210 SELECT * FROM t1;
211 c1
212 5
213 10
214 110
215 250
216 310
217 INSERT INTO t1 VALUES (1000);
218 SET @@INSERT_ID=400;
219 INSERT INTO t1 VALUES(NULL),(NULL);
220 SELECT * FROM t1;
221 c1
222 5
223 10
224 110
225 250
226 310
227 400
228 410
229 1000
230 DROP TABLE t1;
231 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
232 SET @@INSERT_ID=1;
233 SHOW VARIABLES LIKE "%auto_inc%";
234 Variable_name Value
235 auto_increment_increment 1
236 auto_increment_offset 1
237 DROP TABLE IF EXISTS t1;
238 Warnings:
239 Note 1051 Unknown table 'test.t1'
240 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
241 INSERT INTO t1 VALUES(0);
242 SELECT * FROM t1;
243 c1
244 1
245 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
246 INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL);
247 INSERT INTO t1 VALUES (250),(NULL);
248 SELECT * FROM t1;
249 c1
250 -1
251 1
252 2
253 10
254 110
255 250
256 410
257 SET @@INSERT_ID=400;
258 INSERT INTO t1 VALUES(NULL),(NULL);
259 Got one of the listed errors
260 SELECT * FROM t1;
261 c1
262 -1
263 1
264 2
265 10
266 110
267 250
268 410
269 DROP TABLE t1;
270 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
271 SET @@INSERT_ID=1;
272 SHOW VARIABLES LIKE "%auto_inc%";
273 Variable_name Value
274 auto_increment_increment 1
275 auto_increment_offset 1
276 DROP TABLE IF EXISTS t1;
277 Warnings:
278 Note 1051 Unknown table 'test.t1'
279 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
280 INSERT INTO t1 VALUES(-1);
281 SELECT * FROM t1;
282 c1
283 -1
284 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
285 SHOW VARIABLES LIKE "%auto_inc%";
286 Variable_name Value
287 auto_increment_increment 100
288 auto_increment_offset 10
289 INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL);
290 INSERT INTO t1 VALUES (250),(NULL);
291 SELECT * FROM t1;
292 c1
293 -2
294 -1
295 1
296 2
297 10
298 250
299 310
300 INSERT INTO t1 VALUES (1000);
301 SET @@INSERT_ID=400;
302 INSERT INTO t1 VALUES(NULL),(NULL);
303 SELECT * FROM t1;
304 c1
305 -2
306 -1
307 1
308 2
309 10
310 250
311 310
312 400
313 410
314 1000
315 DROP TABLE t1;
316 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
317 SET @@INSERT_ID=1;
318 SHOW VARIABLES LIKE "%auto_inc%";
319 Variable_name Value
320 auto_increment_increment 1
321 auto_increment_offset 1
322 DROP TABLE IF EXISTS t1;
323 Warnings:
324 Note 1051 Unknown table 'test.t1'
325 CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
326 INSERT IGNORE INTO t1 VALUES(-1);
327 Warnings:
328 Warning 1264 Out of range value for column 'c1' at row 1
329 SELECT * FROM t1;
330 c1
331 1
332 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
333 SHOW VARIABLES LIKE "%auto_inc%";
334 Variable_name Value
335 auto_increment_increment 100
336 auto_increment_offset 10
337 INSERT IGNORE INTO t1 VALUES (-2);
338 Warnings:
339 Warning 1264 Out of range value for column 'c1' at row 1
340 INSERT INTO t1 VALUES (NULL);
341 INSERT INTO t1 VALUES (2);
342 INSERT INTO t1 VALUES (NULL);
343 INSERT INTO t1 VALUES (250);
344 INSERT INTO t1 VALUES (NULL);
345 SELECT * FROM t1;
346 c1
347 1
348 2
349 10
350 110
351 210
352 250
353 310
354 INSERT INTO t1 VALUES (1000);
355 SET @@INSERT_ID=400;
356 INSERT INTO t1 VALUES(NULL);
357 INSERT INTO t1 VALUES(NULL);
358 SELECT * FROM t1;
359 c1
360 1
361 2
362 10
363 110
364 210
365 250
366 310
367 400
368 1000
369 1010
370 DROP TABLE t1;
371 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
372 SET @@INSERT_ID=1;
373 SHOW VARIABLES LIKE "%auto_inc%";
374 Variable_name Value
375 auto_increment_increment 1
376 auto_increment_offset 1
377 DROP TABLE IF EXISTS t1;
378 Warnings:
379 Note 1051 Unknown table 'test.t1'
380 CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
381 INSERT IGNORE INTO t1 VALUES(-1);
382 Warnings:
383 Warning 1264 Out of range value for column 'c1' at row 1
384 SELECT * FROM t1;
385 c1
386 1
387 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
388 SHOW VARIABLES LIKE "%auto_inc%";
389 Variable_name Value
390 auto_increment_increment 100
391 auto_increment_offset 10
392 INSERT IGNORE INTO t1 VALUES (-2),(NULL),(2),(NULL);
393 Warnings:
394 Warning 1264 Out of range value for column 'c1' at row 1
395 INSERT INTO t1 VALUES (250),(NULL);
396 SELECT * FROM t1;
397 c1
398 1
399 2
400 10
401 110
402 210
403 250
404 410
405 INSERT INTO t1 VALUES (1000);
406 SET @@INSERT_ID=400;
407 INSERT INTO t1 VALUES(NULL),(NULL);
408 Got one of the listed errors
409 SELECT * FROM t1;
410 c1
411 1
412 2
413 10
414 110
415 210
416 250
417 410
418 1000
419 DROP TABLE t1;
420 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
421 SET @@INSERT_ID=1;
422 SHOW VARIABLES LIKE "%auto_inc%";
423 Variable_name Value
424 auto_increment_increment 1
425 auto_increment_offset 1
426 DROP TABLE IF EXISTS t1;
427 Warnings:
428 Note 1051 Unknown table 'test.t1'
429 CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
430 INSERT INTO t1 VALUES(NULL);
431 INSERT INTO t1 VALUES (9223372036854775794);
432 SELECT * FROM t1;
433 c1
434 1
435 9223372036854775794
436 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
437 SHOW VARIABLES LIKE "%auto_inc%";
438 Variable_name Value
439 auto_increment_increment 2
440 auto_increment_offset 10
441 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
442 SELECT * FROM t1;
443 c1
444 1
445 9223372036854775794
446 9223372036854775796
447 9223372036854775798
448 9223372036854775800
449 9223372036854775802
450 9223372036854775804
451 9223372036854775806
452 DROP TABLE t1;
453 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
454 SET @@INSERT_ID=1;
455 SHOW VARIABLES LIKE "%auto_inc%";
456 Variable_name Value
457 auto_increment_increment 1
458 auto_increment_offset 1
459 DROP TABLE IF EXISTS t1;
460 Warnings:
461 Note 1051 Unknown table 'test.t1'
462 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
463 INSERT INTO t1 VALUES(NULL);
464 INSERT INTO t1 VALUES (18446744073709551603);
465 SELECT * FROM t1;
466 c1
467 1
468 18446744073709551603
469 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
470 SHOW VARIABLES LIKE "%auto_inc%";
471 Variable_name Value
472 auto_increment_increment 2
473 auto_increment_offset 10
474 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
475 ERROR HY000: Failed to read auto-increment value from storage engine
476 SELECT * FROM t1;
477 c1
478 1
479 18446744073709551603
480 DROP TABLE t1;
481 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
482 SET @@INSERT_ID=1;
483 SHOW VARIABLES LIKE "%auto_inc%";
484 Variable_name Value
485 auto_increment_increment 1
486 auto_increment_offset 1
487 DROP TABLE IF EXISTS t1;
488 Warnings:
489 Note 1051 Unknown table 'test.t1'
490 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
491 INSERT INTO t1 VALUES(NULL);
492 INSERT INTO t1 VALUES (18446744073709551603);
493 SELECT * FROM t1;
494 c1
495 1
496 18446744073709551603
497 SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7;
498 SHOW VARIABLES LIKE "%auto_inc%";
499 Variable_name Value
500 auto_increment_increment 5
501 auto_increment_offset 7
502 INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
503 ERROR HY000: Failed to read auto-increment value from storage engine
504 SELECT * FROM t1;
505 c1
506 1
507 18446744073709551603
508 DROP TABLE t1;
509 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
510 SET @@INSERT_ID=1;
511 SHOW VARIABLES LIKE "%auto_inc%";
512 Variable_name Value
513 auto_increment_increment 1
514 auto_increment_offset 1
515 DROP TABLE IF EXISTS t1;
516 Warnings:
517 Note 1051 Unknown table 'test.t1'
518 CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
519 INSERT INTO t1 VALUES(NULL);
520 INSERT INTO t1 VALUES(-9223372036854775806);
521 INSERT INTO t1 VALUES(-9223372036854775807);
522 INSERT INTO t1 VALUES(-9223372036854775808);
523 SELECT * FROM t1;
524 c1
525 -9223372036854775808
526 -9223372036854775807
527 -9223372036854775806
528 1
529 SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3;
530 SHOW VARIABLES LIKE "%auto_inc%";
531 Variable_name Value
532 auto_increment_increment 3
533 auto_increment_offset 3
534 INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
535 SELECT * FROM t1;
536 c1
537 -9223372036854775808
538 -9223372036854775807
539 -9223372036854775806
540 1
541 3
542 6
543 9
544 DROP TABLE t1;
545 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
546 SET @@INSERT_ID=1;
547 SHOW VARIABLES LIKE "%auto_inc%";
548 Variable_name Value
549 auto_increment_increment 1
550 auto_increment_offset 1
551 DROP TABLE IF EXISTS t1;
552 Warnings:
553 Note 1051 Unknown table 'test.t1'
554 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
555 INSERT INTO t1 VALUES(NULL);
556 INSERT INTO t1 VALUES (18446744073709551610);
557 SELECT * FROM t1;
558 c1
559 1
560 18446744073709551610
561 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976;
562 Warnings:
563 Warning 1292 Truncated incorrect auto_increment_increment value: '1152921504606846976'
564 Warning 1292 Truncated incorrect auto_increment_offset value: '1152921504606846976'
565 SHOW VARIABLES LIKE "%auto_inc%";
566 Variable_name Value
567 auto_increment_increment 65535
568 auto_increment_offset 65535
569 INSERT INTO t1 VALUES (NULL),(NULL);
570 ERROR 22003: Out of range value for column 't1' at row 167
571 SELECT * FROM t1;
572 c1
573 1
574 18446744073709551610
575 DROP TABLE t1;
576 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
577 SET @@INSERT_ID=1;
578 SHOW VARIABLES LIKE "%auto_inc%";
579 Variable_name Value
580 auto_increment_increment 1
581 auto_increment_offset 1
582 CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
583 Warnings:
584 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'c1'.
585 INSERT INTO t1 VALUES(NULL, 1);
586 INSERT INTO t1 VALUES(NULL, 2);
587 SELECT * FROM t1;
588 c1 c2
589 1 1
590 2 2
591 ALTER TABLE t1 CHANGE c1 c1 SERIAL;
592 SELECT * FROM t1;
593 c1 c2
594 1 1
595 2 2
596 INSERT INTO t1 VALUES(NULL, 3);
597 INSERT INTO t1 VALUES(NULL, 4);
598 SELECT * FROM t1;
599 c1 c2
600 1 1
601 2 2
602 3 3
603 4 4
604 DROP TABLE IF EXISTS t1;
605 CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
606 Warnings:
607 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'c1'.
608 INSERT INTO t1 VALUES(NULL, 1);
609 INSERT INTO t1 VALUES(NULL, 2);
610 SELECT * FROM t1;
611 c1 c2
612 1 1
613 2 2
614 ALTER TABLE t1 CHANGE c1 c1 SERIAL;
615 SELECT * FROM t1;
616 c1 c2
617 1 1
618 2 2
619 INSERT INTO t1 VALUES(NULL, 3);
620 INSERT INTO t1 VALUES(NULL, 4);
621 SELECT * FROM t1;
622 c1 c2
623 1 1
624 2 2
625 3 3
626 4 4
627 DROP TABLE t1;
628 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
629 DROP TABLE IF EXISTS t1;
630 Warnings:
631 Note 1051 Unknown table 'test.t1'
632 DROP TABLE IF EXISTS t2;
633 Warnings:
634 Note 1051 Unknown table 'test.t2'
635 CREATE TABLE t1 (
636 a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
637 b INT(10) UNSIGNED NOT NULL,
638 c ENUM('FALSE','TRUE') DEFAULT NULL,
639 PRIMARY KEY (a)) ENGINE = InnoDB;
640 Warnings:
641 Warning 1681 Integer display width is deprecated and will be removed in a future release.
642 Warning 1681 Integer display width is deprecated and will be removed in a future release.
643 CREATE TABLE t2 (
644 m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
645 n INT(10) UNSIGNED NOT NULL,
646 o enum('FALSE','TRUE') DEFAULT NULL,
647 PRIMARY KEY (m)) ENGINE = InnoDB;
648 Warnings:
649 Warning 1681 Integer display width is deprecated and will be removed in a future release.
650 Warning 1681 Integer display width is deprecated and will be removed in a future release.
651 INSERT INTO t2 (n,o) VALUES
652 (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
653 (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
654 SHOW CREATE TABLE t2;
655 Table Create Table
656 t2 CREATE TABLE `t2` (
657 `m` int unsigned NOT NULL AUTO_INCREMENT,
658 `n` int unsigned NOT NULL,
659 `o` enum('FALSE','TRUE') DEFAULT NULL,
660 PRIMARY KEY (`m`)
661 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
662 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
663 SHOW CREATE TABLE t1;
664 Table Create Table
665 t1 CREATE TABLE `t1` (
666 `a` int unsigned NOT NULL AUTO_INCREMENT,
667 `b` int unsigned NOT NULL,
668 `c` enum('FALSE','TRUE') DEFAULT NULL,
669 PRIMARY KEY (`a`)
670 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
671 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
672 SELECT * FROM t1;
673 a b c
674 1 1 TRUE
675 2 1 FALSE
676 3 2 TRUE
677 4 2 FALSE
678 5 3 TRUE
679 6 3 FALSE
680 7 4 TRUE
681 8 4 FALSE
682 9 5 TRUE
683 10 5 FALSE
684 16 1 TRUE
685 17 1 FALSE
686 18 2 TRUE
687 19 2 FALSE
688 20 3 TRUE
689 21 3 FALSE
690 22 4 TRUE
691 23 4 FALSE
692 24 5 TRUE
693 25 5 FALSE
694 SHOW CREATE TABLE t1;
695 Table Create Table
696 t1 CREATE TABLE `t1` (
697 `a` int unsigned NOT NULL AUTO_INCREMENT,
698 `b` int unsigned NOT NULL,
699 `c` enum('FALSE','TRUE') DEFAULT NULL,
700 PRIMARY KEY (`a`)
701 ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
702 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
703 SELECT * FROM t1;
704 a b c
705 1 1 TRUE
706 2 1 FALSE
707 3 2 TRUE
708 4 2 FALSE
709 5 3 TRUE
710 6 3 FALSE
711 7 4 TRUE
712 8 4 FALSE
713 9 5 TRUE
714 10 5 FALSE
715 16 1 TRUE
716 17 1 FALSE
717 18 2 TRUE
718 19 2 FALSE
719 20 3 TRUE
720 21 3 FALSE
721 22 4 TRUE
722 23 4 FALSE
723 24 5 TRUE
724 25 5 FALSE
725 31 1 FALSE
726 32 2 FALSE
727 33 3 FALSE
728 34 4 FALSE
729 35 5 FALSE
730 SHOW CREATE TABLE t1;
731 Table Create Table
732 t1 CREATE TABLE `t1` (
733 `a` int unsigned NOT NULL AUTO_INCREMENT,
734 `b` int unsigned NOT NULL,
735 `c` enum('FALSE','TRUE') DEFAULT NULL,
736 PRIMARY KEY (`a`)
737 ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
738 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
739 SELECT * FROM t1;
740 a b c
741 1 1 TRUE
742 2 1 FALSE
743 3 2 TRUE
744 4 2 FALSE
745 5 3 TRUE
746 6 3 FALSE
747 7 4 TRUE
748 8 4 FALSE
749 9 5 TRUE
750 10 5 FALSE
751 16 1 TRUE
752 17 1 FALSE
753 18 2 TRUE
754 19 2 FALSE
755 20 3 TRUE
756 21 3 FALSE
757 22 4 TRUE
758 23 4 FALSE
759 24 5 TRUE
760 25 5 FALSE
761 31 1 FALSE
762 32 2 FALSE
763 33 3 FALSE
764 34 4 FALSE
765 35 5 FALSE
766 38 1 FALSE
767 39 2 FALSE
768 40 3 FALSE
769 41 4 FALSE
770 42 5 FALSE
771 SHOW CREATE TABLE t1;
772 Table Create Table
773 t1 CREATE TABLE `t1` (
774 `a` int unsigned NOT NULL AUTO_INCREMENT,
775 `b` int unsigned NOT NULL,
776 `c` enum('FALSE','TRUE') DEFAULT NULL,
777 PRIMARY KEY (`a`)
778 ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
779 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
780 SHOW CREATE TABLE t1;
781 Table Create Table
782 t1 CREATE TABLE `t1` (
783 `a` int unsigned NOT NULL AUTO_INCREMENT,
784 `b` int unsigned NOT NULL,
785 `c` enum('FALSE','TRUE') DEFAULT NULL,
786 PRIMARY KEY (`a`)
787 ) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
788 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
789 SHOW CREATE TABLE t1;
790 Table Create Table
791 t1 CREATE TABLE `t1` (
792 `a` int unsigned NOT NULL AUTO_INCREMENT,
793 `b` int unsigned NOT NULL,
794 `c` enum('FALSE','TRUE') DEFAULT NULL,
795 PRIMARY KEY (`a`)
796 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
797 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
798 SELECT * FROM t1;
799 a b c
800 1 1 TRUE
801 2 1 FALSE
802 3 2 TRUE
803 4 2 FALSE
804 5 3 TRUE
805 6 3 FALSE
806 7 4 TRUE
807 8 4 FALSE
808 9 5 TRUE
809 10 5 FALSE
810 16 1 TRUE
811 17 1 FALSE
812 18 2 TRUE
813 19 2 FALSE
814 20 3 TRUE
815 21 3 FALSE
816 22 4 TRUE
817 23 4 FALSE
818 24 5 TRUE
819 25 5 FALSE
820 31 1 FALSE
821 32 2 FALSE
822 33 3 FALSE
823 34 4 FALSE
824 35 5 FALSE
825 38 1 FALSE
826 39 2 FALSE
827 40 3 FALSE
828 41 4 FALSE
829 42 5 FALSE
830 45 1 FALSE
831 46 2 FALSE
832 47 3 FALSE
833 48 4 FALSE
834 49 5 FALSE
835 52 1 FALSE
836 53 2 FALSE
837 54 3 FALSE
838 55 4 FALSE
839 56 5 FALSE
840 59 1 FALSE
841 60 2 FALSE
842 61 3 FALSE
843 62 4 FALSE
844 63 5 FALSE
845 SHOW CREATE TABLE t1;
846 Table Create Table
847 t1 CREATE TABLE `t1` (
848 `a` int unsigned NOT NULL AUTO_INCREMENT,
849 `b` int unsigned NOT NULL,
850 `c` enum('FALSE','TRUE') DEFAULT NULL,
851 PRIMARY KEY (`a`)
852 ) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
853 DROP TABLE t1;
854 DROP TABLE t2;
855 DROP TABLE IF EXISTS t1;
856 Warnings:
857 Note 1051 Unknown table 'test.t1'
858 DROP TABLE IF EXISTS t2;
859 Warnings:
860 Note 1051 Unknown table 'test.t2'
861 CREATE TABLE t1(
862 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
863 PRIMARY KEY) ENGINE=InnoDB;
864 Warnings:
865 Warning 1681 Integer display width is deprecated and will be removed in a future release.
866 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
867 CREATE TABLE t2(
868 c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
869 PRIMARY KEY) ENGINE=InnoDB;
870 Warnings:
871 Warning 1681 Integer display width is deprecated and will be removed in a future release.
872 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
873 INSERT INTO t2 SELECT c1 FROM t1;
874 Got one of the listed errors
875 SET sql_mode = default;
876 INSERT INTO t2 SELECT NULL FROM t1;
877 Got one of the listed errors
878 DROP TABLE t1;
879 DROP TABLE t2;
880 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
881 SHOW VARIABLES LIKE "%auto_inc%";
882 Variable_name Value
883 auto_increment_increment 1
884 auto_increment_offset 1
885 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
886 INSERT INTO t1 VALUES (1, NULL);
887 INSERT INTO t1 VALUES (-1, 'innodb');
888 INSERT INTO t1 VALUES (-127, 'innodb');
889 INSERT INTO t1 VALUES (NULL, NULL);
890 SHOW CREATE TABLE t1;
891 Table Create Table
892 t1 CREATE TABLE `t1` (
893 `c1` tinyint NOT NULL AUTO_INCREMENT,
894 `c2` varchar(10) DEFAULT NULL,
895 PRIMARY KEY (`c1`)
896 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
897 SELECT * FROM t1;
898 c1 c2
899 -127 innodb
900 -1 innodb
901 1 NULL
902 2 NULL
903 DROP TABLE t1;
904 CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
905 INSERT INTO t1 VALUES (1, NULL);
906 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
907 Warnings:
908 Warning 1264 Out of range value for column 'c1' at row 1
909 INSERT IGNORE INTO t1 VALUES (-127, 'innodb');
910 Warnings:
911 Warning 1264 Out of range value for column 'c1' at row 1
912 INSERT INTO t1 VALUES (NULL, NULL);
913 SHOW CREATE TABLE t1;
914 Table Create Table
915 t1 CREATE TABLE `t1` (
916 `c1` tinyint unsigned NOT NULL AUTO_INCREMENT,
917 `c2` varchar(10) DEFAULT NULL,
918 PRIMARY KEY (`c1`)
919 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
920 SELECT * FROM t1;
921 c1 c2
922 1 NULL
923 2 innodb
924 3 innodb
925 4 NULL
926 DROP TABLE t1;
927 CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
928 INSERT INTO t1 VALUES (1, NULL);
929 INSERT INTO t1 VALUES (-1, 'innodb');
930 INSERT INTO t1 VALUES (-32767, 'innodb');
931 INSERT INTO t1 VALUES (NULL, NULL);
932 SHOW CREATE TABLE t1;
933 Table Create Table
934 t1 CREATE TABLE `t1` (
935 `c1` smallint NOT NULL AUTO_INCREMENT,
936 `c2` varchar(10) DEFAULT NULL,
937 PRIMARY KEY (`c1`)
938 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
939 SELECT * FROM t1;
940 c1 c2
941 -32767 innodb
942 -1 innodb
943 1 NULL
944 2 NULL
945 DROP TABLE t1;
946 CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
947 INSERT INTO t1 VALUES (1, NULL);
948 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
949 Warnings:
950 Warning 1264 Out of range value for column 'c1' at row 1
951 INSERT IGNORE INTO t1 VALUES (-32757, 'innodb');
952 Warnings:
953 Warning 1264 Out of range value for column 'c1' at row 1
954 INSERT INTO t1 VALUES (NULL, NULL);
955 SHOW CREATE TABLE t1;
956 Table Create Table
957 t1 CREATE TABLE `t1` (
958 `c1` smallint unsigned NOT NULL AUTO_INCREMENT,
959 `c2` varchar(10) DEFAULT NULL,
960 PRIMARY KEY (`c1`)
961 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
962 SELECT * FROM t1;
963 c1 c2
964 1 NULL
965 2 innodb
966 3 innodb
967 4 NULL
968 DROP TABLE t1;
969 CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
970 INSERT INTO t1 VALUES (1, NULL);
971 INSERT INTO t1 VALUES (-1, 'innodb');
972 INSERT INTO t1 VALUES (-8388607, 'innodb');
973 INSERT INTO t1 VALUES (NULL, NULL);
974 SHOW CREATE TABLE t1;
975 Table Create Table
976 t1 CREATE TABLE `t1` (
977 `c1` mediumint NOT NULL AUTO_INCREMENT,
978 `c2` varchar(10) DEFAULT NULL,
979 PRIMARY KEY (`c1`)
980 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
981 SELECT * FROM t1;
982 c1 c2
983 -8388607 innodb
984 -1 innodb
985 1 NULL
986 2 NULL
987 DROP TABLE t1;
988 CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
989 INSERT INTO t1 VALUES (1, NULL);
990 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
991 Warnings:
992 Warning 1264 Out of range value for column 'c1' at row 1
993 INSERT IGNORE INTO t1 VALUES (-8388607, 'innodb');
994 Warnings:
995 Warning 1264 Out of range value for column 'c1' at row 1
996 INSERT INTO t1 VALUES (NULL, NULL);
997 SHOW CREATE TABLE t1;
998 Table Create Table
999 t1 CREATE TABLE `t1` (
1000 `c1` mediumint unsigned NOT NULL AUTO_INCREMENT,
1001 `c2` varchar(10) DEFAULT NULL,
1002 PRIMARY KEY (`c1`)
1003 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1004 SELECT * FROM t1;
1005 c1 c2
1006 1 NULL
1007 2 innodb
1008 3 innodb
1009 4 NULL
1010 DROP TABLE t1;
1011 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
1012 INSERT INTO t1 VALUES (1, NULL);
1013 INSERT INTO t1 VALUES (-1, 'innodb');
1014 INSERT INTO t1 VALUES (-2147483647, 'innodb');
1015 INSERT INTO t1 VALUES (NULL, NULL);
1016 SHOW CREATE TABLE t1;
1017 Table Create Table
1018 t1 CREATE TABLE `t1` (
1019 `c1` int NOT NULL AUTO_INCREMENT,
1020 `c2` varchar(10) DEFAULT NULL,
1021 PRIMARY KEY (`c1`)
1022 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1023 SELECT * FROM t1;
1024 c1 c2
1025 -2147483647 innodb
1026 -1 innodb
1027 1 NULL
1028 2 NULL
1029 DROP TABLE t1;
1030 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
1031 INSERT INTO t1 VALUES (1, NULL);
1032 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
1033 Warnings:
1034 Warning 1264 Out of range value for column 'c1' at row 1
1035 INSERT IGNORE INTO t1 VALUES (-2147483647, 'innodb');
1036 Warnings:
1037 Warning 1264 Out of range value for column 'c1' at row 1
1038 INSERT INTO t1 VALUES (NULL, NULL);
1039 SHOW CREATE TABLE t1;
1040 Table Create Table
1041 t1 CREATE TABLE `t1` (
1042 `c1` int unsigned NOT NULL AUTO_INCREMENT,
1043 `c2` varchar(10) DEFAULT NULL,
1044 PRIMARY KEY (`c1`)
1045 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1046 SELECT * FROM t1;
1047 c1 c2
1048 1 NULL
1049 2 innodb
1050 3 innodb
1051 4 NULL
1052 DROP TABLE t1;
1053 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
1054 INSERT INTO t1 VALUES (1, NULL);
1055 INSERT INTO t1 VALUES (-1, 'innodb');
1056 INSERT INTO t1 VALUES (-9223372036854775807, 'innodb');
1057 INSERT INTO t1 VALUES (NULL, NULL);
1058 SHOW CREATE TABLE t1;
1059 Table Create Table
1060 t1 CREATE TABLE `t1` (
1061 `c1` bigint NOT NULL AUTO_INCREMENT,
1062 `c2` varchar(10) DEFAULT NULL,
1063 PRIMARY KEY (`c1`)
1064 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1065 SELECT * FROM t1;
1066 c1 c2
1067 -9223372036854775807 innodb
1068 -1 innodb
1069 1 NULL
1070 2 NULL
1071 DROP TABLE t1;
1072 CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
1073 INSERT INTO t1 VALUES (1, NULL);
1074 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
1075 Warnings:
1076 Warning 1264 Out of range value for column 'c1' at row 1
1077 INSERT IGNORE INTO t1 VALUES (-9223372036854775807, 'innodb');
1078 Warnings:
1079 Warning 1264 Out of range value for column 'c1' at row 1
1080 INSERT INTO t1 VALUES (NULL, NULL);
1081 SHOW CREATE TABLE t1;
1082 Table Create Table
1083 t1 CREATE TABLE `t1` (
1084 `c1` bigint unsigned NOT NULL AUTO_INCREMENT,
1085 `c2` varchar(10) DEFAULT NULL,
1086 PRIMARY KEY (`c1`)
1087 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1088 SELECT * FROM t1;
1089 c1 c2
1090 1 NULL
1091 2 innodb
1092 3 innodb
1093 4 NULL
1094 DROP TABLE t1;
1095 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB;
1096 CREATE INDEX i1 on t1(c2);
1097 SHOW CREATE TABLE t1;
1098 Table Create Table
1099 t1 CREATE TABLE `t1` (
1100 `c1` int NOT NULL AUTO_INCREMENT,
1101 `c2` int DEFAULT NULL,
1102 PRIMARY KEY (`c1`),
1103 KEY `i1` (`c2`)
1104 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1105 INSERT INTO t1 (c2) values (0);
1106 SELECT * FROM t1;
1107 c1 c2
1108 10 0
1109 DROP TABLE t1;
1110 DROP TABLE IF EXISTS t1;
1111 Warnings:
1112 Note 1051 Unknown table 'test.t1'
1113 CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB;
1114 Warnings:
1115 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'C1'.
1116 INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb');
1117 # restart
1118 INSERT INTO t1(C2) VALUES ('innodb');
1119 SHOW CREATE TABLE t1;
1120 Table Create Table
1121 t1 CREATE TABLE `t1` (
1122 `C1` double NOT NULL AUTO_INCREMENT,
1123 `C2` char(10) DEFAULT NULL,
1124 PRIMARY KEY (`C1`)
1125 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1126 DROP TABLE t1;
1127 CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB;
1128 Warnings:
1129 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'C1'.
1130 INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb');
1131 # restart
1132 INSERT INTO t1(C2) VALUES ('innodb');
1133 SHOW CREATE TABLE t1;
1134 Table Create Table
1135 t1 CREATE TABLE `t1` (
1136 `C1` float NOT NULL AUTO_INCREMENT,
1137 `C2` char(10) DEFAULT NULL,
1138 PRIMARY KEY (`C1`)
1139 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1140 DROP TABLE t1;
1141 DROP TABLE IF EXISTS t1;
1142 Warnings:
1143 Note 1051 Unknown table 'test.t1'
1144 CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
1145 INSERT INTO t1 SET c1 = 1;
1146 SHOW CREATE TABLE t1;
1147 Table Create Table
1148 t1 CREATE TABLE `t1` (
1149 `c1` int NOT NULL AUTO_INCREMENT,
1150 PRIMARY KEY (`c1`)
1151 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1152 INSERT INTO t1 SET c1 = 2;
1153 INSERT INTO t1 SET c1 = -1;
1154 SELECT * FROM t1;
1155 c1
1156 -1
1157 1
1158 2
1159 INSERT INTO t1 SET c1 = -1;
1160 Got one of the listed errors
1161 SHOW CREATE TABLE t1;
1162 Table Create Table
1163 t1 CREATE TABLE `t1` (
1164 `c1` int NOT NULL AUTO_INCREMENT,
1165 PRIMARY KEY (`c1`)
1166 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1167 REPLACE INTO t1 VALUES (-1);
1168 SELECT * FROM t1;
1169 c1
1170 -1
1171 1
1172 2
1173 SHOW CREATE TABLE t1;
1174 Table Create Table
1175 t1 CREATE TABLE `t1` (
1176 `c1` int NOT NULL AUTO_INCREMENT,
1177 PRIMARY KEY (`c1`)
1178 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1179 DROP TABLE t1;
1180 DROP TABLE IF EXISTS t1;
1181 Warnings:
1182 Note 1051 Unknown table 'test.t1'
1183 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
1184 INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL);
1185 SELECT * FROM t1;
1186 c1
1187 -685113344
1188 1
1189 2
1190 3
1191 SHOW CREATE TABLE t1;
1192 Table Create Table
1193 t1 CREATE TABLE `t1` (
1194 `c1` int NOT NULL AUTO_INCREMENT,
1195 PRIMARY KEY (`c1`)
1196 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1197 DROP TABLE t1;
1198 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
1199 INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL);
1200 SELECT * FROM t1;
1201 c1
1202 -685113344
1203 2
1204 3
1205 4
1206 SHOW CREATE TABLE t1;
1207 Table Create Table
1208 t1 CREATE TABLE `t1` (
1209 `c1` int NOT NULL AUTO_INCREMENT,
1210 PRIMARY KEY (`c1`)
1211 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1212 DROP TABLE t1;
1213 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
1214 INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL);
1215 INSERT INTO t1 VALUES (4), (5), (6), (NULL);
1216 SELECT * FROM t1;
1217 c1
1218 -685113344
1219 1
1220 2
1221 3
1222 4
1223 5
1224 6
1225 7
1226 SHOW CREATE TABLE t1;
1227 Table Create Table
1228 t1 CREATE TABLE `t1` (
1229 `c1` int NOT NULL AUTO_INCREMENT,
1230 PRIMARY KEY (`c1`)
1231 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1232 DROP TABLE t1;
1233 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
1234 INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5);
1235 SELECT * FROM t1;
1236 c1
1237 -685113344
1238 1
1239 2
1240 5
1241 SHOW CREATE TABLE t1;
1242 Table Create Table
1243 t1 CREATE TABLE `t1` (
1244 `c1` int NOT NULL AUTO_INCREMENT,
1245 PRIMARY KEY (`c1`)
1246 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1247 DROP TABLE t1;
1248 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
1249 INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL);
1250 SELECT * FROM t1;
1251 c1
1252 -685113344
1253 1
1254 2
1255 3
1256 SHOW CREATE TABLE t1;
1257 Table Create Table
1258 t1 CREATE TABLE `t1` (
1259 `c1` int NOT NULL AUTO_INCREMENT,
1260 PRIMARY KEY (`c1`)
1261 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1262 DROP TABLE t1;
1263 DROP TABLE IF EXISTS t1;
1264 Warnings:
1265 Note 1051 Unknown table 'test.t1'
1266 CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
1267 INSERT INTO t1 VALUES (NULL);
1268 INSERT INTO t1 VALUES (18446744073709551615);
1269 # restart
1270 SHOW CREATE TABLE t1;
1271 Table Create Table
1272 t1 CREATE TABLE `t1` (
1273 `c1` bigint unsigned NOT NULL AUTO_INCREMENT,
1274 PRIMARY KEY (`c1`)
1275 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1276 DROP TABLE t1;
1277 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=256;
1278 SHOW VARIABLES LIKE "%auto_inc%";
1279 Variable_name Value
1280 auto_increment_increment 1
1281 auto_increment_offset 256
1282 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
1283 INSERT INTO t1 VALUES (1, NULL);
1284 SHOW CREATE TABLE t1;
1285 Table Create Table
1286 t1 CREATE TABLE `t1` (
1287 `c1` tinyint NOT NULL AUTO_INCREMENT,
1288 `c2` varchar(10) DEFAULT NULL,
1289 PRIMARY KEY (`c1`)
1290 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1291 SELECT * FROM t1;
1292 c1 c2
1293 1 NULL
1294 DROP TABLE t1;
1295 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
1296 SHOW VARIABLES LIKE "%auto_inc%";
1297 Variable_name Value
1298 auto_increment_increment 1
1299 auto_increment_offset 1
1300 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
1301 INSERT INTO t1 VALUES (2147483648, 'a');
1302 SHOW CREATE TABLE t1;
1303 Table Create Table
1304 t1 CREATE TABLE `t1` (
1305 `c1` int unsigned NOT NULL AUTO_INCREMENT,
1306 `c2` varchar(10) DEFAULT NULL,
1307 PRIMARY KEY (`c1`)
1308 ) ENGINE=InnoDB AUTO_INCREMENT=2147483649 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1309 SELECT * FROM t1;
1310 c1 c2
1311 2147483648 a
1312 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
1313 ALTER TABLE t1 CHANGE c1 c1 INT;
1314 Warnings:
1315 Warning 1264 Out of range value for column 'c1' at row 1
1316 SHOW CREATE TABLE t1;
1317 Table Create Table
1318 t1 CREATE TABLE `t1` (
1319 `c1` int NOT NULL,
1320 `c2` varchar(10) DEFAULT NULL,
1321 PRIMARY KEY (`c1`)
1322 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1323 INSERT INTO t1(c2) VALUES('b');
1324 Warnings:
1325 Warning 1364 Field 'c1' doesn't have a default value
1326 SELECT * FROM t1;
1327 c1 c2
1328 0 b
1329 2147483647 a
1330 SHOW CREATE TABLE t1;
1331 Table Create Table
1332 t1 CREATE TABLE `t1` (
1333 `c1` int NOT NULL,
1334 `c2` varchar(10) DEFAULT NULL,
1335 PRIMARY KEY (`c1`)
1336 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1337 DROP TABLE t1;
1338 SET sql_mode = default;
1339 CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE = MyISAM;
1340 INSERT INTO t1 (c1) VALUES (NULL), (-290783232), (NULL);
1341 SHOW CREATE TABLE t1;
1342 Table Create Table
1343 t1 CREATE TABLE `t1` (
1344 `c1` int NOT NULL AUTO_INCREMENT,
1345 `c2` int DEFAULT NULL,
1346 PRIMARY KEY (`c1`)
1347 ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1348 SELECT * FROM t1;
1349 c1 c2
1350 1 NULL
1351 -290783232 NULL
1352 2 NULL
1353 ALTER TABLE t1 ENGINE = InnoDB;
1354 SELECT * FROM t1;
1355 c1 c2
1356 -290783232 NULL
1357 1 NULL
1358 2 NULL
1359 SHOW CREATE TABLE t1;
1360 Table Create Table
1361 t1 CREATE TABLE `t1` (
1362 `c1` int NOT NULL AUTO_INCREMENT,
1363 `c2` int DEFAULT NULL,
1364 PRIMARY KEY (`c1`)
1365 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1366 REPLACE INTO t1 (c2 ) VALUES (0);
1367 SELECT * FROM t1;
1368 c1 c2
1369 -290783232 NULL
1370 1 NULL
1371 2 NULL
1372 3 0
1373 DROP TABLE t1;
1374 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
1375 AUTO_INCREMENT=10000000000000000000;
1376 Warnings:
1377 Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'c1'.
1378 SHOW CREATE TABLE t1;
1379 Table Create Table
1380 t1 CREATE TABLE `t1` (
1381 `c1` double NOT NULL AUTO_INCREMENT,
1382 PRIMARY KEY (`c1`)
1383 ) ENGINE=InnoDB AUTO_INCREMENT=10000000000000000000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1384 INSERT INTO t1 VALUES ();
1385 ERROR HY000: Failed to read auto-increment value from storage engine
1386 DROP TABLE t1;
1387 #
1388 # Bug #14049391 INNODB MISCALCULATES AUTO-INCREMENT
1389 # AFTER CHANGING AUTO_INCREMENT_INCREMEMENT
1390 #
1391 CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=InnoDB;
1392 SET auto_increment_increment = 300;
1393 INSERT INTO t VALUES (NULL), (NULL);
1394 SHOW CREATE TABLE t;
1395 Table Create Table
1396 t CREATE TABLE `t` (
1397 `i` int NOT NULL AUTO_INCREMENT,
1398 KEY `i` (`i`)
1399 ) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1400 SET auto_increment_increment = 50;
1401 INSERT INTO t VALUES (NULL);
1402 SELECT * FROM t;
1403 i
1404 1
1405 301
1406 601
1407 SHOW CREATE TABLE t;
1408 Table Create Table
1409 t CREATE TABLE `t` (
1410 `i` int NOT NULL AUTO_INCREMENT,
1411 KEY `i` (`i`)
1412 ) ENGINE=InnoDB AUTO_INCREMENT=651 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1413 DROP TABLE t;
1414 #
1415 # Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS
1416 #
1417 # This test shows that the next record to be inserted is not affected
1418 # by a change in auto_increment_increment.
1419 # In addition, current value of auto_increment_increment by the client
1420 # that uses the existing autoinc value with be used to set next autoinc
1421 # value, which will be used by next client reguardless of its own session
1422 # setting for auto_increment_increment.
1423 #
1424 # Client 1: Insert a record with auto_increment_increment=2
1425 CREATE TABLE t(
1426 a SERIAL PRIMARY KEY,
1427 b VARCHAR(200)) ENGINE=InnoDB;
1428 SET SESSION auto_increment_increment=2;
1429 SHOW CREATE TABLE t;
1430 Table Create Table
1431 t CREATE TABLE `t` (
1432 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1433 `b` varchar(200) DEFAULT NULL,
1434 PRIMARY KEY (`a`),
1435 UNIQUE KEY `a` (`a`)
1436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1437 INSERT INTO t(b) VALUES('S1');
1438 SELECT a,b FROM t;
1439 a b
1440 1 S1
1441 # Client 2: Insert records with auto_increment_increment 2,1
1442 SET SESSION auto_increment_increment=2;
1443 SHOW CREATE TABLE t;
1444 Table Create Table
1445 t CREATE TABLE `t` (
1446 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1447 `b` varchar(200) DEFAULT NULL,
1448 PRIMARY KEY (`a`),
1449 UNIQUE KEY `a` (`a`)
1450 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1451 INSERT INTO t(b) VALUES('S2');
1452 SELECT a,b FROM t;
1453 a b
1454 1 S1
1455 3 S2
1456 SET SESSION auto_increment_increment=1;
1457 SHOW CREATE TABLE t;
1458 Table Create Table
1459 t CREATE TABLE `t` (
1460 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1461 `b` varchar(200) DEFAULT NULL,
1462 PRIMARY KEY (`a`),
1463 UNIQUE KEY `a` (`a`)
1464 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1465 INSERT INTO t(b) VALUES('S2');
1466 SELECT a,b FROM t;
1467 a b
1468 1 S1
1469 3 S2
1470 5 S2
1471 # Client 1: Insert a record with auto_increment_increment=1
1472 SET SESSION auto_increment_increment=1;
1473 SHOW CREATE TABLE t;
1474 Table Create Table
1475 t CREATE TABLE `t` (
1476 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1477 `b` varchar(200) DEFAULT NULL,
1478 PRIMARY KEY (`a`),
1479 UNIQUE KEY `a` (`a`)
1480 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1481 INSERT INTO t(b) VALUES('S1');
1482 SELECT a,b FROM t;
1483 a b
1484 1 S1
1485 3 S2
1486 5 S2
1487 6 S1
1488 DROP TABLE t;
1489 # Autoincrement behaviour with mixed insert.
1490 CREATE TABLE t(
1491 a TINYINT AUTO_INCREMENT PRIMARY KEY,
1492 b VARCHAR(200)) ENGINE=InnoDB;
1493 SET SESSION auto_increment_increment=100;
1494 SHOW CREATE TABLE t;
1495 Table Create Table
1496 t CREATE TABLE `t` (
1497 `a` tinyint NOT NULL AUTO_INCREMENT,
1498 `b` varchar(200) DEFAULT NULL,
1499 PRIMARY KEY (`a`)
1500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1501 INSERT INTO t(b) VALUES('S0'),('S1');
1502 SELECT a,b FROM t;
1503 a b
1504 1 S0
1505 101 S1
1506 SHOW CREATE TABLE t;
1507 Table Create Table
1508 t CREATE TABLE `t` (
1509 `a` tinyint NOT NULL AUTO_INCREMENT,
1510 `b` varchar(200) DEFAULT NULL,
1511 PRIMARY KEY (`a`)
1512 ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1513 INSERT INTO t(a,b) VALUES(28,'S2');
1514 SELECT a,b FROM t;
1515 a b
1516 1 S0
1517 28 S2
1518 101 S1
1519 SET SESSION auto_increment_increment=1;
1520 SHOW CREATE TABLE t;
1521 Table Create Table
1522 t CREATE TABLE `t` (
1523 `a` tinyint NOT NULL AUTO_INCREMENT,
1524 `b` varchar(200) DEFAULT NULL,
1525 PRIMARY KEY (`a`)
1526 ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1527 INSERT INTO t(b) VALUES('S3');
1528 SELECT a,b FROM t;
1529 a b
1530 1 S0
1531 28 S2
1532 101 S1
1533 127 S3
1534 SHOW CREATE TABLE t;
1535 Table Create Table
1536 t CREATE TABLE `t` (
1537 `a` tinyint NOT NULL AUTO_INCREMENT,
1538 `b` varchar(200) DEFAULT NULL,
1539 PRIMARY KEY (`a`)
1540 ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1541 INSERT INTO t(b) VALUES('S4');
1542 ERROR 23000: Duplicate entry '127' for key 't.PRIMARY'
1543 DROP TABLE t;
1544 #
1545 # Bug 26906787 - INCORRECT AUTO_INCREMENT IDS ISSUED WHEN CLOSE TO ULLONG_MAX
1546 #
1547 SET auto_increment_increment = 300;
1548 CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8))
1549 ENGINE=innodb;
1550 INSERT INTO t1 VALUES (18446744073709551613, 'a');
1551 SHOW CREATE TABLE t1;
1552 Table Create Table
1553 t1 CREATE TABLE `t1` (
1554 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1555 `b` char(8) DEFAULT NULL,
1556 PRIMARY KEY (`a`)
1557 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1558 INSERT INTO t1 VALUES (NULL, 'b');
1559 ERROR 22003: Out of range value for column 't1' at row 167
1560 SHOW CREATE TABLE t1;
1561 Table Create Table
1562 t1 CREATE TABLE `t1` (
1563 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1564 `b` char(8) DEFAULT NULL,
1565 PRIMARY KEY (`a`)
1566 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1567 DROP TABLE t1;
1568 CREATE TABLE t1 (
1569 a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
1570 b CHAR(8)
1571 ) ENGINE=innodb
1572 PARTITION BY RANGE (a) (
1573 PARTITION p0 VALUES LESS THAN (6),
1574 PARTITION p1 VALUES LESS THAN MAXVALUE
1575 );
1576 INSERT INTO t1 VALUES (18446744073709551613, 'a');
1577 SHOW CREATE TABLE t1;
1578 Table Create Table
1579 t1 CREATE TABLE `t1` (
1580 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1581 `b` char(8) DEFAULT NULL,
1582 PRIMARY KEY (`a`)
1583 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1584 /*!50100 PARTITION BY RANGE (`a`)
1585 (PARTITION p0 VALUES LESS THAN (6) TABLESPACE = `innodb_system` ENGINE = InnoDB,
1586 PARTITION p1 VALUES LESS THAN MAXVALUE TABLESPACE = `innodb_system` ENGINE = InnoDB) */
1587 INSERT INTO t1 VALUES (NULL, 'b');
1588 ERROR 22003: Out of range value for column 't1' at row 167
1589 SHOW CREATE TABLE t1;
1590 Table Create Table
1591 t1 CREATE TABLE `t1` (
1592 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1593 `b` char(8) DEFAULT NULL,
1594 PRIMARY KEY (`a`)
1595 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1596 /*!50100 PARTITION BY RANGE (`a`)
1597 (PARTITION p0 VALUES LESS THAN (6) TABLESPACE = `innodb_system` ENGINE = InnoDB,
1598 PARTITION p1 VALUES LESS THAN MAXVALUE TABLESPACE = `innodb_system` ENGINE = InnoDB) */
1599 DROP TABLE t1;