<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-administration-skip">
      </a>
      19.1.7.3 Skipping Transactions
     </h4>
    </div>
   </div>
  </div>
  <p>
   If replication stops due to an issue with an event in a
        replicated transaction, you can resume replication by skipping
        the failed transaction on the replica. Before skipping a
        transaction, ensure that the replication I/O (receiver) thread
        is stopped as well as the SQL (applier) thread.
  </p>
  <p>
   First you need to identify the replicated event that caused the
        error. Details of the error and the last successfully applied
        transaction are recorded in the Performance Schema table
   <a class="link" href="performance-schema-replication-applier-status-by-worker-table.html" title="29.12.11.7 The replication_applier_status_by_worker Table">
    <code class="literal">
     replication_applier_status_by_worker
    </code>
   </a>
   .
        You can use
   <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
    <span class="command">
     <strong>
      mysqlbinlog
     </strong>
    </span>
   </a>
   to retrieve and
        display the events that were logged around the time of the
        error. For instructions to do this, see
   <a class="xref" href="point-in-time-recovery.html" title="9.5 Point-in-Time (Incremental) Recovery">
    Section 9.5, “Point-in-Time (Incremental) Recovery”
   </a>
   . Alternatively, you can
        issue
   <a class="link" href="show-relaylog-events.html" title="15.7.7.34 SHOW RELAYLOG EVENTS Statement">
    <code class="literal">
     SHOW RELAYLOG EVENTS
    </code>
   </a>
   on the
        replica or
   <a class="link" href="show-binlog-events.html" title="15.7.7.3 SHOW BINLOG EVENTS Statement">
    <code class="literal">
     SHOW BINLOG EVENTS
    </code>
   </a>
   on
        the source.
  </p>
  <p>
   Before skipping the transaction and restarting the replica,
        check these points:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Is the transaction that stopped replication from an unknown
            or untrusted source? If so, investigate the cause in case
            there are any security considerations that indicate the
            replica should not be restarted.
     </p>
    </li>
    <li class="listitem">
     <p>
      Does the transaction that stopped replication need to be
            applied on the replica? If so, either make the appropriate
            corrections and reapply the transaction, or manually
            reconcile the data on the replica.
     </p>
    </li>
    <li class="listitem">
     <p>
      Did the transaction that stopped replication need to be
            applied on the source? If not, undo the transaction manually
            on the server where it originally took place.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To skip the transaction, choose one of the following methods as
        appropriate:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When GTIDs are in use
            (
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode
       </code>
      </a>
      is
      <code class="literal">
       ON
      </code>
      ), see
      <a class="xref" href="replication-administration-skip.html#replication-administration-skip-gtid" title="19.1.7.3.1 Skipping Transactions With GTIDs">
       Section 19.1.7.3.1, “Skipping Transactions With GTIDs”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When GTIDs are not in use or are being phased in
            (
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode
       </code>
      </a>
      is
      <code class="literal">
       OFF
      </code>
      ,
      <code class="literal">
       OFF_PERMISSIVE
      </code>
      ,
            or
      <code class="literal">
       ON_PERMISSIVE
      </code>
      ), see
      <a class="xref" href="replication-administration-skip.html#replication-administration-skip-nogtid" title="19.1.7.3.2 Skipping Transactions Without GTIDs">
       Section 19.1.7.3.2, “Skipping Transactions Without GTIDs”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If you have enabled GTID assignment on a replication channel
            using the
      <code class="literal">
       ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
      </code>
      option of the
      <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
       <code class="literal">
        CHANGE REPLICATION SOURCE
            TO
       </code>
      </a>
      statement, see
      <a class="xref" href="replication-administration-skip.html#replication-administration-skip-nogtid" title="19.1.7.3.2 Skipping Transactions Without GTIDs">
       Section 19.1.7.3.2, “Skipping Transactions Without GTIDs”
      </a>
      .
            Using
      <code class="literal">
       ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
      </code>
      on
            a replication channel is not the same as introducing
            GTID-based replication for the channel, and you cannot use
            the transaction skipping method for GTID-based replication
            with those channels.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To restart replication after skipping the transaction, issue
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START REPLICA
    </code>
   </a>
   , with the
   <code class="literal">
    FOR CHANNEL
   </code>
   clause if the replica is a
        multi-source replica.
  </p>
  <div class="section">
   <div class="titlepage">
    <div>
     <div>
      <h5 class="title">
       <a name="replication-administration-skip-gtid">
       </a>
       19.1.7.3.1 Skipping Transactions With GTIDs
      </h5>
     </div>
    </div>
   </div>
   <p>
    When GTIDs are in use
          (
    <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
     <code class="literal">
      gtid_mode
     </code>
    </a>
    is
    <code class="literal">
     ON
    </code>
    ), the GTID for a committed transaction
          is persisted on the replica even if the content of the
          transaction is filtered out. This feature prevents a replica
          from retrieving previously filtered transactions when it
          reconnects to the source using GTID auto-positioning. It can
          also be used to skip a transaction on the replica, by
          committing an empty transaction in place of the failing
          transaction.
   </p>
   <p>
    This method of skipping transactions is not suitable when you
          have enabled GTID assignment on a replication channel using
          the
    <code class="literal">
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
    </code>
    option of the
    <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
     <code class="literal">
      CHANGE REPLICATION SOURCE
          TO
     </code>
    </a>
    statement.
   </p>
   <p>
    If the failing transaction generated an error in a worker
          thread, you can obtain its GTID directly from the
    <code class="literal">
     APPLYING_TRANSACTION
    </code>
    field in the
          Performance Schema table
    <a class="link" href="performance-schema-replication-applier-status-by-worker-table.html" title="29.12.11.7 The replication_applier_status_by_worker Table">
     <code class="literal">
      replication_applier_status_by_worker
     </code>
    </a>
    .
          To see what the transaction is, issue
    <a class="link" href="show-relaylog-events.html" title="15.7.7.34 SHOW RELAYLOG EVENTS Statement">
     <code class="literal">
      SHOW RELAYLOG EVENTS
     </code>
    </a>
    on the
          replica or
    <a class="link" href="show-binlog-events.html" title="15.7.7.3 SHOW BINLOG EVENTS Statement">
     <code class="literal">
      SHOW BINLOG
          EVENTS
     </code>
    </a>
    on the source, and search the output for a
          transaction preceded by that GTID.
   </p>
   <p>
    When you have assessed the failing transaction for any other
          appropriate actions as described previously (such as security
          considerations), to skip it, commit an empty transaction on
          the replica that has the same GTID as the failing transaction.
          For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25462689"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> GTID_NEXT<span class="token operator">=</span><span class="token string">'aaa-bbb-ccc-ddd:N'</span><span class="token punctuation">;</span>
<span class="token keyword">BEGIN</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> GTID_NEXT<span class="token operator">=</span><span class="token string">'AUTOMATIC'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The presence of this empty transaction on the replica means
          that when you issue a
    <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
     <code class="literal">
      START
          REPLICA
     </code>
    </a>
    statement to restart replication, the
          replica uses the auto-skip function to ignore the failing
          transaction, because it sees a transaction with that GTID has
          already been applied. If the replica is a multi-source
          replica, you do not need to specify the channel name when you
          commit the empty transaction, but you do need to specify the
          channel name when you issue
    <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
     <code class="literal">
      START
          REPLICA
     </code>
    </a>
    .
   </p>
   <p>
    Note that if binary logging is in use on this replica, the
          empty transaction enters the replication stream if the replica
          becomes a source or primary in the future. If you need to
          avoid this possibility, consider flushing and purging the
          replica's binary logs, as in this example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42998628"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">FLUSH</span> <span class="token keyword">LOGS</span><span class="token punctuation">;</span>
<span class="token keyword">PURGE</span> <span class="token datatype">BINARY</span> <span class="token keyword">LOGS</span> <span class="token keyword">TO</span> <span class="token string">'binlog.000146'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The GTID of the empty transaction is persisted, but the
          transaction itself is removed by purging the binary log files.
   </p>
  </div>
  <div class="section">
   <div class="titlepage">
    <div>
     <div>
      <h5 class="title">
       <a name="replication-administration-skip-nogtid">
       </a>
       19.1.7.3.2 Skipping Transactions Without GTIDs
      </h5>
     </div>
    </div>
   </div>
   <p>
    To skip failing transactions when GTIDs are not in use or are
          being phased in (
    <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
     <code class="literal">
      gtid_mode
     </code>
    </a>
    is
    <code class="literal">
     OFF
    </code>
    ,
    <code class="literal">
     OFF_PERMISSIVE
    </code>
    , or
    <code class="literal">
     ON_PERMISSIVE
    </code>
    ), you can skip a specified
          number of events by issuing
    <code class="literal">
     SET GLOBAL
          sql_replica_skip_counter
    </code>
    . Alternatively, you can
          skip past an event or events by issuing a
    <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
     <code class="literal">
      CHANGE REPLICATION SOURCE TO
     </code>
    </a>
    statement to move the source binary log position forward.
   </p>
   <p>
    These methods are also suitable when you have enabled GTID
          assignment on a replication channel using the
    <code class="literal">
     ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
    </code>
    option of the
    <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
     <code class="literal">
      CHANGE REPLICATION SOURCE
          TO
     </code>
    </a>
    statement.
   </p>
   <p>
    When you use these methods, it is important to understand that
          you are not necessarily skipping a complete transaction, as is
          always the case with the GTID-based method described
          previously. These non-GTID-based methods are not aware of
          transactions as such, but instead operate on events. The
          binary log is organized as a sequence of groups known as event
          groups, and each event group consists of a sequence of events.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For transactional tables, an event group corresponds to a
              transaction.
      </p>
     </li>
     <li class="listitem">
      <p>
       For nontransactional tables, an event group corresponds to
              a single SQL statement.
      </p>
     </li>
    </ul>
   </div>
   <p>
    A single transaction can contain changes to both transactional
          and nontransactional tables.
   </p>
   <p>
    When you use a
    <code class="literal">
     SET GLOBAL
          sql_replica_skip_counter
    </code>
    statement to skip events
          and the resulting position is in the middle of an event group,
          the replica continues to skip events until it reaches the end
          of the group. Execution then starts with the next event group.
          The
    <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
     <code class="literal">
      CHANGE REPLICATION SOURCE
          TO
     </code>
    </a>
    statement does not have this function, so you
          must be careful to identify the correct location to restart
          replication at the beginning of an event group. However, using
    <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
     <code class="literal">
      CHANGE REPLICATION SOURCE TO
     </code>
    </a>
    means you do not have to count the events that need to be
          skipped, as you do with
    <code class="literal">
     SET GLOBAL
          sql_replica_skip_counter
    </code>
    , and instead you can just
          specify the location to restart.
   </p>
   <div class="section">
    <div class="titlepage">
     <div>
      <div>
       <h6 class="title">
        <a name="set-global-replica-sql-skip-counter">
        </a>
        19.1.7.3.2.1 Skipping Transactions With
        <code class="literal">
         SET GLOBAL
sql_replica_skip_counter
        </code>
       </h6>
      </div>
     </div>
    </div>
    <a class="indexterm" name="idm46045138609520">
    </a>
    <a class="indexterm" name="idm46045138608432">
    </a>
    <a class="indexterm" name="idm46045138607344">
    </a>
    <a class="indexterm" name="idm46045138606272">
    </a>
    <p>
     When you have assessed the failing transaction for any other
            appropriate actions as described previously (such as
            security considerations), count the number of events that
            you need to skip. One event normally corresponds to one SQL
            statement in the binary log, but note that statements that
            use
     <code class="literal">
      AUTO_INCREMENT
     </code>
     or
     <a class="link" href="information-functions.html#function_last-insert-id">
      <code class="literal">
       LAST_INSERT_ID()
      </code>
     </a>
     count as two
            events in the binary log. When binary log transaction
            compression is in use, a compressed transaction payload
            (
     <code class="literal">
      Transaction_payload_event
     </code>
     ) is counted as
            a single counter value, so all the events inside it are
            skipped as a unit.
    </p>
    <p>
     If you want to skip the complete transaction, you can count
            the events to the end of the transaction, or you can just
            skip the relevant event group. Remember that with
     <a class="link" href="set-variable.html#set-variable-system-variables" title="System Variable Assignment">
      <code class="literal">
       SET
            GLOBAL sql_replica_skip_counter
      </code>
     </a>
     , the replica
            continues to skip to the end of an event group. Make sure
            you do not skip too far forward and go into the next event
            group or transaction so that it is not also skipped.
    </p>
    <p>
     Issue the
     <code class="literal">
      SET
     </code>
     statement as follows, where
     <em class="replaceable">
      <code>
       N
      </code>
     </em>
     is the number of events from
            the source to skip:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa35169989"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> sql_replica_skip_counter <span class="token operator">=</span> <em class="replaceable">N</em></code></pre>
    </div>
    <p>
     This statement cannot be issued if
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
      <code class="literal">
       gtid_mode=ON
      </code>
     </a>
     is set, or if
            the replication I/O (receiver) and SQL (applier) threads are
            running.
    </p>
    <p>
     The
     <code class="literal">
      SET GLOBAL sql_replica_skip_counter
     </code>
     statement has no immediate effect. When you issue the
     <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
      <code class="literal">
       START REPLICA
      </code>
     </a>
     statement for
            the next time following this
     <code class="literal">
      SET
     </code>
     statement, the new value for the system variable
     <a class="link" href="replication-options-replica.html#sysvar_sql_replica_skip_counter">
      <code class="literal">
       sql_replica_skip_counter
      </code>
     </a>
     is
            applied, and the events are skipped. That
     <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
      <code class="literal">
       START REPLICA
      </code>
     </a>
     statement also
            automatically sets the value of the system variable back to
            0. If the replica is a multi-source replica, when you issue
            that
     <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
      <code class="literal">
       START REPLICA
      </code>
     </a>
     statement,
            the
     <code class="literal">
      FOR CHANNEL
     </code>
     clause is required. Make
            sure that you name the correct channel, otherwise events are
            skipped on the wrong channel.
    </p>
   </div>
   <div class="section">
    <div class="titlepage">
     <div>
      <div>
       <h6 class="title">
        <a name="replication-administration-skip-change-master-to">
        </a>
        19.1.7.3.2.2 Skipping Transactions With CHANGE REPLICATION SOURCE TO
       </h6>
      </div>
     </div>
    </div>
    <p>
     When you have assessed the failing transaction for any other
            appropriate actions as described previously (such as
            security considerations), identify the coordinates (file and
            position) in the source's binary log that represent a
            suitable position to restart replication. This can be the
            start of the event group following the event that caused the
            issue, or the start of the next transaction. The replication
            I/O (receiver) thread begins reading from the source at
            these coordinates the next time the thread starts, skipping
            the failing event. Make sure that you have identified the
            position accurately, because this statement does not take
            event groups into account.
    </p>
    <p>
     Issue the
     <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
      <code class="literal">
       CHANGE REPLICATION SOURCE
            TO
      </code>
     </a>
     statement as follows, where
     <em class="replaceable">
      <code>
       source_log_name
      </code>
     </em>
     is the binary log
            file that contains the restart position, and
     <em class="replaceable">
      <code>
       source_log_pos
      </code>
     </em>
     is the number
            representing the restart position as stated in the binary
            log file:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa94724534"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CHANGE</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SOURCE</span> <span class="token keyword">TO</span> <span class="token keyword">SOURCE_LOG_FILE</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">source_log_name</em>'</span><span class="token punctuation">,</span> <span class="token keyword">SOURCE_LOG_POS</span><span class="token operator">=</span><span class="token keyword"><em class="replaceable">source_log_pos</em></span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     If the replica is a multi-source replica, you must use the
     <code class="literal">
      FOR CHANNEL
     </code>
     clause to name the
            appropriate channel on the
     <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
      <code class="literal">
       CHANGE
            REPLICATION SOURCE TO
      </code>
     </a>
     statement.
    </p>
    <p>
     This statement cannot be issued if
     <code class="literal">
      SOURCE_AUTO_POSITION
     </code>
     is
     <code class="literal">
      1
     </code>
     , or if the replication I/O (receiver)
            and SQL (applier) threads are running. If you need to use
            this method of skipping a transaction when
     <code class="literal">
      SOURCE_AUTO_POSITION=1
     </code>
     , you can change
            the setting to
     <code class="literal">
      SOURCE_AUTO_POSITION=0
     </code>
     while issuing the statement, then change it back again
            afterwards. For example:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55153551"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CHANGE</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SOURCE</span> <span class="token keyword">TO</span> <span class="token keyword">SOURCE_AUTO_POSITION</span><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token keyword">SOURCE_LOG_FILE</span><span class="token operator">=</span><span class="token string">'binlog.000145'</span><span class="token punctuation">,</span> <span class="token keyword">SOURCE_LOG_POS</span><span class="token operator">=</span><span class="token number">235</span><span class="token punctuation">;</span>
<span class="token keyword">CHANGE</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SOURCE</span> <span class="token keyword">TO</span> <span class="token keyword">SOURCE_AUTO_POSITION</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
    </div>
   </div>
  </div>
 </div>
 <br/>
</div>
