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

1. Overview of DDL atomicity

There is no unified data dictionary before 8.00.The metadata in dd,server layer and engine layer includes (.frm, .opt, .par, .trg, etc.), which is used to store table definition, partition table definition, trigger definition and other information. The innodb layer also has its own set of metadata, including table information, index information and so on. There is no mechanism to ensure consistency between these two sets of metadata, which may lead to metadata inconsistency in abnormal cases. In a typical scenario, the frm of the sever layer has been deleted successfully, but the data dictionary of the engine layer has not been updated, resulting in the failure of rebuilding the renamed table. Similarly, for example, drop table T1 and T2; there may be problems that only delete T1, but T2 still exists.

An important work of 8.0 is to unify the data dictionary, separate the DD (data Dictionary) module, discard the metadata of server layer, and abstract the metadata of innodb into a DD interface for server layer and innnodb layer to share. On the basis of DD, the atomicity of DDL is introduced to ensure that DDL operations are either done or not done at all. The key point of realizing this set of logic is to make the modifications involved in ddl, including dd data dictionary modification, engine layer modification (creating files, initializing tablespace, creating btree, etc.) and writing binlog as a "transaction", making use of the atomicity of transactions to ensure the atomicity of ddl operations.

The principle of atomicity realization of 2.DDL

The key to atomicity is to ensure that dd data dictionary modification, engine layer modification and writing binlog is a transaction. MySQL's existing XA transaction mechanism can effectively ensure the consistency of DML transactions and binlog. The ddl data dictionary is also stored through the innodb engine, so it is easy to make the dd data dictionary modification consistent with the binlog. Then another problem that needs to be solved is that the dd data dictionary is consistent with the engine layer modification, and the engine layer changes are not always recorded in redo, such as creating files, rename file names, or cleaning cache, etc., which can not simply solve the problem through the XA mechanism, so 8.0 also introduced a set of DDL_LOG mechanism. Specifically, some operations that do not remember redo are written to the ddl_ log table by logging, and this table is the innodb engine table. By ensuring that the ddl_log data is consistent with the dd data dictionary modification, the dd data dictionary modification, engine layer modification and write binlog consistency are finally solved.

Comparison before and after the introduction of 3.DD

​

​ ​

4.DDL operation implementation logic

After the introduction of the ddl_log table, there are some changes in the ddl operation based on the original, there are two main points, one is that during the execution of ddl, the ddl operation will be recorded in the ddl_log table; the other is that a new post_ddl phase has been added. After the ddl transaction is committed, do some ddl closing actions, such as drop-table, and the real deletion of physical files is done in the post-ddl phase. The main thing that post-ddl does is to read the ddl-log content and play back the execution. The types of ddl operations are as follows:

Enum class Log_Type: uint32_t {/ * * Smallest log type * / SMALLEST_LOG = 1 Drop an index tree * / FREE_TREE_LOG = 1 Delete a file * / DELETE_SPACE_LOG,/** Rename a file * / RENAME_SPACE_LOG,/** Drop the entry in innodb_dynamic_metadata * / DROP_LOG,/** Rename table in dict cache. * / RENAME_TABLE_LOG,/** Remove a table from dict cache * / REMOVE_CACHE_LOG,/** Alter Encrypt a tablespace * / ALTER_ENCRYPT_TABLESPACE_LOG,/** Biggest log type * / BIGGEST_LOG = ALTER_ENCRYPT_TABLESPACE_LOG}

Through the innodb_print_ddl_logs switch, you can see what is written to the innodb_ddl_ log table during ddl. The following shows how to ensure the atomicity of ddl with the ddl_log generated by several typical ddl operations.

4.1 create table

Statement: create table dd_tt (id int primary key, C1 int)

[InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=352, thread_id=23, space_id=71, old_file_path=./mysql/dd_tt.ibd] [InnoDB] DDL log delete: 352 [InnoDB] DDL log insert: [DDL record: REMOVE CACHE, id=353, thread_id=23, table_id=1128, new_file_path=mysql/dd_tt] [InnoDB] DDL log delete: 353 [InnoDB] DDL log insert: [DDL record: FREE, id=354, thread_id=23, space_id=71, index_id=231 Page_no=4] [InnoDB] DDL log delete: 354 [InnoDB] DDL log post ddl: begin for thread id: 23 [InnoDB] DDL log post ddl: end for thread id: 23

Description:

1. All insert operations are a single transaction, and the corresponding inverse delete operation is part of the entire ddl transaction.

The 2.insert operation records the reverse operation of the file operation, such as building table_space, and the reverse operation is delete_space_log.

3. If the ddl transaction is finally successful, then all reverse delete operations will eventually take effect, and the ddl_log log will be cleaned normally; if the ddl transaction fails (such as instance crash), then the delete operation will be rolled back, and replay these insert_log,recover when there are three ddl_log remaining in the ddl_ log table, that is, you can clean up the garbage generated during the ddl process.

In the case of 4.crash-recovery, if the binlog has been set down, the corresponding ddl transaction is in the prepare state, then the final transaction needs to be committed and the ddl_log is cleaned up; if the binlog is not down, the ddl transaction needs to be rolled back, and there are three records left in the ddl_ log table. After the fault recovery, these records are needed, which is actually reverse operations such as file creation and btree creation to ensure that the rollback is clean.

4.2 drop table

Statement: drop table dd_tt

[InnoDB] DDL log insert: [DDL record: DROP, id=355, thread_id=23, table_id=1128] [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=356, thread_id=23, space_id=71, old_file_path=./mysql/dd_tt.ibd] [InnoDB] DDL log post ddl: begin for thread id: 23 [InnoDB] DDL log replay: [DDL record: DELETE SPACE, id=356, thread_id=23, space_id=71, old_file_path=./mysql/dd_tt.ibd] [InnoDB] DDL log replay: [DDL record: DROP Id=355, thread_id=23, table_id=1128] [InnoDB] DDL log post ddl: end for thread id: 23

Note: for drop operations, you only operate ddl_log during execution, and do not do real drop physical table operations. In the post-ddl phase, the records in the ddl_ log table are read and replay to do the actual delete action. If the crash occurs during execution, the entire ddl transaction will be rolled back, and the contents of the ddl_log will also be rolled back, so the whole drop operation will be as if it never happened.

4.3 add index

Statement: alter table dd_tt add index idx_c1 (C1)

[InnoDB] DDL log insert: [DDL record: FREE, id=360, thread_id=23, space_id=72, index_id=233, page_no=5] [InnoDB] DDL log delete: 360 [InnoDB] DDL log post ddl: begin for thread id: 23 [InnoDB] DDL log post ddl: end for thread id: 23

Note: indexing is similar to table building. The insert operation is a transaction, committed separately, and a delete operation is recorded. This operation is part of the entire ddl transaction. If the transaction is finally committed, then the ddl-log content is deleted; if the transaction is eventually rolled back, there will be a FREE-log left in the ddl-log. Through replay, you can clean up the built index to achieve the rollback effect.

4.4 drop index

Statement: alter table dd_tt drop index idx_c1

[InnoDB] DDL log insert: [DDL record: FREE, id=361, thread_id=23, space_id=72, index_id=233, page_no=5] [InnoDB] DDL log post ddl: begin for thread id: 23 [InnoDB] DDL log replay: [DDL record: FREE, id=361, thread_id=23, space_id=72, index_id=233, page_no=5] [InnoDB] DDL log post ddl: end for thread id: 23

Description:

Similar to drop table, only logs are recorded during execution, and the real delete operation is performed during the post-ddl phase.

4.5 add column

Statement: alter table dd_tt add column c2 int

[InnoDB] DDL log post ddl: begin for thread id: 23 [InnoDB] DDL log post ddl: end for thread id: 23

Description:

The add column is instant-ddl, which only modifies metadata, similar to dml transactions, and does not rely on ddl-log to guarantee atomicity.

4.6 drop column

Statement: alter table dd_tt drop column c2

Statement decomposition:

1.prepare phase:

Create table # sql-ib1129-2815969725 [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=362, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1129-2815969725.ibd] [InnoDB] DDL log delete: 362[ InnoDB] DDL log insert: [DDL record: REMOVE CACHE, id=363, thread_id=23, table_id=1130, new_file_path=mysql/#sql-ib1129-2815969725] [InnoDB] DDL log delete: 363 [InnoDB] DDL log insert: [DDL record: FREE, id=364, thread_id=23, space_id=73, index_id=234 Page_no=4] [InnoDB] DDL log delete: 364

2.peform stage: nothing about ddl-log

3.commit phase:

3.1 alter table dd_tt rename to # sql-ib1130-2815969726

[InnoDB] DDL log insert: [DDL record: DROP, id=365, thread_id=23, table_id=1129]

[InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=366, thread_id=23, space_id=72, old_file_path=./mysql/#sql-ib1130-2815969726.ibd, new_file_path=./mysql/dd_tt.ibd] [InnoDB] DDL log delete: 366 [InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=367, thread_id=23, table_id=1129, old_file_path=mysql/#sql-ib1130-2815969726, new_file_path=mysql/dd_tt] [InnoDB] DDL log delete: 367

Reverse operation: alter table mysql/#sql-ib1130-2815969726 rename to dd_tt

3.2 alter table # sql-ib1129-2815969725 rename to dd_tt

[InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=368, thread_id=23, space_id=73, old_file_path=./mysql/dd_tt.ibd, new_file_path=./mysql/#sql-ib1129-2815969725.ibd] [InnoDB] DDL log delete: 368 [InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=369, thread_id=23, table_id=1130, old_file_path=mysql/dd_tt, new_file_path=mysql/#sql-ib1129-2815969725] [InnoDB] DDL log delete: 369

Reverse operation: alter table dd_tt rename to mysql/#sql-ib1129-2815969725

[InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=368, thread_id=23, space_id=73, old_file_path=./mysql/dd_tt.ibd, new_file_path=./mysql/#sql-ib1129-2815969725.ibd] [InnoDB] DDL log delete: 368 [InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=369, thread_id=23, table_id=1130, old_file_path=mysql/dd_tt, new_file_path=mysql/#sql-ib1129-2815969725] [InnoDB] DDL log delete: 369

Only record the operation and clean up during the post-ddl phase.

Post-ddl phase:

Drop table # sql-ib1130-2815969726; [InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=368, thread_id=23, space_id=73, old_file_path=./mysql/dd_tt.ibd, new_file_path=./mysql/#sql-ib1129-2815969725.ibd] [InnoDB] DDL log delete: 368 [InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=369, thread_id=23, table_id=1130, old_file_path=mysql/dd_tt, new_file_path=mysql/#sql-ib1129-2815969725] [InnoDB] DDL log delete: 369

Note: drop column is a ddl of copy type. The basic logic is to create a new temporary table, copy the data, and finally perform a rename operation. It mainly includes four stages:

1.prepare phase: the process of building a temporary table is similar to the ddl-log operation of the table creation process. Insert-log commits directly as a separate transaction, and delete-log is part of the whole transaction.

If an exception occurs at this stage, the reverse operation record remains in the ddl-log table. When crash-recovery, you can clean up in replay.

2.peform phase: the copy of the data is finished and the online-ddl logic is implemented.

3. After copying the data, the rename exchange table name operation needs to be performed.

1) DROP, delete temporary table

2) RENAME SPACE/TABLE renamed. / mysql/#sql-ib1130-2815969726.ibd to dd_tt.idb

3) REANAME SPACE/TABLE renamed dd_tt.idb to / # sql-ib1129-2815969725.idb

4) record the sql-ib1130-2815969726.ibd operation of deleting the old table, and do the real deletion in the post-ddl phase.

If an exception occurs at this stage, the same insert-log is a separate transaction, and delete is part of the whole transaction, and insert-log remains in the ddl-log table. Through replay, you can clean up, restore the data of dd_tt, and clean the temporary table # sql-ib1130-2815969726.ibd.

4.post-ddl phase:

1)。 Physically delete old files. / mysql/#sql-ib1130-2815969726.ibd

2)。 Clean up the relevant information in mysql.innodb_dynamic_metadata.

It should be noted that because the contents of the ddl-log table actually operate in reverse, when collecting ddl-log, it is actually collected and played back in reverse order.

4.7 truncate table

Statement: truncate table dd_tt

Statement decomposition:

1.rename dd_tt to # sql-ib1130-2815969727

[InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=372, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1130-2815969727.ibd, new_file_path=./mysql/dd_ tt.ibd [InnoDB] DDL log delete: 372

2.drop table # sql-ib1130-2815969727

[InnoDB] DDL log insert: [DDL record: DROP, id=373, thread_id=23, table_id=1130] [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=374, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1130-2815969727.ibd]

3.create table dd_tt

[InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=375, thread_id=23, space_id=74, old_file_path=./mysql/dd_tt.ibd] [InnoDB] DDL log delete: 375 [InnoDB] DDL log insert: [DDL record: REMOVE CACHE, id=376, thread_id=23, table_id=1131, new_file_path=mysql/dd_tt] [InnoDB] DDL log delete: 376 [InnoDB] DDL log insert: [DDL record: FREE, id=377, thread_id=23, space_id=74, index_id=235 Page_no=4] [InnoDB] DDL log delete: 377 [InnoDB] DDL log post ddl: begin for thread id: 23 [InnoDB] DDL log replay: [DDL record: DELETE SPACE, id=374, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1130-2815969727.ibd] [InnoDB] DDL log replay: [DDL record: DROP, id=373, thread_id=23, table_id=1130] [InnoDB] DDL log post ddl: end for thread id: 23

Description:

1. Rename dd_tt to sql-ib1130-2815969727

two。 The sql-ib1130-2815969727 table is deleted, and the post-ddl stage is really deleted.

3. Create a new table dd_tt, the same insert operation is committed as a separate transaction, the delete operation is part of the entire transaction, and if rolled back, the final remaining insert operation is cleaned through the replay action.

5.DDL operation code stack

5.1 create-table

Sql_cmd_create_table::execute-- > mysql_create_table-> mysql_create_table_no_lock-- > create_table_impl-- > rea_create_base_table-- > ha_create_table-- > ha_create-- > ha_innobase::create-- > innobase_basic_ddl::create_impl-- > create_table_info_t::create_table {. }-- > trans_commit_implicit-- > ha_commit_trans-- > MYSQL_BIN_LOG::prepare-- > ha_prepare_low / / all transaction engines prepare {binlog_prepare innobase_xa_prepare}-- > MYSQL_BIN_LOG::commit-- > MYSQL_BIN_LOG::ordered_commit-- > MYSQL_BIN_LOG::process_flush_stage_queue-- > MYSQL_BIN_LOG::flush_thread_caches -- > binlog_cache_mngr::flush-- > binlog_cache_data::flush-- > MYSQL_BIN_LOG::write_gtid-- > Log_event::write-- > MYSQL_BIN_LOG::Binlog_ofile::write / / write binlog-gtid-- > MYSQL_BIN_LOG::write_cache-- > MYSQL_BIN_LOG::do_write_cache-- > Binlog_cache _ storage::copy_to-- > stream_copy-- > Binlog_event_writer::write-- > MYSQL_BIN_LOG::Binlog_ofile::write / / write binlog-ddl statements-- > MYSQL_BIN_LOG::sync_binlog_file-- > MYSQL_BIN_LOG::process_commit_stage_queue-- > ha_commit_low {binlog_commit innobase_commit- -> trx_commit_for_mysql-- > trx_commit-- > trx_commit_low-- > trx_commit_in_memory-- > trx_undo_insert_cleanup}-- > innobase_post_ddl (ht- > post_ddl (thd))-- > Log_DDL::post_ddl-- > replay_by_thread_id-- > create_table_info_t::create_table-- > create_table_def-- > dict _ mem_table_create / / Construction innodb memory is a dictionary memory object-- > row_create_table_for_mysql-- > dict_build_table_def-- > dict_build_tablespace_for_table-- > New xxx.idb file-- > Log_DDL::write_delete_space_log {--> Log_DDL::insert_delete_space_log-- > trx_start_internal / / Internal open transaction Submit it separately. -- > construct DDL_Record (DELETE_SPACE_LOG)-- > DDL_Log_Table::insert (write to physical B-Tree)-- > Log_DDL:delete_by_id / / remove the ddl_log operation as part of the ddl transaction. }-- > fil_ibd_create-- > initialize segment,extent,page-- > Log_DDL::write_remove_cache_log-- > Log_DDL::insert_remove_cache_log-- > Log_DDL::delete_by_id-- > create_index (main table Secondary index)-- > dict_create_index_tree_in_mem-- > btr_create-- > Log_DDL::write_free_tree_log-- > Log_DDL::insert_free_tree_log-- > Log_DDL:delete_by_id

Crash-recovery-- > ha_post_recover-- > post_recover_handlerton-- > innobase_post_recover-- > Log_DDL::recover-- > Log_DDL::replay_all-- > Log_DDL::replay {replay_delete_space_log replay_remove_cache_log replay_free_tree_log. }-> delete_by_ids-- > DDL_Log_Table::remove

5.2 drop table

Mysql_rm_table-- > mysql_rm_table_no_locks-- > drop_base_table-- > ha_delete_table-- > handler::ha_delete_table-- > ha_innobase::delete_table-- > innobase_basic_ddl::delete_impl-- > row_drop_table_for_mysql-- > Log_DDL::write_drop_log / / record delete innodb_dynamic_metadata log-- > Log _ DDL::write_delete_space_log / / record and delete ibd logs-- > dd::drop_table-- > dd::cache::Dictionary_client::drop-- > dd::cache::Storage_adapter::drop-- > dd::sdi::drop-- > innobase_post_ddl-- > Log_DDL::post_ddl-- > Log_DDL::replay_by_thread_id-- > Log_DDL::replay -> Log_DDL::replay_delete_space_log / / post-ddl really delete innodb_dynamic_metadata-> Log_DDL::replay_drop_log / / post-ddl really delete ibd-- > delete_by_ids-- > DDL_Log_Table::remove

When drop table, only delete action logs are recorded as part of the transaction as a whole. If the transaction is committed, the post_ddl phase will read the log and delete it. If the transaction is rolled back, ddl_log will also be rolled back as part of the transaction.

Reference documentation

Https://dev.mysql.com/worklog/task/?id=9045

Https://dev.mysql.com/worklog/task/?id=9173

Https://dev.mysql.com/worklog/task/?id=9175

Https://dev.mysql.com/worklog/task/?id=9525

Https://dev.mysql.com/worklog/task/?id=9536

Summary

The above is the atomic characteristics and implementation principle of MySQL8.0 DDL introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

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.