### Summary We have identified a racing condition on the creation of some registry entries in the Geo Tracking Database. Additional context can be seen in this customer support issue: https://gitlab.com/gitlab-com/geo-customers/-/issues/246 (Internal Link) ### What is the current _bug_ behavior? A racing condition can happen and is confirmed in at least two registry tables: `job_artifact_registry`, which include the following indexes: ```sql CREATE INDEX index_job_artifact_registry_on_artifact_id ON job_artifact_registry (artifact_id); CREATE INDEX index_job_artifact_registry_on_retry_at ON job_artifact_registry (retry_at); CREATE INDEX job_artifact_registry_failed_verification ON job_artifact_registry (verification_retry_at) WHERE ((state = 2) AND (verification_state = 3)); CREATE INDEX job_artifact_registry_needs_verification ON job_artifact_registry (verification_state) WHERE ((state = 2) AND (verification_state = ANY (ARRAY [0, 3]))); CREATE INDEX job_artifact_registry_pending_verification ON job_artifact_registry (verified_at) WHERE ((state = 2) AND (verification_state = 0)); ``` allows for multiple entries that points to the same `artifact_id` . `file_registry`, which include the following indexes: ```sql create unique index file_registry_pkey on public.file_registry (id); create index file_registry_failed_verification on public.file_registry (verification_retry_at) where ((state = 2) AND (verification_state = 3)); create index file_registry_needs_verification on public.file_registry (verification_state) where ((state = 2) AND (verification_state = ANY (ARRAY [0, 3]))); create index file_registry_pending_verification on public.file_registry (verified_at) where ((state = 2) AND (verification_state = 0)); create index index_file_registry_on_retry_at on public.file_registry (retry_at); ``` allows for multiple entries that points to the same `file_id`. ### What is the expected _correct_ behavior? Each registry entry should always point to a single resource only. ### Possible fixes We need to create unique indexes for the tables that are missing one on the corresponding resource reference. This will prevent all [downstream effects](https://gitlab.com/gitlab-org/gitlab/-/issues/479131) of this bad data. Because we can't do that on a system with existing duplicates, a data migration step will need to be performed to remove duplications before creating the new indexes. Follow https://docs.gitlab.com/development/database/deduplicate_database_records/#deduplicate-strategy-for-large-tables. Note that this requires 3 releases and the last one must come after a required stop. There are exactly 3 unique constraints that we need to add: - `package_file_id` on `package_file_registry` - `artifact_id` on `job_artifact_registry` - `file_id` on `file_registry` ### Workarounds Copied from https://gitlab.com/gitlab-org/gitlab/-/issues/479131#workaround. To delete duplicate registry rows: 1. Open Rails console in secondary site 1. Get number of model record IDs that have duplicates ```ruby artifact_ids = Geo::JobArtifactRegistry.group(:artifact_id).having('COUNT(*) > 1').pluck(:artifact_id); artifact_ids.size upload_ids = Geo::UploadRegistry.group(:file_id).having('COUNT(*) > 1').pluck(:file_id); upload_ids.size package_file_ids = Geo::PackageFileRegistry.group(:package_file_id).having('COUNT(*) > 1').pluck(:package_file_id); package_file_ids.size ``` 2. Output the IDs in case we lose connection later ```ruby puts 'BEGIN Artifact IDs', artifact_ids, 'END Artifact IDs' puts 'BEGIN Upload IDs', upload_ids, 'END Upload IDs' puts 'BEGIN Package File IDs', package_file_ids, 'END Package File IDs' ``` 3. Save the terminal output 4. Delete all duplicates ```ruby Geo::JobArtifactRegistry.where(artifact_id: artifact_ids).delete_all Geo::UploadRegistry.where(file_id: upload_ids).delete_all Geo::PackageFileRegistry.where(package_file_id: package_file_ids).delete_all ``` 5. Wait for background jobs to create the registry rows again and resync etc ### Implementation plan #### MR 1: Resolve known application race conditions that create duplicate rows 1. At some point before calling `registry.start!`, persist the registry if it isn't already. Maybe use `safe_find_or_create_by` [here](https://gitlab.com/gitlab-org/gitlab/-/blob/v17.3.0-ee/ee/app/models/concerns/geo/repository_replicator_strategy.rb#L59) 2. Replace `registry.start!` with `registry.reload.start!` #### MR 2: Document the workaround to manually delete duplicates to make it more easily findable (This can be done parallel with MR 1) 1. Add [this workaround](https://gitlab.com/gitlab-org/gitlab/-/issues/479131#workaround) to [Geo Troubleshooting Common Errors doc](https://docs.gitlab.com/ee/administration/geo/replication/troubleshooting/common.html) Follow up issue: https://gitlab.com/gitlab-org/gitlab/-/issues/519745.