- 16.1.2 Replication Formats
- 16.1.2.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication
- 16.1.2.2 Usage of Row-Based Logging and Replication
- 16.1.2.3 Determination of Safe and Unsafe Statements in Binary Logging
When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement and its effects can be replicated correctly using statement-based format. If this is true of the statement, we refer to the statement as safe; otherwise, we refer to it as unsafe.
In general, a statement is safe if it deterministic, and unsafe if it is not. However, certain nondeterministic functions are not considered unsafe (see Nondeterministic functions not considered unsafe, later in this section). In addition, statements using results from floating-point math functions—which are hardware-dependent—are always considered safe (see Section 16.4.1.12, “Replication and Floating-Point Values”).
Handling of safe and unsafe statements.
A statement is treated differently depending on whether the
statement is considered safe, and with respect to the binary
logging format (that is, the current value of
binlog_format).
No distinction is made in the treatment of safe and unsafe statements when the binary logging mode is
ROW.If the binary logging format is
MIXED, statements flagged as unsafe are logged using the row-based format; statements regarded as safe are logged using the statement-based format.If the binary logging format is
STATEMENT, statements flagged as being unsafe generate a warning to this effect. (Safe statements are logged normally.)
Each statement flagged as unsafe generates a warning. Formerly,
in cases where a great many such statements were executed on the
master, this could lead to very large error log files, sometimes
even filling up an entire disk unexpectedly. To guard against
this, MySQL 5.5.27 introduced a warning suppression mechanism,
which behaves as follows: Whenever the 50 most recent
ER_BINLOG_UNSAFE_STATEMENT
warnings have been generated more than 50 times in any 50-second
period, warning suppression is enabled. When activated, this
causes such warnings not to be written to the error log;
instead, for each 50 warnings of this type, a note The
last warning was repeated is written
to the error log. This continues as long as the 50 most recent
such warnings were issued in 50 seconds or less; once the rate
has decreased below this threshold, the warnings are once again
logged normally. Warning suppression has no effect on how the
safety of statements for statement-based logging is determined,
nor on how warnings are sent to the client (MySQL clients still
receive one warning for each such statement).
N times in
last S seconds
For more information, see Section 16.1.2, “Replication Formats”.
Statements considered unsafe. Statements having the following characteristics are considered unsafe:
Statements containing system functions that may return a different value on slave. These functions include
FOUND_ROWS(),GET_LOCK(),IS_FREE_LOCK(),IS_USED_LOCK(),LOAD_FILE(),MASTER_POS_WAIT(),RAND(),RELEASE_LOCK(),ROW_COUNT(),SESSION_USER(),SLEEP(),SYSDATE(),SYSTEM_USER(),USER(),UUID(), andUUID_SHORT().Nondeterministic functions not considered unsafe. Although these functions are not deterministic, they are treated as safe for purposes of logging and replication:
CONNECTION_ID(),CURDATE(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP(),CURTIME(),LOCALTIME(),LOCALTIMESTAMP(),NOW(),UNIX_TIMESTAMP(),UTC_DATE(),UTC_TIME(),UTC_TIMESTAMP(), andLAST_INSERT_ID()For more information, see Section 16.4.1.14, “Replication and System Functions”.
References to system variables. Most system variables are not replicated correctly using the statement-based format. For exceptions, see Section 5.2.4.3, “Mixed Binary Logging Format”.
UDFs. Since we have no control over what a UDF does, we must assume that it is executing unsafe statements.
Updates a table having an
AUTO_INCREMENTcolumn. This is unsafe because the order in which the rows are updated may differ on the master and the slave.In addition, an
INSERTinto a table that has a composite primary key containing anAUTO_INCREMENTcolumn that is not the first column of this composite key is unsafe.For more information, see Section 16.4.1.1, “Replication and
AUTO_INCREMENT”.INSERT DELAYEDstatement. This statement is considered unsafe because the insertion of the rows may interleave with concurrently executing statements.INSERT ... ON DUPLICATE KEY UPDATEstatements on tables with multiple primary or unique keys. When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends.An
INSERT ... ON DUPLICATE KEY UPDATEstatement against a table having more than one unique or primary key is marked as unsafe for statement-based replication beginning with MySQL 5.5.24. (Bug #11765650, Bug #58637)Updates using
LIMIT. The order in which rows are retrieved is not specified.Accesses or references log tables. The contents of the system log table may differ between master and slave.
Nontransactional operations after transactional operations. Within a transaction, allowing any nontransactional reads or writes to execute after any transactional reads or writes is considered unsafe.
For more information, see Section 16.4.1.32, “Replication and Transactions”.
Accesses or references self-logging tables. All reads and writes to self-logging tables are considered unsafe. Within a transaction, any statement following a read or write to self-logging tables is also considered unsafe.
LOAD DATA INFILEstatements. Beginning with MySQL 5.5.6,LOAD DATA INFILEis considered unsafe, it causes a warning in statement-based mode, and a switch to row-based format when using mixed-format logging. See Section 16.4.1.16, “Replication andLOAD DATA INFILE”.
For additional information, see Section 16.4.1, “Replication Features and Issues”.
User Comments
Add your own comment.