MySQL Bugs: #85723: Changing the global binlog format does not affect existing sessions

Bug #85723	Changing the global binlog format does not affect existing sessions
Submitted:	31 Mar 2017 2:08	Modified:	16 Nov 2017 7:43
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.13	OS:	Any
Assigned to:		CPU Architecture:	Any

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 existing sessions.

How to repeat:
Start up a master/slave pair using ROW format replication. Start up some connections generating frequent entries in the binary logs.

I used pt-heartbeat to simulate the connection with updates.

# pt-heartbeat --socket=`mysql -uroot -Ne "select @@socket"` --database heartbeat --table heartbeat --update --create-table --daemonize --stop

# mysql

mysql> select @@binlog_format\G
*************************** 1. row ***************************
@@binlog_format: ROW
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.022864
ERROR: --base64-output=never specified, but binlog contains a Update_rows event which must be printed in base64.

# mysql

Change the binlog format to STATEMENT

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

*************************** 1. row ***************************
@@binlog_format: ROW
1 row in set (0.00 sec)

Notice that the binlog format was not changed for the current session even though the manual states that it should be changed for all clients.

Close the session and open a new one.

mysql> quit
Bye

# mysql

Check the binlog format and flush the binary logs to create a new one.

mysql> select @@binlog_format\G flush binary logs;
*************************** 1. row ***************************
@@binlog_format: STATEMENT
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.03 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.022865
ERROR: --base64-output=never specified, but binlog contains a Update_rows event which must be printed in base64.

Kill all connected sessions.

# pt-kill --match-command Sleep --kill --victims all --interval 5 --run-time 30

Flush the binary logs to create a new one.

# mysql -e "flush binary logs"

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

# mysqlbinlog --base64-output=NEVER bin.022866

Suggested fix:
Change the binlog format for all clients/connections/sessions/threads.

Hello Monty,

Thank you for the report.
Imho this is an expected and documented behavior per manual - "If you change a global system variable, the value is remembered and used for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any current client sessions (not even the session within which the SET GLOBAL statement occurred)." - https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

Thanks,
Umesh

Change the wording "all clients" to something like "all future clients" or "all new clients" to make it clearer.

[3 Nov 2017 5:34] Paul Dubois 

https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html updated.