Table of Contents
MySQL Server incorporates a transactional data dictionary that stores information about database objects. In previous MySQL releases, dictionary data was stored in metadata files, nontransactional tables, and storage engine-specific data dictionaries.
This chapter describes the main features, benefits, usage differences, and limitations of the data dictionary. For other implications of the data dictionary feature, refer to the “Data Dictionary Notes” section in the MySQL 8.0 Release Notes.
Benefits of the MySQL data dictionary include:
Simplicity of a centralized data dictionary schema that uniformly stores dictionary data. See Section 14.1, “Data Dictionary Schema”.
Removal of file-based metadata storage. See Section 14.2, “Removal of File-based Metadata Storage”.
Transactional, crash-safe storage of dictionary data. See Section 14.3, “Transactional Storage of Dictionary Data”.
Uniform and centralized caching for dictionary objects. See Section 14.4, “Dictionary Object Cache”.
A simpler and improved implementation for some
INFORMATION_SCHEMA tables. See
Section 14.5, “INFORMATION_SCHEMA and Data Dictionary Integration”.
Atomic DDL. See Section 13.1.1, “Atomic Data Definition Statement Support”.
A data dictionary-enabled server entails some general operational differences compared to a server that does not have a data dictionary; see Section 14.7, “Data Dictionary Usage Differences”. Also, for upgrades to MySQL 8.0, the upgrade procedure differs somewhat from previous MySQL releases and requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, see Section 2.10.1, “Upgrading MySQL”, particularly Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.
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
INFORMATION_SCHEMA tables and
SHOW statements. For an overview of
the tables that comprise the data dictionary, see
Data Dictionary Tables.
MySQL system tables still exist in MySQL 8.0 and can
be viewed by issuing a
statement on the
mysql system database.
Generally, the difference between MySQL system tables and data
dictionary tables is that system tables contain auxiliary data
such as time zone and help information, whereas data dictionary
tables contain data required to execute SQL queries. MySQL system
tables and data dictionary tables also differ in how they are
upgraded. Upgrading MySQL system tables requires running
mysql_upgrade. Data dictionary upgrades are
managed by the MySQL server. See
How the Data Dictionary is Upgraded.
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.
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.
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.
--no-dd-upgrade option can be
used to prevent automatic upgrade of data dictionary tables at
specified, and the server finds that the data dictionary version
of the server is different from the version stored in the data
dictionary, startup fails with an error stating that the data
dictionary upgrade is prohibited.
Data dictionary tables are protected by default but can be
accessed by compiling MySQL with debugging support (using the
CMake option) and specifying the
debug option and modifier. For
information about compiling debug builds, see
Section 18.104.22.168, “Compiling MySQL for Debugging”.
Modifying or writing to data dictionary tables directly is not recommended and may render your MySQL instance inoperable.
SET SESSION debug='+d,skip_dd_table_access_check';
Use this query to retrieve a list of data dictionary tables:
SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
SHOW CREATE TABLE to view
data dictionary table definitions. For example:
SHOW CREATE TABLE mysql.catalogs\G
In previous MySQL releases, dictionary data was partially stored in metadata files. Issues with file-based metadata storage included expensive file scans, susceptibility to file system-related bugs, complex code for handling of replication and crash recovery failure states, and a lack of extensibility that made it difficult to add metadata for new features and relational objects.
The metadata files listed below are removed from MySQL. Unless otherwise noted, data previously stored in metadata files is now stored in data dictionary tables.
.frm files: Table metadata files. With
the removal of
The 64KB table definition size limit imposed by the
.frm file structure is removed.
VERSION column reports a hardcoded
10, which is the last
frm file version used in MySQL 5.7.
.par files: Partition definition files.
InnoDB stopped using partition definition
files in MySQL 5.7 with the introduction of native
partitioning support for
.TRN files: Trigger namespace files.
.TRG files: Trigger parameter files.
Symbolic Link files containing the location of
tablespace files created outside of the data directory.
db.opt files: Database configuration
files. These files, one per database directory, contained
database default character set attributes.
The data dictionary schema stores dictionary data in transactional
InnoDB) tables. Data dictionary tables are
located in the
mysql database together with
non-data dictionary system tables.
Data dictionary tables are created in a single
InnoDB tablespace named
mysql.ibd, which resides in the MySQL data
mysql.ibd tablespace file must
reside in the MySQL data directory and its name cannot be modified
or used by another tablespace.
Dictionary data is protected by the same commit, rollback, and
crash-recovery capabilities that protect user data that is stored
The dictionary object cache is a shared global cache that stores previously accessed data dictionary objects in memory to enable object reuse and minimize disk I/O. Similar to other cache mechanisms used by MySQL, the dictionary object cache uses an LRU-based eviction strategy to evict least recently used objects from memory.
The dictionary object cache comprises cache partitions that store different object types. Some cache partition size limits are configurable, whereas others are hardcoded.
tablespace definition cache
partition: Stores tablespace definition objects.
option sets a limit for the number of tablespace definition
objects that can be stored in the dictionary object cache. The
default value is 256.
schema definition cache
partition: Stores schema definition objects. The
option sets a limit for the number of schema definition
objects that can be stored in the dictionary object cache. The
default value is 256.
table definition cache
partition: Stores table definition objects. The
object limit is set to the value of
max_connections, which has a
default value of 151.
The table definition cache partition exists in parallel with
the table definition cache that is configured using the
configuration option. Both caches store table definitions but
serve different parts of the MySQL server. Objects in one
cache have no dependence on the existence of objects in the
stored program definition cache
partition: Stores stored program definition
option sets a limit for the number of stored program
definition objects that can be stored in the dictionary object
cache. The default value is 256.
The stored program definition cache partition exists in
parallel with the stored procedure and stored function caches
that are configured using the
option sets a soft upper limit for the number of cached stored
procedures or functions per connection, and the limit is
checked each time a connection executes a stored procedure or
function. The stored program definition cache partition, on
the other hand, is a shared cache that stores stored program
definition objects for other purposes. The existence of
objects in the stored program definition cache partition has
no dependence on the existence of objects in the stored
procedure cache or stored function cache, and vice versa.
character set definition cache partition: Stores character set definition objects and has a hardcoded object limit of 256.
collation definition cache partition: Stores collation definition objects and has a hardcoded object limit of 256.
For information about valid values for dictionary object cache configuration options, refer to Section 5.1.7, “Server System Variables”.
With the introduction of the data dictionary, the following
INFORMATION_SCHEMA tables are
implemented as views on data dictionary tables:
Queries on those tables are now more efficient because they obtain
information from data dictionary tables rather than by other,
slower means. In particular, for each
INFORMATION_SCHEMA table that is a view on data
The server no longer must create a temporary table for each
query of the
When the underlying data dictionary tables store values
previously obtained by directory scans (for example, to
enumerate database names or table names within databases) or
file-opening operations (for example, to read information from
INFORMATION_SCHEMA queries for those values
now use table lookups instead. (Additionally, even for a
INFORMATION_SCHEMA table, values
such as database and table names are retrieved by lookups from
the data dictionary and do not require directory or file
Indexes on the underlying data dictionary tables permit the
optimizer to construct efficient query execution plans,
something not true for the previous implementation that
using a temporary table per query.
The preceding improvements also apply to
SHOW statements that display
information corresponding to the
INFORMATION_SCHEMA tables that are views on
data dictionary tables. For example,
DATABASES displays the same information as the
In addition to the introduction of views on data dictionary
tables, table statistics contained in the
TABLES tables is now cached to
system variable defines the period of time before cached table
statistics expire. The default is 86400 seconds (24 hours). If
there are no cached statistics or statistics have expired,
statistics are retrieved from storage engine when querying table
statistics columns. To update cached values at any time for a
given table, use
can be set to
0 to have
INFORMATION_SCHEMA queries retrieve
the latest statistics directly from the storage engine, which is
not as fast as retrieving cached statistics.
For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
In addition to storing metadata about database objects in the data
dictionary, MySQL stores it in serialized form. This data is
referred to as Serialized Dictionary Information (SDI).
InnoDB stores SDI data within its tablespace
files. Other storage engines store SDI data in
.sdi files that are created in the schema
directory. SDI data is generated in a compact
Serialized Dictionary Information (SDI) is present in all
InnoDB tablespace files except for temporary
tablespace and undo tablespace files. SDI records in an
InnoDB tablespace file only describe table and
tablespace objects contained within the tablespace.
SDI data in within an
InnoDB tablespace file is
only updated by DDL operations on tables within the tablespace.
The presence of SDI data provides metadata redundancy. For
example, if the data dictionary becomes unavailable, object
metadata can be extracted directly from
tablespace files using the ibd2sdi tool.
InnoDB, an SDI record requires a single
index page, which is 16k in size by default. However, SDI data is
compressed to reduce the storage footprint.
InnoDB tables comprised of
multiple tablespaces, SDI data is stored in the tablespace file of
the first partition.
The MySQL server uses an internal API that is accessed during DDL operations to create and maintain SDI records.
Use of a data dictionary-enabled MySQL server entails some operational differences compared to a server that does not have a data dictionary:
Previously, enabling the
variable prevented creating and dropping tables only for the
InnoDB storage. As of MySQL
these operations for all storage engines. Table creation and
drop operations for any storage engine modify data dictionary
tables in the
mysql system database, but
those tables use the
InnoDB storage engine
and cannot be modified when
innodb_read_only is enabled.
The same principle applies to other table operations that
require modifying data dictionary tables. Examples:
also has important implications for non-data dictionary
tables in the
mysql system database. For
details, see the description of
Section 15.13, “InnoDB Startup Options and System Variables”
Previously, tables in the
database were visible to DML and DDL statements. As of MySQL
8.0, data dictionary tables are invisible and
cannot be modified or queried directly. However, in most cases
there are corresponding
tables that can be queried instead. This enables the
underlying data dictionary tables to be changed as server
development proceeds, while maintaining a stable
INFORMATION_SCHEMA interface for
INFORMATION_SCHEMA tables in MySQL
8.0 are closely tied to the data dictionary,
resulting in several usage differences:
for table statistics in the
TABLES tables retrieved
statistics directly from storage engines. As of MySQL
8.0, cached table statistics are used by
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
TABLE.) 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
0. For more information, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
INFORMATION_SCHEMA 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
queries might differ from previous results. If a query
result must have specific row ordering characteristics,
ORDER BY clause.
TABLE requires that
src_tbl be a base table and
fails if it is an
table that is a view on data dictionary tables.
Previously, result set headers of columns selected from
INFORMATION_SCHEMA tables used the
capitalization specified in the query. This query produces
a result set with a header of
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
As of MySQL 8.0, these headers are
capitalized; the preceding query produces a result set
with a header of
necessary, a column alias can be used to achieve a
different lettercase. For example:
SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;
--events options were
not required to include stored routines and events when
The dump included the
database, and therefore also the
event tables containing stored
routine and event definitions. As of MySQL
proc 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
--all-databases, use the
Previously, it was possible to dump stored routine and
event definitions together with their creation and
modification timestamps, by dumping the
tables. As of MySQL 8.0, those tables are not
used, so it is not possible to dump timestamps.
Previously, creating a stored routine that contains illegal characters produced a warning. As of MySQL 8.0, this is an error.
This section describes temporary limitations introduced with the MySQL data dictionary.
Manual creation of database directories under the data directory (for example, with mkdir) is unsupported. Manually created database directories are not recognized by the MySQL Server.
DDL operations take longer due to writing to storage, undo
logs, and redo logs instead of