Migration failure: insert or update on table "group_type_ci_runner_machines_687967fa8a" violates foreign key constraint "fk_rails_3f92913d27"

Description

The BackfillCiRunnerMachinesPartitionedTable batched background migration fails due to an existing foreign key constraint:

ERROR:  insert or update on table "group_type_ci_runner_machines_687967fa8a" violates foreign key constraint "fk_rails_3f92913d27"
DETAIL:  Key (runner_id, runner_type)=(3169, 2) is not present in table "group_type_ci_runners_e59bb2812d".

Environment

  • Impacted offerings:

    • GitLab Self-Managed

  • Impacted versions:

    • 17.7.0 and later

Solution

This is a known bug awaiting fix.

Workaround

The migration can be completed by re-running the migration without the key constraint. To do this:

  1. Remove the FK constraing by undoing the RetryAddFkFromPartitionedCiRunnerManagersToPartitionedCiRunners migration 
    sudo gitlab-rake db:migrate:down VERSION=20241211072300
  2. Run the failing batched background migration in the UI - BackfillCiRunnerMachinesPartitionedTable: ci_runner_machines. This is queued up by post deploy 20241107064635 - QueueBackfillCiRunnerMachinesPartitionedTable
  3. Once the migration has completed, re-run the migrations to add back the FK constraint 
    sudo gitlab-rake db:migrate
  4. Confirm all migrations have run and none are down 
    sudo gitlab-rake db:migrate:status | grep ' down '

Cause

Similar to Issue, where the foreign key constraint was prematurely added, when it was reintroduced in 17.8, that was too early for Self Managed deployments, and the constraint prevents the BackfillCiRunnerMachinesPartitionedTable migration from running in the UI.

Related Links

Review how batched background migrations work

On this page...