<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="data-dictionary-schema">
      </a>
      16.1 Data Dictionary Schema
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045167802432">
  </a>
  <p>
   Data dictionary tables are protected and may only be accessed in
      debug builds of MySQL. However, MySQL supports access to data
      stored in data dictionary tables through
   <a class="link" href="information-schema.html" title="Chapter 28 INFORMATION_SCHEMA Tables">
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
   </a>
   tables and
   <a class="link" href="show.html" title="15.7.7 SHOW Statements">
    <code class="literal">
     SHOW
    </code>
   </a>
   statements. For an overview of
      the tables that comprise the data dictionary, see
   <a class="xref" href="system-schema.html#system-schema-data-dictionary-tables" title="Data Dictionary Tables">
    Data Dictionary Tables
   </a>
   .
  </p>
  <p>
   MySQL system tables still exist in MySQL 8.4 and can
      be viewed by issuing a
   <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    <code class="literal">
     SHOW TABLES
    </code>
   </a>
   statement on the
   <code class="literal">
    mysql
   </code>
   system database.
      Generally, the difference between MySQL data dictionary tables and
      system tables is that data dictionary tables contain metadata
      required to execute SQL queries, whereas system tables contain
      auxiliary data such as time zone and help information. MySQL
      system tables and data dictionary tables also differ in how they
      are upgraded. The MySQL server manages data dictionary upgrades.
      See
   <a class="xref" href="data-dictionary-schema.html#data-dictionary-upgrade" title="How the Data Dictionary is Upgraded">
    How the Data Dictionary is Upgraded
   </a>
   . Upgrading MySQL
      system tables requires running the full MySQL upgrade procedure.
      See
   <a class="xref" href="upgrading-what-is-upgraded.html" title="3.4 What the MySQL Upgrade Process Upgrades">
    Section 3.4, “What the MySQL Upgrade Process Upgrades”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="data-dictionary-upgrade">
       </a>
       How the Data Dictionary is Upgraded
      </h3>
     </div>
    </div>
   </div>
   <p>
    New versions of MySQL may include changes to data dictionary
        table definitions. Such changes are present in newly installed
        versions of MySQL, but when performing an in-place upgrade of
        MySQL binaries, changes are applied when the MySQL server is
        restarted using the new binaries. At startup, the data
        dictionary version of the server is compared to the version
        information stored in the data dictionary to determine if data
        dictionary tables should be upgraded. If an upgrade is necessary
        and supported, the server creates data dictionary tables with
        updated definitions, copies persisted metadata to the new
        tables, atomically replaces the old tables with the new ones,
        and reinitializes the data dictionary. If an upgrade is not
        necessary, startup continues without updating the data
        dictionary tables.
   </p>
   <p>
    Upgrade of data dictionary tables is an atomic operation, which
        means that all of the data dictionary tables are upgraded as
        necessary or the operation fails. If the upgrade operation
        fails, server startup fails with an error. In this case, the old
        server binaries can be used with the old data directory to start
        the server. When the new server binaries are used again to start
        the server, the data dictionary upgrade is reattempted.
   </p>
   <p>
    Generally, after data dictionary tables are successfully
        upgraded, it is not possible to restart the server using the old
        server binaries. As a result, downgrading MySQL server binaries
        to a previous MySQL version is not supported after data
        dictionary tables are upgraded.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="viewing-data-dictionary-tables">
       </a>
       Viewing Data Dictionary Tables Using a Debug Build of MySQL
      </h3>
     </div>
    </div>
   </div>
   <p>
    Data dictionary tables are protected by default but can be
        accessed by compiling MySQL with debugging support (using the
    <a class="link" href="source-configuration-options.html#option_cmake_with_debug">
     <code class="option">
      -DWITH_DEBUG=1
     </code>
    </a>
    <span class="command">
     <strong>
      CMake
     </strong>
    </span>
    option) and specifying the
    <code class="literal">
     +d,skip_dd_table_access_check
    </code>
    <a class="link" href="server-options.html#option_mysqld_debug">
     <code class="option">
      debug
     </code>
    </a>
    option and modifier. For
        information about compiling debug builds, see
    <a class="xref" href="compiling-for-debugging.html" title="7.9.1.1 Compiling MySQL for Debugging">
     Section 7.9.1.1, “Compiling MySQL for Debugging”
    </a>
    .
   </p>
   <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Warning
    </div>
    <p>
     Modifying or writing to data dictionary tables directly is not
          recommended and may render your MySQL instance inoperable.
    </p>
   </div>
   <p>
    After compiling MySQL with debugging support, use this
    <a class="link" href="set.html" title="13.3.6 The SET Type">
     <code class="literal">
      SET
     </code>
    </a>
    statement to make data
        dictionary tables visible to the
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    client
        session:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa40740751"><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">SET</span> <span class="token keyword">SESSION</span> debug<span class="token operator">=</span><span class="token string">'+d,skip_dd_table_access_check'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Use this query to retrieve a list of data dictionary tables:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20269483"><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">name</span><span class="token punctuation">,</span> schema_id<span class="token punctuation">,</span> hidden<span class="token punctuation">,</span> <span class="token keyword">type</span> <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span><span class="token keyword">tables</span> <span class="token keyword">where</span> schema_id<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> hidden<span class="token operator">=</span><span class="token string">'System'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Use
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW CREATE TABLE
     </code>
    </a>
    to view
        data dictionary table definitions. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92260262"><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">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mysql<span class="token punctuation">.</span>catalogs\G</code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
