stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3; insert into t1 values (NULL,1),(NULL,2),(NULL,3); select * from t1; a b 12 1 22 2 32 3 select * from t1; a b 12 1 22 2 32 3 drop table t1; create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam; insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4); delete from t1 where b=4; insert into t1 values (NULL,5),(NULL,6); select * from t1; a b 1 1 2 2 3 3 22 5 32 6 select * from t1; a b 1 1 2 2 3 3 22 5 32 6 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 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; insert into t1 values (NULL),(5),(NULL); insert into t1 values (250),(NULL); select * from t1; a 5 10 110 250 310 insert into t1 values (1000); set @@insert_id=400; insert into t1 values(NULL),(NULL); select * from t1; a 5 10 110 250 310 400 410 1000 select * from t1; a 5 10 110 250 310 400 410 1000 drop table t1; create table t1 (a int not null auto_increment, primary key (a)) engine=innodb; insert into t1 values (NULL),(5),(NULL); insert into t1 values (250),(NULL); select * from t1; a 5 10 110 250 310 insert into t1 values (1000); set @@insert_id=400; insert into t1 values(NULL),(NULL); select * from t1; a 5 10 110 250 310 400 410 1000 select * from t1; a 5 10 110 250 310 400 410 1000 drop table t1; set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; insert into t1 values (NULL),(5),(NULL),(NULL); insert into t1 values (500),(NULL),(502),(NULL),(NULL); select * from t1; a 1 5 6 7 500 501 502 503 504 set @@insert_id=600; insert into t1 values(600),(NULL),(NULL); ERROR 23000: Duplicate entry '600' for key 'PRIMARY' set @@insert_id=600; insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); select * from t1; a 1 5 6 7 500 501 502 503 504 600 610 611 select * from t1; a 1 5 6 7 500 501 502 503 504 600 610 611 drop table t1; set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; insert into t1 values(2),(12),(22),(32),(42); insert into t1 values (NULL),(NULL); insert into t1 values (3),(NULL),(NULL); select * from t1; a 1 3 11 21 31 select * from t1; a 1 2 3 11 12 21 22 31 32 42 drop table t1; create table t1 (a tinyint not null auto_increment primary key) engine=myisam; insert into t1 values(103); set auto_increment_increment=11; set auto_increment_offset=4; insert into t1 values(null); insert into t1 values(null); insert into t1 values(null); ERROR 23000: Duplicate entry '125' for key 'PRIMARY' select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; a mod(a-@@auto_increment_offset,@@auto_increment_increment) 103 0 114 0 125 0 create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam; set auto_increment_increment=10; set auto_increment_offset=1; set insert_id=1000; insert into t2 values(null); Warnings: Warning 1264 Out of range value for column 'a' at row 1 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; a mod(a-@@auto_increment_offset,@@auto_increment_increment) 251 0 create table t3 like t1; set auto_increment_increment=1000; set auto_increment_offset=700; insert into t3 values(null); Warnings: Warning 1264 Out of range value for column 'a' at row 1 select * from t3 order by a; a 127 select * from t1 order by a; a 103 114 125 select * from t2 order by a; a 251 select * from t3 order by a; a 127 drop table t1,t2,t3;