When running in MIXED
mode, automatic
switching from statement-based to row-based replication takes
place under the following conditions:
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
.
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 client 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 client are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using the row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe, and we approximate this condition by treating all statements made by that client as unsafe until the client 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:
For information about determining system variable scope, see Section 5.1.6, “Using System Variables”.
For information about how replication treats
sql_mode
, see
Section 16.4.1.31, “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)
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.
User Comments
Add your own comment.