Duplicate IDs in a Content Type Despite Unique Field Constraint

[Neehakethi]

Parent Issue

No response

Problem Statement

We are experiencing duplicate IDs for a content type in dotCMS, even though the field is marked as unique. Simultaneous double inserts from different pods do not explain this issue, and it occurs even with the indexPolicy=WAIT_FOR setting.

An example scenario :

Occurrence:
A contentlet with ID XXXX has existed since YYYY.
In June, two new contentlets were created with the same ID.
In July, another contentlet with the same ID was created.

Suspected initial cause:
Index policy timing issues.
findings: The issue persists even with the WAIT_FOR policy.
Ideas proposed: Use database-level constraints or a custom script for unique values.

We need to identify why the unique constraint is not working as expected and how we can address this issue. Potential solutions include:

Adding a unique key constraint at the database level.
Implementing a custom script to generate unique values based on configurable fields.

Steps to Reproduce

https://dotcms.zendesk.com/agent/tickets/117183

This was happening specifically with a customer

Acceptance Criteria

We should avoid creating duplicate id's for unique fields in every possible scenario

dotCMS Version

23.10.24 LTS

Proposed Objective

Core Features

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

https://dotcms.zendesk.com/agent/tickets/117183

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

Tasks

Preview Give feedback

1. Create Task Upgrade to create new unique_field table #30275
   QA : Approved QA : Passed Internal Release : 24.10.16 Team : Falcon +4
   
2. Create API/Factory methods to insert in the unique_fields table #30277
   QA : Approved QA : Passed Internal Release : 24.11.11 Team : Falcon +4
3. Use new Database validation for Unique Fields when a Contentlets is saved or updated #30279
   QA : Approved QA : Passed Internal Release : 24.11.11 Team : Falcon Type : Defect +5
4. When the 'uniquePerSite' is set recalculate unique_fields key values #30281
   OKR : Customer Success OKR : Customer Support QA : Approved QA : Passed Internal Release : 24.11.29 Team : Falcon dotCMS : Content Management +7
5. Clean up the unique_fields table #30285
   OKR : Core Features OKR : Customer Support QA : Passed Internal Team : Falcon Type : Defect +5
   
6. Enable Unique Field DataBase validation #30306
   Merged Release : 24.12.05 Team : Falcon +3
7. Populate the unique_fields table when the Database validation is enabled #30815
   QA : Approved QA : Passed Internal Release : 24.12.10 Team : Falcon dotCMS : Content Management +5

[Neehakethi]

Customer now reporting seeing more duplicates: earlier were 2 or 3 which have increased to 27 now

[john-thomas-dotcms]

The proposed "full" solution to this still hasn't been fully agreed on, since proposed solutions may not address all use cases.

So, for a first pass, we're going to just change the existing Elasticsearch query to a DB query.

We will need to verify that this change doesn't cause a performance hit or bottlenecks waiting for the DB query to return. To verify this, we may need to make a copy of the customer system that we can test against.

[john-thomas-dotcms]

Good poin from J. Sanchez: We should enable/disable the DB/ES query functionality via a config param.

[freddyDOTCMS]

Here the POC done to detect what is the best solution #29707

[crak-mess]

Hello, is there any ETA on this fix ?
Thank you very much.

[m27iu4]

https://dotcms.freshdesk.com/a/tickets/28936