Upgrade to JIRA 6x fails due to duplicate entry into app_user table
Symptoms
Upgrade to JIRA 6x fails due to duplicate entry into app_user table. The following appears in the atlassian-jira.log:
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 performing any modification to the database. If possible, try your modifications on a test server.
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:
delete 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.
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