5.2.4.3 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:

Note

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.

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 formats supported by each engine.

Storage EngineRow Logging SupportedStatement Logging Supported
ARCHIVEYesYes
BLACKHOLEYesYes
CSVYesYes
EXAMPLEYesNo
FEDERATEDYesYes
HEAPYesYes
InnoDBYesYes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.
MyISAMYesYes
MERGEYesYes
NDBCLUSTERYesNo

In MySQL 5.5.3 and later, whether a statement is to be logged and the logging mode to be used is determined according to the type of statement (safe, unsafe, or binary injected), the binary logging format (STATEMENT, ROW, or MIXED), and the logging capabilities of the storage engine (statement capable, row capable, both, or neither). (Binary injection refers to logging a change that must be logged using ROW format.)

Statements may be logged with or without a warning; failed statements are not logged, but generate errors in the log. This is shown in the following decision table, where SLC stands for statement-logging capable and RLC stands for row-logging capable.

ConditionAction
Typebinlog_formatSLCRLCError / WarningLogged as
**NoNoError: Cannot execute statement: Binary logging is impossible since at least one engine is involved that is both row-incapable and statement-incapable.-
SafeSTATEMENTYesNo-STATEMENT
SafeMIXEDYesNo-STATEMENT
SafeROWYesNoError: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging.-
UnsafeSTATEMENTYesNoWarning: Unsafe statement binlogged in statement format, since BINLOG_FORMAT = STATEMENTSTATEMENT
UnsafeMIXEDYesNoError: Cannot execute statement: Binary logging of an unsafe statement is impossible when the storage engine is limited to statement-based logging, even if BINLOG_FORMAT = MIXED.-
UnsafeROWYesNoError: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging.-
Row InjectionSTATEMENTYesNoError: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging.-
Row InjectionMIXEDYesNoError: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging.-
Row InjectionROWYesNoError: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging.-
SafeSTATEMENTNoYesError: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging.-
SafeMIXEDNoYes-ROW
SafeROWNoYes-ROW
UnsafeSTATEMENTNoYesError: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging.-
UnsafeMIXEDNoYes-ROW
UnsafeROWNoYes-ROW
Row InjectionSTATEMENTNoYesError: Cannot execute row injection: Binary logging is not possible since BINLOG_FORMAT = STATEMENT.-
Row InjectionMIXEDNoYes-ROW
Row InjectionROWNoYes-ROW
SafeSTATEMENTYesYes-STATEMENT
SafeMIXEDYesYes-STATEMENT
SafeROWYesYes-ROW
UnsafeSTATEMENTYesYesWarning: Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT.STATEMENT
UnsafeMIXEDYesYes-ROW
UnsafeROWYesYes-ROW
Row InjectionSTATEMENTYesYesError: Cannot execute row injection: Binary logging is not possible because BINLOG_FORMAT = STATEMENT.-
Row InjectionMIXEDYesYes-ROW
Row InjectionROWYesYes-ROW

Handling of mixed-format logging in MySQL 5.5.2 and earlier.  The decision-making process for binary logging changed in MySQL 5.5.3, due to the fix for Bug #39934. Prior to MySQL 5.5.3, when determining the logging mode to be used, the capabilities of all the tables affected by the event are combined, and 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 in MySQL 5.5.2 and earlier 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.

In MySQL 5.5.2 and earlier, 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.

ConditionAction
Safe/unsafebinlog_formatSLRRLRError/WarningLogged as
SafeSTATEMENTYesYesError: not loggable 
SafeSTATEMENTYesNo STATEMENT
SafeSTATEMENTNoYesError: not loggable 
SafeSTATEMENTNoNo STATEMENT
SafeMIXEDYesYesError: not loggable 
SafeMIXEDYesNo STATEMENT
SafeMIXEDNoYes ROW
SafeMIXEDNoNo STATEMENT
SafeROWYesYesError: not loggable 
SafeROWYesNoError: not loggable 
SafeROWNoYes ROW
SafeROWNoNo ROW
UnsafeSTATEMENTYesYesError: not loggable 
UnsafeSTATEMENTYesNoWarning: unsafeSTATEMENT
UnsafeSTATEMENTNoYesError: not loggable 
UnsafeSTATEMENTNoNoWarning: unsafeSTATEMENT
UnsafeMIXEDYesYesError: not loggable 
UnsafeMIXEDYesNoError: not loggable 
UnsafeMIXEDNoYes ROW
UnsafeMIXEDNoNo ROW
UnsafeROWYesYesError: not loggable 
UnsafeROWYesNoError: not loggable 
UnsafeROWNoYes ROW
UnsafeROWNoNo ROW

In all MySQL 5.5 releases, 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.