Edit File: innodb-autoinc.result
drop table if exists t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 9223372036854775807 NULL DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (127, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 127 NULL DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (255, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 255 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (32767, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 32767 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (65535, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 65535 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (8388607, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 8388607 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (16777215, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 16777215 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (2147483647, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 2147483647 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (4294967295, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 4294967295 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 9223372036854775807 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (18446744073709551615, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 18446744073709551615 NULL DROP TABLE t1; CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 4 5 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 TRUNCATE TABLE t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 4 5 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 4 5 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DELETE FROM t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 7 8 9 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL, 1); DELETE FROM t1 WHERE c1 = 1; INSERT INTO t1 VALUES (2,1); INSERT INTO t1 VALUES (NULL,8); SELECT * FROM t1; c1 c2 2 1 3 8 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL, 1); DELETE FROM t1 WHERE c1 = 1; INSERT INTO t1 VALUES (2,1), (NULL, 8); INSERT INTO t1 VALUES (NULL,9); SELECT * FROM t1; c1 c2 2 1 3 8 5 9 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL),(5),(NULL); INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 5 10 110 250 310 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); SELECT * FROM t1; c1 5 10 110 250 310 400 410 1000 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(0); SELECT * FROM t1; c1 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL); INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 -1 1 2 10 110 250 410 SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); Got one of the listed errors SELECT * FROM t1; c1 -1 1 2 10 110 250 410 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(-1); SELECT * FROM t1; c1 -1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL); INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 -2 -1 1 2 10 250 310 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); SELECT * FROM t1; c1 -2 -1 1 2 10 250 310 400 410 1000 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(-1); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 INSERT INTO t1 VALUES (-2); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (250); INSERT INTO t1 VALUES (NULL); SELECT * FROM t1; c1 1 2 10 110 210 250 310 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(NULL); SELECT * FROM t1; c1 1 2 10 110 210 250 310 400 1000 1010 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(-1); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 INSERT INTO t1 VALUES (-2),(NULL),(2),(NULL); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 1 2 10 110 210 250 410 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); Got one of the listed errors SELECT * FROM t1; c1 1 2 10 110 210 250 410 1000 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (9223372036854775794); SELECT * FROM t1; c1 1 9223372036854775794 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 2 auto_increment_offset 10 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); SELECT * FROM t1; c1 1 9223372036854775794 9223372036854775796 9223372036854775798 9223372036854775800 9223372036854775802 9223372036854775804 9223372036854775806 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (18446744073709551603); SELECT * FROM t1; c1 1 18446744073709551603 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 2 auto_increment_offset 10 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t1; c1 1 18446744073709551603 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (18446744073709551603); SELECT * FROM t1; c1 1 18446744073709551603 SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 5 auto_increment_offset 7 INSERT INTO t1 VALUES (NULL),(NULL), (NULL); ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t1; c1 1 18446744073709551603 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(-9223372036854775806); INSERT INTO t1 VALUES(-9223372036854775807); INSERT INTO t1 VALUES(-9223372036854775808); SELECT * FROM t1; c1 -9223372036854775808 -9223372036854775807 -9223372036854775806 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 3 auto_increment_offset 3 INSERT INTO t1 VALUES (NULL),(NULL), (NULL); SELECT * FROM t1; c1 -9223372036854775808 -9223372036854775807 -9223372036854775806 1 3 6 9 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (18446744073709551610); SELECT * FROM t1; c1 1 18446744073709551610 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; Warnings: Warning 1292 Truncated incorrect auto_increment_increment value: '1152921504606846976' Warning 1292 Truncated incorrect auto_increment_offset value: '1152921504606846976' SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 65535 auto_increment_offset 65535 INSERT INTO t1 VALUES (NULL),(NULL); ERROR 22003: Out of range value for column 't1' at row 167 SELECT * FROM t1; c1 1 18446744073709551610 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL, 1); INSERT INTO t1 VALUES(NULL, 2); SELECT * FROM t1; c1 c2 1 1 2 2 ALTER TABLE t1 CHANGE c1 c1 SERIAL; SELECT * FROM t1; c1 c2 1 1 2 2 INSERT INTO t1 VALUES(NULL, 3); INSERT INTO t1 VALUES(NULL, 4); SELECT * FROM t1; c1 c2 1 1 2 2 3 3 4 4 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL, 1); INSERT INTO t1 VALUES(NULL, 2); SELECT * FROM t1; c1 c2 1 1 2 2 ALTER TABLE t1 CHANGE c1 c1 SERIAL; SELECT * FROM t1; c1 c2 1 1 2 2 INSERT INTO t1 VALUES(NULL, 3); INSERT INTO t1 VALUES(NULL, 4); SELECT * FROM t1; c1 c2 1 1 2 2 3 3 4 4 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t1 ( a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, b INT(10) UNSIGNED NOT NULL, c ENUM('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (a)) ENGINE = InnoDB; CREATE TABLE t2 ( m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, n INT(10) UNSIGNED NOT NULL, o enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (m)) ENGINE = InnoDB; INSERT INTO t2 (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `m` int(11) unsigned NOT NULL AUTO_INCREMENT, `n` int(10) unsigned NOT NULL, `o` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`m`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 16 1 TRUE 17 1 FALSE 18 2 TRUE 19 2 FALSE 20 3 TRUE 21 3 FALSE 22 4 TRUE 23 4 FALSE 24 5 TRUE 25 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 16 1 TRUE 17 1 FALSE 18 2 TRUE 19 2 FALSE 20 3 TRUE 21 3 FALSE 22 4 TRUE 23 4 FALSE 24 5 TRUE 25 5 FALSE 31 1 FALSE 32 2 FALSE 33 3 FALSE 34 4 FALSE 35 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 16 1 TRUE 17 1 FALSE 18 2 TRUE 19 2 FALSE 20 3 TRUE 21 3 FALSE 22 4 TRUE 23 4 FALSE 24 5 TRUE 25 5 FALSE 31 1 FALSE 32 2 FALSE 33 3 FALSE 34 4 FALSE 35 5 FALSE 38 1 FALSE 39 2 FALSE 40 3 FALSE 41 4 FALSE 42 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 16 1 TRUE 17 1 FALSE 18 2 TRUE 19 2 FALSE 20 3 TRUE 21 3 FALSE 22 4 TRUE 23 4 FALSE 24 5 TRUE 25 5 FALSE 31 1 FALSE 32 2 FALSE 33 3 FALSE 34 4 FALSE 35 5 FALSE 38 1 FALSE 39 2 FALSE 40 3 FALSE 41 4 FALSE 42 5 FALSE 45 1 FALSE 46 2 FALSE 47 3 FALSE 48 4 FALSE 49 5 FALSE 52 1 FALSE 53 2 FALSE 54 3 FALSE 55 4 FALSE 56 5 FALSE 59 1 FALSE 60 2 FALSE 61 3 FALSE 62 4 FALSE 63 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE t2; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t1( c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; 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); CREATE TABLE t2( c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t2 SELECT c1 FROM t1; Got one of the listed errors INSERT INTO t2 SELECT NULL FROM t1; Got one of the listed errors DROP TABLE t1; DROP TABLE t2; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-127, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` tinyint(4) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -127 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-127, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-32767, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` smallint(6) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -32767 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-32757, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-8388607, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` mediumint(9) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -8388607 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-8388607, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-2147483647, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -2147483647 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-2147483647, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -9223372036854775807 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; CREATE INDEX i1 on t1(c2); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `i1` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 INSERT INTO t1 (c2) values (0); SELECT * FROM t1; c1 c2 10 0 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); INSERT INTO t1(C2) VALUES ('innodb'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `C1` double NOT NULL AUTO_INCREMENT, `C2` char(10) DEFAULT NULL, PRIMARY KEY (`C1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); INSERT INTO t1(C2) VALUES ('innodb'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `C1` float NOT NULL AUTO_INCREMENT, `C2` char(10) DEFAULT NULL, PRIMARY KEY (`C1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 SET c1 = 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 INSERT INTO t1 SET c1 = 2; INSERT INTO t1 SET c1 = -1; SELECT * FROM t1; c1 -1 1 2 INSERT INTO t1 SET c1 = -1; Got one of the listed errors SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 REPLACE INTO t1 VALUES (-1); SELECT * FROM t1; c1 -1 1 2 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); SELECT * FROM t1; c1 -685113344 1 2 3 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); SELECT * FROM t1; c1 -685113344 2 3 4 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); INSERT INTO t1 VALUES (4), (5), (6), (NULL); SELECT * FROM t1; c1 -685113344 1 2 3 4 5 6 7 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); SELECT * FROM t1; c1 -685113344 1 2 5 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); SELECT * FROM t1; c1 -685113344 1 2 3 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (18446744073709551615); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=256; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 256 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` tinyint(4) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (2147483648, 'a'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2147483649 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 2147483648 a ALTER TABLE t1 CHANGE c1 c1 INT; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL DEFAULT '0', `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO t1(c2) VALUES('b'); SELECT * FROM t1; c1 c2 0 b 2147483647 a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL DEFAULT '0', `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE = MyISAM; INSERT INTO t1 (c1) VALUES (NULL), (-290783232), (NULL); Warnings: Warning 1264 Out of range value for column 'c1' at row 3 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=MyISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL -290783232 NULL 2147483647 NULL ALTER TABLE t1 ENGINE = InnoDB; SELECT * FROM t1; c1 c2 -290783232 NULL 1 NULL 2147483647 NULL SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 REPLACE INTO t1 (c2 ) VALUES (0); ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t1; c1 c2 -290783232 NULL 1 NULL 2147483647 NULL DROP TABLE t1; CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=10000000000000000000; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` double NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=10000000000000000000 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); ERROR HY000: Failed to read auto-increment value from storage engine DROP TABLE t1; # # Bug #14049391 INNODB MISCALCULATES AUTO-INCREMENT # AFTER CHANGING AUTO_INCREMENT_INCREMEMENT # CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=InnoDB; SET auto_increment_increment = 300; INSERT INTO t VALUES (NULL), (NULL); SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `i` int(11) NOT NULL AUTO_INCREMENT, KEY `i` (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=latin1 SET auto_increment_increment = 50; INSERT INTO t VALUES (NULL); SELECT * FROM t; i 1 301 351 SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `i` int(11) NOT NULL AUTO_INCREMENT, KEY `i` (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=401 DEFAULT CHARSET=latin1 DROP TABLE t;
Back to File Manager