Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 580 insertions, 0 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_insert_id.result b/mysql-test/suite/rpl/r/rpl_insert_id.result
new file mode 100644
index 00000000..00b41109
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_insert_id.result
@@ -0,0 +1,580 @@
+#
+# 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
+[connection master]
+connection master;
+SET @old_concurrent_insert= @@global.concurrent_insert;
+SET @@global.concurrent_insert= 0;
+connection master;
+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());
+connection slave;
+select * from t1 ORDER BY a;
+a
+1
+2
+3
+4
+select * from t2 ORDER BY b;
+b	c
+1	4
+connection master;
+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;
+connection slave;
+select * from t1;
+a
+10
+11
+12
+13
+select * from t2;
+b	c
+5	0
+6	11
+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)) 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
+connection slave;
+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
+connection master;
+drop table t1;
+drop table t2;
+connection slave;
+#
+# 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 ) engine=myisam;
+SET FOREIGN_KEY_CHECKS=0;
+INSERT INTO t1 VALUES (1),(1);
+Got one of the listed errors
+connection slave;
+connection master;
+drop table t1;
+connection slave;
+#
+# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
+#
+connection master;
+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
+connection slave;
+connection slave;
+select * from t2;
+a
+1
+connection master;
+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.
+#
+connection master;
+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
+connection slave;
+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
+connection master;
+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
+connection slave;
+insert into t1 values(null,200),(null,300);
+delete from t1 where b <> 100;
+select * from t1 order by n;
+n	b
+1	100
+connection master;
+replace into t1 values(null,100),(null,350);
+select * from t1 order by n;
+n	b
+2	100
+3	350
+connection slave;
+select * from t1 order by n;
+n	b
+2	100
+3	350
+connection master;
+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
+connection slave;
+select * from t1 order by n;
+n	b
+2	100
+4	400
+1000	350
+1001	600
+connection master;
+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
+connection slave;
+insert into t1 values(null,200),(null,300);
+delete from t1 where b <> 100;
+select * from t1 order by n;
+n	b
+1	100
+connection master;
+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
+connection slave;
+select * from t1 order by n;
+n	b
+2	100
+3	350
+connection master;
+drop table t1;
+connection slave;
+connection master;
+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
+connection slave;
+SELECT * FROM t1 ORDER BY a;
+a	b
+1	10
+2	2
+connection master;
+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
+connection slave;
+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
+connection master;
+drop table t1, t2;
+connection master;
+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
+connection slave;
+SELECT * FROM t1 ORDER BY id;
+id	last_id
+0	1
+SELECT * FROM t2 ORDER BY id;
+id	last_id
+1	0
+connection master;
+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);
+connection master1;
+INSERT INTO t1 (i) VALUES (NULL);
+connection master;
+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
+connection slave;
+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
+connection master;
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP TABLE t1, t2;
+connection slave;
+#
+# End of 5.0 tests
+#
+connection master;
+create table t2 (
+id int not null auto_increment,
+last_id int,
+primary key (id)
+) engine=myisam;
+connection master;
+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
+connection slave;
+select * from t1 order by id;
+id
+0
+select * from t2 order by id;
+id	last_id
+4	0
+8	0
+connection master;
+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
+connection slave;
+select * from t1 order by n;
+n	b
+1	10
+select * from t2 order by id;
+id	last_id
+1	3
+connection master;
+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