订单审计表迁移失败后,Flyway 的 repair 清掉了失败记录,H2 里已经建好的表和已经提交的一条审计数据仍然保留。直接重跑,迁移又撞上了“表已存在”。本次用 Flyway 13.12.2 核对残留对象,再补齐尚未完成的 V2,恢复后的库和全新安装的库都只保留一条审计数据。
这是失败迁移恢复实操,验证的是 repair 的既有边界,不把它当成该版本的新修复。实验固定为 Flyway 13.12.2、H2 2.5.252、Spring Boot 3.5.16、Java 21;只验证 H2 文件库,没有把结果外推到所有数据库。
报错之后,库里可能已经留下了半次迁移
假设订单服务要增加审计表。旧版本只有订单表,升级脚本先建审计表、写一条初始化记录、建索引,最后一条 SQL 却引用了不存在的表。发布失败了,但不能据此判断前面的语句都被撤销。
Flyway 的事务说明把这个边界交给数据库能否完整回滚 DDL。H2 的 CREATE TABLE 和 CREATE INDEX 都会提交连接中打开的事务。下面特意把建索引放在插入之后,用来观察已提交数据是否跨进程保留。
初始迁移 fixtures/V1__orders.sql 只有一张表和一条合成订单:
CREATE TABLE orders (id INT PRIMARY KEY, status VARCHAR(20) NOT NULL);
INSERT INTO orders VALUES (1, 'NEW');
失败版本保存在 fixtures/V2-broken.sql,运行时复制为 migrations/V2__order_audit.sql:
CREATE TABLE order_audit (id INT PRIMARY KEY, order_id INT NOT NULL, event VARCHAR(40) NOT NULL);
INSERT INTO order_audit VALUES (10, 1, 'before-upgrade');
CREATE INDEX idx_order_audit_order ON order_audit(order_id);
INSERT INTO missing_migration_probe VALUES (1);
实验是本地新建的文件库,不连接真实订单系统。Boot 只负责启动容器;每次操作都由程序显式调用 Flyway API,随后关闭进程。没有依赖 Boot 的自动迁移开关,也没有在同一个 JVM 里模拟重启。
工程依赖与 application.properties
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>3.5.16</version><relativePath/></parent>
<groupId>demo</groupId><artifactId>flyway-repair-lab</artifactId><version>1.0</version>
<properties><java.version>21</java.version></properties>
<dependencies>
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency>
<dependency><groupId>org.flywaydb</groupId><artifactId>flyway-core</artifactId><version>13.12.2</version></dependency>
<dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><version>2.5.252</version></dependency>
</dependencies>
<build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build>
</project>
spring.main.banner-mode=off
spring.main.web-application-type=none
logging.level.root=OFF
src/main/java/demo/RecoveryLab.java 接受 migrate、repair、inspect、preflight 四种操作。迁移和修复始终使用同一个 filesystem:migrations;状态行来自 JDBC 查询,不是根据命令是否成功推算出来的。
完整启动与核验代码
package demo;
import java.sql.*;
import java.util.*;
import org.flywaydb.core.Flyway;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
@SpringBootApplication
public class RecoveryLab {
static final String URL = "jdbc:h2:file:./state/orders";
static final String USER = "sa";
public static void main(String[] args) throws Exception {
int exit = 0;
String action = args[0];
try (ConfigurableApplicationContext context = SpringApplication.run(RecoveryLab.class, args)) {
Flyway flyway = Flyway.configure().dataSource(URL, USER, "")
.locations("filesystem:migrations").load();
try (Connection c = DriverManager.getConnection(URL, USER, "")) {
System.out.printf("BEGIN action=%s pid=%d h2=%s%n", action,
ProcessHandle.current().pid(),
c.getMetaData().getDatabaseProductVersion());
}
try {
switch (action) {
case "migrate" -> {
var result = flyway.migrate();
System.out.printf("MIGRATE flyway=%s applied=%d%n", result.flywayVersion, result.migrationsExecuted);
}
case "repair" -> System.out.println("REPAIR removed=" + flyway.repair().migrationsRemoved.size());
case "preflight" -> preflight();
case "inspect" -> { }
default -> throw new IllegalArgumentException(action);
}
System.out.println("RESULT action=" + action + " status=OK");
} catch (Exception e) {
String state = "none";
for (Throwable cause = e; cause != null; cause = cause.getCause()) {
if (cause instanceof SQLException sql) state = sql.getSQLState();
}
System.out.printf("RESULT action=%s status=FAILED error=%s sqlState=%s%n",
action, e.getClass().getSimpleName(), state);
exit = 2;
}
inspect();
}
System.exit(exit);
}
static long count(Connection c, String sql) throws SQLException {
try (Statement s = c.createStatement(); ResultSet rs = s.executeQuery(sql)) {
rs.next(); return rs.getLong(1);
}
}
static void inspect() throws SQLException {
try (Connection c = DriverManager.getConnection(URL, USER, "")) {
boolean audit = count(c, "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='PUBLIC' AND TABLE_NAME='ORDER_AUDIT'") == 1;
long auditRows = audit ? count(c, "SELECT COUNT(*) FROM order_audit") : 0;
long orders = count(c, "SELECT COUNT(*) FROM orders WHERE id=1 AND status='NEW'");
long failed = count(c, "SELECT COUNT(*) FROM \"flyway_schema_history\" WHERE \"version\"='2' AND \"success\"=FALSE");
long success = count(c, "SELECT COUNT(*) FROM \"flyway_schema_history\" WHERE \"version\"='2' AND \"success\"=TRUE");
System.out.printf("STATE orderPreserved=%d auditTable=%s auditRows=%d failedV2=%d successV2=%d%n",
orders, audit, auditRows, failed, success);
}
}
static void preflight() throws SQLException {
try (Connection c = DriverManager.getConnection(URL, USER, "")) {
DatabaseMetaData md = c.getMetaData();
List<String> columns = new ArrayList<>();
try (ResultSet rs = md.getColumns(null, "PUBLIC", "ORDER_AUDIT", null)) {
while (rs.next()) columns.add(rs.getString("COLUMN_NAME") + ":" + rs.getInt("DATA_TYPE")
+ ":" + rs.getInt("COLUMN_SIZE") + ":" + rs.getInt("NULLABLE"));
}
if (!columns.equals(List.of("ID:4:32:0", "ORDER_ID:4:32:0", "EVENT:12:40:0")))
throw new IllegalStateException("unexpected columns " + columns);
List<String> primaryKey = new ArrayList<>();
try (ResultSet rs = md.getPrimaryKeys(null, "PUBLIC", "ORDER_AUDIT")) {
while (rs.next()) primaryKey.add(rs.getString("COLUMN_NAME"));
}
if (!primaryKey.equals(List.of("ID"))) throw new IllegalStateException("unexpected primary key");
if (count(c, "SELECT COUNT(*) FROM order_audit") != 1 ||
count(c, "SELECT COUNT(*) FROM order_audit WHERE id=10 AND order_id=1 AND event='before-upgrade'") != 1)
throw new IllegalStateException("unexpected audit rows");
System.out.println("PREFLIGHT columns=ID/ORDER_ID/EVENT types=INT/INT/VARCHAR(40) nullable=false primaryKey=ID auditRow=10/1/before-upgrade");
}
}
}
repair 成功了,第二次 migrate 仍然失败
先只放入 V1,运行一次 migrate。再加入坏的 V2,启动另一个进程执行迁移。第一条失败日志如下,42S02 对应这次引用不存在表的错误:
java -jar target/flyway-repair-lab-1.0.jar migrate
BEGIN action=migrate pid=1207903 h2=2.5.252 (2026-09-23)
RESULT action=migrate status=FAILED error=FlywayMigrateException sqlState=42S02
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=1 successV2=0
failedV2=1 是历史表中的失败记录,auditRows=1 是业务审计表中的真实行数。原来的订单也还在。二者描述的是不同状态,不能只看 Flyway 历史表。
接着保持脚本不变,单独运行 repair:
java -jar target/flyway-repair-lab-1.0.jar repair
BEGIN action=repair pid=1208020 h2=2.5.252 (2026-09-23)
REPAIR removed=1
RESULT action=repair status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=0 successV2=0
失败历史由 1 变为 0,审计表和那一行数据都没有消失。再次执行相同的 migrate:
BEGIN action=migrate pid=1208079 h2=2.5.252 (2026-09-23)
RESULT action=migrate status=FAILED error=FlywayMigrateException sqlState=42S01
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=1 successV2=0
这回错误变成 42S01:脚本从头执行,第一条 CREATE TABLE 就遇到了残留表。程序还没走到原先那条坏 SQL,历史表又多出一条失败记录。
repair 文档明确说明,它修复的是 schema history table。它会移除失败迁移记录,遗留的用户对象仍需另行处理;它也会对齐已应用迁移的校验和、描述和类型,并把找不到的迁移标为 deleted。这里没有把它当成撤销 SQL 的命令。
保留那条审计数据,把未完成脚本补到可重跑
本例选择向前完成迁移,不删审计表,也不抹掉已提交的审计行。前提是先确认残留状态:三列的类型、长度、非空约束、主键,以及唯一一行数据都与这次失败留下的内容一致。
preflight 直接查询 JDBC 元数据和数据内容。本次实际检查结果:
BEGIN action=preflight pid=1208161 h2=2.5.252 (2026-09-23)
PREFLIGHT columns=ID/ORDER_ID/EVENT types=INT/INT/VARCHAR(40) nullable=false primaryKey=ID auditRow=10/1/before-upgrade
RESULT action=preflight status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=1 successV2=0
这个核对只覆盖代码里列明的结构和样本数据,不声称验证了触发器、外键或整库差异。IF NOT EXISTS 只负责按名字跳过已存在对象,不会替程序比较列定义。
所有 Java 进程退出后,先复制 H2 文件留存故障现场,再把当前从未成功执行过的 V2 换成下面的内容。V1 始终未改:
cp state/orders.mv.db evidence/before-recovery.mv.db
sha256sum state/orders.mv.db evidence/before-recovery.mv.db
CREATE TABLE IF NOT EXISTS order_audit (id INT PRIMARY KEY, order_id INT NOT NULL, event VARCHAR(40) NOT NULL);
INSERT INTO order_audit (id, order_id, event)
SELECT 10, 1, 'before-upgrade'
WHERE NOT EXISTS (SELECT 1 FROM order_audit WHERE id = 10);
CREATE INDEX IF NOT EXISTS idx_order_audit_order ON order_audit(order_id);
建表和建索引允许对象已存在,插入语句则只在主键 10 不存在时执行。原来的 before-upgrade 行不会被覆盖或重复插入。这里处理的是已核对过的失败 V2,不包含“其他环境已经成功应用 V2 后再改历史脚本”的情形。
随后仍用原来的迁移目录执行 repair,再执行 migrate。repair 的作用是移除第二次失败留下的历史记录,修正后的 SQL 才负责完成数据库变更:
BEGIN action=repair pid=1208229 h2=2.5.252 (2026-09-23)
REPAIR removed=1
RESULT action=repair status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=0 successV2=0
BEGIN action=migrate pid=1208277 h2=2.5.252 (2026-09-23)
MIGRATE flyway=13.12.2 applied=1
RESULT action=migrate status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=0 successV2=1
V2 现在只有一条成功历史,失败历史为 0,审计行仍为 1。再开一个独立 Boot 进程模拟重启,迁移数降为 0:
BEGIN action=migrate pid=1208333 h2=2.5.252 (2026-09-23)
MIGRATE flyway=13.12.2 applied=0
RESULT action=migrate status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=0 successV2=1
重启后重新检查结构和具体审计行,仍与修复前一致:
BEGIN action=preflight pid=1209996 h2=2.5.252 (2026-09-23)
PREFLIGHT columns=ID/ORDER_ID/EVENT types=INT/INT/VARCHAR(40) nullable=false primaryKey=ID auditRow=10/1/before-upgrade
RESULT action=preflight status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=0 successV2=1
同一份修正脚本,还要能安装到空库
恢复已有库只是半条路径。发布包还会被用于新环境,所以又准备了一个独立的空 H2 文件库,放入相同的 V1 和修正后的 V2,一次执行完整迁移链:
BEGIN action=migrate pid=1208434 h2=2.5.252 (2026-09-23)
MIGRATE flyway=13.12.2 applied=2
RESULT action=migrate status=OK
STATE orderPreserved=1 auditTable=true auditRows=1 failedV2=0 successV2=1
空库执行两条迁移,已有库的恢复只执行一条 V2;两条路径最终都保留原订单和一条审计数据。整个恢复流程保留了已有业务对象,也没有绕过校验或手工修改历史表。
完整复现入口
工程目录包含 src、fixtures,以及空的 migrations、state、evidence、logs 目录。三个 SQL 文件的内容已在前文给出。首次构建下载依赖,缓存准备好后还实际做过一次离线构建:
mvn -B -DskipTests package 2>&1 | tee logs/build-attempt-01.log
mvn -o -B -DskipTests package 2>&1 | tee logs/build.log
bash run.sh 2>&1 | tee logs/workflow.log
run.sh 遇到已有实验数据库会直接停止,避免把前一轮现场清掉。两次预期的迁移失败都保留独立日志,退出码必须为 2;其余操作必须正常退出。
run.sh:从新库到失败恢复,再到全新安装
#!/usr/bin/env bash
set -euo pipefail
cd "$(dirname "$0")"
if [[ -e state/orders.mv.db ]]; then
echo "Refusing to reuse an existing database; preserve the previous run."
exit 3
fi
run_expected() {
local label="$1" expected="$2" action="$3"
printf 'COMMAND java -jar target/flyway-repair-lab-1.0.jar %s\n' "$action" | tee -a logs/commands.log
set +e
java -jar target/flyway-repair-lab-1.0.jar "$action" 2>&1 | tee "logs/$label.log"
local actual=${PIPESTATUS[0]}
set -e
printf 'EXIT label=%s actual=%s expected=%s\n' "$label" "$actual" "$expected" | tee -a logs/commands.log
[[ "$actual" == "$expected" ]]
}
cp fixtures/V1__orders.sql migrations/V1__orders.sql
run_expected init 0 migrate
cp fixtures/V2-broken.sql migrations/V2__order_audit.sql
run_expected first-failure 2 migrate
run_expected premature-repair 0 repair
run_expected retry-still-fails 2 migrate
run_expected preflight 0 preflight
cp state/orders.mv.db evidence/before-recovery.mv.db
sha256sum state/orders.mv.db evidence/before-recovery.mv.db | tee logs/backup.log
cp fixtures/V2-recovered.sql migrations/V2__order_audit.sql
run_expected repair-after-correction 0 repair
run_expected migrate-corrected 0 migrate
run_expected restart 0 migrate
run_expected final-inspect 0 inspect
mkdir -p fresh/state fresh/migrations
cp fixtures/V1__orders.sql fresh/migrations/V1__orders.sql
cp fixtures/V2-recovered.sql fresh/migrations/V2__order_audit.sql
(
cd fresh
java -jar ../target/flyway-repair-lab-1.0.jar migrate
) 2>&1 | tee logs/fresh-install.log
(
cd fresh
java -jar ../target/flyway-repair-lab-1.0.jar inspect
) 2>&1 | tee logs/fresh-inspect.log
java -jar target/flyway-repair-lab-1.0.jar preflight 2>&1 | tee logs/final-preflight.log
本轮还用下面的校验脚本核对阶段输出、12 个不同进程号、故障现场副本哈希和最终脚本内容。所有进程都已退出:
verify.py:核对阶段结果与独立进程
import hashlib
import json
import pathlib
import re
root = pathlib.Path(__file__).parent
stages = ["init", "first-failure", "premature-repair", "retry-still-fails", "preflight", "repair-after-correction", "migrate-corrected", "restart", "final-inspect", "fresh-install", "fresh-inspect", "final-preflight"]
logs = {stage: (root / f"logs/{stage}.log").read_text() for stage in stages}
expected = {
"init": ["MIGRATE flyway=13.12.2 applied=1", "auditTable=false auditRows=0 failedV2=0 successV2=0"],
"first-failure": ["status=FAILED error=FlywayMigrateException sqlState=42S02", "auditTable=true auditRows=1 failedV2=1 successV2=0"],
"premature-repair": ["REPAIR removed=1", "auditTable=true auditRows=1 failedV2=0 successV2=0"],
"retry-still-fails": ["status=FAILED error=FlywayMigrateException sqlState=42S01", "failedV2=1 successV2=0"],
"preflight": ["PREFLIGHT columns=ID/ORDER_ID/EVENT", "RESULT action=preflight status=OK"],
"repair-after-correction": ["REPAIR removed=1", "failedV2=0 successV2=0"],
"migrate-corrected": ["MIGRATE flyway=13.12.2 applied=1", "failedV2=0 successV2=1"],
"restart": ["MIGRATE flyway=13.12.2 applied=0", "failedV2=0 successV2=1"],
"fresh-install": ["MIGRATE flyway=13.12.2 applied=2", "auditTable=true auditRows=1 failedV2=0 successV2=1"],
"final-preflight": ["PREFLIGHT columns=ID/ORDER_ID/EVENT", "RESULT action=preflight status=OK"]
}
for stage in stages:
assert "STATE orderPreserved=1" in logs[stage], stage
for text in expected.get(stage, []):
assert text in logs[stage], (stage, text)
pids = [int(re.search(r"pid=(\d+)", logs[stage])[1]) for stage in stages]
assert len(set(pids)) == 12, pids
assert all(not pathlib.Path(f"/proc/{pid}").exists() for pid in pids)
backup = root / "evidence/before-recovery.mv.db"
backup_hash = hashlib.sha256(backup.read_bytes()).hexdigest()
assert (root / "logs/backup.log").read_text().count(backup_hash) == 2
assert (root / "migrations/V1__orders.sql").read_bytes() == (root / "fixtures/V1__orders.sql").read_bytes()
assert (root / "migrations/V2__order_audit.sql").read_bytes() == (root / "fixtures/V2-recovered.sql").read_bytes()
paths = [root / "pom.xml", root / "run.sh", root / "verify.py", *sorted((root / "src").rglob("*")), *sorted((root / "fixtures").rglob("*")), root / "target/flyway-repair-lab-1.0.jar"]
evidence = {"flyway": "13.12.2", "h2": "2.5.252", "boot": "3.5.16", "stages": stages, "independentJvmPids": pids, "allProcessesExited": True, "backupSha256": backup_hash, "sha256": {str(path.relative_to(root)): hashlib.sha256(path.read_bytes()).hexdigest() for path in paths if path.is_file()}}
(root / "evidence.json").write_text(json.dumps(evidence, ensure_ascii=False, indent=2) + "\n")
print("VERIFIED stages=12 independentJvms=12 expectedFailures=2 orderPreserved=true auditRowPreserved=true freshInstall=true")python3 verify.py 2>&1 | tee logs/verification.log
VERIFIED stages=12 independentJvms=12 expectedFailures=2 orderPreserved=true auditRowPreserved=true freshInstall=true
项目与资料
Flyway 仓库为 flyway/flyway,本例使用的开源部分采用 Apache License 2.0。13.12.2 发布于 2026 年 11 月 12 日 22:52:16(北京时间);本文记录该版本上 repair 的既有恢复边界。repair 范围与事务边界分别对应官方的 Repair 和 Migration transaction handling。全部数据均为本地合成样本。
一起交流
分享你的思考,让讨论更进一步。