Flyway Repair With Spring Boot – 用Spring Boot修复Flyway

最后修改: 2020年 8月 6日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

Flyway migrations don’t always go according to plan. In this tutorial, we’ll explore the options we have for recovering from a failed migration.

Flyway迁移并不总是按计划进行。在本教程中，我们将探讨我们有哪些选项可以从失败的迁移中恢复过来。

2. Setup

2.设置

Let’s start with a basic Flyway configured Spring Boot project. It has the flyway-core, spring-boot-starter-jdbc, and flyway-maven-plugin dependencies.

让我们从一个基本的Flyway配置的Spring Boot项目开始。它有flyway-core、spring-boot-starter-jdbc和flyway-maven-plugin依赖。

For more configuration details please refer to our article that introduces Flyway.

关于更多的配置细节，请参考我们的文章，介绍Flyway。

2.1. Configuration

2.1.配置

First, let’s add two different profiles. This will enable us to easily run migrations against different database engines:

首先，让我们添加两个不同的配置文件。这将使我们能够轻松地针对不同的数据库引擎运行迁移。

<profile>
    <id>h2</id>
    <activation>
        <activeByDefault>true</activeByDefault>
    </activation>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
	    <artifactId>h2</artifactId>
        </dependency>
    </dependencies>
</profile>
<profile>
    <id>postgre</id>
    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
    </dependencies>
</profile>

Let’s also add the Flyway database configuration files for each of these profiles.

让我们也为每个配置文件添加Flyway数据库的配置文件。

First, we create the application-h2.properties:

首先，我们创建application-h2.properties。

flyway.url=jdbc:h2:file:./testdb;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE;MODE=MySQL;DATABASE_TO_UPPER=false;
flyway.user=testuser
flyway.password=password

And after that, let’s create the PostgreSQL application-postgre.properties:

之后，让我们创建PostgreSQL的application-postgre.properties。

flyway.url=jdbc:postgresql://127.0.0.1:5431/testdb
flyway.user=testuser
flyway.password=password

Note: We can either adjust the PostgreSQL configuration to match an already existing database, or we can use the docker-compose file in the code sample.

注意：我们可以调整PostgreSQL的配置以匹配已经存在的数据库，或者我们可以使用代码示例中的docker-compose文件。

2.2. Migrations

2.2.迁移

Let’s add our first migration file, V1_0__add_table.sql:

让我们添加第一个迁移文件，V1_0__add_table.sql。

create table table_one (
  id numeric primary key
);

Now let’s add a second migration file that contains an error, V1_1__add_table.sql:

现在让我们添加第二个包含错误的迁移文件，V1_1__add_table.sql:。

create table <span style="color: #ff0000">table_one</span> (
  id numeric primary key
);

We’ve made a mistake on purpose by using the same table name. This should lead to a Flyway migration error.

我们已经故意犯了一个错误，使用了相同的表名。这应该会导致Flyway迁移错误。

3. Run the Migrations

3.运行迁移程序

Now, let’s run the application and try to apply the migrations.

现在，让我们运行应用程序并尝试应用迁移。

First for the default h2 profile:

首先是默认的h2配置文件。

mvn spring-boot:run

Then for the postgre profile:

然后为postgre配置文件。

mvn spring-boot:run -Ppostgre

As expected, the first migration was successful, while the second failed:

正如预期的那样，第一次迁移是成功的，而第二次则失败了。

Migration V1_1__add_table.sql failed
...
Message    : Table "TABLE_ONE" already exists; SQL statement:

3.1. Checking the State

3.1.检查状态

Before moving on to repair the database, let’s inspect the Flyway migration state by running:

在继续修复数据库之前，让我们通过运行检查Flyway迁移的状态。

mvn flyway:info -Ph2

This returns, as expected:

这就像预期的那样，返回了。

+-----------+---------+-------------+------+---------------------+---------+
| Category  | Version | Description | Type | Installed On        | State   |
+-----------+---------+-------------+------+---------------------+---------+
| Versioned | 1.0     | add table   | SQL  | 2020-07-17 12:57:35 | Success |
| Versioned | 1.1     | add table   | SQL  | 2020-07-17 12:57:35 | <span style="color: #ff0000">Failed</span>  |
+-----------+---------+-------------+------+---------------------+---------+

But when we check the state for PostgreSQL with:

但当我们用以下方法检查PostgreSQL的状态时。

mvn flyway:info -Ppostgre

We notice the state of the second migration is Pending and not Failed:

我们注意到第二次迁移的状态是Pending而不是Failed:。

+-----------+---------+-------------+------+---------------------+---------+
| Category  | Version | Description | Type | Installed On        | State   |
+-----------+---------+-------------+------+---------------------+---------+
| Versioned | 1.0     | add table   | SQL  | 2020-07-17 12:57:48 | Success |
| Versioned | 1.1     | add table   | SQL  |                     | <span style="color: #339966">Pending</span> |
+-----------+---------+-------------+------+---------------------+---------+

The difference comes from the fact that PostgreSQL supports DDL transactions while others like H2 or MySQL don’t. As a result, PostgreSQL was able to rollback the transaction for the failed migration. Let’s see how this difference affects things when we try to repair the database.

区别来自于PostgreSQL支持DDL事务，而其他的如H2或MySQL不支持。因此，PostgreSQL能够回滚迁移失败的事务。让我们看看当我们试图修复数据库时，这种差异是如何影响的。

3.2. Correct the Mistake and Re-Run the Migration

3.2.纠正错误并重新运行迁移程序

Let’s fix the migration file V1_1__add_table.sql by correcting the table name from table_one to table_two.

让我们修复迁移文件V1_1__add_table.sql，将表名从table_one改为table_two.。

Now, let’s try and run the application again:

现在，让我们再次尝试运行该应用程序。

mvn spring-boot:run -Ph2

We now notice that the H2 migration fails with:

我们现在注意到，H2迁移失败了。

Validate failed: 
Detected failed migration to version 1.1 (add table)

Flyway will not re-run the version 1.1 migration as long as an already failed migration exists for this version.

只要这个版本的迁移已经失败，Flyway就不会重新运行版本1.1的迁移。

On the other hand, the postgre profile ran successfully. As mentioned earlier, due to the rollback, the state was clean and ready to apply the corrected migration.

另一方面，postgre配置文件成功运行。如前所述，由于回滚，状态是干净的，可以应用修正后的迁移。

Indeed, by running mvn flyway:info -Ppostgre we can see both migrations applied with Success. So, in conclusion, for PostgreSQL, all we had to do was correct our migration script and re-trigger the migration.

事实上，通过运行mvn flyway:info -Ppostgre，我们可以看到两个迁移都应用了Success。所以，总的来说，对于PostgreSQL，我们所要做的就是纠正我们的迁移脚本并重新触发迁移。

4. Manually Repair the Database State

4.手动修复数据库状态

The first approach to repair the database state is to manually remove the Flyway entry from flyway_schema_history table.

修复数据库状态的第一个方法是手动从flyway_schema_history 表中删除Flyway条目。

Let’s simply run this SQL statement against the database:

让我们简单地对数据库运行这个SQL语句。

delete from flyway_schema_history where version = '1.1';

Now, when we run mvn spring-boot:run again, we see the migration successfully applied.

现在，当我们再次运行mvn spring-boot:run时，我们看到迁移成功应用。

However, directly manipulating the database might not be ideal. So, let’s see what other options we have.

然而，直接操作数据库可能并不理想。因此，让我们看看我们还有什么其他的选择。

5. Flyway Repair

5.Flyway维修

5.1. Repair a Failed Migration

5.1.修复一个失败的迁移

Let’s move forward by adding another broken migration V1_2__add_table.sql file, running the application and getting back to a state where we have a failed migration.

让我们继续前进，添加另一个破碎的迁移V1_2__add_table.sql文件，运行应用程序，回到我们有一个失败的迁移状态。

Another way to repair the database state is by using the flyway:repair tool. After correcting the SQL file, instead of manually touching the flyway_schema_history table, we can instead run:

另一种修复数据库状态的方法是使用 flyway:repair 工具。在纠正了SQL文件后，我们可以不手动触摸flyway_schema_history表，而是运行。

mvn flyway:repair

which will result in:

这将导致。

Successfully repaired schema history table "PUBLIC"."flyway_schema_history"

Behind the scenes, Flyway simply removes the failed migration entry from the flyway_schema_history table.

在幕后，Flyway只是从flyway_schema_history表中删除失败的迁移条目。

Now, we can run flyway:info again and see the state of the last migration changed from Failed to Pending.

现在，我们可以再次运行flyway:info，看到上次迁移的状态从Failed变为Pending。

Let’s run the application again. As we can see, the corrected migration is now successfully applied.

让我们再次运行该应用程序。正如我们所看到的，修正后的迁移现在已经成功应用。

5.2. Realign Checksums

5.2.重新调整校验和

It’s generally recommended never to change successfully applied migrations. But there might be cases where there is no way around it.

一般来说，我们建议不要改变已经成功应用的迁移。但是，在某些情况下，可能没有办法解决这个问题。

So, in such a scenario, let’s alter migration V1_1__add_table.sql by adding a comment at the beginning of the file.

因此，在这样的情况下，让我们改变迁移V1_1__add_table.sql，在文件的开头添加一个注释。

Running the application now, we see a “Migration checksum mismatch” error message like:

现在运行应用程序，我们看到一个“迁移校验和不匹配 “的错误信息，如。

Migration checksum mismatch for migration version 1.1
-> Applied to database : 314944264
-> Resolved locally    : 1304013179

This happens because we altered an already applied migration and Flyway detects an inconsistency.

这是因为我们改变了一个已经应用的迁移，而Flyway检测到了不一致的地方。

In order to realign the checksums, we can use the same flyway:repair command. However, this time no migration will be executed. Only the checksum of the version 1.1 entry in the flyway_schema_history table will be updated to reflect the updated migration file.

为了重新调整校验和，我们可以使用相同的flyway:repair命令。然而，这一次将不执行迁移。只有flyway_schema_history表中的version 1.1条目的校验和将被更新以反映更新的迁移文件。

By running the application again, after the repair, we notice the application now starts successfully.

通过再次运行应用程序，在修复之后，我们注意到应用程序现在成功启动了。

Note that, in this case, we’ve used flyway:repair via Maven. Another way is to install the Flyway command-line tool and run flyway repair. The effect is the same: flyway repair will remove failed migrations from the flyway_schema_history table and realign checksums of already applied migrations.

注意，在本例中，我们通过Maven使用了flyway:repair。另一种方法是安装Flyway命令行工具并运行flyway repair。其效果是一样的：flyway repair将从flyway_schema_history表中删除失败的迁移，并重新调整已应用的迁移的校验和。

6. Flyway Callbacks

6.航道回调

If we don’t want to manually intervene, we could consider an approach to automatically clean the failed entries from the flyway_schema_history after a failed migration. For this purpose, we can use the afterMigrateError Flyway callback.

如果我们不想手动干预，我们可以考虑一种方法，在迁移失败后自动清理flyway_schema_history中的失败条目。为此，我们可以使用afterMigrateError Flyway回调。

We first create the SQL callback file db/callback/afterMigrateError__repair.sql:

我们首先创建SQL回调文件db/callback/afterMigrateError__repair.sql。

DELETE FROM flyway_schema_history WHERE success=false;

This will automatically remove any failed entry from the Flyway state history, whenever a migration error occurs.

只要发生迁移错误，这将自动从Flyway状态历史中删除任何失败的条目。

Let’s create an application-callbacks.properties profile configuration that will include the db/callback folder in the Flyway locations list:

让我们创建一个application-callbacks.properties配置文件配置，将db/callback文件夹列入Flyway位置列表。

spring.flyway.locations=classpath:db/migration,classpath:db/callback

And now, after adding yet another broken migration V1_3__add_table.sql, we run the application including the callbacks profile:

而现在，在添加了另一个破碎的迁移V1_3__add_table.sql之后，我们运行包括callbacks配置文件的应用程序。

mvn spring-boot:run -Dspring-boot.run.profiles=h2,callbacks
...
Migrating schema "PUBLIC" to version 1.3 - add table
Migration of schema "PUBLIC" to version 1.3 - add table failed!
...
Executing SQL callback: afterMigrateError - repair

As expected, the migration failed but the afterMigrateError callback ran and cleaned up the flyway_schema_history.

正如预期的那样，迁移失败了，但是afterMigrateError回调运行并清理了flyway_schema_history。

Simply correcting the V1_3__add_table.sql migration file and running the application again will be enough to apply to corrected migration.

只需纠正V1_3__add_table.sql迁移文件并再次运行应用程序，就足以应用于纠正后的迁移。

7. Summary

7.摘要

In this article, we looked at different ways of recovering from a failed Flyway migration.

在这篇文章中，我们研究了从失败的飞路迁移中恢复的不同方法。

We saw how a database like PostgreSQL – that is, one that supports DDL transactions – requires no additional effort to repair the Flyway database state.

我们看到像PostgreSQL这样的数据库–也就是支持DDL事务的数据库–不需要额外的努力来修复Flyway数据库状态。

On the other hand, for databases like H2 without this support, we saw how Flyway repair can be used to clean the Flyway history and eventually apply a corrected migration.

另一方面，对于像H2这样没有这种支持的数据库，我们看到Flyway repair可以用来清理Flyway历史，并最终应用修正后的迁移。

As always the complete code is available over on GitHub.

像往常一样，完整的代码可在GitHub上获得。