Child pages
  • How to Fix the Duplicate Agile Custom Fields After Upgrading
Skip to end of metadata
Go to start of metadata

This is also applicable in case one of the custom fields used by Agile is accidentally deleted from the Custom Fields administration. Do note that following the below procedure in this scenario will only ensure that Agile works correctly from now onwards. The lost data due to the deletion of the custom field can only be retrieved by rolling back the JIRA instance database to a backup taken before the deletion of the custom field.

Symptoms

After upgrading Agile from the version older than v4.3 to v4.3+, users may find the duplicate Agile custom fields in JIRA.

Cause

While the exact root cause is still unknown it is observed that Agile freshly creates the custom fields after an upgrade. In addition for upgrades from version < 4.3, as shown in Specifying your Project Templates instruction, to support "Scrum" template no matter whether there is the existing field sharing the same name, Agile auto creates four custom fields ("Story Points"/"Flagged"/"Rank"/"Business Value"), which are linked with "Scrum" template by default.

Diagnosis

  1. Open and check whether any of the Agile fields are duplicated. An example snapshot is given below:
  2. It can also be confirmed by a direct SQL query in database. For example to return all Agile Custom Fields, the following query can be used.

    SELECT id,cfname FROM customfield WHERE customfieldtypekey like 'com.pyxis.greenhopper.jira%' OR cfname in ('Flagged','Story Points','Epic/Theme','Business Value') ORDER BY cfname, id;

    In the above example, there will be multiple Custom Fields returned for Epic Name.

Workaround

If you have any duplicated Custom Fields from the above SQL, this workaround is applicable.

Backup

The following changes are irreversible. Please ensure that the JIRA database backup is taken before proceeding to the resolution steps. It is also recommended that JIRA is taken out of service before starting the database backup.

After completing the resolution steps described below, please check the JIRA instance and the Agile configuration including user tests before bringing JIRA online. For e.g.

  • Do all the boards show up correctly
  • Are all the earlier sprints intact
  • Are the Story Points, Epic, Ranking and Business Value are correct in issues by doing a random check
  • Are the Sprint Reports and Burn-down charts intact (both Classic boards and Rapid boards)

Please advise users to stop using Agile before commencing these steps, so as to prevent any incorrect values being added to the duplicated Custom Fields. This could be done by Configuring an Announcement Banner to have a warning in it.

  1. Identify the custom field IDs of the following fields from the JIRA instance with the below SQL.

    SELECT id,cfname FROM customfield WHERE customfieldtypekey like 'com.pyxis.greenhopper.jira%' OR cfname in ('Flagged','Story Points','Epic/Theme','Business Value') ORDER BY cfname, id;
    • Business Value
    • Epic Colour
    • Epic Link
    • Epic Name
    • Epic Status
    • Epic/Theme
    • Flagged
    • Rank
    • Release Version History
    • Sprint
    • Story Points
     Expand for further steps on finding the custom field IDs...

    Go to Administration > Issues > Fields

    • Locate each of the custom fields mentioned above (which is expected to be used by Agile) and click on the gear icon to the right and click 'Configure'
    • This will open the Configuration page of the custom field and the id can be located from the url displayed in the browser. For example if the url is http://servername/secure/admin/ConfigureCustomField!default.jspa?customFieldId=10005 then the id is 10005. Repeat the same for all the fields.
  2. Identify the 'Issue Type' IDs for the below Issue Types used by Agile with the following SQL.

    select id, pname from issuetype where pname in ('Epic','Story','Task');
    • Epic
    • Story
    • Task
     Expand for further steps for finding the Issue Type IDs...

    Go to Administration > Issues > Issue Types

    1. Click on 'Edit' link next to the below of the Issue Types used by Agile and note down the IDs
      1. Story
      2. Epic
    2.  For example if the url is http://servername/secure/admin/EditIssueType!default.jspa?id=6 then the ID is 6. Repeat the same for all the fields.
  3. Identify which fields to use by checking to see which have the correct value. The lowest IDs will be the original fields - we want to check that the duplicated fields are not associated with any issues. This can be done with the following:

     Expand for further steps on checking the data associated with a field...
    1.  In JIRA Issue Navigator run the below JQL (adjust the custom field id as per your instance) and ensure that no results are returned.

      cf[10101] is not empty
    2. The custom field id can be found while typing in the field name as given in the below snapshot.
  4. Take note of the fields that have the duplicated values. For example:
    1. cf[10004] returns 1736 issues. This is the original Story Points field.
    2. cf[10101] returns 196 issues. This is the duplicated Story Points field, and will need to have the data migrated to the original (10004).
  5. Shutdown JIRA.
  6. Before Migrating data from one custom field to another, we must check for issues that may have values in both custom fields.  

    select * from customfieldvalue where customfield=<duplicated custom field ID> and issue in (select issue from customfieldvalue where customfield = <original custom field ID>);

    If the values are the same then we can remove the duplicate values, else it will be up to the user to decide which value to keep.

    delete from customfieldvalue where customfield=<duplicated custom field ID> and issue in (select issue from customfieldvalue where customfield = <original custom field ID>);
  7. Migrate the data from the duplicated custom fields to the original with the below SQL. The ID can be seen within the Lucene search results that are returned.

    update customfieldvalue set customfield = <original custom field ID> where customfield = <duplicated custom field ID>;

    For example, using the data from step 4:

    update customfieldvalue set customfield = 10004 where customfield = 10101;


  8. Run the below SQL in the JIRA database to locate the current configuration data:

    select propertyvalue from propertytext where id = (select id from propertyentry where property_key ='SCRUM_DEFAULT_TEMPLATE_CONFIGURATION');
  9. The returned text will be a long one and we recommend copying the contents from the displayed output to a text editor of your choice.
    Modify the XML output from the previous step (using the text editor) to so that the below entries point to the right custom field ids which we identified in the initial steps.

    XML AttributeRelated Custom FieldSample XML
    gh.issue.storypointsStory Points
    <entry>
      <string>gh.issue.storypoints</string>
      <string>customfield_10900</string>
    </entry>
    gh.issue.rankingfieldRanking (Obsolete) (info) This is from versions of Agile prior to 6.x.

    <entry>
    <string>gh.issue.rankingfield</string>
    <string>customfield_10002</string>
    </entry>

    gh.issue.labelfieldEpic/Theme

    <entry>
    <string>gh.issue.labelfield</string>
    <string>customfield_10001</string>
    </entry>

    gh.issue.businessvaluesBusiness Value

    <entry>
    <string>gh.issue.businessvalues</string>
    <string>customfield_10004</string>
    </entry>

    gh.issue.flaggingfieldFlagging

    <entry>
    <string>gh.issue.flaggingfield</string>
    <string>customfield_10000</string>
    </entry>

    The XML also includes these additional values for the 'Issue Types' used by Agile:

    XML AttributeRelated Issue TypeSample XML
    gh.issue.storyStory

    <entry>
    <string>gh.issue.story</string>
    <string>7</string>
    </entry>

    gh.issue.taskTechnical Task

    <entry>
    <string>gh.issue.task</string>
    <string>8</string>
    </entry>

    gh.issue.epicEpic

    <entry>
    <string>gh.issue.epic</string>
    <string>6</string>
    </entry>

    gh.issue.businessvaluesBusiness Value

    <entry>
    <string>gh.issue.businessvalues</string>
    <string>customfield_10004</string>
    </entry>

  10. Once the XML is prepared, update back the database with the below SQL command: 

    update propertytext set propertyvalue = <copy the contents from the text editor containing the XML here> where id = (select id from propertyentry where property_key ='SCRUM_DEFAULT_TEMPLATE_CONFIGURATION');


  11. Run the below SQL and verify that the custom field IDs (column propertyvalue) are correct for the Agile configuration. The propertyvalue is the customfield.id which can used to identify which Custom Field is the correct one from step 1. 

    SELECT pn.id, entity_name, entity_id, property_key, propertyvalue, cfname FROM propertynumber pn JOIN propertyentry pe ON pe.id = pn.id LEFT OUTER JOIN customfield cf ON pn.propertyvalue = cf.id WHERE property_key like 'GreenHopper.%'

    The values that are of interest to us are the following:

    Property KeyCustom Field
    GreenHopper.Sprint.Default.customfield.id
    Sprint
    GreenHopper.Rank.customfield.idGlobal Rank
    GreenHopper.EpicLink.Default.customfield.id
    Epic Link
    GreenHopper.EpicLabel.Default.customfield.id
    Epic Name
    GreenHopper.EpicStatus.Default.customfield.id
    Epic Status
    GreenHopper.EpicColor.Default.customfield.id
    Epic Colour
    GreenHopper.Rank.Default.customfield.id
    Rank
  12. If the above step shows any incorrect values update it using the below SQL command (replace the <value> as per the configuration entry)

    update propertynumber set propertyvalue=<<the correct value>> where id = <<id from the previous query>> 

    (info) Use the Custom Field IDs from step 1, ensuring that the duplicated data has been migrated as in step 6 (if applicable).

  13. (Optional - Oracle Only): Commit the changes to database by an explicit COMMIT.
  14. Start JIRA.

  15. Reindex JIRA and test to see if you can use Agile without error.
  16. Go to Administration > Issue Fields > Custom Fields and delete the auto-created duplicated fields. These fields will need to be unlocked in the newer Agile versions - this can be done as in our How to unlock a Locked field KB.
  17. Follow the Configure Custom Field instructions and make sure the left "Story Points" field is available for "Story" issue type (if it is Managed, this should already be done).
  18. Follow the Specify Project Template instructions, go to template configuration page, under Card Styles section select 'Story' Issue Type, add 'Story Points' to List View, and set it as "corner" field for Card/Summary View.
  19. Go to Agile > Planning Board, follow Configure Statistics Guide to add the appropriate "Story Points" field for statistics view. What can happen is the duplicated field may be put here instead and an error will appear when browsing to a Rapid Board.
(info) In case you are finding difficulties in any of the steps above, please contact Atlassian Support with a detailed description of the problem. It's likely we'll need to request your data so as to analyse the problem further and provide specific assistance.
Help us improve!