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:

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:

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.