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_onlysystem variable prevented creating and dropping tables only for the
InnoDBstorage. As of MySQL 8.0, enabling
innodb_read_onlyprevents these operations for all storage engines. Table creation and drop operations for any storage engine modify data dictionary tables in the
mysqlsystem database, but those tables use the
InnoDBstorage engine and cannot be modified when
innodb_read_onlyis enabled. The same principle applies to other table operations that require modifying data dictionary tables. Examples:Note
innodb_read_onlyalso has important implications for non-data dictionary tables in the
mysqlsystem database. For details, see the description of
innodb_read_onlyin Section 15.13, “InnoDB Startup Options and System Variables”
Previously, tables in the
mysqlsystem 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_SCHEMAtables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable
INFORMATION_SCHEMAinterface for application use.
INFORMATION_SCHEMAtables in MySQL 8.0 are closely tied to the data dictionary, resulting in several usage differences:
INFORMATION_SCHEMAqueries for table statistics in the
TABLEStables retrieved statistics directly from storage engines. As of MySQL 8.0, cached table statistics are used by default. The
information_schema_stats_expirysystem 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
ANALYZE 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_SCHEMAtables 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_SCHEMAqueries might differ from previous results. If a query result must have specific row ordering characteristics, include an
CREATE TABLErequires that
src_tblbe a base table and fails if it is an
INFORMATION_SCHEMAtable that is a view on data dictionary tables.
Previously, result set headers of columns selected from
INFORMATION_SCHEMAtables 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
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;
--eventsoptions were not required to include stored routines and events when using the
--all-databasesoption: The dump included the
mysqlsystem database, and therefore also the
eventtables containing stored routine and event definitions. As of MySQL 8.0, the
proctables 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
eventtables. 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.