^^&&^^

Edit File: rpl_insert_id.result

#
# Setup
#
#
# 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
#
include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
SET @old_concurrent_insert= @@global.concurrent_insert;
SET @@global.concurrent_insert= 0;
create table t1(a int auto_increment, key(a)) engine=myisam;
create table t2(b int auto_increment, c int, key(b)) engine=myisam;
insert into t1 values (1),(2),(3);
insert into t1 values (null);
insert into t2 values (null,last_insert_id());
include/sync_slave_sql_with_master.inc
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=myisam;
create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=myisam;
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;
include/sync_slave_sql_with_master.inc
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=myisam;
create table t2(b int auto_increment, c int, key(b)) engine=myisam;
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
include/sync_slave_sql_with_master.inc
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;
include/sync_slave_sql_with_master.inc
#
# Bug#8412: Error codes reported in binary log for CHARACTER SET,
#           FOREIGN_KEY_CHECKS
#
SET TIMESTAMP=1000000000;
CREATE TABLE t1 ( a INT UNIQUE ) engine=myisam;
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (1),(1);
Got one of the listed errors
include/sync_slave_sql_with_master.inc
drop table t1;
include/sync_slave_sql_with_master.inc
#
# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
#
set @@session.sql_auto_is_null=1;
create table t1(a int auto_increment, key(a)) engine=myisam;
create table t2(a int) engine=myisam;
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
include/sync_slave_sql_with_master.inc
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=myisam;
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=myisam;
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
include/sync_slave_sql_with_master.inc
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=myisam;
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
include/sync_slave_sql_with_master.inc
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
include/sync_slave_sql_with_master.inc
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
5	600
1000	350
include/sync_slave_sql_with_master.inc
select * from t1 order by n;
n	b
2	100
4	400
5	600
1000	350
drop table t1;
create table t1 (n int primary key auto_increment not null,
b int, unique(b)) engine=myisam;
insert into t1 values(null,100);
select * from t1 order by n;
n	b
1	100
include/sync_slave_sql_with_master.inc
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
include/sync_slave_sql_with_master.inc
select * from t1 order by n;
n	b
2	100
3	350
drop table t1;
include/sync_slave_sql_with_master.inc
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
UNIQUE(b)) ENGINE=myisam;
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
include/sync_slave_sql_with_master.inc
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=myisam;
CREATE TABLE t2 (
field_a int(10) unsigned NOT NULL,
field_b varchar(255) NOT NULL,
field_c varchar(255) NOT NULL
) ENGINE=myisam;
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
include/sync_slave_sql_with_master.inc
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=myisam;
CREATE TABLE t2 (
id INT NOT NULL AUTO_INCREMENT,
last_id INT,
PRIMARY KEY (id)
) ENGINE=myisam;
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
include/sync_slave_sql_with_master.inc
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=myisam;
CREATE TABLE t2 (i INT) ENGINE=myisam;
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, 0), (NULL, LAST_INSERT_ID());
UPDATE t1 SET j= -1 WHERE i IS NULL;
INSERT INTO t1 (i) VALUES (NULL);
INSERT INTO t1 (i) VALUES (NULL);
SELECT f3();
f3()
0
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	-1
15	13
16	0
17	0
SELECT * FROM t2 ORDER BY i;
i
2
3
5
6
16
include/sync_slave_sql_with_master.inc
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	-1
15	13
16	0
17	0
SELECT * FROM t2;
i
2
3
5
6
16
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION f3;
DROP TABLE t1, t2;
include/sync_slave_sql_with_master.inc
#
# End of 5.0 tests
#
create table t2 (
id int not null auto_increment,
last_id int,
primary key (id)
) engine=myisam;
truncate table t2;
create table t1 (id tinyint primary key) engine=myisam;
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
include/sync_slave_sql_with_master.inc
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=myisam;
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
include/sync_slave_sql_with_master.inc
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;
SET @@global.concurrent_insert= @old_concurrent_insert;
set @@session.sql_auto_is_null=default;
include/rpl_end.inc