1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
drop table if exists t1;
drop view if exists t1;
#
# MDEV-6948: SET STATEMENT gtid_domain_id = ... FOR has no effect
# (same for gtid_seq_no and server_id)
#
reset master;
create table t1 (i int);
set gtid_domain_id = 10;
insert into t1 values (1),(2);
set statement gtid_domain_id = 20 for insert into t1 values (3),(4);
show binlog events limit 5,5;
Log_name Pos Event_type Server_id End_log_pos Info
x x x x x BEGIN GTID 10-1-1
x x x x x use `test`; insert into t1 values (1),(2)
x x x x x COMMIT
x x x x x BEGIN GTID 20-1-1
x x x x x use `test`; set statement gtid_domain_id = 20 for insert into t1 values (3),(4)
drop table t1;
reset master;
SET @a=11;
create table t1 (a int not null auto_increment, c int, d int, primary key (a));
create table t2 (b int);
insert into t2 values (1),(2);
CREATE function f1() returns int
BEGIN
SET STATEMENT last_insert_id=@a for insert into t1 values (NULL, @a,
last_insert_id());
SET @a:=@a*100+13;
return @a;
end|
call mtr.add_suppression("Unsafe statement written to the binary log using");
select f1() from t2;
f1()
1113
111313
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
show binlog events limit 16, 100;
Log_name Pos Event_type Server_id End_log_pos Info
x x x x x LAST_INSERT_ID=0
x x x x x INSERT_ID=1
x x x x x @`a`=11
x x x x x @`a`=11
x x x x x use `test`; SELECT `test`.`f1`()
x x x x x LAST_INSERT_ID=0
x x x x x INSERT_ID=2
x x x x x @`a`=1113
x x x x x @`a`=1113
x x x x x use `test`; SELECT `test`.`f1`()
x x x x x COMMIT
select * from t1;
a c d
1 11 11
2 1113 1113
drop function f1;
drop table t1,t2;
reset master;
SET @a=11;
create table t1 (a int not null auto_increment, c int, d int, primary key (a));
create table t2 (b int);
insert into t2 values (1),(2);
CREATE function f1() returns int
BEGIN
SET @save= @@last_insert_id;
SET session last_insert_id=@a;
insert into t1 values (NULL, @a, last_insert_id());
SET session last_insert_id=@save;
SET @a:=@a*100+13;
return @a;
end|
select f1() from t2;
f1()
1113
111313
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
show binlog events limit 13, 100;
Log_name Pos Event_type Server_id End_log_pos Info
x x x x x LAST_INSERT_ID=0
x x x x x INSERT_ID=1
x x x x x @`a`=11
x x x x x @`save`=0
x x x x x use `test`; SELECT `test`.`f1`()
x x x x x LAST_INSERT_ID=0
x x x x x INSERT_ID=2
x x x x x @`a`=1113
x x x x x @`save`=0
x x x x x use `test`; SELECT `test`.`f1`()
x x x x x COMMIT
select * from t1;
a c d
1 11 11
2 1113 1113
drop function f1;
drop table t1,t2;
reset master;
set statement last_insert_id = 112 for create table t1 as select last_insert_id();
show binlog events limit 4,1;
Log_name Pos Event_type Server_id End_log_pos Info
x x x x x LAST_INSERT_ID=112
drop table t1;
|
