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

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

The deploy and the migration are two halves of one change and they ran in the wrong order. Stabilize first: either finish the migration immediately (if it is safe and quick) or roll the app back to the previous version. Then wire the pipeline so the deploy waits for the migration job's success, and make migrations backward-compatible so ordering mistakes degrade instead of crash.

The query

agent deployed the app before the migration job finished - the new code queried a column that didnt exist yet

Use this when

• App deployed before the migration job finished
• New code queries a column that does not exist yet
• Deploy and migration raced each other
• An agent deployed without waiting for migrations

Not for

• Code querying a column that will never exist (a code bug, not timing)
• A migration that failed on its own (fix the migration)
• Blue-green deploys with explicit schema versioning (a different architecture)

Steps

1. Confirm the race

Check the migration job status and the deploy timeline: the app version went live while the migration was still running (or never started). Check the state table to see exactly which migrations have applied.

Expected output: confirmation that the column is missing because the migration had not finished, not because the migration is wrong.

2. Stabilize: finish the migration or roll back the app

If the migration is safe to complete right now (it is quick and non-destructive), run it to completion: the new code then finds its column. If the migration is long or risky, roll the app back to the previous version first, then run the migration in its proper window.

Expected output: the app and the schema agree again, by whichever path was safer. Do not leave them disagreeing while you deliberate.

3. Wire deploy-after-migration in the pipeline

Make the deploy job depend on the migration job's success explicitly: the deploy does not start until the migration job reports complete. In the agent's procedure, "deploy" becomes two ordered steps with a gate between them, never one fire-and-forget.

Expected output: the pipeline physically cannot deploy over an unfinished migration.

4. Make migrations backward-compatible as defense in depth

Even with ordering fixed, write migrations the old code tolerates (additive changes, nullable columns) so a future race degrades instead of crashes. The deploy-order gate is the fix; compatibility is the seatbelt.

Expected output: the next ordering mistake (there will be one) causes a quiet deploy instead of an outage.

Tool compatibility

Tool-agnostic: the ordering principle applies to every migration runner and deploy system.

Variant phrasings

deployed app before migration finished column does not exist

Step 2: finish the migration now if it is safe, else roll the app back first.

deploy raced migration how to prevent

Step 3: an explicit pipeline dependency from deploy to migration-job success.

new code queries missing column after deploy

Step 1: verify it is the race (migration unfinished) rather than a wrong migration.

Why it happens

Deploys and migrations are usually owned by different jobs or different agents, and nothing orders them by default: both are "the next step" and they race. The application assumes the schema it was built against; the migration produces that schema. Without an explicit dependency, "assumes" and "produces" are only correlated by luck and timing.

Edge cases

• Rolling the app back is only safe if the migration made no incompatible change first: check what applied before choosing the rollback path.
• Long migrations and fast deploys will always race unless the gate exists: the fix is structural (the dependency), not exhortation to "be careful".
• For zero-downtime purists, the deeper fix is expand/contract: then the new code works against both the old and new schema, and ordering stops mattering.

Provenance

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

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

Thread query: agent deployed the app before the migration job finished - the new code queried a column that didnt exist yet

TL;DR: The deploy and the migration are two halves of one change and they ran in the wrong order. Stabilize first: either finish the migration immediately (if it is safe and quick) or roll the app back to the previous version. Then wire the pipeline so the deploy waits for the migration job's success, and make migrations backward-compatible so ordering mistakes degrade instead of crash.