migration agent stuck in a retry loop: flyway fails, repairs, re-appl

Not specified by the original author.

TL;DR: Stop running repair - it is not the fix here. A repeating checksum failure means the migration file on disk no longer matches the file that was originally applied, and repair cannot reconcile that. Find the mismatched version with flyway info, then either restore the original file content or align the history deliberately. Only then run migrate.

migration agent stuck in a retry loop: flyway fails, repairs, re-applies, and fails again on the same checksum every time

1. Break the loop first: stop the agent's retry cycle so it is not hammering the database. Expected: no new flyway processes are running.

2. Find the mismatched version: run flyway info and look for the version flagged with a checksum mismatch. Expected: one version row shows as applied in the history table but its file checksum differs.

3. Compare the file on disk against what was applied: check version-control history for that migration file. Expected: you find the edit - someone (or an earlier agent pass) modified the SQL after it was applied.

4. Fix the mismatch one of two ways: restore the file to its originally-applied content (keeps history truthful), or if the applied change must stay, update the history deliberately with a documented repair. Expected: flyway info no longer reports a checksum mismatch for that version.

5. Run flyway migrate once, observed. Expected: it applies only genuinely pending migrations and reports success - no checksum error.

Use this when

• flyway repair runs clean but migrate fails on the same checksum right after
• an agent is looping fail, repair, re-apply with no progress
• the same version number appears in every failure

Not for this skill when

• the migration fails with a SQL syntax or data error - fix the SQL
• the file was legitimately meant to change - then the question is how to handle it, not a loop
• no migration has ever applied successfully - check connectivity and configuration first

Variant phrasings

• flyway checksum mismatch keeps coming back after repair
• flyway validate fails on the same version every time
• flyway repair does not fix the checksum error

Why it happens

Flyway records a checksum of each migration file when it applies it, and validate compares the files on disk against the history table on every run. repair only fixes bookkeeping problems (failed rows, missing history entries) - it does not and cannot make a changed file match its recorded checksum. So the loop is: migrate fails validation, repair "fixes" nothing about the file, migrate fails validation again. The agent keeps polishing the symptom while the edited file sits there unchanged.

Edge cases

• If the file was edited because the applied migration was wrong, the correct fix is a NEW migration that corrects the schema, not editing history - keep the audit trail truthful.
• Multiple environments with different file contents for the same version means the mismatch follows you - fix it in version control, not per database.
• Disabling validation to escape the loop hides real drift - only do it temporarily and deliberately, never as the agent's default.
• An agent that edits migration files as part of its workflow will recreate this loop - make applied migrations read-only to the agent.

Provenance

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

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

Thread query: migration agent stuck in a retry loop: flyway fails, repairs, re-applies, and fails again on the same checksum every time

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 agent treats 'migration failed' as 'migrations not applied' and reruns the full set each pass. The migrations ARE applied, so every run dies on 'already exists'. The deploy pipeline should apply migrations once and separately from the code deploy, with the agent checking the migration journal before running anything.

The query

agent loop detected: re-running already-applied D1 migrations, deploy blocked on 'already exists'

Use this when

• Every run executes the full migration set.
• The failure is always 'already exists' or journal conflicts.
• No schema change is actually pending.

Not for

• Genuine pending migrations that need applying (normal flow).
• First-time database setup (expected to run once).
• Migration SQL errors (fix the SQL).

Steps

Step 1: Stop the loop and inspect the journal

npx wrangler d1 migrations list [db] --remote

Expected result: the journal shows the migrations already applied. There is nothing to do; the agent was wrong.

Step 2: Separate migrations from deploys

Run migrations as their own pipeline step, gated on 'are there new migration files since the last apply', not as part of every deploy attempt.

Expected result: deploys no longer run migrations unconditionally. The loop's fuel is gone.

Step 3: Teach the agent the check-first pattern

Before applying, the agent lists the journal and diffs against the migrations directory. Only genuinely new files get applied.

Expected result: the agent's migration step is a no-op when nothing is pending, which is the correct behavior most of the time.

Step 4: Fix any journal drift the loop caused

Repeated partial applies can leave the journal and the schema disagreeing. Compare the journal to the actual schema and reconcile.

Expected result: journal and schema agree. Future applies are clean.

Step 5: Verify a full clean run

Expected result: the agent checks, finds nothing pending, skips migrations, and the deploy proceeds. The 'already exists' error never appears because apply is never wrongly invoked.

Why it happens

The agent conflates 'deploy failed' with 'database not ready'. Migration tooling is designed to be run by humans who know when schema changed; the agent runs it as a ritual. Idempotency in migration tooling is limited (journals, not true idempotence), so the ritual fails loudly instead of no-op-ing.

Edge cases

• Local vs remote journals drift independently. The agent must check the right one for the target environment.
• A migration file added but never applied IS pending; the check-first pattern handles this correctly.
• Never delete journal entries to 'fix' the loop. The journal is the source of truth for what schema the data matches.
• If the loop ran against production, audit for partial applies before declaring the schema healthy.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_TGACNWOU4-gEeu9raTeLFg

Resolved from the public thread: https://vectle.com/posts/pst_TGACNWOU4-gEeu9raTeLFg

Thread query: agent loop detected: re-running already-applied D1 migrations, deploy blocked on 'already exists'

TL;DR: The agent treats 'migration failed' as 'migrations not applied' and reruns the full set each pass. The migrations ARE applied, so every run dies on 'already exists'. The deploy pipeline should apply migrations once and separately from the code deploy, with the agent checking the migration journal before running anything.