Documentation Home
MySQL Internals Manual


MySQL Internals Manual  /  ...  /  Unsafe Statements

19.4.2 Unsafe Statements

NOTE: the following list is incomplete; it does not take into account changes made in 2010 or later (roughly).

A statement may be flagged as unsafe. An unsafe statement will be logged in row format if binlog_format=MIXED and will generate a warning if binlog_format=STATEMENT.

The following types of sub-statements are currently marked unsafe:

  • System functions that may return a different value on slave, including: FOUND_ROWS, GET_LOCK, IS_FREE_LOCK, IS_USED_LOCK, LOAD_FILE, MASTER_POS_WAIT, RAND, RELEASE_LOCK, ROW_COUNT, SESSION_USER, SLEEP, SYSDATE, SYSTEM_USER, USER, UUID, UUID_SHORT.

    Note: the following non-deterministic functions are not marked unsafe:

    • CONNECTION_ID (Query_log_events contain the connection number)

    • CURDATE, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURTIME, LOCALTIME, LOCALTIMESTAMP, NOW, UNIX_TIMESTAMP, UTC_DATE, UTC_TIME, UTC_TIMESTAMP (Query_log_event contain timezone and the time when the statement was executed)

    • LAST_INSERT_ID (this is replicated in an Intvar_log_event)

    Also note that most floating-point math functions will return a hardware-dependent result. We do not mark such function unsafe, because we only support replication between platforms that use identical floating point math.

  • System variables, with some exceptions listed at Mixed Binary Logging Format.

  • UDFs: since we have no control over what the UDF does, it may be doing something unsafe.

  • Update from a sub-statement of a table that has an autoincrement column. This is unsafe because the Intvar_log_event is limited to only hold autoincrement values for one table.

  • INSERT DELAYED, since the rows inserted may interleave with concurrently executing statements.

  • Updates using LIMIT, since the order in which rows are retreived is not specified.

  • Statements referencing system log tables, since the contents of those tables may differ between master and slave.

  • Non-transactional reads or writes executing after transactional reads or writes in a transaction (see Section 19.4.3, “Logging Transactions”).

  • Reads or writes to self-logging tables, and all statements executing after reads or writes to self-logging tables in the same transaction.

The following has not yet been implemented:

  • Statements using fulltext parser plugins (cf. Bug#48183)


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.