Debian Codegraph @

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

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