Bug #85724 | Changing the global binary log format does not affect the slave SQL thread | ||
---|---|---|---|
Submitted: | 31 Mar 2017 2:41 | Modified: | 9 Aug 2018 14:53 |
Reporter: | monty solomon | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.7.19 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[31 Mar 2017 2:41]
monty solomon
[31 Mar 2017 2:47]
monty solomon
The documentation states ... Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can cause replication to fail with errors such as Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.' ... but that does not discuss changing the binary logging format on the slave. If I change the binary logging format on the slave from ROW to STATEMENT in the my.cnf file and restart it then replication fails when the master is writing ROW format binary logs. Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
[31 Mar 2017 2:54]
monty solomon
I have a master/slave cluster using RBR that I want to change to SBR and want to test for any issues before making the change. To accomplish that goal I added a new slave that I want to run in SBR to get any warnings about unsafe statements. I can't get those warnings unless running with SBR (see bug #84971). Is it possible to have a SBR slave in a cluster where the master uses RBR? It would be useful to document the valid combinations of binary formats on the master and slaves.
[22 Aug 2017 10:37]
MySQL Verification Team
Hi, > Is the slave thread able to convert the ROW format entry > read from the master to a STATEMENT format entry to write > to its binary log? That is not possible. The ROW entry does not have data required to generate the STATEMENT that created it. It could, in theory, generate some "generic" statements but what would be the point. > Is it possible to have a SBR slave in a cluster where the master uses RBR? nope, not really. > I want to change to SBR That is a weird decision. Anyhow, what you are asking here is really out of scope of documentation so I don't really agree that documentation about this particular issue needs changes. As for the changes you want to make on your system I can suggest contacting MySQL Support & Consulting service as they can help you with the core of your issue (so instead solving that issue by moving to SBR you can actually solve the problem "properly" :) ) all best Bogdan
[24 Aug 2017 3:41]
monty solomon
The documentation states that the logging format can be switched at runtime but that applies only to a master since there are restrictions on when one can change the logging format on a slave. Those restrictions are not documented. The section of that page that describes the exceptions when you cannot switch the replication format at runtime should be updated to include the restrictions. There are exceptions when you cannot switch the replication format at all on a slave that are not documented. Thanks.
[31 Aug 2017 12:44]
MySQL Verification Team
I don't really agree that documentation is unclear as all those cases to me look like common sense but I might be biased so I verified the bug and pushed it to documentation team thanks Bogdan
[19 Oct 2017 11:33]
Pavel Gusev
We have same problem. But, if you run MySQL with STATEMENT format, then you can change binlog_format in runtime and it works good. But if you start MySQL with ROW format, you have this problem.
[9 Aug 2018 14:53]
Margaret Fisher
Posted by developer: Thanks very much for the report and subsequent input. I have reworked the information in 5.7 and 8.0 https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html I have clarified upfront that there are situations where the format cannot be changed at runtime. I moved the material from the note together with the other restrictions, and reworked as follows based on the discussions in this bug: Switching the replication format while replication is ongoing can also cause issues. Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. When using STATEMENT mode, the binlog_format system variable is not replicated. When using MIXED or ROW logging mode, it is replicated but is ignored by the slave. A replication slave is not able to convert binary log entries received in ROW logging format to STATEMENT format for use in its own binary log. The slave must therefore use ROW or MIXED if the master does. Changing the binary logging format on the master from STATEMENT to ROW or MIXED while replication is ongoing to a slave with STATEMENT format can cause replication to fail with errors such as "Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'" Changing the binary logging format on the slave to STATEMENT format when the master is still using MIXED or ROW format also causes the same type of replication failure. To change the format safely, you must stop replication and ensure that the same change is made on both the master and the slave.