Debian Codegraph @

mysql-test/suite/binlog/r/binlog_truncate_innodb.result

raw
1 SET @old_binlog_format=@@binlog_format;
2 SET BINLOG_FORMAT=ROW;
3 RESET MASTER;
4 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
5 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
6 INSERT INTO t2 VALUES (1),(2),(3);
7 **** Truncate of empty table shall be logged
8 TRUNCATE TABLE t1;
9 TRUNCATE TABLE t2;
10 include/show_binlog_events.inc
11 Log_name Pos Event_type Server_id End_log_pos Info
12 master-bin.000001 # Gtid # # GTID #-#-#
13 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
14 master-bin.000001 # Gtid # # GTID #-#-#
15 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
16 DROP TABLE t1,t2;
17 #
18 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
19 #
20 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
21 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
22 INSERT INTO t1 VALUES (1),(2);
23 BEGIN;
24 INSERT INTO t2 SELECT * FROM t1;
25 connect truncate,localhost,root,,;
26 TRUNCATE TABLE t1;
27 connection default;
28 INSERT INTO t2 SELECT * FROM t1;
29 SELECT COUNT(*) FROM t2;
30 COUNT(*)
31 4
32 COMMIT;
33 connection truncate;
34 # Reaping TRUNCATE TABLE
35 SELECT COUNT(*) FROM t1;
36 COUNT(*)
37 0
38 SELECT COUNT(*) FROM t2;
39 COUNT(*)
40 4
41 connection default;
42 include/show_binlog_events.inc
43 Log_name Pos Event_type Server_id End_log_pos Info
44 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
45 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
46 master-bin.000001 # Table_map # # table_id: # (test.t2)
47 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
48 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
49 master-bin.000001 # Table_map # # table_id: # (test.t2)
50 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
51 master-bin.000001 # Xid # # COMMIT /* XID */
52 master-bin.000001 # Gtid # # GTID #-#-#
53 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
54 disconnect truncate;
55 DROP TABLE t1,t2;
56 # Even though the isolation level might be permissive, truncate
57 # table follows a stricter isolation as its locking is based on
58 # (exclusive) metadata locks.
59 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
60 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
61 INSERT INTO t2 VALUES (1),(2),(3);
62 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
63 **** Truncate of empty table shall be logged
64 TRUNCATE TABLE t1;
65 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
66 TRUNCATE TABLE t2;
67 include/show_binlog_events.inc
68 Log_name Pos Event_type Server_id End_log_pos Info
69 master-bin.000001 # Gtid # # GTID #-#-#
70 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
71 master-bin.000001 # Gtid # # GTID #-#-#
72 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
73 DROP TABLE t1,t2;
74 #
75 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
76 #
77 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
78 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
79 INSERT INTO t1 VALUES (1),(2);
80 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
81 BEGIN;
82 INSERT INTO t2 SELECT * FROM t1;
83 connect truncate,localhost,root,,;
84 TRUNCATE TABLE t1;
85 connection default;
86 INSERT INTO t2 SELECT * FROM t1;
87 SELECT COUNT(*) FROM t2;
88 COUNT(*)
89 4
90 COMMIT;
91 connection truncate;
92 # Reaping TRUNCATE TABLE
93 SELECT COUNT(*) FROM t1;
94 COUNT(*)
95 0
96 SELECT COUNT(*) FROM t2;
97 COUNT(*)
98 4
99 connection default;
100 include/show_binlog_events.inc
101 Log_name Pos Event_type Server_id End_log_pos Info
102 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
103 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
104 master-bin.000001 # Table_map # # table_id: # (test.t2)
105 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
106 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
107 master-bin.000001 # Table_map # # table_id: # (test.t2)
108 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
109 master-bin.000001 # Xid # # COMMIT /* XID */
110 master-bin.000001 # Gtid # # GTID #-#-#
111 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
112 disconnect truncate;
113 DROP TABLE t1,t2;
114 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
115 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
116 INSERT INTO t2 VALUES (1),(2),(3);
117 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
118 **** Truncate of empty table shall be logged
119 TRUNCATE TABLE t1;
120 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
121 TRUNCATE TABLE t2;
122 include/show_binlog_events.inc
123 Log_name Pos Event_type Server_id End_log_pos Info
124 master-bin.000001 # Gtid # # GTID #-#-#
125 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
126 master-bin.000001 # Gtid # # GTID #-#-#
127 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
128 DROP TABLE t1,t2;
129 #
130 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
131 #
132 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
133 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
134 INSERT INTO t1 VALUES (1),(2);
135 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
136 BEGIN;
137 INSERT INTO t2 SELECT * FROM t1;
138 connect truncate,localhost,root,,;
139 TRUNCATE TABLE t1;
140 connection default;
141 INSERT INTO t2 SELECT * FROM t1;
142 SELECT COUNT(*) FROM t2;
143 COUNT(*)
144 4
145 COMMIT;
146 connection truncate;
147 # Reaping TRUNCATE TABLE
148 SELECT COUNT(*) FROM t1;
149 COUNT(*)
150 0
151 SELECT COUNT(*) FROM t2;
152 COUNT(*)
153 4
154 connection default;
155 include/show_binlog_events.inc
156 Log_name Pos Event_type Server_id End_log_pos Info
157 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
158 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
159 master-bin.000001 # Table_map # # table_id: # (test.t2)
160 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
161 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
162 master-bin.000001 # Table_map # # table_id: # (test.t2)
163 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
164 master-bin.000001 # Xid # # COMMIT /* XID */
165 master-bin.000001 # Gtid # # GTID #-#-#
166 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
167 disconnect truncate;
168 DROP TABLE t1,t2;
169 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
170 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
171 INSERT INTO t2 VALUES (1),(2),(3);
172 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
173 **** Truncate of empty table shall be logged
174 TRUNCATE TABLE t1;
175 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
176 TRUNCATE TABLE t2;
177 include/show_binlog_events.inc
178 Log_name Pos Event_type Server_id End_log_pos Info
179 master-bin.000001 # Gtid # # GTID #-#-#
180 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
181 master-bin.000001 # Gtid # # GTID #-#-#
182 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
183 DROP TABLE t1,t2;
184 #
185 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
186 #
187 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
188 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
189 INSERT INTO t1 VALUES (1),(2);
190 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
191 BEGIN;
192 INSERT INTO t2 SELECT * FROM t1;
193 connect truncate,localhost,root,,;
194 TRUNCATE TABLE t1;
195 connection default;
196 INSERT INTO t2 SELECT * FROM t1;
197 SELECT COUNT(*) FROM t2;
198 COUNT(*)
199 4
200 COMMIT;
201 connection truncate;
202 # Reaping TRUNCATE TABLE
203 SELECT COUNT(*) FROM t1;
204 COUNT(*)
205 0
206 SELECT COUNT(*) FROM t2;
207 COUNT(*)
208 4
209 connection default;
210 include/show_binlog_events.inc
211 Log_name Pos Event_type Server_id End_log_pos Info
212 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
213 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
214 master-bin.000001 # Table_map # # table_id: # (test.t2)
215 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
216 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
217 master-bin.000001 # Table_map # # table_id: # (test.t2)
218 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
219 master-bin.000001 # Xid # # COMMIT /* XID */
220 master-bin.000001 # Gtid # # GTID #-#-#
221 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
222 disconnect truncate;
223 DROP TABLE t1,t2;
224 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
225 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
226 INSERT INTO t2 VALUES (1),(2),(3);
227 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
228 **** Truncate of empty table shall be logged
229 TRUNCATE TABLE t1;
230 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
231 TRUNCATE TABLE t2;
232 include/show_binlog_events.inc
233 Log_name Pos Event_type Server_id End_log_pos Info
234 master-bin.000001 # Gtid # # GTID #-#-#
235 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
236 master-bin.000001 # Gtid # # GTID #-#-#
237 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
238 DROP TABLE t1,t2;
239 #
240 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
241 #
242 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
243 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
244 INSERT INTO t1 VALUES (1),(2);
245 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
246 BEGIN;
247 INSERT INTO t2 SELECT * FROM t1;
248 connect truncate,localhost,root,,;
249 TRUNCATE TABLE t1;
250 connection default;
251 INSERT INTO t2 SELECT * FROM t1;
252 SELECT COUNT(*) FROM t2;
253 COUNT(*)
254 4
255 COMMIT;
256 connection truncate;
257 # Reaping TRUNCATE TABLE
258 SELECT COUNT(*) FROM t1;
259 COUNT(*)
260 0
261 SELECT COUNT(*) FROM t2;
262 COUNT(*)
263 4
264 connection default;
265 include/show_binlog_events.inc
266 Log_name Pos Event_type Server_id End_log_pos Info
267 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
268 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
269 master-bin.000001 # Table_map # # table_id: # (test.t2)
270 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
271 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
272 master-bin.000001 # Table_map # # table_id: # (test.t2)
273 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
274 master-bin.000001 # Xid # # COMMIT /* XID */
275 master-bin.000001 # Gtid # # GTID #-#-#
276 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
277 disconnect truncate;
278 DROP TABLE t1,t2;
279 SET BINLOG_FORMAT=STATEMENT;
280 RESET MASTER;
281 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
282 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
283 INSERT INTO t2 VALUES (1),(2),(3);
284 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
285 **** Truncate of empty table shall be logged
286 TRUNCATE TABLE t1;
287 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
288 TRUNCATE TABLE t2;
289 include/show_binlog_events.inc
290 Log_name Pos Event_type Server_id End_log_pos Info
291 master-bin.000001 # Gtid # # GTID #-#-#
292 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
293 master-bin.000001 # Gtid # # GTID #-#-#
294 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
295 DROP TABLE t1,t2;
296 #
297 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
298 #
299 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
300 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
301 INSERT INTO t1 VALUES (1),(2);
302 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
303 BEGIN;
304 INSERT INTO t2 SELECT * FROM t1;
305 connect truncate,localhost,root,,;
306 TRUNCATE TABLE t1;
307 connection default;
308 INSERT INTO t2 SELECT * FROM t1;
309 SELECT COUNT(*) FROM t2;
310 COUNT(*)
311 4
312 COMMIT;
313 connection truncate;
314 # Reaping TRUNCATE TABLE
315 SELECT COUNT(*) FROM t1;
316 COUNT(*)
317 0
318 SELECT COUNT(*) FROM t2;
319 COUNT(*)
320 4
321 connection default;
322 include/show_binlog_events.inc
323 Log_name Pos Event_type Server_id End_log_pos Info
324 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
325 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
326 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
327 master-bin.000001 # Xid # # COMMIT /* XID */
328 master-bin.000001 # Gtid # # GTID #-#-#
329 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
330 disconnect truncate;
331 DROP TABLE t1,t2;
332 # Truncate is not supported for SBR if the isolation level is
333 # READ UNCOMMITTED or READ COMMITTED. These specific isolation
334 # levels are tested elsewhere.
335 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
336 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
337 INSERT INTO t2 VALUES (1),(2),(3);
338 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
339 **** Truncate of empty table shall be logged
340 TRUNCATE TABLE t1;
341 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
342 TRUNCATE TABLE t2;
343 include/show_binlog_events.inc
344 Log_name Pos Event_type Server_id End_log_pos Info
345 master-bin.000001 # Gtid # # GTID #-#-#
346 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
347 master-bin.000001 # Gtid # # GTID #-#-#
348 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
349 DROP TABLE t1,t2;
350 #
351 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
352 #
353 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
354 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
355 INSERT INTO t1 VALUES (1),(2);
356 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
357 BEGIN;
358 INSERT INTO t2 SELECT * FROM t1;
359 connect truncate,localhost,root,,;
360 TRUNCATE TABLE t1;
361 connection default;
362 INSERT INTO t2 SELECT * FROM t1;
363 SELECT COUNT(*) FROM t2;
364 COUNT(*)
365 4
366 COMMIT;
367 connection truncate;
368 # Reaping TRUNCATE TABLE
369 SELECT COUNT(*) FROM t1;
370 COUNT(*)
371 0
372 SELECT COUNT(*) FROM t2;
373 COUNT(*)
374 4
375 connection default;
376 include/show_binlog_events.inc
377 Log_name Pos Event_type Server_id End_log_pos Info
378 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
379 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
380 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
381 master-bin.000001 # Xid # # COMMIT /* XID */
382 master-bin.000001 # Gtid # # GTID #-#-#
383 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
384 disconnect truncate;
385 DROP TABLE t1,t2;
386 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
387 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
388 INSERT INTO t2 VALUES (1),(2),(3);
389 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
390 **** Truncate of empty table shall be logged
391 TRUNCATE TABLE t1;
392 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
393 TRUNCATE TABLE t2;
394 include/show_binlog_events.inc
395 Log_name Pos Event_type Server_id End_log_pos Info
396 master-bin.000001 # Gtid # # GTID #-#-#
397 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
398 master-bin.000001 # Gtid # # GTID #-#-#
399 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
400 DROP TABLE t1,t2;
401 #
402 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
403 #
404 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
405 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
406 INSERT INTO t1 VALUES (1),(2);
407 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
408 BEGIN;
409 INSERT INTO t2 SELECT * FROM t1;
410 connect truncate,localhost,root,,;
411 TRUNCATE TABLE t1;
412 connection default;
413 INSERT INTO t2 SELECT * FROM t1;
414 SELECT COUNT(*) FROM t2;
415 COUNT(*)
416 4
417 COMMIT;
418 connection truncate;
419 # Reaping TRUNCATE TABLE
420 SELECT COUNT(*) FROM t1;
421 COUNT(*)
422 0
423 SELECT COUNT(*) FROM t2;
424 COUNT(*)
425 4
426 connection default;
427 include/show_binlog_events.inc
428 Log_name Pos Event_type Server_id End_log_pos Info
429 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
430 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
431 master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
432 master-bin.000001 # Xid # # COMMIT /* XID */
433 master-bin.000001 # Gtid # # GTID #-#-#
434 master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
435 disconnect truncate;
436 DROP TABLE t1,t2;
437 SET @@global.binlog_format = @old_binlog_format;
438 SET @@session.binlog_format = @old_binlog_format;