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
.
Edited by Pedro Pombeiro - OOO from Oct 13-24