MySQL 5.1 Reference Manual  /  ...  /  Mixed Binary Logging Format Mixed Binary Logging Format

When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:


Starting with MySQL 5.1.20, a warning is generated if you try to execute a statement using statement-based logging that should be written using row-based logging. The warning is shown both in the client (in the output of SHOW WARNINGS) and through the mysqld error log. A warning is added to the SHOW WARNINGS table each time such a statement is executed. However, only the first statement that generated the warning for each client session is written to the error log to prevent flooding the log.

Starting with MySQL 5.1.20, in addition to the decisions above, individual engines can also determine the logging format used when information in a table is updated. The logging capabilities of an individual engine can be defined as follows:

  • If an engine supports row-based logging, the engine is said to be row-logging capable.

  • If an engine supports statement-based logging, the engine is said to be statement-logging capable.

A given storage engine can support either or both logging formats. The following table lists the logging formats supported by each storage engine.

Storage EngineRow Logging SupportedStatement Logging Supported
InnoDBYesYes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.

When determining the logging mode to be used, the capabilities of all the tables affected by the event are combined. The set of affected tables is then marked according to these rules:

  • A set of tables is defined as row-logging restricted if the tables are row-logging capable but not statement-logging capable.

  • A set of tables is defined as statement-logging restricted if the tables are statement-logging capable but not row-logging capable.

Once the determination of the possible logging formats required by the statement is complete it is compared to the current binlog_format setting. The following table is used to decide how the information is recorded in the binary log or, if appropriate, whether an error is raised. In the table, a safe operation is defined as one that is deterministic.

Several rules decide whether the statement is deterministic, as shown in the following table, where SLR stands for statement-logging restricted and RLR stands for row-logging restricted. A statement is statement-logging restricted if one or more of the tables it accesses is not row-logging capable. Similarly, a statement is row-logging restricted if any table accessed by the statement is not statement-logging capable.

Safe/unsafebinlog_formatSLRRLRError/WarningLogged as
SafeSTATEMENTYesYesError: not loggable 
SafeSTATEMENTNoYesError: not loggable 
SafeMIXEDYesYesError: not loggable 
SafeROWYesYesError: not loggable 
SafeROWYesNoError: not loggable 
UnsafeSTATEMENTYesYesError: not loggable 
UnsafeSTATEMENTYesNoWarning: unsafeSTATEMENT
UnsafeSTATEMENTNoYesError: not loggable 
UnsafeMIXEDYesYesError: not loggable 
UnsafeMIXEDYesNoError: not loggable 
UnsafeROWYesYesError: not loggable 
UnsafeROWYesNoError: not loggable 
UnsafeROWNoYes ROW

When a warning is produced by the determination, a standard MySQL warning is produced (and is available using SHOW WARNINGS). The information is also written to the mysqld error log. Only one error for each error instance per client connection is logged to prevent flooding the log. The log message includes the SQL statement that was attempted.

If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.

User Comments
  Posted by Peter Burns on March 28, 2014
It took me a while to understand the statement "Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe". I think it's describing the situation where a temporary table is updated with an unsafe query: in this case, the unsafe update can't be logged row-based, and all subsequent statements using that table are indeed unsafe. However, this understanding conflicts with the earlier statement that "logging by row is used for all subsequent statements (except for those accessing temporary tables)": surely it's *only* the statements using temporary tables which now need to be logged by row?

The rule to approximate this condition ("treating all statements executed during the session as unsafe until the session no longer holds any temporary tables") causes us some problems when we try to use MIXED replication, because
a) we run multiple servers in a replication loop and
b) the rule seems to be applied to the slave SQL thread, i.e. the statements executed by this thread are treated as a single session

So what we see is this (with MIXED replication everywhere):
1. a session on the master creates a temporary table (logged by statement)
2. another session on the master logs by row (e.g. because it's unsafe)
3. other sessions on the master log by statement
4. but when these statement-logged events replicate to the slave, they now get logged by row (until the temporary table is dropped)

Hence statements safe for SBR but unsafe for RBR (see my comment on can get logged by row and cause problems further down the replication chain. Disaster!

I think the easiest solution is to always log by row when using temporary tables ("SET SESSION binlog_format='ROW'"). But it would be good to have an option to do this automatically in MIXED mode, e.g. to always treat statements using temporary tables as unsafe.

  Posted by Scott M. Sanders on August 19, 2014
If you need it mixed but are not a SUPER user, do this first instead:
Sign Up Login You must be logged in to post a comment.