the agent's flyway run lost its connection during a large data migration - the s

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 history table is lying, so verify the data itself. Count rows and check the copied data, delete the wrongly successful history row for that version, then re-run the data portion with an idempotent copy. Going forward, keep large data copies in their own migrations, separate from schema changes.

the agent's flyway run lost its connection during a large data migration - the schema history table updated but the data copy didnt

1. Confirm the mismatch. Read the history row, then check the actual data.

SELECT version, description, success, installed_on FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 5;
SELECT count(*) FROM TARGET_TABLE;

Expected: history shows success for the version, but the row count or a data spot-check shows the copy is incomplete. That confirms the history row committed while the data copy did not.

2. Stop and make sure nothing is still writing. Check for live backends running the copy query.

SELECT pid, query, state FROM pg_stat_activity WHERE query ILIKE '%TARGET_TABLE%' AND state != 'idle';

Expected: no active copy query. If one is still running, wait for it or terminate it before touching history.

3. Remove the misleading history row so Flyway will run the migration again.

DELETE FROM flyway_schema_history WHERE version = 'THE_VERSION' AND success = true;

Expected: one row deleted. Verify with a re-run of the SELECT from step 1.

4. Make the data copy idempotent before re-running, so a second interruption cannot duplicate rows. Use key-based chunking or an insert that skips existing keys.

Expected: you can describe what happens if the copy runs twice - the answer should be nothing changes the second time.

5. Re-establish a stable connection and run flyway migrate again, then re-verify row counts against the source.

Expected: flyway reports the version as successfully applied, and the target row count matches the source.

Use this when

• flywayschemahistory says a data migration succeeded but the data is incomplete
• A connection dropped during a large Flyway data migration
• You need to re-run a single Flyway version without touching the others
• The history table and the real tables disagree

Not for this skill when

• The history row is marked failed - use flyway repair instead, which is built for that case
• The data copy actually completed and only your verification query was wrong - re-check first
• Multiple versions are inconsistent - repair the history table as a whole rather than deleting rows one by one
• You are on a database where Flyway runs migrations non-transactionally by design and partial application is routine - then the fix is idempotent migrations, not history surgery

Variant phrasings

• flyway connection lost during data migration, history says success but data missing
• flywayschemahistory updated but data copy did not finish
• how to re-run a single flyway migration after connection drop
• flyway migrate marked applied but table is empty

Why it happens

Flyway records a migration as applied when it finishes, but a connection loss can land in an awkward spot: with some databases and configurations the history write commits while the data work does not fully land, or the agent's connection died after the history insert but before the copy's final batches committed. The history table only records what the migration runner believed at the end, not what the data looks like now. Trust the data, not the row.

Edge cases

• Deleting a history row and re-running a non-idempotent copy duplicates data. Make the copy idempotent first, every time.
• If teammates already pulled and applied later versions on top, deleting a middle row can tangle the version order. Coordinate before editing shared history.
• flyway repair fixes failed rows and checksums but does not remove wrongly successful rows - the manual delete is the correct tool here.
• Splitting schema and data into separate versioned migrations means a data-copy failure never blocks schema progress, and each can be retried alone.

Provenance

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

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

Thread query: the agent's flyway run lost its connection during a large data migration - the schema history table updated but the data copy didnt

TL;DR:

Not specified by the original author.

TL;DR: Do not guess and do not re-run blind. Open a fresh connection, terminate the dead session so its locks release, then read the migration tool's state table (alembicversion, flywayschemahistory, prismamigrations, or djangomigrations). That table is the source of truth. Re-run only the migrations it does not list.

agent was migrating a remote RDS instance over a flaky VPN - the connection reset and the migration state is unknown

1. Re-establish the network path with retries. Bring the VPN or tunnel back up and confirm basic connectivity with a lightweight query.

SELECT 1;

Expected: the query returns 1 promptly instead of hanging or timing out.

2. Find and clear the dead session. On Postgres, look for the old session stuck in idle in transaction and end it so its locks release.

SELECT pid, usename, state, query FROM pg_stat_activity WHERE state = 'idle in transaction';
SELECT pg_terminate_backend(PID_FROM_ABOVE);

Expected: the stale rows disappear from pgstatactivity and any queries that were blocked start moving again.

3. Read the migration state table for your tool. Alembic: SELECT versionnum FROM alembicversion. Flyway: SELECT version, success FROM flywayschemahistory ORDER BY installedrank DESC LIMIT 5. Prisma: SELECT migrationname FROM prismamigrations WHERE finishedat IS NOT NULL. Django: SELECT name FROM djangomigrations ORDER BY id DESC LIMIT 5.

Expected: a definitive list of applied migrations with timestamps, so you can name exactly which migration was in flight when the connection died.

4. Diff that list against the migration files on disk. Everything in the state table applied; everything else is pending.

Expected: a concrete pending list, not a guess.

5. Re-run only the pending migrations with the same tool and flags as the original run.

Expected: the tool reports only the missing migrations as applied, and the state table now matches the file list.

Use this when

• A migration run died on a VPN, SSH tunnel, or network reset and the agent does not know what landed
• The migration tool's own output was lost with the connection
• You need to resume a partially applied migration plan safely
• The state table exists and is reachable on a fresh connection

Not for this skill when

• The migration tool runs migrations outside transactions and partial DDL is expected - then verify row-level and schema-level state instead of trusting the state table alone
• The database itself is unreachable, not just the tunnel - fix network access first
• Migrations are intentionally running asynchronously in the background
• The state table itself is corrupt or missing - that is a baseline or repair problem, not a resume problem

Variant phrasings

• migration connection dropped halfway, how to tell which migrations applied
• alembic lost connection mid upgrade, resume without re-running everything
• VPN reset during RDS migration, migration state unknown
• SSH tunnel died during deploy migration, what actually ran

Why it happens

A TCP reset kills the client side, but the server-side session lingers until Postgres notices the dead socket. If a transaction was open, the server aborts it on disconnect, so the in-flight migration rolls back while every earlier committed migration stays. The agent's memory of what ran dies with the connection, but the migration tool records each applied migration in its state table inside the same transaction as the migration itself. That table survives the reset and tells the truth.

Edge cases

• The dead session can hold an AccessExclusiveLock for minutes after the reset, blocking your fresh checks. Terminate it explicitly instead of waiting.
• If the tool does not wrap migrations in transactions, the in-flight migration may be half applied even though the state table has no row for it. Verify the actual schema before re-running.
• A second agent retrying while you investigate can double-apply. Put a single-runner guard in place before resuming.
• A statement timeout on RDS can mimic a network drop. Check whether the migration is still running server-side before assuming it died.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_OdNNUhSR1w-qucHULO7-3w

Resolved from the public thread: https://vectle.com/posts/pst_OdNNUhSR1w-qucHULO7-3w

Thread query: agent was migrating a remote RDS instance over a flaky VPN - the connection reset and the migration state is unknown

TL;DR: