Mysql – commit statement missing for a dml statement in binary log

MySQLmysql-5.5

I have noticed 'commit' statement missing for a DML statement in binary log. Does it mean that this statement is rolled back in server? I am using mysql 5.5.40.

sample binlog

at 159527792

160229 23:25:22 server id 104 end_log_pos 159528127 Query thread_id=45195 exec_time=0 error_code=0(in comments)

SET TIMESTAMP=1456809922/!/;

update table_name set col1=4 where col2=4
/!/;

at 159528275

160229 23:25:22 server id 104 end_log_pos 159528484 Query thread_id=45195 exec_time=0 error_code=0(in comments)

SET TIMESTAMP=1456809922/!/;

insert into table_name values (1)
/!/;

at 159528632

160229 23:25:22 server id 104 end_log_pos 159528511 Xid = 22993533(in comments)

COMMIT/!/;

As per mysql documentation

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

please let me know whether my undersatnding is correct or not. if any statement getting rolled back, server write rollback to binlog?

Best Answer

mysql will write rollback to binary logs provided the transaction has mixture of engines (myisam and innodb tables). As the myisam can not be rollbacked.

I guess you'd like this piece of documentation...

... This ensures that the binary log reflects the exact data of InnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).