Innodb_flush_log_at_trx_commit and innodb table corruption possibiliy

MySQL & MariaDB Other MySQL® Questions
1

Hi

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 ):
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

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 InnoDB data. 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.

Any thoughts?

Regards

1 Like
2

@MaxSol
This setting corresponds to transactional log writes. I am not aware about possible data corruption related to setting innodb_flush_log_at_trx_commit = 2, but it is really possible to lose up to 1 sec in committed transactions.

1 Like