2.) When a temporary table creation statement does find it's way to a
downstream server, it seems to at least partially remain in effect
even after a STOP SLAVE; START SLAVE; on the replica. I had thought
Yes, the temporary tables on the slave remain across stop/start slave;
this
is necessary since otherwise stopping the slave temporarily could easily
break replication if temporary tables are used (in statement/mixed mode).
Of course temporary tables are lost if the server is restarted, so in
that
case replication _can_ break. Temporary tables are unfortunately rather
fragile when not using ROW mode in MariaDB prior to 12.0.
On Server B, the new binary log looks mostly as expected. The only
questionable point is that the "CREATE TEMPORARY TABLE tempDb1.t2 ..."
statement DOES go to the binlog. This seems to contradict the docs
Yes. This is a bug (read-only mode should have prevented that). And it
means
replication is now broken (in your test) when you switch Server C to
replicate from A, as C now has a dangling temporary table that causes
trouble, as you describe.
On Server C, the binary logs show that not only do the "UPDATE ...
LIMIT" statements get written as ROW, but also the two statements
"UPDATE tempDb1.t1 SET f1 = 6;" and "UPDATE tempDb1.t1 SET f1 = 9;".
sent those statements through. Interestingly, the "UPDATE tempDb1.t1
SET f1 = 7;" DOES get written as statement based on Server C's binary
log; as if it knows the temporary table is no longer in the mix but
nonetheless continues writing the later statements as ROW based
following the next "UPDATE ... LIMIT" statement.
Yes. I think restarting the slave on C means that the replication
thread is
now in the state "I have temporary tables, but did not yet binlog
anything
in ROW mode", so it can use statement mode when appropriate. Then once it
gets a row event from A, it goes to the state "I have temporary tables
and
already binlogged in row mode", so from then on everything becomes row.
This becomes worse because of the bug that causes C to have a dangling
temporary table lingering indefinitely.
This is somewhat unfortunate behaviour. As I said, temporary tables in
statement/mixed mode have been rather fragile for (too) long.
From 12.0.1 this is improved:
https://jira.mariadb.org/browse/MDEV-36099
This should prevent a thread (such as the replication thread in C) from
having to binlog _everything_ in row mode just because there exists a
temporary table. And more, it will by default avoid binlogging temporary
tables at all in MIXED mode (using ROW mode instead for those statements
that referenced the temporary tables on the master).
As a work around we're just being more diligent about when/how we use
temporary tables (including taking steps to make sure they aren't
replicated at all). Mostly I'm just looking for some clarification as
Yes, sorry for the trouble :-(
As the problem is with CREATE TEMPORARY TABLE ... AS SELECT ... in
particular, one work-around is to instead use the two-step approach
CREATE
TEMPORARY TABLE ... ; INSERT INTO ... SELECT, which doesn't seem to have
the bug in my test. This depends on whether you have the ability to
change
the application like this.
Another possible work-around is to switch temporarily to ROW mode when
using
temporary tables on a read-only slave; then nothing will be binlogged. If
you cannot easily add 'SET binlog_format=ROW' to the application, then
maybe
putting this command into --init-connect on the read-only slave could
work
(this would not affect slave threads). Or set binlog_format=ROW
globally of
course, but then the slave would convert everything to row in its own
binlog, which is perhaps not desired.
