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

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.

1. 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.

1. 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.

1. 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.

1. 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