Recently, when integrating Flyway into the project, I found an interesting paragraph in the official documentation regarding transaction processing:

If your database cleanly supports DDL statements within a transaction, failed migrations will always be rolled back (unless they were marked as non-transactional).

If on the other hand your database does NOT cleanly supports DDL statements within a transaction (by for example issuing an implicit commit before and after every DDL statement), Flyway won’t be able to perform a clean rollback in case of failure and will instead mark the migration as failed, indicating that some manual cleanup may be required.

If the database supports DDL in the transaction, the flyway will roll back cleanly if errors occur. Otherwise, Flyway does not roll back cleanly and sets the record in the migration record table (default is flyway_schemA_history) to failure.

By for example issuing an implicit commit before and after every DDL statement So what’s going on here? Let’s find out.

DDL

First of all, what is DDL? DDL = Data Define Language (DDL), used to Define Data structures. DDL is basically SQL DDL, such as CREATE TABEL T (Column Int) statement. Of course, it’s not just table construction. Operations that involve changing table names, adding or deleting columns, changing column types, and so on count as DDL. It is also necessary to wrap the DDL in a transaction when executing the DDL. Although the syntax and data are not prone to error, you can still rollback the DDL if you encounter something like “DDL failed because database disk is full”.

transactional

As we all know, transactions in databases follow the ACID principle, namely, atomicity, consistency, isolation and durability. SQL statements in a transaction are either executed entirely or not executed at all. In this article, we execute multiple DDL statements in a single transaction, such as a table building statement, a new column statement, and we expect all of these DDLS to be committed or rolled back. To take an example (PostgreSQL as an example), first create a table and insert data:

create table users ( name varchar(256) not null );
insert into users values ("Alice 25 female");
Copy the code

Next, we are going to add two new columns: age and gender, and process the original data

alter table users add column age integer not null;
alter table users add column gender varchar(10) not null;
update users set name=split_part(name,' ',1), age=split_part(name, ' ',2)::int, gender=split_part(name, ' ',3);
Copy the code

The result is exactly what we want:

# SELECT * FROM users; The name │ age │ gender ─ ─ ─ ─ ─ ─ ─ ┼ ─ ─ ─ ─ ─ ┼ ─ ─ ─ ─ ─ ─ ─ ─ Alice │ │ 25 female row (1)Copy the code

Transaction rollback?

Here’s the point. If you add two columns to the table, the original data is as follows:

SELECT * FROM users;

The name ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ Alice 25 female Boberror (row 2)

The statement is then executed in a transaction

# alter table users add column age integer not null; ALTER TABLE # alter table users add column gender varchar(10) not null; ALTER TABLE # update users set name=split_part(name,' ',1), age=split_part(name, ' ',2)::int, gender=split_part(name, ", 3); ERROR: invalid input syntax for integer: ""Copy the code

The transaction is rolled back due to a data error, and we will execute the above statement again after processing the data error.

When we re-execute the above statement after processing, we may get this error:

# alter table users add column age integer not null;
ERROR:  column "age" of relation "users" already exists
Copy the code

WTF? Why is that? When we first ran the upgrade process, we did not run it in a transaction. Each DDL statement is committed immediately after execution. Thus, the current state of our database is semi-migrated: our table structure has been updated, but no data has been migrated.

The database lied to us!

Some database systems (such as MySQL) do not support running DDL in a transaction, so there is no choice but to run the three operations (ALTER, ALTER, and then UPDATE) as three different operations: if any of them fail, there is no way to recover and return to the original state.

Let’s take a look at mysql

mysql> CREATE TABLE users (name text NOT NULL); Query OK, 0 rows affected (0.03 SEC) mysql> BEGIN; Query OK, 0 rows affected (0.00 SEC) mysql> ALTER TABLE users ADD COLUMN age INTEGER; Query OK, 0 rows affected (0.05 SEC) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE users ADD COLUMN gender text; Query OK, 0 rows affected (0.01 SEC) Records: 0 Duplicates: 0 Warnings: 0 mysql> ROLLBACK; Query OK, 0 rows affected (0.00 SEC) mysql> DESC 10 feta; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | name | text | NO | | NULL | | | quantity | int(11) | YES | | NULL | | | unit | text | YES | | NULL | | + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

In the output above, you can see that we issued two DDL statements in one transaction, and then we rolled back the transaction. At no time did MySQL output any errors, leading us to believe that it had not changed our tables. However, when we examine the schema of the database, we can see that nothing has been rolled back. Not only does MySQL not support transactional DDL, but it also doesn’t explicitly state that it doesn’t have ROLLBACK!

In sqlserver, on the other hand, the table structure can be perfectly rolled back.

This feature is worth noting

Transactional DDL is a feature often overlooked by software engineers, but it is a key feature for managing the life cycle of a database.

This is also the case in Flyway where if a migration script with DDL fails to execute, the execution record is simply set to fail if the database does not support transactional DDL. (After Flyway rolls back the migration operation completely, it deletes the execution record instead of putting it in a failed state.) At this point we have the following three options to solve/avoid the problem:

  1. You must determine where the upgrade script stopped, roll back the upgrade yourself, fix the fault, and then rerun the upgrade process.
  2. You must anticipate each potential upgrade failure, write a rollback program for each, and test each case.
  3. Use a database system that handles transaction DDL.

Without a doubt, plan 3 is the best, so sometimes mysql is not the best choice, and postgreSQL is good too. The next time you use a database, you need to think carefully about database migration