Unsafe Statements for Statement-based Replication

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 (the default until MariaDB 10.2.3), unsafe statements generate a warning and are logged normally.
• If the binary logging format is mixed (the default from MariaDB 10.2.4), 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 upon 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. Before MariaDB 5.5.24, these statements were not regarded as unsafe. In MariaDB 10.0 this warning has been removed as 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's 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. Before MariaDB 5.5, all updates on tables with an AUTO_INCREMENT column were considered unsafe, as the order that the rows were updated could differ across servers.
• 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, since MariaDB 10.0.11, 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 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()
  • 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 (since MariaDB 5.5).
• 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. The REPEATABLE READ and SERIALIZABLE isolation levels can only be used with the row-based format.

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

See Also

• Replication and Foreign Keys