Shulou(Shulou.com)06/01 Report--

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

By introducing the MySQL data dictionary in MySQL 8.0, Atomic DDL can be implemented. 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 transaction metadata storage provided by the MySQL data dictionary removes this barrier and makes 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 function supports both table and non-table DDL statements. Table-related DDL operations require storage engine support, while non-table DDL operations do not. Currently, only the InnoDB storage engine supports atomic DDL.

①: supported table DDL statements include CREATE,ALTER and DROP statements for databases, tables, tables and indexes, and statement TRUNCATE TABLE statements.

②: supported non-table DDL statements include:

CREATE and DROP statements, and, if applicable, statements for ALTER stored procedures, triggers, views, and user-defined functions (UDF).

Account management statements: CREATE,ALTER, DROP, if applicable, RENAME report users and roles, and GRANT and REVOKE reports.

1.1.Atomic DDL function 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 statements.

③: INSTALL COMPONENT and UNINSTALL COMPONENT statements.

④: CREATE SERVER, ALTER SERVER and DROP SERVER statements.

2. Atomic DDL properties:

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

②: there is no intermediate commit in the SQL layer during the DDL operation.

③: where applicable:

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

The storage engine method involved in the DDL operation does not perform an intermediate commit, and the storage engine registers itself as part of the DDL transaction.

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

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

Note:

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

3. Changes in the behavior of DDL statements.

3.1 、 DROP TABLE:

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

DROP TABLE fails with an error if the named table does not exist and no changes have been made (regardless of the storage engine). As follows:

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 atomic DDL, DROP TABLE reported that the error table did not exist, but the existing table would 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)

Note:

Due to this change in behavior, DROP TABLE fails when partial completion statements on the MySQL 5.7 master server are replicated from the server by MySQL 8.0. To avoid this failure scenario, use IF EXISTS syntax in DROP TABLE statements to prevent errors on tables that do not exist

3.2 、 DROP DATABASE:

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

For tables that do not use a storage engine supported by atomic DDL, table deletion occurs outside of atomic DROP TABLE or DROP DATABASE transactions. Such table deletions are written separately to the binary log, which limits the difference between the storage engine, the data dictionary, and the binary log to up to one table when interrupting DROP TABLE or DROP DATABASE operations. For operations that delete multiple tables, tables that do not use a storage engine supported by atomic DDL are deleted before execution.

Actions performed by CREATE TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLE, CREATE TABLESPACE, and DROP TABLESPACE on storage engine tables that use atomic DDL support are either fully committed or if the server's operation stops the rollback. In earlier versions of MySQL, interruptions of these operations could lead to differences between storage engines, data dictionaries, and binary logs, or leaving orphaned files. RENAME TABLE if all named tables use a storage engine supported by atomic DDL, the operation is just an atomic operation.

3.5 、 DROP VIEW:

If the named view does not exist and no changes have been made, it fails. The behavior change is demonstrated in this example, 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 |

+-+ +

Before the introduction of atomic DDL, deleting a view using DROP VIEW would report an error, but existing views would be successfully deleted:

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)

Note:

Due to this change in behavior, the partial completion of DROP VIEW on the MySQL 5.7 primary server will fail when MySQL 8.0 replicates from the server. To avoid this failure scenario, use IF EXISTS syntax in the DROP VIEW statement to prevent errors on views that do not exist.

3.6. Partial execution of account management statements is no longer allowed. The account management statement succeeds or rolls back to all named users and is invalid if an error occurs. In earlier versions of MySQL, account management statements named for multiple users might succeed for some users and fail for others.

As follows: 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 atomic DDL, the second user who created using the CREATE USER statement returned an error, but a user that did not exist would successfully create:

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 |

+-+

Note:

Due to this change in behavior, the upper part of the primary server of MySQL 5.7 will execute successfully and will fail when MySQL 8.0 replicates from the server. To avoid this failure scenario, use IF EXISTS or IF NOT EXISTS syntax in the commands that create the user to prevent errors related to the named user.

4. Storage engine support: currently, only innodb storage engine supports atomic DDL.

Currently, only the InnoDB storage engine supports atomic DDL. Storage engines that do not support atomic DDL are exempt from DDL atomicity. DDL operations involving the exemption storage engine can still introduce inconsistencies that may occur when the 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.

For mysql.innodb_ddl_log to view the DDL logs written to the table during the DDL operation, enable the innodb_print_ddl_logs configuration option.

Note:

Mysql.innodb_ddl_log regardless of the innodb_flush_log_at_trx_commit setting, the redo log of changes to the table is immediately flushed to disk. Refreshing the redo log immediately prevents DDL operations from modifying data files, but mysql.innodb_ddl_log redo logs for changes to the table resulting from these operations are not persisted to disk. This situation may cause an error during a rollback or restore.

The InnoDB storage engine performs DDL operations in phases. The DDL operation ALTER TABLE can execute the Prepare and Perform phases multiple times before the Commit phase:

Prepare: create the required object and write the DDL log to the mysql.innodb_ddl_ log table. The DDL log defines how to roll forward and roll back DDL operations.

Execute: perform the DDL operation. For example, execute the create routine for the CREATE TABLE operation.

Submit: update the data dictionary and commit the data dictionary transaction.

Post-DDL: replay and delete the DDL log from the mysql.innodb_ddl_ log table. To ensure that the rollback can be safely performed without introducing inconsistencies, perform file operations at the final stage, such as renaming or deleting data files. This phase also removes the data dictionary table DROP TABLE,TRUNCATE TABLE from the dynamic metadata mysql.innodb_dynamic_metadata and other DDL operations of the rebuild table.

Note:

Regardless of whether the transaction is committed or rolled back, the DDL log is replayed during the Post-DDL phase and deleted from the table. Mysql.innodb_ddl_log if the server is paused during the DDL operation, the DDL log should only be kept in the table. In this case, the DDL log will be replayed and deleted after 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 in the redo log and binary log that was performed during the commit phase of the DDL operation, the operation is considered successful and rolled forward. Otherwise, the incomplete data dictionary transaction and the DDL transaction are rolled back when the InnoDB replays the data dictionary redo log.

5. View the DDL log:

InnoDB writes DDL logs 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 hidden in the mysql.ibd data dictionary table space. Like other hidden data dictionary tables, mysql.innodb_ddl_log cannot access the table directly in a non-debug version of MySQL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.