Unsafe Statements for Statement-Based Replication

Identify unsafe statements for statement-based replication in MariaDB Server. This section details SQL commands that can cause inconsistencies, guiding you toward safer replication practices.

A safe statement is generally deterministic; in other words the statement will always produce the same result. For example, an INSERT statement producing a random number will most likely produce a different result on the primary than on the replica, and so cannot be replicated safely.

When an unsafe statement is run, the current binary logging format determines how the server responds.

• If the binary logging format is statement-based, unsafe statements generate a warning and are logged normally.

• If the binary logging format is mixed, unsafe statements are logged using the row-based format, while safe statements use the statement-based format.

• If the binary logging format is row-based, all statements are logged normally, and the distinction between safe and unsafe is not made.

MariaDB tries to detect unsafe statements. When an unsafe statement is issued, a warning similar to the following is produced:

Note (Code 1592): Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

MariaDB also issues this warning for some classes of statements that are safe.

Unsafe Statements

The following statements are regarded as unsafe:

• INSERT ... ON DUPLICATE KEY UPDATE statements using tables with multiple primary or unique keys, as the order that the keys are checked in, and which affect the rows chosen to update, is not deterministic. The warning about this was removed, because we always check keys in the same order on the primary and replica if the primary and replica are using the same storage engine.

• INSERT-DELAYED. These statements are inserted in an indeterminate order.

• INSERT ... SELECT for a table that has an AUTO_INCREMENT column.

• INSERTs on tables with a composite primary key that has an AUTO_INCREMENT column that isn't the first column of the composite key.

• When a table has an AUTO_INCREMENT column and a trigger or stored procedure executes an UPDATE statement against the table.

• UPDATE statements that use LIMIT, since the order of the returned rows is unspecified. This applies even to statements using an ORDER BY clause, which are deterministic (a known bug). However, LIMIT 0 is an exception to this rule (see MDEV-6170), and these statements are safe for replication.

• When using a user-defined function.

• Statements using any of the following functions, which can return different results on the replica:

  • CURRENT_ROLE()

  • CURRENT_USER()

  • FOUND_ROWS()

  • GET_LOCK()

  • IS_FREE_LOCK()

  • IS_USED_LOCK()

  • JSON_TABLE()

  • LOAD_FILE()

  • MASTER_POS_WAIT()

  • RAND()

  • RANDOM_BYTES()

  • RELEASE_ALL_LOCKS()

  • RELEASE_LOCK()

  • ROW_COUNT()

  • SESSION_USER()

  • SLEEP()

  • SYSDATE()

  • SYSTEM_USER()

  • USER()

  • UUID()

  • UUID_SHORT().

• Statements which refer to log tables, since these may differ across servers.

• Statements which refer to self-logging tables. Statements following a read or write to a self-logging table within a transaction are also considered unsafe.

• Statements which refer to system variables (there are a few exceptions).

• LOAD DATA INFILE statements.

• Non-transactional reads or writes that execute after transactional reads within a transaction.

• If row-based logging is used for a statement, and the session executing the statement has any temporary tables, row-based logging is used for the remaining statements until the temporary table is dropped. This is because temporary tables can't use row-based logging, so if it is used due to one of the above conditions, all subsequent statements using that table are unsafe. The server deals with this situation by treating all statements in the session as unsafe for statement-based logging until the temporary table is dropped.

Safe Statements

The following statements are not deterministic, but are considered safe for binary logging and replication:

• CONNECTION_ID()

• CURDATE()

• CURRENT_DATE()

• CURRENT_TIME()

• CURRENT_TIMESTAMP()

• CURTIME()

• LAST_INSERT_ID()

• LOCALTIME()

• LOCALTIMESTAMP()

• NOW()

• UNIX_TIMESTAMP()

• UTC_DATE()

• UTC_TIME()

• UTC_TIMESTAMP()

Isolation Levels

Even when using safe statements, not all transaction isolation levels are safe with statement-based or mixed binary logging. While the REPEATABLE READ and SERIALIZABLE isolation levels can be used with both statement- and row-based replication, the READ COMMITTED and READ UNCOMMITTED isolation levels only support row-based replication, as with them isolation between transactions is not guaranteed at all, and different transaction orders on a replica, or when doing point-in-time recovery, would lead to different results than on the original master.

This restriction does not apply if only non-transactional storage engines are used.

See Also

• Replication and Foreign Keys

_{This page is licensed: CC BY-SA / Gnu FDL}