I am curious to find out if innodb_flush_log_at_trx_commit = 2 can cause a data / table / innodb corruption during the crash?
I know that this option could lead to a data loss of plus-minus 1 second.
It’s fine for me.
According to official MySQL docs this option looks “safe” ( defined as no corruption can occur, just data loss ):
However, I started to doubt after reading some Best Practices regarding MySQL RDS on AWS, there is a remark:
Data loss also refers here to any potential loss of data, not just transactions. Thus, this setting can be a source of potential corruption.
P.S Let’s exclude the case when SSD or OS “fool” MySQL, i.e. see “Caution” section from the MySQL docs above:
- Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell [ mysqld ] that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt
InnoDBdata. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.