MySQL Bugs: #112078: redo log is never flushed

Bug #112078	redo log is never flushed
Submitted:	15 Aug 2023 23:04	Modified:	19 Dec 2023 21:30
Reporter:	Vladislav Vaintroub	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Even if  innodb_flush_log_at_trx_commit=1, the redo log file is never flushed on Windows. That breaks the durability promise, the client must not get an OK from an update/insert/delete, until the data is durably stored.  The redo log is also cached in OS, thus an OS crash can easily lose the data, even with battery-backed write cache.

How to repeat:
Start mysqld with --skip-log-bin. It is not strictly necessary, but will better illustrate the issue

> create table t(i int);
# wait a couple of second until all writes are finished

Now, start recording mysqld.exe's filesystem and TCP activity with procmon
(https://learn.microsoft.com/en-us/sysinternals/downloads/procmon)

> insert  into t(i) values(1);

Stop recording, look into the procmon output.

1. There is a TCPSend event, *before any file is flushed by mysqld*.
   Which means server sends update confirmation (OK packet) via MySQL protocol
   before data is persistent.

2. There is no flush for the redo log, ever, not even once per second.
   (once-per-second is usually done for innodb_flush_log_at_trx_commit=0|2)

procmon log file

Attachment: insert2.PML (application/octet-stream, text), 2.66 MiB.

Hi Mr. Vaintroub,

Thank you for your bug report.

This is a known problem with Windows OS, but you have provided the `procmon` output that may come handy.

This is now a verified bug report for the Windows platform only.

Posted by developer:
 
Fixed as of the MySQL Server 8.0.35 and 8.2.0 releases, and here's the proposed changelog entry from the documentation team:

On Windows, the redo log file was not flushed even with
innodb_flush_log_at_trx_commit=1.

Thank you for the bug report.