Repair GTID from Restore with sql_log_bin=0 and then drop table

Question

I have been experimenting with mysql innodb cluster. One of the errors i've come across I don't know how to remedy or why exactly it causes a permanent death to any slave that wishes to catch up to the master - let me explain.

Create 3 instances of mysql. Use the innodb cluster utility to combine them into a cluster. You can use the AdminAPI to make sure the cluster is good. (use Single Primary mode so only 1 writer)

In the master mysql use mysql.exe to restore a dump of any table that has sql_log_bin = 0;

You'll notice that this causes that table not to replicate to the other database instances. Now if you drop the table the other slaves die and can never be caught up to the master.

You can even create mysql instance from scratch and point it to the master and it will never catch up. It will say recovering and then it will fail.

How do you repair in the case of this scenario? And, what exactly is going on that a slave even from scratch can't be synced after you do this?

Answer 1

The issue is that you have this blob of data that was no way of being replicated. When you put it on the master and it set the gtid purged variable it basically says: I have this data, but I can't give it to you.

What you can do here is to use this dump to provision the slave/secondaries servers. So you can execute this update in the master, but when you add a new member before it joins you must also execute the dump there. Then they both can say: we have this lump of data we cannot replicate so we are in sync. Then the joiner can fetch whatever it is missing from the primary .