Duplicated entry - unique constraint violated

Still need help?

The Atlassian Community is here for you.

Ask the community

This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Functional differences in Atlassian Cloud, the contents of this article cannot be applied to Atlassian Cloud applications.

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_membership

In 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.

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

MySQL query

SELECT max(CAST(id AS SIGNED)) FROM cwd_membership;

PostgreSQL query
SELECT max(id::integer) FROM cwd_membership;

Cause

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.

Resolution

Scenario 1

Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

  1. Shutdown your JIRA application;
  2. Run the following query in the database:

    UPDATE sequence_value_item SET seq_id = (SELECT max(id)+100 FROM <TableName>) WHERE seq_name = '<EntityName>';

     Be sure to replace '<EntityName>' and <TableName> with the ones shown in the error message.

  3. 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'; 

Scenario 2

If you are using an earlier version of JIRA (such as 5.x), and an upgrade to a later version is a viable option, performing the upgrade could fix the problem.

Always try to perform the upgrade on a test instance first



Description If you run into a situation in the logs where the logs throw an error stating: "Duplicated entry - unique constraint violated" this article can be used to resolve the issue
Product Jira
Platform Server
Last modified on Oct 17, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.