<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-online-ddl-limitations">
      </a>
      17.12.8 Online DDL Limitations
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045159464192">
  </a>
  <a class="indexterm" name="idm46045159462704">
  </a>
  <a class="indexterm" name="idm46045159461216">
  </a>
  <p>
   The following limitations apply to online DDL operations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The table is copied when creating an index on a
      <code class="literal">
       TEMPORARY TABLE
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      clause
      <code class="literal">
       LOCK=NONE
      </code>
      is not permitted if there are
      <code class="literal">
       ON...CASCADE
      </code>
      or
      <code class="literal">
       ON...SET
          NULL
      </code>
      constraints on the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Before an in-place online DDL operation can finish, it must
          wait for transactions that hold metadata locks on the table to
          commit or roll back. An online DDL operation may briefly
          require an exclusive metadata lock on the table during its
          execution phase, and always requires one in the final phase of
          the operation when updating the table definition.
          Consequently, transactions holding metadata locks on the table
          can cause an online DDL operation to block. The transactions
          that hold metadata locks on the table may have been started
          before or during the online DDL operation. A long running or
          inactive transaction that holds a metadata lock on the table
          can cause an online DDL operation to timeout.
     </p>
    </li>
    <li class="listitem">
     <p>
      When running an in-place online DDL operation, the thread that
          runs the
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement
          applies an online log of DML operations that were run
          concurrently on the same table from other connection threads.
          When the DML operations are applied, it is possible to
          encounter a duplicate key entry error (
      <span class="errortext">
       ERROR 1062
          (23000): Duplicate entry
      </span>
      ), even if the duplicate
          entry is only temporary and would be reverted by a later entry
          in the online log. This is similar to the idea of a foreign
          key constraint check in
      <code class="literal">
       InnoDB
      </code>
      in which
          constraints must hold during a transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      for an
      <code class="literal">
       InnoDB
      </code>
      table is mapped to an
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      operation to
          rebuild the table and update index statistics and free unused
          space in the clustered index. Secondary indexes are not
          created as efficiently because keys are inserted in the order
          they appeared in the primary key.
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      is supported
          with the addition of online DDL support for rebuilding regular
          and partitioned
      <code class="literal">
       InnoDB
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      Tables created before MySQL 5.6 that include temporal columns
          (
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATE
       </code>
      </a>
      ,
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      or
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      ) and have not been
          rebuilt using
      <code class="literal">
       ALGORITHM=COPY
      </code>
      do not
          support
      <code class="literal">
       ALGORITHM=INPLACE
      </code>
      . In this case, an
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE ...
          ALGORITHM=INPLACE
       </code>
      </a>
      operation returns the following
          error:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8088400"><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 output">ERROR 1846 (0A000)<span class="token punctuation">:</span> ALGORITHM=INPLACE is not supported.
Reason<span class="token punctuation">:</span> Cannot change column type INPLACE. Try ALGORITHM=COPY.</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The following limitations are generally applicable to online
          DDL operations on large tables that involve rebuilding the
          table:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         There is no mechanism to pause an online DDL operation or
              to throttle I/O or CPU usage for an online DDL operation.
        </p>
       </li>
       <li class="listitem">
        <p>
         Rollback of an online DDL operation can be expensive
              should the operation fail.
        </p>
       </li>
       <li class="listitem">
        <p>
         Long running online DDL operations can cause replication
              lag. An online DDL operation must finish running on the
              source before it is run on the replica. Also, DML that was
              processed concurrently on the source is only processed on
              the replica after the DDL operation on the replica is
              completed.
        </p>
       </li>
      </ul>
     </div>
     <p>
      For additional information related to running online DDL
          operations on large tables, see
      <a class="xref" href="innodb-online-ddl-performance.html" title="17.12.2 Online DDL Performance and Concurrency">
       Section 17.12.2, “Online DDL Performance and Concurrency”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
