Understanding Atomic DDL and the Data Dictionary in MySQL 8.0

MySQL versions prior to 8.0 executed DDL statements non‑atomically, meaning that a multi‑statement DDL such as rename table t1 to t1_bak, t2 to t2_bak; could leave the database in an inconsistent state if a crash occurred during execution.

In those versions the metadata was scattered across .FRM, .TRG, .OPT files, MyISAM system tables, and InnoDB system tables, making recovery difficult.

MySQL 8.0 redesigns the data dictionary so that all metadata resides in InnoDB‑managed system tables (the Data Dictionary Table). This centralization enables crash‑safe recovery and guarantees atomicity for DDL operations.

Practical tests comparing MySQL 5.7.22 and 8.0.20 show that when a DDL fails, 8.0 retains the original table (t1) while 5.7 deletes it, confirming the atomic behavior.

The atomic DDL workflow in InnoDB consists of four phases:

Prepare : create required objects and write a DDL log entry to mysql.innodb_ddl_log .

Execute : perform the actual DDL operation (e.g., CREATE TABLE).

Commit : update the data dictionary and commit the transaction.

Post‑DDL : replay and delete the DDL log, handling file‑system actions such as renaming data files, and clean up dynamic metadata for DROP, TRUNCATE, etc.

During both commit and rollback, the mysql.innodb_ddl_log table is replayed or cleared; only when a failure occurs does the log remain for recovery after a server restart.

To inspect DDL logs, InnoDB writes entries to the hidden table mysql.innodb_ddl_log , which resides in the mysql.ibd tablespace and can be viewed by enabling innodb_print_ddl_logs=1 and setting log_error_verbosity=3 . In Docker environments, the logs appear in docker logs mysql8.0 .

The DDL log table is defined as:

CREATE TABLE mysql.innodb_ddl_log ( 
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  thread_id BIGINT UNSIGNED NOT NULL, 
  type INT UNSIGNED NOT NULL, 
  space_id INT UNSIGNED, 
  page_no INT UNSIGNED, 
  index_id BIGINT UNSIGNED, 
  table_id BIGINT UNSIGNED, 
  old_file_path VARCHAR(512) COLLATE UTF8_BIN, 
  new_file_path VARCHAR(512) COLLATE UTF8_BIN, 
  KEY(thread_id) 
);

Each column stores information such as the unique log ID, associated thread, operation type (FREE, DELETE, RENAME, DROP), tablespace IDs, file paths, etc.

Supported atomic DDL in MySQL 8.0 includes CREATE/ALTER/DROP for databases, tablespaces, tables, indexes, TRUNCATE TABLE, as well as CREATE/DROP/ALTER for stored procedures, triggers, views, UDFs, and account management statements (CREATE/ALTER/DROP USER/ROLE, RENAME, GRANT, REVOKE).

Unsupported DDL includes non‑InnoDB engine statements, plugin/component installation commands, and server‑related statements such as CREATE SERVER.

In summary, MySQL 8.0’s unified InnoDB data dictionary and DDL logging provide true atomic DDL, improving reliability and simplifying recovery, while the article encourages readers to consult the official documentation for deeper details.