Mixed Binary Logging Format

本文转载自：http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html

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:

• When a DML statement updates an NDBCLUSTER table.

• When a function contains UUID().

• When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.

  For more information, see Section 17.4.1.1, “Replication and AUTO_INCREMENT”.

• When any INSERT DELAYED is executed.

• When the body of a view requires row-based replication, the statement creating the view also uses it. For example, this occurs when the statement creating a view uses the UUID() function.

• When a call to a UDF is involved.

• If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.

  This is true whether or not any temporary tables are actually logged.

  Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.

• When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)

• When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)

• When a statement refers to one or more system variables. (Bug #31168)

  Exception.  The following system variables, when used with session scope (only), do not cause the logging format to switch:

  • auto_increment_increment

  • auto_increment_offset

  • character_set_client

  • character_set_connection

  • character_set_database

  • character_set_server

  • collation_connection

  • collation_database

  • collation_server

  • foreign_key_checks

  • identity

  • last_insert_id

  • lc_time_names

  • pseudo_thread_id

  • sql_auto_is_null

  • time_zone

  • timestamp

  • unique_checks

  For information about determining system variable scope, see Section 5.1.5, “Using System Variables”.

  For information about how replication treats sql_mode, see Section 17.4.1.36, “Replication and Variables”.

• When one of the tables involved is a log table in the mysql database.

• When the LOAD_FILE() function is used. (Bug #39701)

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:

• 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 formats supported by each 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 orSERIALIZABLE; No otherwise.
MyISAM	Yes	Yes
MERGE	Yes	Yes
NDBCLUSTER	Yes	No

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 RLCstands for “row-logging capable”.

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 = ROWand 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 = ROWand 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	-	STATEMENT
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

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:

• 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 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.

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

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.

Previous /  Next /  Up /  Table of Contents

User Comments

Posted by Peter Burns on March 28 2014 12:17pm

[Delete] [Edit]

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 http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html) 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.

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“日志系统”主题相关。