Got fatal error 1236 from master when reading data from binary log

Question

I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ?

Background

Needed to perform a host updates on master host. Hence promoting one of the slave as new master and restore replication across all slaves without missing any transactions

Expected: Since replication is current, failover should be quick and have a minimum read write downtime.

• Lock current master to RO, flush everything in buffer, pending (which appears to be imperfect)
• Make sure all salves replicated everything
• Flip master
• Point all slaves to new master and restore replication

Technical details

MySQL 5.6, one master(RW) and 25 slaves(RO). All slaves current. No network issues, all of them in same region connected over LAN.

my.cnf

# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Connection pool, C3PO. If the MySQL is in RO mode C3PO would not setup any new connection. churns pool till DB cnames resolves to a RW enabled MySQL host. However, there could be existing connections to the old master.

Operational details

To flip the database we perform the below operations sequentially

1. execute sql statement through python - FLUSH TABLES WITH READ LOCK; set @@global.read_only = ON
2. Wait till DB is in RO mode by querying select @@global.read_only
3. Sleep 5 sec to make sure we give time for replication to catch up
4. Pick one slave to promote, make sure GTID of slave (get slave status) is subset of the master GTID executed (get master status)
5. On new master execute stop slave, reset slave all, set @@global.read_only = OFF, UNLOCK TABLES
6. Update DNS of master DB cname, sleep 90 sec for DNS to update cache on hosts
7. on each slave run below commands

stop slave
CHANGE MASTER TO master_host=<> master_port=<> master_sll_path=<> MASTER_AUTO_POSITION=1, ..
start slave

Problem

At times replication would be current and would undergo smooth failover, apart from old master (where we restore through mysql dump). However, sometimes slaves run into the issue of broken replication reporting missing transactions and only approach is to restore through Mysql dump or set the GTID position purged to latest one from new master. Both of them sound like missing transactions

error 1236, Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting 
using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs 
that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from
backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing 
purged transactions are too long to print in this message. For more information, please see the master's
error log or the manual for GTID_SUBTRACT.'

Theories

Only possible situation I can think of is old master had some transactions which were not send to slave which is recently promoted to master. These transactions came in after promoting new master and got replicated to all slaves which are connected to old master (till script was sleeping or 90 sec) and recent GTID is not available on new master leading to conclusion that GTID's were purged.

Statements contradicting above theory

• Database is in RO mode, hence C3PO can't grab new connections and write to DB
• Even the existing connections can't write to DB since DB is in RO mode and we issued FLUSH TABLES WITH READ LOCK. However, we didn't give enough time for existing transactions to complete ? i.e we didn't wait for flush tables command to complete ? (suspicion)
• innodb_flush_log_at_trx_commit = 1 which ensures ACID. Hence there is no chance of new transactions pending in buffer which are about to be written to DB and logs.

Not sure anything that can cause broken replication. However, I see a post FLUSH TABLES WITH READ LOCK will not halt writes to InnoDB. unsure why can DB have more transactions while it is in RO mode. Should I do flush tables and Flush Logs as well ? that would be flying blue unsure if it would fix it and even if it does leaves me with uncertainity. Any thoughts from MySQL experts are appreciated.

Fall back approach is to revoke replication for all slaves on old master but even that would lead to missing transactions not being replicated which is the worry here.