Unsafe Statements for Replication

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

A 'safe' statement is one that can be replicated correctly in the statement-based binary log format.

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), unsafe statements generate a warning and are logged normally.
  • If the binary logging format is row-based, all statements are logged normally, and the distinction between safe and unsafe is not made.
  • If the binary logging format is mixed, unsafe statements are logged using the row-based format, while safe statements use the statement-based format.

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:

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.

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.