Cannot insert duplicate key in object when adding content to a Jira application
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
Symptoms
When adding a new version, comment, issue, custom field, or other JIRA application entity, an error occurs like:
1
2
3
4
5
6
7
8
9
10
2009-08-30 00:28:44,749 pool-97-thread-2 ERROR [jira.action.admin.OfbizImportHandler] Exception importing entity: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Version][id,11201][project,10121][sequence,1][description,5.3.1][name,5.3.1] (SQL Exception while executing the following:INSERT INTO jiraschema1.projectversion (ID, PROJECT, vname, DESCRIPTION, SEQUENCE, RELEASED, ARCHIVED, URL, RELEASEDATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) (Violation of PRIMARY KEY constraint 'PK_projectversion'. Cannot insert duplicate key in object 'jiraschema1.projectversion'.))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:123)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:88)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:63)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:480)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:460)
at org.ofbiz.core.entity.GenericValue.create(GenericValue.java:77)
at com.atlassian.jira.action.admin.OfbizImportHandler$1.run(OfbizImportHandler.java:235)
at com.atlassian.jira.util.concurrent.BoundedExecutor$1.run(BoundedExecutor.java:39)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1061)
Cause
Entries were inserted manually into the Jira application database instead of via the application user interface.
Jira's database is a cluster with a loadbalancer that doesn't honor session affinity or doesn't replicate in real-time where queries from the same user session land on different database server nodes with different content.
Resolution
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.
Find the specific table that is having issues with data insertion and find the highest id in that table:
In the above example, the error was "SQL Exception while executing the following:INSERT INTO jiraschema1.projectversion..." which signifies that this is an issue with a row being inserted into the projectversion table
1SELECT MAX(id) FROM projectversion;
Find the appropriate entry in the sequence_value_item table and take note of the id
In the above example, the specific row is where seq_name = Version. For tables other than projectversion the seq_name will be different. You can find the correct corresponding value by checking the database table name defined in
WEB-INF/classes/entitydefs/entitymodel.xml1SELECT * FROM sequence_value_item WHERE seq_name = 'Version';
Verify that the id value from the sequence_value_item table is higherthanor closer to the max(id) value in the projectversion table
If it is not; Update the sequence_value_item table and set the relevant row's(in this case 'Version') 'seq_id' value to a value greater than the actual maximum ID in the table you're getting errors for.
Restart your JIRA application after this update for it to take effect.
For the second cause , if Jira database is behind a loadbalancer for redundancy/high availability make sure that requests routing and session affinity is well configured and the user's requests land on the same node.
ℹ️ Direct data manipulation is highly discouraged, in part because it is easy to cause errors. A better route is to use JIRA application Remote API to insert data.
Was this helpful?