<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-features-auto-increment">
      </a>
      19.5.1.1 Replication and AUTO_INCREMENT
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045136010304">
  </a>
  <a class="indexterm" name="idm46045136008816">
  </a>
  <a class="indexterm" name="idm46045136007328">
  </a>
  <a class="indexterm" name="idm46045136005840">
  </a>
  <a class="indexterm" name="idm46045136004352">
  </a>
  <a class="indexterm" name="idm46045136002864">
  </a>
  <p>
   Statement-based replication of
   <code class="literal">
    AUTO_INCREMENT
   </code>
   ,
   <a class="link" href="information-functions.html#function_last-insert-id">
    <code class="literal">
     LAST_INSERT_ID()
    </code>
   </a>
   , and
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   values is carried out
        subject to the following exceptions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A statement invoking a trigger or function that causes an
            update to an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column is not
            replicated correctly using statement-based replication.
            These statements are marked as unsafe. (Bug #45677)
     </p>
    </li>
    <li class="listitem">
     <p>
      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 that includes an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column that is not the
            first column of this composite key is not safe for
            statement-based logging or replication. These statements are
            marked as unsafe. (Bug #11754117, Bug #45670)
     </p>
     <p>
      This issue does not affect tables using the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      storage engine, since an
      <code class="literal">
       InnoDB
      </code>
      table with an
      <a class="link" href="glossary.html#glos_auto_increment" title="auto-increment">
       AUTO_INCREMENT
      </a>
      column requires at least one key where the auto-increment
            column is the only or leftmost column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Adding an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column to a
            table with
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      might
            not produce the same ordering of the rows on the replica and
            the source. This occurs because the order in which the rows
            are numbered depends on the specific storage engine used for
            the table and the order in which the rows were inserted. If
            it is important to have the same order on the source and
            replica, the rows must be ordered before assigning an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      number. Assuming that you
            want to add an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column to a
            table
      <code class="literal">
       t1
      </code>
      that has columns
      <code class="literal">
       col1
      </code>
      and
      <code class="literal">
       col2
      </code>
      , the
            following statements produce a new table
      <code class="literal">
       t2
      </code>
      identical to
      <code class="literal">
       t1
      </code>
      but
            with an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73703429"><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">TABLE</span> t2 <span class="token operator">LIKE</span> t1<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ADD</span> id <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> col1<span class="token punctuation">,</span> col2<span class="token punctuation">;</span></code></pre>
     </div>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       To guarantee the same ordering on both source and replica,
              the
       <code class="literal">
        ORDER BY
       </code>
       clause must name
       <span class="emphasis">
        <em>
         all
        </em>
       </span>
       columns of
       <code class="literal">
        t1
       </code>
       .
      </p>
     </div>
     <p>
      The instructions just given are subject to the limitations
            of
      <a class="link" href="create-table-like.html" title="15.1.20.3 CREATE TABLE ... LIKE Statement">
       <code class="literal">
        CREATE
            TABLE ... LIKE
       </code>
      </a>
      : Foreign key definitions are
            ignored, as are the
      <code class="literal">
       DATA DIRECTORY
      </code>
      and
      <code class="literal">
       INDEX DIRECTORY
      </code>
      table options. If a table
            definition includes any of those characteristics, create
      <code class="literal">
       t2
      </code>
      using a
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TABLE
       </code>
      </a>
      statement that is identical to the one used
            to create
      <code class="literal">
       t1
      </code>
      , but with the addition of
            the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column.
     </p>
     <p>
      Regardless of the method used to create and populate the
            copy having the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column,
            the final step is to drop the original table and then rename
            the copy:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18793241"><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">DROP</span> t1<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">RENAME</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      See also
      <a class="xref" href="alter-table-problems.html" title="B.3.6.1 Problems with ALTER TABLE">
       Section B.3.6.1, “Problems with ALTER TABLE”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
