Determination of Safe and Unsafe Statements in Binary Logging

When speaking of the "safeness" of a statement in MariaDB 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 , "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.)

For more information, see , "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(), RELEASE_LOCK(), ROW-COUNT(), SESSION_USER(), SLEEP(), SYSDATE(), SYSTEM_USER(), USER(), UUID(), and UUID_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(), and LAST_INSERT_ID()

  For more information, see , "Replication and System Functions".

• References to system variables. Most system variables are not replicated correctly using the statement-based format. For exceptions, see , "Mixed Binary Logging Format".

  See , "Replication and Variables".

• 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_INCREMENT column. This is unsafe because the order in which the rows are updated may differ on the master and the slave.

  For more information, see , "Replication and AUTO_INCREMENT".

• INSERT DELAYED statement. This statement is considered unsafe because the insertion of the rows may interleave with concurrently executing statements.
• Updates using LIMIT. The order in which rows are retrieved is not specified.

  See , "Replication and LIMIT".

• 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 , "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 INFILE statements. Beginning with MariaDB 5.6, LOAD DATA INFILE is considered unsafe, it causes a warning in statement-based mode, and a switch to row-based format when using mixed-format logging. See , "Replication and LOAD DATA INFILE".

For additional information, see , "Replication Features and Issues".

Retornar