Duplicated entry - unique constraint violated
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Problem
The logs may include stack traces similar to the ones below:
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:GlobalPermissionEntry][group_id,BED-Jira-Access][permission,USE][id,10015] (SQL Exception while executing the following:INSERT INTO globalpermissionentry (ID, PERMISSION, GROUP_ID) VALUES (?, ?, ?) (Duplicate entry '10015' for key 'PRIMARY'))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:214)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:179)Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:GadgetUserPreference][id,60081][userprefvalue,assignees][userprefkey,ystattype][portletconfiguration,13951] (SQL Exception while executing the following:INSERT INTO gadgetuserpreference (ID, PORTLETCONFIGURATION, USERPREFKEY, USERPREFVALUE) VALUES (?, ?, ?, ?) (ORA-00001: unique constraint (JIRAPROD.PK_GADGETUSERPREFERENCE) violated
))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:136)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:101)Caused by: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:PortletConfiguration][position,0][id,14485][portalpage,12305][color,color1][gadgetXml,rest/gadgets/1.0/g/com.atlassian.jira.gadgets:admin-gadget/gadgets/admin-gadget.xml][columnNumber,0] (SQL Exception while executing the following:INSERT INTO portletconfiguration (ID, PORTALPAGE, PORTLET_ID, COLUMN_NUMBER, positionseq, GADGET_XML, COLOR) VALUES (?, ?, ?, ?, ?, ?, ?) (ORA-00001: unique constraint (JIRAPROD.PK_PORTLETCONFIGURATION) violated
2013-08-05 10:46:05,050 http-bio-8080-exec-2838 ERROR [500ErrorPage.jsp] Exception caught in 500 page org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10002][userKey,oarteaga][lowerUserName,oarteaga] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry '10002' for key 1))
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10002][userKey,oarteaga][lowerUserName,oarteaga] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry '10002' for key 1))Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership][id,82263][membershipType,GROUP_USER][lowerParentName,nicr][parentId,80014][childId,42310][childName,lhooipen][lowerChildName,lhooipen][directoryId,10100][parentName,NICR] (SQL Exception while executing the following:INSERT INTO cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ORA-00001: unique constraint (JIRAUSER.PK_CWD_MEMBERSHIP) violated
Diagnosis
It is possible to see in the error message the entity that is throwing the problem, together with its corresponding table e.g.:
org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership]
...
SQL Exception while executing the following:INSERT INTO cwd_membershipIn this example, we can see that the problematic entity is 'Membership', and its corresponding table is 'cwd_membership'. With this, we can run the following queries in the database:
SELECT * FROM sequence_value_item WHERE seq_name = 'Membership';SELECT max(id) FROM cwd_membership;If the value returned in the second query is bigger than the value returned in the first one, please refer to the steps under Resolution to fix this problem. Otherwise, refer to Alternative Resolution for the solution
The names of the tables used are mostly equal to the entity's name. In some occasions though, the name is not exactly the same, so you may need to look for similar names as in the above example. Please take note of the case-sensitivity of the table name in your specific database. Sometimes you may need to use SEQUENCE_VALUE_ITEM instead of sequence_value_item.
May have to use numerical sorting for the SQL query, for example
Causes
- It is not known yet what causes this problem, although the most likely reason is an update attempt was not successfully finished and therefore caused this mismatch in the id counts.
- Also, this can occur after the migration from Jira Cloud to Jira Server/Datacenter.
Resolution
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
- Shutdown your JIRA application;
Run the following query in the database:
UPDATE sequence_value_item SET seq_id = (SELECT max(id)+100 FROM <TableName>) WHERE seq_name = '<EntityName>';Don't forget to commit your changes If you're using Oracle database.
Be sure to replace '<EntityName>' and <TableName> with the ones shown in the error message.
- Restart your JIRA application.
For example, the update statement below would be used to fix the problem in the example above:
UPDATE sequence_value_item SET seq_id = (SELECT max(id)+100 FROM cwd_membership) WHERE seq_name = 'Membership'; Alternative Resolution
If the sequence value is higher than the maximum ID of the table in question (and the failing ID number already exists), try restarting Jira.
In some cases, JIRA will generate the next ID number internally, rather than referring to the database. If this in-memory value is not in line with what's in the database, database insertions will fail.
Restarting JIRA should allow for JIRA to re-read what's in the database and have the correct IDs to generate.
Mapping of sequence name and sequence_value_item
The following is a mapping of sequence_value_item entries and a SQL script to update maximum ID. The SQL script is useful in fixing possible multiple incorrect sequence ID values after a migration. The script has been tested in versions 8.20.11, 9.4.2, and 9.7.0.
We recommend backup the database before applying the script, and always test in non-production first. Some minor adjustments may needed due to each Jira version may use a different set of values.