Blank page when accessing Automation in Jira Service Management project settings with NonUniqueResultException in the logs

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

Platform Notice: Server, Data Center, and Cloud By Request - This article was written for the Atlassian server and data center platforms but may also be useful for Atlassian Cloud customers. If completing instructions in this article would help you, please contact Atlassian Support and mention it.

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.

*Fisheye および Crucible は除く

問題

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)

診断

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;

原因

Somehow there is duplicate entry for default event user for a project on AO_9B2E3B_PROJECT_USER_CONTEXT data (Root cause is still unknown).

回避策

  • 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:

IDPROJECT_IDSTRATEGYUSER_KEY
310401specificUseradmin
410401specificUseradmin


  • 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;

    データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。


最終更新日 2020 年 11 月 23 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.