1
###################################################################################
3
###################################################################################
4
CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
5
CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM;
6
CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb;
7
CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb;
8
CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW
10
INSERT INTO nt_1 VALUES (NEW.a, NEW.b);
12
CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW
14
INSERT INTO tt_2 VALUES (NEW.a, NEW.b);
16
###################################################################################
17
# CHECK HISTORY IN BINLOG
18
###################################################################################
22
*** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries
24
INSERT INTO nt_1 VALUES ("new text 1", 1);
26
INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1);
27
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
28
INSERT INTO tt_2 VALUES ("new text 3", 3);
30
show binlog events from <binlog_start>;
31
Log_name Pos Event_type Server_id End_log_pos Info
32
master-bin.000001 # Query # # BEGIN
33
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
34
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
35
master-bin.000001 # Query # # COMMIT
36
master-bin.000001 # Query # # BEGIN
37
master-bin.000001 # Table_map # # table_id: # (test.tt_1)
38
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
39
master-bin.000001 # Write_rows # # table_id: #
40
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
41
master-bin.000001 # Query # # ROLLBACK
42
master-bin.000001 # Query # # BEGIN
43
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
44
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
45
master-bin.000001 # Xid # # COMMIT /* XID */
47
INSERT INTO tt_2 VALUES ("new text 4", 4);
49
INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4);
50
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
51
INSERT INTO tt_2 VALUES ("new text 6", 6);
53
show binlog events from <binlog_start>;
54
Log_name Pos Event_type Server_id End_log_pos Info
55
master-bin.000001 # Query # # BEGIN
56
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
57
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
58
master-bin.000001 # Xid # # COMMIT /* XID */
59
master-bin.000001 # Query # # BEGIN
60
master-bin.000001 # Table_map # # table_id: # (test.nt_2)
61
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
62
master-bin.000001 # Write_rows # # table_id: #
63
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
64
master-bin.000001 # Query # # ROLLBACK
65
master-bin.000001 # Query # # BEGIN
66
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
67
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
68
master-bin.000001 # Xid # # COMMIT /* XID */
72
*** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries
74
INSERT INTO nt_1 VALUES ("new text 10", 10);
76
INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8);
77
INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10);
78
ERROR 23000: Duplicate entry '10' for key 'PRIMARY'
79
INSERT INTO tt_2 VALUES ("new text 11", 11);
81
show binlog events from <binlog_start>;
82
Log_name Pos Event_type Server_id End_log_pos Info
83
master-bin.000001 # Query # # BEGIN
84
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
85
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
86
master-bin.000001 # Query # # COMMIT
87
master-bin.000001 # Query # # BEGIN
88
master-bin.000001 # Table_map # # table_id: # (test.tt_1)
89
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
90
master-bin.000001 # Write_rows # # table_id: #
91
master-bin.000001 # Write_rows # # table_id: #
92
master-bin.000001 # Write_rows # # table_id: #
93
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
94
master-bin.000001 # Table_map # # table_id: # (test.tt_1)
95
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
96
master-bin.000001 # Write_rows # # table_id: #
97
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
98
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
99
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
100
master-bin.000001 # Xid # # COMMIT /* XID */
102
INSERT INTO tt_2 VALUES ("new text 15", 15);
104
INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13);
105
INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15);
106
ERROR 23000: Duplicate entry '15' for key 'PRIMARY'
107
INSERT INTO tt_2 VALUES ("new text 16", 16);
109
show binlog events from <binlog_start>;
110
Log_name Pos Event_type Server_id End_log_pos Info
111
master-bin.000001 # Query # # BEGIN
112
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
113
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
114
master-bin.000001 # Xid # # COMMIT /* XID */
115
master-bin.000001 # Query # # BEGIN
116
master-bin.000001 # Table_map # # table_id: # (test.nt_2)
117
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
118
master-bin.000001 # Write_rows # # table_id: #
119
master-bin.000001 # Write_rows # # table_id: #
120
master-bin.000001 # Write_rows # # table_id: #
121
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
122
master-bin.000001 # Table_map # # table_id: # (test.nt_2)
123
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
124
master-bin.000001 # Write_rows # # table_id: #
125
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
126
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
127
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
128
master-bin.000001 # Xid # # COMMIT /* XID */
132
*** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries
134
INSERT INTO nt_1 VALUES ("new text 18", 18);
135
INSERT INTO nt_1 VALUES ("new text 20", 20);
137
INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18);
138
ERROR 23000: Duplicate entry '18' for key 'PRIMARY'
139
INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20);
140
ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
141
INSERT INTO tt_2 VALUES ("new text 21", 21);
143
show binlog events from <binlog_start>;
144
Log_name Pos Event_type Server_id End_log_pos Info
145
master-bin.000001 # Query # # BEGIN
146
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
147
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
148
master-bin.000001 # Query # # COMMIT
149
master-bin.000001 # Query # # BEGIN
150
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
151
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
152
master-bin.000001 # Query # # COMMIT
153
master-bin.000001 # Query # # BEGIN
154
master-bin.000001 # Table_map # # table_id: # (test.tt_1)
155
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
156
master-bin.000001 # Write_rows # # table_id: #
157
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
158
master-bin.000001 # Query # # ROLLBACK
159
master-bin.000001 # Query # # BEGIN
160
master-bin.000001 # Table_map # # table_id: # (test.tt_1)
161
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
162
master-bin.000001 # Write_rows # # table_id: #
163
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
164
master-bin.000001 # Query # # ROLLBACK
165
master-bin.000001 # Query # # BEGIN
166
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
167
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
168
master-bin.000001 # Xid # # COMMIT /* XID */
170
INSERT INTO tt_2 VALUES ("new text 23", 23);
171
INSERT INTO tt_2 VALUES ("new text 25", 25);
173
INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23);
174
ERROR 23000: Duplicate entry '23' for key 'PRIMARY'
175
INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25);
176
ERROR 23000: Duplicate entry '25' for key 'PRIMARY'
177
INSERT INTO tt_2 VALUES ("new text 26", 26);
179
show binlog events from <binlog_start>;
180
Log_name Pos Event_type Server_id End_log_pos Info
181
master-bin.000001 # Query # # BEGIN
182
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
183
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
184
master-bin.000001 # Xid # # COMMIT /* XID */
185
master-bin.000001 # Query # # BEGIN
186
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
187
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
188
master-bin.000001 # Xid # # COMMIT /* XID */
189
master-bin.000001 # Query # # BEGIN
190
master-bin.000001 # Table_map # # table_id: # (test.nt_2)
191
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
192
master-bin.000001 # Write_rows # # table_id: #
193
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
194
master-bin.000001 # Query # # ROLLBACK
195
master-bin.000001 # Query # # BEGIN
196
master-bin.000001 # Table_map # # table_id: # (test.nt_2)
197
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
198
master-bin.000001 # Write_rows # # table_id: #
199
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
200
master-bin.000001 # Query # # ROLLBACK
201
master-bin.000001 # Query # # BEGIN
202
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
203
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
204
master-bin.000001 # Xid # # COMMIT /* XID */
208
*** "B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates
209
*** in the binlog the following entries: "Nothing".
210
*** There is a bug in that will be fixed after WL#2687. Please, check BUG#47175 for further details.
214
INSERT INTO tt_2 VALUES ("new text 7", 7);
216
INSERT INTO tt_2 VALUES ("new text 27", 27);
217
INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1;
218
ERROR 23000: Duplicate entry '7' for key 'PRIMARY'
219
INSERT INTO tt_2 VALUES ("new text 28", 28);
221
show binlog events from <binlog_start>;
222
Log_name Pos Event_type Server_id End_log_pos Info
223
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_2
224
master-bin.000001 # Query # # BEGIN
225
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2
226
master-bin.000001 # Xid # # COMMIT /* XID */
227
master-bin.000001 # Query # # BEGIN
228
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
229
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
230
master-bin.000001 # Xid # # COMMIT /* XID */
234
*** "B INSERT M..SELECT* C" with an error in INSERT M...SELECT* generates
235
*** in the binlog the following entries: "B INSERT M..SELECT* R".
239
INSERT INTO tt_2 VALUES ("new text 7", 7);
241
INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1;
242
ERROR 23000: Duplicate entry '7' for key 'PRIMARY'
244
show binlog events from <binlog_start>;
245
Log_name Pos Event_type Server_id End_log_pos Info
246
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_2
247
master-bin.000001 # Query # # BEGIN
248
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2
249
master-bin.000001 # Xid # # COMMIT /* XID */
250
master-bin.000001 # Query # # BEGIN
251
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
252
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
253
master-bin.000001 # Xid # # COMMIT /* XID */
254
master-bin.000001 # Query # # BEGIN
255
master-bin.000001 # Table_map # # table_id: # (test.nt_2)
256
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
257
master-bin.000001 # Write_rows # # table_id: #
258
master-bin.000001 # Write_rows # # table_id: #
259
master-bin.000001 # Write_rows # # table_id: #
260
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
261
master-bin.000001 # Query # # ROLLBACK
265
*** "B N N T C" generates in the binlog the "B N C B N C B T C" entries
270
INSERT INTO nt_1 VALUES (USER(), 1);
271
INSERT INTO nt_1 VALUES (USER(), 2);
272
INSERT INTO tt_2 VALUES (USER(), 3);
274
show binlog events from <binlog_start>;
275
Log_name Pos Event_type Server_id End_log_pos Info
276
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_1
277
master-bin.000001 # Query # # BEGIN
278
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2
279
master-bin.000001 # Xid # # COMMIT /* XID */
280
master-bin.000001 # Query # # BEGIN
281
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
282
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
283
master-bin.000001 # Query # # COMMIT
284
master-bin.000001 # Query # # BEGIN
285
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
286
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
287
master-bin.000001 # Query # # COMMIT
288
master-bin.000001 # Query # # BEGIN
289
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
290
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
291
master-bin.000001 # Xid # # COMMIT /* XID */
295
*** "B N N T R" generates in the binlog the "B N C B N C B T R" entries
298
INSERT INTO nt_1 VALUES (USER(), 4);
299
INSERT INTO nt_1 VALUES (USER(), 5);
300
INSERT INTO tt_2 VALUES (USER(), 6);
303
Warning 1196 Some non-transactional changed tables couldn't be rolled back
304
show binlog events from <binlog_start>;
305
Log_name Pos Event_type Server_id End_log_pos Info
306
master-bin.000001 # Query # # BEGIN
307
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
308
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
309
master-bin.000001 # Query # # COMMIT
310
master-bin.000001 # Query # # BEGIN
311
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
312
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
313
master-bin.000001 # Query # # COMMIT
314
master-bin.000001 # Query # # BEGIN
315
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
316
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
317
master-bin.000001 # Query # # ROLLBACK
321
*** "B N* N* T C" with error in N* generates in the binlog the "B N R B N R B T C" entries
324
INSERT INTO nt_1 VALUES (USER(), 7), (USER(), 1);
325
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
326
INSERT INTO nt_1 VALUES (USER(), 8), (USER(), 1);
327
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
328
INSERT INTO tt_2 VALUES (USER(), 9);
330
show binlog events from <binlog_start>;
331
Log_name Pos Event_type Server_id End_log_pos Info
332
master-bin.000001 # Query # # BEGIN
333
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
334
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
335
master-bin.000001 # Query # # ROLLBACK
336
master-bin.000001 # Query # # BEGIN
337
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
338
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
339
master-bin.000001 # Query # # ROLLBACK
340
master-bin.000001 # Query # # BEGIN
341
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
342
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
343
master-bin.000001 # Xid # # COMMIT /* XID */
347
*** "B N* N* T R" with error in N* generates in the binlog the "B N R B N R B T R" entries
350
INSERT INTO nt_1 VALUES (USER(), 10), (USER(), 1);
351
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
352
INSERT INTO nt_1 VALUES (USER(), 11), (USER(), 1);
353
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
354
INSERT INTO tt_2 VALUES (USER(), 12);
357
Warning 1196 Some non-transactional changed tables couldn't be rolled back
358
show binlog events from <binlog_start>;
359
Log_name Pos Event_type Server_id End_log_pos Info
360
master-bin.000001 # Query # # BEGIN
361
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
362
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
363
master-bin.000001 # Query # # ROLLBACK
364
master-bin.000001 # Query # # BEGIN
365
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
366
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
367
master-bin.000001 # Query # # ROLLBACK
368
master-bin.000001 # Query # # BEGIN
369
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
370
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
371
master-bin.000001 # Query # # ROLLBACK
375
*** "B N N T N T C" generates in the binlog the "B N C B N C B T N T C" entries
378
INSERT INTO nt_1 VALUES (USER(), 13);
379
INSERT INTO nt_1 VALUES (USER(), 14);
380
INSERT INTO tt_2 VALUES (USER(), 15);
381
INSERT INTO nt_1 VALUES (USER(), 16);
382
INSERT INTO tt_2 VALUES (USER(), 17);
384
show binlog events from <binlog_start>;
385
Log_name Pos Event_type Server_id End_log_pos Info
386
master-bin.000001 # Query # # BEGIN
387
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
388
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
389
master-bin.000001 # Query # # COMMIT
390
master-bin.000001 # Query # # BEGIN
391
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
392
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
393
master-bin.000001 # Query # # COMMIT
394
master-bin.000001 # Query # # BEGIN
395
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
396
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
397
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
398
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
399
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
400
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
401
master-bin.000001 # Xid # # COMMIT /* XID */
405
*** "B N N T N T R" generates in the binlog the "B N C B N C B T N T R" entries
408
INSERT INTO nt_1 VALUES (USER(), 18);
409
INSERT INTO nt_1 VALUES (USER(), 19);
410
INSERT INTO tt_2 VALUES (USER(), 20);
411
INSERT INTO nt_1 VALUES (USER(), 21);
412
INSERT INTO tt_2 VALUES (USER(), 22);
415
Warning 1196 Some non-transactional changed tables couldn't be rolled back
416
show binlog events from <binlog_start>;
417
Log_name Pos Event_type Server_id End_log_pos Info
418
master-bin.000001 # Query # # BEGIN
419
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
420
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
421
master-bin.000001 # Query # # COMMIT
422
master-bin.000001 # Query # # BEGIN
423
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
424
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
425
master-bin.000001 # Query # # COMMIT
426
master-bin.000001 # Query # # BEGIN
427
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
428
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
429
master-bin.000001 # Table_map # # table_id: # (test.nt_1)
430
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
431
master-bin.000001 # Table_map # # table_id: # (test.tt_2)
432
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
433
master-bin.000001 # Query # # ROLLBACK
434
###################################################################################
436
###################################################################################