mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
580 lines
11 KiB
Text
580 lines
11 KiB
Text
#
|
|
# 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
|