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
participants (2)
-
Kristian Nielsen -
mariadb＠Biblestuph.com