MySQL Bugs: #69162: MySQL 5.6 may lose the last transaction when sync

Bug #69162	MySQL 5.6 may lose the last transaction when sync_binlog = 0.
Submitted:	7 May 2013 10:23	Modified:	15 Jun 2018 10:17
Reporter:	Sadao Hiratsuka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	sync_binlog

Description:
MySQL 5.6 may lose the last transaction result under the following settings.
MySQL 5.5 does not lose.

innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1
log_bin = mysql-bin
sync_binlog = 0

From MySQL 5.6, InnoDB does not invoke fsync() at 3).

1) InnoDB prepare (fsync)
2) Binlog write   (fsync if sync_binlog = 1)
3) InnoDB commit  (fsync on MySQL 5.5, not on MySQL 5.6)

When sync_binlog = 0, If the OS crashes after 3)
MySQL 5.5 does not lose the last transaction result
(but the binary log is broken).

However MySQL 5.6 may lose the last transaction result
because the binary log and InnoDB commit log are not written.

How to repeat:
N/A

Suggested fix:
When sync_binlog = 0,
thd_get_durability_property() should return HA_REGULAR_DURABILITY
(but the performance penalty is big).

Hi!

Thank you for your bug report. Your analysis is 100 % correct. However, this is a known behaviour, but alas, not well documented one.

Hence, I am verifying it as a documentation bug.

Posted by developer:
 
The sync_binlog description has been improved. 
Thank you for the bug report.

https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_sync_bi...