← Back to team overview

maria-discuss team mailing list archive

gtid and current_pos vs slave_pos


I have four servers all running 10.3 as follows:

   A <=> B => C => D

where server_ids are:

   A => 301
   B => 302
   C => 303
   D => 304

I.E., A is a master to B, B is a master to A, B is also a master to C, and C is a master to D. In addition to their actual replicating DBs, all four servers also have a "norep" DB that is used to create temporary tables for local report processing (as well as any other possible writes we might want to make locally without affecting the slave chain). Historically we've prevented replication for the norep DB via:

    replicate_ignore_db = mysql,norep
    replicate_wild_ignore_table = mysql.%,norep.%

Which is set as such on all four servers (the replicate_do and binlog_do/ignore flavor of filters are all unset). Writes to the A and B servers are programmatically controlled such that only one of the two servers will accept writes at any given moment.

Server D writes binlogs for potential backup/disaster recovery, but has no attached slave.

All that in mind, looking at the binary logs on D, I see this statement:

# at 51363132
#190423 9:07:51 server id 302 end_log_pos 51363170 GTID 1-302-47886064 ddl
/*!100001 SET @@session.server_id=302*//*!*/;
/*!100001 SET @@session.gtid_seq_no=47886064*//*!*/;
# at 51363170
#190423 9:07:51 server id 302 end_log_pos 51363324 Query thread_id=345449 exec_time=1800 error_code=0
SET TIMESTAMP=1556024871/*!*/;
SET @@session.pseudo_thread_id=345449/*!*/;
DROP TEMPORARY TABLE IF EXISTS `norep`.`locations` /* generated by server */

How is it that that statement made it all the way through to server D from B? Shouldn't it have been filtered out by server C?

Next, I'm reading up on gtid-based replication with the thought that I would love to be able to do things like bring down C for maintenance and temporarily point D to B. I read the docs here:


however, in looking at the actual binary logs of the servers they don't seem to reflect the explanations that I read there especially in regards to gtid_current_pos vs. gtid_slave_pos.

Specifically, when I look at the gtid_slave_pos on server D, which I thought was only supposed to reflect transactions that were actually replicated, I sometimes see statements coming from server C; these are temporary tables being written into norep on C. They are not actually replicating on D (at least as far as I can tell), and they don't show up D's binary log. So why would they be reflected in D's gtid_slave_pos?

For example, just a moment ago SELECT @@GLOBAL.gtid_slave_pos on D showed this:


This transaction does not appear in D's binlog, which I would expect since it should not in fact actually be replicated. But because it is reflected in gtid_slave_pos, it seems to me that in my setup I cannot reliably use gtid_current_pos or gtid_slave_pos, since either may at any given time point to an entry on C that of course won't exist on B should I ever want to redirect D to B.

Am I missing or mis-understanding something here?



Follow ups