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 ?
Solve it on CI/CD level and restore the old database if some error occurs? Cons: Sounds really clumsy.
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?