The Scenario

The Failover via onChange handler in case of master failure is problematic in some scenarios. I will describe a scenario in which the Failover via onChange handler will cause data consistencies between the master and slaves with the current design. Let's assume the setup is 3 containers: C1, C2, and C3. C1 is the master with both C2 and C3 as the slaves of it. As per the documentation of the autopilot pattern for MySQL, we are using GTID based replication.

Assume that C1 crashes for some reason (I don't care why), and is not flapping nor easily recoverable. At the time of the crash C1 has written transactions 1-1000 (GTID based). Due to some issues with IO latency or whatever, both of the slaves C2 and C3 are lagging in replication. As compared to the more common scenario of slave lag due to SQL_THREAD slowness, this slave lag is caused by the IO_THREAD slowness. The slave C2 has only transactions 1-995 from the master, C1, in its relay logs and applied. The slave C3, which may have had more lag, has only transactions 1-990 from the master, C2, in its relay logs and applied.

So at this point we have to promote C2 or C3 to the new master and make the other one the slave. No matter which slave we promote to the new master (C2 or C3), it is obvious that transactions 996-1000 from the master is already lost. This is unavoidable in the current design and I will skip this part.

So according to the talk at Velocity, my reading of the documentation, and code, what happens is now that both slaves (C2 and C3) will try to become the new master by writing the lock in Consul. The other slave (the one that didn’t become the new master) will change its replication to point to the new master. The problem comes if C3 somehow wins and becomes the new master. Below I'll describe the two cases and the repressions.

C2 Becomes The Master

Since the slave C2 (with transactions 1-995 from the old master C1) has more transactions from the old master C1 than the slave C3 (with transactions 1-990 from the old master C1), it should logically be promoted to the new master. When C3 is made a slave of C2, C3 checks to see if it is missing any transactions relative to the new master C2 and in fact is missing transactions 991-995 from the old master C1. C2 finds the relevant transactions, 991-995 from the old master C1, in its binlogs and sends it to C3 to be applied. At this point in time data in C2 and C3 are in sync. Everything is good and the application is happy.

C3 Becomes The Master

When C2 is made a slave of C3, C2 checks to see if it is missing any transaction relative to the new master C3 and the answer is no. C2 has all the transactions that C3 has so replication says everything is fine. The fact that C2 has more transactions (transactions 991-995 from the old master C1) than the new master C3 does not matter to replication. At this point the data between the new master, C3, and the slave, C2, is inconsistent. The changes from transactions 991-995 from the old master, C1, is on C2 but not C3. I have successfully prototyped this scenario in Vagrant.

How To Avoid This

Off the top of my head, I can come up with two ways to avoid this.

Global Coordination To Promote Master

When the master dies, we require global coordination to promote the the slave. The global coordinator, whatever form it takes, has to examine all the slaves and choose the one that is most ahead (eg. has the most transaction from the dead master) and promote it as the master. The remaining servers become slaves on the new master. This is pretty much scenario 'C2 Becomes The Master' above.

Rebuild All Slaves Upon Master Promotion

Once a master fails, all slaves tries to become the new master using the process as described. Once a slave becomes the new master, we immediately rebuild all the other slaves using a backup of the new master. This ensures that we start everything in a consistent state. However the issue is that it can cause more data loss. If we use this logic in the 'C3 Becomes The Master' scenario above, then transactions 991-995 from the old master (C1) becomes unnecessary lost as well.