Unable to update ProjectGroupLink access level

Summary

For some unknown reason, we have duplicates of (project_id, group_id) pairs in the project_group_links tables that prevent updating the access level of the project-group link because we have a validation to check for uniqueness. The user sees the message Could not update role:

invited-group-role-edit-error

In the production_json logs, the error message is Group already shared with this group. The message is also wrong. It should be Project already shared with this group.

Steps to reproduce

I don't think we will be able to reproduce this locally. Here's the query using which we can find the affected projects on gitlab.com:

SELECT
    COUNT(*)
FROM (
    SELECT
        project_id,
        group_id,
        COUNT(*)
    FROM
        project_group_links
    GROUP BY
        1,
        2
    HAVING
        COUNT(*) > 1) duplicates;

 count
-------
   837
(1 row)

There are no duplicates in the group_group_links table.

What is the current bug behavior?

The user is unable to update the access level of the group invited to a project.

What is the expected correct behavior?

The user should be able to update the access level of the invited group.

Relevant logs and/or screenshots

Kibana link

"json.status": "422",
"json.exception.message": "Validation failed: Group already shared with this group",
"json.params.key": "[group_access, namespace_id, project_id, id, group_link]", 
"json.meta.caller_id": "Projects::GroupLinksController#update",
"json.method": "PUT"

Trying to uninvite a group from the project takes you to the 422: The change you requested was rejected page.

Output of checks

This bug happens on GitLab.com

Possible fixes

  1. Identify and fix code path that produces duplicate records.
  2. Remove duplicates (keep the most recently updated row).
  3. Add a unique index to group_id_and_project_id. Currently, we do have an index on it but it's not unique:
CREATE INDEX index_project_group_links_on_group_id_and_project_id ON project_group_links USING btree (group_id, project_id);
  1. The message should also be corrected.

Workaround

Remove the project group sharing and then try to update/add with the correct access level.

Edited by Shane Maglangit