1 -- source include/have_innodb.inc
2 # embedded server ignores 'delayed', so skip this
3 -- source include/not_embedded.inc
6 drop table if exists t1;
12 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
13 INSERT INTO t1 VALUES (9223372036854775807, null);
14 -- error ER_DUP_ENTRY,1062
15 INSERT INTO t1 (c2) VALUES ('innodb');
19 ## Test AUTOINC overflow
23 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
24 INSERT INTO t1 VALUES (127, null);
25 -- error ER_DUP_ENTRY,1062
26 INSERT INTO t1 (c2) VALUES ('innodb');
30 CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
31 INSERT INTO t1 VALUES (255, null);
32 -- error ER_DUP_ENTRY,1062
33 INSERT INTO t1 (c2) VALUES ('innodb');
39 CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
40 INSERT INTO t1 VALUES (32767, null);
41 -- error ER_DUP_ENTRY,1062
42 INSERT INTO t1 (c2) VALUES ('innodb');
46 CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
47 INSERT INTO t1 VALUES (65535, null);
48 -- error ER_DUP_ENTRY,1062
49 INSERT INTO t1 (c2) VALUES ('innodb');
55 CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
56 INSERT INTO t1 VALUES (8388607, null);
57 -- error ER_DUP_ENTRY,1062
58 INSERT INTO t1 (c2) VALUES ('innodb');
62 CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
63 INSERT INTO t1 VALUES (16777215, null);
64 -- error ER_DUP_ENTRY,1062
65 INSERT INTO t1 (c2) VALUES ('innodb');
71 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
72 INSERT INTO t1 VALUES (2147483647, null);
73 -- error ER_DUP_ENTRY,1062
74 INSERT INTO t1 (c2) VALUES ('innodb');
78 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
79 INSERT INTO t1 VALUES (4294967295, null);
80 -- error ER_DUP_ENTRY,1062
81 INSERT INTO t1 (c2) VALUES ('innodb');
87 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
88 INSERT INTO t1 VALUES (9223372036854775807, null);
89 -- error ER_DUP_ENTRY,1062
90 INSERT INTO t1 (c2) VALUES ('innodb');
94 CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
95 INSERT INTO t1 VALUES (18446744073709551615, null);
96 -- error ER_AUTOINC_READ_FAILED,1467
97 INSERT INTO t1 (c2) VALUES ('innodb');
103 # After truncate, auto_increment behaves incorrectly for InnoDB
105 CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
106 INSERT INTO t1 VALUES (1), (2), (3);
107 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
109 SHOW CREATE TABLE t1;
111 SHOW CREATE TABLE t1;
112 INSERT INTO t1 VALUES (1), (2), (3);
113 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
115 SHOW CREATE TABLE t1;
119 # Deleting all records should not reset the AUTOINC counter.
121 CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
122 INSERT INTO t1 VALUES (1), (2), (3);
123 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
125 SHOW CREATE TABLE t1;
127 SHOW CREATE TABLE t1;
128 INSERT INTO t1 VALUES (1), (2), (3);
129 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
131 SHOW CREATE TABLE t1;
136 # Reset the last value generated at end of statement
138 DROP TABLE IF EXISTS t1;
139 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB;
140 INSERT INTO t1 VALUES (NULL, 1);
141 DELETE FROM t1 WHERE c1 = 1;
142 INSERT INTO t1 VALUES (2,1);
143 INSERT INTO t1 VALUES (NULL,8);
146 # Bug 38839 -- same as above but for multi value insert
147 DROP TABLE IF EXISTS t1;
148 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB;
149 INSERT INTO t1 VALUES (NULL, 1);
150 DELETE FROM t1 WHERE c1 = 1;
151 INSERT INTO t1 VALUES (2,1), (NULL, 8);
152 INSERT INTO t1 VALUES (NULL,9);
157 # Test changes to AUTOINC next value calculation
158 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
159 SHOW VARIABLES LIKE "%auto_inc%";
160 DROP TABLE IF EXISTS t1;
161 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
162 INSERT INTO t1 VALUES (NULL),(5),(NULL);
163 INSERT INTO t1 VALUES (250),(NULL);
165 INSERT INTO t1 VALUES (1000);
167 INSERT INTO t1 VALUES(NULL),(NULL);
171 # Test with SIGNED INT column, by inserting a 0 for the first column value
172 # 0 is treated in the same was NULL.
173 # Reset the AUTOINC session variables
174 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
176 SHOW VARIABLES LIKE "%auto_inc%";
177 DROP TABLE IF EXISTS t1;
178 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
179 INSERT INTO t1 VALUES(0);
181 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
182 INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL);
183 INSERT INTO t1 VALUES (250),(NULL);
186 # Duplicate error expected here for autoinc_lock_mode != TRADITIONAL
187 -- error ER_DUP_ENTRY,1062
188 INSERT INTO t1 VALUES(NULL),(NULL);
192 # Test with SIGNED INT column
193 # Reset the AUTOINC session variables
194 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
196 SHOW VARIABLES LIKE "%auto_inc%";
197 DROP TABLE IF EXISTS t1;
198 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
199 INSERT INTO t1 VALUES(-1);
201 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
202 SHOW VARIABLES LIKE "%auto_inc%";
203 INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL);
204 INSERT INTO t1 VALUES (250),(NULL);
206 INSERT INTO t1 VALUES (1000);
208 INSERT INTO t1 VALUES(NULL),(NULL);
212 # Test with UNSIGNED INT column, single insert
213 # The sign in the value is ignored and a new column value is generated
214 # Reset the AUTOINC session variables
215 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
217 SHOW VARIABLES LIKE "%auto_inc%";
218 DROP TABLE IF EXISTS t1;
219 CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
220 INSERT INTO t1 VALUES(-1);
222 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
223 SHOW VARIABLES LIKE "%auto_inc%";
224 INSERT INTO t1 VALUES (-2);
225 INSERT INTO t1 VALUES (NULL);
226 INSERT INTO t1 VALUES (2);
227 INSERT INTO t1 VALUES (NULL);
228 INSERT INTO t1 VALUES (250);
229 INSERT INTO t1 VALUES (NULL);
231 INSERT INTO t1 VALUES (1000);
233 INSERT INTO t1 VALUES(NULL);
234 INSERT INTO t1 VALUES(NULL);
238 # Test with UNSIGNED INT column, multi-value inserts
239 # The sign in the value is ignored and a new column value is generated
240 # Reset the AUTOINC session variables
241 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
243 SHOW VARIABLES LIKE "%auto_inc%";
244 DROP TABLE IF EXISTS t1;
245 CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
246 INSERT INTO t1 VALUES(-1);
248 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
249 SHOW VARIABLES LIKE "%auto_inc%";
250 INSERT INTO t1 VALUES (-2),(NULL),(2),(NULL);
251 INSERT INTO t1 VALUES (250),(NULL);
253 INSERT INTO t1 VALUES (1000);
255 # Duplicate error expected here for autoinc_lock_mode != TRADITIONAL
256 -- error ER_DUP_ENTRY,1062
257 INSERT INTO t1 VALUES(NULL),(NULL);
262 # Check for overflow handling when increment is > 1
263 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
265 SHOW VARIABLES LIKE "%auto_inc%";
266 DROP TABLE IF EXISTS t1;
267 CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
268 # TODO: Fix the autoinc init code
269 # We have to do this because of a bug in the AUTOINC init code.
270 INSERT INTO t1 VALUES(NULL);
271 INSERT INTO t1 VALUES (9223372036854775794); #-- 2^63 - 14
273 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
274 SHOW VARIABLES LIKE "%auto_inc%";
275 # This should just fit
276 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
281 # Check for overflow handling when increment and offser are > 1
282 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
284 SHOW VARIABLES LIKE "%auto_inc%";
285 DROP TABLE IF EXISTS t1;
286 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
287 # TODO: Fix the autoinc init code
288 # We have to do this because of a bug in the AUTOINC init code.
289 INSERT INTO t1 VALUES(NULL);
290 INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13
292 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
293 SHOW VARIABLES LIKE "%auto_inc%";
294 --error ER_AUTOINC_READ_FAILED
295 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
300 # Check for overflow handling when increment and offset are odd numbers
301 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
303 SHOW VARIABLES LIKE "%auto_inc%";
304 DROP TABLE IF EXISTS t1;
305 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
306 # TODO: Fix the autoinc init code
307 # We have to do this because of a bug in the AUTOINC init code.
308 INSERT INTO t1 VALUES(NULL);
309 INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13
311 SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7;
312 SHOW VARIABLES LIKE "%auto_inc%";
313 --error ER_AUTOINC_READ_FAILED
314 INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
318 # Check for overflow handling when increment and offset are odd numbers
319 # and check for large -ve numbers
320 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
322 SHOW VARIABLES LIKE "%auto_inc%";
323 DROP TABLE IF EXISTS t1;
324 CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
325 # TODO: Fix the autoinc init code
326 # We have to do this because of a bug in the AUTOINC init code.
327 INSERT INTO t1 VALUES(NULL);
328 INSERT INTO t1 VALUES(-9223372036854775806); #-- -2^63 + 2
329 INSERT INTO t1 VALUES(-9223372036854775807); #-- -2^63 + 1
330 INSERT INTO t1 VALUES(-9223372036854775808); #-- -2^63
332 SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3;
333 SHOW VARIABLES LIKE "%auto_inc%";
334 INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
338 # Check for overflow handling when increment and offset are very
340 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
342 SHOW VARIABLES LIKE "%auto_inc%";
343 DROP TABLE IF EXISTS t1;
344 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
345 # TODO: Fix the autoinc init code
346 # We have to do this because of a bug in the AUTOINC init code.
347 INSERT INTO t1 VALUES(NULL);
348 INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2
350 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976;
351 SHOW VARIABLES LIKE "%auto_inc%";
352 --error ER_WARN_DATA_OUT_OF_RANGE
353 INSERT INTO t1 VALUES (NULL),(NULL);
358 # Check for floating point autoinc column handling
360 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
362 SHOW VARIABLES LIKE "%auto_inc%";
363 CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
364 INSERT INTO t1 VALUES(NULL, 1);
365 INSERT INTO t1 VALUES(NULL, 2);
367 ALTER TABLE t1 CHANGE c1 c1 SERIAL;
369 INSERT INTO t1 VALUES(NULL, 3);
370 INSERT INTO t1 VALUES(NULL, 4);
372 DROP TABLE IF EXISTS t1;
373 CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
374 INSERT INTO t1 VALUES(NULL, 1);
375 INSERT INTO t1 VALUES(NULL, 2);
377 ALTER TABLE t1 CHANGE c1 c1 SERIAL;
379 INSERT INTO t1 VALUES(NULL, 3);
380 INSERT INTO t1 VALUES(NULL, 4);
385 # Bug# 42714: AUTOINC column calculated next value not greater than highest
386 # value stored in table.
388 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
389 DROP TABLE IF EXISTS t1;
390 DROP TABLE IF EXISTS t2;
392 a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
393 b INT(10) UNSIGNED NOT NULL,
394 c ENUM('FALSE','TRUE') DEFAULT NULL,
395 PRIMARY KEY (a)) ENGINE = InnoDB;
397 m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
398 n INT(10) UNSIGNED NOT NULL,
399 o enum('FALSE','TRUE') DEFAULT NULL,
400 PRIMARY KEY (m)) ENGINE = InnoDB;
401 INSERT INTO t2 (n,o) VALUES
402 (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
403 (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
404 SHOW CREATE TABLE t2;
405 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
406 SHOW CREATE TABLE t1;
407 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
409 SHOW CREATE TABLE t1;
410 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
412 SHOW CREATE TABLE t1;
413 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
415 SHOW CREATE TABLE t1;
416 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
417 SHOW CREATE TABLE t1;
418 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
419 SHOW CREATE TABLE t1;
420 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
422 SHOW CREATE TABLE t1;
426 # 43203: Overflow from auto incrementing causes server segv
429 DROP TABLE IF EXISTS t1;
430 DROP TABLE IF EXISTS t2;
432 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
433 PRIMARY KEY) ENGINE=InnoDB;
434 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);
436 c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
437 PRIMARY KEY) ENGINE=InnoDB;
438 -- error ER_DUP_ENTRY,1062
439 INSERT INTO t2 SELECT c1 FROM t1;
440 -- error ER_DUP_ENTRY,1467
441 INSERT INTO t2 SELECT NULL FROM t1;
445 # If the user has specified negative values for an AUTOINC column then
446 # InnoDB should ignore those values when setting the table's max value.
447 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
448 SHOW VARIABLES LIKE "%auto_inc%";
450 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
451 INSERT INTO t1 VALUES (1, NULL);
452 INSERT INTO t1 VALUES (-1, 'innodb');
453 INSERT INTO t1 VALUES (-127, 'innodb');
454 INSERT INTO t1 VALUES (NULL, NULL);
455 SHOW CREATE TABLE t1;
459 CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
460 INSERT INTO t1 VALUES (1, NULL);
461 INSERT INTO t1 VALUES (-1, 'innodb');
462 INSERT INTO t1 VALUES (-127, 'innodb');
463 INSERT INTO t1 VALUES (NULL, NULL);
464 SHOW CREATE TABLE t1;
470 CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
471 INSERT INTO t1 VALUES (1, NULL);
472 INSERT INTO t1 VALUES (-1, 'innodb');
473 INSERT INTO t1 VALUES (-32767, 'innodb');
474 INSERT INTO t1 VALUES (NULL, NULL);
475 SHOW CREATE TABLE t1;
479 CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
480 INSERT INTO t1 VALUES (1, NULL);
481 INSERT INTO t1 VALUES (-1, 'innodb');
482 INSERT INTO t1 VALUES (-32757, 'innodb');
483 INSERT INTO t1 VALUES (NULL, NULL);
484 SHOW CREATE TABLE t1;
490 CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
491 INSERT INTO t1 VALUES (1, NULL);
492 INSERT INTO t1 VALUES (-1, 'innodb');
493 INSERT INTO t1 VALUES (-8388607, 'innodb');
494 INSERT INTO t1 VALUES (NULL, NULL);
495 SHOW CREATE TABLE t1;
499 CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
500 INSERT INTO t1 VALUES (1, NULL);
501 INSERT INTO t1 VALUES (-1, 'innodb');
502 INSERT INTO t1 VALUES (-8388607, 'innodb');
503 INSERT INTO t1 VALUES (NULL, NULL);
504 SHOW CREATE TABLE t1;
510 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
511 INSERT INTO t1 VALUES (1, NULL);
512 INSERT INTO t1 VALUES (-1, 'innodb');
513 INSERT INTO t1 VALUES (-2147483647, 'innodb');
514 INSERT INTO t1 VALUES (NULL, NULL);
515 SHOW CREATE TABLE t1;
519 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
520 INSERT INTO t1 VALUES (1, NULL);
521 INSERT INTO t1 VALUES (-1, 'innodb');
522 INSERT INTO t1 VALUES (-2147483647, 'innodb');
523 INSERT INTO t1 VALUES (NULL, NULL);
524 SHOW CREATE TABLE t1;
530 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
531 INSERT INTO t1 VALUES (1, NULL);
532 INSERT INTO t1 VALUES (-1, 'innodb');
533 INSERT INTO t1 VALUES (-9223372036854775807, 'innodb');
534 INSERT INTO t1 VALUES (NULL, NULL);
535 SHOW CREATE TABLE t1;
539 CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
540 INSERT INTO t1 VALUES (1, NULL);
541 INSERT INTO t1 VALUES (-1, 'innodb');
542 INSERT INTO t1 VALUES (-9223372036854775807, 'innodb');
543 INSERT INTO t1 VALUES (NULL, NULL);
544 SHOW CREATE TABLE t1;
548 # End negative number check
551 # 47125: auto_increment start value is ignored if an index is created
554 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB;
555 CREATE INDEX i1 on t1(c2);
556 SHOW CREATE TABLE t1;
557 INSERT INTO t1 (c2) values (0);
562 # 49032: Use the correct function to read the AUTOINC column value
564 DROP TABLE IF EXISTS t1;
565 CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB;
566 INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb');
568 -- source include/restart_mysqld.inc
569 INSERT INTO t1(C2) VALUES ('innodb');
570 SHOW CREATE TABLE t1;
572 CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB;
573 INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb');
575 -- source include/restart_mysqld.inc
576 INSERT INTO t1(C2) VALUES ('innodb');
577 SHOW CREATE TABLE t1;
581 # 47720: REPLACE INTO Autoincrement column with negative values
583 DROP TABLE IF EXISTS t1;
584 CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
585 INSERT INTO t1 SET c1 = 1;
586 SHOW CREATE TABLE t1;
587 INSERT INTO t1 SET c1 = 2;
588 INSERT INTO t1 SET c1 = -1;
590 -- error ER_DUP_ENTRY,1062
591 INSERT INTO t1 SET c1 = -1;
592 SHOW CREATE TABLE t1;
593 REPLACE INTO t1 VALUES (-1);
595 SHOW CREATE TABLE t1;
599 # 49497: Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value
601 DROP TABLE IF EXISTS t1;
602 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
603 INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL);
605 SHOW CREATE TABLE t1;
607 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
608 INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL);
610 SHOW CREATE TABLE t1;
612 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
613 INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL);
614 INSERT INTO t1 VALUES (4), (5), (6), (NULL);
616 SHOW CREATE TABLE t1;
618 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
619 INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5);
621 SHOW CREATE TABLE t1;
623 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
624 INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL);
626 SHOW CREATE TABLE t1;
630 # 55277: Failing assertion: auto_inc > 0
632 DROP TABLE IF EXISTS t1;
633 CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
634 INSERT INTO t1 VALUES (NULL);
635 INSERT INTO t1 VALUES (18446744073709551615);
637 -- source include/restart_mysqld.inc
638 SHOW CREATE TABLE t1;