Automation for Jira rule creates corrupted issues with missing workflow actions
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
Issue creation via Automation for Jira fails as per the Automation for Jira Audit log randomly, but still corrupted issues with missing workflow actions are created.
This issue is a random issue and does not occur always but sometimes when multiple issues are created using Automation For Jira then this issue is observed.
Diagnosis
The below error is found in the atlassian-jira.log when the issue is seen.
1
2
3
4
5
6
7
8
9
10
SdOffThreadEventJobRunner:thread-2 ERROR /servicedesk/customer/portal/1/create/67 [c.a.s.internal.util.SafeRunner] Unable to run event handler onIssueChangedEvent: IssueChangedEventImpl{changeItems=[com.atlassian.jira.issue.history.ChangeItemBean@29e2cbc3[fieldType=jira,field=assignee,from=<null>,fromString=<null>,to=JIRAUSER11226,toString= ,created=], author=Optional, comment=Optional.empty, issue=ITSMSD-29654, eventTime=, sendMail=true, spanningOperation=null}
com.querydsl.core.NonUniqueResultException: Only one result is allowed for fetchOne calls
at com.querydsl.core.support.FetchableQueryBase.uniqueResult(FetchableQueryBase.java:64)
at com.querydsl.sql.ProjectableSQLQuery.fetchOne(ProjectableSQLQuery.java:398)
at com.atlassian.jira.versioning.VersioningDao.lambda$getCurrentVersion$6(VersioningDao.java:152)
at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:68)
at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:116)
at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:67)
at com.atlassian.jira.versioning.VersioningDao.getCurrentVersion(VersioningDao.java:147)
at com.atlassian.jira.versioning.EntityVersioningManagerImpl.lambda$getIssueVersion$3(EntityVersioningManagerImpl.java:122)There are multiple rows with the same issue version and history step with the same time stamp in the table issue_version,OS_HISTORYSTEP_prev,OS_HISTORYSTEP
1
2
3
4
5
6
7
8
9
10
select * from issue_version where issue_id in (SELECT issue_id
FROM issue_version
GROUP BY issue_id
HAVING COUNT(*) > 1);
select * from OS_HISTORYSTEP where id in (SELECT id
FROM OS_HISTORYSTEP GROUP BY id having count(*) > 1);
select * from OS_HISTORYSTEP_prev where id in (SELECT id
FROM OS_HISTORYSTEP_prev GROUP BY id having count(*) > 1);Please note: The query above is valid for PostgreSQL database. For other databases, the schema under which the table exists can be different. Please adapt the query as needed.
Run the below query to validate whether the primary key indexes pk_issue_version,pk_os_historystep_prev, pk_os_historystep are present or not
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
*
FROM
pg_indexes
WHERE
schemaname = 'public'
AND tablename = 'issue_version'
ORDER BY
tablename,
indexname;
SELECT
*
FROM
pg_indexes
WHERE
schemaname = 'public'
AND tablename = 'OS_HISTORYSTEP'
ORDER BY
tablename,
indexname;
SELECT
*
FROM
pg_indexes
WHERE
schemaname = 'public'
AND tablename = 'OS_HISTORYSTEP_prev'
ORDER BY
tablename,
indexname;Cause
The primary key indexes
pk_issue_version
pk_os_historystep_prev
pk_os_historystep
for all the above mentioned tables were not present and due to that multiple duplicate entries were created in the above tables. The reason for which the primary key indexes were missing is not known.
Solution
It is recommended to raise a ticket with Atlassian for help on deleting the duplicates as it involves complex steps.
Take a backup of the database
Shut Down Jira
Run the below queries to delete the duplicate entries
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELETE FROM issue_version T1
USING issue_version T2
WHERE T1.ctid < T2.ctid -- delete the older versions
AND T1.issue_id = T2.issue_id;
DELETE FROM os_historystep T1
USING os_historystep T2
WHERE T1.ctid < T2.ctid
AND T1.id = T2.id;
DELETE FROM OS_HISTORYSTEP_prev T1
USING OS_HISTORYSTEP_prev T2
WHERE T1.ctid < T2.ctid
AND T1.id = T2.id;Then create the missing indexes
1
2
3
CREATE UNIQUE INDEX pk_issue_version ON public.issue_version USING btree (issue_id);
CREATE UNIQUE INDEX pk_os_historystep_prev ON public.OS_HISTORYSTEP_prev USING btree (id);
CREATE UNIQUE INDEX pk_os_historystep ON public.OS_HISTORYSTEP USING btree (id);Restart Jira
Perform Full Reindexing and then everything should work as expected.
Was this helpful?