Table of Contents
MySQL Server now incorporates a transactional data dictionary that stores information about database objects. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional tables.
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.
InnoDB
uses the MySQL data dictionary in MySQL
8.0.
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”.
A data dictionary-enabled server entails some general operational differences compared to a server that does not have a data dictionary; see Section 14.6, “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 invisible and may only be accessed
using a debug build 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 SHOW TABLES
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.
Data dictionary tables are hidden by default but may be accessed
by compiling MySQL with debugging support using the
-DWITH_DEBUG=1
CMake option and specifying the
+d,skip_dd_table_access_check
debug
option and modifier. For
information about compiling debug builds, see
Section 28.5.1.1, “Compiling MySQL for Debugging”.
Modifying or writing to data dictionary tables directly is not recommended and may render your MySQL instance inoperable.
After compiling MySQL with debugging support, use this
SET
statement to make data
dictionary tables visible to the mysql client
session:
mysql> SET SESSION debug='+d,skip_dd_table_access_check';
Use this query to retrieve a list of data dictionary tables:
mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
Use SHOW CREATE TABLE
to view
data dictionary table definitions. For example:
mysql> 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 .frm
files:
The 64KB table definition size limit imposed by the
.frm
file structure is removed.
the INFORMATION_SCHEMA.TABLES
VERSION
column now reports a hardcoded
value of 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 InnoDB
tables.
.TRN
files: Trigger namespace files.
.TRG
files: Trigger parameter files.
.isl
files: InnoDB
Symbolic Link files containing the location of
file-per-table
tablespace files created outside of the MySQL 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
in the MySQL data directory. The
mysql.ibd
tablespace file must reside in the
MySQL data directory and its name cannot be modified or used by
another tablespace. Previously, these tables were created in
individual tablespace files in the mysql
database directory.
Dictionary data is protected by the same commit, rollback, and
crash-recovery capabilities that protect user data stored in
InnoDB
tables.
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.
The
tablespace_definition_cache
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
schema_definition_cache
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
table_definition_cache
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
other.
stored program definition cache
partition: Stores stored program definition
objects. The
stored_program_definition_cache
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
stored_program_cache
option.
The stored_program_cache
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.5, “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
dictionary tables:
The server no longer must create a temporary table for each
query of the INFORMATION_SCHEMA
table.
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
.frm
files),
INFORMATION_SCHEMA
queries for those values
now use table lookups instead. (Additionally, even for a
non-view 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
scans.)
Indexes on the underlying data dictionary tables permit the
optimizer to construct efficient query execution plans,
something not true for the previous implementation that
processed the INFORMATION_SCHEMA
table
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, SHOW
DATABASES
displays the same information as the
SCHEMATA
table.
In addition to the introduction of views on data dictionary
tables, table metadata contained in the
STATISTICS
and
TABLES
tables is now cached to
improve INFORMATION_SCHEMA
query
performance. Caching of table metadata is controlled by the
information_schema_stats
configuration option, which is set to CACHED
by
default. Cached table metadata is updated by issuing an
ANALYZE TABLE
statement.
information_schema_stats
can be
set to LATEST
to have
INFORMATION_SCHEMA
queries retrieve
the latest metadata directly from the storage engine, which is not
as fast as retrieving cached table metadata.
For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
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
innodb_read_only
system
variable prevented creating and dropping tables only for the
InnoDB
storage. As of MySQL
8.0, enabling
innodb_read_only
prevents
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:
ANALYZE TABLE
fails because
it updates table statistics, which are stored in the data
dictionary.
ALTER TABLE
fails because it updates the storage engine designation,
which is stored in the data dictionary.
tbl_name
ENGINE=engine_name
Enabling innodb_read_only
also has important implications for non-data dictionary
tables in the mysql
system database. For
details, see the description of
innodb_read_only
in
Section 15.13, “InnoDB Startup Options and System Variables”
Previously, tables in the mysql
system
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 INFORMATION_SCHEMA
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
application use.
INFORMATION_SCHEMA
tables in MySQL
8.0 are closely tied to the data dictionary,
resulting in several usage differences:
Previously, INFORMATION_SCHEMA
queries
for table statistics in the
STATISTICS
and
TABLES
tables retrieved
statistics directly from storage engines. As of MySQL
8.0, the default is to use cached table
statistics, which is more efficient. However, when the
server starts, the cached statistics are
NULL
and are not updated for a given
table until ANALYZE TABLE
is run. Also, the statistics go out of date to the extent
that the table is modified thereafter. To use the latest
table statistics obtained from storage engines (the
pre-8.0 behavior), change the
information_schema_stats
system variable from CACHED
to
LATEST
. For more information, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
Several 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 INFORMATION_SCHEMA
queries might differ from previous results. If a query
result must have specific row ordering characteristics,
include an ORDER BY
clause.
mysqldump and
mysqlpump no longer dump the
INFORMATION_SCHEMA
database, even if
explicitly named on the command line.
CREATE
TABLE
requires that
dst_tbl
LIKE
src_tbl
src_tbl
be a base table and
fails if it is an INFORMATION_SCHEMA
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
table_name
:
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 TABLE_NAME
. If
necessary, a column alias can be used to achieve a
different lettercase. For example:
SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;
The data directory affects how mysqldump
and mysqlpump dump information from the
mysql
system database:
Previously, it was possible to dump all tables in the
mysql
system database. As of MySQL
8.0, mysqldump and
mysqlpump dump only non-data dictionary
tables in that database.
Previously, the
--routines
and
--events
options were
not required to include stored routines and events when
using the
--all-databases
option:
The dump included the mysql
system
database, and therefore also the proc
and event
tables containing stored
routine and event definitions. As of MySQL
8.0, the event
and
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
--routines
and
--events
options
explicitly.
Previously, the
--routines
option
required the SELECT
privilege for the proc
table. As of
MySQL 8.0, that table is not used;
--routines
requires the
global SELECT
privilege
instead.
Previously, it was possible to dump stored routine and
event definitions together with their creation and
modification timestamps, by dumping the
proc
and event
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.
Moving data stored in MyISAM
tables by
copying and moving MyISAM
data files is
unsupported. Tables moved using this method are not discovered
by the server.
Simple backup and restore of individual
MyISAM
tables using copied data files is
unsupported.
TRUNCATE TABLE
, which is mapped
to DROP TABLE
and
CREATE TABLE
in MySQL 8.0, is
temporarily non-atomic. A server exit during a
TRUNCATE TABLE
operation can
result in a dropped table. It may also result in orphaned
foreign key entries in the InnoDB
SYS_FOREIGN
and
SYS_FOREIGN_COLS
dictionary tables if the
table contains foreign key constraints.
DDL operations take longer due to writing to storage, undo
logs, and redo logs instead of .frm
files.
The period of time during which DDL operations are vulnerable
to a server exit is longer due to writing to storage, undo
logs, and redo logs instead of .frm
files. The minimal risk introduced by this limitation is
mostly applicable to restore operations and other operations
that load numerous new tables.
With the removal of .isl
files in MySQL
8.0, offline relocation of
file-per-table
tablespaces created outside of the MySQL data directory is not
supported.
If MySQL exits in the middle of an ALTER
TABLE
operation on an InnoDB
table, you may be left with an orphan intermediate or
temporary table. Removing orphan intermediate and temporary
tables is unsupported.