Understanding MySQL 8.0 Data Dictionary, Atomic DDL, and Persistent Autoincrement

The MySQL data dictionary stores metadata such as databases, tables, indexes, columns, functions, triggers, privileges, and engine‑specific information; MySQL 8.0 introduces a unified dictionary stored in InnoDB tables within the mysql.ibd tablespace.

Pre‑8.0 Data Dictionary – Metadata was split across server‑level files (.frm, .par, .TRN, .TRG, .isl, .db.opt), system tables in the mysql schema, and InnoDB internal tables (SYS_DATAFILES, SYS_FOREIGN, etc.). This distribution caused maintenance difficulties, lack of atomic DDL, and poor scalability.

MySQL 8.0 Data Dictionary – All metadata is now stored in InnoDB dictionary tables (e.g., mysql.tables, mysql.columns, mysql.indexes) inside a dedicated tablespace (mysql.ibd). The new architecture is illustrated in Figure 3.

Example to list dictionary tables in debug mode: SET SESSION debug='+d,skip_dd_table_access_check';

Querying dictionary tables: select name from mysql.tables where hidden='System' and type='BASE TABLE';

Dictionary Object Cache – Global caches store definitions for tables, schemas, stored programs, and tablespaces. Cache variables can be inspected with: show variables like '%definition%';

Cache entries include: table_definition_cache schema_definition_cache stored_program_definition_cache tablespace_definition_cache

Information_schema Changes – Table names were renamed (e.g., INNODB_SYS_COLUMNS → INNODB_COLUMNS). Queries now read directly from dictionary tables without creating temporary tables, improving performance.

Serialized Dictionary Information (SDI) – Metadata is also stored in SDI files. For MyISAM tables, SDI appears as *.sdi files; for InnoDB tables, SDI is embedded in the .ibd file. Example SDI JSON for a MyISAM table is shown in the article.

SDI schema creation example: create table SDI_$TABLESPACEID(type int, id int, compressed_len int, uncompressed_len int, data blob not null, primary key(type,id));

Atomic DDL – MySQL 8.0 ensures that DDL operations affecting the data dictionary, storage engine, and binary log are atomic. The process consists of Prepare (log creation), Perform (DDL execution), Commit (dictionary update), and Post‑DDL (log replay or cleanup). Log types are defined in the enum Log_Type and stored in mysql.innodb_ddl_log.

DDL log table definition example: CREATE TABLE `innodb_ddl_log` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `thread_id` bigint unsigned NOT NULL, `type` int unsigned NOT NULL, `space_id` int unsigned DEFAULT NULL, `page_no` int unsigned DEFAULT NULL, `index_id` bigint unsigned DEFAULT NULL, `table_id` bigint unsigned DEFAULT NULL, `old_file_path` varchar(512) DEFAULT NULL, `new_file_path` varchar(512) DEFAULT NULL, PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`) );

Examples of atomic DDL include DROP TABLE, CREATE TABLE, and TRUNCATE TABLE, each logging actions such as delete_space_log, rename_space_log, and free_tree_log to enable safe rollback or crash recovery.

Persistent Autoincrement – MySQL 8.0 persists autoincrement values using redo logs and the mysql.innodb_dynamic_metadata table. Autoinc values are written to the log with MLOG_TABLE_DYNAMIC_META and flushed during checkpoints, ensuring correct values after crashes.

Upgrade Considerations – Direct upgrades are supported only from 5.7 to 8.0. Issues to check include duplicate dictionary table names, unsupported data types, non‑native partition tables, old triggers, foreign‑key name length, view column limits, and enum length limits.

References are provided for the MySQL source code, official documentation, and community articles covering these topics.