Cleanup inconsistent records in internal_ids
The importer does not respect the generator scheme we have in place for internal ids. In this issue, we'll cleanup any inconsistent records we have on GitLab.com in one-go. This is a one-off data migration that is not going to fix the root cause but unblock any projects that already ran into it.
The bug and root cause fix are tracked in: https://gitlab.com/gitlab-org/gitlab-ce/issues/49446
Schedule + Roles
Executed on Fri, Jul 27, 10:20am UTC
Reviewed: @yorickpeterse
Change team: @abrandl
Data migration:
We're going to remove any records in internal_ids
that are inconsistent with respect to the related maximum internal id present in models. Those tracking records in internal_ids
are going to be recreated upon the next create
action, e.g. when the next issue is created. At this time, the maximum iid
is taken from the relation and a record in internal_ids
is created to track this.
According to https://gitlab.com/gitlab-org/gitlab-ce/issues/49446#note_89686012, affected models are.
- issues
- merge_requests
- deployments
- milestones (by project)
- ci_pipelines
This is what basically happens. However this has been put into a Rails migration that can be defined in a Rails console, so we're not copy&pasting queries.
-- SELECT runtime: 4.6s, rows: 2
DELETE FROM internal_ids WHERE id IN (
SELECT internal_ids.id FROM (
SELECT project_id AS project_id, max(iid) as maximum_iid from issues GROUP BY project_id
) maxima JOIN internal_ids USING (project_id)
WHERE internal_ids.usage=0 AND maxima.maximum_iid > internal_ids.last_value
) RETURNING project_id, 'issues'
-- SELECT runtime: 4.5s, rows: 3
DELETE FROM internal_ids WHERE id IN (
SELECT internal_ids.id FROM (
SELECT target_project_id AS project_id, max(iid) as maximum_iid from merge_requests GROUP BY project_id
) maxima JOIN internal_ids USING (project_id)
WHERE internal_ids.usage=1 AND maxima.maximum_iid > internal_ids.last_value
) RETURNING project_id, 'merge_requests'
-- SELECT runtime: 2.4s, rows: 1
DELETE FROM internal_ids WHERE id IN (
SELECT internal_ids.id FROM (
SELECT project_id AS project_id, max(iid) as maximum_iid from deployments GROUP BY project_id
) maxima JOIN internal_ids USING (project_id)
WHERE internal_ids.usage=2 AND maxima.maximum_iid > internal_ids.last_value
) RETURNING project_id, 'deployments'
-- SELECT runtime: <1s, rows: 19
DELETE FROM internal_ids WHERE id IN (
SELECT internal_ids.id FROM (
SELECT project_id AS project_id, max(iid) as maximum_iid from milestones GROUP BY project_id
) maxima JOIN internal_ids USING (project_id)
WHERE internal_ids.usage=3 AND maxima.maximum_iid > internal_ids.last_value
) RETURNING project_id, 'milestones'
-- SELECT runtime: 55s, rows: 2
DELETE FROM internal_ids WHERE id IN (
SELECT internal_ids.id FROM (
SELECT project_id AS project_id, max(iid) as maximum_iid from ci_pipelines GROUP BY project_id
) maxima JOIN internal_ids USING (project_id)
WHERE internal_ids.usage=5 AND maxima.maximum_iid > internal_ids.last_value
) RETURNING project_id, 'ci_pipelines'
Plan
- In a rails console, define the migration from https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/6676
- Execute the migration by running
DeleteInconsistentInternalIdRecords.new.migrate(:up)
- Take note of the output of the migration.
Impact
During the time of the DELETE
, it won't be possible for those projects to create another respective model instance. However, that is likely to be the case already because of the inconsistency (for some projects). The maximum expected time for a project to be unable to create a model instance is under 1 minute.