Documentation Home
MySQL Internals Manual
Download this Manual
EPUB - 1.2Mb


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
Sign Up Login You must be logged in to post a comment.