skip to content

A developer fixes a typo by editing a migration script that has already been applied to production and to several teammates' local databases. What goes wrong, and what should they have done instead?

level: middleimportance: must knowfreq 62%

answer

  1. applied = immutable, append-only
  2. checksum mismatch blocks the deploy
  3. silent divergence is the worse outcome
  4. fix forward with a new version
  5. repair only for tool/whitespace changes

basics

~20 s

The stored checksum no longer matches the edited file, so validation fails on every database that already ran it, while a fresh database gets the corrected version — the two diverge. Applied migrations are immutable: revert the edit and ship a new migration containing the fix.

solid answer

~50 s

Every applied script has a checksum row in the history table. Editing the file makes the recomputed checksum differ, so the next run fails validation with a "migration checksum mismatch" error on production, staging and every teammate's database that already applied it — the deploy is blocked. Worse is the case where validation is disabled: a database created from scratch now has the corrected schema while the already-migrated ones keep the old one, and nothing tells you they differ. The rule is that **applied migrations are append-only and immutable**. The fix ships as a *new* versioned script that corrects the state — add the missing column, drop the wrong index, rename the misspelled one. Only a script that has never been applied anywhere (still on your branch, never merged) can safely be edited. If the file was already changed and the change is genuinely cosmetic, `flyway repair` (or Liquibase's `clearCheckSums`) rewrites the stored checksums — an operator action, taken deliberately, never a routine step in the pipeline.

code

sql · 8 lines
sql
-- V12__create_customer.sql  (ALREADY APPLIED — do not touch)
CREATE TABLE customer (
  id     BIGINT PRIMARY KEY,
  email  VARCHAR(50) NOT NULL
);

-- V38__widen_customer_email.sql  (the fix)
ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(255);

go deeper

for a junior

Say that applied migrations are immutable and that the checksum makes the tool fail; the fix is a new migration file.

for a middle

Explain the divergence between freshly built and already-migrated databases, and name the narrow exception of an unmerged, never-applied script.

for a senior

Discuss repair/clearCheckSums and their narrow legitimate uses, half-applied migrations on engines without transactional DDL, and CI running migrations against a production snapshot.

for a principal

Frame immutability as the invariant that makes the schema reproducible across environments, and describe the guardrails (review, CI on restored dumps, commit-time checks) that make the invariant enforced rather than merely agreed.

## Why immutability is the load-bearing rule A versioned migration is not source code that describes a desired state; it is a *record of an event that already happened somewhere*. Production has already executed `V37`. That execution cannot be un-executed by changing the text of the file. Once you accept that, the rule follows: the set of applied scripts is append-only, and correction happens by appending, never by rewriting history. ## What the checksum actually does When a script is applied, the tool stores a hash of its content (Flyway's `checksum`, Liquibase's `MD5SUM`). On every subsequent run it recomputes hashes for the scripts that already have history rows and compares. A mismatch means the file changed after being applied, so the tool aborts with a validation error before touching anything. This is a *detector*, not a protection: it catches the mistake, it does not undo it. The failure is loud and blocking — which is the point. The alternative, silent divergence, is far worse. ## The divergence failure mode Imagine `V12__create_customer.sql` created `email VARCHAR(50)` and someone edits it to `VARCHAR(255)` with validation turned off: - Production applied V12 months ago: the column is still 50 characters. No new script runs, because V12 already has a row. - A brand-new environment (a CI database created from scratch, a new developer's laptop) applies V12 fresh and gets 255. You now have two schemas from one repository. Tests pass in CI and fail in production, or vice versa, and the migration history offers no clue because nothing in it is wrong-looking. Reproducibility — the whole reason to use versioned migrations — is gone. ## The correct fix: forward-only Append `V38__widen_customer_email.sql` with `ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(255)`. Every environment converges: those at V37 apply it, fresh ones apply V12 then V38 and land in the same place. The history reads as a truthful narrative of how the schema evolved, including the mistake and its correction, which is exactly what you want during an incident review. The same logic applies to a migration that failed halfway on an engine without transactional DDL: do not "fix the script and rerun". Inspect what actually landed, repair the history row, and write the next script to be correct against the real current state. ## The one legitimate exception A migration that exists **only on your unmerged branch**, never applied to any shared database, is still a draft. Edit it freely; your local database can be dropped and rebuilt. The moment it merges to the trunk — or the moment CI, staging or a colleague's laptop has run it — it becomes history. Some teams enforce this with a CI job that runs migrations against a restored production dump: a checksum mismatch there fails the pull request. ## Repair, and why it is not routine Both tools offer an escape hatch: `flyway repair` recomputes and rewrites checksums for applied scripts (it also removes failed rows), and Liquibase has `clearCheckSums`. Legitimate uses are narrow: - A tool upgrade changed how checksums are computed for everyone. - A whitespace/line-ending normalisation (an editor rewrote CRLF to LF) altered content that has no semantic effect. What makes repair dangerous is that it *asserts* "the file and the database agree" without checking. If someone edited a real `ALTER` statement, repair silences the alarm and locks in the divergence. Never wire `repair` into an automated deploy step to make red builds go green; that converts the safety mechanism into noise. ## Practical hygiene that prevents the whole class of problem - One logical change per migration file, so a mistake is easy to correct by a follow-up rather than a rewrite. - Code review on migration files, treating them like a public API change. - CI that applies migrations both to an empty database *and* to a restored snapshot of production — the two paths must yield the same schema. - A lint or pre-commit check that refuses modifications to migration files already present on the main branch. - Never storing environment-specific values inside a migration, because that tempts people to edit it per environment.

  • When is it actually fine to edit a migration file?
    Only while it has never been applied to any database anyone else depends on — typically a script still on an unmerged branch, where the only affected database is your own and you can drop and recreate it. Once it lands on the main branch, or CI, staging or a colleague has run it, it is history and must be corrected by appending a new migration.
  • Your pipeline is red with a checksum mismatch on V12 and someone suggests adding flyway repair to the deploy script. What is your response?
    Repair rewrites the stored checksum to match the file without verifying that they mean the same thing, so automating it turns a real divergence detector into a no-op. The right move is to find out what changed in V12: if it is whitespace or a tool-version artefact, run repair once as a deliberate operator action; if it is a real statement change, revert the file and append the correction as a new migration.
  • How do you catch this class of mistake before it reaches production?
    Run migrations in CI against a restored production-like snapshot in addition to an empty database — a mismatch fails the pull request rather than the deploy. A repository check that rejects diffs to migration files already on the main branch catches it even earlier, at commit time.

It is like editing a bank statement to correct a transaction: the money already moved. You post a correcting entry, you do not rewrite the ledger line.

saying these in an interview costs you the question

  • "Just delete the row from the history table and rerun it" — that re-executes DDL against a schema that already has the change
  • Disabling validation so the build goes green, which converts a loud failure into silent schema drift
  • Believing editing the file retroactively changes what production already ran
  • Treating flyway repair as a normal pipeline step rather than a deliberate one-off operator action

context

resolving links…