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) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.6, 5.6.25 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | binary log, data loss, innodb, innodb_flush_log_at_trx_commit |
[15 Jan 2015 17:05]
Davi Arnaut
[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
[27 Sep 2023 11:51]
Laurynas Biveinis
This bug is still actual as of 8.0.34. Setting innodb_flush_log_at_trx_commit to 1 does not actually flush the redo log on commits when binlog is enabled, and this is not documented anywhere. It does seem like a specific design decision with binlog group commit (https://dev.mysql.com/worklog/task/?id=5223), to have binlog as the source of truth of what was committed and just roll forward prepared transactions in other SEs. Moreover, in 8.0, where InnoDB keeps the server DD too, it takes care to flush on commit all the DD transactions, in any configuration. Which is absolutely needed, because the DD is initialized after InnoDB but before binlog crash recovery, meaning that any committed-in-binlog but prepared-in-InnoDB DD transactions would be invisible otherwise. This would result in DD inconsistency with what's on disk. So at the very least, this is a documentation bug on several points: - innodb_flush_log_at_trx_commit = 1 isn't, when binlog is enabled. - Is innodb_flush_log_at_trx_commit = 0 & sync_binlog = 1 actually a fully durable configuration? - Is innodb_flush_log_at_trx_commit = 1 & sync_binlog = 0 actually a data-losing configuration? - If a server acknowledges a commit to client and then crashes, is it normal to see binlog XA crash recovery to roll forward to committed state that same transaction that was already acknowledged as committed?
[27 Sep 2023 11:55]
Laurynas Biveinis
Test for bug 75519
Attachment: bug75519-test.patch (application/octet-stream, text), 2.42 KiB.
[27 Sep 2023 11:59]
Laurynas Biveinis
I have uploaded a test to show InnoDB losing its commit and then being rolled forward by binlog. An error log from its run: ... Blocking log write & flush after committing LSN 19621273 Blocking log writer thread safe_process[95631]: Child process: 95632, killed by signal: 9 ... [InnoDB] Doing recovery: scanned up to log sequence number 19621265 <- the committed LSN is not there ... [Server] Recovering after a crash using binlog [Server] Starting XA crash recovery... [Server] Crash recovery finished in binlog engine. No attempts to commit, rollback or prepare any transactions. [InnoDB] Starting recovery for XA transactions... [InnoDB] Transaction 1554 in prepared state after recovery [InnoDB] Transaction contains changes to 1 rows [InnoDB] 1 transactions in prepared state after recovery [Server] Found 1 prepared transaction(s) in InnoDB [Server] Crash recovery finished in InnoDB engine. Successfully committed 1 internal transaction(s). [Server] XA crash recovery finished.