path: root/mysql-test/t/rpl_insert_id.test

--echo #
--echo # Setup
--echo #

source include/master-slave.inc;
source include/have_innodb.inc;
use test;
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings

--echo #
--echo # See if queries that use both auto_increment and LAST_INSERT_ID()
--echo # are replicated well
--echo #
--echo # We also check how the foreign_key_check variable is replicated
--echo #

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;

--echo #
--echo # check if INSERT SELECT in auto_increment is well replicated (bug #490)
--echo #

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;

--echo #
--echo # Bug#8412: Error codes reported in binary log for CHARACTER SET,
--echo #           FOREIGN_KEY_CHECKS
--echo #

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;
 
--echo #
--echo # Bug#14553: NULL in WHERE resets LAST_INSERT_ID
--echo #

connection master;
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;

--echo #
--echo # End of 4.1 tests
--echo #

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

# test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in
# auto_increment breaks binlog

create table t1 (n int primary key auto_increment not null,
b int, unique(b));

# First, test that we do not call restore_auto_increment() too early
# in write_record():
set sql_log_bin=0;
insert into t1 values(null,100);
replace into t1 values(null,50),(null,100),(null,150);
select * from t1 order by n;
truncate table t1;
set sql_log_bin=1;

insert into t1 values(null,100);
select * from t1 order by n;
sync_slave_with_master;
# make slave's table autoinc counter bigger
insert into t1 values(null,200),(null,300);
delete from t1 where b <> 100;
# check that slave's table content is identical to master
select * from t1 order by n;
# only the auto_inc counter differs.

connection master;
replace into t1 values(null,100),(null,350);
select * from t1 order by n;
sync_slave_with_master;
select * from t1 order by n;

# Same test as for REPLACE, but for ON DUPLICATE KEY UPDATE

# We first check that if we update a row using a value larger than the
# table's counter, the counter for next row is bigger than the
# after-value of the updated row.
connection master;
insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
select * from t1 order by n;
sync_slave_with_master;
select * from t1 order by n;

# and now test for the bug:
connection master;
drop table t1;
create table t1 (n int primary key auto_increment not null,
b int, unique(b));
insert into t1 values(null,100);
select * from t1 order by n;
sync_slave_with_master;
insert into t1 values(null,200),(null,300);
delete from t1 where b <> 100;
select * from t1 order by n;

connection master;
insert into t1 values(null,100),(null,350) on duplicate key update n=2;
select * from t1 order by n;
sync_slave_with_master;
select * from t1 order by n;

connection master;
drop table t1;
sync_slave_with_master;

--echo 
--echo # End of 5.0 tests
--echo