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

No response

Tasks

Beta Give feedback

No tasks being tracked yet.

Tasks

Beta 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
   Team : Falcon +1
   
3. Use new Database validation for Unique Fields when a Contentlets is saved or updated #30279
   Team : Falcon Type : Defect +2
   
4. When the 'uniquePerSite' is set recalculate unique_fields key values #30281
   Team : Falcon +1
5. Clean up the unique_fields table #30285
   Team : Falcon +1
6. Enabled Unique Field DataBase validation #30306
   Team : Falcon +1

[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