MySQL Bugs: #71550: Value 0 for innodb_flush_log_at

Bug #71550	Value 0 for innodb_flush_log_at_timeout is not valid
Submitted:	1 Feb 2014 15:55	Modified:	22 Oct 2014 15:04
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	innodb_flush_log_at_timeout, missing manual

Description:
MySQL 5.6 allows to set new server variable, innodb_flush_log_at_timeout, to any integer value from 0 to 2700 (see http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_t...). Manual does not explain, though, what will happen if this value is set to 0:

mysql> set global innodb_flush_log_at_timeout=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_flush_%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 0     |
| innodb_flush_log_at_trx_commit | 1     |
| innodb_flush_method            |       |
| innodb_flush_neighbors         | 2     |
| innodb_flushing_avg_loops      | 1     |
+--------------------------------+-------+
5 rows in set (0.00 sec)

Will there be any checks for timeouts? Or flush will happen at every check (and then how often this check is performed)? 

This is important to know in cases when innodb_flush_log_at_trx_commit != 1, as it defines how many changes may be lost in case of crash.

How to repeat:
Try to find out in the manual what happens when innodb_flush_log_at_timeout=0.

Suggested fix:
Read source code:

/********************************************************************//**
The master thread is tasked to ensure that flush of log file happens
once every second in the background. This is to ensure that not more
than one second of trxs are lost in case of crash when
innodb_flush_logs_at_trx_commit != 1 */
static
void
srv_sync_log_buffer_in_background(void)
/*===================================*/
{
        time_t  current_time = time(NULL);

        srv_main_thread_op_info = "flushing log";
        if (difftime(current_time, srv_last_log_flush_time)
            >= srv_flush_log_at_timeout) {
                log_buffer_sync_in_background(TRUE);
                srv_last_log_flush_time = current_time;
                srv_log_writes_and_flush++;
        }
}

and document based on that and assumption that srv_flush_log_at_timeout=0.

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

The documented range for innodb_flush_log_at_timeout has been changed from "0 to 2700" to "1 to 2700". 

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_t...

The revised content should appear soon, with the next published documentation build.

I have adjusted the bug synopsis and reclassified the bug as a code bug. Setting innodb_flush_log_at_timeout=0 should not be permitted.

Thank you for the bug report.

Posted by developer:
 
The documentation has been revised, as noted above.

Thank you for the bug report.