Skip to content

Fix sharding_key_id for project runners pointing to non-associated projects

As of Feb 6 2025, we have 274 project runners whose sharding_key_id is pointing to a project that isn't associated to the runner itself:

gitlabhq_dblab> SELECT COUNT(*) FROM ci_runners WHERE ci_runners.runner_type = 3 AND sharding_key_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM ci_runner_projects WHERE ci_runner_projects.project_id = ci_runners.sharding_key_id AND ci_runner_projects.runner_id = ci_runners.id)
+-------+
| count |
|-------|
| 274   |
+-------+
SELECT 1

gitlabhq_dblab> SELECT sharding_key_id, COUNT(*) AS count FROM ci_runners WHERE ci_runners.runner_type = 3 AND sharding_key_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM ci_runner_projects WHERE ci_runner_projects.project_id = ci_runners.sharding_key_id AND ci_runner_projects.runner_id = ci_runners.id) GROUP BY sharding_key_id ORDER BY count DESC LIMIT 10
+-----------------+-------+
| sharding_key_id | count |
|-----------------+-------|
| 13699771        | 4     |
| 55942245        | 4     |
| 16780970        | 3     |
| 45213619        | 3     |
| 42664494        | 3     |
| 56706104        | 3     |
| 52165892        | 2     |
| 42338315        | 2     |
| 60841272        | 2     |
| 47126379        | 2     |
+-----------------+-------+

Now that !180368 (merged) seems to have fixed the problem, we should run a batched background migration to fix these runners (along with runner machines and taggings), otherwise they'll cause problems with the Org Mover. The migration could be based on Gitlab::BackgroundMigration::BackfillShardingKeyIdOnCiRunners.