Supported table DDL statements include CREATE, ALTER, and DROP statements for databases, tablespaces, tables, and indexes, and the TRUNCATE TABLE statement.

Supported non-table DDL statements include:

Table-related DDL statements that involve a storage engine other than InnoDB.

INSTALL PLUGIN and UNINSTALL PLUGIN statements.

INSTALL COMPONENT and UNINSTALL COMPONENT statements.

CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

Metadata updates, binary log writes, and storage engine operations, where applicable, are combined into a single atomic operation.

There are no intermediate commits at the SQL layer during the DDL operation.

Where applicable:

The visible behaviour of DDL operations is atomic, which changes the behavior of some DDL statements. See Changes in DDL Statement Behavior.

DROP TABLE operations are fully atomic if all named tables use a storage engine which supports atomic DDL. The statement either drops all tables successfully or is rolled back.

DROP TABLE fails with an error if a named table does not exist, and no changes are made, regardless of the storage engine. This change in behavior is demonstrated in the following example, where the DROP TABLE statement fails because a named table does not exist:

mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

Prior to the introduction of atomic DDL, DROP TABLE reports an error for the named table that does not exist but succeeds for the named table that does exist:

mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
Empty set (0.00 sec)

DROP DATABASE is atomic if all tables use a storage engine which supports atomic DDL. The statement either drops all objects successfully or is rolled back. However, removal of the database directory from the file system occurs last and is not part of the atomic operation. If removal of the database directory fails due to a file system error or server halt, the DROP DATABASE transaction is not rolled back.

For tables that do not use a storage engine which supports atomic DDL, table deletion occurs outside of the atomic DROP TABLE or DROP DATABASE transaction. Such table deletions are written to the binary log individually, which limits the discrepancy between the storage engine, data dictionary, and binary log to one table at most in the case of an interrupted DROP TABLE or DROP DATABASE operation. For operations that drop multiple tables, the tables that do not use a storage engine which supports atomic DDL are dropped before tables that do.

CREATE TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLE, CREATE TABLESPACE, and DROP TABLESPACE operations for tables that use a storage engine which supports atomic DDL are either fully committed or rolled back if the server halts during their operation. In earlier MySQL releases, interruption of these operations could cause discrepancies between the storage engine, data dictionary, and binary log, or leave behind orphan files. RENAME TABLE operations are only atomic if all named tables use a storage engine which supports atomic DDL.

As of MySQL 8.0.21, on storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is logged as one transaction in the binary log when row-based replication is in use. Previously, it was logged as two transactions, one to create the table, and the other to insert data. A server failure between the two transactions or while inserting data could result in replication of an empty table. With the introduction of atomic DDL support, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication.

On storage engines that support both atomic DDL and foreign key constraints, creation of foreign keys is not permitted in CREATE TABLE ... SELECT statements when row-based replication is in use. Foreign key constraints can be added later using ALTER TABLE.

When CREATE TABLE ... SELECT is applied as an atomic operation, a metadata lock is held on the table while data is inserted, which prevents concurrent access to the table for the duration of the operation.

DROP VIEW fails if a named view does not exist, and no changes are made. The change in behavior is demonstrated in this example, where the DROP VIEW statement fails because a named view does not exist:

mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
mysql> DROP VIEW test.viewA, test.viewB;
ERROR 1051 (42S02): Unknown table 'test.viewB'
mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| viewA          | VIEW       |
+----------------+------------+

Prior to the introduction of atomic DDL, DROP VIEW returns an error for the named view that does not exist but succeeds for the named view that does exist:

mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
mysql> DROP VIEW test.viewA, test.viewB;
ERROR 1051 (42S02): Unknown table 'test.viewB'
mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
Empty set (0.00 sec)

Partial execution of account management statements is no longer permitted. Account management statements either succeed for all named users or roll back and have no effect if an error occurs. In earlier MySQL versions, account management statements that name multiple users could succeed for some users and fail for others.

The change in behavior is demonstrated in this example, where the second CREATE USER statement returns an error but fails because it cannot succeed for all named users.

mysql> CREATE USER userA;
mysql> CREATE USER userA, userB;
ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%'
mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
+-------+
| User  |
+-------+
| userA |
+-------+

Prior to the introduction of atomic DDL, the second CREATE USER statement returns an error for the named user that does not exist but succeeds for the named user that does exist:

mysql> CREATE USER userA;
mysql> CREATE USER userA, userB;
ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%'
mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
+-------+
| User  |
+-------+
| userA |
| userB |
+-------+

id: A unique identifier for a DDL log record.

thread_id: Each DDL log record is assigned a thread_id, which is used to replay and remove DDL logs that belong to a particular DDL operation. DDL operations that involve multiple data file operations generate multiple DDL log records.

type: The DDL operation type. Types include FREE (drop an index tree), DELETE (delete a file), RENAME (rename a file), or DROP (drop metadata from the mysql.innodb_dynamic_metadata data dictionary table).

space_id: The tablespace ID.

page_no: A page that contains allocation information; an index tree root page, for example.

index_id: The index ID.

table_id: The table ID.

old_file_path: The old tablespace file path. Used by DDL operations that create or drop tablespace files; also used by DDL operations that rename a tablespace.

new_file_path: The new tablespace file path. Used by DDL operations that rename tablespace files.