Thanks for your response!
On 5/13/2025 4:36 PM, Kristian Nielsen wrote:
mariadb--- via discuss <discuss@lists.mariadb.org> writes:
Following up on this, as I've found the culprit here, which has to do
with how temporary table statements get replicated. But as usual that
means more questions to try and get all the way to the bottom of it.
Thanks for taking the time to test this so carefully and for the detailed
writeup.
On server B:
CREATE TEMPORARY TABLE tempDb1.t2 AS ( SELECT f1 FROM tempDb1.t1 );
It seems that this behaviour is specific to CREATE TEMPORARY TABLE ... AS
SELECT...
I can reproduce as you describe with this statement.
However, if I replace this statement with this (seemingly equivalent):
CREATE TEMPORARY TABLE tempDb1.t2 (f1 INT);
INSERT INTO tempDb1.t2 SELECT f1 FROM tempDb1.t1;
Then the behaviour is as expected and documented: the temporary table is
_not_ written to the binary log.
I think this is a bug, CREATE TEMPORARY ... SELECT should behave as other
temporary table operations in read-only mode and not binlog. Another hint
that this is a bug is that putting the CREATE TEMPORARY ... SELECT within
BEGIN/COMMIT actually gives an error:
BEGIN;
CREATE TEMPORARY TABLE ttmp2 AS ( SELECT a FROM t1 );
COMMIT;
mysqltest: At line 29: query 'COMMIT' failed: ER_OPTION_PREVENTS_STATEMENT
(1290): The MariaDB server is running with the --read-only option so it cannot
execute this statement
I can file it as a bug on jira.mariadb.org, referencing your excellent
writeup. Or you can report it yourself, if you prefer.
Perfect, thanks for the confirmation. I'll write it up.
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.
Awesome, thanks for all the options. For now I think we're ok with a
little extra monitoring of the binlogs and diligence with the users when
creating temp tables in ways such that they don't replicate at all (we
have a DB that is set to not replicate via binlog-ignore-db,
replicate-ignore-db, etc).
Cheers!
Dan
Hope this helps,
- Kristian.
_______________________________________________
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org
