Ke Yuchang Qingyun Technology R&D consultant engineer is currently engaged in the R&D of RadonDB containerization. He graduated from Huazhong University of Science and Technology and has many years of experience in database kernel development.

The number of words in the article is 3800+, and the reading time is 15 minutes

Dictionary information for MySQL 5.7 is kept in non-transactional tables and in different files (.FRM, .PAR, .OPT, .TRN, .TRG, etc.). All DDL operations are not Crash Safe, and for combined DDL (ALTER multiple tables) there will be success and failure rather than total failure. In this way, there is a problem with master-slave replication, and it also makes the high-availability system based on replication no longer safe.

MySQL 8.0 introduces a new feature – atomic DDL, which solves the above problems.

DDL refers to the Data Definition Language (Data Definition Language), which is responsible for the definition of data structures and the definition of data objects. Atomic DDL means that a DDL operation is indivisible and either all succeeds or all fails.

MySQL 8.0 supports atomic DDL only on the InnoDB storage engine.

Supported statements: CREATE, ALTER, and DROP statements for databases, tablespaces, tables, indexes, and the TRUNCATE TABLE statement.

MySQL 8.0 system tables are all stored in the InnoDB storage engine, and all dictionary objects support atomic DDL.

Supported statements: CREATE and DROP, ALTER operations for stored procedures, triggers, views, and user-defined functions (UDFs), CREATE, ALTER, DROP statements for users and roles, and RENAME statements where applicable, and GRANT and REVOKE statements.

Unsupported statement:

  • INSTALL PLUGIN, UNINSTALL PLUGIN
  • INSTALL COMPONENT, UNINSTALL COMPONENT
  • REATE SERVER, ALTER SERVER, DROP SERVER

First, 8.0 stores dictionary information in the system table of the transaction engine (InnoDB storage engine). In this way, the DDL operation is transformed into a set of DML operations on the system table, so that the atomicity of the system table can be guaranteed according to the transaction rollback of the transaction engine itself after failure.

It seems that DDL atomicity can be done here, but it’s not that simple. First of all, dictionary information is not only system tables, but also a set of dictionary caches, such as:

  • Table Share Cache
  • DD cache
  • dict in InnoDB

In addition, dictionary information is only metadata of database objects. DDL operations not only need to modify dictionary information, but also actually manipulate objects, and the objects themselves are cached in memory.

  • tablespace
  • Dynamic meta
  • Btree
  • ibd file
  • The page page of the tablespace in the buffer pool

Additionally, binlog also takes into account DDL failures.

Therefore, when atomic DDL fails to process DDL, it not only directly rolls back the data of the system table, but also ensures that the memory cache and database objects can also be rolled back to a consistent state.

To address rollback of database objects in DDL failure situations, 8.0 introduces the system table DDL_LOG. The table is in the mysql library. It is invisible and cannot be manipulated by humans. If you want to see the results for this table, first compile a debug version of MySQL:

SET SESSION debug='+d,skip_dd_table_access_check';
show create table  mysql.innodb_ddl_log;

You can see the following table structure:

CREATE TABLE `innodb_ddl_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `thread_id` bigint unsigned NOT NULL,
  `type` int unsigned NOT NULL,
  `space_id` int unsigned DEFAULT NULL,
  `page_no` int unsigned DEFAULT NULL,
  `index_id` bigint unsigned DEFAULT NULL,
  `table_id` bigint unsigned DEFAULT NULL,
  `old_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `new_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `thread_id` (`thread_id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC

In 8.0, this table needs to satisfy two scenarios and two tasks:

  • Scenario 1: Conforms to the DDL failure scenario, requiring a rollback of the partially completed DDL.

  • Scenario 2: DDL is in progress, and a fault occurs (power failure, software and hardware failure, etc.), and restarting the machine needs to complete some DDL.

Two tasks:

Some people may ask, why is it necessary to perform cleanup work for successful execution?

The cleanup is performed because the ibd files and indexes cannot be recovered once deleted. In order to achieve rollback, when DDL deletes these objects, it does not actually delete them, but backs them up for use during rollback. Therefore, only after confirming that the DDL has been successfully executed and these backup objects are no longer needed, the cleanup work is performed.

In order to make this principle clear, let’s start with CREATE TABLE, which is a relatively simple process. It can be seen from the inside.Assuming that the 8.0 debug version has been compiled, and innodb_file_per_table For on, first execute the following command:

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

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

thus openingddl loglog, then create the table:

mysql> create table t2 (a int);
Query OK, 0 rows affected (25 min 26.42 sec)

You can see the following logs:

XXXXX 8 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=20, thread_id=8, space_id=6, old_file_path=./test/t2.ibd]
XXXXX 8 [Note] [MY-012478] [InnoDB] DDL log delete : 20
XXXXX 8 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=21, thread_id=8, table_id=1067, new_file_path=test/t2]
XXXXX 8 [Note] [MY-012478] [InnoDB] DDL log delete : 21
XXXXX 8 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=22, thread_id=8, space_id=6, index_id=157, page_no=4]
XXXXX 8 [Note] [MY-012478] [InnoDB] DDL log delete : 22
XXXXX 8 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 8
XXXXX 8 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 8

create table The DDL only has reverse operation logging, no cleanup operation logging. Careful readers may see a DDL log inserted into the log, and then deleted, and will be puzzled. But that’s the secret of MySQL’s atomic DDL.we choose DELETE SPACE This DDL log write functionLog_DDL::write_delete_space_log to reveal the process.

dberr_t Log_DDL::write_delete_space_log(trx_t *trx, const dict_table_t *table,

space_id_t space_id,

const char *file_path, bool is_drop,

bool dict_locked) {

ut_ad(trx == thd_to_trx(current_thd));

ut_ad(table == nullptr || dict_table_is_file_per_table(table));


if (skip(table, trx->mysql_thd)) {

return (DB_SUCCESS);

}


uint64_t id = next_id();

ulint thread_id = thd_get_thread_id(trx->mysql_thd);

dberr_t err;


trx->ddl_operation = true;


DBUG_INJECT_CRASH("ddl_log_crash_before_delete_space_log",

crash_before_delete_space_log_counter++);



if (is_drop) { //（1）

err = insert_delete_space_log(trx, id, thread_id, space_id, file_path,

dict_locked);

if (err != DB_SUCCESS) {

return err;

}


DBUG_INJECT_CRASH("ddl_log_crash_after_delete_space_log",

crash_after_delete_space_log_counter++);

} else { // （2）

err = insert_delete_space_log(nullptr, id, thread_id, space_id, file_path,

dict_locked);

if (err != DB_SUCCESS) {

return err;

}


DBUG_INJECT_CRASH("ddl_log_crash_after_delete_space_log",

crash_after_delete_space_log_counter++);


DBUG_EXECUTE_IF("DDL_Log_remove_inject_error_2",

srv_inject_too_many_concurrent_trxs = true;);


err = delete_by_id(trx, id, dict_locked); //（3）

ut_ad(err == DB_SUCCESS || err == DB_TOO_MANY_CONCURRENT_TRXS);


DBUG_EXECUTE_IF("DDL_Log_remove_inject_error_2",

srv_inject_too_many_concurrent_trxs = false;);


DBUG_INJECT_CRASH("ddl_log_crash_after_delete_space_delete",

crash_after_delete_space_delete_counter++);

}

return (err);

}

existcreate table Called during this processwrite_delete_space_log,is_drop forfalseexecute the above code to execute the branch (2) and (3) .Please be aware of insert_delete_space_log The first parameter is empty, which means that a background transaction will be created (callingtrx_allocate_for_background)insertDELETE_SPACE recorded toinnodb_ddl_log table, and then commit the transaction.notice(3) wheredelete_by_id The first parameter istrx heretrx That is, this DDL transaction,(3) The action taken is to delete in this transaction(2)Inserted record.

Why is this logic?

file

The following two situations are discussed, as shown in the figure above:

  1. If after inserting the DDL log, the various steps of the DDL are successfully executed, and the final transactiontrx successfully submitted, then innodb_ddl_log There is no record of this DDL, so in the subsequentpost_ddl does nothing (post_ddl is described later).
  2. If a step of the DDL fails after inserting the DDL log, the transaction in which the DDL is locatedtrxwill roll back.At this time, the above figuredelete [DELETE SPACE, id=20]This action also rolls back. at last,innodb_ddl_log will exist inDELETE SPACE This record, the subsequent executionpost_ddl perform a Replay to delete this failedcreate table DDL has created tablespaces. you can find,create table DDL to create a tablespace, it will definitely use this mechanism toinnodb_ddl_log Insert an opposite action inDELETE SPACElogging, so it is also known as reverse operation logging.

Other DDL log operations such asREMOVE CACHE ,FREE The writing of log records is also similar logic. Complex DDL not only inserts reverse operation log records, but also inserts cleanup operation logs.for exampleTRUNCATE The table operation will rename the original table space to a zero-time table space. After the DDL is successful, it needs to pass thepost_ddl Replay DDL log records, delete the temporary tablespace.If it fails, then post_ddlReplay the DDL log, perform the reverse operation, and rename the temporary tablespace to the original tablespace.In short, if it is a reverse operation log, usebackground trx insert and commit, then usetrx delete; if cleaning the log, usetrx Just insert it.

Notice:innodb_ddl_logA table is the same as other InnoDB tables. All operations on the table will generate Redo logs and Undo records by the InnoDB engine. Therefore, do not regard the reverse operation records in the DDL log table as Undo logs, which are not at the same abstraction level. And the reverse operation is performed in another transaction, and when rolling back, the Undo log is performed on the same original transaction.

Is it necessary for DDL to flush the log?

We know that MySQL has a innodb_flush_log_at_trx_commit The parameter, when set to 0, will not immediately flush the Redo log into persistent storage when submitting. Although it can improve performance, there is a certain probability of losing committed transactions in the event of power failure or shutdown. For DML operations, this is just a lost transaction, but for DDL, losing DDL transactions will cause database metadata to be inconsistent with other data, and the database system will not work properly.

So, intrx_commit Depending on whether the transaction is a DDL operation, special processing is performed:

regardlessinnodb_flush_log_at_trx_commitHow to set the parameters, transactions related to DDL, the log must be flushed when submitting!

DDL log write timing

After understanding the mechanism of DDL log, the author asks you a question, forcreate table For example, execute firstwrite_delete_space_log Or create a tablespace first?

Let’s first assume that the tablespace is created first (action A), and then the reverse operation log is written (action B).If A fails after execution, B has not been executed at this time, at this timecreate table action is not completed, andinnodb_ddl_log does not existDELETE SPACE With such DDL reverse logging, after the database crash is recovered, the database system will roll back the system table data, but the tablespace created by A is not deleted. Due to the existence of an intermediate state, at this timecreate table It’s not atomic DDL anymore.

Therefore, in each step in the DDL, first write the reverse operation log record of the step toinnodb_ddl_log , and then perform this step. That is to say, the timing of writing the DDL Log is before the execution step.ifcreate table The DDL log has been written, but there is a power failure without creating a tablespace?it doesn’t matter, in post_ddl When doing Replay, it will be processed.

Replay’s calling logic

After the DDL operation is completed, regardless of whether the DDL transaction is committed or rolled back, it will be calledpost_ddl function,post_ddl will callreplayThe function performs Replay.In addition, MySQL 8.0 database crash recovery process, compared with MySQL 5.7, also has moreha_post_recoverprocess, it will calllog_ddl->recover Will innodb_ddl_log All log records are Replayed.

existpost_ddlis calledreplay_by_thread_idcrash recoveryha_post_recover is calledreplay_alland its logic is described as follows:

  1. According to incomingthread_id is the index (thread_id andtrx It can be one-to-one correspondence), get all the records in reverse order, and then perform the Replay action in sequence according to the recorded content, and finally delete the replayed records.
  2. replay_all Willinnodb_ddl_log All records are acquired in reverse order, the Replay actions are performed in sequence, and finally the replayed records are deleted.

It can be seen that the above two functions have the process of obtaining the records in reverse order. Why do you want to reverse the order?

Inverse function

1. Reverse operation

If we regard each step in DDL as a function, the parameter is the database system. Assuming that the ith step function is oi, then the n steps are the composite function of the n functions:

file

That is, the inverse of the composite function is the reverse composite of the inverse function of all steps. Therefore, the reverse operation needs to process the DDL log in reverse order.

2. Cleaning operation

The cleaning action of DDL often has no order requirements, and the effect of reverse operation and forward operation is often the same, so there is no problem in uniformly performing reverse order processing.

idempotency

Similar to Redo and Undo, each type of log replay is idempotent.

The so-called idempotency means that the effect of executing it multiple times is the same as executing it once. Especially during crash recovery, when replaying the reverse operation, a power failure occurs before completion, and crash recovery is performed again. A replay operation may occur multiple times at this time.

Therefore, MySQL 8.0 implements these replay operations and must consider idempotency. The most typical is to repeat some delete operations, you must first determine whether the database object exists. If it exists, delete it, otherwise do nothing.

Tips: Speaking of which, the author recommends a book “Concrete Mathematics: A Cornerstone in Computer Science”. This book explains many mathematical knowledge and skills used in computer science, and especially focuses on algorithm analysis.

  1. DDL starts to update, no matter if it fails or not, table share will be cached and updated, tdc_remove_table;
  2. After DDL is successful, execute transaction commit, otherwise execute transaction rollback;
  3. Whether the transaction is committed or rolled back, it is called post_ddl , post_ddl The role has been described above, to r Replay system table innodb_ddl_log recorded logs;
  4. During crash recovery, in addition to executing the Redo log and rolling back uncommitted transactions, you also need to execute ha_post_recoverwhile InnoDB’s ha_post_recover is to call post_ddl Perform the reverse operation of DDL;
  5. There is only one principle in binglog processing, that is, the DDL transaction is successful.And after submitting, call write_bin_log Write binlog.

Precautions

  1. MySQL 8.0 supports atomic DDL, which does not mean that DDL can be rolled back through SQL statement commands. In fact, except for SQLServer, almost all database systems do not support the rollback of DDL SQL commands. The DDL rollback introduces far more problems than its benefits.

  2. MySQL 8.0 only promises the atomicity of a single DDL statement, and does not guarantee that multiple DDL combinations can also maintain atomicity.In order to realize a large factory Truncate table flashback only in the Server layer of MySQL truncate table Action is converted to rename table Action, when flashback, the table, index and constraint are re-executed with RENAME DDL combination to realize flashback, this is extremely dangerous, and its atomicity is not guaranteed. The author has also completed this function, not so tricky, but honestly transforms it from the Server layer, the InnoDB storage engine, and the binlog to fully guarantee its atomicity.

  3. The fact that MySQL 8.0 implements atomic DDL in this way does not imply that other databases implement atomic DDL in this way.

refer to

#Talking #principle #MySQL #atomic #DDL #RadonDBs #personal #space #News Fast Delivery

Leave a Comment

Your email address will not be published. Required fields are marked *