MySQL InnoDB migrations custom implementation: How to deal with DML statements which trigger a commit in the background?

After some discussion on that topic I can assume that there is a quite frustrating fact about MySQL InnoDB: It does not support (atomic) transactions when it comes to DML. If you have a database migration with data there is a fairly easy solution to make it either completely fail or finish successfully. START TRANSACTION; INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31'); INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31'); COMMIT; A transaction is an atomic unit of database operations against the data in one or more databases. Unfortunately this is not true for the following: START TRANSACTION; CREATE TABLE Persons ( PersonID int, LastName varchar(255),FirstName varchar(255)); CREATE TABLE Ducks ( DuckID int, DuckName varchar(255)); CREATE INDEX duckname_index ON Ducks (DuckName varchar(255)); COMMIT; Each of that statements will create an implicit commit, so if the migration fails in between your MySQL database is broke and half migrated. From the docs: > Some statements cannot be rolled back. In general, these include data > definition language (DDL) statements, such as those that create or > drop databases, those that create, drop, or alter tables or stored > routines.You should design your transactions not to include such > statements. If you issue a statement early in a transaction that > cannot be rolled back, and then another statement later fails, the > full effect of the transaction cannot be rolled back in such cases by > issuing a ROLLBACK statement. As we have to implement a custom migration system for a certain software we are wondering now how this could be solved? How does e.g. Symfony (https://symfony.com/) Doctrine (https://www.doctrine-project.org/) solve that internally ? Ideas: 1. Solve it on CI/CD level and restore the old database if some error occurs? Cons: Sounds really clumsy. 2. Only allow Migrations with exactly one DML statement and strictly seperate DML and DDL migrations. Cons: You will have 10 or maybe hundreds of migration files per production deployment. Still I hope there is a better way? What is the best practical solution to that problem - if any?