Java.DBMigrationTools.What are common errors when using migration tools?

Short interview answer (what to say first)

“The most common errors are editing applied migrations, running long data updates during deployment, non-idempotent scripts, relying on rollbacks in production, and environment drift caused by manual DB changes.”

That alone already signals seniority.

---

1. Editing an already-applied migration ❌ (TOP #1 mistake)

What happens

• Migration is already applied in prod
• Developer edits the SQL file
• Checksum mismatch → migration fails

This applies to both Flyway and Liquibase.

Why it’s bad

• Breaks reproducibility
• Prod ≠ staging ≠ local
• Forces unsafe “repair” actions

Senior rule

Migrations are immutable.
If you need a change → create a new migration.

---

2. Large data updates inside migrations ❌

UPDATE orders SET status = 'DONE';
-- millions of rows

Risks

• long locks
• deployment hangs
• replica lag
• rollback impossible

Correct approach

• Schema change → migration
• Data backfill → background job / batch

3. Non-idempotent migrations ❌

INSERT INTO roles (name) VALUES ('ADMIN');

Runs twice → duplicated data.

Correct

INSERT INTO roles (name)
VALUES ('ADMIN')
ON CONFLICT DO NOTHING;

Senior engineers always assume migrations might re-run.

---

4. Assuming rollbacks are safe ❌

Especially common with Liquibase users.

Reality

• Rollbacks are rarely tested
• Large data rollbacks are unsafe
• Production rollbacks often make things worse

Senior mindset

Forward-only fixes in production.
Rollbacks are mostly for local/dev.

---

5. Mixing schema & business logic ❌

Bad migration:

UPDATE account
SET balance = balance - fee(balance);

Why wrong

• Business rules change
• Hard to reason about
• Impossible to re-run safely

Rule

Migrations ≠ business logic.

---

6. Environment drift ❌

Symptoms:

• “Works on my DB”
• Manual hotfixes in prod
• Missing migrations in some envs

Root cause

• DB changes not tracked in migrations
• Manual SQL in prod

Senior practice

• Everything goes through migrations
• No exceptions (even hotfixes)

7. Wrong migration ordering ❌

Examples:

• FK before table exists
• Index before column
• Data migration before schema expand

Fix

Use Expand → Migrate → Contract:

1. Add new schema
2. Backfill safely
3. Remove old schema

---

8. One migration per “feature” ❌

Anti-pattern:

V15__feature_X_everything.sql

Contains:

• 5 tables
• 10 columns
• 3 data updates

Problem

• Hard to debug
• Hard to revert
• Hard to reason about

Better

• Small, focused migrations
• One logical change per file

9. Running migrations automatically everywhere ❌

Blind auto-run on:

• local
• tests
• prod
• read replicas (!)

Better

• Explicit control in CI/CD
• Separate migration job
• Clear ownership

---

10. No testing of migrations ❌

Common lie:

“They’re just SQL.”

Reality:

• syntax errors
• lock issues
• slow plans
• permission problems

Senior practice

• Run migrations on:
  • clean DB
  • real-sized staging DB
• Measure execution time

---

Perfect final interview answer (polished)

“Common errors include editing applied migrations, running large data updates during deployment, writing non-idempotent scripts, relying on rollbacks in production, and allowing environment drift through manual DB changes. Mature teams keep migrations small, immutable, forward-only, and limit them mostly to schema changes.”

If you say that calmly — senior signal achieved ✅