MySQL Bugs: #70659: Make crash safe slave work with gtid + less durable settings

Bug #70659	Make crash safe slave work with gtid + less durable settings
Submitted:	18 Oct 2013 18:31	Modified:	21 Oct 2013 13:23
Reporter:	Yoshinori Matsunobu (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using GTID with less durable settings (sync_binlog != 1 and innodb_flush_log_at_trx_commit != 1), after crash recovery on a slave, the following problems may happen.

1) The slave loses transactions if slave's binary log is ahead innodb (binary log has N transactions that are not persisted in InnoDB => N transactions are lost)

2) The slave stops with duplicate key error if binary log is behind innodb (InnoDB has N transactions that are not persisted in binary log)

Without gtid, both cases do not cause problems because slave can continue replication based on slave_relay_log_info InnoDB table.

How to repeat:
Set master/slave replication with gtid enabled, and set less durable settings.

sync-binlog=0
innodb-flush-log-at-trx-commit=0
log-bin
log-slave-updates
gtid-mode=on
enforce-gtid-consistency
relay_log_info_repository=table
relay_log_recovery=1

master> create table t1 (id int primary key) engine=innodb;
master> insert into t1 values (1);

1) Losing transaction

Attach slave's mysqld process via gdb, then set breakpoint on trx_commit_complete_for_mysql function

master> insert into t1 values (2);
-- hit breakpoint on the slave
-- binlog on the slave should have "insert into t1 values (2)"

kill -9 slave's mysqld

restart slave mysqld, restart replication

master> insert into t1 values (3);

slave has only 1 and 3. It misses 2.

2) Duplicate key error

slave> select @@global.gtid_executed; --> x

copy slave's binary log somewhere --> (a)

master> insert into t1 values (2,2,2);

slave> select @@global.gtid_executed; --> y

kill -9 slave's mysqld_safe and mysqld

restore slave's binary log taken at (a) (simulating "transactions are persisted in InnoDB but not in binary log")

restarting slave, restarting replication
=> slave stops with duplicate key error

select @@global.gtid_executed; => pointing x

so slave tries to start replication from gtid x, so apparently it fails by duplicate key error.

Suggested fix:
1) Avoiding data loss
Binary log and InnoDB have to be consistent each other. InnoDB prints binary log file and position at crash recovery. This is trustful when using InnoDB only. 
---
Example:
InnoDB: Last MySQL binlog file position 0 974, file name binary.000019
---
Truncating binary log based on the above position makes binary log and InnoDB consistent each other. Then the slave can start replication from the correct position.

2) Avoiding duplicate key error
To prevent this problem, slave should start replication from positions/gtid based on InnoDB, not on binary log. To make it work, InnoDB should have GTID information somewhere. Then we can set the valid gtid by SET gtid_next before starting replication.

Hello Yoshinori,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

Regarding the suggested fix:

"Truncating binary log based on the above position makes binary log and InnoDB consistent each other."

It won't work well if this slave serves as a master of its own slave because the transactions in binary log may have already replicated. I would change it skip writing to the binary for transactions already recorded there.

Related with . suggested fix for MySQL 5.7 and 8.0: Bug#92109.

While doing tests for Bug#101874, I tested replication crash safety with GTID, sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2 in a recent MySQL 8.0, and I did not get replication breakage.  I suspect that replication crash safety with GTID and less durable settings was introduced with WL#9211 in MySQL 8.0.17.  Could we get an official statement from Oracle on this subject ?

In Bug#92109, Joe Grasse shared the following:

> It looks like that [I suspect that replication crash safety with GTID and less durable settings was introduced with WL#9211 in MySQL 8.0.17.] is true, based on this section of the manual.
>
> If binary logging is enabled (log_bin is ON), from MySQL 8.0.17 for the InnoDB storage engine only, the server updates the mysql.gtid_executed table in the same way as when binary logging or replica update logging is disabled, storing the GTID for each transaction at transaction commit time. 
>
> https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html

So it looks like this bug is fixed in MySQL 8.0.17.  In Bug#92109, I asked for this fix to be back-ported in MySQL 5.7.