Skip to content

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:

image

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

  1. 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)
  2. 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;