Duplicate MLCS option causing Out of Memory Error (Data Center)

This issue has been resolved in newer versions (v9.0.1 or above for Jira 11.x, v8.1.4 or above for Jira 10.x, v7.1.1 or above in Jira 9).

Problem Overview

Jira can create duplicate entries of the selected options for a Multi-Level Cascading Select custom field within an issue. These duplicates can lead to recursive cycles that cause an Out of Memory (OOM) error during operations such as a full reindex.

[c.a.jira.index.AccumulatingResultBuilder] java.util.concurrent.ExecutionException: java.lang.OutOfMemoryError: Requested array size exceeds VM limit

[c.a.jira.index.AccumulatingResultBuilder] java.util.concurrent.ExecutionException: java.lang.OutOfMemoryError: Java heap space

The reason for the duplication of options is not yet fully understood. This affects older MLCS versions (v9.0.0 or older for Jira 11.x, v8.1.3 or older for Jira 10.x, v7.1.0 or older in Jira 9).

Root Cause

The cause is the presence of duplicate options IDs in the customfieldvalue table of the Jira database.

Recommended action

Update the plugin to the latest versions (v9.0.1 or above for Jira 11.x, v8.1.4 or above for Jira 10.x, v7.1.1 or above in Jira 9) to avoid memory exhaustion.

New versions behaviour:

The cycle is detected and logged as a warning. However, it no longer causes a blocking error: the reindex continues normally.

Warning: The new version does not remove the duplicates from the database. Corrupted data remains present and will eventually (but not necessarily) need to be corrected manually.

How to delete the duplicates

Once you have updated the Multi-Level Cascading Select plugin to the latest version, if you have duplicate IDs, the issue (issue key) involved will be shown in the logs as a warning:

[c.s.j.p.customfield.type.MultiLevelCascadingSelectCFType] Cycle detected in multilevel cascading select for issue '__________-____': optionId _____ already visited. Stopping.

After identifying the custom field and the issue keys involved, the following SQL query can be executed directly on the Jira database using any standard SQL client compatible with the underlying database engine.

⚠️ Note: replace the value of Customfield ID with the ID of the custom field involved, and the Issue key in the sub-query with those reported in the warn logs.

SELECT 
    cfo.id,
    cfo.customfield,
    cfo.parentoptionid,
    cfo.sequence,
    cfo.customvalue,
    cfo.disabled,
    cfv.updated,
    cfv.issue AS issue_id
FROM customfieldoption cfo
JOIN customfieldvalue cfv ON cfv.stringvalue = cfo.id
WHERE cfo.customfield = 10000 -- Customfield ID
AND cfv.issue = (
    SELECT id
    FROM jiraissue
    WHERE pkey IN ('__________-____', '__________-____', '__________-____', '__________-____', '__________-____', '__________-____') -- Issue key
)
ORDER BY cfo.parentoptionid NULLS FIRST, cfo.sequence;

Conclusion

The table below summarises the recommended steps to resolve the issue. Rows highlighted in red indicate actions that are required, while rows highlighted in blue represent actions that are suggested but not strictly necessary.

Action	Description

Action	Description
Update the plugin	Upgrade to the new version of the plugin to prevent the `OutOfMemory` issue from causing blocking errors and have the log with the `issuekey` of the issue involved.
Identify the issue key	Check the Jira logs for the cycle detection warning. The issue keys involved are listed in the log message.
Run the diagnostic query	Once the issue keys are identified, run the SQL query above to identify the issues and options involved.
Manual correction	If necessary, intervene directly on the database to remove duplicate options.