<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-gtids-restrictions">
      </a>
      19.1.3.7 Restrictions on Replication with GTIDs
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045145817456">
  </a>
  <p>
   Because GTID-based replication is dependent on transactions, some
      features otherwise available in MySQL are not supported when using
      it. This section provides information about restrictions on and
      limitations of replication with GTIDs.
  </p>
  <p>
   <b>
    Updates involving nontransactional storage engines.
   </b>
   When using GTIDs, updates to tables using nontransactional
        storage engines such as
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   cannot be made in the same statement or transaction as updates
        to tables using transactional storage engines such as
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   .
  </p>
  <p>
   This restriction is due to the fact that updates to tables that
      use a nontransactional storage engine mixed with updates to tables
      that use a transactional storage engine within the same
      transaction can result in multiple GTIDs being assigned to the
      same transaction.
  </p>
  <p>
   Such problems can also occur when the source and the replica use
      different storage engines for their respective versions of the
      same table, where one storage engine is transactional and the
      other is not. Also be aware that triggers that are defined to
      operate on nontransactional tables can be the cause of these
      problems.
  </p>
  <p>
   In any of the cases just mentioned, the one-to-one correspondence
      between transactions and GTIDs is broken, with the result that
      GTID-based replication cannot function correctly.
  </p>
  <p>
   <b>
    CREATE TABLE ... SELECT statements.
   </b>
   For storage engines which support atomic DDL,
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE
        TABLE ... SELECT
    </code>
   </a>
   is recorded in the binary log as one
        transaction. For more information, see
   <a class="xref" href="atomic-ddl.html" title="15.1.1 Atomic Data Definition Statement Support">
    Section 15.1.1, “Atomic Data Definition Statement Support”
   </a>
   .
  </p>
  <p>
   <b>
    Temporary tables.
   </b>
   If
   <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>
   ,
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TEMPORARY
        TABLE
    </code>
   </a>
   and
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TEMPORARY
        TABLE
    </code>
   </a>
   statements cannot be used inside transactions,
        procedures, functions, and triggers when GTIDs are in use on the
        server (that is, when the
   <a class="link" href="replication-options-gtids.html#sysvar_enforce_gtid_consistency">
    <code class="literal">
     enforce_gtid_consistency
    </code>
   </a>
   system
        variable is set to
   <code class="literal">
    ON
   </code>
   ). They can be used
        outside these contexts when GTIDs are in use, provided that
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit=1
    </code>
   </a>
   is set. 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">
    ROW
   </code>
   or
   <code class="literal">
    MIXED
   </code>
   ,
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TEMPORARY
        TABLE
    </code>
   </a>
   and
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TEMPORARY
        TABLE
    </code>
   </a>
   statements are allowed inside a transaction,
        procedure, function, or trigger when GTIDs are in use. The
        statements are not written to the binary log and are therefore
        not replicated to replicas. The use of row-based replication
        means that the replicas remain in sync without the need to
        replicate temporary tables. If the removal of these statements
        from a transaction results in an empty transaction, the
        transaction is not written to the binary log.
  </p>
  <p>
   <b>
    Preventing execution of unsupported statements.
   </b>
   To prevent execution of statements that would cause GTID-based
        replication to fail, all servers must be started with the
   <a class="link" href="replication-options-gtids.html#sysvar_enforce_gtid_consistency">
    <code class="option">
     --enforce-gtid-consistency
    </code>
   </a>
   option
        when enabling GTIDs. This causes statements of any of the types
        discussed previously in this section to fail with an error.
  </p>
  <p>
   Note that
   <a class="link" href="replication-options-gtids.html#sysvar_enforce_gtid_consistency">
    <code class="option">
     --enforce-gtid-consistency
    </code>
   </a>
   only
      takes effect if binary logging takes place for a statement. If
      binary logging is disabled on the server, or if statements are not
      written to the binary log because they are removed by a filter,
      GTID consistency is not checked or enforced for the statements
      that are not logged.
  </p>
  <p>
   For information about other required startup options when enabling
      GTIDs, see
   <a class="xref" href="replication-gtids-howto.html" title="19.1.3.4 Setting Up Replication Using GTIDs">
    Section 19.1.3.4, “Setting Up Replication Using GTIDs”
   </a>
   .
  </p>
  <p>
   <b>
    Skipping transactions.
   </b>
   <a class="link" href="replication-options-replica.html#sysvar_sql_replica_skip_counter">
    <code class="literal">
     sql_replica_skip_counter
    </code>
   </a>
   is not
        available when using GTID-based replication. If you need to skip
        transactions, use the value of the source's
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
    <code class="literal">
     gtid_executed
    </code>
   </a>
   variable instead.
        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,
   <a class="link" href="replication-options-replica.html#sysvar_sql_replica_skip_counter">
    <code class="literal">
     sql_replica_skip_counter
    </code>
   </a>
   is
        available. For more information, see
   <a class="xref" href="replication-administration-skip.html" title="19.1.7.3 Skipping Transactions">
    Section 19.1.7.3, “Skipping Transactions”
   </a>
   .
  </p>
  <p>
   <b>
    Ignoring servers.
   </b>
   <a class="link" href="change-replication-source-to.html#crs-opt-ignore_server_ids">
    <code class="literal">
     IGNORE_SERVER_IDS
    </code>
   </a>
   cannot be
        used with
   <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>
   when using GTIDs, because transactions that have
        already been applied are automatically ignored. Before starting
        GTID-based replication, check for and clear all ignored server
        ID lists that have previously been set on the servers involved.
        The
   <a class="link" href="show-replica-status.html" title="15.7.7.35 SHOW REPLICA STATUS Statement">
    <code class="literal">
     SHOW REPLICA STATUS
    </code>
   </a>
   statement, which can be issued for individual channels, displays
        the list of ignored server IDs if there is one. If there is no
        list, the
   <code class="literal">
    Replicate_Ignore_Server_Ids
   </code>
   field
        is blank. If the list of ignored server IDs is not empty, you
        can clear it with
   <code class="literal">
    CHANGE REPLICATION SOURCE TO ...
        IGNORE_SERVER_IDS=()
   </code>
   (in other words, with an empty
        list of server IDs to be ignored).
  </p>
 </div>
 <br/>
</div>
