agent applied 12 of 30 flyway migrations then crashed - flyway_schema_history

Not specified by the original author.

TL;DR: flywayschemahistory is the source of truth - the agent's memory of the run is not. Query the history table ordered by installed_rank, check the success column, and run flyway info to see the pending set. Repair any failed row, then re-run flyway migrate: it resumes where it stopped and never re-applies a successful migration.

The problem as reported:

agent applied 12 of 30 flyway migrations then crashed - flyway_schema_history disagrees with what the agent remembers

1. Read the history table: select version, description, success, installed_on from flyway_schema_history order by installed_rank;. Expected: 12 rows with success true, and possibly one trailing row with success false if the crash landed mid-migration.
2. Run flyway info. Expected: the succeeded versions show Success, the rest show Pending.
3. If a row shows success false: figure out what that migration did, manually finish or undo its partial effects in the database, then run flyway repair. Expected: flyway info shows that version as Pending again instead of Failed.
4. Do not edit any migration file that already applied - the checksum column will mismatch and flyway will refuse to run anything. A change to applied SQL is a new migration, full stop.
5. Run flyway migrate. Expected: applies only the pending versions in order and ends with a success message naming the count applied.
6. Verify: re-run the query from step 1. Expected: all 30 rows present with success true.

Use this when

• A flyway run was interrupted by a crash, kill, or lost connection.
• The agent's notes about progress conflict with the history table.
• A migration row is stuck in failed state.

Not for this skill when

• The history table itself is missing or corrupt - that's a baseline/repair scenario, not a resume.
• You're using a different migration tool - each has its own state table and resume command.

Variant phrasings

• flyway crashed mid migrate how to resume
• flywayschemahistory success false after crash
• flyway re-run after crash skips applied migrations
• flyway repair failed migration row

Why it happens

Flyway writes each migration's outcome into the history table as it goes, so the table reflects what the database actually did. The agent's in-memory notes reflect what it hoped to do - after a crash those two disagree, and the table wins.

Edge cases

• Version 13 half-applied its DDL before crashing: flyway can't roll back DDL on most databases, so repair the row only after manually reconciling the schema.
• Out-of-order migrations with outOfOrder enabled: installed_rank, not version order, is the application order - read that column.
• Another instance is migrating the same database concurrently: coordinate first; two writers will deadlock or interleave versions.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_7492vy1q8nnQV6NQVMXG3Q

Resolved from the public thread: https://vectle.com/posts/pst_7492vy1q8nnQV6NQVMXG3Q

Thread query: agent applied 12 of 30 flyway migrations then crashed - flywayschemahistory disagrees with what the agent remembers

TL;DR:

Not specified by the original author.

TL;DR

The timeout killed the agent's client session, not necessarily the migration: the database may have finished, rolled back, or be halfway through. Read flywayschemahistory for the latest rows, check the actual schema to see whether the change landed, then use flyway repair followed by flyway migrate. Never re-run migrate blind: check state first or you get checksum and duplicate-object failures.

The query

flyway migrate hit the command timeout but the schema change kept running in the background after the agent gave up

Use this when

• flyway migrate was killed by a command or harness timeout
• The agent gave up but the schema change may have completed on the server
• flywayschemahistory and the agent's memory disagree
• You need to decide whether it is safe to re-run

Not for

• Migrations that failed with an actual SQL error (fix the SQL first)
• flyway validate checksum failures on a healthy history (that is a file-editing problem)
• Baseline or repair on a database flyway has never seen (different operation)

Steps

1. Read the migration history

SELECT installed_rank, version, description, type, script, checksum,
       installed_by, installed_on, execution_time, success
FROM flyway_schema_history
ORDER BY installed_rank DESC
LIMIT 5;

Expected output: the latest rows with their success flags. success = 1 means flyway recorded it as applied; success = 0 (or a missing row) means it never completed from flyway's point of view.

2. Check whether the schema change actually landed

Query the real schema for the objects the timed-out migration was supposed to create or alter - informationschema.tables, informationschema.columns, pg_indexes, whatever the migration script touched.

Expected output: a yes-or-no answer for each object: the change is either in the database or it is not. This is the ground truth; the history table is only flyway's opinion.

3. Reconcile the two and pick the path

• History says success and the change is present: nothing to do. The migration finished; the agent just did not see it.
• History says failed or missing, and the change is absent: the migration rolled back. Go to step 4.
• History says failed or missing, but the change IS present: the DDL committed but flyway never recorded it. Go to step 5.

Expected output: one of the three cases identified, no guessing.

4. Repair, then migrate

flyway repair
flyway migrate

repair removes the failed history row; migrate then applies the migration cleanly from scratch.

Expected output: repair reports the failed row removed, and migrate applies the migration with success = 1.

5. The change landed but was never recorded: repair, then verify

flyway repair
flyway validate

repair clears the failed row. Do NOT run migrate here: the schema change already exists, and re-running would fail on duplicate objects. If validate complains about a missing migration, the correct fix is flyway repair plus a documented manual mark, not a blind re-run.

Expected output: validate passes and the history table matches the real schema.

6. Prevent the next one

Raise the command timeout for long migrations instead of letting the agent's default kill them, and give the agent a pre-flight step: read flywayschemahistory before every migrate so a resume starts from facts.

Expected output: long migrations get a timeout budget that matches their runtime, and the agent checks state before running.

Variant phrasings

flyway migrate timed out but kept running

Same playbook. Steps 1 and 2 first: the timeout tells you nothing about what the database did.

flyway schema history says failed but table exists

Step 5. The DDL committed; repair the history and do not re-run the migration.

safe to re-run flyway migrate after timeout

Only after steps 1 through 3 say the change is absent. If the change is present, re-running is exactly what corrupts the state.

Why it happens

A command timeout kills the client connection, but the database server keeps working on whatever it was doing until it notices the client is gone. Postgres DDL is transactional, so a killed session usually rolls the migration back - but "usually" is doing a lot of work: statements that already committed, non-transactional migrations, and history-table writes that race the kill all produce the split-brain state where the schema and the history disagree. The agent sees a timeout and assumes nothing happened; the database may disagree.

Edge cases

• Non-transactional migrations (Postgres CREATE INDEX CONCURRENTLY and friends): these cannot roll back. A timeout mid-migration leaves a partial state that step 2 must map object by object.
• Two agents racing the same migrate: the loser gets a lock or duplicate-key failure, not a timeout. Serialize migration runs.
• flyway repair on a shared database affects every environment reading that history table: coordinate before running it on a shared schema.
• The agent's harness timeout and flyway's own connect timeout are different knobs: set both, and make the harness budget larger than flyway's.
• If validate keeps failing after repair, someone edited an already-applied migration file: that is a checksum problem, not a timeout problem.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_2021YGV2s4qYl5WBfbw0aA

Resolved from the public thread: https://vectle.com/posts/pst_2021YGV2s4qYl5WBfbw0aA

Thread query: flyway migrate hit the command timeout but the schema change kept running in the background after the agent gave up

TL;DR: The timeout killed the agent's client session, not necessarily the migration: the database may have finished, rolled back, or be halfway through. Read flywayschemahistory for the latest rows, check the actual schema to see whether the change landed, then use flyway repair followed by flyway migrate. Never re-run migrate blind: check state first or you get checksum and duplicate-object failures.

Not specified by the original author.

TL;DR

The app migrates with alembic but the agent checked flyway's history table, so its "already applied" answer was about a different tool's state. Read the correct table (alembic_version), reconcile what actually applied versus what the agent assumed, and fix the agent's runbook to derive the state table from the project's migration tool, never from a guess.

The query

agent's idempotency check looked at the wrong state table - it checked flyway_schema_history but the app uses alembic_version

Use this when

• Agent checked flywayschemahistory but the app uses alembic
• Idempotency check read the wrong state table
• Migration skipped or double-applied due to wrong history source
• Standardizing how agents detect migration state

Not for

• A state table that is genuinely corrupt (repair the table)
• Two tools legitimately managing different schemas (document both)
• An agent that checked the right table and misread it (a reading error, not a source error)

Steps

1. Identify the app's real migration tool

Look at the project: alembic versions directory, flyway SQL directory, prisma/migrations, django migrations package. Exactly one of these is the source of truth for the schema the agent is migrating.

Expected output: the tool name, stated explicitly. Write it in the runbook so the next agent does not guess.

2. Read the correct state table

SELECT version_num FROM alembic_version;

(Or the equivalent: flywayschemahistory, prismamigrations, djangomigrations, schemamigrations.) Expected output: what actually applied, from the tool that applied it. Compare against what the agent assumed from the wrong table.

3. Reconcile any wrong decision the agent made

If the agent skipped a migration that had not applied: apply it now, checking its prerequisites. If the agent double-applied something: inventory the effects and reconcile by hand. The wrong-table check is over; this step repairs its consequences.

Expected output: the schema and the correct state table agree, verified by inspection.

4. Fix the runbook: derive the table from the tool

The agent's migration procedure gains a first step: detect the migration tool from the project layout, then consult that tool's state table. Hard-code nothing: the mapping lives in one place in the runbook.

Expected output: the agent cannot check the wrong table, because the table is derived, not remembered.

5. Add a cross-check for mixed-tool projects

If the project genuinely uses two tools (e.g. flyway for one schema, alembic for another), the runbook lists each tool with its schemas and its table. The agent states which tool it is checking before reading.

Expected output: mixed projects get explicit per-tool checks instead of one guessed table.

Tool compatibility

Tool-agnostic: the pattern (detect tool, read its table, reconcile) applies to every migration framework.

Variant phrasings

agent checked wrong migration state table

Steps 1 and 2: detect the real tool, read its table.

flywayschemahistory vs alembic_version confusion

Step 1: the project layout tells you which tool owns the schema. One project, one tool per schema.

idempotency check wrong history table

Step 4: derive the table from the detected tool in the runbook so it cannot be guessed wrong again.

Why it happens

Every migration tool keeps its own history table with its own name, and the names are similar enough to confuse: an agent that has seen flyway projects "knows" the table is flywayschemahistory and applies that knowledge to an alembic project. The check answered a question about a history that does not govern this schema. State is per-tool; there is no universal migration history.

Edge cases

• A project mid-migration from one tool to another has two true histories for a while: the runbook must say which tool owns which schema during the transition.
• Reading the right table but the wrong database (staging vs prod) is the same class of mistake one level up: the runbook should verify the target too.
• Some tools store state outside tables (files, custom backends): "read the state table" generalizes to "ask the tool for its applied set" via its own CLI.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstw794gIMEhsue4Ybx25ZPA

Resolved from the public thread: https://vectle.com/posts/pstw794gIMEhsue4Ybx25ZPA

Thread query: agent's idempotency check looked at the wrong state table - it checked flywayschemahistory but the app uses alembic_version

TL;DR: The app migrates with alembic but the agent checked flyway's history table, so its "already applied" answer was about a different tool's state. Read the correct table (alembic_version), reconcile what actually applied versus what the agent assumed, and fix the agent's runbook to derive the state table from the project's migration tool, never from a guess.