migrations: dirty flag can linger even after a successful migration

[chrismwendt]

It's possible for a migration to complete successfully but leave the dirty flag set to true.

This requires unnecessary manual intervention to fix, and the fix is always UPDATE schema_migrations SET dirty = 'f'.

Here's how it can happen:

• The frontend starts up and begins a golang-migrate migration
• golang-migrate sets the dirty flag to true
• golang-migrate sends the SQL commands to Postgres
• The migration happens to be slow, and the frontend pod gets restarted by Kubernetes after 5 minutes
• The SQL commands complete successfully
• The dirty flag is left set to true (if the SQL commands completed fast enough, the frontend would still be around and golang-migrate would set the dirty flag to false)
• The frontend pod starts back up and sees a dirty database and prints the dreaded "Failed to migrate the DB. Please contact support@sourcegraph.com for further assistance: Dirty database version 1528395882. Fix and force version."

Solutions

• Hardcode: modify all migration files by appending SET dirty = 'f' and git commit those changes https://github.com/sourcegraph/sourcegraph/pull/25090
• FS injection: inject SET dirty = 'f' into each migration file on the fly when reading the migration files
• golang-migrate injection: add an option to golang-migrate to inject SET dirty = 'f'

Previous work

We intended to fix this in the past, but I accidentally merged the PR into the wrong branch and it never got into master 🤦‍♂️ https://github.com/sourcegraph/sourcegraph/pull/7909

[github-actions]

Heads up @macraig - the "team/code-intelligence" label was applied to this issue.

[efritz]

Another solution is to rewrite the entire freaking thing.