Summary

Custom field option deletion becomes extremely sluggish for large instances where customfieldvalue table is massive.
/jira/secure/admin/DeleteCustomField!default.jspa or /secure/admin/EditCustomFieldOptions!remove.jspa calls take way too long to finish on the DB SELECT queries causing proxy timeout.

Example:

# SELECT COUNT(*) FROM customfieldvalue"
   count
-----------
 167999387
(1 row)

# SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VAL UETYPE FROM public.customfieldvalue WHERE CUSTOMFIELD=16616 AND STRINGVALUE='21797' AND VALUETYPE IS NULL"
 id | issue | customfield | updated | parentkey | stringvalue | numbervalue | textvalue | datevalue | valuetype
----+-------+-------------+---------+-----------+-------------+-------------+-----------+-----------+-----------
(0 rows)


real    11m34.465s
user    0m0.007s
sys     0m0.004s

Environment

• 8.5.x with very big customfieldvalue table

Steps to Reproduce

1. Step 1: Create a lot of issues using many "Select List" type custom fields
2. Step 2: Try to delete/modify the customfield options

Expected Results

Query is returned in time and custom field options are deleted/modified

Actual Results

• Access Logs:

  127.0.0.1 625x11238113x5 xxxxxx [08/May/2020:10:52:55 +0200] "GET /jira/secure/admin/EditCustomFieldOptions!remove.jspa?fieldConfigId=19083&atl_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx HTTP/1.1" 200 17399 1670121 "https://jira.example.com/jira/secure/admin/EditCustomFieldOptions!default.jspa?atl_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_lin&fieldConfigSchemeId=19083&fieldConfigId=19083&customFieldId=16616&returnUrl=ConfigureCustomField%21default.jspa%3FcustomFieldId%3D16616" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:76.0) Gecko/20100101 Firefox/76.0" "1hoakcc"
  

• atlassian-jira.log:

  2020-05-08 10:52:55,404+0200 http-nio-8080-exec-1519 url:/jira/secure/ad...ptions!remove.jspa username:xxxxxxxxx url:/jira/secure/ad...tomfieldoption.jsp username:xxxxxx ERROR xxxxxx 623x11234240x3 1hoakcc 1.1.1.1,2.2.2.2 /secure/admin/EditCustomFieldOptions!remove.jspa [webwork.util.ValueStack] query="affectedIssues/size" {[id="affectedIssues" type="8" values=""]} {[id="size" type="8" values=""]}
  ...skipping...
  Caused by: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM public.customfieldvalue WHERE CUSTOMFIELD=? AND STRINGVALUE=? AND VALUETYPE IS NULL (An I/O error occurred while sending to the backend.)
          at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:85)
          at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findByAnd(WrappingOfBizDelegator.java:62)
          at com.atlassian.jira.issue.customfields.persistence.OfBizCustomFieldValuePersister.getIssueIdsWithValue(OfBizCustomFieldValuePersister.java:83)
          at com.atlassian.jira.issue.customfields.impl.CascadingSelectCFType.getIssueIdsWithValue(CascadingSelectCFType.java:128)
          at com.atlassian.jira.web.action.admin.customfields.EditCustomFieldOptions.getAffectedIssues(EditCustomFieldOptions.java:299)
  

• execute command directly on db

# SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VAL UETYPE FROM public.customfieldvalue WHERE CUSTOMFIELD=16616 AND STRINGVALUE='21797' AND VALUETYPE IS NULL"
 id | issue | customfield | updated | parentkey | stringvalue | numbervalue | textvalue | datevalue | valuetype
----+-------+-------------+---------+-----------+-------------+-------------+-----------+-----------+-----------
(0 rows)


real    11m34.465s
user    0m0.007s
sys     0m0.004s

Notes

This is the happy flow with very small customfieldvalue in a test instance:
1. Jira runs this SELECT query to find usage of the custom field option:

2020-05-10 12:34:19,300+0530 http-nio-8080-exec-5 admin 754x861x1 1jdq6ii /secure/admin/EditCustomFieldOptions!remove.jspa 4ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM public.customfieldvalue WHERE CUSTOMFIELD='11300' AND STRINGVALUE='10603' AND VALUETYPE IS NULL"

2. The returned query result populates the confirmation page:

3. Once you hit delete, it iterates through each of those issues with individual SELECT queries followed by the DELETE query to delete the options from each issue keys:

2020-05-10 12:34:19,607+0530 http-nio-8080-exec-5 admin 754x861x1 1jdq6ii /secure/admin/EditCustomFieldOptions!remove.jspa 1ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM public.customfieldvalue WHERE ISSUE='14848' AND CUSTOMFIELD='11300' AND STRINGVALUE='10603'"
2020-05-10 12:34:19,609+0530 http-nio-8080-exec-5 admin 754x861x1 1jdq6ii /secure/admin/EditCustomFieldOptions!remove.jspa 1ms "DELETE FROM public.customfieldvalue WHERE ID='23337'"
--
2020-05-10 12:34:19,613+0530 http-nio-8080-exec-5 admin 754x861x1 1jdq6ii /secure/admin/EditCustomFieldOptions!remove.jspa 1ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM public.customfieldvalue WHERE ISSUE='14849' AND CUSTOMFIELD='11300' AND STRINGVALUE='10603'"
2020-05-10 12:34:19,614+0530 http-nio-8080-exec-5 admin 754x861x1 1jdq6ii /secure/admin/EditCustomFieldOptions!remove.jspa 1ms "DELETE FROM public.customfieldvalue WHERE ID='23652'"
--

etc.

If the first SELECT query takes a very long time, then UI times out even before getting to the confirm delete page and hence becomes very difficult to delete customfield option.

We could potentially bulk update all issues using this value to be deleted before running the delete option call which would reduce the number of times the SELECT query iterations. However, this become cumbersome any ways for large environments.

Workaround

• The workaround would be to add explicit index on the customfield column:

  CREATE INDEX customfieldvalue_customfield_explicit_index on customfieldvalue (customfield);

• Increasing the Reverse Proxy timeout value to let the call finish could be one workaround