Database migration to SQL Server fails because of duplicate entries
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
Problem
After migrating a JIRA database from MySQL/PostgreSQL/Cloud to Microsoft SQL Server, the import of an XML backup to JIRA fails with the following error:
1
2
3
4
5
6
7
8
9
Error importing data:org.xml.sax.SAXException:
com.atlassian.jira.exception.DataAccessException:
org.ofbiz.core.entity.GenericEntityException:
while inserting: [GenericEntity:Group][groupName,Résumé][createdDate,2016-11-09 10:39:35.0][lowerGroupName,résumé][active,1][directoryId,1][id,25011]
[updatedDate,2016-11-09 10:39:35.0][type,GROUP][local,0]
(SQL Exception while executing the following:
INSERT INTO dbo.cwd_group (ID, group_name, lower_group_name, active, local, created_date, updated_date, description, lower_description, group_type, directory_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
(Cannot insert duplicate key row in object 'dbo.cwd_group' with unique index 'uk_group_name_dir_id'. The duplicate key value is (résumé, 1).))There might also be other variations of this error if the import succeeds, but the Integration Checker fails anyway:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Referer URL: http://jiratest/secure/admin/IntegrityChecker!default.jspa
com.atlassian.jira.appconsistency.integrity.exception.IntegrityException: Error occurred while performing check.
com.atlassian.jira.appconsistency.integrity.exception.IntegrityException: Error occurred while performing check.
at com.atlassian.jira.appconsistency.integrity.check.WorkflowCurrentStepCheck.doCheck(WorkflowCurrentStepCheck.java:167)
at com.atlassian.jira.appconsistency.integrity.check.WorkflowCurrentStepCheck.preview(WorkflowCurrentStepCheck.java:54)
at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.preview(IntegrityChecker.java:63)
at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.previewWithIds(IntegrityChecker.java:35)
at com.atlassian.jira.web.action.admin.IntegrityChecker.doExecute(IntegrityChecker.java:71)
...
Caused by: com.atlassian.cache.CacheException: java.lang.IllegalStateException: There are more than one workflows associated with 'Purchases' in the database!
at com.atlassian.cache.memory.DelegatingCache$DelegatingLoadingCache.get(DelegatingCache.java:270)
at com.atlassian.jira.workflow.CachingWorkflowDescriptorStore.getWorkflow(CachingWorkflowDescriptorStore.java:68)
at com.atlassian.jira.workflow.JiraWorkflowFactory.getWorkflow(JiraWorkflowFactory.java:37)
at com.opensymphony.workflow.config.DefaultConfiguration.getWorkflow(DefaultConfiguration.java:89)
at com.atlassian.jira.workflow.OSWorkflowManager.getWorkflow(OSWorkflowManager.java:202)
at com.atlassian.jira.workflow.OSWorkflowManager.getWorkflow(OSWorkflowManager.java:548)
at com.atlassian.jira.appconsistency.integrity.check.WorkflowCurrentStepCheck.doCheck(WorkflowCurrentStepCheck.java:111)
... 188 more
Caused by: java.lang.IllegalStateException: There are more than one workflows associated with 'Purchases' in the database!
at com.atlassian.jira.workflow.OfBizWorkflowDescriptorStore.getWorkflowDescriptorGV(OfBizWorkflowDescriptorStore.java:155)
at com.atlassian.jira.workflow.OfBizWorkflowDescriptorStore.getWorkflow(OfBizWorkflowDescriptorStore.java:46)
at com.atlassian.jira.workflow.CachingWorkflowDescriptorStore$WorkflowCacheLoader.load(CachingWorkflowDescriptorStore.java:139)
at com.atlassian.jira.workflow.CachingWorkflowDescriptorStore$WorkflowCacheLoader.load(CachingWorkflowDescriptorStore.java:132)
...Cause
This problem occurs, because SQL Server uses case-insensitive and accent-insensitive collations, like SQL_Latin1_General_CP437_CI_AI,or Latin1_General_CI_AI (for details, see Connecting JIRA applications to SQL Server 2019
To illustrate this with an example, the values shown below will be treated as identical by SQL Server, although they're unique for other supported databases:
Case-insensitive | Resume | resume |
|---|---|---|
Accent-insensitive | resume | résumé |
During the import of your XML backup, both values are being inserted into a column that has unique constraints checks enabled, which results in a violation error.
Resolution
There's no automated solution for this problem. You will need to check the database you're migrating from, and update all the values that are considered identical by changing them to something unique (e.g. changing resume to resumed).
Once all the values are fixed, you can make another XML backup and import it to JIRA.
Was this helpful?