Error when creating a status, resolution, issue type or priority

Problem

On an instance running JIRA 6.1.2 or later, where configuration files have been loaded with versions 1.2.1 or earlier of the plugin, an error may seldom occur when trying to create a status, resolution, priority or issue type. This error can happen when these entities are created either manually or with version 1.3.0 or later of the plugin. Therefore, this problem may have remained hidden if no status was created manually and become visible only after updating the plugin to version 1.3.0 or later.

The error message is like:

com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Status][id,10000][sequence,13][description,Status desc][name,StatusName][statuscategory,2][iconurl,/images/icons/statuses/generic.png] (SQL Exception while executing the following:INSERT INTO PUBLIC.issuestatus (ID, SEQUENCE, pname, DESCRIPTION, ICONURL, STATUSCATEGORY) VALUES (?, ?, ?, ?, ?, ?) (Unique constraint violation: SYS_IDX_148 in statement [INSERT INTO PUBLIC.issuestatus (ID, SEQUENCE, pname, DESCRIPTION, ICONURL, STATUSCATEGORY) VALUES (?, ?, ?, ?, ?, ?)]))

Solution

This problem may be caused because versions of the plugin 1.2.1 or earlier did not update correctly the sequence numbers for those entities in table SEQUENCE_VALUE_ITEM. Sequence numbers for these entities were added with JIRA 6.1.2. They are used internally by JIRA to create ids for new entities. If they are not updated properly, JIRA can generate an id which is already being used by an existing entity. It is essentially the same problem as those described in the related articles.

If you get this error, it sometimes disappears after restarting JIRA. In this case, everything is fine and there is no need to fix anything. If the problem persisted, follow these steps:


  1. Verification: First consider that this problem can only appear if you have been loading configurations on JIRA 6.1.2 and later. In this case, the way to make sure that a wrong sequence value is causing the problem is to connect to the database and run these queries (depending on the type of entity affected):

SELECT * FROM ISSUETYPE WHERE CAST(ID AS INT) > (SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME='IssueType')

SELECT * FROM PRIORITY WHERE CAST(ID AS INT)  > (SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME='Priority')

SELECT * FROM RESOLUTION WHERE CAST(ID AS INT) > (SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME='Resolution')

SELECT * FROM ISSUESTATUS WHERE CAST(ID AS INT)  > (SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME='Status')

If any one of these entities returns one or more rows, that means the sequence value for that entity is too low, and it could cause the problem described at the beginning. In this case, proceed to the next step.

     2.  Fix:  If any of the previous queries returned rows, the sequence value for that entity should be corrected. This can be achieved by running the following SQL sentences (depending on the affected entities).

Warning

Before modifying the JIRA database directly, take these precautions:

  1. Stop JIRA
  2. Backup the database
SQL Sentences for fixing sequences
UPDATE SEQUENCE_VALUE_ITEM SET SEQ_ID = (SELECT MAX(CAST(ID AS INT)) FROM ISSUETYPE)+1 WHERE SEQ_NAME = 'IssueType';
UPDATE SEQUENCE_VALUE_ITEM SET SEQ_ID = (SELECT MAX(CAST(ID AS INT)) FROM PRIORITY)+1 WHERE SEQ_NAME = 'Priority';
UPDATE SEQUENCE_VALUE_ITEM SET SEQ_ID = (SELECT MAX(CAST(ID AS INT)) FROM RESOLUTION)+1 WHERE SEQ_NAME = 'Resolution';
UPDATE SEQUENCE_VALUE_ITEM SET SEQ_ID = (SELECT MAX(CAST(ID AS INT)) FROM ISSUESTATUS)+1 WHERE SEQ_NAME = 'Status';

https://jira.atlassian.com/browse/JRA-35857

https://confluence.atlassian.com/display/JIRAKB/Cannot+Insert+Duplicate+Key+in+Object+when+Adding+JIRA+Content