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.sqlout synchronized while switching between local development branches. - Developers then often just selectively pick changes from
db/structure.sqland 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-schemais 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:pristinetask. - Make CI job
db:check-schemaable to detect if something goes wrong, like an index being accidentally deleted fromdb/structure.sqlwithout 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