Movatterモバイル変換

• Supported table DDL statements includeCREATE,ALTER, andDROP statements for databases, tablespaces, tables, and indexes, and theTRUNCATE TABLE statement.

• Supported non-table DDL statements include:

  • CREATE andDROP statements, and, if applicable,ALTER statements for stored programs, triggers, views, and loadable functions.

  • Account management statements:CREATE,ALTER,DROP, and, if applicable,RENAME statements for users and roles, as well asGRANT andREVOKE statements.

• Table-related DDL statements that involve a storage engine other thanInnoDB.

• INSTALL PLUGIN andUNINSTALL PLUGIN statements.

• INSTALL COMPONENT andUNINSTALL COMPONENT statements.

• CREATE SERVER,ALTER SERVER, andDROP SERVER statements.

• Metadata updates, binary log writes, and storage engine operations, where applicable, are combined into a single atomic operation.

• There are no intermediate commits at the SQL layer during the DDL operation.

• Where applicable:

  • The state of data dictionary, routine, event, and loadable function 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 operation.

  • The storage engine supports redo and rollback of DDL operations, which is performed in thePost-DDL phase of the DDL operation.

• The visible behaviour of DDL operations is atomic.

• DROP TABLE operations are fully atomic if all named tables use a storage engine which supports atomic DDL. 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.

• CREATE DATABASE andDROP DATABASE are fully atomic and crash-safe, provided that all tables in the named database use a storage engine which supports atomic DDL, in which case the statement either adds or drops all objects successfully, or is rolled back.

• For tables that do not use a storage engine which supports atomic DDL, table deletion occurs outside of the atomicDROP 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, any tables that do not use a storage engine which supports atomic DDL are dropped before tables that do so.

• CREATE TABLE,ALTER TABLE,RENAME TABLE,TRUNCATE TABLE,CREATE TABLESPACE, andDROP TABLESPACE operations for tables that use a storage engine which supports atomic DDL are either fully committed or rolled back if the server halts during their operation.RENAME TABLE operations are atomic only if all named tables use a storage engine which supports atomic DDL.

• For storage engines that support atomic DDL, theCREATE TABLE ... SELECT statement is logged as one transaction in the binary log when row-based replication is in use.

  On storage engines that support both atomic DDL and foreign key constraints, creation of foreign keys is not permitted inCREATE TABLE ... SELECT statements when row-based replication is in use. Foreign key constraints can be added later usingALTER TABLE.

  WhenCREATE TABLE ... SELECT is applied as an atomic operation, a metadata lock is held on the table while data is inserted, which prevents concurrent access to the table for the duration of the operation.

• DROP VIEW fails if a named view does not exist, and no changes are made.

• Account management statements either succeed for all named users or roll back and have no effect if an error occurs.

The redo logs for changes to themysql.innodb_ddl_log table are flushed to disk immediately regardless of theinnodb_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 themysql.innodb_ddl_log table resulting from those operations are not persisted to disk. Such a situation could cause errors during rollback or recovery.

1. Prepare: Create the required objects and write the DDL logs to themysql.innodb_ddl_log table. The DDL logs define how to roll forward and roll back the DDL operation.

2. Perform: Perform the DDL operation. For example, perform a create routine for aCREATE TABLE operation.

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

4. Post-DDL: Replay and remove DDL logs from themysql.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.

• 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 operation. DDL operations 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.