Bug #92109 Please make replication crash safe with GITD and less durable setting (bis).
Submitted: 21 Aug 17:53 Modified: 24 Aug 16:29
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[21 Aug 17:53] Jean-François Gagné
Description:
Hi,

(This is similar to Bug#70659, but the solution suggested here only applies to 5.7 and 8.0.)

In verified Bug#70659, it is indicated that MySQL 5.6 is not replication crash safe with GTID and less durable settings (sync_binlog != 1 and/or and innodb_flush_log_at_trx_commit != 1).  In short (see the bug for reference) and after an OS (Linux) crash, there is a risk that InnoDB and the binary logs are out of sync.  As replication will be restarted according to the GTID state of the slave (in MySQL 5.6, this state is derived from the binary logs), resuming replication risks skipping transactions or running transactions in double.

MySQL 5.7 introduced the mysql.gtid_executed table for allowing running a slave with GTID, without binary logs or without log-slave-updates.  The page [1] from the manual describes how this table is updated and can be summarised to:

1) If binary logging is disabled (log_bin is OFF), or if log_slave_updates is disabled, the server stores the GTID belonging to each transaction together with the transaction in the table. 

2) If binary logging is enabled (log_bin is ON), whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log into the mysql.gtid_executed table.

[1]: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-...

Note that for fixing Bug#75393 (documentation bug), 2) should read "If binary logging is enabled (log_bin is ON) and log_slave_updates is enabled, [...]".

The behavior describe in [1] is storing the GTID state of the slave in a correct and crash persisitent way if binary logs are disabled or log_slave_updates is disabled.  So in this case, replication is crash safe even with GTID and less durable settings (if relay_log_info is in a table and relay_log_recovey is enabled).

It looks like it would be so simple to make replication crash safe with GITD and less durable setting by updating the mysql.gtid_executed table after each transactions, even if binary logging and log_slave_updates are enabled.  After all, something very similar is already done for the mysql.slave_relay_log_info.  So the additional cost would be relatively small.  If this cost wants to be avoided, maybe updating the table only when sync_binlog != 1 and/or innodb_flush_log_at_trx_commit != 1 could be done.  Also, if this cost needs absolutely to be avoided, maybe a configuration option could be added to enable/disable updating the table after each transaction when binary logging and log_slave_updates are enabled.

Note: updating mysql.gtid_executed would not solve the problem that the binary logs might be out of sync with the database.  It would only allow to resume replication in a crash safe way.  As described by Yoshinori in Bug#70659, the binary logs can still be ahead or behind InnoDB and someone setting less durable setting needs to understand that.  Yoshinori's idea to truncate the binary logs if they are ahead is still interesting, but it is out of the scope of this bug report.  Also, Yoshinori's idea to store GTIDs in the InnoDB Redo logs is interesting for avoiding loosing the GTID position of a master after a crash and allowing re-slaving it to a newly promoted master, but it is also out of the scope of this bug report.  I will open other bugs about this and put the link in the comments.

Many thanks for looking into that and allowing us run replication with GTID and less durable settings without worrying about slave crashes,

JFG

How to repeat:
Crash a slave with GTID and with less durable setting.  See Bug#70659 for more details.

Suggested fix:
Update the mysql.gtid_executed table after each transactions to avoid loosing InnoDB GTID state.
[24 Aug 14:17] Bogdan Kecman
Hi,
I'm switching this to "Feature request" as IMO behavior now is not a bug and as for the suggested changes, I'll let replication team decide how to go forward.

Thanks!
Bogdan
[24 Aug 16:29] Jean-François Gagné
Hi Bogdan, thanks for verifying.

Allow myself to try changing your mind: I actually think this is a bug.

Replication not being crash safe is causing data corruption, and data consistency is not something that can be a "feature" of a database.

Also, Bug#70659 is flagged as a bug (not a feature request), so I also think this should be a bug.

So I am setting it back to S2, sorry for fighting you on this.  If you still think it is a feature request and set it back to S4, I will not fight back.  :-)

Cheers, JFG
[24 Aug 17:06] Bogdan Kecman
Hi,
Well I'm torn to be honest. If the 70659 did not exist I'd set this as bug, if those doc one did not exist same thing but with them all classified as bug I'm looking at this one as "potential way to change behavior" (that solves it) rather then final solution.. but in any way the classification is not too important as verifying it makes it go to replication team and then they make decision themselves how to proceed :)

all best
Bogdan