Analysis of the new features of MySQL 8.0 - transactional data dictionary and atomic DDL

Analysis of the new features of MySQL 8.0 - transactional data dictionary and atomic DDL

Preface

Transactional data dictionary and atomic DDL are two very important new features introduced in MySQL 8.0. The reason why these two new features are put together is that they are closely related. Transactional data dictionary is a prerequisite, and atomic DDL is an important application scenario.

Data Dictionary before MySQL 8.0

The data dictionary before MySQL 8.0 mainly consists of the following three parts:

(1) Operating system files

db.opt: ​​database metadata information
frm: table metadata information
par: table partition metadata information
TRN/TRG: Trigger metadata information
ddl_log.log: metadata information generated during the DDL process

(2) Non-InnoDB system tables under the mysql database

mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='mysql' and engine<>'InnoDB';
+--------------+------------------+------------+--------+
| table_schema | table_name | table_type | engine |
+--------------+------------------+------------+--------+
| mysql | columns_priv | BASE TABLE | MyISAM |
| mysql | db | BASE TABLE | MyISAM |
| mysql | event | BASE TABLE | MyISAM |
| mysql | func | BASE TABLE | MyISAM |
| mysql | general_log | BASE TABLE | CSV |
| mysql | ndb_binlog_index | BASE TABLE | MyISAM |
| mysql | proc | BASE TABLE | MyISAM |
| mysql | procs_priv | BASE TABLE | MyISAM |
| mysql | proxies_priv | BASE TABLE | MyISAM |
| mysql | slow_log | BASE TABLE | CSV |
| mysql | tables_priv | BASE TABLE | MyISAM |
| mysql | user | BASE TABLE | MyISAM |
+--------------+------------------+------------+--------+
12 rows in set (0.00 sec)

(3) InnoDB system tables under the mysql database

mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='mysql' and engine='InnoDB';
+--------------+---------------------------+------------+--------+
| table_schema | table_name | table_type | engine |
+--------------+---------------------------+------------+--------+
| mysql | engine_cost | BASE TABLE | InnoDB |
| mysql | gtid_executed | BASE TABLE | InnoDB |
| mysql | help_category | BASE TABLE | InnoDB |
| mysql | help_keyword | BASE TABLE | InnoDB |
| mysql | help_relation | BASE TABLE | InnoDB |
| mysql | help_topic | BASE TABLE | InnoDB |
| mysql | innodb_index_stats | BASE TABLE | InnoDB |
| mysql | innodb_table_stats | BASE TABLE | InnoDB |
| mysql | plugin | BASE TABLE | InnoDB |
| mysql | server_cost | BASE TABLE | InnoDB |
| mysql | servers | BASE TABLE | InnoDB |
| mysql | slave_master_info | BASE TABLE | InnoDB |
| mysql | slave_relay_log_info | BASE TABLE | InnoDB |
| mysql | slave_worker_info | BASE TABLE | InnoDB |
| mysql | time_zone | BASE TABLE | InnoDB |
| mysql | time_zone_leap_second | BASE TABLE | InnoDB |
| mysql | time_zone_name | BASE TABLE | InnoDB |
| mysql | time_zone_transition | BASE TABLE | InnoDB |
| mysql | time_zone_transition_type | BASE TABLE | InnoDB |
+--------------+---------------------------+------------+--------+
19 rows in set (0.00 sec)

We can see that the data dictionary is distributed to multiple locations, which is not conducive to the unified management of metadata on the one hand, and easily causes data inconsistency on the other hand (since operating system files and non-InnoDB system tables do not support transactions, ACID cannot be guaranteed when executing DDL operations).

MySQL 8.0 Data Dictionary

To solve the above problems, MySQL 8.0 improves the data dictionary to be stored in the InnoDB storage engine, which is divided into two parts:

(1) Data dictionary table: stores the most important metadata information, located in the MySQL database and stored in the MySQL shared tablespace (mysql.ibd)

(2) Other system tables: store auxiliary metadata information, located in the MySQL database, stored in the MySQL shared tablespace (mysql.ibd)

Data dictionary table

Data dictionary tables are invisible and cannot be accessed through select, nor do they appear in the show tables or information.schema.tables results. Attempting to access them will result in the following error:

mysql> select * from mysql.tables limit 10;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

However, these hidden data dictionary tables can be accessed in debug mode; we recompile and install (the process is omitted), start the process in debug mode, and try to access again. The results are as follows:

mysql> SET SESSION debug='+d,skip_dd_table_access_check';

mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
+------------------------------+-----------+--------+------------+
| name | schema_id | hidden | type |
+------------------------------+-----------+--------+------------+
| catalogs | 1 | System | BASE TABLE |
| character_sets | 1 | System | BASE TABLE |
| check_constraints | 1 | System | BASE TABLE |
| collations | 1 | System | BASE TABLE |
| column_statistics | 1 | System | BASE TABLE |
| column_type_elements | 1 | System | BASE TABLE |
| columns | 1 | System | BASE TABLE |
| dd_properties | 1 | System | BASE TABLE |
| events | 1 | System | BASE TABLE |
| foreign_key_column_usage | 1 | System | BASE TABLE |
| foreign_keys | 1 | System | BASE TABLE |
| index_column_usage | 1 | System | BASE TABLE |
| index_partitions | 1 | System | BASE TABLE |
| index_stats | 1 | System | BASE TABLE |
| indexes | 1 | System | BASE TABLE |
| innodb_ddl_log | 1 | System | BASE TABLE |
| innodb_dynamic_metadata | 1 | System | BASE TABLE |
| parameter_type_elements | 1 | System | BASE TABLE |
| parameters | 1 | System | BASE TABLE |
| resource_groups | 1 | System | BASE TABLE |
| routines | 1 | System | BASE TABLE |
| schemata | 1 | System | BASE TABLE |
| st_spatial_reference_systems | 1 | System | BASE TABLE |
| table_partition_values ​​| 1 | System | BASE TABLE |
| table_partitions | 1 | System | BASE TABLE |
| table_stats | 1 | System | BASE TABLE |
| tables | 1 | System | BASE TABLE |
| tablespace_files | 1 | System | BASE TABLE |
| tablespaces | 1 | System | BASE TABLE |
| triggers | 1 | System | BASE TABLE |
| view_routine_usage | 1 | System | BASE TABLE |
| view_table_usage | 1 | System | BASE TABLE |
+------------------------------+-----------+--------+------------+
32 rows in set (0.01 sec)

Other system tables

Other system tables can be viewed through show tables or information_schema.tables. They are all improved to the InnoDB storage engine (except general_log and slow_log. These two tables do not record metadata information, but are only used for logging):

mysql> select table_schema,table_name,engine from information_schema.tables where table_schema='mysql';
+--------------+---------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+---------------------------+--------+
| mysql | columns_priv | InnoDB |
| mysql | component | InnoDB |
| mysql | db | InnoDB |
| mysql | default_roles | InnoDB |
| mysql | engine_cost | InnoDB |
| mysql | func | InnoDB |
| mysql | general_log | CSV |
| mysql | global_grants | InnoDB |
| mysql | gtid_executed | InnoDB |
| mysql | help_category | InnoDB |
| mysql | help_keyword | InnoDB |
| mysql | help_relation | InnoDB |
| mysql | help_topic | InnoDB |
| mysql | innodb_index_stats | InnoDB |
| mysql | innodb_table_stats | InnoDB |
| mysql | password_history | InnoDB |
| mysql | plugin | InnoDB |
| mysql | procs_priv | InnoDB |
| mysql | proxies_priv | InnoDB |
| mysql | role_edges | InnoDB |
| mysql | server_cost | InnoDB |
| mysql | servers | InnoDB |
| mysql | slave_master_info | InnoDB |
| mysql | slave_relay_log_info | InnoDB |
| mysql | slave_worker_info | InnoDB |
| mysql | slow_log | CSV |
| mysql | tables_priv | InnoDB |
| mysql | time_zone | InnoDB |
| mysql | time_zone_leap_second | InnoDB |
| mysql | time_zone_name | InnoDB |
| mysql | time_zone_transition | InnoDB |
| mysql | time_zone_transition_type | InnoDB |
| mysql | user | InnoDB |
+--------------+---------------------------+--------+
33 rows in set (0.00 sec)

Data dictionary views

As mentioned above, the data dictionary table can only be accessed in debug mode. So how should we obtain metadata information in a production environment? The answer is through the data dictionary views under the information_schema library. The design concept is the same as that of Oracle database, which stores metadata information in base tables (x$, $) and then provides it to users through views (v$, dba_/all_/user_). The same is true for MySQL database, which stores metadata information in the data dictionary table of MySQL library and hides it, and then provides information_schema library views for users to query:

mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='information_schema'; 
+--------------------+---------------------------------------+-------------+--------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+--------------------+---------------------------------------+-------------+--------+
| information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS | SYSTEM VIEW | NULL |
| information_schema | APPLICABLE_ROLES | SYSTEM VIEW | NULL |
| information_schema | CHARACTER_SETS | SYSTEM VIEW | NULL |
| information_schema | CHECK_CONSTRAINTS | SYSTEM VIEW | NULL |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | NULL |
| information_schema | COLLATIONS | SYSTEM VIEW | NULL |
| information_schema | COLUMN_PRIVILEGES | SYSTEM VIEW | NULL |
| information_schema | COLUMN_STATISTICS | SYSTEM VIEW | NULL |
| information_schema | COLUMNS | SYSTEM VIEW | NULL |
| information_schema | ENABLED_ROLES | SYSTEM VIEW | NULL |
| information_schema | ENGINES | SYSTEM VIEW | NULL |
| information_schema | EVENTS | SYSTEM VIEW | NULL |
| information_schema | FILES | SYSTEM VIEW | NULL |
| information_schema | INNODB_BUFFER_PAGE | SYSTEM VIEW | NULL |
| information_schema | INNODB_BUFFER_PAGE_LRU | SYSTEM VIEW | NULL |
| information_schema | INNODB_BUFFER_POOL_STATS | SYSTEM VIEW | NULL |
| information_schema | INNODB_CACHED_INDEXES | SYSTEM VIEW | NULL |
| information_schema | INNODB_CMP | SYSTEM VIEW | NULL |
| information_schema | INNODB_CMP_PER_INDEX | SYSTEM VIEW | NULL |
| information_schema | INNODB_CMP_PER_INDEX_RESET | SYSTEM VIEW | NULL |
| information_schema | INNODB_CMP_RESET | SYSTEM VIEW | NULL |
| information_schema | INNODB_CMPMEM | SYSTEM VIEW | NULL |
| information_schema | INNODB_CMPMEM_RESET | SYSTEM VIEW | NULL |
| information_schema | INNODB_COLUMNS | SYSTEM VIEW | NULL |
| information_schema | INNODB_DATAFILES | SYSTEM VIEW | NULL |
| information_schema | INNODB_FIELDS | SYSTEM VIEW | NULL |
| information_schema | INNODB_FOREIGN | SYSTEM VIEW | NULL |
| information_schema | INNODB_FOREIGN_COLS | SYSTEM VIEW | NULL |
| information_schema | INNODB_FT_BEING_DELETED | SYSTEM VIEW | NULL |
| information_schema | INNODB_FT_CONFIG | SYSTEM VIEW | NULL |
| information_schema | INNODB_FT_DEFAULT_STOPWORD | SYSTEM VIEW | NULL |
| information_schema | INNODB_FT_DELETED | SYSTEM VIEW | NULL |
| information_schema | INNODB_FT_INDEX_CACHE | SYSTEM VIEW | NULL |
| information_schema | INNODB_FT_INDEX_TABLE | SYSTEM VIEW | NULL |
| information_schema | INNODB_INDEXES | SYSTEM VIEW | NULL |
| information_schema | INNODB_METRICS | SYSTEM VIEW | NULL |
| information_schema | INNODB_SESSION_TEMP_TABLESPACES | SYSTEM VIEW | NULL |
| information_schema | INNODB_TABLES | SYSTEM VIEW | NULL |
| information_schema | INNODB_TABLESPACES | SYSTEM VIEW | NULL |
| information_schema | INNODB_TABLESPACES_BRIEF | SYSTEM VIEW | NULL |
| information_schema | INNODB_TABLESTATS | SYSTEM VIEW | NULL |
| information_schema | INNODB_TEMP_TABLE_INFO | SYSTEM VIEW | NULL |
| information_schema | INNODB_TRX | SYSTEM VIEW | NULL |
| information_schema | INNODB_VIRTUAL | SYSTEM VIEW | NULL |
| information_schema | KEY_COLUMN_USAGE | SYSTEM VIEW | NULL |
| information_schema | KEYWORDS | SYSTEM VIEW | NULL |
| information_schema | OPTIMIZER_TRACE | SYSTEM VIEW | NULL |
| information_schema | PARAMETERS | SYSTEM VIEW | NULL |
| information_schema | PARTITIONS | SYSTEM VIEW | NULL |
| information_schema | PLUGINS | SYSTEM VIEW | NULL |
| information_schema | PROCESSLIST | SYSTEM VIEW | NULL |
| information_schema | PROFILING | SYSTEM VIEW | NULL |
| information_schema | REFERENTIAL_CONSTRAINTS | SYSTEM VIEW | NULL |
| information_schema | RESOURCE_GROUPS | SYSTEM VIEW | NULL |
| information_schema | ROLE_COLUMN_GRANTS | SYSTEM VIEW | NULL |
| information_schema | ROLE_ROUTINE_GRANTS | SYSTEM VIEW | NULL |
| information_schema | ROLE_TABLE_GRANTS | SYSTEM VIEW | NULL |
| information_schema | ROUTINES | SYSTEM VIEW | NULL |
| information_schema | SCHEMA_PRIVILEGES | SYSTEM VIEW | NULL |
| information_schema | SCHEMATA | SYSTEM VIEW | NULL |
| information_schema | ST_GEOMETRY_COLUMNS | SYSTEM VIEW | NULL |
| information_schema | ST_SPATIAL_REFERENCE_SYSTEMS | SYSTEM VIEW | NULL |
| information_schema | ST_UNITS_OF_MEASURE | SYSTEM VIEW | NULL |
| information_schema | STATISTICS | SYSTEM VIEW | NULL |
| information_schema | TABLE_CONSTRAINTS | SYSTEM VIEW | NULL |
| information_schema | TABLE_PRIVILEGES | SYSTEM VIEW | NULL |
| information_schema | TABLES | SYSTEM VIEW | NULL |
| information_schema | TABLESPACES | SYSTEM VIEW | NULL |
| information_schema | TRIGGERS | SYSTEM VIEW | NULL |
| information_schema | USER_PRIVILEGES | SYSTEM VIEW | NULL |
| information_schema | VIEW_ROUTINE_USAGE | SYSTEM VIEW | NULL |
| information_schema | VIEW_TABLE_USAGE | SYSTEM VIEW | NULL |
| information_schema | VIEWS | SYSTEM VIEW | NULL |
+--------------------+---------------------------------------+-------------+--------+
73 rows in set (0.00 sec)

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
        View: TABLES
     Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TABLES` AS select (`cat`.`name` collate utf8_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,if((`tbl`.`type` = 'VIEW'),NULL,internal_table_rows(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `TABLE_ROWS`,if((`tbl`.`type` = 'VIEW'),NULL,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `AVG_ROW_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `MAX_DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `INDEX_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_FREE`,if((`tbl`.`type` = 'VIEW'),NULL,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`)) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_update_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `UPDATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,if((`tbl`.`type` = 'VIEW'),NULL,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1),if((`sch`.`default_encryption` = 'YES'),1,0))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

Data dictionary cache

In order to reduce disk IO and improve access efficiency, MySQL 8.0 introduced data dictionary cache. The data dictionary cache is a global shared area that uses the LRU algorithm for memory management, including:

tablespace definition cache partition: used to cache tablespace definition objects; the size limit is determined by the parameter tablespace_definition_cache.
Schema definition cache partition: used to cache schema definition objects; the size limit is determined by the parameter schema_definition_cache.
table definition cache partition: used to cache table definition objects; the size limit is determined by the parameter max_connections.
stored program definition cache partition: used to cache stored procedure definition objects; the size limit is determined by the parameter stored_program_definition_cache.
character set definition cache partition: used to cache character set definition objects; hard-coded limit of 256.
collation definition cache partition: used to cache collation definition objects; hard-coded limit of 256.

Atomic DDL

First, let's understand what atomicity is? Atomicity means that a transaction execution either succeeds completely or fails completely.

Prior to MySQL 8.0, due to the lack of support for atomic DDL, if the service process crashes abnormally or the server crashes abnormally, it may cause inconsistencies between the data dictionary, storage engine structure, and binary log.

In MySQL 8.0, data dictionaries were transformed into InnoDB storage engine tables, and atomic DDL was also introduced. Atomic DDL is to execute data dictionary updates, storage engine operations, and binary log writes in the same transaction. Either all are successfully committed or all fail and roll back.

Next, let's take a look at an example to understand atomic DDL. In this example, DROP TABLE t1 and t2 belong to the same transaction. In version 5.7, a transaction partially succeeded but partially failed, i.e., DROP TABLE t1 succeeded but DROP TABLE t2 failed. However, in version 8.0, the entire transaction failed and rolled back because DROP TABLE t2 failed. This example illustrates the difference between atomicity and non-atomicity.

Version 5.7:
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)

Version 8.0:
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 |
+----------------+

After having a preliminary understanding of atomic DDL, let's introduce the specific process:

(1) prepare: Create the required objects and write the DDL log to mysql.innodb_ddl_log; the DDL log records how to roll forward and roll back DDL operations.
(2) perform: execute DDL operation.
(3) commit: Update the data dictionary and commit.
(4) post-ddl: replay and delete ddl logs. The DDL log will continue to be saved in mysql.innodb_ddl_log only when the instance crashes abnormally. After the instance is restarted, the DDL log will be replayed and deleted during the instance recovery phase. If step 3 - data dictionary update has been successfully submitted and written to the redo log and binlog, the DDL operation is successful. Otherwise, the DDL operation fails and is rolled back according to the DDL log.

Finally, let me introduce how to view DDL logs.

One method is to access the mysql.innodb_ddl_log table at debug level for viewing (not recommended)

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 UTF8_BIN,
 new_file_path VARCHAR(512) COLLATE UTF8_BIN,
 KEY(thread_id)
);

Another way is to print the DDL log to the error log for viewing (recommended)

mysql> set global innodb_print_ddl_logs=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global LOG_ERROR_VERBOSITY=3;
Query OK, 0 rows affected (0.00 sec)

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

$ tail -100f mysql-error.log
2020-08-17T19:55:09.804345+08:00 73 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=57, thread_id=73, space_id=12, old_file_path=./test/test.ibd]
2020-08-17T19:55:09.804396+08:00 73 [Note] [MY-012478] [InnoDB] DDL log delete : 57
2020-08-17T19:55:09.816850+08:00 73 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=58, thread_id=73, table_id=1069, new_file_path=test/test]
2020-08-17T19:55:09.816887+08:00 73 [Note] [MY-012478] [InnoDB] DDL log delete : 58
2020-08-17T19:55:09.820623+08:00 73 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=59, thread_id=73, space_id=12, index_id=160, page_no=4]
2020-08-17T19:55:09.820673+08:00 73 [Note] [MY-012478] [InnoDB] DDL log delete : 59
2020-08-17T19:55:09.837695+08:00 73 [Note] [MY-012485] [InnoDB] DDL log post ddl: begin for thread id: 73
2020-08-17T19:55:09.837721+08:00 73 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 73

Summarize

MySQL 8.0's improvements to the data dictionary bring many benefits, including unified metadata management, data dictionary caching, information_schema performance improvement, atomic DDL, and more.

The above is the detailed analysis of the new features of MySQL 8.0 - transactional data dictionary and atomic DDL. For more information about the new features of MySQL 8.0, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • How to quickly add columns in MySQL 8.0
  • Detailed explanation of the use of MySQL Online DDL
  • How to solve the synchronization delay caused by MySQL DDL
  • Detailed explanation of MySQL 8.0 atomic DDL syntax
  • MySQL online DDL tool gh-ost principle analysis
  • Use of MySQL DDL statements
  • Summary of common Mysql DDL operations
  • Basic statements of MySQL data definition language DDL
  • MySQL 8.0 DDL atomicity feature and implementation principle
  • Summary of using MySQL online DDL gh-ost
  • Solve the problem of blocking positioning DDL in MySQL 5.7
  • MySQL 8.0 new features: support for atomic DDL statements
  • MySQL exposes Riddle vulnerability that can cause username and password leakage
  • Summary of MySQL 8.0 Online DDL Quick Column Addition

<<:  One line of code teaches you how to hide Linux processes

>>:  Vue-router does not allow navigation to the current location (/path) Error reasons and fixes

Recommend

Detailed explanation of CSS animation attribute keyframes

How long has it been since I updated my column? H...

Detailed explanation of MySQL syntax, special symbols and regular expressions

Mysql commonly used display commands 1. Display t...

How to install MySQL 8.0 database on M1 chip (picture and text)

1. Download First of all, I would like to recomme...

Analysis of Hyper-V installation CentOS 8 problem

CentOS 8 has been released for a long time. As so...

The meaning and calculation method of QPS and TPS of MySQL database

When doing DB benchmark testing, qps and tps are ...

How to use Vue+ElementUI Tree

The use of Vue+ElementUI Tree is for your referen...

A brief discussion on logic extraction and field display of Vue3 in projects

Table of contents Logical Layering Separate busin...

Implementation of mysql backup strategy (full backup + incremental backup)

Table of contents Design scenario Technical Point...

TCP third handshake data transmission process diagram

The process packets with the SYN flag in the RFC7...

MySQL query method with multiple conditions

mysql query with multiple conditions Environment:...

Detailed explanation of the solution to permission denied in Linux

Permission denied: The reason for this is: there ...

Gojs implements ant line animation effect

Table of contents 1. Gojs Implementation 1. Drawi...

How to Develop a Progressive Web App (PWA)

Table of contents Overview Require URL of the app...

Detailed explanation of sshd service and service management commands under Linux

sshd SSH is the abbreviation of Secure Shell, whi...

CSS3 uses transform to create a moving 2D clock

Now that we have finished the transform course, l...