Removing duplicate project associations from an Issue Type Scheme
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
Summary
You may encounter a situation where a single Issue Type Scheme is associated multiple times with the same project. Currently, this duplication appears to have no other known impact beyond being visually indicated and resulting in an additional record in the configurationcontext database table.
Diagnosis
Option #1:
You see the same project twice when hovering over the projects associated with the Issue Type Scheme.
Option #2:
You see duplicate projects associated in the configuration context table in the database.
Run the database query below to identify duplicate projects linked to a specific issue type scheme. Replace 'Your Issue Type Scheme' with the name of your scheme:
1
2
3
4
5
6
7
8
9
10
11
SELECT
configurationcontext.ID AS CONFCONTEXT_ID,
configurationcontext.PROJECT AS PROJECT_ID,
project.pname AS PROJECT_NAME,
project.pkey AS PROJECT_KEY,
fieldconfigscheme.configname AS ISSUE_TYPE_SCHEME,
project.LEAD AS PROJECT_LEAD
FROM configurationcontext
INNER JOIN fieldconfigscheme ON configurationcontext.FIELDCONFIGSCHEME = fieldconfigscheme.ID
INNER JOIN project ON configurationcontext.PROJECT = project.ID
WHERE fieldconfigscheme.configname='Your Issue Type Scheme';An example of duplicate entries:
1
2
3
4
CONFCONTEXT_ID,PROJECT_ID, PROJECT_NAME, PROJECT_KEY, ISSUE_TYPE_SCHEME, PROJECT_LEAD
'10300','19406', 'OK project', 'OP', 'My Issue Type Scheme', 'projectlead'
'10301','25217', 'PROJ showing duplicate', 'PSD', 'Your Issue Type Scheme', 'projectlead'
'10316','25217', 'PROJ showing duplicate', 'PSD', 'Your Issue Type Scheme', 'projectlead'Run the database query below to find all duplicated associations between projects and Issue Type Schemes:
1
2
3
4
5
6
7
SELECT
fieldconfigscheme.configname, count(project.pname), project.pname
FROM configurationcontext
INNER JOIN fieldconfigscheme ON configurationcontext.FIELDCONFIGSCHEME = fieldconfigscheme.ID
INNER JOIN project ON configurationcontext.PROJECT = project.ID where fieldconfigscheme.fieldid='issuetype'
group by fieldconfigscheme.configname, project.pname having count(project.pname)>1
order by fieldconfigscheme.configname,pname;Cause
The cause is unknown, but it could be caused by database manipulation or data corruption. Jira does not enforce a 1:1 relation between Project and Issue Type Schemes.
Solution
1. Find the affected Issue Type Scheme and copy it into a temporary new Issue Type Scheme. A copy is the safest way since it will have all the Issue Types and will not require any issue migration.
2. Modify the project, which was showing twice before, to be associated with the temporary Issue Type Scheme.
3. Modify the project once more back to the original Issue Type Scheme.
4. Delete the temporary Issue Type Scheme.
Alternate Solution (if UI is not available)
Alternatively, it is possible to manually edit the database to remove one of the duplicate entries in the configurationcontexttable.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Using the result of the query provided early, find the configurationcontext.ID of the duplicate entries.
Using the example from earlier, we delete either ID 10301 or 10316.
1
delete from configurationcontext where id ='10316'If you're using Oracle, don't forget to commit.
Was this helpful?