Sunday, October 19, 2008

An odd spot in relay slaves

relay slave

A relay slave is a slave that writes to its binary log the stream of commands received from the master, thus enabling other slaves to replicate.
This is often used to relieve the master from heavy traffic created by many slaves asking for binlog events.
A typical scenario is when a master has a few dozen slaves, and instead of dealing with all of them directly, uses four relay slaves, each one dealing with 6 slaves.
So, where's the trick? The trouble comes if you change replication format after you start the slave.
Example. One master (M), two relay slaves (R1, R2), with four slaves each (S1,S2,S3,S4, S5, S6, S7, S8).
You started working in MIXED mode, which was set in the option file of each server.
Now you want to switch to STATEMENT mode for a while.
Then, you connect to each of the 11 servers, one by one, with a script that sends to each one the command SET GLOBAL binlog_format=STATEMENT.
You close all open connections from your clients, and before issuing your command, you again connect to all servers, and run SELECT @@binlog_format to make sure that all servers have the same setup.
Your special query is a tracking command, which inserts @@server_id in a table, and in each server the table should have the server_id of its host;
However, you notice that the propagation happens until the relay slaves. All the leaf nodes have the same values of their master.
What happened? It's something that is somehow documented, but results in an unexpected result. A SET GLOBAL statement is valid for every new connection. Meaning that, if you have an open connection, the global behavior doesn't apply. In our example, we closed all client connections. But there was one hidden connection that was not closed.
The slave SQL thread was already running, and was thus unaffected by the change.
To make the change stick, and thus propagate to all the slaves, we need to start and stop the slave.
I open Bug#40106 about this behavior because, although it's is logically explainable, it goes against the user's expectation. (If I change the binlog format, I expect to affect the binary log. Stopping the slave does not occur naturally. Perhaps this is a question for the certification exam).


arjen said...

Good catch Giuseppe!
Indeed, many people wouldn't realise (or care) that a slave thread is essentially a client/connection itself.

So do you know of a good way to close all regular client connections?

Sheeri K. Cabral said...

Actually more and more people are using persistent connections in Java, so it's more of an issue than with replication.

Although it's more likely to be caught by an application than by replication.

This is why sync checks need to happen regularly -- Pythian has started to do regular sync checks with masters and slaves.

Vote on Planet MySQL