Bug #75519 Potential InnoDB data loss when binary logging is enabled

• Submitted: 15 Jan 2015 17:05
• Modified: 4 Jun 2015 10:05
• Reporter: Davi Arnaut (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.6, 5.6.25
• OS: Any
• CPU Architecture: Any
• Tags: binary log, data loss, innodb, innodb_flush_log_at_trx_commit

[15 Jan 2015 17:05] Davi Arnaut

Description:
When binary logging is enabled, InnoDB’s redo log is not flushed to
disk even if innodb_flush_log_at_trx_commit is set to 1. This means
that if there is a crash shortly after a transaction is committed,
the transaction will be in prepared state after recovery (there is
still a redo log flush during prepare).

This would normally not be a problem since one of the steps in
recovery is to commit any prepared transactions that have made it to
the binary log. But if sync_binlog is not used, there are no
guarantees that the committed transaction will be in the binary log,
in which case recovery will rollback any prepared transactions, thus
leading to data loss of committed transactions.

How to repeat:
Crash server after a transaction is committed, but before redo log is flushed.
Before recovery, remove binary log.
After recovery, the transaction will have been rolled back.

Example:

2015-01-14 18:34:09 7fd818e30740  InnoDB: Transaction 1295 in prepared state after recovery
2015-01-14 18:34:09 7fd818e30740  InnoDB: Transaction contains changes to 1 rows
2015-01-14 18:34:09 7fd818e30740  InnoDB: 1 transactions in prepared state after recovery
2015-01-14 18:34:09 14515 [Note] Found 1 prepared transaction(s) in InnoDB
2015-01-14 18:34:09 14515 [Note] rollback xid 'MySQLXid\1\0\0\0\0\0\0\0!\0\0\0\0\0\0\0'

[4 Jun 2015 10:05] MySQL Verification Team

Hello Davi,

Thank you for the report.
Sorry somehow this report sipped from my queue.
Observed this behavior with 5.6.25 build(source).

Thanks,
Umesh