Duplicate value in "Time in Status" Field persists despite fix code in place

Summary

Duplicate values are created in the Time in Status field. The steps to trigger the Duplication in the Time in Status Field have not yet been fully identified and cannot be reliably reproduced, however where the BUG is occuring is that we already have Code in the system to remove the Duplication that is failing to clear out the duplicate by pick a winning value based on the most recent value input once the duplicate value is identified:

com.atlassian.jira.issue.customfields.impl.AbstractSingleFieldType#getValueFromIssue(com.atlassian.jira.issue.fields.CustomField, java.lang.Long, java.lang.String)

  /**
     * Retrieve the current value of the customfield in the DB based on issue id.
     * <p>
     * Could be used directly in sub-classes when we don't have the full issue object.
     *
     * @param field    the customfield
     * @param issueId  the id of the issue
     * @param issueKey the issue key. optional, only used for logging purpose
     * @return the value object
     */
    protected T getValueFromIssue(@Nonnull CustomField field, @Nullable Long issueId, @Nullable String issueKey) {
        final List<Object> values = customFieldValuePersister.getValues(field, issueId, getDatabaseType());

        final Object databaseValue;
        if (values.isEmpty()) {
            return null;
        } else if (values.size() > 1) {
            // The data is corrupt - presumably because of concurrent update bug in customFieldValuePersister
            // Best we can do is pick one value as the winner.
            //
            // JSDS-1007 : Changing this to keep the Last value (not first) as CustomFieldValuePersister will return by ID ASC
            databaseValue = getLast(values);
            log.warn("More than one value stored for custom field id '" + field.getId() + "' for issue '" + issueKey +
                    "'. Keeping '" + databaseValue + "' (the newest value) and deleting other values" +
                    ". Original values:" + values);
            customFieldValuePersister.updateValues(field, issueId, getDatabaseType(), singletonList(databaseValue));
        } else {
            databaseValue = values.get(0);
        }

Steps to Reproduce

1. Action to cause Duplicate Unknown
2. Once Duplication occurs trigger a workflow transition on the affected issue

Expected Results

When Duplicate is detected it is deleted and correct value applied to time in status

Actual Results

The follow 3 error exceptions are thrown in the atlassian-jira.log file and the Duplicate value is not removed and time in status is not updated with new value:

automation-event-serializer:thread-2 ERROR testuser 1247x646x1 bszk9o <IP_Address> /secure/WorkflowUIDispatcher.jspa [c.a.j.e.charting.field.TimeInStatusCFType] More than one value stored for custom field id'customfield_#####'.  Values [XXX, XXX]

automation-event-serializer:thread-2 ERROR testuser 1247x646x1 bszk9o <IP_Address> /secure/WorkflowUIDispatcher.jspa [c.a.j.e.charting.field.TimeInStatusCFType] More than one value stored for custom field id'customfield_#####'.  Values [XXX, XXX]

http-nio-8080-exec-15 ERROR testuser 1277x1156x1 bszk9o <IP_Address> /secure/WorkflowUIDispatcher.jspa [c.a.j.e.charting.field.TimeInStatusCFType] More than one value stored for custom field id'customfield_#####'.  Values [XXX, XXX]

Note

In Jira 7.5+ customfieldvalue has extra column updated which can be used to resolve the conflicts:

\d customfieldvalue
          Table "public.customfieldvalue"
   Column    |           Type           | Modifiers
-------------+--------------------------+-----------
 id          | numeric(18,0)            | not null
 issue       | numeric(18,0)            |
 customfield | numeric(18,0)            |
 parentkey   | character varying(255)   |
 stringvalue | character varying(255)   |
 numbervalue | double precision         |
 textvalue   | text                     |
 datevalue   | timestamp with time zone |
 valuetype   | character varying(255)   |
 updated     | numeric(18,0)            |

Workaround

This Will Automatically Clear out over time, But Performance is impacted while duplicates exist

The Following can be used to manually clear the duplicate, Adapted From previous BUG with similar behavior on a different Field:

• https://jira.atlassian.com/browse/JRASERVER-12796

  We recommend taking a full database backup before making these direct modifications to Jira's database. This will ensure that you can perform a restore to a known good state should anything go awry.
  We encourage you to test these statements in your lower environment prior to the production changes.
  Ensure Jira is fully down when applying the Delete statements below.

• Identify issues Using the ID of the Time in Status Field value (Can be seen in the error) with the following query:

  select issue from customfieldvalue where customfield = <INSERT_ID_HERE> group by issue having count(issue) > 1 order by issue;
  

• You will then need to look at each of the issue individually with:

  select * from customfieldvalue where customfield = <INSERT_ID_HERE> and issue = <value_of_ISSUE from query above>;
  

• Delete the duplicate value with the lower value in the ID Column EXE in the following query for issue_ID 89857 returns:

  select * from customfieldvalue where customfield = 10709 and issue = 89857;
     id   | issue | customfield | parentkey | stringvalue | numbervalue |                  textvalue                  | datevalue | valuetype 
  --------+-------+-------------+-----------+-------------+-------------+---------------------------------------------+-----------+-----------
   747497 | 89857 |       10709 |           |             |             | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |           | 
   747496 | 89857 |       10709 |           |             |             | xxxx                                        |           | 
  (2 rows)
  

• removed the lower value id = 747496;

  delete from customfieldvalue where customfield = 10709 and id = 747496;