Introduction

Guaranteeing the atomicity of database operations is critical for maintaining data integrity. While DDL operations for InnoDB tables have long been atomic and crash-safe, we recognized that DDL operations for schemas lacked the same level of robustness. Starting from MySQL Server 9.1 Community Edition, we have improved the atomicity of CREATE SCHEMA and DROP SCHEMA. These enhancements reduce the risk of inconsistencies and make schema operations more resilient in the event of a failure.

Understanding the Issue: Use Cases

  1. CREATE SCHEMA: When a CREATE SCHEMA operation failed after the schema directory was created but before the operation was committed, an inconsistency occurred. The data dictionary had no record of the schema’s existence, but the schema directory remained in the file system, necessitating manual cleanup.
  2. DROP SCHEMA: When a DROP SCHEMA operation failed before the final step of deleting the schema directory, an inconsistency occurred. This step used to take place after the statement was committed. As a result, the data dictionary had no record of the schema’s existence, but the schema directory remained in the file system, necessitating manual cleanup.

These scenarios could leave the database in an inconsistent state because schema operations lacked crash-safety mechanisms. This necessitates manual cleanup. This is far from ideal, particularly for cloud-based Database-as-a-Service (DBaaS) solutions, where end-users often lack direct access to the filesystem.

Solution

To address the lack of atomicity with CREATE SCHEMA and DROP SCHEMA operations, we introduced a crash safety mechanism using the InnoDB DDL Log. This log is a Write-Ahead Logging (WAL) system in InnoDB, designed to ensure the atomicity of Data Definition Language (DDL) operations. As a part of this enhancement, we have introduced a new event type, DELETE_SCHEMA_DIRECTORY_LOG, in the InnoDB DDL Log. This event type logs the deletion of schema directories and, when executed, it deletes the filesystem directory specified by the old_file_path field in the log entry. 

To learn more about how Atomic DDLs work in MySQL Server, please refer to the Atomic Data Definition Statement Support. To view DDL logs related to DDL operations in the error-logs, enable the innodb_print_ddl_logs configuration variable like below:

mysql> SET PERSIST innodb_print_ddl_logs = ON;
Query OK, 0 rows affected (0.01 sec)

 

Below, we illustrate how DELETE_SCHEMA_DIRECTORY_LOG is utilized to make CREATE SCHEMA and DROP SCHEMA operations atomic.

CREATE SCHEMA

We log and commit the DELETE_SCHEMA_DIRECTORY_LOG in a separate atomic transaction. During the main schema creation transaction, we insert the corresponding delete operation, which is committed when the schema creation succeeds. This mechanism acts as a safeguard—if something goes wrong, the DDL Log mechanism (specifically the post-DDL hook, which typically executes after commit or rollback) ensures proper cleanup. If the schema creation is successful, the log entry is deleted, preventing unnecessary directory deletion: 

mysql> CREATE SCHEMA TEST_DB;
Query OK, 1 rows affected (0.00 sec)
[A Snippet from error-logs]
[Note] [MY-015503] [InnoDB] DDL log insert : [DDL record: DELETE SCHEMA DIRECTORY,
id=769, thread_id=9, old_file_path=./TEST_DB/]
[Note] [MY-012478] [InnoDB] DDL log delete : 769
[Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9
[Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9

 

If a crash occurs after the directory is created but before the full schema creation is completed, the recovery can cleanly roll back and delete the directory, avoiding any leftover directories: 

mysql> SET DEBUG = “+d,MAKE_SERVER_ABORT_AFTER_SCHEMA_DIR”;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE SCHEMA TEST_DB;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect…
[ A Snippet from error-logs ]
[Note] [MY-015503] [InnoDB] DDL log insert : [DDL record: DELETE SCHEMA DIRECTORY,
id=75, thread_id=7, old_file_path=./TEST_DB/]
[Note] [MY-012478] [InnoDB] DDL log delete : 75
SIGKILL myself

 

Below is what a recovery looks like during restart:

[ A Snippet from error-logs ]
[Note] [MY-014023] [InnoDB] Resurrected 1 transactions doing updates.
[Note] [MY-013023] [InnoDB] 1 transaction(s) which must be rolled back or cleaned up in
total 1 row operations to undo
…
[Note] [MY-012487] [InnoDB] DDL log recovery : begin
[Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SCHEMA DIRECTORY,
id=75, thread_id=7, old_file_path=./TEST_DB/]
[Note] [MY-015502] [InnoDB] deleted the schema directory: ./TEST_DB/
[Note] [MY-012488] [InnoDB] DDL log recovery : end

 

Additionally, the MySQL Server upgrade process has been modified to execute CREATE SCHEMA operations in a traditional, non-atomic manner until a critical upgrade stage (i.e. point of no return) is reached. This safeguards against a scenario where a server crash occurs after the DELETE_SCHEMA_DIRECTORY_LOG entry is committed, leaving an incompatible log record in the old DDL log (where such a log type is not supported).

DROP SCHEMA

We log the DELETE_SCHEMA_DIRECTORY_LOG within the main schema deletion transaction. The actual deletion of the directory occurs in the post-ddl hook, executed after the transaction has been committed. This ensures that if the statement is successfully executed (i.e., committed), the DELETE_SCHEMA_DIRECTORY_LOG entry will be committed, and will be replayed which will cause the schema directory deletion:

mysql> DROP SCHEMA TEST_DB;
Query OK, 1 rows affected (0.00 sec)
[ A Snippet from error-logs ]
[Note] [MY-015503] [InnoDB] DDL log insert : [DDL record: DELETE SCHEMA DIRECTORY,
id=770, thread_id=9, old_file_path=./TEST_DB/]
[Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 9
[Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SCHEMA DIRECTORY,
id=770, thread_id=9, old_file_path=./TEST_DB/]
[Note] [MY-015502] [InnoDB] deleted the schema directory: ./TEST_DB/
[Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 9

 

If a crash occurs, recovery will either roll back the transaction (if it crashed before committing) or complete the directory deletion (if it crashed after committing). The DROP SCHEMA will execute in this new atomic way only if all tables in the schema are Innodb tables, or there are no tables present in the schema.

Scenario: Recovery from an Interruption Before commit

In the event of a crash occurring after the deletion of one table (for example) out of the total n tables present in the schema, the recovery process will roll back the transaction and restore the schema to its previous consistent state. The DROP and DELETE_SPACE DDL logs shown below are utilized in the execution of DROP TABLE statements, which are executed as part of the DROP SCHEMA process.

mysql> SET DEBUG = “+d,MAKE_SERVER_ABORT_AFTER_DROPPING_ONE_TABLE”;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP DATABASE TEST_DB;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect…
[ A Snippet from error-logs ]
[Note] [MY-015503] [InnoDB] DDL log insert : [DDL record: DELETE SCHEMA DIRECTORY,
id=83, thread_id=7, old_file_path=./TEST_DB/]
[Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=84, thread_id=7, table_id=1077]
[Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE,
id=85, thread_id=7, space_id=15, old_file_path=./TEST_DB/t1.ibd]
SIGKILL myself

 

Below is what a recovery looks like during restart (the transaction is rolled-back):

[ A Snippet from error-logs ]
[Note] [MY-014023] [InnoDB] Resurrected 1 transactions doing updates.
[Note] [MY-013023] [InnoDB] 1 transaction(s) which must be rolled back or cleaned up in
total 15 row operations to undo

 

Scenario: Recovery from an Interruption After commit

In the event of a crash occurring after the commit (but before the post-ddl hook execution), the recovery will roll-forward the transaction and delete the schema directory. 

mysql> SET DEBUG = “+d,MAKE_SERVER_ABORT_BEFORE_DELETING_THE_SCHEMA_DIR_NEW_WAY”;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP DATABASE TEST_DB;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect…
[ A Snippet from error-logs ]
[Note] [MY-015503] [InnoDB] DDL log insert : [DDL record: DELETE SCHEMA DIRECTORY,
id=92, thread_id=7, old_file_path=./TEST_DB/]
SIGKILL myself

 

Below is what a recovery looks like during restart (the directory is deleted):

[ A Snippet from error-logs ]
‘[Note] [MY-012487] [InnoDB] DDL log recovery : begin
[Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SCHEMA DIRECTORY,
id=92, thread_id=7, old_file_path=./TEST_DB/]
[Note] [MY-015502] [InnoDB] deleted the schema directory: ./TEST_DB/
[Note] [MY-012488] [InnoDB] DDL log recovery : end

 

Conclusion

We recognized the challenges schema operations faced due to their lack of robustness and atomicity. To address these concerns, we have implemented enhancements in MySQL Server 9.1 Community Edition that improve the atomicity of CREATE SCHEMA and DROP SCHEMA. These changes ensure that schema operations are more resilient and reduce the risk of inconsistencies in case of a failure. We believe these improvements will significantly benefit users by providing greater confidence in managing their critical data. For more details, refer to the Release Notes.

Thanks for using MySQL!