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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
|
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 connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
CALL mtr.add_suppression('Cannot execute statement because it needs to be written to the binary log as multiple statements');
CALL mtr.add_suppression('DROP DATABASE failed; some tables may have been dropped but the database directory remains.');
CALL mtr.add_suppression("Error dropping database");
CALL mtr.add_suppression("Can't drop database '.*'; database doesn't exist");
CALL mtr.add_suppression("Replica SQL for channel '': ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state. .* Error_code: 1756");
==== Case 1: CALL is split on master ====
---- Initialize ----
CREATE TABLE t (a INT);
CREATE PROCEDURE proc ()
BEGIN
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
END|
---- GTID_NEXT=AUTOMATIC ----
include/gtid_step_reset.inc
CALL proc();
include/gtid_step_assert.inc [count=2, only_count=0]
include/assert.inc [Both rows were inserted]
DELETE FROM t;
---- GTID_NEXT=non-automatic ----
include/gtid_step_reset.inc
SET GTID_NEXT = '#';
CALL proc();
ERROR HY000: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'SOURCE_UUID:9'.
SET GTID_NEXT= 'AUTOMATIC';
include/gtid_step_assert.inc [count=1, only_count=0]
include/assert.inc [One row inserted if GTID_MODE=ON, two if GTID_MODE=OFF]
DROP TABLE t;
DROP PROCEDURE proc;
==== Case 2A: DROP TABLE is split on master ====
---- Initialize ----
CREATE PROCEDURE create_tables()
BEGIN
CREATE TABLE base (a INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE temp_t (a INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE temp_n (a INT) ENGINE = MyISAM;
END|
CREATE PROCEDURE drop_tables()
BEGIN
DROP TABLE IF EXISTS base;
DROP TABLE IF EXISTS temp_t;
DROP TABLE IF EXISTS temp_n;
END|
include/rpl_sync.inc
---- GTID_MODE=AUTOMATIC ----
CALL create_tables();
include/gtid_step_reset.inc
DROP TABLE base, temp_t;
include/gtid_step_assert.inc [count=2, only_count=0]
include/assert_binlog_events.inc
DROP TABLE temp_n;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
include/gtid_step_reset.inc
DROP TABLE base, temp_n;
include/gtid_step_assert.inc [count=2, only_count=0]
include/assert_binlog_events.inc
DROP TABLE temp_t;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
include/gtid_step_reset.inc
DROP TABLE temp_t, temp_n;
include/gtid_step_assert.inc [count=2, only_count=0]
include/assert_binlog_events.inc
DROP TABLE base;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
include/gtid_step_reset.inc
DROP TABLE base, temp_t, temp_n;
include/gtid_step_assert.inc [count=3, only_count=0]
include/assert_binlog_events.inc
include/rpl_sync.inc
include/rpl_reset.inc
---- GTID_MODE=non-automatic ----
CALL create_tables();
include/gtid_step_reset.inc
SET GTID_NEXT = '#';
DROP TABLE base, temp_t;
ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
SET GTID_NEXT = 'AUTOMATIC';
include/gtid_step_assert.inc [count=0, only_count=0]
include/assert_binlog_events.inc
DROP TABLE temp_n;
DROP TABLE base, temp_t;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
include/gtid_step_reset.inc
SET GTID_NEXT = '#';
DROP TABLE base, temp_n;
ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
SET GTID_NEXT = 'AUTOMATIC';
include/gtid_step_assert.inc [count=0, only_count=0]
include/assert_binlog_events.inc
DROP TABLE temp_t;
DROP TABLE base, temp_n;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
include/gtid_step_reset.inc
SET GTID_NEXT = '#';
DROP TABLE temp_t, temp_n;
ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
SET GTID_NEXT = 'AUTOMATIC';
include/gtid_step_assert.inc [count=0, only_count=0]
include/assert_binlog_events.inc
DROP TABLE base;
DROP TABLE temp_t, temp_n;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
include/gtid_step_reset.inc
SET GTID_NEXT = '#';
DROP TABLE base, temp_t, temp_n;
ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
SET GTID_NEXT = 'AUTOMATIC';
include/gtid_step_assert.inc [count=0, only_count=0]
include/assert_binlog_events.inc
DROP TABLE base, temp_t, temp_n;
include/rpl_sync.inc
include/rpl_reset.inc
==== Case 2B: DROP TABLE is split on slave ====
---- Initialize ----
CREATE TABLE dummy (a INT);
DROP PROCEDURE create_tables;
CREATE FUNCTION create_tables_func() RETURNS INT
BEGIN
IF @@GLOBAL.SERVER_ID = 2 THEN
CREATE TEMPORARY TABLE temp_t (a INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE temp_n (a INT) ENGINE = MyISAM;
END IF;
RETURN 0;
END|
CREATE PROCEDURE create_tables()
BEGIN
CREATE TABLE base (a INT);
SET @@SESSION.SQL_LOG_BIN = 0;
CREATE TABLE temp_t (a INT);
CREATE TABLE temp_n (a INT);
SET @@SESSION.SQL_LOG_BIN = 1;
INSERT INTO dummy VALUES (create_tables_func());
END|
include/rpl_sync.inc
---- GTID_MODE=AUTOMATIC ----
CALL create_tables();
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the replica.
include/gtid_step_reset.inc
DROP TABLE base, temp_t;
include/gtid_step_assert.inc [count=1, only_count=0]
include/assert_binlog_events.inc
DROP TABLE temp_n;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the replica.
include/gtid_step_reset.inc
DROP TABLE base, temp_n;
include/gtid_step_assert.inc [count=1, only_count=0]
include/assert_binlog_events.inc
DROP TABLE temp_t;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the replica.
include/gtid_step_reset.inc
DROP TABLE temp_t, temp_n;
include/gtid_step_assert.inc [count=1, only_count=0]
include/assert_binlog_events.inc
DROP TABLE base;
include/rpl_sync.inc
include/rpl_reset.inc
CALL create_tables();
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the replica.
include/gtid_step_reset.inc
DROP TABLE base, temp_t, temp_n;
include/gtid_step_assert.inc [count=1, only_count=0]
include/assert_binlog_events.inc
include/rpl_sync.inc
include/rpl_reset.inc
---- Clean up ----
DROP FUNCTION create_tables_func;
DROP PROCEDURE create_tables;
DROP PROCEDURE drop_tables;
DROP TABLE dummy;
==== Case 2C: DROP TABLES for base tables in engines with and without atomic DDL support ====
CREATE TABLE base_1_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_2_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_3_a (a INT) ENGINE = InnoDB;
CREATE TABLE base_4_a (a INT) ENGINE = InnoDB;
include/rpl_sync.inc
---- GTID_MODE=AUTOMATIC ----
include/gtid_step_reset.inc
DROP TABLES base_1_n, base_2_n, base_3_a, base_4_a;
# In AUTOMATIC mode the above statement should be split into three:
# two statements for each of MyISAM tables and single statement
# dropping both of InnoDB tables.
include/gtid_step_assert.inc [count=3, only_count=0]
CREATE TABLE base_1_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_2_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_3_a (a INT) ENGINE = InnoDB;
CREATE TABLE base_4_a (a INT) ENGINE = InnoDB;
include/rpl_sync.inc
---- GTID_NEXT=non-automatic ----
include/gtid_step_reset.inc
SET GTID_NEXT = '#';
DROP TABLES base_1_n, base_2_n, base_3_a, base_4_a;
SET GTID_NEXT= 'AUTOMATIC';
# With GTID assigned the above statement should not be split.
include/gtid_step_assert.inc [count=1, only_count=0]
include/rpl_sync.inc
==== Case 2D: Failing DROP TABLES for base tables with and without atomic DDL support ====
See rpl_split_statements_debug.test
==== Case 3: DROP DATABASE ====
See rpl_split_statements_debug.test
==== Case 4: CREATE TABLE ... SELECT ====
See rpl_gtid_create_select.test
include/rpl_end.inc
|
