ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_ci_pipelines_on_project_id_and_iid"DETAIL: Key (project_id, iid)=([PROJECT_ID], [IID]) already exists.
Problem
In https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/264 and https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/293 we are seeing errors due to the IID tracking (handled by InternalId
) not reflecting the latest CI Pipeline IID after a project is imported, even though the IID tracking should be flushed after import https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/264#note_1718285528.
Workaround
A workaround is to reset the IID tracking for CI Pipelines for the project https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/264#note_1730359496.
To verify if you're affected, check your logs for an error like this:
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_ci_pipelines_on_project_id_and_iid"
DETAIL: Key (project_id, iid)=(52897123, 756) already exists.
The project ID of your affected project would be 52897123
in this example. To resolve the issue, connect to the PostgreSQL database and run the following queries:
SELECT * FROM internal_ids WHERE project_id = 52897123 and usage = 5;
SELECT COUNT(*) FROM ci_pipelines WHERE project_id = 52897123;
Make sure to replace 52897123
with your project ID. The value 5
for usage
in the first query is a constant and refers to CI pipelines.
The last_value
in the response of the first query will differ from the count in the second query response, 349 vs. 8217 in this example:
id | project_id | usage | last_value | namespace_id
----------+------------+-------+------------+--------------
25674456 | 52897123 | 5 | 349 |
(1 row)
count
-------
8217
(1 row)
To resolve this, delete the row from the internal_ids
table via its ID, 25674456
in this example:
DELETE FROM internal_ids WHERE id = 25674456;
This ID will be recreated when next needed, deleting it poses no risk.
Alternatively, you can use a Rails console session to achieve the same result:
InternalId.flush_records!(usage: :ci_pipelines, project_id: <project_id>)