# See if queries that use both auto_increment and LAST_INSERT_ID() # are replicated well # We also check how the foreign_key_check variable is replicated source include/master-slave.inc; source include/have_innodb.inc; connection master; create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); insert into t1 values (1),(2),(3); insert into t1 values (null); insert into t2 values (null,last_insert_id()); save_master_pos; connection slave; sync_with_master; select * from t1; select * from t2; connection master; #check if multi-line inserts, #which set last_insert_id to the first id inserted, #are replicated the same way drop table t1; drop table t2; --disable_warnings create table t1(a int auto_increment, key(a)) engine=innodb; create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=innodb; --enable_warnings SET FOREIGN_KEY_CHECKS=0; insert into t1 values (10); insert into t1 values (null),(null),(null); insert into t2 values (5,0); insert into t2 values (null,last_insert_id()); SET FOREIGN_KEY_CHECKS=1; save_master_pos; connection slave; sync_with_master; select * from t1; select * from t2; connection master; # check if INSERT SELECT in auto_increment is well replicated (bug #490) drop table t2; drop table t1; create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); insert into t1 values (10); insert into t1 values (null),(null),(null); insert into t2 values (5,0); insert into t2 (c) select * from t1; select * from t2; save_master_pos; connection slave; sync_with_master; select * from t1; select * from t2; connection master; drop table t1; drop table t2; save_master_pos; connection slave; sync_with_master; # # Bug#8412: Error codes reported in binary log for CHARACTER SET, # FOREIGN_KEY_CHECKS # connection master; SET TIMESTAMP=1000000000; CREATE TABLE t1 ( a INT UNIQUE ); SET FOREIGN_KEY_CHECKS=0; --error 1062 INSERT INTO t1 VALUES (1),(1); sync_slave_with_master; connection master; drop table t1; sync_slave_with_master; # # Bug#14553: NULL in WHERE resets LAST_INSERT_ID # connection master; drop table if exists t1,t2; create table t1(a int auto_increment, key(a)); create table t2(a int); insert into t1 (a) values (null); insert into t2 (a) select a from t1 where a is null; insert into t2 (a) select a from t1 where a is null; select * from t2; sync_slave_with_master; connection slave; select * from t2; connection master; drop table t1; drop table t2; sync_slave_with_master; # End of 4.1 tests # # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0 # # The solution is not to reset last_insert_id on enter to sub-statement. # connection master; --disable_warnings drop function if exists bug15728; drop function if exists bug15728_insert; drop table if exists t1, t2; --enable_warnings create table t1 ( id int not null auto_increment, last_id int, primary key (id) ); create function bug15728() returns int(11) return last_insert_id(); insert into t1 (last_id) values (0); insert into t1 (last_id) values (last_insert_id()); insert into t1 (last_id) values (bug15728()); # Check that nested call replicates too. create table t2 ( id int not null auto_increment, last_id int, primary key (id) ); delimiter |; create function bug15728_insert() returns int(11) modifies sql data begin insert into t2 (last_id) values (bug15728()); return bug15728(); end| create trigger t1_bi before insert on t1 for each row begin declare res int; select bug15728_insert() into res; set NEW.last_id = res; end| delimiter ;| insert into t1 (last_id) values (0); drop trigger t1_bi; # Check that nested call doesn't affect outer context. select last_insert_id(); select bug15728_insert(); select last_insert_id(); insert into t1 (last_id) values (bug15728()); # This should be exactly one greater than in the previous call. select last_insert_id(); save_master_pos; connection slave; sync_with_master; select * from t1; select * from t2; connection master; drop function bug15728; drop function bug15728_insert; drop table t1, t2; # End of 5.0 tests sync_slave_with_master;