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:
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[21 Sep 2011 17:16] Morgan Tocker
We just had downtime related to a change in setting of binlog_format.

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:

.. even though this manual page happily says the setting is Dynamic:

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.
[21 Sep 2011 17:48] Valeriy Kravchuk
Thank you for the problem report.
[15 Jan 2013 8:16] Jon Stephens
I'll look at improving docs, then get back with Dev on possibly improving the error/warning message situation.
[24 Jan 2013 17:05] Jon Stephens
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.
[24 Jan 2013 17:07] Jon Stephens
Having fixed the docs issues, I'm reverting this bug to Development for further resolution.
[4 Feb 2013 5:18] Manish Kumar
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."