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