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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
include/master-slave.inc
[connection master]
connection slave;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
connection master;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
SET @@session.binlog_direct_non_transactional_updates= FALSE;
DROP DATABASE IF EXISTS db1;
DROP DATABASE IF EXISTS db2;
CREATE DATABASE db1;
CREATE DATABASE db2;
use db1;
CREATE TABLE db1.t1 (a INT) ENGINE=InnoDB;
CREATE TABLE db1.t2 (s CHAR(255)) ENGINE=MyISAM;
connection slave;
include/stop_slave.inc
connection master;
CREATE PROCEDURE db1.p1 ()
BEGIN
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (5);
END//
CREATE PROCEDURE db1.p2 ()
BEGIN
INSERT INTO t1 VALUES (6);
INSERT INTO t1 VALUES (7);
INSERT INTO t1 VALUES (8);
INSERT INTO t1 VALUES (9);
INSERT INTO t1 VALUES (10);
INSERT INTO t2 VALUES ('executed db1.p2()');
END//
INSERT INTO db1.t2 VALUES ('before call db1.p1()');
use test;
BEGIN;
CALL db1.p1();
COMMIT;
INSERT INTO db1.t2 VALUES ('after call db1.p1()');
SELECT * FROM db1.t1;
a
1
2
3
4
5
SELECT * FROM db1.t2;
s
before call db1.p1()
after call db1.p1()
connection slave;
start slave until master_log_file='master-bin.000001', master_log_pos=MASTER_POS;
include/wait_for_slave_sql_to_stop.inc
#
# If we got non-zero here, then we're suffering BUG#43263
#
SELECT 0 as 'Must be 0';
Must be 0
0
SELECT * from db1.t1;
a
1
2
3
4
5
SELECT * from db1.t2;
s
before call db1.p1()
connection master;
INSERT INTO db1.t2 VALUES ('before call db1.p2()');
BEGIN;
CALL db1.p2();
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
ROLLBACK;
INSERT INTO db1.t2 VALUES ('after call db1.p2()');
SELECT * FROM db1.t1;
a
1
2
3
4
5
SELECT * FROM db1.t2;
s
before call db1.p1()
after call db1.p1()
before call db1.p2()
executed db1.p2()
after call db1.p2()
connection slave;
start slave until master_log_file='master-bin.000001', master_log_pos=MASTER_POS;
include/wait_for_slave_sql_to_stop.inc
#
# If we got non-zero here, then we're suffering BUG#43263
#
SELECT 0 as 'Must be 0';
Must be 0
0
SELECT * from db1.t1;
a
1
2
3
4
5
SELECT * from db1.t2;
s
before call db1.p1()
executed db1.p2()
START SLAVE;
include/wait_for_slave_sql_to_start.inc
#
# SAVEPOINT and ROLLBACK TO have the same problem in BUG#43263
# This was reported by BUG#50407
connection master;
BEGIN;
INSERT INTO db1.t1 VALUES(20);
#
# Verify whether this statement is binlogged correctly
/*comment*/ SAVEPOINT has_comment;
USE db1;
INSERT INTO db1.t1 VALUES(30);
INSERT INTO db1.t2 VALUES("in savepoint has_comment");
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
USE db2;
SavePoint mixed_cases;
USE db1;
INSERT INTO db1.t2 VALUES("in savepoint mixed_cases");
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
INSERT INTO db1.t1 VALUES(40);
USE db2;
ROLLBACK TO mixed_cases;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
ROLLBACK TO has_comment;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
USE db1;
INSERT INTO db1.t2 VALUES("after rollback to");
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
INSERT INTO db1.t1 VALUES(50);
USE db2;
COMMIT;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO db1.t1 VALUES(20)
master-bin.000001 # Query # # SAVEPOINT `has_comment`
master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(30)
master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("in savepoint has_comment")
master-bin.000001 # Query # # SAVEPOINT `mixed_cases`
master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("in savepoint mixed_cases")
master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(40)
master-bin.000001 # Query # # ROLLBACK TO `mixed_cases`
master-bin.000001 # Query # # ROLLBACK TO `has_comment`
master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("after rollback to")
master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(50)
master-bin.000001 # Xid # # COMMIT /* XID */
connection slave;
#
# Verify INSERT statements in savepoints are executed, for MyISAM table
# is not effected by ROLLBACK TO
SELECT * FROM db1.t2 WHERE s LIKE '% savepoint %';
s
in savepoint has_comment
in savepoint mixed_cases
#
# Verify INSERT statements on the Innodb table are rolled back;
SELECT * FROM db1.t1 WHERE a IN (30, 40);
a
#
# Clean up
#
connection master;
DROP DATABASE db1;
DROP DATABASE db2;
include/rpl_end.inc
|
