MySQL8.0 new feature--support for Atomic DDL statements

MySQL 8.0 begins to support atomic data definition language (DDL) statements. This feature is known as Atomic DDL. The Atomic DDL statement updates the data dictionary associated with the DDL operation, and the storage engine operations and binary log writes are combined into a single atomic transaction. Even if the server pauses during the operation, the transaction is committed and the applicable changes are persisted to the data dictionary, the storage engine and the binary log, or the transaction is rolled back.

The atomic DDL can be implemented by introducing MySQL data dictionary in MySQL 8.0. In earlier versions of MySQL, metadata was stored in metadata files, non-transactional tables, and storage engine-specific dictionaries, which required intermediate commits. The centralized transactional metadata store provided by the MySQL data dictionary eliminates this barrier, making it possible to reorganize DDL statement operations into atomic transactions.

Official documents:

Https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

1. Supported DDL statements

The Atomic DDL feature supports tables and non-table DDL statements. Table-related DDL operations require storage engine support, not table DDL operations. Currently, only the InnoDB storage engine supports atomic DDL.

①: Supported table DDL statements include create,alter and drop pairs of databases, tables, tables, and indexes, as well as statements TRUNCATE table declarations.

②: Supported non-table DDL statements include:

The Create and Drop statements, and (if applicable) ALTER stores the statements of the program, triggers, views, and user-defined functions (UDFs).

Account Management statements: Create,alter, DROP, if applicable, rename reporting users and roles, and Grant and revoke reports.

1.1. The Atomic DDL feature does not support the following statements:

①: A table-related DDL statement InnoDB involving a storage engine other than the storage engine.

②:install PLUGIN and UNINSTALL PLUGIN stated.

③:install COMPONENT and UNINSTALL COMPONENT stated.

④:create server, ALTER server, and DROP server statements.

2. Atomic DDL Characteristics:

①: Metadata updates, binary log writes, and storage engine operations (if applicable) are merged into a single transaction.

②: During DDL operations, the SQL layer does not have an intermediate commit.

③: In the case where applicable:

The state of the Data dictionary, program, event, and UDF cache is consistent with the state of the DDL operation, which means that the cache is updated to reflect whether the DDL operation was completed successfully or rolled back.

The storage engine methods involved in the DDL operation do not perform intermediate commits, and the storage engine registers itself as part of the DDL transaction.

The storage engine supports the redo and rollback of DDL operations, which are performed during the POST-DDL phase of the DDL operation.

The visible behavior of the ④:DDL operation is atomic, which changes the behavior of some DDL statements

Attention:

An atom or other DDL statement implicitly ends any transaction that is active in the current session, as if your commit was completed before executing the statement. This means that the DDL statement cannot execute START TRANSACTION in the transaction control statement in another transaction ... COMMIT, or used in conjunction with other statements in the same transaction.

3. Changes in DDL statement behavior

3.1. DROP TABLE:

If all named tables use the storage engine supported by the atomic DDL, the operation is completely atomic. The statement either successfully deletes all the tables or rolls back.

DROP table if the named table does not exist and no changes have been made (regardless of the storage engine), it will fail with an error. As shown below:

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 |

+----------------+

Before the introduction of the Atomic DDL, the DROP table will report that the error table does not exist, but the existing table will be executed successfully, as follows:

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)

Attention:

Because of this change in behavior, DROP table fails when a partial completion statement on the MySQL 5.7 master server is copied from the server on MySQL 8.0. To avoid this failure condition, use the IF exists syntax in the DROP TABLE statement to prevent errors on tables that do not exist

3.2. DROP DATABASE:

Atomic if all tables use the storage engine supported by the atomic DDL. The statement either successfully deletes all the objects or rolls back. However, removing the database directory from the file system is the last time and is not part of the atomic transaction. If the deletion of the database directory fails due to a file system error or a server pause, the drop DB does not roll back the transaction.

3.3. For tables that do not use the storage engine supported by the Atomic DDL, table deletions occur outside the atomic drop table or drop database transaction. Such table deletions are written to the binary log separately, which limits the difference between the storage engine, the data dictionary, and the binary log to a maximum of one table in the event of an interrupt drop table or drop database operation. For operations that delete multiple tables, tables that do not use the storage engine supported by the atomic DDL are removed before they are executed.

3.4. CREATE TABLE, ALTER table, RENAME table, TRUNCATE table, create tablespace, and DROP tablespace perform operations on storage engine tables supported with atomic DDL either completely Commits or if the server's operation stops rolling back. In earlier versions of MySQL, the interruption of these operations could lead to differences between the storage engine, the data dictionary and the binary log, or leave orphaned files. RENAME table if all named tables use the storage engine supported by the atomic DDL, the operation is atomic.

3.5. DROP VIEW:

If the named view does not exist and no changes have been made, it will fail. In this example, the behavior change is demonstrated, where the DROP view statement fails because the named view does not exist, as follows:

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 |

+----------------+------------+

Using Drop view to delete the views will be an error until the atomic DDL is introduced, but the existing view will be deleted successfully:

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)

Attention:

Because of this change in behavior, the partial completion of the DROP view on the MySQL 5.7 master server fails when the MySQL 8.0 replicates from the server. To avoid this failure condition, use the IF exists syntax in the DROP VIEW statement to prevent errors on the nonexistent view.

3.6. The partial execution of account management statements is no longer allowed. The account management statement succeeds or rolls back for all named users and is not valid if an error occurs. In earlier versions of MySQL, account management statements that were named for multiple users might succeed for some users and fail for other users.

As follows: Where the second create USER statement returned an error but failed because it could not 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 |

+-------+

Before the introduction of the Atomic DDL, the second use of the CREATE USER statement would return an error, but the non-existent user would have successfully created:

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 |

+-------+

Attention:

As a result of this change in behavior, the MySQL 5.7 Master server section will be executed successfully and will fail to replicate on MySQL 8.0 from the server. To avoid this failure situation, use the if exists or if not EXISTS syntax in the command that creates the user to prevent errors related to named users.

4. Storage Engine support: Currently only INNODB storage engines support Atomic DDL

Currently, only the InnoDB storage engine supports atomic DDL. The storage engine that does not support atomic DDL is free of DDL atomicity. DDL operations involving exemption of the storage engine can still introduce the inconsistency that may occur when an operation is interrupted or only partially completed.

To support redo and rollback DDL operations, InnoDB writes the DDL log to the Mysql.innodb_ddl_log table, which is a hidden data dictionary table that resides in the MYSQL.IBD data dictionary tablespace.

To mysql.innodb_ddl_log the DDL logs written to the table during DDL operations, enable the Innodb_print_ddl_logs configuration option.

Attention:

Mysql.innodb_ddl_log no matter how much the innodb_flush_log_at_trx_commit is set, the redo log of changes to the table is immediately flushed to disk. Refreshing the redo log now avoids the DDL operation modifying the data file, but the redo log mysql.innodb_ddl_log the changes made to the table by these operations will not persist to disk. This condition may cause an error during rollback or recovery.

The InnoDB storage engine performs a staged DDL operation. The DDL Operation ALTER Table can perform the prepare and perform phases several times before the commit phase:

Prepare: Create the desired object and write the DDL log to the Mysql.innodb_ddl_log table. The DDL log defines how the DDL operations are rolled forward and rolled back.

Execute: Performs a DDL operation. For example, the CREATE TABLE operation executes the creation routine.

Commit: Update the data dictionary and submit the data dictionary transaction.

POST-DDL: Replay and remove the DDL log from the Mysql.innodb_ddl_log table. To ensure that rollback can be safely performed without introducing inconsistencies, perform file operations in the final phase, such as renaming or deleting data files. This phase also removes the dynamic metadata from the Mysql.innodb_dynamic_metadata Data dictionary table of drop table,truncate tables and other DDL operations on the rebuilt table.

Attention:

The DDL logs are replayed in the post-ddl phase and removed from the table, regardless of whether the transaction is committed or rolled back. Mysql.innodb_ddl_log If the server is paused during a DDL operation, the DDL log should remain only in the table. In this case, the DDL logs are replayed and deleted after the recovery.

In the case of recovery, the DDL transaction can be committed or rolled back when the server is restarted. If there is a data dictionary transaction that executes during the commit phase of the DDL operation in the Redo log and the binary log, the operation is considered successful and rolled forward. Otherwise, the incomplete data dictionary transaction is rolled back when the InnoDB replay the data dictionary redo log, and the DDL transaction is rolled back.

5. View the DDL logs:

InnoDB writes the DDL log to the Mysql.innodb_ddl_log table to support redo and rollback DDL operations. The Mysql.innodb_ddl_log table is a hidden data dictionary table that is hidden in the MYSQL.IBD Data dictionary table space. As with other hidden data dictionary tables, Mysql.innodb_ddl_log cannot access the table directly in a non-debug version of MySQL.

MySQL8.0 new feature--support for Atomic DDL statements