agent ran 8 of 15 migrations before the deploy failed - no down migrations exist

Not specified by the original author.

TL;DR: Roll forward, not back. Query the migration state table to name exactly which 8 applied, fix whatever broke the deploy, then run the remaining 7 in order. If one of the 8 applied migrations is itself broken, write a new forward repair migration - don't invent a downgrade after the fact.

The problem as reported:

agent ran 8 of 15 migrations before the deploy failed - no down migrations exist and production is half-migrated

1. Name the applied 8. Query the tool's state table - e.g. for flyway: select version from flyway_schema_history where success = true order by installed_rank;. Expected: exactly 8 rows; you now know which migrations the database has.
2. Confirm the app still works against the half-migrated schema, and read the deploy failure - most of the time the deploy died for an unrelated reason (bad config, failed health check), not because of migration 8. Expected: a concrete, named blocker.
3. Fix the blocker. Expected: the deploy pipeline is green again apart from the pending migrations.
4. Run the migration command for the remaining 7. Expected: only migrations 9-15 run, in order; the tool reports the database up to date.
5. Verify: the state table shows all 15 applied, and the app boots and passes smoke tests against the full schema. Expected: no gaps, no errors.
6. Going forward: write down migrations for every future change (or adopt the expand/contract pattern) and rehearse the full sequence on a staging clone before prod. Expected: the next failed deploy has a tested path back.

Use this when

• A deploy died partway through a migration sequence.
• No down migrations exist and production is partially migrated.
• The only safe direction is forward.

Not for this skill when

• Down migrations exist and a rollback is genuinely safe - then roll back instead.
• One of the applied migrations corrupted data - stop, assess, and write a repair migration; don't just keep rolling forward blindly.

Variant phrasings

• migration failed halfway no rollback available
• half migrations applied deploy failed
• production half migrated no down migration
• resume failed deploy migrations forward

Why it happens

Migration runners apply in order and stop at the first failure. Without down migrations there is no automated path back to the pre-deploy state, so forward - completing the sequence - is the only safe direction.

Edge cases

• Migration 8 of 8 half-applied: check whether your tool wraps each migration in a transaction (postgres DDL is transactional, MySQL's is not) - non-transactional engines need manual reconciliation.
• The deploy failure was CAUSED by migration 8: write a repair migration first, then continue the sequence.
• Old and new app instances running against the half-migrated schema simultaneously: keep the schema backward compatible until the deploy finishes.

Provenance

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

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

Thread query: agent ran 8 of 15 migrations before the deploy failed - no down migrations exist and production is half-migrated

TL;DR:

Not specified by the original author.

TL;DR

MySQL commits every DDL statement implicitly, so the failed migration left some statements applied and some not, with no transaction to roll back. Inventory which statements took effect by comparing the migration file against the live schema, apply the missing ones by hand, and mark the migration complete. Going forward, write mysql migrations as forward-only and idempotent, and test them on mysql, never sqlite.

The query

agent assumed DDL was transactional but mysql commits implicitly - the half-applied migration cant be rolled back

Use this when

• A mysql migration failed halfway and cannot be rolled back
• Agent assumed DDL was transactional but mysql commits implicitly
• Half-applied migration on mysql with no transaction to roll back
• Writing mysql-safe migrations after a transactional assumption broke

Not for

• Postgres migrations (transactional DDL usually rolls back cleanly there)
• A mysql migration that failed before any statement ran (just fix and re-run)
• DML-only migrations (those are transactional on mysql's innodb)

Steps

1. Inventory what applied and what did not

Read the migration file top to bottom. For each DDL statement, check the live schema: does the table, column, index, or constraint exist?

SHOW CREATE TABLE [table];

Expected output: a statement-by-statement list of applied vs not-applied. This inventory is the recovery plan; do not proceed without it.

2. Apply the missing statements by hand

Run the not-applied statements individually, in order, watching each one succeed. Skip the ones already applied: re-running a CREATE TABLE that exists errors, and mysql has no IF NOT EXISTS for every statement type, so the inventory from step 1 is what keeps you safe.

Expected output: the schema fully matches the migration's intended end state, verified statement by statement.

3. Mark the migration complete in the history table

Update the migration state (flywayschemahistory, alembic_version, or your tool's equivalent) to record the version as applied, since you applied it by hand. Rehearse the exact update on a copy first.

Expected output: the history agrees with the schema, and the next migrate run continues from the right version instead of retrying the half-applied file.

4. Rewrite future mysql migrations as forward-only and idempotent

Each migration must tolerate partial application: guard with existence checks where the dialect allows, keep migrations small (one logical change each), and never rely on rollback. The down migration, if one exists, is for tested reversals, not for crash recovery.

Expected output: the next mid-migration failure is a short inventory-and-finish, not a forensic exercise.

5. Test migrations on mysql, never on sqlite

Add a CI job that runs the migration chain against real mysql (a container is fine). Sqlite's transactional DDL hides exactly the failure mode that just bit you.

Expected output: mysql-specific behavior (implicit commits, lock behavior) is tested where it will actually run.

Tool compatibility

mysql 5.7, 8.0, and mariadb. Postgres and sqlite behave differently (transactional DDL); the recovery pattern here is mysql-specific.

Variant phrasings

mysql DDL implicit commit half-applied migration

Steps 1 through 3: inventory, hand-apply the rest, mark complete.

agent assumed DDL transactional mysql

Steps 4 and 5: forward-only idempotent migrations, tested on real mysql.

mysql migration failed midway cannot rollback

Step 1 first: you cannot roll back, so the only path is forward through the inventory.

Why it happens

MySQL's storage engine interface commits implicitly around DDL: each ALTER, CREATE, or DROP ends whatever transaction was open. This is a documented engine behavior, not a bug, and it means "the migration transaction" never existed on mysql. Tools written against postgres semantics (alembic's transactional upgrades) silently lose their safety net on mysql.

Edge cases

• Some statements are not safely re-runnable even when skipped correctly (e.g. data backfills inside the migration): inventory the DML too, not just the DDL.
• pt-online-schema-change and gh-ost exist precisely because mysql DDL locks and commits: for large tables, prefer them over raw ALTER in migrations.
• Marking the migration complete by hand is a lie if the inventory was wrong: verify the end state against a scratch database migrated cleanly, not just against your notes.

Provenance

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

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

Thread query: agent assumed DDL was transactional but mysql commits implicitly - the half-applied migration cant be rolled back

TL;DR: MySQL commits every DDL statement implicitly, so the failed migration left some statements applied and some not, with no transaction to roll back. Inventory which statements took effect by comparing the migration file against the live schema, apply the missing ones by hand, and mark the migration complete. Going forward, write mysql migrations as forward-only and idempotent, and test them on mysql, never sqlite.

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.