Add foreign key between ci_runner_projects and ci_runners
When comparing the database schema between ci_runner_namespaces
and ci_runner_projects
, it is clear that one important constraint is missing:
We should try to avoid having these edge cases between ci_runner_namespaces
and ci_runner_projects
, as it makes the codebase harder to follow, and requires special-case logic to be spread around in the codebase to handle the 2 different scenarios.
When a runner is deleted, there's no point for ci_runner_projects
records to remain in the database, so we should have a FK in place to ensure referential integrity.
Implementation plan
-
Delete all
ci_runner_projects
records which don't point to a valid runner. This represents just over 30% of the records of that table in .com:gitlabhq_dblab> SELECT COUNT(*) FROM ci_runner_projects WHERE NOT EXISTS (SELECT 1 FROM ci_runners WHERE ci_runners.id = ci_runner_projects.runner_id) +--------+ | count | |--------| | 546619 | +--------+ SELECT 1 Time: 17.769s (17 seconds), executed in: 17.768s (17 seconds) gitlabhq_dblab> SELECT COUNT(*) FROM ci_runner_projects +---------+ | count | |---------| | 1790283 | +---------+ SELECT 1 Time: 1.927s (1 second), executed in: 1.925s (1 second)
-
Add FK to
ci_runner_projects
table:ALTER TABLE ci_runner_projects ADD CONSTRAINT fk_project_runner_id FOREIGN KEY (runner_id) REFERENCES ci_runners (id) ON DELETE CASCADE;
Edited by Pedro Pombeiro - OOO from Oct 13-24