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_tblsrc_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.