Upgrade to JIRA 6x fails due to duplicate entry into app_user table
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
Upgrade to JIRA 6x fails due to duplicate entry into app_user table. The following appears in the atlassian-jira.log:
1
2
3
4
5
6
7
8
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10300][userKey,User01][lowerUserName,user01] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry 'User01' for key 'uk_user_key'))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:136)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:101)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:64)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:487)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:467)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:374)
... 29 more
Cause
In JIRA 6x was introduced the ability of renaming users, the table app_user was created to store information related to user name changes. In JIRA versions previous to 5.2.8 there is a bug when deleting an User Directory, some information from the deleted User Directory are not deleted from the database, which causes this issue when upgrading to JIRA 6x.
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.
Delete the orphan entries into cwd_user_attributes and cwd_user tables that belong to the old directory that was deleted and were not remove from the database:
Shutdown JIRA;
Execute the following queries:
1 2delete from cwd_user_attributes where directory_id not in (select id from cwd_directory); delete from cwd_user where directory_id not in (select id from cwd_directory);Restart JIRA.
Sometimes (especially when using Microsoft SQL Server or MySQL) a trailing space in a user name will cause the same type of error during upgrade.
1
2
3
4
5
6
7
8
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ApplicationUser][id,10300][userKey,User01][lowerUserName,user01] (SQL Exception while executing the following:INSERT INTO app_user (ID, user_key, lower_user_name) VALUES (?, ?, ?) (Duplicate entry 'User01 ' for key 'uk_user_key'))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:136)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:101)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:64)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:487)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:467)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:374)
... 29 moreIn this case you'll need to remove the trailing white space from that user's name or check if there's another user name with the same name (but without a trailing space). You can achieve this a few different ways.
Unzip a backup XML and replace all instances of 'User01 ' with 'User01' in the entities.xml file
Was this helpful?