Adding Sprint to an issue throws SQL Exception on Edit Issue screen
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
SQL exceptions when adding the sprint, to an issue on the Edit Issue screen.
This can occur after migration from Jira Cloud to Jira Server/Datacenter, or for other reasons.
Environment
Jira 8.20.11, 9.4.0
(Known to occur after migration from Jira Cloud)
Diagnosis
While editing an issue, when a sprint is added to the Sprint field, it throws DataAccessException on the screen and update doesn't persist.
The following errors may appear in the atlassian-jira.log
1
2
3
4
5
6
2022-11-11 10:55:39,565+0000 http-nio-8080-exec-16 ERROR recovery_admin 655x1880x1 hof4jk 172.29.179.133,172.50.0.2 /secure/QuickEditIssue.jspa [c.a.j.bc.issue.DefaultIssueService] Exception occurred editing issue: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:CustomFieldValue][parentkey,null][customfield,10020][issue,11806][stringvalue,13][id,10800][updated,1668164139557] (SQL Exception while executing the following:INSERT INTO public.customfieldvalue (ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "pk_customfieldvalue"
Detail: Key (id)=(10800) already exists.))
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:CustomFieldValue][parentkey,null][customfield,10020][issue,11806][stringvalue,13][id,10800][updated,1668164139557] (SQL Exception while executing the following:INSERT INTO public.customfieldvalue (ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "pk_customfieldvalue"
Detail: Key (id)=(10800) already exists.))
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:299)
at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.createValue(WrappingOfBizDelegator.java:172)If you check the database, you can compare the value of "MAX(id)" in the customfieldvalue table with the "seq_id" of CustomFieldValue from sequence_value_item table :
1
2
SELECT MAX(id) FROM customfieldvalue;
SELECT * FROM sequence_value_item WHERE seq_name = 'CustomFieldValue';ℹ️ The above queries are written for Postgres and may need revision for other database systems.
You should get output similar to the one below
1
2
3
4
5
6
7
8
9
10
11
jira=# SELECT MAX(id) FROM customfieldvalue;
max
-------
13500
(1 row)
jira=# SELECT * FROM sequence_value_item WHERE seq_name = 'CustomFieldValue';
seq_name | seq_id
------------------+--------
CustomFieldValue | 13200
(1 row)As evident from the customfieldvalue table, the MAX(id)is 13500 for this case, whereas the seq_id for seq_name of CustomFieldValue is smaller than that.
Cause
This has been observed in Instances migrated from Jira Cloud to Jira Server:
If the value of
MAX(id)in thecustomfieldvaluetable is higher than theseq_idofCustomFieldValuefromsequence_value_itemtable, it causes this problem.
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Please make a native database backup before modifying the database
Stop Jira
Run the following query to update the value of
seq_idto a higher value1update sequence_value_item set seq_id = 14500 WHERE seq_name = 'CustomFieldValue';Start Jira
Was this helpful?