Description\n-----------\n\nWe improved readability for DDL (Data Definition Language) operations to make most of them atomic, and the rest crash-safe, even if the server crashes in the middle of an operation.\n\nThese improvements were made in MariaDB 10.6.1.\n\nThe design of Atomic/Crash-safe DDL (MDEV-17567) allows it to work with all storage engines.\n\nDefinitions\n\n Atomic means that either the operation succeeds (and is logged to the binary log or is completely reversed.\n Crash-safe means that in case of a crash, after the server has restarted, all tables are consistent, there are no temporary files or tables on disk and the binary log matches the status of the server.\n DDL Data definition language.\n DML Data manipulation language.\n 'DDL recovery log' or 'DDL log' for short, is the new log file, ddl_recovery.log by default, that stores all DDL operations in progress. This is used to recover the state of the server in case of sudden crash.\n\nBackground\n\nBefore 10.6, in case of a crash, there was a small possibility that one of the following things could happen:\n\n There could be temporary tables starting with #sql-alter or #sql-shadow or temporary files ending with '' left.\n The table in the storage engine and the table's .frm file could be out of sync.\n During a multi-table rename, only some of the tables were renamed.\n\nWhich DDL Operations are Now Atomic\n\n CREATE TABLE, except when used with CREATE OR REPLACE, which is only crash safe.\n RENAME TABLE and RENAME TABLES.\n CREATE VIEW\n CREATE SEQUENCE\n CREATE TRIGGER\n DROP TRIGGER\n DROP TABLE and DROP VIEW. Dropping multiple tables is only crash safe.\n ALTER TABLE\n ALTER SEQUENCE is not listed above as it is internally implemented as a DML.\n\nWhich DDL Operations are Now Crash Safe\n\nDROP TABLE of Multiple Tables.\n\nDROP TABLE over multiple tables is treated as if every DROP is a separate, atomic operation. This means that after a crash, all fully, or partly, dropped tables will be dropped and logged to the binary log. The undropped tables will be left untouched.\n\nCREATE OR REPLACE TABLE\n\nCREATE OR REPLACE TABLE foo is implemented as:\n\n``sql\nDROP TABLE IF EXISTS foo;\nCREATE TABLE foo ...\n`\n\nThis means that if there is a crash during CREATE TABLE then the original table 'foo' will be dropped even if the new table was not created.\\\nIf the table was not re-created, the binary log will contain theDROP TABLE.\n\nDROP DATABASE\n\nDROP DATABASE is implemented as:\n\n`sql\n  DROP TABLE table\nLOOP OVER ALL tables\n`\n\nEach DROP TABLE is atomic, but in case of a crash, things will work the same way as DROP TABLE with multiple tables.\n\nAtomic with Different Storage Engines\n\nAtomic/Crash-safe DDL works with all storage engines that either have atomic DDLs internally or are able to re-execute DROP or RENAME in case of failure.\n\nThis should be true for most storage engines. The ones that still need some work are:\n\n The S3 storage engine.\n The partitioning engine. Partitioning should be atomic for most cases, but there are still some known issues that need to be tested and fixed.\n\nThe DDL Log Recovery File\n\nThe new startup option --log-ddl-recovery=path (ddl_recovery.log by default) can be used to specify the place for the DDL log file. This is mainly useful in the case when one has a filesystem on persistent memory, as there is a lot of sync on this file during DDL operations.\n\nThis file contains all DDL operations that are in progress.\n\nAt MariaDB server startup, the DDL log file is copied to a file with the same base name but with a backup.log suffix. This is mainly done to be able to find out what went wrong if recovery fails.\n\nIf the server crashes during recovery (unlikely but possible), the recovery will continue where it was before. The recovery will retry each entry up to 3 times before giving up and proceeding with the next entry.\n\nConclusions\n\n We believe that a clean separation of layers leads to an easier-to-maintain solution. The Atomic DDL implementation in MariaDB 10.6 introduced minimal changes to the storage engine API, mainly for native ALTER TABLE.\n In our InnoDB implementation, no file format changes were needed on top of the RENAME undo log that was introduced in MariaDB 10.2.19 for a backup-safe TRUNCATE re-implementation. Correct use of sound design principles (write-ahead logging and transactions; also file creation now follows the ARIES protocol) is sufficient. We removed the hacks (at most one CREATE or DROP per transaction) and correctly implemented rollback and purge` triggers for the InnoDB SYS_INDEXES table.\n Numerous DDL recovery bugs in InnoDB were found and fixed quickly thanks to rr-project.org. We are still working on one: data files must not be deleted before the DDL transaction is committed.\n\nThanks to Atomic/Crash-safe DDL, the MariaDB server is now much more stable and reliable in unstable environments. There is still ongoing work to fix the few remaining issues mentioned above to make all DDL operations Atomic.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-definition/atomic-ddl