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:
None 
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
Description:
The manual states

Setting The Binary Log Format
https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable: 

but after executing the statement SET GLOBAL binlog_format = 'STATEMENT' the server continues to write ROW format entries to the binary log file for the slave sql thread.

How to repeat:
Start up a master/slave pair using ROW format replication, log the slave statements, and change the binary log format on the slave.

binlog-format = ROW
log_slave_updates = 1

mysql> select @@binlog_format, @@log_slave_updates\G
*************************** 1. row ***************************
    @@binlog_format: ROW
@@log_slave_updates: 1
1 row in set (0.00 sec)

mysql> quit
Bye

Execute mysqlbinlog on the current binary log file to check for row format entries and observe that rows are present.

# mysqlbinlog --base64-output=NEVER bin.003535
ERROR: --base64-output=never specified, but binlog contains a Write_rows event which must be printed in base64.

# mysql

Change the binary log format to STATEMENT.

mysql> set global binlog_format='STATEMENT'; select @@binlog_format;
Query OK, 0 rows affected (0.00 sec)

+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

mysql> quit
Bye

# mysql

Confirm the binary log format and flush the binary logs.

mysql> select @@binlog_format; flush binary logs;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye

Execute mysqlbinlog on the current binary log file to check for row format entries and observe that rows are present.

# mysqlbinlog --base64-output=NEVER bin.003536
ERROR: --base64-output=never specified, but binlog contains a Update_rows event which must be printed in base64.

Suggested fix:
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?

Is this a documentation issue?
[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.