Bug #63197 binlog_format Isn't Really Dynamic Variable
Submitted: 11 Nov 2011 7:14 Modified: 20 Dec 2011 11:29
Reporter: Denis Melnikov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.16 OS:Linux (CentOS 6 x86_64)
Assigned to: CPU Architecture:Any
Tags: binlog_format

[11 Nov 2011 7:14] Denis Melnikov 
Description:
Started with binlog_format=MIXED, MySQL doesn't switch to STATEMENT with

SET GLOBAL binlog_format=STATEMENT

How to repeat:
Start slave MySQL with 
binlog_format=MIXED
log-slave-updates=1

Ensure that (some) statements in binlog appear in binary format.
Execute SET GLOBAL binlog_format=STATEMENT on the slave.

The statements appear in binary format as before.
[11 Nov 2011 7:47] Valeriy Kravchuk 
As this happens on slave, are you sure that statements logged in binary format are NOT coming from master? I would not expect slave to convert binary event coming from master into statement format on the fly, before logging them to slave's own binary log.
[11 Nov 2011 8:04] Denis Melnikov 
First, yes, I'm sure because
a) they were logged in text format before MySQL was started with binlog_format=MIXED, and
b) master is 5.0.

Second, when I say 'binlog' I mean 'binlog', not 'relay log'. As I understand, slave writes to binlog after it replays relay log, doesn't it?
[18 Nov 2011 9:09] Denis Melnikov 
One need to STOP/START SLAVE to turn binlog_format into effect.
I think it's a feature not bug.
[19 Dec 2011 19:26] Sveta Smirnova 
Thank you for the feedback.

Do you expect to see results in already running connection?
[20 Dec 2011 7:46] Denis Melnikov 
Yes, I did.
[20 Dec 2011 11:29] Sveta Smirnova 
Thank you for the feedback.

This is not a bug than. See at http://dev.mysql.com/doc/refman/5.5/en/set-option.html:

If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that 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 client that is currently connected (not even that of the client that issues the SET GLOBAL statement). 

So restart of slave thread is required.