CI check for MRs with migrations that db:rollback results to the same schema as the one before db:migrate
We found an MR that adds an index, where the migration's down method accidentally used the wrong table:
def up
add_concurrent_index(:vulnerability_occurrences, :id, where: "report_type = 2", name: INDEX_NAME)
end
def down
remove_concurrent_index_by_name(:project_registry, INDEX_NAME)
end
So basically, remove_concurrent_index_by_name
can not find the index in the wrong table and just ends gracefully with warning level message in the logs.
The issue is that our CI tests are not catching this and similar issues.
As far as I can see, the db:check-schema
job just checks for inconsistencies going up (db:migrate
), while the db:rollback
job just rolls back and then migrates back up and just checks for errors.
It would help a lot to catch issues like this one if we could somehow:
- Start with the schema before the migrations introduced in the MR run (
schema1
) - Run
db:migrate
anddb:rollback
with the appropriateVERSION
to get back where we started - Compare the schema (
schema2
) withschema1
for inconsistencies. As an example, in the case of !39962 (merged) we would have an additional index defined.
I am not sure if this should be done by dumping and file diff or whether we can just use git diff or a similar trick.