Edit File: auto_increment.result
drop table if exists t1; drop table if exists t2; SET SQL_WARNINGS=1; create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3; insert into t1 values (1,1),(NULL,3),(NULL,4); delete from t1 where a=4; insert into t1 values (NULL,5),(NULL,6); select * from t1; a b 1 1 3 3 5 5 6 6 delete from t1 where a=6; replace t1 values (3,1); ALTER TABLE t1 add c int; replace t1 values (3,3,3); insert into t1 values (NULL,7,7); update t1 set a=8,b=b+1,c=c+1 where a=7; insert into t1 values (NULL,9,9); select * from t1; a b c 1 1 NULL 3 3 3 5 5 NULL 8 8 8 9 9 9 drop table t1; create table t1 ( skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY, sval char(20) ); insert into t1 values (NULL, "hello"); insert into t1 values (NULL, "hey"); select * from t1; skey sval 1 hello 2 hey select _rowid,t1._rowid,skey,sval from t1; _rowid _rowid skey sval 1 1 1 hello 2 2 2 hey drop table t1; create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)); insert into t1 values ("a",1),("b",2),("a",2),("c",1); insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); insert into t1 (a) values ("a"),("b"),("c"),("d"); insert into t1 (a) values ('k'),('d'); insert into t1 (a) values ("a"); insert into t1 values ("d",last_insert_id()); select * from t1; a b a 1 a 2 a 3 a 4 a 5 b 2 b 3 b 4 c 1 c 2 c 3 d 1 d 2 d 5 e 1 k 1 drop table t1; create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid)); insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj'); select * from t1; ordid ord 1 sdj 2 sdj drop table t1; create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)); insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc'); select * from t1; ordid ord 1 abc 2 abc 3 abc 1 sdj 2 sdj 3 sdj 1 zzz drop table t1; create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id)); create table t2 (sid char(20), id int(2)); insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL); insert into t1 select * from t2; select * from t1; sid id skr 1 skr 2 test 1 drop table t1,t2; create table t1 (a int not null primary key auto_increment); insert into t1 values (0); update t1 set a=0; select * from t1; a 0 check table t1; Table Op Msg_type Msg_text test.t1 check warning Found row where the auto_increment column has the value 0 test.t1 check status OK drop table t1; create table t1 (a int not null auto_increment primary key); insert into t1 values (NULL); insert into t1 values (-1); select last_insert_id(); last_insert_id() 1 insert into t1 values (NULL); select * from t1; a -1 1 2 drop table t1; create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */; insert into t1 values (NULL); insert into t1 values (-1); select last_insert_id(); last_insert_id() 1 insert into t1 values (NULL); select * from t1; a 1 -1 2 drop table t1; create table t1 (i tinyint unsigned not null auto_increment primary key); insert into t1 set i = 254; insert into t1 set i = null; select last_insert_id(); last_insert_id() 255 explain extended select last_insert_id(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select last_insert_id() AS `last_insert_id()` insert into t1 set i = 254; ERROR 23000: Duplicate entry '254' for key 'PRIMARY' select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; ERROR 23000: Duplicate entry '255' for key 'PRIMARY' select last_insert_id(); last_insert_id() 255 drop table t1; create table t1 (i tinyint unsigned not null auto_increment, key (i)); insert into t1 set i = 254; insert into t1 set i = null; select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; Warnings: Warning 1264 Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 drop table t1; create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b)); insert into t1 values (NULL, 10); select last_insert_id(); last_insert_id() 1 insert into t1 values (NULL, 15); select last_insert_id(); last_insert_id() 2 insert into t1 values (NULL, 10); ERROR 23000: Duplicate entry '10' for key 'b' select last_insert_id(); last_insert_id() 2 drop table t1; create table t1(a int auto_increment,b int null,primary key(a)); SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; insert into t1(a,b)values(NULL,1); insert into t1(a,b)values(200,2); insert into t1(a,b)values(0,3); insert into t1(b)values(4); insert into t1(b)values(5); insert into t1(b)values(6); insert into t1(b)values(7); select * from t1 order by b; a b 1 1 200 2 0 3 201 4 202 5 203 6 204 7 alter table t1 modify b mediumint; select * from t1 order by b; a b 1 1 200 2 0 3 201 4 202 5 203 6 204 7 create table t2 (a int); insert t2 values (1),(2); alter table t2 add b int auto_increment primary key; select * from t2; a b 1 1 2 2 drop table t2; delete from t1 where a=0; update t1 set a=0 where b=5; select * from t1 order by b; a b 1 1 200 2 201 4 0 5 203 6 204 7 delete from t1 where a=0; update t1 set a=NULL where b=6; Warnings: Warning 1048 Column 'a' cannot be null update t1 set a=300 where b=7; SET SQL_MODE=''; insert into t1(a,b)values(NULL,8); insert into t1(a,b)values(400,9); insert into t1(a,b)values(0,10); insert into t1(b)values(11); insert into t1(b)values(12); insert into t1(b)values(13); insert into t1(b)values(14); select * from t1 order by b; a b 1 1 200 2 201 4 0 6 300 7 301 8 400 9 401 10 402 11 403 12 404 13 405 14 delete from t1 where a=0; update t1 set a=0 where b=12; select * from t1 order by b; a b 1 1 200 2 201 4 300 7 301 8 400 9 401 10 402 11 0 12 404 13 405 14 delete from t1 where a=0; update t1 set a=NULL where b=13; Warnings: Warning 1048 Column 'a' cannot be null update t1 set a=500 where b=14; select * from t1 order by b; a b 1 1 200 2 201 4 300 7 301 8 400 9 401 10 402 11 0 13 500 14 drop table t1; create table t1 (a bigint); insert into t1 values (1), (2), (3), (NULL), (NULL); alter table t1 modify a bigint not null auto_increment primary key; select * from t1; a 1 2 3 4 5 drop table t1; create table t1 (a bigint); insert into t1 values (1), (2), (3), (0), (0); alter table t1 modify a bigint not null auto_increment primary key; select * from t1; a 1 2 3 4 5 drop table t1; create table t1 (a bigint); insert into t1 values (0), (1), (2), (3); set sql_mode=NO_AUTO_VALUE_ON_ZERO; alter table t1 modify a bigint not null auto_increment primary key; set sql_mode= ''; select * from t1; a 0 1 2 3 drop table t1; create table t1 (a int auto_increment primary key , b int null); set sql_mode=NO_AUTO_VALUE_ON_ZERO; insert into t1 values (0,1),(1,2),(2,3); select * from t1; a b 0 1 1 2 2 3 set sql_mode= ''; alter table t1 modify b varchar(255); insert into t1 values (0,4); select * from t1; a b 0 1 1 2 2 3 3 4 drop table t1; CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10))); INSERT INTO t1 (b) VALUES ('aaaa'); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK INSERT INTO t1 (b) VALUES (''); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK INSERT INTO t1 (b) VALUES ('bbbb'); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE IF EXISTS t1; CREATE TABLE `t1` ( t1_name VARCHAR(255) DEFAULT NULL, t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, KEY (t1_name), PRIMARY KEY (t1_id) ) AUTO_INCREMENT = 1000; INSERT INTO t1 (t1_name) VALUES('MySQL'); INSERT INTO t1 (t1_name) VALUES('MySQL'); INSERT INTO t1 (t1_name) VALUES('MySQL'); SELECT * from t1; t1_name t1_id MySQL 1000 MySQL 1001 MySQL 1002 SHOW CREATE TABLE `t1`; Table Create Table t1 CREATE TABLE `t1` ( `t1_name` varchar(255) DEFAULT NULL, `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`t1_id`), KEY `t1_name` (`t1_name`) ) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 DROP TABLE `t1`; create table t1(a int not null auto_increment primary key); create table t2(a int not null auto_increment primary key, t1a int); insert into t1 values(NULL); insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); insert into t1 values (NULL); insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); insert into t1 values (NULL); insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); select * from t2; a t1a 1 1 2 1 3 2 4 2 5 2 6 3 7 3 8 3 9 3 drop table t1, t2; End of 4.1 tests CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)); insert into t1 (b) values (1); replace into t1 (b) values (2), (1), (3); select * from t1; a b 3 1 2 2 4 3 truncate table t1; insert into t1 (b) values (1); replace into t1 (b) values (2); replace into t1 (b) values (1); replace into t1 (b) values (3); select * from t1; a b 3 1 2 2 4 3 drop table t1; create table t1 (rowid int not null auto_increment, val int not null,primary key (rowid), unique(val)); replace into t1 (val) values ('1'),('2'); replace into t1 (val) values ('1'),('2'); insert into t1 (val) values ('1'),('2'); ERROR 23000: Duplicate entry '1' for key 'val' select * from t1; rowid val 3 1 4 2 drop table t1; create table t1 (a int not null auto_increment primary key, val int); insert into t1 (val) values (1); update t1 set a=2 where a=1; insert into t1 (val) values (1); select * from t1; a val 2 1 3 1 drop table t1; CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10)); INSERT INTO t1 VALUES(0, 0); INSERT INTO t1 VALUES(1, 1); ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment; ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' DROP TABLE t1; create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c)); insert into t1 values(null,1,1,now()); insert into t1 values(null,0,0,null); replace into t1 values(null,1,0,null); select last_insert_id(); last_insert_id() 3 drop table t1; create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e)); insert into t1 values(null,1,1,1,now()); insert into t1 values(null,0,0,0,null); replace into t1 values(null,1,0,2,null); select last_insert_id(); last_insert_id() 3 drop table t1; # # Bug#46616: Assertion `!table->auto_increment_field_not_null' on view # manipulations # CREATE TABLE t1 ( a INT ); INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY ); INSERT INTO t2 SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' UPDATE t2 SET a = 2; SELECT a FROM t2; a 2 DROP TABLE t1, t2; # # Bug#39828 autoinc wraps around when offset and increment > 1 # CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engine=MyISAM; INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES (18446744073709551601); SET @@SESSION.AUTO_INCREMENT_INCREMENT=10; SELECT @@SESSION.AUTO_INCREMENT_OFFSET; @@SESSION.AUTO_INCREMENT_OFFSET 1 INSERT INTO t1 VALUES (NULL), (NULL), (NULL); ERROR 22003: Out of range value for column 't1' at row 167 SELECT * FROM t1; c1 1 18446744073709551601 18446744073709551611 SET @@SESSION.AUTO_INCREMENT_INCREMENT=default; SET @@SESSION.AUTO_INCREMENT_OFFSET=default; DROP TABLE t1; End of 5.1 tests # # Bug#50619 assert in handler::update_auto_increment # CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk)); INSERT INTO t1 VALUES (NULL), (-1), (NULL); SELECT * FROM t1; pk -1 1 2 DROP TABLE t1; CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk)); INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL); ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t1; pk 1 18446744073709551614 DROP TABLE t1;