<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="upgrade-prerequisites">
      </a>
      3.6 Preparing Your Installation for Upgrade
     </h2>
    </div>
   </div>
  </div>
  <p>
   Before upgrading to the latest MySQL 8.4 release,
      ensure the upgrade readiness of your current MySQL
      8.3 or MySQL 8.4 server instance by
      performing the preliminary checks described below. The upgrade
      process may fail otherwise.
  </p>
  <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Tip
   </div>
   <p>
    Consider using the
    <a class="ulink" href="/doc/mysql-shell/8.4/en/mysql-shell-utilities-upgrade.html" target="_top">
     MySQL Shell upgrade checker utility
    </a>
    that enables you to verify whether MySQL server instances are ready for upgrade. You can select a target MySQL Server release to which you plan to upgrade, ranging from the MySQL Server 8.0.11 up to the MySQL Server release number that matches the current MySQL Shell release number. The upgrade checker utility carries out the automated checks that are relevant for the specified target release, and advises you of further relevant checks that you should make manually. The upgrade checker works for all GA releases of MySQL 5.7, 8.0, and 8.3. Installation instructions for MySQL Shell can be found
    <a class="ulink" href="/doc/mysql-shell/8.4/en/mysql-shell-install.html" target="_top">
     here
    </a>
    .
   </p>
  </div>
  <p>
   Preliminary checks:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      The following issues must not be present:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         There must be no tables that use obsolete data types or
              functions.
        </p>
       </li>
       <li class="listitem">
        <p>
         There must be no orphan
         <code class="filename">
          .frm
         </code>
         files.
        </p>
       </li>
       <li class="listitem">
        <p>
         Triggers must not have a missing or empty definer or an
              invalid creation context (indicated by the
         <code class="literal">
          character_set_client
         </code>
         ,
         <code class="literal">
          collation_connection
         </code>
         ,
         <code class="literal">
          Database
              Collation
         </code>
         attributes displayed by
         <a class="link" href="show-triggers.html" title="15.7.7.40 SHOW TRIGGERS Statement">
          <code class="literal">
           SHOW TRIGGERS
          </code>
         </a>
         or the
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         <a class="link" href="information-schema-triggers-table.html" title="28.3.44 The INFORMATION_SCHEMA TRIGGERS Table">
          <code class="literal">
           TRIGGERS
          </code>
         </a>
         table). Any such
              triggers must be dumped and restored to fix the issue.
        </p>
       </li>
      </ul>
     </div>
     <p>
      To check for these issues, execute this command:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa82656044"><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-terminal">mysqlcheck <span class="token property">-u</span> root <span class="token property">-p</span> <span class="token property">--all-databases</span> <span class="token property">--check-upgrade</span></code></pre>
     </div>
     <p>
      If
      <a class="link" href="mysqlcheck.html" title="6.5.3 mysqlcheck — A Table Maintenance Program">
       <span class="command">
        <strong>
         mysqlcheck
        </strong>
       </span>
      </a>
      reports any errors, correct
          the issues.
     </p>
    </li>
    <li class="listitem">
     <p>
      There must be no partitioned tables that use a storage engine
          that does not have native partitioning support. To identify
          such tables, execute this query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70815228"><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> TABLE_SCHEMA<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span>
    <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span>
    <span class="token keyword">WHERE</span> <span class="token keyword">ENGINE</span> <span class="token operator">NOT</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token string">'innodb'</span><span class="token punctuation">,</span> <span class="token string">'ndbcluster'</span><span class="token punctuation">)</span>
        <span class="token operator">AND</span> CREATE_OPTIONS <span class="token operator">LIKE</span> <span class="token string">'%partitioned%'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Any table reported by the query must be altered to use
      <code class="literal">
       InnoDB
      </code>
      or be made nonpartitioned. To change
          a table storage engine to
      <code class="literal">
       InnoDB
      </code>
      , execute
          this statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91040654"><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">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table_name</em></span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> INNODB<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For information about converting
      <code class="literal">
       MyISAM
      </code>
      tables to
      <code class="literal">
       InnoDB
      </code>
      , see
      <a class="xref" href="converting-tables-to-innodb.html" title="17.6.1.5 Converting Tables from MyISAM to InnoDB">
       Section 17.6.1.5, “Converting Tables from MyISAM to InnoDB”
      </a>
      .
     </p>
     <p>
      To make a partitioned table nonpartitioned, execute this
          statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89459261"><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">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table_name</em></span> <span class="token keyword">REMOVE</span> <span class="token keyword">PARTITIONING</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Some keywords may be reserved in MySQL 8.4 that
          were not reserved previously. See
      <a class="xref" href="keywords.html" title="11.3 Keywords and Reserved Words">
       Section 11.3, “Keywords and Reserved Words”
      </a>
      .
          This can cause words previously used as identifiers to become
          illegal. To fix affected statements, use identifier quoting.
          See
      <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
       Section 11.2, “Schema Object Names”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      There must be no tables in the MySQL 8.3
      <code class="literal">
       mysql
      </code>
      system database that have the same
          name as a table used by the MySQL 8.4 data
          dictionary. To identify tables with those names, execute this
          query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82936274"><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> TABLE_SCHEMA<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span>
<span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span>
<span class="token keyword">WHERE</span> 
    <span class="token function">LOWER</span><span class="token punctuation">(</span>TABLE_SCHEMA<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'mysql'</span>
    <span class="token operator">AND</span> 
    <span class="token function">LOWER</span><span class="token punctuation">(</span><span class="token keyword">TABLE_NAME</span><span class="token punctuation">)</span> <span class="token keyword">IN</span>
    <span class="token punctuation">(</span>
    <span class="token string">'catalogs'</span><span class="token punctuation">,</span>
    <span class="token string">'character_sets'</span><span class="token punctuation">,</span>
    <span class="token string">'check_constraints'</span><span class="token punctuation">,</span>
    <span class="token string">'collations'</span><span class="token punctuation">,</span>
    <span class="token string">'column_statistics'</span><span class="token punctuation">,</span>
    <span class="token string">'column_type_elements'</span><span class="token punctuation">,</span>
    <span class="token string">'columns'</span><span class="token punctuation">,</span>
    <span class="token string">'dd_properties'</span><span class="token punctuation">,</span>
    <span class="token string">'events'</span><span class="token punctuation">,</span>
    <span class="token string">'foreign_key_column_usage'</span><span class="token punctuation">,</span>
    <span class="token string">'foreign_keys'</span><span class="token punctuation">,</span>
    <span class="token string">'index_column_usage'</span><span class="token punctuation">,</span>
    <span class="token string">'index_partitions'</span><span class="token punctuation">,</span>
    <span class="token string">'index_stats'</span><span class="token punctuation">,</span>
    <span class="token string">'indexes'</span><span class="token punctuation">,</span>
    <span class="token string">'parameter_type_elements'</span><span class="token punctuation">,</span>
    <span class="token string">'parameters'</span><span class="token punctuation">,</span>
    <span class="token string">'resource_groups'</span><span class="token punctuation">,</span>
    <span class="token string">'routines'</span><span class="token punctuation">,</span>
    <span class="token string">'schemata'</span><span class="token punctuation">,</span>
    <span class="token string">'st_spatial_reference_systems'</span><span class="token punctuation">,</span>
    <span class="token string">'table_partition_values'</span><span class="token punctuation">,</span>
    <span class="token string">'table_partitions'</span><span class="token punctuation">,</span>
    <span class="token string">'table_stats'</span><span class="token punctuation">,</span>
    <span class="token string">'tables'</span><span class="token punctuation">,</span>
    <span class="token string">'tablespace_files'</span><span class="token punctuation">,</span>
    <span class="token string">'tablespaces'</span><span class="token punctuation">,</span>
    <span class="token string">'triggers'</span><span class="token punctuation">,</span>
    <span class="token string">'view_routine_usage'</span><span class="token punctuation">,</span>
    <span class="token string">'view_table_usage'</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Any tables reported by the query must be dropped or renamed
          (use
      <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
       <code class="literal">
        RENAME TABLE
       </code>
      </a>
      ). This may
          also entail changes to applications that use the affected
          tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      There must be no tables that have foreign key constraint names
          longer than 64 characters. Use this query to identify tables
          with constraint names that are too long:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45402852"><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> TABLE_SCHEMA<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span>
<span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span>
<span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token keyword">IN</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">LEFT</span><span class="token punctuation">(</span><span class="token function">SUBSTR</span><span class="token punctuation">(</span>ID<span class="token punctuation">,</span><span class="token function">INSTR</span><span class="token punctuation">(</span>ID<span class="token punctuation">,</span><span class="token string">'/'</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
               <span class="token function">INSTR</span><span class="token punctuation">(</span><span class="token function">SUBSTR</span><span class="token punctuation">(</span>ID<span class="token punctuation">,</span><span class="token function">INSTR</span><span class="token punctuation">(</span>ID<span class="token punctuation">,</span><span class="token string">'/'</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'_ibfk_'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span>
   <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_SYS_FOREIGN
   <span class="token keyword">WHERE</span> <span class="token function">LENGTH</span><span class="token punctuation">(</span><span class="token function">SUBSTR</span><span class="token punctuation">(</span>ID<span class="token punctuation">,</span><span class="token function">INSTR</span><span class="token punctuation">(</span>ID<span class="token punctuation">,</span><span class="token string">'/'</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">&gt;</span><span class="token number">64</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For a table with a constraint name that exceeds 64 characters,
          drop the constraint and add it back with constraint name that
          does not exceed 64 characters (use
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
          TABLE
       </code>
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      There must be no obsolete SQL modes defined by
      <a class="link" href="server-system-variables.html#sysvar_sql_mode">
       <code class="literal">
        sql_mode
       </code>
      </a>
      system variable.
          Attempting to use an obsolete SQL mode prevents MySQL
          8.4 from starting. Applications that use obsolete
          SQL modes should be revised to avoid them. For information
          about SQL modes removed in MySQL 8.4, see
      <a class="ulink" href="/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-server-changes" target="_top">
       Server Changes
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      There must be no views with explicitly defined columns names
          that exceed 64 characters (views with column names up to 255
          characters were permitted in MySQL 5.7). To avoid upgrade
          errors, such views should be altered before upgrading.
          Currently, the only method of identify views with column names
          that exceed 64 characters is to inspect the view definition
          using
      <a class="link" href="show-create-view.html" title="15.7.7.14 SHOW CREATE VIEW Statement">
       <code class="literal">
        SHOW CREATE VIEW
       </code>
      </a>
      . You can
          also inspect view definitions by querying the Information
          Schema
      <a class="link" href="information-schema-views-table.html" title="28.3.47 The INFORMATION_SCHEMA VIEWS Table">
       <code class="literal">
        VIEWS
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      There must be no tables or stored procedures with individual
      <code class="literal">
       ENUM
      </code>
      or
      <code class="literal">
       SET
      </code>
      column
          elements that exceed 255 characters or 1020 bytes in length.
          Prior to MySQL 8.4, the maximum combined length
          of
      <code class="literal">
       ENUM
      </code>
      or
      <code class="literal">
       SET
      </code>
      column
          elements was 64K. In MySQL 8.4, the maximum
          character length of an individual
      <code class="literal">
       ENUM
      </code>
      or
      <code class="literal">
       SET
      </code>
      column element is 255 characters, and
          the maximum byte length is 1020 bytes. (The 1020 byte limit
          supports multibyte character sets). Before upgrading to MySQL
          8.0, modify any
      <code class="literal">
       ENUM
      </code>
      or
      <code class="literal">
       SET
      </code>
      column elements that exceed the new
          limits. Failing to do so causes the upgrade to fail with an
          error.
     </p>
    </li>
    <li class="listitem">
     <p>
      Your MySQL 8.3 installation must not use
          features that are not supported by MySQL 8.4. Any
          changes here are necessarily installation specific, but the
          following example illustrates the kind of thing to look for:
     </p>
     <p>
      Some server startup options and system variables have been
          removed in MySQL 8.4. See
      <a class="xref" href="mysql-nutshell.html#mysql-nutshell-removals" title="Features Removed in MySQL 8.4">
       Features Removed in MySQL 8.4
      </a>
      , and
      <a class="xref" href="added-deprecated-removed.html" title="1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.4 since 8.0">
       Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in
    MySQL 8.4 since 8.0”
      </a>
      . If you use any of
          these, an upgrade requires configuration changes.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you intend to change the
      <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
       <code class="literal">
        lower_case_table_names
       </code>
      </a>
      setting to 1 at upgrade time, ensure that schema and table
          names are lowercase before upgrading. Otherwise, a failure
          could occur due to a schema or table name lettercase mismatch.
          You can use the following queries to check for schema and
          table names containing uppercase characters:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56866208"><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 keyword">TABLE_NAME</span><span class="token punctuation">,</span> <span class="token function">if</span><span class="token punctuation">(</span><span class="token function">sha</span><span class="token punctuation">(</span><span class="token keyword">TABLE_NAME</span><span class="token punctuation">)</span> <span class="token operator">!=</span><span class="token function">sha</span><span class="token punctuation">(</span><span class="token function">lower</span><span class="token punctuation">(</span><span class="token keyword">TABLE_NAME</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'Yes'</span><span class="token punctuation">,</span><span class="token string">'No'</span><span class="token punctuation">)</span> <span class="token keyword">as</span> UpperCase <span class="token keyword">from</span> information_schema<span class="token punctuation">.</span><span class="token keyword">tables</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      If
      <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
       <code class="literal">
        lower_case_table_names=1
       </code>
      </a>
      ,
          table and schema names are checked by the upgrade process to
          ensure that all characters are lowercase. If table or schema
          names are found to contain uppercase characters, the upgrade
          process fails with an error.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Changing the
       <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
        <code class="literal">
         lower_case_table_names
        </code>
       </a>
       setting at upgrade time is not recommended.
      </p>
     </div>
    </li>
   </ol>
  </div>
  <p>
   If upgrade to MySQL 8.4 fails due to any of the
      issues outlined above, the server reverts all changes to the data
      directory. In this case, remove all redo log files and restart the
      MySQL 8.3 server on the existing data directory to
      address the errors. The redo log files
      (
   <code class="filename">
    ib_logfile*
   </code>
   ) reside in the MySQL data
      directory by default. After the errors are fixed, perform a slow
      shutdown (by setting
   <a class="link" href="innodb-parameters.html#sysvar_innodb_fast_shutdown">
    <code class="literal">
     innodb_fast_shutdown=0
    </code>
   </a>
   ) before
      attempting the upgrade again.
  </p>
 </div>
 <br/>
</div>
