Skip to content

Make sure CI job `db:check-schema` can catch corrupted `structure.sql`

Problem to solve

The underlying problem is explained at !72697 (comment 710950807)

The problem here is that:

  • It's easy to get local db/structure.sql out synchronized while switching between local development branches.
  • Developers then often just selectively pick changes from db/structure.sql and commit them to their merge requests introducing database migrations.
  • This is a manual process which can go wrong, accidentally putting the ordering wrong and exaggerate the problem more. And it can be accidentally deleting an index 🙀 !37148 (comment 482165472)
  • Given that db:check-schema is running rake db:structure:load, it can load the wrong schema to begin with, it cannot catch this problem.

There are two things to solve here:

  1. Make it easier to repair the local database to follow the same schema ordering. Perhaps add rake db:structure:pristine task.
  2. Make CI job db:check-schema able to detect if something goes wrong, like an index being accidentally deleted from db/structure.sql without touching the migration introducing it.

Both things can potentially use the same way to find the "correct" db/structure.sql and restore/verify against the current db/structure.sql.

Potential solution

We can consider git checkout to a previous major release of GitLab, and basing on that db/structure.sql for rake db:structure:load, and run full rake db:migrate after that.

This will simulate an instance installed from a previous major release, and run migrations for the current release, and the committed db/structure.sql should stay the same.

Edited by Lin Jen-Shin