問題

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:

• 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 コマンドの変更、挿入、更新、または削除を行うようにします。