Blank page when accessing Automation in Jira Service Management project settings with NonUniqueResultException in the logs
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
When accessing Jira Service Management project's Automation administration page, a blank page is seen without any automation rules displayed and no link to add new rule. The following appears in atlassian-jira.log file
2018-09-14 10:57:09,329 http-nio-8080-exec-111 ERROR username 657x39068919x3 1oyd4gf xx.xx.xxxx.xx,xx.xxx.xxx.xx /rest/servicedesk/automation/1/pages/settings/automation/PROJ [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Only one result is allowed for fetchOne calls
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:373)
at com.atlassian.servicedesk.plugins.automation.internal.configuration.project.dao.querydsl.ProjectUserContextDaoImpl.lambda$getContextConfigForProject$1(ProjectUserContextDaoImpl.java:78)
at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:68)Diagnosis
Diagnostic Steps
Enable debug logs for AO tables following steps in https://confluence.atlassian.com/jirakb/how-to-enable-detailed-sql-logging-in-jira-665224933.html
Debug logs will show the following SQL query occurring just before the stacktrace above:
2018-09-14 17:25:43,540 http-nio-8080-exec-112 DEBUG username 1045x39454201x3 h2e903 xx.xx.xxxx.xx,xx.xxx.xxx.xx /rest/servicedesk/automation/1/pages/settings/automation/PROJ [c.querydsl.sql.AbstractSQLQuery] select "AO_9B2E3B_PROJECT_USER_CONTEXT"."ID", "AO_9B2E3B_PROJECT_USER_CONTEXT"."PROJECT_ID", "AO_9B2E3B_PROJECT_USER_CONTEXT"."STRATEGY", "AO_9B2E3B_PROJECT_USER_CONTEXT"."USER_KEY" from "public"."AO_9B2E3B_PROJECT_USER_CONTEXT" "AO_9B2E3B_PROJECT_USER_CONTEXT" where "AO_9B2E3B_PROJECT_USER_CONTEXT"."PROJECT_ID" = ? limit ? 2018-09-14 17:25:43,543 http-nio-8080-exec-112 ERROR username 1045x39454201x3 h2e903 xx.xx.xxxx.xx,xx.xxx.xxx.xx /rest/servicedesk/automation/1/pages/settings/automation/PROJ [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Only one result is allowed for fetchOne calls com.querydsl.core.NonUniqueResultException: Only one result is allowed for fetchOne calls at com.querydsl.core.support.FetchableQueryBase.uniqueResult(FetchableQueryBase.java:64)You can find all affected project IDs with the following SQL query (written for postgres please adjust SQL syntax accordingly for other databases):
select "PROJECT_ID",count(*) as count from "AO_9B2E3B_PROJECT_USER_CONTEXT" group by "PROJECT_ID" having count(*) > 1;
Cause
Somehow there is duplicate entry for default event user for a project on AO_9B2E3B_PROJECT_USER_CONTEXT data (Root cause is still unknown).
Workaround
Find the duplicates for an affected project id with the following SQL query run against the Jira database:
select * from "AO_9B2E3B_PROJECT_USER_CONTEXT" where "PROJECT_ID" = (select id from project where pkey = '<PROJECT_KEY>');remember to replace <PROJECT_KEY> with actual project key of affected project.
Sample Results:
ID | PROJECT_ID | STRATEGY | USER_KEY |
|---|---|---|---|
3 | 10401 | specificUser | admin |
4 | 10401 | specificUser | admin |
Delete the duplicate records from AO_9B2E3B_PROJECT_USER_CONTEXT (following example above the record with ID 4 is a duplicate of ID 3, so we delete 4 - in order cases where you find multiple duplicates you need to delete all save the first one):
delete from "AO_9B2E3B_PROJECT_USER_CONTEXT" where ID = 4;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.
Was this helpful?