Blank page when accessing Automation in Jira Service Management project settings with NonUniqueResultException in the logs
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
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
1
2
3
4
5
6
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:
1 2 3 42018-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):
1select "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:
1select * 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):
1delete 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?