<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="known-issues">
      </a>
      B.3.7 Known Issues in MySQL
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045053320000">
  </a>
  <a class="indexterm" name="idm46045053318512">
  </a>
  <a class="indexterm" name="idm46045053317024">
  </a>
  <a class="indexterm" name="idm46045053315536">
  </a>
  <p>
   This section lists known issues in recent versions of MySQL.
  </p>
  <p>
   For information about platform-specific issues, see the
        installation and debugging instructions in
   <a class="xref" href="general-installation-issues.html" title="2.1 General Installation Guidance">
    Section 2.1, “General Installation Guidance”
   </a>
   , and
   <a class="xref" href="debugging-mysql.html" title="7.9 Debugging MySQL">
    Section 7.9, “Debugging MySQL”
   </a>
   .
  </p>
  <p>
   The following problems are known:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Subquery optimization for
      <code class="literal">
       IN
      </code>
      is not as
            effective as for
      <code class="literal">
       =
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Even if you use
      <code class="literal">
       lower_case_table_names=2
      </code>
      (which enables MySQL to remember the case used for databases
            and table names), MySQL does not remember the case used for
            database names for the function
      <a class="link" href="information-functions.html#function_database">
       <code class="literal">
        DATABASE()
       </code>
      </a>
      or within the
            various logs (on case-insensitive systems).
     </p>
    </li>
    <li class="listitem">
     <p>
      Dropping a
      <code class="literal">
       FOREIGN KEY
      </code>
      constraint does
            not work in replication because the constraint may have
            another name on the replica.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      (and
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      with the
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      option) does not
            trigger
      <code class="literal">
       ON DELETE CASCADE
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DISTINCT
      </code>
      with
      <code class="literal">
       ORDER BY
      </code>
      does not work inside
      <a class="link" href="aggregate-functions.html#function_group-concat">
       <code class="literal">
        GROUP_CONCAT()
       </code>
      </a>
      if you do not
            use all and only those columns that are in the
      <code class="literal">
       DISTINCT
      </code>
      list.
     </p>
    </li>
    <li class="listitem">
     <p>
      When inserting a big integer value (between
            2
      <sup>
       63
      </sup>
      and
            2
      <sup>
       64
      </sup>
      −1) into a decimal or
            string column, it is inserted as a negative value because
            the number is evaluated in signed integer context.
     </p>
    </li>
    <li class="listitem">
     <p>
      With statement-based binary logging, the source server
            writes the executed queries to the binary log. This is a
            very fast, compact, and efficient logging method that works
            perfectly in most cases. However, it is possible for the
            data on the source and replica to become different if a
            query is designed in such a way that the data modification
            is nondeterministic (generally not a recommended practice,
            even outside of replication).
     </p>
     <p>
      For example:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <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>
         or
         <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
          <code class="literal">
           INSERT
                ... SELECT
          </code>
         </a>
         statements that insert zero or
         <code class="literal">
          NULL
         </code>
         values into an
         <code class="literal">
          AUTO_INCREMENT
         </code>
         column.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
          <code class="literal">
           DELETE
          </code>
         </a>
         if you are
                deleting rows from a table that has foreign keys with
         <code class="literal">
          ON DELETE CASCADE
         </code>
         properties.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
          <code class="literal">
           REPLACE ...
                SELECT
          </code>
         </a>
         ,
         <code class="literal">
          INSERT IGNORE ...
                SELECT
         </code>
         if you have duplicate key values in the
                inserted data.
        </p>
       </li>
      </ul>
     </div>
     <p>
      <span class="bold">
       <strong>
        If and only if the preceding queries
            have no
        <code class="literal">
         ORDER BY
        </code>
        clause guaranteeing a
            deterministic order
       </strong>
      </span>
      .
     </p>
     <p>
      For example, for
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT ...
            SELECT
       </code>
      </a>
      with no
      <code class="literal">
       ORDER BY
      </code>
      , the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      may return rows in a
            different order (which results in a row having different
            ranks, hence getting a different number in the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column), depending on the
            choices made by the optimizers on the source and replica.
     </p>
     <p>
      A query is optimized differently on the source and replica
            only if:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The table is stored using a different storage engine on
                the source than on the replica. (It is possible to use
                different storage engines on the source and replica. For
                example, you can use
         <code class="literal">
          InnoDB
         </code>
         on the
                source, but
         <code class="literal">
          MyISAM
         </code>
         on the replica if
                the replica has less available disk space.)
        </p>
       </li>
       <li class="listitem">
        <p>
         MySQL buffer sizes
                (
         <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
          <code class="literal">
           key_buffer_size
          </code>
         </a>
         , and
                so on) are different on the source and replica.
        </p>
       </li>
       <li class="listitem">
        <p>
         The source and replica run different MySQL versions, and
                the optimizer code differs between these versions.
        </p>
       </li>
      </ul>
     </div>
     <p>
      This problem may also affect database restoration using
      <span class="command">
       <strong>
        mysqlbinlog|mysql
       </strong>
      </span>
      .
     </p>
     <p>
      The easiest way to avoid this problem is to add an
      <code class="literal">
       ORDER BY
      </code>
      clause to the aforementioned
            nondeterministic queries to ensure that the rows are always
            stored or modified in the same order. Using row-based or
            mixed logging format also avoids the problem.
     </p>
    </li>
    <li class="listitem">
     <p>
      Log file names are based on the server host name if you do
            not specify a file name with the startup option. To retain
            the same log file names if you change your host name to
            something else, you must explicitly use options such as
      <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
       <code class="option">
        --log-bin=
        <em class="replaceable">
         <code>
          old_host_name
         </code>
        </em>
        -bin
       </code>
      </a>
      .
            See
      <a class="xref" href="server-options.html" title="7.1.7 Server Command Options">
       Section 7.1.7, “Server Command Options”
      </a>
      . Alternatively, rename
            the old files to reflect your host name change. If these are
            binary logs, you must edit the binary log index file and fix
            the binary log file names there as well. (The same is true
            for the relay logs on a replica.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <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>
      does not delete temporary
            files left after a
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      statement. See
      <a class="xref" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
       Section 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RENAME
      </code>
      does not work with
      <code class="literal">
       TEMPORARY
      </code>
      tables or tables used in a
      <code class="literal">
       MERGE
      </code>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      When using
      <code class="literal">
       SET CHARACTER SET
      </code>
      , you cannot
            use translated characters in database, table, and column
            names.
     </p>
    </li>
    <li class="listitem">
     <p>
      The server uses only the first
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      bytes when
            comparing data values. This means that values cannot
            reliably be used in
      <code class="literal">
       GROUP BY
      </code>
      ,
      <code class="literal">
       ORDER BY
      </code>
      , or
      <code class="literal">
       DISTINCT
      </code>
      if they differ only after the first
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      bytes. To
            work around this, increase the variable value. The default
            value of
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      is
            1024 and can be changed at server startup time or at
            runtime.
     </p>
    </li>
    <li class="listitem">
     <p>
      Numeric calculations are done with
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        BIGINT
       </code>
      </a>
      or
      <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
       <code class="literal">
        DOUBLE
       </code>
      </a>
      (both are normally 64
            bits long). Which precision you get depends on the function.
            The general rule is that bit functions are performed with
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        BIGINT
       </code>
      </a>
      precision,
      <a class="link" href="flow-control-functions.html#function_if">
       <code class="literal">
        IF()
       </code>
      </a>
      and
      <a class="link" href="string-functions.html#function_elt">
       <code class="literal">
        ELT()
       </code>
      </a>
      with
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        BIGINT
       </code>
      </a>
      or
      <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
       <code class="literal">
        DOUBLE
       </code>
      </a>
      precision, and the
            rest with
      <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
       <code class="literal">
        DOUBLE
       </code>
      </a>
      precision.
            You should try to avoid using unsigned long long values if
            they resolve to be larger than 63 bits (9223372036854775807)
            for anything other than bit fields.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can have up to 255
      <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
       <code class="literal">
        ENUM
       </code>
      </a>
      and
      <a class="link" href="set.html" title="13.3.6 The SET Type">
       <code class="literal">
        SET
       </code>
      </a>
      columns in one table.
     </p>
    </li>
    <li class="listitem">
     <p>
      In
      <a class="link" href="aggregate-functions.html#function_min">
       <code class="literal">
        MIN()
       </code>
      </a>
      ,
      <a class="link" href="aggregate-functions.html#function_max">
       <code class="literal">
        MAX()
       </code>
      </a>
      , and other aggregate
            functions, MySQL currently compares
      <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
       <code class="literal">
        ENUM
       </code>
      </a>
      and
      <a class="link" href="set.html" title="13.3.6 The SET Type">
       <code class="literal">
        SET
       </code>
      </a>
      columns by their string
            value rather than by the string's relative position in the
            set.
     </p>
    </li>
    <li class="listitem">
     <p>
      In an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      statement,
            columns are updated from left to right. If you refer to an
            updated column, you get the updated value instead of the
            original value. For example, the following statement
            increments
      <code class="literal">
       KEY
      </code>
      by
      <code class="literal">
       2
      </code>
      ,
      <span class="bold">
       <strong>
        not
       </strong>
      </span>
      <code class="literal">
       1
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80669013"><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">UPDATE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">SET</span> <span class="token keyword">KEY</span><span class="token operator">=</span><span class="token keyword">KEY</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">KEY</span><span class="token operator">=</span><span class="token keyword">KEY</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      You can refer to multiple temporary tables in the same
            query, but you cannot refer to any given temporary table
            more than once. For example, the following does not work:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80698939"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> temp_table<span class="token punctuation">,</span> temp_table <span class="token keyword">AS</span> t2<span class="token punctuation">;</span>
<span class="token output">ERROR 1137<span class="token punctuation">:</span> Can't reopen table<span class="token punctuation">:</span> 'temp_table'</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The optimizer may handle
      <code class="literal">
       DISTINCT
      </code>
      differently when you are using
      <span class="quote">
       “
       <span class="quote">
        hidden
       </span>
       ”
      </span>
      columns
            in a join than when you are not. In a join, hidden columns
            are counted as part of the result (even if they are not
            shown), whereas in normal queries, hidden columns do not
            participate in the
      <code class="literal">
       DISTINCT
      </code>
      comparison.
     </p>
     <p>
      An example of this is:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51942486"><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 keyword">DISTINCT</span> mp3id <span class="token keyword">FROM</span> band_downloads
       <span class="token keyword">WHERE</span> userid <span class="token operator">=</span> <span class="token number">9</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id <span class="token keyword">DESC</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      and
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59504686"><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 keyword">DISTINCT</span> band_downloads<span class="token punctuation">.</span>mp3id
       <span class="token keyword">FROM</span> band_downloads<span class="token punctuation">,</span>band_mp3
       <span class="token keyword">WHERE</span> band_downloads<span class="token punctuation">.</span>userid <span class="token operator">=</span> <span class="token number">9</span>
       <span class="token operator">AND</span> band_mp3<span class="token punctuation">.</span>id <span class="token operator">=</span> band_downloads<span class="token punctuation">.</span>mp3id
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> band_downloads<span class="token punctuation">.</span>id <span class="token keyword">DESC</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In the second case, you may get two identical rows in the
            result set (because the values in the hidden
      <code class="literal">
       id
      </code>
      column may differ).
     </p>
     <p>
      Note that this happens only for queries that do not have the
      <code class="literal">
       ORDER BY
      </code>
      columns in the result.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you execute a
      <code class="literal">
       PROCEDURE
      </code>
      on a query
            that returns an empty set, in some cases the
      <code class="literal">
       PROCEDURE
      </code>
      does not transform the columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      Creation of a table of type
      <code class="literal">
       MERGE
      </code>
      does
            not check whether the underlying tables are compatible
            types.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      to add
            a
      <code class="literal">
       UNIQUE
      </code>
      index to a table used in a
      <code class="literal">
       MERGE
      </code>
      table and then add a normal index
            on the
      <code class="literal">
       MERGE
      </code>
      table, the key order is
            different for the tables if there was an old,
            non-
      <code class="literal">
       UNIQUE
      </code>
      key in the table. This is
            because
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      puts
      <code class="literal">
       UNIQUE
      </code>
      indexes before normal indexes to
            be able to detect duplicate keys as early as possible.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
