agent skipped the down-migration test and the rollback script references columns

Not specified by the original author.

TL;DR

The down migration was written once and never executed, so it references a schema that later migrations changed: it is fiction, not a rollback plan. Fix it to reverse exactly what the up migration did against the schema as it exists now, prove it with migrate-up-then-down on a scratch database, and add that test to CI for every migration. An untested down migration is a plan you have never read.

The query

agent skipped the down-migration test and the rollback script references columns that no longer exist

Use this when

• Rollback script references columns that no longer exist
• Down migration was never tested and now fails
• Need up/down migration testing in CI
• An agent skipped testing the down migration

Not for

• Rollbacks that fail because data cannot be restored (a data problem, not a script problem)
• Intentionally irreversible migrations (document them; do not fake a down)
• Up migrations that fail (fix the up direction first)

Steps

1. Read the down migration against the current schema

Open the down function and compare every statement against the schema as it exists after the up migration plus all later migrations. Mark every reference to a column, table, or constraint that no longer exists or has changed.

Expected output: the list of stale references. This is the complete repair list.

2. Rewrite the down to reverse the up exactly

The down migration must undo precisely what its up did: drop what up added, re-add what up dropped (with types), restore what up altered. Where the schema moved on (later migrations changed the same objects), the down must account for the current shape, not the shape at write time.

Expected output: a down migration whose every statement targets objects that exist.

3. Prove it: up then down on scratch

On a fresh database, run the migration up, then immediately down, then up again:

alembic upgrade [rev] && alembic downgrade -1 && alembic upgrade [rev]

(or the equivalent for your tool). Expected output: all three steps succeed, and the schema after the cycle matches the schema after a single up.

4. Add the up/down test to CI

Every migration gets the up-down-up cycle on a scratch database in CI. A migration whose down fails never merges.

Expected output: stale down migrations break the build at pull-request time instead of during a production rollback.

5. Teach the agent the reversibility rule

The agent's migration procedure: no migration is "done" until its down has executed successfully on scratch. Irreversible migrations are declared explicitly and get a compensating plan instead of a fictional down.

Expected output: down migrations stop being write-only.

Tool compatibility

alembic, django, rails, flyway (undo), liquibase (rollback). The up-down-up test pattern maps to every tool.

Variant phrasings

down migration references dropped columns

Steps 1 and 2: diff the down against the current schema, rewrite it to reverse the up exactly.

test rollback migration CI

Step 4: up-down-up on scratch for every migration, in CI.

rollback script never tested fails

Step 3: the up-down-up cycle is the test; run it before trusting any rollback.

Why it happens

Down migrations are written against a schema that keeps changing: later migrations alter the objects the down references, but nobody re-reads the down. The up gets executed constantly (every deploy, every fresh database); the down gets executed only in emergencies, which is the worst time to discover it is stale. Testing is the only thing that keeps the two in sync.

Edge cases

• A down that restores dropped columns restores them empty: data loss is inherent to rolling back a drop. Note it in the migration; the test proves the mechanics, not the data.
• Some downs are only valid immediately after their up (before later migrations build on it): document the valid rollback window, or the test gives false confidence.
• Destructive ups deserve a backup before the up runs, not just a tested down: the down reverses schema, backups reverse data loss.

Provenance

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

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

Thread query: agent skipped the down-migration test and the rollback script references columns that no longer exist

TL;DR: The down migration was written once and never executed, so it references a schema that later migrations changed: it is fiction, not a rollback plan. Fix it to reverse exactly what the up migration did against the schema as it exists now, prove it with migrate-up-then-down on a scratch database, and add that test to CI for every migration. An untested down migration is a plan you have never read.

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: This is the normal middle of an expand/contract migration, not a broken state. Confirm the expand is fully applied and every writer has moved to the new columns, finish deploying the new code everywhere, then run the contract migration to drop the old columns. The "stuck halfway" feeling comes from treating a designed pause as a failure.

The problem as reported:

the agent applied the expand migration but the contract migration never ran - the old columns are still being written to and the agent is stuck halfway

1. Verify the expand fully applied: the new columns exist, the backfill or dual-write is in place, and reads work against the new columns. Expected: the schema matches the expand migration's end state.
2. Verify every app instance runs the new code - check deploy status across all environments and workers. Expected: zero instances still on the old version.
3. Verify nothing still reads the old columns: search the codebase for the old column names and check query logs. Expected: zero references outside the contract migration itself.
4. Run the contract migration (drop the old columns, remove dual writes). Expected: it applies cleanly and the old columns are gone.
5. Smoke-test writes and reads end to end. Expected: the app works with only the new columns present.

Use this when

• An expand/contract migration paused between phases.
• Old columns are still present and being written.
• You're unsure whether it's safe to run the contract step.

Not for this skill when

• You never used expand/contract - a plain half-applied sequence is the roll-forward situation, different skill.
• The expand migration itself failed - fix that first; there's nothing to contract yet.

Variant phrasings

• expand migration done contract never ran
• stuck halfway expand contract migration
• old columns still written after migration
• safe to run contract migration

Why it happens

Expand/contract deliberately splits a breaking schema change into two safe deploys with a pause between them. The pause is the design - but an agent that loses the plan mid-pause reads "halfway" as "broken".

Edge cases

• A forgotten cron job, analytics pipeline, or replica still writing the old columns: find it in query logs before contracting, or the contract migration breaks it.
• Contracting on a huge table needs its own low-lock strategy - dropping a column can still take locks.
• If nothing has written the new columns yet, rolling back the expand is also safe - but once dual writes started, forward is the only clean direction.

Provenance

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

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

Thread query: the agent applied the expand migration but the contract migration never ran - the old columns are still being written to and the agent is stuck halfway

TL;DR: