MySQL Bugs: #11825: Clarify whether innodb_flush_log_at_trx

Bug #11825	Clarify whether innodb_flush_log_at_trx_commit preserves crash-recovery
Submitted:	8 Jul 2005 21:10	Modified:	5 Aug 2005 21:43
Reporter:	Kristian Nielsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.0.8-beta-nightly-20050618	OS:	N/A
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Just a suggestion for a point in the reference manual which was
important, yet not clear to me when I read it.

It concerns the innodb_flush_log_at_trx_commit option as documented in
section 15.5, http://dev.mysql.com/doc/mysql/en/innodb-start.html. The
docs clearly state that setting the option != 1 may cause loss of
transactions in case of crash.

What is not clear from the docs is whether crash recovery is
affected. After a crash InnoDB applies the log since the latest
checkpoint to ensure a consistent tablespace. I believe this works
because changes to data blocks are not written until corresponding log
entries have been flushed to disk.

When I read the docs on innodb_flush_log_at_trx_commit, it wasn't clear
whether setting this option != 1 would interfere with the crash recovery
mechanism. This caused me to not want to use the option. I think it
would be great if the docs mentioned explicitly that crash recovery is
preserved in any case (as I think it is).

How to repeat:
Read section 15.5, http://dev.mysql.com/doc/mysql/en/innodb-start.html

Assigning this to Jan Lindström.

InnoDB's crash recovery does work regardless of the parameter value. Tables will not get corrupt in a crash.

Setting it to != 1 can only cause some last transactions to be lost.

Regards,

Heikki

InnoDB's crash recovery is not affected by the value but tables can be corrupted in
the worst case if operating system or hardware fools that disk flush has been done but in reality it has not been done.

Jans changes are confirmed as present in the refman, closing bug.