# # Setup # use test; drop table if exists t1, t2, t3; # # 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 # 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 auto_increment, key(a)) engine=innodb; create table t2(b int auto_increment, c int, key(b)) engine=innodb; insert into t1 values (1),(2),(3); insert into t1 values (null); insert into t2 values (null,last_insert_id()); select * from t1 ORDER BY a; a 1 2 3 4 select * from t2 ORDER BY b; b c 1 4 drop table t1; drop table t2; 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; 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; select * from t1; a 10 11 12 13 select * from t2; b c 5 0 6 11 # # 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)) engine=innodb; create table t2(b int auto_increment, c int, key(b)) engine=innodb; 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 ORDER BY a; select * from t2 ORDER BY b; b c 5 0 6 10 7 11 8 12 9 13 select * from t1 ORDER BY a; a 10 11 12 13 select * from t2 ORDER BY b; b c 5 0 6 10 7 11 8 12 9 13 drop table t1; drop table t2; # # Bug#8412: Error codes reported in binary log for CHARACTER SET, # FOREIGN_KEY_CHECKS # SET TIMESTAMP=1000000000; CREATE TABLE t1 ( a INT UNIQUE ) engine=innodb; SET FOREIGN_KEY_CHECKS=0; INSERT INTO t1 VALUES (1),(1); Got one of the listed errors drop table t1; # # Bug#14553: NULL in WHERE resets LAST_INSERT_ID # create table t1(a int auto_increment, key(a)) engine=innodb; create table t2(a int) engine=innodb; 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; a 1 select * from t2; a 1 drop table t1; drop table t2; # # 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. # drop function if exists bug15728; drop function if exists bug15728_insert; drop table if exists t1, t2; create table t1 ( id int not null auto_increment, last_id int, primary key (id) ) engine=innodb; 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()); create table t2 ( id int not null auto_increment, last_id int, primary key (id) ) engine=innodb; 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| insert into t1 (last_id) values (0); drop trigger t1_bi; select last_insert_id(); last_insert_id() 4 select bug15728_insert(); bug15728_insert() 2 select last_insert_id(); last_insert_id() 4 insert into t1 (last_id) values (bug15728()); select last_insert_id(); last_insert_id() 5 drop procedure if exists foo; create procedure foo() begin declare res int; insert into t2 (last_id) values (bug15728()); insert into t1 (last_id) values (bug15728()); end| call foo(); select * from t1; id last_id 1 0 2 1 3 2 4 1 5 4 6 3 select * from t2; id last_id 1 3 2 4 3 5 select * from t1; id last_id 1 0 2 1 3 2 4 1 5 4 6 3 select * from t2; id last_id 1 3 2 4 3 5 drop function bug15728; drop function bug15728_insert; drop table t1,t2; drop procedure foo; create table t1 (n int primary key auto_increment not null, b int, unique(b)) engine=innodb; 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; n b 2 50 3 100 4 150 truncate table t1; set sql_log_bin=1; insert into t1 values(null,100); select * from t1 order by n; n b 1 100 insert into t1 values(null,200),(null,300); delete from t1 where b <> 100; select * from t1 order by n; n b 1 100 replace into t1 values(null,100),(null,350); select * from t1 order by n; n b 2 100 3 350 select * from t1 order by n; n b 2 100 3 350 insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000; select * from t1 order by n; n b 2 100 4 400 1000 350 1001 600 select * from t1 order by n; n b 2 100 4 400 1000 350 1001 600 drop table t1; create table t1 (n int primary key auto_increment not null, b int, unique(b)) engine=innodb; insert into t1 values(null,100); select * from t1 order by n; n b 1 100 insert into t1 values(null,200),(null,300); delete from t1 where b <> 100; select * from t1 order by n; n b 1 100 insert into t1 values(null,100),(null,350) on duplicate key update n=2; select * from t1 order by n; n b 2 100 3 350 select * from t1 order by n; n b 2 100 3 350 drop table t1; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, UNIQUE(b)) ENGINE=innodb; INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; SELECT * FROM t1 ORDER BY a; a b 1 10 2 2 SELECT * FROM t1 ORDER BY a; a b 1 10 2 2 drop table t1; CREATE TABLE t1 ( id bigint(20) unsigned NOT NULL auto_increment, field_1 int(10) unsigned NOT NULL, field_2 varchar(255) NOT NULL, field_3 varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY field_1 (field_1, field_2) ) ENGINE=innodb; CREATE TABLE t2 ( field_a int(10) unsigned NOT NULL, field_b varchar(255) NOT NULL, field_c varchar(255) NOT NULL ) ENGINE=innodb; INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); INSERT INTO t1 (field_1, field_2, field_3) SELECT t2.field_a, t2.field_b, t2.field_c FROM t2 ON DUPLICATE KEY UPDATE t1.field_3 = t2.field_c; INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); INSERT INTO t1 (field_1, field_2, field_3) SELECT t2.field_a, t2.field_b, t2.field_c FROM t2 ON DUPLICATE KEY UPDATE t1.field_3 = t2.field_c; SELECT * FROM t1 ORDER BY id; id field_1 field_2 field_3 1 1 a 1a 2 2 b 2b 3 3 c 3c 4 4 d 4d 5 5 e 5e 6 6 f 6f SELECT * FROM t1 ORDER BY id; id field_1 field_2 field_3 1 1 a 1a 2 2 b 2b 3 3 c 3c 4 4 d 4d 5 5 e 5e 6 6 f 6f drop table t1, t2; DROP PROCEDURE IF EXISTS p1; DROP TABLE IF EXISTS t1, t2; SELECT LAST_INSERT_ID(0); LAST_INSERT_ID(0) 0 CREATE TABLE t1 ( id INT NOT NULL DEFAULT 0, last_id INT, PRIMARY KEY (id) ) ENGINE=innodb; CREATE TABLE t2 ( id INT NOT NULL AUTO_INCREMENT, last_id INT, PRIMARY KEY (id) ) ENGINE=innodb; CREATE PROCEDURE p1() BEGIN INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID()); INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID()); END| CALL p1(); SELECT * FROM t1 ORDER BY id; id last_id 0 1 SELECT * FROM t2 ORDER BY id; id last_id 1 0 SELECT * FROM t1 ORDER BY id; id last_id 0 1 SELECT * FROM t2 ORDER BY id; id last_id 1 0 DROP PROCEDURE p1; DROP TABLE t1, t2; DROP PROCEDURE IF EXISTS p1; DROP FUNCTION IF EXISTS f1; DROP FUNCTION IF EXISTS f2; DROP FUNCTION IF EXISTS f3; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, j INT DEFAULT 0 ) ENGINE=innodb; CREATE TABLE t2 (i INT) ENGINE=innodb; CREATE PROCEDURE p1() BEGIN INSERT INTO t1 (i) VALUES (NULL); INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); INSERT INTO t1 (i) VALUES (NULL), (NULL); INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); END | CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA BEGIN INSERT INTO t1 (i) VALUES (NULL); INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); INSERT INTO t1 (i) VALUES (NULL), (NULL); INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); RETURN 0; END | CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC RETURN LAST_INSERT_ID() | CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA BEGIN INSERT INTO t2 (i) VALUES (LAST_INSERT_ID()); RETURN 0; END | INSERT INTO t1 VALUES (NULL, -1); CALL p1(); SELECT f1(); f1() 0 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2()); INSERT INTO t1 VALUES (NULL, f2()); INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)), (NULL, @@LAST_INSERT_ID); INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID()); UPDATE t1 SET j= -1 WHERE i IS NULL; INSERT INTO t1 (i) VALUES (NULL); SET @old_concurrent_insert= @@global.concurrent_insert; SET @@global.concurrent_insert= 0; INSERT INTO t1 (i) VALUES (NULL); SELECT f3(); f3() 0 SET @@global.concurrent_insert= @old_concurrent_insert; SELECT * FROM t1 ORDER BY i; i j 1 -1 2 0 3 0 4 0 5 0 6 0 7 0 8 3 9 3 10 3 11 3 12 3 13 8 14 13 15 5 16 13 17 -1 18 14 19 0 20 0 SELECT * FROM t2 ORDER BY i; i 2 3 5 6 19 SELECT * FROM t1; i j 1 -1 2 0 3 0 4 0 5 0 6 0 7 0 8 3 9 3 10 3 11 3 12 3 13 8 14 13 15 5 16 13 17 -1 18 14 19 0 20 0 SELECT * FROM t2; i 2 3 5 6 19 DROP PROCEDURE p1; DROP FUNCTION f1; DROP FUNCTION f2; DROP FUNCTION f3; DROP TABLE t1, t2; # # End of 5.0 tests # create table t2 ( id int not null auto_increment, last_id int, primary key (id) ) engine=innodb; truncate table t2; create table t1 (id tinyint primary key) engine=innodb; create function insid() returns int begin insert into t2 (last_id) values (0); return 0; end| set sql_log_bin=0; insert into t2 (id) values(1),(2),(3); delete from t2; set sql_log_bin=1; select insid(); insid() 0 set sql_log_bin=0; insert into t2 (id) values(5),(6),(7); delete from t2 where id>=5; set sql_log_bin=1; insert into t1 select insid(); select * from t1 order by id; id 0 select * from t2 order by id; id last_id 4 0 8 0 select * from t1 order by id; id 0 select * from t2 order by id; id last_id 4 0 8 0 drop table t1; drop function insid; truncate table t2; create table t1 (n int primary key auto_increment not null, b int, unique(b)) engine=innodb; create procedure foo() begin insert into t1 values(null,10); insert ignore into t1 values(null,10); insert ignore into t1 values(null,10); insert into t2 values(null,3); end| call foo(); select * from t1 order by n; n b 1 10 select * from t2 order by id; id last_id 1 3 select * from t1 order by n; n b 1 10 select * from t2 order by id; id last_id 1 3 drop table t1, t2; drop procedure foo;