# Innodb\_flush\_log\_at\_trx\_commit and innodb table corruption possibiliy

**URL:** <https://forums.percona.com/t/innodb-flush-log-at-trx-commit-and-innodb-table-corruption-possibiliy/10143>\
**Category:** Other MySQL® Questions\
**Created:** [April 18, 2021, 5:27pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit-and-innodb-table-corruption-possibiliy/10143 "2021-04-18T17:27:43Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![MaxSol](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/maxsol/32/3361_2.png) [@MaxSol](https://forums.percona.com/u/MaxSol)\
**Post date:** [April 18, 2021, 5:27pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit-and-innodb-table-corruption-possibiliy/10143/1 "2021-04-18T17:27:43Z")

</div>

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](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.

> **[Best practices for configuring parameters for Amazon RDS for MySQL, part 1:...](https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/)**
>
> This blog post was last reviewed or updated May, 2022. With Amazon Relational Database Service (Amazon RDS) for MySQL, you can deploy scalable MySQL servers in minutes with cost-efficient and resizable hardware capacity. Amazon RDS frees you up to...

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

---

<div class="post-metadata">

**Author:** ![vadimtk](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadimtk/32/9528_2.png) [@vadimtk](https://forums.percona.com/u/vadimtk)\
**Post date:** [June 7, 2021, 2:43pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit-and-innodb-table-corruption-possibiliy/10143/2 "2021-06-07T14:43:40Z")

</div>

@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.