Atomic DDL Implementation in MySQL 8.0: Metadata Management and DDL Log Mechanisms

Background

MySQL 8.0 DDL is a complex process involving multiple modules such as MDL locks, table definition caches, row formats, row logs, DDL logs, online attributes, and tablespace physical file operations. This article compares MySQL 8.0 with version 5.7 to explain the implementation of atomic DDL operations.

Metadata Before MySQL 8.0

Prior to version 8.0, MySQL metadata was scattered across physical files (FRM, OPT, TRG), the MyISAM engine (system tables like user/proc/events), and the InnoDB engine (SYS_* tables). This fragmented approach lacked transactional protection, leading to inconsistencies between the server layer and InnoDB during crashes. For example, if a crash occurred during an ALTER TABLE rename phase, the FRM file might retain the old definition while InnoDB held the new one, rendering the table inaccessible.

Metadata After MySQL 8.0

MySQL 8.0 introduces a unified Data Dictionary (DD) that stores system tables within the InnoDB engine, providing a structured metadata service framework comprising a DD Client and a Storage Adaptor. The SQL layer's Table Definition Cache now retrieves metadata via the DD Client instead of reading FRM files. By leveraging InnoDB's transaction system, MySQL 8.0 consolidates multiple DDL operations into a single DDL transaction (DDL Trx), ensuring that both metadata changes and physical file operations are atomic. If the transaction commits, the DDL succeeds; if it rolls back, all operations revert.

Atomic Guarantee 1: InnoDB New DD for Metadata Atomicity

The new Data Dictionary is implemented using InnoDB transactional tables. Operations like creating a table involve inserting rows into system tables (tables, indexes, columns) within the same DDL Trx. These modifications are fully protected by InnoDB's UNDO LOG, guaranteeing atomicity and resolving previous server-InnoDB inconsistencies. The DD architecture consists of three layers: the Client layer (provides unified interfaces and cache management), the Conversion layer (translates client requests into system table operations), and the Storage layer (handles InnoDB table storage and access).

Atomic Guarantee 2: DDL Log for Physical File Atomicity

DDL operations involve physical file manipulations (e.g., B-tree creation, tablespace creation/deletion) that must also be reversible. The DDL Log system table records these physical changes to ensure atomicity. It serves two primary purposes: enabling rollback of newly created physical files during transaction failure, and deferring deletion operations until after commit to allow recovery if a crash occurs. The DDL Log table structure is as follows:

mysql> show create table mysql.innodb_ddl_log \G
*************************** 1. row ***************************
       Table: innodb_ddl_log
Create Table: 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) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `new_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `thread_id` (`thread_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC;

Create Table DDL Log (Rollback Mechanism)

During table creation, the DDL Log records reverse operations (e.g., DELETE SPACE, REMOVE CACHE, FREE) to undo physical changes if the transaction fails. These log entries are inserted via a separate transaction and immediately committed. The main DDL Trx marks them for deletion upon success. If the DDL Trx rolls back, the logs persist and execute the reverse operations.

[MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=7, thread_id=8, space_id=4, old_file_path=./test/test.ibd]
[MY-012478] [InnoDB] DDL log delete : 7
[MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=8, thread_id=8, table_id=1066, new_file_path=test/test]
[MY-012478] [InnoDB] DDL log delete : 8
[MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=9, thread_id=8, space_id=4, index_id=156, page_no=4]
[MY-012478] [InnoDB] DDL log delete : 9
[MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 8
[MY-012486] [InnoDB] DDL log post ddl : end for thread id : 8

Drop Table DDL Log (Post-Commit Cleanup)

For DROP TABLE, the DDL Log records physical deletions (e.g., DROP, DELETE SPACE) that must execute after the DDL Trx commits. These logs are not deleted immediately but are replayed during the Post-DDL phase. If a crash occurs before completion, the system recovers by replaying these logs upon restart.

[InnoDB] DDL log insert : [DDL record: DROP, id=10, thread_id=8, table_id=1066]
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=11, thread_id=8, space_id=4, old_file_path=./test/test.ibd]
[InnoDB] DDL log post ddl : begin for thread id : 8
[InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=11, thread_id=8, space_id=4, old_file_path=./test/test.ibd]
[InnoDB] DDL log replay : [DDL record: DROP, id=10, thread_id=8, table_id=1066]
[InnoDB] DDL log post ddl : end for thread id : 8

Alter Table DDL Log (Three-Phase Execution)

ALTER TABLE utilizes DDL Logs across three phases: Prepare (creates temporary tables with reverse logs), Alter (data migration without DDL logs), and Commit (renames spaces/tables and schedules old table deletion for Post-DDL). The logs ensure that renames and deletions can be safely rolled back or executed post-commit.

// ======================Prepare=============================================
[MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=15, thread_id=8, space_id=6, old_file_path=./test/#sql-ib1067-850981604.ibd]
[MY-012478] [InnoDB] DDL log delete : 15
[MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=16, thread_id=8, table_id=1068, new_file_path=test/#sql-ib1067-850981604]
[MY-012478] [InnoDB] DDL log delete : 16
[MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=17, thread_id=8, space_id=6, index_id=158, page_no=4]
[MY-012478] [InnoDB] DDL log delete : 17
// ======================Alter=============================================
[MY-000000] [InnoDB] TXSQL: parallel_read_threads with 1 threads, parallel_sort_threads with 1 threads, sql: alter table test add id1 int, algorithm = inplace, sample_step: 1, max_sample_cnt: 0, skip_pk_sort: 1
// ======================Commit=============================================
[MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=18, thread_id=8, table_id=1067]
[MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=19, thread_id=8, space_id=5, old_file_path=./test/#sql-ib1068-850981605.ibd, new_file_path=./test/test.ibd]
[MY-012478] [InnoDB] DDL log delete : 19
[MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=20, thread_id=8, table_id=1067, old_file_path=test/#sql-ib1068-850981605, new_file_path=test/test]
[MY-012478] [InnoDB] DDL log delete : 20
[MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=21, thread_id=8, space_id=6, old_file_path=./test/test.ibd, new_file_path=./test/#sql-ib1067-850981604.ibd]
[MY-012478] [InnoDB] DDL log delete : 21
[MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=22, thread_id=8, table_id=1068, old_file_path=test/test, new_file_path=test/#sql-ib1067-850981604]
[MY-012478] [InnoDB] DDL log delete : 22
[MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=23, thread_id=8, table_id=1067]
[MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=24, thread_id=8, space_id=5, old_file_path=./test/#sql-ib1068-850981605.ibd]
[MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 8
[MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=24, thread_id=8, space_id=5, old_file_path=./test/#sql-ib1068-850981605.ibd]
[MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=23, thread_id=8, table_id=1067]
[MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=18, thread_id=8, table_id=1067]
[MY-012486] [InnoDB] DDL log post ddl : end for thread id : 8