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.