Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you the new features of MySQL 8.0 support atomic DDL statement example analysis, I believe most people do not know how to share this article for your reference, I hope you read this article after a great harvest, let us go to understand it!

MySQL 8.0 supports Atomic Data Definition Language (DDL) statements. This feature is called atomic DDL. Atomic DDL statements combine 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 operation, it commits transactions and retains applicable changes to the data dictionary, storage engine, and binary log, or rolls back transactions.

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 commit. The centralized transactional metadata store provided by MySQL Data Dictionary removes this barrier, making it possible to reassemble DDL statement operations into atomic transactions.

Official documents:

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

1. Supported DDL statements

Atomic DDL functionality supports tabular and non-tabular 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.

1: Supported table DDL statements include CREATE, ALTER, and DROP statements for databases, tables, tables, and indexes, as well as the statement TRUNCATE TABLE.

②: Supported non-table DDL statements include:

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

Account management statements: CREATE, ALTER, DROP,, if applicable, RENAME reports Users and Roles, and GRANT and REVOKE reports.

1.1 The atomic DDL feature does not support the following statements:

①: Table-related DDL statements involving storage engines other than storage engines InnoDB.

2: INSTALL PLUGIN and UNINSTALL PLUGIN statements.

3: INSTALL COMPONENT and UNINSTALL COMPONENT statements.

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

2. Atomic DDL characteristics:

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

②: During DDL operations, the SQL layer has no intermediate commit.

3: Where applicable:

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

Storage engine methods involved in DDL operations do not perform intermediate commits, and the storage engine registers itself as part of a 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 DDL operations is atomic, which changes the behavior of certain DDL statements.

Note:

Atomic or other DDL statements implicitly end any transactions active in the current session as if you COMMIT completed before executing the statement. This means that DDL statements cannot execute START TRANSACTION in a transaction control statement in another transaction... COMMIT, or in combination with other statements in the same transaction.

3. Changes in DDL statement behavior

3.1、DROP TABLE：

If all named tables use atomic DDL-supported storage engines, the operation is fully 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 table did not exist, but the existing table was successfully executed, 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:

Because of this change in behavior, DROP TABLE fails when partially completed statements on MySQL 5.7 master servers are replicated on MySQL 8.0 slave servers. To avoid this failure scenario, use the IF EXISTS syntax in the DROP TABLE statement to prevent errors from occurring on tables that do not exist

3.2、DROP DATABASE：

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

3.3 For tables that do not use atomic DDL supported storage engines, table deletions occur 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, data dictionary, and binary log to a maximum of one table in case of an interrupted DROP TABLE or DROP DATABASE operation. For operations that delete multiple tables, tables that do not use atomic DDL-supported storage engines are deleted before execution.

3.4 CREATE TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLE, CREATE TABLESPACE, and DROP TABLESPACE Operations performed on storage engine tables using atomic DDL support are either fully committed or rollback is stopped if the server is operating. In earlier versions of MySQL, interruptions in these operations could cause discrepancies between the storage engine, data dictionary, and binary logs, or leave orphaned files. RENAME TABLE If all named tables use storage engines supported by atomic DDL, the operation is atomic only.

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 atomic DDL was introduced, dropping views using DROP VIEW would give an error, but existing views would be successfully dropped:

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:

Because of this change in behavior, DROP VIEW partial completion operations on MySQL 5.7 master servers fail when replicated on MySQL 8.0 slave servers. To avoid this failure scenario, use the IF EXISTS syntax in the DROP VIEW statement to prevent errors for nonexistent views.

3.6 No longer allows partial execution of account management statements. Account management statements succeed or roll back for all named users and are invalid if an error occurs. In earlier versions of MySQL, account management statements naming multiple users might succeed for some users and fail for others.

The second of these CREATE USER statements returns an error but fails because it fails 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 atomic DDL was introduced, the second user created using the CREATE USER statement returned an error, but the nonexistent user was 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 |+-------+

Note:

Because of this change in behavior, MySQL 5.7 partially succeeds on the master server and fails when replicated on the MySQL 8.0 slave server. To avoid this failure scenario, use the IF EXISTS or IF NOT EXISTS syntax in the Create User command to prevent errors associated with naming users.

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 exempt storage engines can still introduce inconsistencies that can occur when the operation breaks or is only partially complete.

To support redo and rollback 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.

To view DDL logs written to tables during DDL operations by mysql.innodb_ddl_log, enable the innodb_print_ddl_logs configuration option.

Note:

mysql.innodb_ddl_log The redo log of changes to the table is flushed to disk immediately regardless of how innodb_flush_log_at_trx_commit is set. Flushing redo logs immediately prevents DDL operations from modifying data files, but mysql.innodb_ddl_log redo logs of changes to tables resulting from these operations are not persisted to disk. This situation can cause errors during rollback or recovery.

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

Preparation: Create the required objects and write DDL logs to the mysql.innodb_ddl_log table. DDL logging defines how DDL operations are rolled forward and rolled back.

Execution: Executes DDL operations. For example, perform a create routine for CREATE TABLE operations.

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

Post-DDL: Replays and removes DDL logs from the mysql.innodb_ddl_log table. To ensure that rollbacks can be performed safely without introducing inconsistencies, file operations are performed at the last stage, such as renaming or deleting data files. This phase also removes dynamic metadata from mysql.innodb_dynamic_metadata data dictionary tables DROP TABLE, TRUNCATE TABLE and other DDL operations that rebuild tables.

Note:

Whether the transaction commits or rolls back, DDL logs are replayed and removed from the table during the Post-DDL phase. mysql.innodb_ddl_log If the server pauses during DDL operations, DDL logs should remain only in tables. In this case, DDL logs are replayed and deleted after recovery.

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

5. View DDL logs:

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 be accessed directly in non-debug versions of MySQL.

The above is "MySQL 8.0 new features support atomic DDL statement sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.