Rechercher dans le manuel MySQL
13.1.1 Atomic Data Definition Statement Support
MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.
Atomic DDL is made possible by the introduction of the MySQL data dictionary in MySQL 8.0. In earlier MySQL versions, metadata was stored in metadata files, nontransactional tables, and storage engine-specific dictionaries, which necessitated intermediate commits. Centralized, transactional metadata storage provided by the MySQL data dictionary removed this barrier, making it possible to restructure DDL statement operations into atomic transactions.
The atomic DDL feature is described under the following topics in this section:
Supported DDL Statements
The atomic DDL feature supports both table and non-table DDL
statements. Table-related DDL operations require storage engine
support, whereas non-table DDL operations do not. Currently,
only the InnoDB
storage engine supports
atomic DDL.
Supported table DDL statements include
CREATE
,ALTER
, andDROP
statements for databases, tablespaces, tables, and indexes, and theTRUNCATE TABLE
statement.Supported non-table DDL statements include:
The following statements are not supported by the atomic DDL feature:
Table-related DDL statements that involve a storage engine other than
InnoDB
.INSTALL PLUGIN
andUNINSTALL PLUGIN
statements.INSTALL COMPONENT
andUNINSTALL COMPONENT
statements.CREATE SERVER
,ALTER SERVER
, andDROP SERVER
statements.
The characteristics of atomic DDL statements include the following:
Metadata updates, binary log writes, and storage engine operations, where applicable, are combined into a single transaction.
There are no intermediate commits at the SQL layer during the DDL operation.
Where applicable:
The state of data dictionary, routine, event, and UDF caches is consistent with the status of the DDL operation, meaning that caches are updated to reflect whether or not the DDL operation was completed successfully or rolled back.
The storage engine methods involved in a DDL operation do not perform intermediate commits, and the storage engine registers itself as part of the DDL transaction.
The storage engine supports redo and rollback of DDL operations, which is performed in the Post-DDL phase of the DDL operation.
The visible behaviour of DDL operations is atomic, which changes the behavior of some DDL statements. See Changes in DDL Statement Behavior.
DDL statements, atomic or otherwise, implicitly end any
transaction that is active in the current session, as if you
had done a COMMIT
before
executing the statement. This means that DDL statements cannot
be performed within another transaction, within transaction
control statements such as
START TRANSACTION ...
COMMIT
, or combined with other statements within the
same transaction.
This section describes changes in DDL statement behavior due to the introduction of atomic DDL support.
DROP TABLE
operations are fully atomic if all named tables use an atomic DDL-supported storage engine. The statement either drops all tables successfully or is rolled back.DROP TABLE
fails with an error if a named table does not exist, and no changes are made, regardless of the storage engine. This change in behavior is demonstrated in the following example, where theDROP TABLE
statement fails because a named table does not exist:- +----------------+
- | Tables_in_test |
- +----------------+
- | t1 |
- +----------------+
Prior to the introduction of atomic DDL,
DROP TABLE
reports an error for the named table that does not exist but succeeds for the named table that does exist:NoteDue to this change in behavior, a partially completed
DROP TABLE
statement on a MySQL 5.7 master fails when replicated on a MySQL 8.0 slave. To avoid this failure scenario, useIF EXISTS
syntax inDROP TABLE
statements to prevent errors from occurring for tables that do not exist.DROP DATABASE
is atomic if all tables use an atomic DDL-supported storage engine. The statement either drops all objects successfully or is rolled back. However, removal of the database directory from the file system occurs last and is not part of the atomic transaction. If removal of the database directory fails due to a file system error or server halt, theDROP DATABASE
transaction is not rolled back.For tables that do not use an atomic DDL-supported storage engine, table deletion occurs outside of the atomic
DROP TABLE
orDROP DATABASE
transaction. Such table deletions are written to the binary log individually, which limits the discrepancy between the storage engine, data dictionary, and binary log to one table at most in the case of an interruptedDROP TABLE
orDROP DATABASE
operation. For operations that drop multiple tables, the tables that do not use an atomic DDL-supported storage engine are dropped before tables that do.CREATE TABLE
,ALTER TABLE
,RENAME TABLE
,TRUNCATE TABLE
,CREATE TABLESPACE
, andDROP TABLESPACE
operations for tables that use an atomic DDL-supported storage engine are either fully committed or rolled back if the server halts during their operation. In earlier MySQL releases, interruption of these operations could cause discrepancies between the storage engine, data dictionary, and binary log, or leave behind orphan files.RENAME TABLE
operations are only atomic if all named tables use an atomic DDL-supported storage engine.DROP VIEW
fails if a named view does not exist, and no changes are made. The change in behavior is demonstrated in this example, where theDROP VIEW
statement fails because a named view does not exist:- +----------------+------------+
- | Tables_in_test | Table_type |
- +----------------+------------+
- +----------------+------------+
Prior to the introduction of atomic DDL,
DROP VIEW
returns an error for the named view that does not exist but succeeds for the named view that does exist:Partial execution of account management statements is no longer permitted. Account management statements either succeed for all named users or roll back and have no effect if an error occurs. In earlier MySQL versions, account management statements that name multiple users could succeed for some users and fail for others.
The change in behavior is demonstrated in this example, where the second
CREATE USER
statement returns an error but fails because it cannot succeed for all named users.- +-------+
- +-------+
- | userA |
- +-------+
Prior to the introduction of atomic DDL, the second
CREATE USER
statement returns an error for the named user that does not exist but succeeds for the named user that does exist:- +-------+
- +-------+
- | userA |
- | userB |
- +-------+
NoteDue to this change in behavior, partially completed account management statements on a MySQL 5.7 master fail when replicated on a MySQL 8.0 slave. To avoid this failure scenario, use
IF EXISTS
orIF NOT EXISTS
syntax, as appropriate, in account management statements to prevent errors related to named users.
Currently, only the InnoDB
storage engine
supports atomic DDL. Storage engines that do not support atomic
DDL are exempted from DDL atomicity. DDL operations involving
exempted storage engines remain capable of introducing
inconsistencies that can occur when operations are interrupted
or only partially completed.
To support redo and rollback of 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 that are written to the
mysql.innodb_ddl_log
table during a DDL
operation, enable the
innodb_print_ddl_logs
configuration option. For more information, see
Viewing DDL Logs.
The redo logs for changes to the
mysql.innodb_ddl_log
table are flushed to
disk immediately regardless of the
innodb_flush_log_at_trx_commit
setting. Flushing the redo logs immediately avoids situations
where data files are modified by DDL operations but the redo
logs for changes to the
mysql.innodb_ddl_log
table resulting from
those operations are not persisted to disk. Such a situation
could cause errors during rollback or recovery.
The InnoDB
storage engine executes DDL
operations in phases. DDL operations such as
ALTER TABLE
may perform the
Prepare and Perform
phases multiple times prior to the Commit
phase.
Prepare: Create the required objects and write the DDL logs to the
mysql.innodb_ddl_log
table. The DDL logs define how to roll forward and roll back the DDL operation.Perform: Perform the DDL operation. For example, perform a create routine for a
CREATE TABLE
operation.Commit: Update the data dictionary and commit the data dictionary transaction.
Post-DDL: Replay and remove DDL logs from the
mysql.innodb_ddl_log
table. To ensure that rollback can be performed safely without introducing inconsistencies, file operations such as renaming or removing data files are performed in this final phase. This phase also removes dynamic metadata from themysql.innodb_dynamic_metadata
data dictionary table forDROP TABLE
,TRUNCATE TABLE
, and other DDL operations that rebuild the table.
DDL logs are replayed and removed from the
mysql.innodb_ddl_log
table during the
Post-DDL phase, regardless of whether the
transaction is committed or rolled back. DDL logs should only
remain in the mysql.innodb_ddl_log
table if
the server is halted during a DDL operation. In this case, the
DDL logs are replayed and removed after recovery.
In a recovery situation, a DDL transaction may be committed or
rolled back when the server is restarted. If the data dictionary
transaction that was performed during the
Commit phase of a DDL operation is present
in the redo log and binary log, the operation is considered
successful and is rolled forward. Otherwise, the incomplete data
dictionary transaction is rolled back when
InnoDB
replays data dictionary redo logs, and
the DDL transaction is rolled back.
To view DDL logs that are written to the
mysql.innodb_ddl_log
data dictionary table
during atomic DDL operations that involve the
InnoDB
storage engine, enable
innodb_print_ddl_logs
to have
MySQL write the DDL logs to stderr
. Depending
on the host operating system and MySQL configuration,
stderr
may be the error log, terminal, or
console window. See
Section 5.4.2.2, “Default Error Log Destination Configuration”.
InnoDB
writes DDL logs to the
mysql.innodb_ddl_log
table to support redo
and rollback of DDL operations. The
mysql.innodb_ddl_log
table is a hidden data
dictionary table that resides in the
mysql.ibd
data dictionary tablespace. Like
other hidden data dictionary tables, the
mysql.innodb_ddl_log
table cannot be accessed
directly in non-debug versions of MySQL. (See
Section 14.1, “Data Dictionary Schema”.) The structure of the
mysql.innodb_ddl_log
table corresponds to
this definition:
- );
id
: A unique identifier for a DDL log record.thread_id
: Each DDL log record is assigned athread_id
, which is used to replay and remove DDL logs that belong to a particular DDL transaction. DDL transactions that involve multiple data file operations generate multiple DDL log records.type
: The DDL operation type. Types includeFREE
(drop an index tree),DELETE
(delete a file),RENAME
(rename a file), orDROP
(drop metadata from themysql.innodb_dynamic_metadata
data dictionary table).space_id
: The tablespace ID.page_no
: A page that contains allocation information; an index tree root page, for example.index_id
: The index ID.table_id
: The table ID.old_file_path
: The old tablespace file path. Used by DDL operations that create or drop tablespace files; also used by DDL operations that rename a tablespace.new_file_path
: The new tablespace file path. Used by DDL operations that rename tablespace files.
This example demonstrates enabling
innodb_print_ddl_logs
to view
DDL logs written to strderr
for a
CREATE TABLE
operation.
[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
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-atomic-ddl.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.