MySQL 8.2 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic operation. The operation is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.
Atomic DDL is not transactional
DDL. DDL statements, atomic or otherwise, implicitly
end any transaction that is active in the current session, as if
you had done a
executing the statement. This means that DDL statements cannot
be performed within another transaction, within transaction
control statements such as
START TRANSACTION ...
COMMIT, or combined with other statements within the
Atomic DDL is made possible by the MySQL data dictionary, which provides centralized, transactional metadata storage.
The atomic DDL feature is described under the following topics in this section:
The atomic DDL feature supports both table and non-table DDL
statements. Table-related DDL operations require storage engine
support, whereas non-table DDL operations do not. Currently,
InnoDB storage engine supports
Supported table DDL statements include
DROPstatements for databases, tablespaces, tables, and indexes, and the
Supported non-table DDL statements include:
The following statements are not supported by the atomic DDL feature:
The characteristics of atomic DDL statements include the following:
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.
The state of data dictionary, routine, event, and loadable function caches is consistent with the status of the DDL operation, meaning that caches are updated to reflect whether or not the DDL operation was completed successfully or rolled back.
The storage engine methods involved in a DDL operation do not perform intermediate commits, and the storage engine registers itself as part of the DDL operation.
The storage engine supports redo and rollback of DDL operations, which is performed in the Post-DDL phase of the DDL operation.
The visible behaviour of DDL operations is atomic, which changes the behavior of some DDL statements. See Changes in DDL Statement Behavior.
Currently, only the
InnoDB storage engine
supports atomic DDL. Storage engines that do not support atomic
DDL are exempted from DDL atomicity. DDL operations involving
exempted storage engines remain capable of introducing
inconsistencies that can occur when operations are interrupted
or only partially completed.
To support redo and rollback of DDL operations,
InnoDB writes DDL logs to the
mysql.innodb_ddl_log table, which is a hidden
data dictionary table that resides in the
mysql.ibd data dictionary tablespace.
The redo logs for changes to the
mysql.innodb_ddl_log table are flushed to
disk immediately regardless of the
setting. Flushing the redo logs immediately avoids situations
where data files are modified by DDL operations but the redo
logs for changes to the
mysql.innodb_ddl_log table resulting from
those operations are not persisted to disk. Such a situation
could cause errors during rollback or recovery.
InnoDB storage engine executes DDL
operations in phases. DDL operations such as
ALTER TABLE may perform the
Prepare and Perform
phases multiple times prior to the Commit
Prepare: Create the required objects and write the DDL logs to the
mysql.innodb_ddl_logtable. The DDL logs define how to roll forward and roll back the DDL operation.
Perform: Perform the DDL operation. For example, perform a create routine for a
Commit: Update the data dictionary and commit the data dictionary transaction.
Post-DDL: Replay and remove DDL logs from the
mysql.innodb_ddl_logtable. To ensure that rollback can be performed safely without introducing inconsistencies, file operations such as renaming or removing data files are performed in this final phase. This phase also removes dynamic metadata from the
mysql.innodb_dynamic_metadatadata dictionary table for
TRUNCATE TABLE, and other DDL operations that rebuild the table.
DDL logs are replayed and removed from the
mysql.innodb_ddl_log table during the
Post-DDL phase, regardless of whether the
DDL operation is committed or rolled back. DDL logs should only
remain in the
mysql.innodb_ddl_log table if
the server is halted during a DDL operation. In this case, the
DDL logs are replayed and removed after recovery.
In a recovery situation, a DDL operation may be committed or
rolled back when the server is restarted. If the data dictionary
transaction that was performed during the
Commit phase of a DDL operation is present
in the redo log and binary log, the operation is considered
successful and is rolled forward. Otherwise, the incomplete data
dictionary transaction is rolled back when
InnoDB replays data dictionary redo logs, and
the DDL operation is rolled back.
To view DDL logs that are written to the
mysql.innodb_ddl_log data dictionary table
during atomic DDL operations that involve the
InnoDB storage engine, enable
innodb_print_ddl_logs to have
MySQL write the DDL logs to
on the host operating system and MySQL configuration,
stderr may be the error log, terminal, or
console window. See
Section 126.96.36.199, “Default Error Log Destination Configuration”.
InnoDB writes DDL logs to the
mysql.innodb_ddl_log table to support redo
and rollback of DDL operations. The
mysql.innodb_ddl_log table is a hidden data
dictionary table that resides in the
mysql.ibd data dictionary tablespace. Like
other hidden data dictionary tables, the
mysql.innodb_ddl_log table cannot be accessed
directly in non-debug versions of MySQL. (See
Section 14.1, “Data Dictionary Schema”.) The structure of the
mysql.innodb_ddl_log table corresponds to
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 utf8mb4_bin, new_file_path VARCHAR(512) COLLATE utf8mb4_bin, KEY(thread_id) );
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_metadatadata 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.
This example demonstrates enabling
innodb_print_ddl_logs to view
DDL logs written to
strderr for a
CREATE TABLE operation.
mysql> SET GLOBAL innodb_print_ddl_logs=1; mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
[Note]  InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7, space_id=5, old_file_path=./test/t1.ibd] [Note]  InnoDB: DDL log delete : by id 18 [Note]  InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7, table_id=1058, new_file_path=test/t1] [Note]  InnoDB: DDL log delete : by id 19 [Note]  InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7, space_id=5, index_id=132, page_no=4] [Note]  InnoDB: DDL log delete : by id 20 [Note]  InnoDB: DDL log post ddl : begin for thread id : 7 [Note]  InnoDB: DDL log post ddl : end for thread id : 7