<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-gtids-functions">
      </a>
      19.1.3.8 Stored Function Examples to Manipulate GTIDs
     </h4>
    </div>
   </div>
  </div>
  <p>
   This section provides examples of stored functions (see
   <a class="xref" href="stored-objects.html" title="Chapter 27 Stored Objects">
    Chapter 27,
    <i>
     Stored Objects
    </i>
   </a>
   ) which you can create using some
      of the built-in functions provided by MySQL for use with
      GTID-based replication, listed here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="gtid-functions.html#function_gtid-subset">
       <code class="literal">
        GTID_SUBSET()
       </code>
      </a>
      : Shows whether
          one GTID set is a subset of another.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="gtid-functions.html#function_gtid-subtract">
       <code class="literal">
        GTID_SUBTRACT()
       </code>
      </a>
      : Returns the
          GTIDs from one GTID set that are not in another.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       WAIT_FOR_EXECUTED_GTID_SET()
      </code>
      : Waits until
          all transactions in a given GTID set have been executed.
     </p>
    </li>
   </ul>
  </div>
  <p>
   See
   <a class="xref" href="gtid-functions.html" title="14.18.2 Functions Used with Global Transaction Identifiers (GTIDs)">
    Section 14.18.2, “Functions Used with Global Transaction Identifiers (GTIDs)”
   </a>
   , more more information about
      the functions just listed.
  </p>
  <p>
   Note that in these stored functions, the delimiter command has
      been used to change the MySQL statement delimiter to a vertical
      bar, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa54666559"><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 prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token operator">|</span></code></pre>
  </div>
  <p>
   All of the stored functions shown in this section take string
      representations of GTID sets as arguments, so GTID sets must
      always be quoted when used with them.
  </p>
  <p>
   This function returns nonzero (true) if two GTID sets are the same
      set, even if they are not formatted in the same way:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80898794"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_IS_EQUAL<span class="token punctuation">(</span>gs1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> gs2 <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
  <span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBSET</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> gs2<span class="token punctuation">)</span> <span class="token operator">AND</span> <span class="token function">GTID_SUBSET</span><span class="token punctuation">(</span>gs2<span class="token punctuation">,</span> gs1<span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function returns nonzero (true) if two GTID sets are
      disjoint:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75093956"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_IS_DISJOINT<span class="token punctuation">(</span>gs1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> gs2 <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBSET</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> gs2<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function returns nonzero (true) if two GTID sets are disjoint
      and
   <code class="literal">
    sum
   </code>
   is their union:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7938482"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_IS_DISJOINT_UNION<span class="token punctuation">(</span>gs1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> gs2 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> sum <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
  <span class="token keyword">RETURN</span> GTID_IS_EQUAL<span class="token punctuation">(</span><span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>sum<span class="token punctuation">,</span> gs1<span class="token punctuation">)</span><span class="token punctuation">,</span> gs2<span class="token punctuation">)</span> <span class="token operator">AND</span>
         GTID_IS_EQUAL<span class="token punctuation">(</span><span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>sum<span class="token punctuation">,</span> gs2<span class="token punctuation">)</span><span class="token punctuation">,</span> gs1<span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function returns a normalized form of the GTID set, in all
      uppercase, with no whitespace and no duplicates, with UUIDs in
      alphabetic order and intervals in numeric order:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30288001"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_NORMALIZE<span class="token punctuation">(</span>gs <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">LONGTEXT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>gs<span class="token punctuation">,</span> <span class="token string">''</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
  </p>
  <p>
   This function returns the union of two GTID sets:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58935235"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_UNION<span class="token punctuation">(</span>gs1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> gs2 <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">LONGTEXT</span>
  <span class="token keyword">RETURN</span> GTID_NORMALIZE<span class="token punctuation">(</span><span class="token function">CONCAT</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> <span class="token string">','</span><span class="token punctuation">,</span> gs2<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function returns the intersection of two GTID sets.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69656008"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_INTERSECTION<span class="token punctuation">(</span>gs1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> gs2 <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">LONGTEXT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> gs2<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function returns the symmetric difference between two GTID
      sets, that is, the GTIDs that exist in
   <code class="literal">
    gs1
   </code>
   but
      not in
   <code class="literal">
    gs2
   </code>
   , as well as the GTIDs that exist in
   <code class="literal">
    gs2
   </code>
   but not in
   <code class="literal">
    gs1
   </code>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63972432"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_SYMMETRIC_DIFFERENCE<span class="token punctuation">(</span>gs1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> gs2 <span class="token datatype">LONGTEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">LONGTEXT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span><span class="token function">CONCAT</span><span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> <span class="token string">','</span><span class="token punctuation">,</span> gs2<span class="token punctuation">)</span><span class="token punctuation">,</span> GTID_INTERSECTION<span class="token punctuation">(</span>gs1<span class="token punctuation">,</span> gs2<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function removes from a GTID set all the GTIDs with the
      specified origin, and returns the remaining GTIDs, if any. The
      UUID is the identifier used by the server where the transaction
      originated, which is normally the value of
   <a class="link" href="replication-options.html#sysvar_server_uuid">
    <code class="literal">
     server_uuid
    </code>
   </a>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17337314"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_SUBTRACT_UUID<span class="token punctuation">(</span>gs <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> uuid <span class="token datatype">TEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">LONGTEXT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>gs<span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>UUID<span class="token punctuation">,</span> <span class="token string">':1-'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">&lt;&lt;</span> <span class="token number">63</span><span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <p>
   This function acts as the reverse of the previous one; it returns
      only those GTIDs from the GTID set that originate from the server
      with the specified identifier (UUID).
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17924126"><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">CREATE</span> <span class="token keyword">FUNCTION</span> GTID_INTERSECTION_WITH_UUID<span class="token punctuation">(</span>gs <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span> uuid <span class="token datatype">TEXT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">LONGTEXT</span>
  <span class="token keyword">RETURN</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span>gs<span class="token punctuation">,</span> GTID_SUBTRACT_UUID<span class="token punctuation">(</span>gs<span class="token punctuation">,</span> uuid<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">|</span></code></pre>
  </div>
  <div class="example">
   <a name="idm46045145737376">
   </a>
   <p class="title">
    <b>
     Example 19.1 Verifying that a replica is up to date
    </b>
   </p>
   <div class="example-contents">
    <p>
     The built-in functions
     <a class="link" href="gtid-functions.html#function_gtid-subset">
      <code class="literal">
       GTID_SUBSET()
      </code>
     </a>
     and
     <a class="link" href="gtid-functions.html#function_gtid-subtract">
      <code class="literal">
       GTID_SUBTRACT()
      </code>
     </a>
     can be used to
        check that a replica has applied at least every transaction that
        a source has applied.
    </p>
    <p>
     To perform this check with
     <code class="literal">
      GTID_SUBSET()
     </code>
     ,
        execute the following statement on the replica:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20546380"><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">SELECT</span> <span class="token function">GTID_SUBSET</span><span class="token punctuation">(</span><em class="replaceable">source_gtid_executed</em><span class="token punctuation">,</span> <em class="replaceable">replica_gtid_executed</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     If the returns value is
     <code class="literal">
      0
     </code>
     (false), this means
        that some GTIDs in
     <em class="replaceable">
      <code>
       source_gtid_executed
      </code>
     </em>
     are not present
        in
     <em class="replaceable">
      <code>
       replica_gtid_executed
      </code>
     </em>
     , and that
        the replica has not yet applied transactions that were applied
        on the source, which means that the replica is not up to date.
    </p>
    <p>
     To perform the same check with
     <code class="literal">
      GTID_SUBTRACT()
     </code>
     , execute the following
        statement on the replica:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25009136"><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">SELECT</span> <span class="token function">GTID_SUBTRACT</span><span class="token punctuation">(</span><em class="replaceable">source_gtid_executed</em><span class="token punctuation">,</span> <em class="replaceable">replica_gtid_executed</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     This statement returns any GTIDs that are in
     <em class="replaceable">
      <code>
       source_gtid_executed
      </code>
     </em>
     but not in
     <em class="replaceable">
      <code>
       replica_gtid_executed
      </code>
     </em>
     . If any GTIDs
        are returned, the source has applied some transactions that the
        replica has not applied, and the replica is therefore not up to
        date.
    </p>
   </div>
  </div>
  <br class="example-break"/>
  <div class="example">
   <a name="idm46045145723856">
   </a>
   <p class="title">
    <b>
     Example 19.2 Backup and restore scenario
    </b>
   </p>
   <div class="example-contents">
    <p>
     The stored functions
     <code class="literal">
      GTID_IS_EQUAL()
     </code>
     ,
     <code class="literal">
      GTID_IS_DISJOINT()
     </code>
     , and
     <code class="literal">
      GTID_IS_DISJOINT_UNION()
     </code>
     can be used to
        verify backup and restore operations involving multiple
        databases and servers. In this example scenario,
     <code class="literal">
      server1
     </code>
     contains database
     <code class="literal">
      db1
     </code>
     , and
     <code class="literal">
      server2
     </code>
     contains
        database
     <code class="literal">
      db2
     </code>
     . The goal is to copy database
     <code class="literal">
      db2
     </code>
     to
     <code class="literal">
      server1
     </code>
     , and the
        result on
     <code class="literal">
      server1
     </code>
     should be the union of the
        two databases. The procedure used is to back up
     <code class="literal">
      server2
     </code>
     using
     <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
      <span class="command">
       <strong>
        mysqldump
       </strong>
      </span>
     </a>
     ,
        then to restore this backup on
     <code class="literal">
      server1
     </code>
     .
    </p>
    <p>
     Provided that
     <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
      <span class="command">
       <strong>
        mysqldump
       </strong>
      </span>
     </a>
     was run with
     <a class="link" href="mysqldump.html#option_mysqldump_set-gtid-purged">
      <code class="option">
       --set-gtid-purged
      </code>
     </a>
     set to
     <code class="literal">
      ON
     </code>
     or
     <code class="literal">
      AUTO
     </code>
     (the default),
        the output contains a
     <code class="literal">
      SET
        @@GLOBAL.gtid_purged
     </code>
     statement which adds the
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     set from
     <code class="literal">
      server2
     </code>
     to the
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_purged">
      <code class="literal">
       gtid_purged
      </code>
     </a>
     set on
     <code class="literal">
      server1
     </code>
     .
     <code class="literal">
      gtid_purged
     </code>
     contains the GTIDs of all the transactions that have been
        committed on a given server but which do not exist in any binary
        log file on the server. When database
     <code class="literal">
      db2
     </code>
     is
        copied to
     <code class="literal">
      server1
     </code>
     , the GTIDs of the
        transactions committed on
     <code class="literal">
      server2
     </code>
     , which are
        not in the binary log files on
     <code class="literal">
      server1
     </code>
     , must
        be added to
     <code class="literal">
      gtid_purged
     </code>
     for
     <code class="literal">
      server1
     </code>
     to make the set complete.
    </p>
    <p>
     The stored functions can be used to assist with the following
        steps in this scenario:
    </p>
    <div class="itemizedlist">
     <ul class="itemizedlist" style="list-style-type: disc; ">
      <li class="listitem">
       <p>
        Use
        <code class="literal">
         GTID_IS_EQUAL()
        </code>
        to verify that the
            backup operation computed the correct GTID set for the
        <code class="literal">
         SET @@GLOBAL.gtid_purged
        </code>
        statement. On
        <code class="literal">
         server2
        </code>
        , extract that statement from the
        <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
         <span class="command">
          <strong>
           mysqldump
          </strong>
         </span>
        </a>
        output, and store the GTID set
            into a local variable, such as
        <code class="literal">
         $gtid_purged_set
        </code>
        . Then execute the
            following statement:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa45451356"><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">server2<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> GTID_IS_EQUAL<span class="token punctuation">(</span>$gtid_purged_set<span class="token punctuation">,</span> <span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
       </div>
       <p>
        If the result is 1, the two GTID sets are equal, and the set
            has been computed correctly.
       </p>
      </li>
      <li class="listitem">
       <p>
        Use
        <code class="literal">
         GTID_IS_DISJOINT()
        </code>
        to verify that the
            GTID set in the
        <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
         <span class="command">
          <strong>
           mysqldump
          </strong>
         </span>
        </a>
        output does not
            overlap with the
        <code class="literal">
         gtid_executed
        </code>
        set on
        <code class="literal">
         server1
        </code>
        . Having identical GTIDs present
            on both servers causes errors when copying database
        <code class="literal">
         db2
        </code>
        to
        <code class="literal">
         server1
        </code>
        . To
            check, on
        <code class="literal">
         server1
        </code>
        , extract and store
        <code class="literal">
         gtid_purged
        </code>
        from the output into a local
            variable as done previously, then execute the following
            statement:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa51492795"><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">server1<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> GTID_IS_DISJOINT<span class="token punctuation">(</span>$gtid_purged_set<span class="token punctuation">,</span> <span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
       </div>
       <p>
        If the result is 1, there is no overlap between the two GTID
            sets, so no duplicate GTIDs are present.
       </p>
      </li>
      <li class="listitem">
       <p>
        Use
        <code class="literal">
         GTID_IS_DISJOINT_UNION()
        </code>
        to verify
            that the restore operation resulted in the correct GTID
            state on
        <code class="literal">
         server1
        </code>
        . Before restoring the
            backup, on
        <code class="literal">
         server1
        </code>
        , obtain the existing
        <code class="literal">
         gtid_executed
        </code>
        set by executing the
            following statement:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa30645690"><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">server1<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">;</span></code></pre>
       </div>
       <p>
        Store the result in a local variable
        <code class="literal">
         $original_gtid_executed
        </code>
        , as well as the
            set from
        <code class="literal">
         gtid_purged
        </code>
        in another local
            variable as described previously. When the backup from
        <code class="literal">
         server2
        </code>
        has been restored onto
        <code class="literal">
         server1
        </code>
        , execute the following statement
            to verify the GTID state:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5067275"><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">server1<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> 
<span class="token prompt">      -&gt;</span>   GTID_IS_DISJOINT_UNION<span class="token punctuation">(</span>$original_gtid_executed<span class="token punctuation">,</span>
<span class="token prompt">      -&gt;</span>                          $gtid_purged_set<span class="token punctuation">,</span>
<span class="token prompt">      -&gt;</span>                          <span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
       </div>
       <p>
        If the result is
        <code class="literal">
         1
        </code>
        , the stored function
            has verified that the original
        <code class="literal">
         gtid_executed
        </code>
        set from
        <code class="literal">
         server1
        </code>
        (
        <code class="literal">
         $original_gtid_executed
        </code>
        ) and the
        <code class="literal">
         gtid_purged
        </code>
        set that was added from
        <code class="literal">
         server2
        </code>
        (
        <code class="literal">
         $gtid_purged_set
        </code>
        ) have no overlap, and
            that the updated
        <code class="literal">
         gtid_executed
        </code>
        set on
        <code class="literal">
         server1
        </code>
        now consists of the previous
        <code class="literal">
         gtid_executed
        </code>
        set from
        <code class="literal">
         server1
        </code>
        plus the
        <code class="literal">
         gtid_purged
        </code>
        set from
        <code class="literal">
         server2
        </code>
        , which is the desired result.
            Ensure that this check is carried out before any further
            transactions take place on
        <code class="literal">
         server1
        </code>
        ,
            otherwise the new transactions in
        <code class="literal">
         gtid_executed
        </code>
        cause it to fail.
       </p>
      </li>
     </ul>
    </div>
   </div>
  </div>
  <br class="example-break"/>
  <div class="example">
   <a name="idm46045145656048">
   </a>
   <p class="title">
    <b>
     Example 19.3 Selecting the most up-to-date replica for manual failover
    </b>
   </p>
   <div class="example-contents">
    <p>
     The stored function
     <code class="literal">
      GTID_UNION()
     </code>
     can be used
        to identify the most up-to-date replica from a set of replicas,
        in order to perform a manual failover operation after a source
        server has stopped unexpectedly. If some of the replicas are
        experiencing replication lag, this stored function can be used
        to compute the most up-to-date replica without waiting for all
        the replicas to apply their existing relay logs, and therefore
        to minimize the failover time. The function can return the union
        of
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     on each
        replica with the set of transactions received by the replica,
        which is recorded in the Performance Schema
     <a class="link" href="performance-schema-replication-connection-status-table.html" title="29.12.11.13 The replication_connection_status Table">
      <code class="literal">
       replication_connection_status
      </code>
     </a>
     table. You can compare these results to find which
        replica's record of transactions is the most up to date,
        even if not all of the transactions have been committed yet.
    </p>
    <p>
     On each replica, compute the complete record of transactions by
        issuing the following statement:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5863146"><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">SELECT</span> GTID_UNION<span class="token punctuation">(</span>RECEIVED_TRANSACTION_SET<span class="token punctuation">,</span> <span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">)</span>
    <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>replication_connection_status
    <span class="token keyword">WHERE</span> channel_name <span class="token operator">=</span> <span class="token string">'name'</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     You can then compare the results from each replica to see which
        one has the most up-to-date record of transactions, and use this
        replica as the new source.
    </p>
   </div>
  </div>
  <br class="example-break"/>
  <div class="example">
   <a name="idm46045145648256">
   </a>
   <p class="title">
    <b>
     Example 19.4 Checking for extraneous transactions on a replica
    </b>
   </p>
   <div class="example-contents">
    <p>
     The stored function
     <code class="literal">
      GTID_SUBTRACT_UUID()
     </code>
     can
        be used to check whether a replica has received transactions
        that did not originate from its designated source or sources. If
        it has, there might be an issue with your replication setup, or
        with a proxy, router, or load balancer. This function works by
        removing from a GTID set all the GTIDs from a specified
        originating server, and returning the remaining GTIDs, if any.
    </p>
    <p>
     For a replica with a single source, issue the following
        statement, giving the identifier of the originating source,
        which is normally the same as
     <a class="link" href="replication-options.html#sysvar_server_uuid">
      <code class="literal">
       server_uuid
      </code>
     </a>
     :
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14286437"><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">SELECT</span> GTID_SUBTRACT_UUID<span class="token punctuation">(</span><span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">,</span> server_uuid_of_source<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     If the result is not empty, the transactions returned are extra
        transactions that did not originate from the designated source.
    </p>
    <p>
     For a replica in a multisource topology, include the server UUID
        of each source in the function call, like this:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81837690"><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">SELECT</span> 
  GTID_SUBTRACT_UUID<span class="token punctuation">(</span>GTID_SUBTRACT_UUID<span class="token punctuation">(</span><span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">,</span>
                                        server_uuid_of_source_1<span class="token punctuation">)</span><span class="token punctuation">,</span>
                                        server_uuid_of_source_2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     If the result is not empty, the transactions returned are extra
        transactions that did not originate from any of the designated
        sources.
    </p>
   </div>
  </div>
  <br class="example-break"/>
  <div class="example">
   <a name="idm46045145640064">
   </a>
   <p class="title">
    <b>
     Example 19.5 Verifying that a server in a replication topology is read-only
    </b>
   </p>
   <div class="example-contents">
    <p>
     The stored function
     <code class="literal">
      GTID_INTERSECTION_WITH_UUID()
     </code>
     can be used to
        verify that a server has not originated any GTIDs and is in a
        read-only state. The function returns only those GTIDs from the
        GTID set that originate from the server with the specified
        identifier. If any of the transactions listed in
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     from this server
        use the server's own identifier, the server itself
        originated those transactions. You can issue the following
        statement on the server to check:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90193428"><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">SELECT</span> GTID_INTERSECTION_WITH_UUID<span class="token punctuation">(</span><span class="token variable">@@GLOBAL.gtid_executed</span><span class="token punctuation">,</span> my_server_uuid<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
   </div>
  </div>
  <br class="example-break"/>
  <div class="example">
   <a name="idm46045145635376">
   </a>
   <p class="title">
    <b>
     Example 19.6 Validating an additional replica in multisource replication
    </b>
   </p>
   <div class="example-contents">
    <p>
     The stored function
     <code class="literal">
      GTID_INTERSECTION_WITH_UUID()
     </code>
     can be used to
        find out if a replica attached to a multisource replication
        setup has applied all the transactions originating from one
        particular source. In this scenario,
     <code class="literal">
      source1
     </code>
     and
     <code class="literal">
      source2
     </code>
     are both sources and replicas and
        replicate to each other.
     <code class="literal">
      source2
     </code>
     also has its
        own replica. The replica also receives and applies transactions
        from
     <code class="literal">
      source1
     </code>
     if
     <code class="literal">
      source2
     </code>
     is
        configured with
     <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
      <code class="literal">
       log_replica_updates=ON
      </code>
     </a>
     , but it
        does not do so if
     <code class="literal">
      source2
     </code>
     uses
     <code class="literal">
      log_replica_updates=OFF
     </code>
     . Whichever the case,
        we currently want only to find out if the replica is up to date
        with
     <code class="literal">
      source2
     </code>
     . In this situation,
     <code class="literal">
      GTID_INTERSECTION_WITH_UUID()
     </code>
     can be used to
        identify the transactions that
     <code class="literal">
      source2
     </code>
     originated, discarding the transactions that
     <code class="literal">
      source2
     </code>
     has replicated from
     <code class="literal">
      source1
     </code>
     . The built-in function
     <a class="link" href="gtid-functions.html#function_gtid-subset">
      <code class="literal">
       GTID_SUBSET()
      </code>
     </a>
     can then be used to
        compare the result with the
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     set on the
        replica. If the replica is up to date with
     <code class="literal">
      source2
     </code>
     , the
     <code class="literal">
      gtid_executed
     </code>
     set on the replica contains all the transactions in the
        intersection set (the transactions that originated from
     <code class="literal">
      source2
     </code>
     ).
    </p>
    <p>
     To carry out this check, store the values of
     <code class="literal">
      gtid_executed
     </code>
     and the server UUID from
     <code class="literal">
      source2
     </code>
     and the value of
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     from the replica
        into user variables as follows:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9376049"><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">source2<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@GLOBAL.gtid_executed</span> <span class="token keyword">INTO</span> <span class="token variable">@source2_gtid_executed</span><span class="token punctuation">;</span>

source2<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@GLOBAL.server_uuid</span> <span class="token keyword">INTO</span> <span class="token variable">@source2_server_uuid</span><span class="token punctuation">;</span>

<span class="token prompt">replica&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@GLOBAL.gtid_executed</span> <span class="token keyword">INTO</span> <span class="token variable">@replica_gtid_executed</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     Then use
     <code class="literal">
      GTID_INTERSECTION_WITH_UUID()
     </code>
     and
     <code class="literal">
      GTID_SUBSET()
     </code>
     with these variables as input,
        as follows:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41565623"><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">SELECT</span> 
  <span class="token function">GTID_SUBSET</span><span class="token punctuation">(</span>
    GTID_INTERSECTION_WITH_UUID<span class="token punctuation">(</span><span class="token variable">@source2_gtid_executed</span><span class="token punctuation">,</span>
                                <span class="token variable">@source2_server_uuid</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
                                <span class="token variable">@replica_gtid_executed</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     The server identifier from
     <code class="literal">
      source2
     </code>
     (
     <code class="literal">
      @source2_server_uuid
     </code>
     ) is used with
     <code class="literal">
      GTID_INTERSECTION_WITH_UUID()
     </code>
     to identify and
        return only those GTIDs from the set of GTIDs that originated on
     <code class="literal">
      source2
     </code>
     , omitting those that originated on
     <code class="literal">
      source1
     </code>
     . The resulting GTID set is then
        compared with the set of all executed GTIDs on the replica,
        using
     <code class="literal">
      GTID_SUBSET()
     </code>
     . If this statement
        returns nonzero (true), all the identified GTIDs from
     <code class="literal">
      source2
     </code>
     (the first set input) are also found
        in
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     from the
        replica, meaning that the replica has received and executed all
        the transactions that originated from
     <code class="literal">
      source2
     </code>
     .
    </p>
   </div>
  </div>
  <br class="example-break"/>
 </div>
 <br/>
</div>
