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 runningrake db:structure:load
, it can load the wrong schema to begin with, it cannot catch this problem.
There are two things to solve here:
- Make it easier to repair the local database to follow the same schema ordering. Perhaps add
rake db:structure:pristine
task. - Make CI job
db:check-schema
able to detect if something goes wrong, like an index being accidentally deleted fromdb/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