10.11.8 writing all binary logs in row format (even though format is MIXED)
Doing a little fishing here ... Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED? This replica has been running for quite some time, though admittedly I had to restore it from a backup about 3 weeks ago as the result of a server crash. But it had been writing data fine over those three weeks (and prior); starting on March 2nd it started writing all of its entries in ROW format. I didn't notice until it filled the disk this morning. I can't think of anything that may have changed at that time. Normally a few entries will get written as ROW but most are definitely MIXED in normal operation. I stopped/restarted it this morning and it's writing in MIXED again, as expected. I also checked the upstream primary, it is _not_ writing its records as ROW. I.E., my replica is (apparently) not getting them that way from upstream. Both are 10.11.8. Any ideas? TIA, Dan
mariadb--- via discuss <discuss@lists.mariadb.org> writes:
Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED?
I stopped/restarted it this morning and it's writing in MIXED again, as expected.
This suggests that binlog format was set to ROW at some point, and that the slave SQL thread had not been restarted since it was set back to statement. Ie. the following scenario: SET GLOBAL binlog_format=ROW; STOP SLAVE; START SLAVE; SET GLOBAL binlog_format=MIXED; Like other variables, SET GLOBAL only takes effect for new sessions (or newly started slave threads). Thus, this would explain why stop/restart made it go back to MIXED.
Any ideas?
This would require that the binlog format had been set to ROW temporarily sometimes before the last slave restart, and back to MIXED after, which is not known to have happened from what you wrote; but it seems a likely explanation. - Kristian.
Thank you for your response! The problem has happened again today, this time to one of the upstream servers. First (most important) question: One of the replicas filled the disk (again). It's the IO thread that's hung: +--------+-------------+-----------+--------------+--------------+-------+--------------------------------------------------------+-----------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+-------------+-----------+--------------+--------------+-------+--------------------------------------------------------+-----------------------+----------+ | 5 | system user | | NULL | Slave_IO | 93846 | Waiting for someone to free space | NULL | 0.000 | | 7 | system user | | NULL | Slave_worker | 6688 | Commit | NULL | 0.000 | | 8 | system user | | NULL | Slave_worker | 6688 | Waiting for someone to free space | NULL | 0.000 | | 6 | system user | | NULL | Slave_SQL | 6987 | Slave has read all relay log; waiting for more updates | NULL | 0.000 | I've cleared space (at least enough that it should process for a few minutes; just trying to get it responsive so I can restart it gracefully), but the thread won't budge. Oddly enough, PURGE BINARY LOGS TO ... appeared to work in that it cleared older logs I didn't need; but it (the command) did not return properly. It too was hanging and I had to 'CTRL-C' it. I also tried STOP SLAVE SQL_THREAD (with the intention of following with STOP SLAVE IO_THREAD) but it too hung. Tried CTRL-C there, as well, but it's still showing in the process list as "KILLED". Is there anything I can do to get the slave thread to be responsive again short of force shutting down the DB? Should I try STOP SLAVE IO_THREAD? As far as the binlog format issue: I have a production primary DB (A) which feeds two replicas (let's call them B and C). I.E., both B and C are directly connected to and replicating from A. Other servers then replication from C. The interesting thing here is that the problem showed up this time on both B and C; they both were exhibiting the behavior of where everything they wrote into their logs was in ROW format. I checked the global BINLOG_FORMAT and it was "MIXED" as I expected (on both B and C). Is there any way to see what the BINLOG_FORMAT is of the slave threads themselves while they are still running? B was not out of space, so I was able to restart the slave there and I'm waiting to see if the binary logs are still problematic. C is the one that is still hung after clearing space. And in all of this I should say no, we don't have anything (at least that I can find) that is changing the BINLOG_FORMAT directly. Indeed, server B is AWS RDS, and I don't think anything has access to change it without shutting down the server anyway (which I know has not happened). Thank you! Dan On 3/5/2025 8:08 AM, Kristian Nielsen wrote:
mariadb--- via discuss <discuss@lists.mariadb.org> writes:
Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED?
I stopped/restarted it this morning and it's writing in MIXED again, as expected.
This suggests that binlog format was set to ROW at some point, and that the slave SQL thread had not been restarted since it was set back to statement.
Ie. the following scenario:
SET GLOBAL binlog_format=ROW; STOP SLAVE; START SLAVE; SET GLOBAL binlog_format=MIXED;
Like other variables, SET GLOBAL only takes effect for new sessions (or newly started slave threads). Thus, this would explain why stop/restart made it go back to MIXED.
Any ideas?
This would require that the binlog format had been set to ROW temporarily sometimes before the last slave restart, and back to MIXED after, which is not known to have happened from what you wrote; but it seems a likely explanation.
- Kristian.
Looking at that processlist, it does look like it's one of the SQL threads that's hung (on Commit). But still stumped, don't know how to get to move along. On 4/3/2025 3:24 PM, mariadb--- via discuss wrote:
Thank you for your response!
The problem has happened again today, this time to one of the upstream servers.
First (most important) question: One of the replicas filled the disk (again). It's the IO thread that's hung:
+--------+-------------+-----------+--------------+-------------- +-------+-------------------------------------------------------- +-----------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+-------------+-----------+--------------+-------------- +-------+-------------------------------------------------------- +-----------------------+----------+ | 5 | system user | | NULL | Slave_IO | 93846 | Waiting for someone to free space | NULL | 0.000 | | 7 | system user | | NULL | Slave_worker | 6688 | Commit | NULL | 0.000 | | 8 | system user | | NULL | Slave_worker | 6688 | Waiting for someone to free space | NULL | 0.000 | | 6 | system user | | NULL | Slave_SQL | 6987 | Slave has read all relay log; waiting for more updates | NULL | 0.000 |
I've cleared space (at least enough that it should process for a few minutes; just trying to get it responsive so I can restart it gracefully), but the thread won't budge. Oddly enough, PURGE BINARY LOGS TO ... appeared to work in that it cleared older logs I didn't need; but it (the command) did not return properly. It too was hanging and I had to 'CTRL-C' it. I also tried STOP SLAVE SQL_THREAD (with the intention of following with STOP SLAVE IO_THREAD) but it too hung. Tried CTRL-C there, as well, but it's still showing in the process list as "KILLED".
Is there anything I can do to get the slave thread to be responsive again short of force shutting down the DB? Should I try STOP SLAVE IO_THREAD?
As far as the binlog format issue:
I have a production primary DB (A) which feeds two replicas (let's call them B and C). I.E., both B and C are directly connected to and replicating from A. Other servers then replication from C. The interesting thing here is that the problem showed up this time on both B and C; they both were exhibiting the behavior of where everything they wrote into their logs was in ROW format. I checked the global BINLOG_FORMAT and it was "MIXED" as I expected (on both B and C). Is there any way to see what the BINLOG_FORMAT is of the slave threads themselves while they are still running? B was not out of space, so I was able to restart the slave there and I'm waiting to see if the binary logs are still problematic. C is the one that is still hung after clearing space.
And in all of this I should say no, we don't have anything (at least that I can find) that is changing the BINLOG_FORMAT directly. Indeed, server B is AWS RDS, and I don't think anything has access to change it without shutting down the server anyway (which I know has not happened).
Thank you!
Dan
On 3/5/2025 8:08 AM, Kristian Nielsen wrote:
mariadb--- via discuss <discuss@lists.mariadb.org> writes:
Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED?
I stopped/restarted it this morning and it's writing in MIXED again, as expected.
This suggests that binlog format was set to ROW at some point, and that the slave SQL thread had not been restarted since it was set back to statement.
Ie. the following scenario:
SET GLOBAL binlog_format=ROW; STOP SLAVE; START SLAVE; SET GLOBAL binlog_format=MIXED;
Like other variables, SET GLOBAL only takes effect for new sessions (or newly started slave threads). Thus, this would explain why stop/ restart made it go back to MIXED.
Any ideas?
This would require that the binlog format had been set to ROW temporarily sometimes before the last slave restart, and back to MIXED after, which is not known to have happened from what you wrote; but it seems a likely explanation.
- Kristian.
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
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. Longish example and explanation below, but TL/DR: 1.) This link seems to indicate that temporary table statements should NOT hit the binlog on a read-only server; but I'm seeing otherwise, at least in my configuration below: https://mariadb.com/kb/en/read-only-replicas/ 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 that STOP SLAVE/START SLAVE would clear the temporary table on the replica's slave thread, but can't really find any documentation to confirm/deny that. So to reproduce the issue, I setup a test as follows. I saw the original problem on 10.11.8, but for this test I setup all the servers as 11.4.5. Setup a replication chain, such that A replicates to B and B replicates to C (A => B => C). Replication is via GTID, with gtid_strict_mode=OFF, binlog_format=MIXED, and log_slave_updates=ON. Server B and Server C are both read_only (and in my actual environment are used for data warehouse reporting and backup purposes). On server C: FLUSH BINARY LOGS; On server B: FLUSH BINARY LOGS; On server A: FLUSH BINARY LOGS; CREATE DATABASE tempDb1; CREATE TABLE tempDb1.t1 ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, f1 INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ) Engine=INNODB; INSERT INTO tempDb1.t1 ( f1 ) VALUES ( 1 ); UPDATE tempDb1.t1 SET f1 = 2 LIMIT 1; UPDATE tempDb1.t1 SET f1 = 3; On server B: CREATE TEMPORARY TABLE tempDb1.t2 AS ( SELECT f1 FROM tempDb1.t1 ); On server A: UPDATE tempDb1.t1 SET f1 = 4; UPDATE tempDb1.t1 SET f1 = 5 LIMIT 1; UPDATE tempDb1.t1 SET f1 = 6; On server C: STOP SLAVE; CHANGE MASTER TO MASTER_HOST = <SERVER A>; START SLAVE; On server A: UPDATE tempDb1.t1 SET f1 = 7; UPDATE tempDb1.t1 SET f1 = 8 LIMIT 1; UPDATE tempDb1.t1 SET f1 = 9; On server B: DROP TEMPORARY TABLE tempDb1.t2; On server A: DROP TABLE tempDb1.t1; DROP DATABASE tempDb1; Following all of this, examine the new binary logs on each of the servers. Server A is exactly as expected, only the "UPDATE ... LIMIT 1" statements are written as ROW-BASED. All others are STATEMENT based. The "UPDATE ... LIMIT" statements are documented to happen as such. 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 here, https://mariadb.com/kb/en/read-only-replicas/ which says IRT read-only replicas "CREATE, DROP, ALTER, INSERT and DELETE of temporary tables are not logged to binary log, even in statement or mixed mode." 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;". The first (f1 = 6) looks consistent with the documentation here https://mariadb.com/kb/en/unsafe-statements-for-statement-based-replication/ which states "If row-based logging is used for a statement, and the session executing the statement has any temporary tables, row-based logging is used for the remaining statements until the temporary table is dropped." Since the "UPDATE tempDb1.t1 SET f1 = 6;" followed an "UPDATE ... LIMIT" statement, then it is expected that it would be row-based; since the "UPDATE ... LIMIT" statement itself was row based AND there is a now a known temporary table on the session (the temporary table came from from Server B). But I don't understand why the "UPDATE tempDb1.t1 SET f1 = 9;" is row-based on Server C, since I did an explicit STOP SLAVE; CHANGE MASTER TO MASTER_HOST = <SERVER A>; START SLAVE; on Server C before I 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. The point is that ALL of the normal update statements from that point on on Server C will be written as ROW based until I get in and intervene directly. Of course the "DROP TEMPORARY TABLE ... " never hits Server C, since it was no longer pointing at Server B when the DROP statement was issued. The only way I've been able to "clear" the row-based formatting for good is to do a full restart of Server C; since STOP SLAVE/START SLAVE won't do it in and of itself. 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 to what is supposed to be happening and if I'm misunderstanding something in the process. Any thoughts would be appreciated! Dan On 4/3/2025 3:33 PM, mariadb--- via discuss wrote:
Looking at that processlist, it does look like it's one of the SQL threads that's hung (on Commit). But still stumped, don't know how to get to move along.
On 4/3/2025 3:24 PM, mariadb--- via discuss wrote:
Thank you for your response!
The problem has happened again today, this time to one of the upstream servers.
First (most important) question: One of the replicas filled the disk (again). It's the IO thread that's hung:
+--------+-------------+-----------+--------------+-------------- +-------+-------------------------------------------------------- +-----------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+-------------+-----------+--------------+-------------- +-------+-------------------------------------------------------- +-----------------------+----------+ | 5 | system user | | NULL | Slave_IO | 93846 | Waiting for someone to free space | NULL | 0.000 | | 7 | system user | | NULL | Slave_worker | 6688 | Commit | NULL | 0.000 | | 8 | system user | | NULL | Slave_worker | 6688 | Waiting for someone to free space | NULL | 0.000 | | 6 | system user | | NULL | Slave_SQL | 6987 | Slave has read all relay log; waiting for more updates | NULL | 0.000 |
I've cleared space (at least enough that it should process for a few minutes; just trying to get it responsive so I can restart it gracefully), but the thread won't budge. Oddly enough, PURGE BINARY LOGS TO ... appeared to work in that it cleared older logs I didn't need; but it (the command) did not return properly. It too was hanging and I had to 'CTRL-C' it. I also tried STOP SLAVE SQL_THREAD (with the intention of following with STOP SLAVE IO_THREAD) but it too hung. Tried CTRL-C there, as well, but it's still showing in the process list as "KILLED".
Is there anything I can do to get the slave thread to be responsive again short of force shutting down the DB? Should I try STOP SLAVE IO_THREAD?
As far as the binlog format issue:
I have a production primary DB (A) which feeds two replicas (let's call them B and C). I.E., both B and C are directly connected to and replicating from A. Other servers then replication from C. The interesting thing here is that the problem showed up this time on both B and C; they both were exhibiting the behavior of where everything they wrote into their logs was in ROW format. I checked the global BINLOG_FORMAT and it was "MIXED" as I expected (on both B and C). Is there any way to see what the BINLOG_FORMAT is of the slave threads themselves while they are still running? B was not out of space, so I was able to restart the slave there and I'm waiting to see if the binary logs are still problematic. C is the one that is still hung after clearing space.
And in all of this I should say no, we don't have anything (at least that I can find) that is changing the BINLOG_FORMAT directly. Indeed, server B is AWS RDS, and I don't think anything has access to change it without shutting down the server anyway (which I know has not happened).
Thank you!
Dan
On 3/5/2025 8:08 AM, Kristian Nielsen wrote:
mariadb--- via discuss <discuss@lists.mariadb.org> writes:
Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED?
I stopped/restarted it this morning and it's writing in MIXED again, as expected.
This suggests that binlog format was set to ROW at some point, and that the slave SQL thread had not been restarted since it was set back to statement.
Ie. the following scenario:
SET GLOBAL binlog_format=ROW; STOP SLAVE; START SLAVE; SET GLOBAL binlog_format=MIXED;
Like other variables, SET GLOBAL only takes effect for new sessions (or newly started slave threads). Thus, this would explain why stop/ restart made it go back to MIXED.
Any ideas?
This would require that the binlog format had been set to ROW temporarily sometimes before the last slave restart, and back to MIXED after, which is not known to have happened from what you wrote; but it seems a likely explanation.
- Kristian.
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
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.
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. Hope this helps, - Kristian.
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.
On 5/14/2025 9:27 AM, mariadb--- via discuss wrote:
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.
I added this as a comment to https://jira.mariadb.org/browse/MDEV-21442, which already had discussion that mentioned the issue. Cheers! Dan
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-leave@lists.mariadb.org
participants (2)
-
Kristian Nielsen -
mariadb＠Biblestuph.com