<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-rbr-safe-unsafe">
      </a>
      19.2.1.3 Determination of Safe and Unsafe Statements in Binary Logging
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045138307408">
  </a>
  <a class="indexterm" name="idm46045138305952">
  </a>
  <a class="indexterm" name="idm46045138304448">
  </a>
  <p>
   The
   <span class="quote">
    “
    <span class="quote">
     safeness
    </span>
    ”
   </span>
   of a statement in MySQL replication
        refers to whether the statement and its effects can be
        replicated correctly using statement-based format. If this is
        true of the statement, we refer to the statement as
   <span class="firstterm">
    safe
   </span>
   ; otherwise, we refer
        to it as
   <span class="firstterm">
    unsafe
   </span>
   .
  </p>
  <p>
   In general, a statement is safe if it deterministic, and unsafe
        if it is not. However, certain nondeterministic functions are
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   considered unsafe (see
   <a class="xref" href="replication-rbr-safe-unsafe.html#replication-rbr-safe-unsafe-not" title="Nondeterministic functions not considered unsafe">
    Nondeterministic functions not considered unsafe
   </a>
   , later in this
        section). In addition, statements using results from
        floating-point math functions—which are
        hardware-dependent—are always considered unsafe (see
   <a class="xref" href="replication-features-floatvalues.html" title="19.5.1.12 Replication and Floating-Point Values">
    Section 19.5.1.12, “Replication and Floating-Point Values”
   </a>
   ).
  </p>
  <p>
   <b>
    Handling of safe and unsafe statements.
   </b>
   A statement is treated differently depending on whether the
          statement is considered safe, and with respect to the binary
          logging format (that is, the current value of
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format
    </code>
   </a>
   ).
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When using row-based logging, no distinction is made in the
            treatment of safe and unsafe statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      When using mixed-format logging, statements flagged as
            unsafe are logged using the row-based format; statements
            regarded as safe are logged using the statement-based
            format.
     </p>
    </li>
    <li class="listitem">
     <p>
      When using statement-based logging, statements flagged as
            being unsafe generate a warning to this effect. Safe
            statements are logged normally.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Each statement flagged as unsafe generates a warning. If a large
        number of such statements were executed on the source, this
        could lead to excessively large error log files. To prevent
        this, MySQL has a warning suppression mechanism. Whenever the 50
        most recent
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_binlog_unsafe_statement" target="_top">
    <code class="literal">
     ER_BINLOG_UNSAFE_STATEMENT
    </code>
   </a>
   warnings have been generated more than 50 times in any 50-second
        period, warning suppression is enabled. When activated, this
        causes such warnings not to be written to the error log;
        instead, for each 50 warnings of this type, a note
   <code class="literal">
    The
        last warning was repeated
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    times in
        last
    <em class="replaceable">
     <code>
      S
     </code>
    </em>
    seconds
   </code>
   is written
        to the error log. This continues as long as the 50 most recent
        such warnings were issued in 50 seconds or less; once the rate
        has decreased below this threshold, the warnings are once again
        logged normally. Warning suppression has no effect on how the
        safety of statements for statement-based logging is determined,
        nor on how warnings are sent to the client. MySQL clients still
        receive one warning for each such statement.
  </p>
  <p>
   For more information, see
   <a class="xref" href="replication-formats.html" title="19.2.1 Replication Formats">
    Section 19.2.1, “Replication Formats”
   </a>
   .
  </p>
  <p>
   <b>
    Statements considered unsafe.
   </b>
   <a class="indexterm" name="idm46045138284880">
   </a>
   Statements with the following characteristics are considered
          unsafe:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       Statements containing system functions that may return a different value
              on the replica.
      </b>
      These functions include
      <a class="link" href="information-functions.html#function_found-rows">
       <code class="literal">
        FOUND_ROWS()
       </code>
      </a>
      ,
      <a class="link" href="locking-functions.html#function_get-lock">
       <code class="literal">
        GET_LOCK()
       </code>
      </a>
      ,
      <a class="link" href="locking-functions.html#function_is-free-lock">
       <code class="literal">
        IS_FREE_LOCK()
       </code>
      </a>
      ,
      <a class="link" href="locking-functions.html#function_is-used-lock">
       <code class="literal">
        IS_USED_LOCK()
       </code>
      </a>
      ,
      <a class="link" href="string-functions.html#function_load-file">
       <code class="literal">
        LOAD_FILE()
       </code>
      </a>
      ,
      <a class="link" href="mathematical-functions.html#function_rand">
       <code class="literal">
        RAND()
       </code>
      </a>
      ,
      <a class="link" href="locking-functions.html#function_release-lock">
       <code class="literal">
        RELEASE_LOCK()
       </code>
      </a>
      ,
      <a class="link" href="information-functions.html#function_row-count">
       <code class="literal">
        ROW_COUNT()
       </code>
      </a>
      ,
      <a class="link" href="information-functions.html#function_session-user">
       <code class="literal">
        SESSION_USER()
       </code>
      </a>
      ,
      <a class="link" href="miscellaneous-functions.html#function_sleep">
       <code class="literal">
        SLEEP()
       </code>
      </a>
      ,
      <a class="link" href="replication-functions-synchronization.html#function_source-pos-wait">
       <code class="literal">
        SOURCE_POS_WAIT()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_sysdate">
       <code class="literal">
        SYSDATE()
       </code>
      </a>
      ,
      <a class="link" href="information-functions.html#function_system-user">
       <code class="literal">
        SYSTEM_USER()
       </code>
      </a>
      ,
      <a class="link" href="information-functions.html#function_user">
       <code class="literal">
        USER()
       </code>
      </a>
      ,
      <a class="link" href="miscellaneous-functions.html#function_uuid">
       <code class="literal">
        UUID()
       </code>
      </a>
      , and
      <a class="link" href="miscellaneous-functions.html#function_uuid-short">
       <code class="literal">
        UUID_SHORT()
       </code>
      </a>
      .
     </p>
     <p>
      <a name="replication-rbr-safe-unsafe-not">
      </a>
      <b>
       Nondeterministic functions not considered unsafe.
      </b>
      Although these functions are not deterministic, they are
              treated as safe for purposes of logging and replication:
      <a class="link" href="information-functions.html#function_connection-id">
       <code class="literal">
        CONNECTION_ID()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_curdate">
       <code class="literal">
        CURDATE()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_current-date">
       <code class="literal">
        CURRENT_DATE()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_current-time">
       <code class="literal">
        CURRENT_TIME()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_current-timestamp">
       <code class="literal">
        CURRENT_TIMESTAMP()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_curtime">
       <code class="literal">
        CURTIME()
       </code>
      </a>
      ,
      <a class="link" href="information-functions.html#function_last-insert-id">
       <code class="literal">
        LAST_INSERT_ID()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_localtime">
       <code class="literal">
        LOCALTIME()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_localtimestamp">
       <code class="literal">
        LOCALTIMESTAMP()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_now">
       <code class="literal">
        NOW()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_unix-timestamp">
       <code class="literal">
        UNIX_TIMESTAMP()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_utc-date">
       <code class="literal">
        UTC_DATE()
       </code>
      </a>
      ,
      <a class="link" href="date-and-time-functions.html#function_utc-time">
       <code class="literal">
        UTC_TIME()
       </code>
      </a>
      , and
      <a class="link" href="date-and-time-functions.html#function_utc-timestamp">
       <code class="literal">
        UTC_TIMESTAMP()
       </code>
      </a>
      .
     </p>
     <p>
      For more information, see
      <a class="xref" href="replication-features-functions.html" title="19.5.1.14 Replication and System Functions">
       Section 19.5.1.14, “Replication and System Functions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       References to system variables.
      </b>
      Most system variables are not replicated correctly using
              the statement-based format. See
      <a class="xref" href="replication-features-variables.html" title="19.5.1.39 Replication and Variables">
       Section 19.5.1.39, “Replication and Variables”
      </a>
      . For
              exceptions, see
      <a class="xref" href="binary-log-mixed.html" title="7.4.4.3 Mixed Binary Logging Format">
       Section 7.4.4.3, “Mixed Binary Logging Format”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Loadable Functions.
      </b>
      Since we have no control over what a loadable function
              does, we must assume that it is executing unsafe
              statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Fulltext plugin.
      </b>
      This plugin may behave differently on different MySQL
              servers; therefore, statements depending on it could have
              different results. For this reason, all statements relying
              on the fulltext plugin are treated as unsafe in MySQL.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Trigger or stored program updates a table having an AUTO_INCREMENT
              column.
      </b>
      This is unsafe because the order in which the rows are
              updated may differ on the source and the replica.
     </p>
     <p>
      In addition, an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      into a
            table that has a composite primary key containing an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column that is not the
            first column of this composite key is unsafe.
     </p>
     <p>
      For more information, see
      <a class="xref" href="replication-features-auto-increment.html" title="19.5.1.1 Replication and AUTO_INCREMENT">
       Section 19.5.1.1, “Replication and AUTO_INCREMENT”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       INSERT ... ON DUPLICATE KEY UPDATE statements on tables with multiple
              primary or unique keys.
      </b>
      When executed against a table that contains more than one
              primary or unique key, this statement is considered
              unsafe, being sensitive to the order in which the storage
              engine checks the keys, which is not deterministic, and on
              which the choice of rows updated by the MySQL Server
              depends.
     </p>
     <p>
      An
      <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
       <code class="literal">
        INSERT
            ... ON DUPLICATE KEY UPDATE
       </code>
      </a>
      statement against a
            table having more than one unique or primary key is marked
            as unsafe for statement-based replication. (Bug #11765650,
            Bug #58637)
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Updates using LIMIT.
      </b>
      The order in which rows are retrieved is not specified,
              and is therefore considered unsafe. See
      <a class="xref" href="replication-features-limit.html" title="19.5.1.18 Replication and LIMIT">
       Section 19.5.1.18, “Replication and LIMIT”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Accesses or references log tables.
      </b>
      The contents of the system log table may differ between
              source and replica.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Nontransactional operations after transactional operations.
      </b>
      Within a transaction, allowing any nontransactional reads
              or writes to execute after any transactional reads or
              writes is considered unsafe.
     </p>
     <p>
      For more information, see
      <a class="xref" href="replication-features-transactions.html" title="19.5.1.35 Replication and Transactions">
       Section 19.5.1.35, “Replication and Transactions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Accesses or references self-logging tables.
      </b>
      All reads and writes to self-logging tables are considered
              unsafe. Within a transaction, any statement following a
              read or write to self-logging tables is also considered
              unsafe.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       LOAD DATA statements.
      </b>
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      is treated as
              unsafe and when
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=MIXED
       </code>
      </a>
      the
              statement is logged in row-based format. When
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=STATEMENT
       </code>
      </a>
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      does not generate
              a warning, unlike other unsafe statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       XA transactions.
      </b>
      If two XA transactions committed in parallel on the source
              are being prepared on the replica in the inverse order,
              locking dependencies can occur with statement-based
              replication that cannot be safely resolved, and it is
              possible for replication to fail with deadlock on the
              replica. When
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=STATEMENT
       </code>
      </a>
      is set, DML statements inside XA transactions are flagged
              as being unsafe and generate a warning. When
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=MIXED
       </code>
      </a>
      or
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=ROW
       </code>
      </a>
      is set,
              DML statements inside XA transactions are logged using
              row-based replication, and the potential issue is not
              present.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       <code class="literal">
        DEFAULT
       </code>
       clause that refers to a nondeterministic
              function.
      </b>
      If an expression default value refers to a
              nondeterministic function, any statement that causes the
              expression to be evaluated is unsafe for statement-based
              replication. This includes statements such as
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      . Unlike most
              other unsafe statements, this category of statement cannot
              be replicated safely in row-based format. When
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format
       </code>
      </a>
      is set to
      <code class="literal">
       STATEMENT
      </code>
      , the statement is logged and
              executed but a warning message is written to the error
              log. When
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format
       </code>
      </a>
      is set to
      <code class="literal">
       MIXED
      </code>
      or
      <code class="literal">
       ROW
      </code>
      , the statement is not executed and
              an error message is written to the error log. For more
              information on the handling of explicit defaults, see
      <a class="xref" href="data-type-defaults.html#data-type-defaults-explicit" title="Explicit Default Handling">
       Explicit Default Handling
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   For additional information, see
   <a class="xref" href="replication-features.html" title="19.5.1 Replication Features and Issues">
    Section 19.5.1, “Replication Features and Issues”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
