flyway migrate hit the command timeout but the schema change kept running in the

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

Without a statementtimeout, a DDL statement runs until it finishes or someone kills it, holding locks that block deploys the whole time. Assess whether it is safe to cancel (pgcancelbackend for a clean abort), set statementtimeout and lock_timeout so future statements fail fast, and redo the change with an online tool (concurrent index builds, gh-ost, pt-online-schema-change) instead of a raw ALTER on a hot table.

The query

agent didnt set a statement_timeout and the ALTER TABLE ran for 6 hours blocking deploys

Use this when

• ALTER TABLE ran for hours blocking deploys
• No statement_timeout set on a migration
• Need to kill a runaway DDL safely
• An agent's migration hung on a big ALTER

Not for

• Slow DML queries (tune the query or the indexes)
• A DDL that is slow but not blocking (schedule it, do not kill it)
• Lock waits rather than slow execution (that is lock_timeout territory)

Steps

1. Assess before killing

Check what the ALTER is doing and what it blocks: pgstatactivity for waiters, and whether the ALTER itself is progressing (e.g. an index build's phase). Killing a nearly-done CREATE INDEX CONCURRENTLY wastes hours; killing a stuck lock-wait is pure win.

Expected output: a decision based on progress and blockers, not on elapsed time alone.

2. Cancel it cleanly if it should stop

SELECT pg_cancel_backend([pid]);

pgcancelbackend aborts the statement; pgterminatebackend kills the session (stronger, for when cancel does not work). Expected output: the ALTER aborts and its locks release. Verify the blockers clear.

3. Set timeouts so this fails fast next time

SET statement_timeout = '60s';
SET lock_timeout = '30s';

Set them in the migration's connection setup (alembic env.py, the CI job, the agent's wrapper), not just in your psql session. Expected output: future runaway statements die in a minute instead of running for six hours.

4. Redo the change the online way

For the actual schema change, use the non-blocking variant: CREATE INDEX CONCURRENTLY instead of CREATE INDEX, or gh-ost / pt-online-schema-change for table rebuilds on mysql. Schedule it in a quiet window with monitoring.

Expected output: the schema change completes without holding the locks that blocked deploys.

5. Make timeouts mandatory in the agent's procedure

Every migration connection the agent opens sets statementtimeout and locktimeout first. A migration that needs longer gets an explicit, reviewed override, not a silent absence of limits.

Expected output: unbounded DDL becomes impossible by default.

Tool compatibility

postgres 12-17 (statementtimeout, locktimeout, CONCURRENTLY). mysql: use pt-online-schema-change/gh-ost; the timeout principle maps to lockwaittimeout and maxexecutiontime.

Variant phrasings

ALTER TABLE ran 6 hours blocking deploys

Steps 1 and 2: assess progress, cancel cleanly if warranted.

no statement_timeout migration hung

Step 3: set statementtimeout and locktimeout in the migration connection setup.

kill long running DDL postgres safely

Step 2: pgcancelbackend first (aborts the statement), pgterminatebackend only if needed.

Why it happens

Postgres defaults to no statement timeout: the server assumes you mean what you ask for, however long it takes. DDL on a hot table takes locks that queue every other statement behind them, so one unbounded ALTER becomes a six-hour deploy freeze. Timeouts convert "hangs forever" into "fails fast", which is the only sane default for automated migrations.

Edge cases

• Cancelling CREATE INDEX (non-concurrent) rolls back cleanly; cancelling CREATE INDEX CONCURRENTLY can leave an INVALID index behind: check pg_index for indisvalid and drop invalids.
• locktimeout and statementtimeout compose: a statement can pass lock acquisition and still die on statement_timeout mid-execution. Set both deliberately.
• Some migrations legitimately need hours (big backfills): give them explicit higher timeouts and a quiet window, rather than leaving the default infinite for everything.

Provenance

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

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

Thread query: agent didnt set a statement_timeout and the ALTER TABLE ran for 6 hours blocking deploys

TL;DR: Without a statementtimeout, a DDL statement runs until it finishes or someone kills it, holding locks that block deploys the whole time. Assess whether it is safe to cancel (pgcancelbackend for a clean abort), set statementtimeout and lock_timeout so future statements fail fast, and redo the change with an online tool (concurrent index builds, gh-ost, pt-online-schema-change) instead of a raw ALTER on a hot table.

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: