Skip navigation links
5.2.4.4 Logging Format for Changes to mysql Database Tables »
Section Navigation      [Toggle]

5.2.4.3. Mixed Binary Logging Format

When running in MIXED mode, automatic switching from statement-based to row-based replication takes place 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 mysqld 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 following table lists the logging formats supported by each storage engine.

Storage Engine Row Logging Supported Statement Logging Supported
ARCHIVE Yes Yes
BLACKHOLE Yes Yes
CSV Yes Yes
EXAMPLE Yes No
FEDERATED Yes Yes
HEAP Yes Yes
InnoDB Yes Yes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.
MyISAM Yes Yes
MERGE Yes Yes
NDBCLUSTER Yes No

A given storage engine can support either or both logging formats; the logging capabilities of an individual engine can be further 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.

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 capabailities of the storage engine (statement-capable, row-capable, both, or neither). 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:

Condition Action
Type binlog_format SLC RLC Error / Warning Logged as
* * No No Error: Cannot execute statement: Binary logging is impossible since at least one engine is involved that is both row-incapable and statement-incapable. -
Safe STATEMENT Yes No - STATEMENT
Safe MIXED Yes No - STATEMENT
Safe ROW Yes No Error: 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. -
Unsafe STATEMENT Yes No Warning: Unsafe statement binlogged in statement format, since BINLOG_FORMAT = STATEMENT STATEMENT
Unsafe MIXED Yes No Error: 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. -
Unsafe ROW Yes No Error: 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 Injection STATEMENT Yes No Error: 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 Injection MIXED Yes No Error: 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 Injection ROW Yes No Error: 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. -
Safe STATEMENT No Yes Error: 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. -
Safe MIXED No Yes - ROW
Safe ROW No Yes - ROW
Unsafe STATEMENT No Yes Error: 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. -
Unsafe MIXED No Yes - ROW
Unsafe ROW No Yes - ROW
Row Injection STATEMENT No Yes Error: Cannot execute row injection: Binary logging is not possible since BINLOG_FORMAT = STATEMENT. -
Row Injection MIXED No Yes - ROW
Row Injection ROW No Yes - ROW
Safe STATEMENT Yes Yes - STATEMENT
Safe MIXED Yes Yes - ROW
Safe ROW Yes Yes - ROW
Unsafe STATEMENT Yes Yes Warning: Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. STATEMENT
Unsafe MIXED Yes Yes - ROW
Unsafe ROW Yes Yes - ROW
Row Injection STATEMENT Yes Yes Error: Cannot execute row injection: Binary logging is not possible because BINLOG_FORMAT = STATEMENT. -
Row Injection MIXED Yes Yes - ROW
Row Injection ROW Yes Yes - ROW

The decision-making process for bainary 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:

  • 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 or not, 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 statement-logging capable. Similarly, a statement is row-logging restricted if any table accessed by the statement is not row-logging capable.

Condition Action
Safe/unsafe binlog_format SLR RLR Error/Warning Logged as
Safe STATEMENT Yes Yes Error: not loggable  
Safe STATEMENT Yes No   STATEMENT
Safe STATEMENT No Yes Error: not loggable  
Safe STATEMENT No No   STATEMENT
Safe MIXED Yes Yes Error: not loggable  
Safe MIXED Yes No   STATEMENT
Safe MIXED No Yes   ROW
Safe MIXED No No   STATEMENT
Safe ROW Yes Yes Error: not loggable  
Safe ROW Yes No Error: not loggable  
Safe ROW No Yes   ROW
Safe ROW No No   ROW
Unsafe STATEMENT Yes Yes Error: not loggable  
Unsafe STATEMENT Yes No Warning: unsafe STATEMENT
Unsafe STATEMENT No Yes Error: not loggable  
Unsafe STATEMENT No No Warning: unsafe STATEMENT
Unsafe MIXED Yes Yes Error: not loggable  
Unsafe MIXED Yes No Error: not loggable  
Unsafe MIXED No Yes   ROW
Unsafe MIXED No No   ROW
Unsafe ROW Yes Yes Error: not loggable  
Unsafe ROW Yes No Error: not loggable  
Unsafe ROW No Yes   ROW
Unsafe ROW No No   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. The log message will include 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.