The Ultimate Guide to High Availability with MariaDB

Unsafe Statements for Statement-based Replication

You are viewing an old version of this article. View the current version here.

Contents

  1. Unsafe Statements
  2. Safe Statements
  3. Isolation Levels
  4. See Also

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 master than on the slave, 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 master and slave if the master and slave is 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.3, 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 slave:
  • 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:

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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.