agent treated 0 rows affected as a migration failure and rolled back a successful deploy

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: Inspect the failed row, decide whether the migration actually landed, then tell prisma which it is with migrate resolve. If the schema changes are present in the database, mark the migration applied; if not, mark it rolled back and re-run the deploy. Never delete the row by hand - resolve is the supported path and keeps prisma's bookkeeping consistent.

The problem as reported:

the agent's prisma migrate lost its database connection halfway - the _prisma_migrations table has a row stuck in failed state

1. Look at the stuck row: select migration_name, started_at, finished_at, applied_steps_count from _prisma_migrations where finished_at is null;. Expected: one row for the interrupted migration, with finished_at null.
2. Check whether its changes actually landed: compare the live schema against the migration's SQL file (inspect the affected tables in psql and read the migration.sql). Expected: a yes-or-no answer - the statements either ran or they didn't.
3. If the changes ARE in the database: npx prisma migrate resolve --applied "MIGRATION_NAME" (copy the exact name from step 1). Expected: prisma reports the migration marked as applied.
4. If the changes are NOT there: npx prisma migrate resolve --rolled-back "MIGRATION_NAME", then npx prisma migrate deploy. Expected: the failed row is cleared and the migration applies cleanly.
5. Run npx prisma migrate status. Expected: "Database schema is up to date" with no failed migrations listed.

Use this when

• A prisma migrate died mid-run after losing its database connection.
• prismamigrations has a row with finished_at null blocking later deploys.
• You need to choose between resolve --applied and --rolled-back.

Not for this skill when

• The complaint is drift rather than a failed row - that's a baseline problem, different fix.
• You're on migrate dev fighting the shadow database - that's a shadow-DB issue, not a failed production migration.

Variant phrasings

• prisma migration stuck in failed state
• prisma migrate resolve applied vs rolled back
• prismamigrations finished_at null
• prisma deploy blocked by failed migration

Why it happens

Prisma writes the migration row when the migration starts and only stamps finished_at on success. A dropped connection leaves the row open, and every later deploy refuses to proceed until that row is explicitly resolved.

Edge cases

• The migration half-applied (some statements ran): reconcile the schema by hand before resolving - resolving --applied on a half-applied migration corrupts future diffs.
• MIGRATION_NAME must match exactly: copy it from the step-1 query; a typo resolves nothing.
• Resolving --applied when the migration did NOT land: prisma will believe the schema changed and generate wrong diffs forever after - when in doubt, roll back and re-run.

Provenance

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

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

Thread query: the agent's prisma migrate lost its database connection halfway - the prismamigrations table has a row stuck in failed state

TL;DR:

Not specified by the original author.

TL;DR

The agent polled the deploy status aggressively, got rate-limited, and mistook a stale or empty status response for success. The deploy never happened (or never finished). The fix is verification discipline: check the deployments list directly, confirm the version changed, and never infer success from the absence of an error.

The query

agent hit wrangler rate limit while polling deploy status, reported success that never happened

Use this when

• The agent reported a successful deploy but behavior is unchanged.
• The run log shows rapid polling followed by 429s.
• The deployments list has no new version.

Not for

• Deploys that failed with a clear error (fix the error).
• Polling timeouts with no rate limiting (timeout problem).
• Deploys that succeeded but serve old code (routing problem).

Steps

Step 1: Check the ground truth

npx wrangler deployments list --name [worker-name]

Expected result: the truth about whether a new version exists. If the agent's 'success' has no matching version, the report was fiction.

Step 2: Find what the agent actually saw

Read the polling section of the run log. Identify the 429 responses and what the agent concluded from them.

Expected result: the misread is visible: an empty or error response parsed as success, or a stale cached status treated as current.

Step 3: Poll correctly: slow, bounded, terminal-state aware

Poll the deployments list (not the deploy command's output) every 15-30 seconds, up to a bounded total, stopping on terminal states.

Expected result: status checks stay under the rate limit and report reality.

Step 4: Require version-change proof for 'success'

The agent may only report success after the deployments list shows a new version ID that was not there before the deploy started.

Expected result: 'success' means a new version exists, not 'no error was seen'. The evidentiary bar is explicit.

Step 5: Run the deploy again, properly observed

Expected result: this time the deploy is real, the version appears, and the report matches reality.

Why it happens

Absence of error is not success, but polling code often treats it that way. Rate limiting makes it worse: the 429s suppress the very signals the agent needs, and the agent fills the silence with optimism. The root fix is epistemic: define what counts as evidence of success before polling starts.

Edge cases

• A deploy can succeed while polling is rate-limited. The version check afterward still works; polling is for progress, the deployments list is for truth.
• 429s during the deploy itself (not just polling) can leave ambiguous state. Check the list before retrying.
• Cache the 'before' version ID at the start of the run so the comparison is exact.
• Different wrangler commands have different rate limits. Polling deployments list is cheaper than re-running deploy.

Provenance

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

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

Thread query: agent hit wrangler rate limit while polling deploy status, reported success that never happened

TL;DR: The agent polled the deploy status aggressively, got rate-limited, and mistook a stale or empty status response for success. The deploy never happened (or never finished). The fix is verification discipline: check the deployments list directly, confirm the version changed, and never infer success from the absence of an error.