Fix duplicates in pages when sorting members by last_activity_on
What does this MR do and why?
Fix duplicate entries in different pages when sorting by last_activity_on. This fix will affect the Group Members and Billable Members lists both in the API and UI.
Fixes https://gitlab.com/gitlab-org/gitlab/-/issues/352306
Screenshots or screen recordings
How to set up and validate locally
- Create a group
- Invite more than 20 members. Consider using the seed snippet below
- Ensure most of the invited members have the same Last Activity date. Consider using the seed snippet below
- Navigate to Settings -> Usage Quotas -> Seats tab
- Check members list in first and second pages
- You should not see any duplicates
Seed Snippet
require 'gitlab'
client = Gitlab.client(
endpoint: 'http://gdk.test:3000/api/v4',
private_token: '<TOKEN>'
)
require 'faker'
def gen_people
[
Faker::Movies::BackToTheFuture.character,
Faker::TvShows::HowIMetYourMother.character,
Faker::TvShows::GameOfThrones.character,
Faker::TvShows::ParksAndRec.character
].sample
end
user_count = 55
people = Array.new(user_count) { gen_people }.uniq
require 'securerandom'
users = people.map do |user|
username = user.downcase.gsub(/[^0-9A-Za-z]/, '')
email = "#{username}@example.com"
password = SecureRandom.base64(32) # random passwords by default
puts "User -- Name: #{user}, UserName: #{username}, Email: #{email}"
client.create_user(email, password, username, { name: user, skip_confirmation: true } )
end
group_access = [10, 20, 30, 40, 50]
users.sample(users.count).each do |user|
begin
puts "Group Add: <GROUPID>: #{user.name}"
client.add_group_member(<GROUPID>, user.id, group_access.sample)
rescue StandardError
next
end
end
users.each do |user|
user.last_activity_on = Date.yesterday
user.save!
endTo compare and find the duplicates in the different pages of the API, the following snippet can be utilized.
Username comparison snippet
require 'uri'
require 'net/http'
require 'json'
begin
puts "Get first page"
uri = URI('http://gdk.test:3000/api/v4/groups/<GROUPID>/billable_members?page=1&sort=last_activity_on_desc&private_token=<TOKEN>')
page1_usernames = Net::HTTP.get_response(uri)
page1_usernames = JSON.parse(page1_usernames.body).map { |item| item['username'] } if page1_usernames.is_a?(Net::HTTPSuccess)
p 'Page 1: '
p page1_usernames
puts "Get second page"
uri = URI('http://gdk.test:3000/api/v4/groups/<GROUPID>/billable_members?page=2&sort=last_activity_on_desc&private_token=<TOKEN>')
page2_usernames = Net::HTTP.get_response(uri)
page2_usernames = JSON.parse(page2_usernames.body).map { |item| item['username'] } if page2_usernames.is_a?(Net::HTTPSuccess)
p 'Page 2: '
p page2_usernames
p 'Duplicates: '
p page1_usernames & page2_usernames
rescue StandardError => ex
p ex
endFor Database Review
RAW SQL
SELECT DISTINCT "users"."id"
FROM "users"
WHERE "users"."id" IN (SELECT DISTINCT "members"."user_id"
FROM "members"
WHERE "members"."type" = 'ProjectMember'
AND "members"."source_type" = 'Project'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (SELECT "projects"."id"
FROM "projects"
INNER JOIN "namespaces"
ON "namespaces"."type" = 'Group' AND
"namespaces"."id" =
"projects"."namespace_id" AND
"namespaces"."type" = 'Group'
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants"
AS ((SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 73)
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT id
FROM "base_and_descendants" AS "namespaces")))
AND ("users"."user_type" IS NULL OR "users"."user_type" != 6)
AND ("users"."state" IN ('active'));The Explain Query Plan link: https://console.postgres.ai/shared/9cff4cc5-962f-4236-b97f-b6630e6fceb3
The Exec Query in slack for gitlab-org shows: The query has been executed. Duration: 65.187 ms
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #352306
Edited by Mayra Cabrera