maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

(Sending again, as I don't think my original reply went to the list; 
apologies if I duplicate.)

Thank you both, for your helpful responses.

> Hello.
>
>> mariadb@xxxxxxxxxxxxxx writes:
>>
>>> I have four servers all running 10.3 as follows:
>>>
>>>     A <=> B => C => D
>>
>>> 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.%
>>
> ...
>> Yes. Using replicate_ignore_db is not appropriate for doing local 
changes on
>> one server that should be invisible to the replication chain. So 
this will
>> not work, as you suspect.
>>
>> The simplest way is to just set sql_log_bin=0 when doing local 
transactions
>> on a slave - this avoids the statements being written to the binlog 
in the
>> first place. No replicate_ignore_db options are needed then.
>>
>> It's possible you can achieve something similar using binlog_ignore_db
>> instead (I don't 100% recall all details, but from the documentation it
>> looks like it might work).
>
> Indeed.
>
> To this matter
>     CHANGE MASTER {DO,IGNORE}_DOMAIN_IDS
> could've been defined to block certain domain transaction from sending
> by master. But it works conformly to the replicate db rules.
>

I actually lied earlier about binlog_ignore_db (though not 
intentionally, was just looking in the wrong spot), it is in fact set to 
norep on all four servers. As I recall the problem we had when we set 
these up years ago was that it would only block the statements from the 
binlog if norep was the default database at the time the statement 
occurred. We rarely set norep as the default DB (just create directly 
via CREATE TABLE norep.someTable), thus the replicate_ignore and 
replicate_wild_ignore seemed to be the best alternative to make sure the 
statements didn't actually get applied on the slaves.

So, it would seem to make it work in my case I'll need to change some 
code after all.

>>
>> Your current setup is effectively multi-master from the point of view of
>> GTID (all servers written concurrently), even though you then
>> replicate_ignore_db changes from all but one server. As described in the
>> documentation, GTID can handle multi-master setups using 
gtid_domain_id, but
>> I think that is much more complicated than needed for your actual 
usecase.
>> Just using sql_log_bin=0 (or possibly binlog_ignore_db) should be fine.
>>
>>> 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?
>>
>> I vaguely recall an old bug that causes in particular redundant DROP
>> TEMPORARY TABLES statement to be unnecessarily written to the 
binlog. Maybe
>> this bug is still there and causing this.
>>
>
> This one must relate:
> MDEV-17863 DROP TEMPORARY TABLE creates a transaction in binary log 
on read only server.
>

Yah, it's at least similar if not the same. But I guess my question is, 
isn't the presence of these unwanted DROP TEMPORARY TABLE statements in 
and of itself enough to prevent the mistake-free possibility of 
redirecting a lower slave to a higher master at some point in the 
future? I.E., I want to point Slave D to Master B (because I've brought 
down Master C for maintenance) but doing so fails because Slave D 
currently happens to be pointing to a DROP TEMPORARY TABLE statement 
that was injected on Slave C? Does setting sql_log_bin=0 prior to the 
creation of the temporary table also prevent the creation of these DROP 
statements?

Thanks!

Dan