MySQL Bugs: #62493: server should issue warning when changing binlog

Bug #62493	server should issue warning when changing binlog_format
Submitted:	21 Sep 2011 17:16	Modified:	4 Feb 2013 5:20
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We just had downtime related to a change in setting of binlog_format.
i.e.

SET GLOBAL binlog_format = 'STATEMENT'; # was row

It turns out the clients were receiving this error:

"Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED."

We are using 5.5, where the default storage engine is InnoDB.  It makes no sense that I should be able to make this change and receive no warning whatsoever from my command line interface.

There is also no documentation in the manual to warn me about this change:
http://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

.. even though this manual page happily says the setting is Dynamic:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_binlog-format

How to repeat:
- Use row based replication + read-committed isolation level (typically recommended).
- Try and switch to binlog_format=statement.
- Watch as no updates can be applied.

Suggested fix:
- Include a warning in the manual.
- (Most backward compatible) Include a warning in the client as you make the change to statement-based if isolation level = repeatable-read.
- (Ideal) refuse to switch if default-storage-engine is InnoDB.

Thank you for the problem report.

I'll look at improving docs, then get back with Dev on possibly improving the error/warning message situation.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Having fixed the docs issues, I'm reverting this bug to Development for further resolution.

The docs have been updated and this should be enough to fix the bug: 

"If you are using InnoDB tables and the transaction isolation level is READ. 
 COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is.
 possible to change the logging format to STATEMENT, but doing so at runtime   
 leads very rapidly to errors because InnoDB can no longer perform inserts."