Is your DDL execution transactional
Recently, flyway was integrated into the project. In terms of transaction processing in the official document, an interesting paragraph was found:
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.
The above general meaning is that during the migration of flyway, if the database supports DDL in the transaction, if an error occurs, it will be rolled back cleanly; On the contrary, if it is not supported, flyway will not be rolled back cleanly, and the records in the migration record table (the default is flyway_schema_history) will be set as failed.
There is also a sentence: by for example issuing an implicit commit before and after every DDL statement, that is to say, there will be implicit commit when some databases execute DDL. So what's going on? Let's study it together.
DDL
First, what is DDL? DDL = Data Define Language, which is used to define the data structure. The DDL we usually talk about is basically SQL DDL, such as create table t (column int) statement. Of course, it's not just table creation statements. Operations involving modifying table names, adding and deleting columns, and changing column types are counted as DDL. When we execute DDL, it is also necessary to wrap it in the transaction. Although it is not easy to make mistakes in syntax and data, if we encounter "DDL failure due to full database disk", we can still do rollback operation to recover.
routine
We all know that transactions in the database will follow the ACID principle, that is, atomicity, consistency, isolated and durable. sql statements in a transaction are either fully executed or not executed. In this article, we execute multiple DDL statements in one transaction, such as a statement to create a table and a statement to add a new column. We also hope that all these DDLS can be committed or rolled back. Let's take 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");Next, we plan to add two 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);
The processing results are as follows, which is exactly what we want:
# SELECT * FROM users; name │ age │ gender ───────┼─────┼──────── Alice │ 25 │ female (1 row)
Transaction fallback?
Here are the key points. Before adding two columns, the data in the original table is as follows:
SELECT * FROM users;
name
─────────────────────
Alice 25 female
Boberror
(2 row)
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: ""
Ah, due to data error, processing failure and transaction rollback, we process the error data and then re execute the above statement.
After processing, when we re execute the above statement, we may get this error:
# alter table users add column age integer not null; ERROR: column "age" of relation "users" already exists
WTF？ Why? When we first ran the upgrade process, we didn't run it in a transaction. Each DDL statement is submitted immediately after execution. Therefore, the current state of our database is semi migrated: our table structure has been updated, but the data has not been migrated.
The database deceived us!
Some database systems (such as MySQL) do not support running DDL in transactions, so they have no choice but to run three operations (ALTER, ALTER and then UPDATE) as three different operations: if any of them fails, they cannot recover and return to the initial 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 ingredients; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | name | text | NO | | NULL | | | quantity | int(11) | YES | | NULL | | | unit | text | YES | | NULL | | +----------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
In the above output, we can see that we issued two DDL statements in a transaction, and then we rolled back the transaction. MySQL doesn't output any errors at any time, which makes us think it hasn't changed our table. However, when examining the schema of the database, we can see that nothing has been rolled back. MySQL not only does not support transactional DDL, but also does not explicitly indicate that it does not have rollback!
On the contrary, in SQL server, the table structure can be perfectly fallback.
This feature is worth noting
Transactional DDL is a feature often ignored by software engineers, but it is a key feature in managing the database life cycle.
This is also true in Flyway. If the migration script with DDL fails to execute, if the database does not support transactional DDL, the execution record will only be set as failed. (after Flyway completely rolls back the migration operation, it will delete the execution record instead of setting it to failed state.) At this time, we have the following three options to solve / avoid this problem:
- You must determine where the upgrade script stops, roll back the upgrade by yourself, fix the fault, and then run the upgrade process again.
- You must predict each potential upgrade failure, write a rollback program for each case, and test each case.
- A database system that uses transaction handling DDL.
There is no doubt that scheme 3 is the best, so sometimes mysql is not necessarily the best choice, and postgreSQL is also very good. The next time you use a database, you need to think carefully about database migration~