~drizzle-trunk/drizzle/development

#

# Test of auto_increment with offset

#

#####################################

# By: JBM

# Date: 2006-02-10

# Change: NDB does not support auto inc

# in this usage. Currently there is no

# plan to implment. Skipping test when

# NDB is default engine.

#####################################

-- source include/not_ndb_default.inc

-- source include/master-slave.inc

eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2 auto_increment=3;

insert into t1 values (NULL,1),(NULL,2),(NULL,3);

select * from t1;

sync_slave_with_master;

select * from t1;

connection master;

drop table t1;

eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2;

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;

sync_slave_with_master;

select * from t1;

connection master;

drop table t1;

set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10;

show variables like "%auto_inc%";

eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2;

# Insert with 2 insert statements to get better testing of logging

insert into t1 values (NULL),(5),(NULL);

insert into t1 values (250),(NULL);

select * from t1;

insert into t1 values (1000);

set @@insert_id=400;

insert into t1 values(NULL),(NULL);

select * from t1;

sync_slave_with_master;

select * from t1;

connection master;

drop table t1;

#

# Same test with innodb (as the innodb code is a bit different)

#

eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type;

# Insert with 2 insert statements to get better testing of logging

insert into t1 values (NULL),(5),(NULL);

insert into t1 values (250),(NULL);

select * from t1;

insert into t1 values (1000);

set @@insert_id=400;

insert into t1 values(NULL),(NULL);

select * from t1;

sync_slave_with_master;

select * from t1;

connection master;

drop table t1;

set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;

eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2;

# Insert with 2 insert statements to get better testing of logging

insert into t1 values (NULL),(5),(NULL),(NULL);

insert into t1 values (500),(NULL),(502),(NULL),(NULL);

select * from t1;

set @@insert_id=600;

--error ER_DUP_ENTRY

insert into t1 values(600),(NULL),(NULL);

set @@insert_id=600;

insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);

select * from t1;

sync_slave_with_master;

select * from t1;

connection master;

drop table t1;

#

# Test that auto-increment works when slave has rows in the table

#

set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1;

eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2;

sync_slave_with_master;

insert into t1 values(2),(12),(22),(32),(42);

connection master;

insert into t1 values (NULL),(NULL);

insert into t1 values (3),(NULL),(NULL);

select * from t1;

sync_slave_with_master;

select * from t1;

# Test for BUG#20524 "auto_increment_* not observed when inserting

# a too large value". When an autogenerated value was bigger than the

# maximum possible value of the field, it was truncated to that max

# possible value, without being "rounded down" to still honour

# auto_increment_* variables.

connection master;

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);

--error ER_DUP_ENTRY

insert into t1 values(null);

select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;

# same but with a larger value

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);

select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a;

# An offset so big that even first value does not fit

create table t3 like t1;

set auto_increment_increment=1000;

set auto_increment_offset=700;

insert into t3 values(null);

select * from t3 order by a;

sync_slave_with_master;

select * from t1 order by a;

select * from t2 order by a;

select * from t3 order by a;

connection master;

drop table t1,t2,t3;

# End cleanup

sync_slave_with_master;