<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="data-dictionary-usage-differences">
      </a>
      16.7 Data Dictionary Usage Differences
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045167581712">
  </a>
  <p>
   Use of a data dictionary-enabled MySQL server entails some
      operational differences compared to a server that does not have a
      data dictionary:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Previously, enabling the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
       <code class="literal">
        innodb_read_only
       </code>
      </a>
      system
          variable prevented creating and dropping tables only for the
      <code class="literal">
       InnoDB
      </code>
      storage engine. As of MySQL
          8.4, enabling
      <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
       <code class="literal">
        innodb_read_only
       </code>
      </a>
      prevents
          these operations for all storage engines. Table creation and
          drop operations for any storage engine modify data dictionary
          tables in the
      <code class="literal">
       mysql
      </code>
      system database, but
          those tables use the
      <code class="literal">
       InnoDB
      </code>
      storage engine
          and cannot be modified when
      <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
       <code class="literal">
        innodb_read_only
       </code>
      </a>
      is enabled.
          The same principle applies to other table operations that
          require modifying data dictionary tables. Examples:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
          <code class="literal">
           ANALYZE TABLE
          </code>
         </a>
         fails because
              it updates table statistics, which are stored in the data
              dictionary.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
           <em class="replaceable">
            <code>
             tbl_name
            </code>
           </em>
           ENGINE=
           <em class="replaceable">
            <code>
             engine_name
            </code>
           </em>
          </code>
         </a>
         fails because it updates the storage engine designation,
              which is stored in the data dictionary.
        </p>
       </li>
      </ul>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Enabling
       <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
        <code class="literal">
         innodb_read_only
        </code>
       </a>
       also has important implications for non-data dictionary
            tables in the
       <code class="literal">
        mysql
       </code>
       system database. For
            details, see the description of
       <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
        <code class="literal">
         innodb_read_only
        </code>
       </a>
       in
       <a class="xref" href="innodb-parameters.html" title="17.14 InnoDB Startup Options and System Variables">
        Section 17.14, “InnoDB Startup Options and System Variables”
       </a>
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Previously, tables in the
      <code class="literal">
       mysql
      </code>
      system
          database were visible to DML and DDL statements. As of MySQL
          8.4, data dictionary tables are invisible and
          cannot be modified or queried directly. However, in most cases
          there are corresponding
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      tables that can be queried instead. This enables the
          underlying data dictionary tables to be changed as server
          development proceeds, while maintaining a stable
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      interface for
          application use.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      tables in MySQL
          8.4 are closely tied to the data dictionary,
          resulting in several usage differences:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Previously,
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         queries
              for table statistics in the
         <a class="link" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
          <code class="literal">
           STATISTICS
          </code>
         </a>
         and
         <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
          <code class="literal">
           TABLES
          </code>
         </a>
         tables retrieved
              statistics directly from storage engines. As of MySQL
              8.4, cached table statistics are used by
              default. The
         <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
          <code class="literal">
           information_schema_stats_expiry
          </code>
         </a>
         system variable defines the period of time before cached
              table statistics expire. The default is 86400 seconds (24
              hours). (To update the cached values at any time for a
              given table, use
         <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
          <code class="literal">
           ANALYZE
              TABLE
          </code>
         </a>
         .) If there are no cached statistics or
              statistics have expired, statistics are retrieved from
              storage engines when querying table statistics columns. To
              always retrieve the latest statistics directly from
              storage engines, set
         <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
          <code class="literal">
           information_schema_stats_expiry
          </code>
         </a>
         to
         <code class="literal">
          0
         </code>
         . For more information, see
         <a class="xref" href="information-schema-optimization.html" title="10.2.3 Optimizing INFORMATION_SCHEMA Queries">
          Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         Several
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         tables are
              views on data dictionary tables, which enables the
              optimizer to use indexes on those underlying tables.
              Consequently, depending on optimizer choices, the row
              order of results for
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         queries might differ from previous results. If a query
              result must have specific row ordering characteristics,
              include an
         <code class="literal">
          ORDER BY
         </code>
         clause.
        </p>
       </li>
       <li class="listitem">
        <p>
         Queries on
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         tables
              may return column names in a different lettercase than in
              earlier MySQL series. Applications should test result set
              column names in case-insensitive fashion. If that is not
              feasible, a workaround is to use column aliases in the
              select list that return column names in the required
              lettercase. For example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56554258"><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 keyword">AS</span> table_schema<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span> <span class="token keyword">AS</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 operator">=</span> <span class="token string">'users'</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
          <span class="command">
           <strong>
            mysqldump
           </strong>
          </span>
         </a>
         no longer dumps the
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         database, even if
              explicitly named on the command line.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="create-table-like.html" title="15.1.20.3 CREATE TABLE ... LIKE Statement">
          <code class="literal">
           CREATE
              TABLE
           <em class="replaceable">
            <code>
             dst_tbl
            </code>
           </em>
           LIKE
           <em class="replaceable">
            <code>
             src_tbl
            </code>
           </em>
          </code>
         </a>
         requires that
         <em class="replaceable">
          <code>
           src_tbl
          </code>
         </em>
         be a base table and
              fails if it is an
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         table that is a view on data dictionary tables.
        </p>
       </li>
       <li class="listitem">
        <p>
         Previously, result set headers of columns selected from
         <code class="literal">
          INFORMATION_SCHEMA
         </code>
         tables used the
              capitalization specified in the query. This query produces
              a result set with a header of
         <code class="literal">
          table_name
         </code>
         :
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa12679201"><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">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 punctuation">;</span></code></pre>
        </div>
        <p>
         As of MySQL 8.4, these headers are
              capitalized; the preceding query produces a result set
              with a header of
         <code class="literal">
          TABLE_NAME
         </code>
         . If
              necessary, a column alias can be used to achieve a
              different lettercase. For example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95232025"><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">table_name</span> <span class="token keyword">AS</span> <span class="token string">'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 punctuation">;</span></code></pre>
        </div>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      The data directory affects how
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      dumps information from the
      <code class="literal">
       mysql
      </code>
      system
          database:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
          <span class="command">
           <strong>
            mysqldump
           </strong>
          </span>
         </a>
         only dumps non-data
              dictionary tables in that database, when previously it was
              possible to dump all tables in the
         <code class="literal">
          mysql
         </code>
         system database.
        </p>
       </li>
       <li class="listitem">
        <p>
         Previously, the
         <a class="link" href="mysqldump.html#option_mysqldump_routines">
          <code class="option">
           --routines
          </code>
         </a>
         and
         <a class="link" href="mysqldump.html#option_mysqldump_events">
          <code class="option">
           --events
          </code>
         </a>
         options were
              not required to include stored routines and events when
              using the
         <a class="link" href="mysqldump.html#option_mysqldump_all-databases">
          <code class="option">
           --all-databases
          </code>
         </a>
         option:
              The dump included the
         <code class="literal">
          mysql
         </code>
         system
              database, and therefore also the
         <code class="literal">
          proc
         </code>
         and
         <code class="literal">
          event
         </code>
         tables containing stored
              routine and event definitions. As of MySQL
              8.4, the
         <code class="literal">
          event
         </code>
         and
         <code class="literal">
          proc
         </code>
         tables are not used. Definitions
              for the corresponding objects are stored in data
              dictionary tables, but those tables are not dumped. To
              include stored routines and events in a dump made using
         <a class="link" href="mysqldump.html#option_mysqldump_all-databases">
          <code class="option">
           --all-databases
          </code>
         </a>
         , use the
         <a class="link" href="mysqldump.html#option_mysqldump_routines">
          <code class="option">
           --routines
          </code>
         </a>
         and
         <a class="link" href="mysqldump.html#option_mysqldump_events">
          <code class="option">
           --events
          </code>
         </a>
         options
              explicitly.
        </p>
       </li>
       <li class="listitem">
        <p>
         Previously, the
         <a class="link" href="mysqldump.html#option_mysqldump_routines">
          <code class="option">
           --routines
          </code>
         </a>
         option
              required the
         <a class="link" href="privileges-provided.html#priv_select">
          <code class="literal">
           SELECT
          </code>
         </a>
         privilege for the
         <code class="literal">
          proc
         </code>
         table. As of
              MySQL 8.4, that table is not used;
         <a class="link" href="mysqldump.html#option_mysqldump_routines">
          <code class="option">
           --routines
          </code>
         </a>
         requires the
              global
         <a class="link" href="privileges-provided.html#priv_select">
          <code class="literal">
           SELECT
          </code>
         </a>
         privilege
              instead.
        </p>
       </li>
       <li class="listitem">
        <p>
         Previously, it was possible to dump stored routine and
              event definitions together with their creation and
              modification timestamps, by dumping the
         <code class="literal">
          proc
         </code>
         and
         <code class="literal">
          event
         </code>
         tables. As of MySQL 8.4, those tables are not
              used, so it is not possible to dump timestamps.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Previously, creating a stored routine that contains illegal
          characters produced a warning. As of MySQL 8.4,
          this is an error.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
