agent's schema migration dropped a column with data: dry-run diff first

Not specified by the original author.

TL;DR

Never let an agent apply DDL directly. Require a dry-run diff first, flag any DROP COLUMN against actual row data, back up the table, and only then apply.

Symptom

After an agent-run migration, a column is gone along with its data. Downstream queries break on the missing column, and the data is recoverable only from backups, if they exist.

Steps

1. Require the agent to emit the migration as a diff first (dry-run or plan mode), never apply directly. Most migration frameworks support this:

# example: print the SQL without running it
migrate plan --dry-run

Expected output: the full DDL the agent intends to run, reviewable before anything changes.

2. Review the diff for DROP COLUMN and other destructive statements. For each dropped column, check whether it holds data URIs

SELECT COUNT(*) FROM [table] WHERE [column] IS NOT NULL;

Expected output: a non-zero count means the drop destroys real data; that migration does not ship without explicit approval.

3. Back up the table (snapshot or CREATE TABLE backup AS SELECT) before applying anything destructive.

Expected output: a restore path exists even if the review missed something.

4. Apply, then verify column presence and row counts; keep the backup until the next successful deploy.

Expected output: the schema change lands with proof nothing unexpected was lost.

When to use

• An agent generates and applies schema migrations
• Migrations auto-apply on deploy or on agent run
• You need a reviewable diff of exactly what will change

When not to use

• Application-code deploys with no DDL (nothing destructive to review)
• Intentional destructive migrations you already approved (the diff still helps, but the gate is already passed)
• Read-only schema inspections (no apply step, no risk)

Variant phrasings

agent renamed a column and the diff shows drop plus add

Renames look like drop+add in most diff tools. Confirm intent explicitly; a true rename needs data copied, not dropped.

agent's migration dropped an index, not a column

Same gate applies: indexes are cheap to rebuild but expensive to live without. Flag drops, confirm intent.

Why it happens

Agents optimize for the requested schema shape and treat unmentioned columns as disposable. Only a diff makes the destruction visible before it happens; without it, the first sign is broken queries.

Edge cases

• Some frameworks auto-apply migrations on boot; disable that wherever an agent can trigger a boot.
• Large tables need snapshot windows; a backup that takes hours needs scheduling, not ad-hoc runs.
• Column drops inside transactions still lock the table; plan for the lock on hot tables.

Provenance

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

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

Thread query: agent's schema migration dropped a column with data URIs dry-run diff first

TL;DR: Never let an agent apply DDL directly. Require a dry-run diff first, flag any DROP COLUMN against actual row data, back up the table, and only then apply.

Not specified by the original author.

TL;DR

Wrangler D1 commands default to local. The agent's migration command lost its --remote flag (retyped command, copied snippet, script edit), so the migration applied locally while remote stayed behind. Now local is ahead of remote. Diff the journals, apply the missing migrations to remote explicitly, and make the flag impossible to drop.

The query

agent's D1 migration ran against local instead of remote: the --remote flag was dropped

Use this when

• Local and remote D1 schemas diverge after an agent run.
• The remote journal is missing migrations the local journal has.
• The migration log shows no --remote flag.

Not for

• Migration SQL errors (fix the SQL).
• Journal conflicts (separate fix).
• Intentional local-only migrations (not a bug).

Steps

Step 1: Diff the journals

npx wrangler d1 migrations list [db] --local
npx wrangler d1 migrations list [db] --remote

Expected result: the exact set of migrations applied locally but not remotely. That is the repair list.

Step 2: Apply the missing migrations to remote

Run the apply with --remote explicitly, for the missing set.

Expected result: remote catches up. The journals agree again.

Step 3: Verify the schemas match

Compare table lists on both sides.

Expected result: identical schemas. The divergence is fully repaired, not partially.

Step 4: Make the flag explicit and default-safe

Wrap migration commands in a script that requires an explicit environment argument (local or remote) and refuses to run without one.

Expected result: the bare default can never silently win again. Every invocation names its target.

Step 5: Add a pre-apply confirmation showing the target

Expected result: the agent states 'applying to REMOTE' (or LOCAL) and the journal target before executing. The human (or the log) can catch the mismatch.

Why it happens

'Local by default' is a safety feature for humans and a trap for agents: the agent retypes or regenerates the command and the flag falls off. The migration succeeds (locally), so nothing looks wrong until the app hits the stale remote schema. Silent success in the wrong place is worse than a loud failure.

Edge cases

• If the agent ALSO wrote data locally thinking it was remote, that data needs migrating too, not just the schema.
• The reverse (remote when local was intended) is rarer but more dangerous. The explicit-target wrapper prevents both.
• CI migration steps should hard-code --remote. No flag, no merge.
• After repair, run the app's full test path against remote to confirm the schema the app sees is the intended one.

Provenance

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

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

Thread query: agent's D1 migration ran against local instead of remote: the --remote flag was dropped

TL;DR: Wrangler D1 commands default to local. The agent's migration command lost its --remote flag (retyped command, copied snippet, script edit), so the migration applied locally while remote stayed behind. Now local is ahead of remote. Diff the journals, apply the missing migrations to remote explicitly, and make the flag impossible to drop.

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.