Detailed explanation of MySQL 8.0 atomic DDL syntax

01 Introduction to Atomic DDL

Atomic DDL statements combine the data dictionary updates, storage engine operations, and binary log writes associated with the DDL operation into a single atomic operation. The operation is either committed, preserving applicable changes to the data dictionary, storage engines, and binary log, or rolled back.

In MySQL 8.0, the atomic DDL operation feature supports table-related operations, such as create table and drop table, as well as non-table-related operations, such as create routine and drop trigger.

in:

Supported table operations include:

drop, create, alter (operation objects are databases, tablespaces, tables, and indexes) syntax, truncate syntax

Supported non-table operations include:

create, drop, alter (the operation object is trigger, event, views, etc.)

Account management statements: create, alter, drop, and rename statements for users and roles, and grant and revoke statements

It should be noted that for table-related DDL operations, the storage engine must be Innodb. For non-table-related operations, there is no such requirement.

Some SQL statements do not support atomic DDL, for example:

1. Table operations for non-Innodb storage engines

2. Install plugin and uninstall plugin operations (install plugins)

3. installcomponent and uninstallcomponent statements

4. Create server, alter server, and drop server statements (this statement is used by the FEDERATED storage engine and can be temporarily ignored)

02 Changes in the execution behavior of some DDL operations

The change in the execution behavior of atomic operations is related to the change in the organizational structure of the data dictionary. Before MySQL 8.0, the Data Dictionary existed in system tables (MyISAM non-transaction engine tables) in addition to .FRM, .TRG, and .OPT files. In MySQL 8.0, the Data Dictionary exists entirely in the Data Dictionary Storage Engine (that is, the InnoDB table), which makes it possible to maintain atomicity in crash recovery. The following figure describes the structural changes of the data dictionary.

Before MySQL 8.0, the data dictionary structure was as follows:

After MySQL 8.0, the data dictionary becomes:

Let's look at two specific syntax changes:

(1) Changes in Drop syntax:

We create a table called test1 in the database, but no table called test2, and then execute drop table test1, test2 to observe the results.

MySQL 5.7 performance:

mysql> create table test1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| t1 |
| t2 |
| t3 |
| test1 |
+----------------+
4 rows in set (0.00 sec)

mysql> drop table test1,test2;
ERROR 1051 (42S02): Unknown table 'yeyz.test2'
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| t1 |
| t2 |
| t3 |
+----------------+
3 rows in set (0.00 sec)

MySQL 8.0 performance:

mysql> create table test1(id int);
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)

mysql> drop table test1,test2;
ERROR 1051 (42S02): Unknown table 'yeyz.test2'
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)

It can be seen that in MySQL 8.0, when there is no test2, the test1 table is not deleted, and the entire statement is completely rolled back; while in MySQL 5.7, the test1 table is mistakenly deleted and the entire statement is not completely rolled back.

Due to the difference in processing mechanisms, when we use MySQL 5.7 and MySQL 8.0 for master-slave replication, if we use statements similar to the above, an error will occur. Because their execution behaviors are different. To solve this problem, you need to use the drop table if not exists syntax. Similarly, the processing methods for a series of operations such as drop database and drop trigger are similar. Another point worth noting is that if all tables in a database are innodb, then drop database is atomic, otherwise, drop database is not atomic.

(2) Create Table...Select Syntax:

As of MySQL 8.0.21, on storage engines that support atomic DDL, when using row-based replication mode, the CREATE TABLE ... SELECT ... statement is recorded in the binary log as a transaction. In previous versions, it was recorded as two transactions, one for creating the table and another for inserting the data. A server failure between transactions or while inserting data might result in an empty table being copied. With the introduction of atomic DDL support, CREATE TABLE ... SELECT statements are now safe for row-based replication and are allowed to be used with GTID-based replication.

03 How to view the log of DDL operations?

To support redo and rollback of DDL operations, InnoDB writes DDL logs to the mysql.innodb_ddl_log table. This table exists in the data dictionary tablespace. If the user wants to view the contents of this table, the parameter needs to be turned on:

mysql> show variables like '%innodb_print_ddl_logs%'; 
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_print_ddl_logs | OFF |
+-----------------------+-------+
1 row in set (0.01 sec)

Then you can see the log of the DDL operation in the error log. The relevant logs are as follows:

[Note] [000000] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7,
space_id=5, old_file_path=./test/t1.ibd]
[Note] [000000] InnoDB: DDL log delete : by id 18
[Note] [000000] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7,
table_id=1058, new_file_path=test/t1]
[Note] [000000] InnoDB: DDL log delete : by id 19
[Note] [000000] InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7,
space_id=5, index_id=132, page_no=4]
[Note] [000000] InnoDB: DDL log delete : by id 20
[Note] [000000] InnoDB: DDL log post ddl: begin for thread id: 7
[Note] [000000] InnoDB: DDL log post ddl : end for thread id : 7

The flushing timing of the mysql.innodb_ddl_log table is not affected by the innodb_flush_logs_at_trx_commit parameter. This is done to avoid the situation where the data file is modified by the DDL operation, but the corresponding redo log has not been flushed to the disk, resulting in an error during recovery or rollback.

Finally, let's introduce the stages of the entire atomic DDL operation:

1. Preparation phase: Create the required objects 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.

2. Execution phase: Operational process of executing DDL

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

4. Post-DDL phase: Replay and delete DDL logs from the mysql.innodb_ddl_log table. To ensure that the rollback can be performed safely without introducing inconsistencies, on-disk file operations, such as renaming or deleting data files, are performed during this final phase. This phase also removes dynamic metadata from the mysql.innodb_dynamic_metadata data dictionary table for use in DROP TABLE , TRUNCATE TABLE , and other DDL operations that rebuild tables.

The above is a detailed explanation of the atomic DDL syntax of MySQL 8.0. For more information about the atomic DDL syntax of MySQL 8.0, please pay attention to other related articles on 123WORDPRESS.COM!