4 DROP TABLE IF EXISTS t1, t2;
6 # We need a fixed timestamp to avoid varying results.
8 SET timestamp=1000000000;
10 # Delete all existing binary logs.
19 ) ENGINE=InnoDB DEFAULT CHARSET latin1;
23 ) ENGINE=MyISAM DEFAULT CHARSET latin1;
25 # Start transaction #1, transactional table only, commit.
31 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
32 UPDATE t1 SET c1 = c1 + 10;
33 DELETE FROM t1 WHERE c1 = 12;
44 # Start transaction #2, transactional table only, rollback.
50 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
51 UPDATE t1 SET c1 = c1 + 10;
52 DELETE FROM t1 WHERE c1 = 12;
54 # Rollback transaction.
61 # Start transaction #3, both tables, commit.
65 # Do some statements on the transactional table.
67 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
68 UPDATE t1 SET c1 = c1 + 10;
69 DELETE FROM t1 WHERE c1 = 12;
71 # Do some statements on the non-transactional table.
73 INSERT INTO t2 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
74 UPDATE t2 SET c1 = c1 + 10;
75 DELETE FROM t2 WHERE c1 = 12;
91 # Start transaction #4, both tables, rollback.
95 # Do some statements on the transactional table.
97 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
98 UPDATE t1 SET c1 = c1 + 10;
99 DELETE FROM t1 WHERE c1 = 12;
101 # Do some statements on the non-transactional table.
103 INSERT INTO t2 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
104 UPDATE t2 SET c1 = c1 + 10;
105 DELETE FROM t2 WHERE c1 = 12;
107 # Rollback transaction.
111 Warning 1196 Some non-transactional changed tables couldn't be rolled back
121 # Flush all log buffers to the log file.
125 # Call mysqlbinlog to display the log file contents.
127 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
128 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
131 #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup
134 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
136 SET TIMESTAMP=1000000000/*!*/;
137 SET @@session.pseudo_thread_id=#/*!*/;
138 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
139 SET @@session.sql_mode=0/*!*/;
140 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
141 /*!\C latin1 *//*!*/;
142 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
143 SET @@session.lc_time_names=0/*!*/;
144 SET @@session.collation_database=DEFAULT/*!*/;
148 ) ENGINE=InnoDB DEFAULT CHARSET latin1
151 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
152 SET TIMESTAMP=1000000000/*!*/;
156 ) ENGINE=MyISAM DEFAULT CHARSET latin1
159 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
160 SET TIMESTAMP=1000000000/*!*/;
165 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
166 #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F
167 ### INSERT INTO `test`.`t1`
169 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
170 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
171 ### INSERT INTO `test`.`t1`
173 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
174 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
175 ### INSERT INTO `test`.`t1`
177 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
178 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
181 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
182 #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F
183 ### UPDATE `test`.`t1`
185 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
186 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
188 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */
189 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
190 ### UPDATE `test`.`t1`
192 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
193 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
195 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
196 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
197 ### UPDATE `test`.`t1`
199 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
200 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
202 ### @1=13 /* INT meta=0 nullable=1 is_null=0 */
203 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
206 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
207 #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F
208 ### DELETE FROM `test`.`t1`
210 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
211 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
213 #010909 4:46:40 server id 1 end_log_pos # Xid = #
216 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
217 SET TIMESTAMP=1000000000/*!*/;
221 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
222 SET TIMESTAMP=1000000000/*!*/;
226 #010909 4:46:40 server id 1 end_log_pos # Xid = #
229 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
230 SET TIMESTAMP=1000000000/*!*/;
234 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
235 SET TIMESTAMP=1000000000/*!*/;
239 #010909 4:46:40 server id 1 end_log_pos # Xid = #
242 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
243 SET TIMESTAMP=1000000000/*!*/;
248 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
249 #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F
250 ### INSERT INTO `test`.`t1`
252 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
253 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
254 ### INSERT INTO `test`.`t1`
256 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
257 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
258 ### INSERT INTO `test`.`t1`
260 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
261 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
264 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
265 #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F
266 ### UPDATE `test`.`t1`
268 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
269 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
271 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */
272 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
273 ### UPDATE `test`.`t1`
275 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
276 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
278 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
279 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
280 ### UPDATE `test`.`t1`
282 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
283 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
285 ### @1=13 /* INT meta=0 nullable=1 is_null=0 */
286 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
289 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
290 #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F
291 ### DELETE FROM `test`.`t1`
293 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
294 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
297 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t2` mapped to number #
298 #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F
299 ### INSERT INTO `test`.`t2`
301 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
302 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
303 ### INSERT INTO `test`.`t2`
305 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
306 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
307 ### INSERT INTO `test`.`t2`
309 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
310 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
313 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t2` mapped to number #
314 #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F
315 ### UPDATE `test`.`t2`
317 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
318 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
320 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */
321 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
322 ### UPDATE `test`.`t2`
324 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
325 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
327 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
328 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
329 ### UPDATE `test`.`t2`
331 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
332 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
334 ### @1=13 /* INT meta=0 nullable=1 is_null=0 */
335 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
338 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t2` mapped to number #
339 #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F
340 ### DELETE FROM `test`.`t2`
342 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
343 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
345 #010909 4:46:40 server id 1 end_log_pos # Xid = #
348 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
349 SET TIMESTAMP=1000000000/*!*/;
353 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
354 SET TIMESTAMP=1000000000/*!*/;
358 #010909 4:46:40 server id 1 end_log_pos # Xid = #
361 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
362 SET TIMESTAMP=1000000000/*!*/;
366 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
367 SET TIMESTAMP=1000000000/*!*/;
372 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
373 #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F
374 ### INSERT INTO `test`.`t1`
376 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
377 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
378 ### INSERT INTO `test`.`t1`
380 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
381 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
382 ### INSERT INTO `test`.`t1`
384 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
385 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
388 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
389 #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F
390 ### UPDATE `test`.`t1`
392 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
393 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
395 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */
396 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
397 ### UPDATE `test`.`t1`
399 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
400 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
402 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
403 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
404 ### UPDATE `test`.`t1`
406 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
407 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
409 ### @1=13 /* INT meta=0 nullable=1 is_null=0 */
410 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
413 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number #
414 #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F
415 ### DELETE FROM `test`.`t1`
417 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
418 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
421 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t2` mapped to number #
422 #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F
423 ### INSERT INTO `test`.`t2`
425 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
426 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
427 ### INSERT INTO `test`.`t2`
429 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
430 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
431 ### INSERT INTO `test`.`t2`
433 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
434 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
437 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t2` mapped to number #
438 #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F
439 ### UPDATE `test`.`t2`
441 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
442 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
444 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */
445 ### @2='varchar-1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
446 ### UPDATE `test`.`t2`
448 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */
449 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
451 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
452 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
453 ### UPDATE `test`.`t2`
455 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */
456 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
458 ### @1=13 /* INT meta=0 nullable=1 is_null=0 */
459 ### @2='varchar-3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
462 #010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t2` mapped to number #
463 #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F
464 ### DELETE FROM `test`.`t2`
466 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */
467 ### @2='varchar-2' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
469 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
470 SET TIMESTAMP=1000000000/*!*/;
474 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
475 SET TIMESTAMP=1000000000/*!*/;
479 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
480 SET TIMESTAMP=1000000000/*!*/;
484 #010909 4:46:40 server id 1 end_log_pos # Xid = #
487 #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0
488 SET TIMESTAMP=1000000000/*!*/;
492 #010909 4:46:40 server id 1 end_log_pos # Rotate to master-bin.000002 pos: 4
495 ROLLBACK /* added by mysqlbinlog */;
496 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;