500 errors when trying to open the Email Requests page from the Jira Applications menu

お困りですか?

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

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

問題

When navigating to the page ⚙ > Applications > Jira Service Management > Email Requests, a 500 error is displayed in the UI with the message "Sorry we had some technical problems during your last operation".

The following error can be found in the logs:

2018-10-19 07:19:13,401 http-nio-8080-exec-16 ERROR [o.a.c.c.C.[.[localhost].[/].[action]] Servlet.service() for servlet [action] in context with path [] threw exception [com.google.template.soy.tofu.SoyTofuException: In 'foreach' command {foreach $entry in $emailSetting.entries}<tr class="sd-email-setting" data-id="{$entry.emailSettingsId |escapeHtml}" data-project-id="{$entry.projectId |escapeHtml}" data-email="{$entry.email |escapeHtml}" data-channel-key="{$entry.channelKey |escapeHtml}">{if isFirst($entry) and length($emailSetting.entries) > 1}<th headers="sd-col" rowspan="{length($emailSetting.entries) |escapeHtml}" class="font-normal">{$emailSetting.serviceDeskName |escapeHtml}</th>{elseif length($emailSetting.entries) == 1}<td headers="sd-col">{$emailSetting.serviceDeskName |escapeHtml}</td>{/if}<td headers="sd-address-col">{$entry.email |escapeHtml}</td><td headers="sd-request-type-col">{if $entry.requestName}<div>{$entry.requestName |escapeHtml}</div>{else}<div class="sd-status-bad">{getText('sd.admin.email.invalid.request') |escapeHtml}</div>{/if}</td><td headers="sd-status-col" class="js-test-server-status">{if $entry.lastSentRaw == 0}<div class="sd-status-bad">{$entry.lastSentMessage |escapeHtml}</div>{else}<div>{$entry.lastSentMessage |escapeHtml}</div>{/if}</td><td headers="sd-test-server-col"><div class="js-action-buttons"><button class="aui-button aui-button-compact js-test-server">{getText('sd.admin.email.table.column.test') |escapeHtml}</button><button class="aui-button aui-button-compact aui-button-link js-view-log">{getText('sd.admin.email.table.column.log') |escapeHtml}</button><button class="js-tooltip aui-button aui-button-compact aui-button-link js-delete-settings" title="{getText('sd.admin.email.confirm.delete.tipsy') |escapeHtml}">{getText('sd.admin.email.table.column.delete') |escapeHtml}</button><span class="cv-sd-status js-status"></span></div></td></tr>{/foreach}, the data reference does not resolve to a SoyList (encountered type com.google.template.soy.data.restricted.UndefinedData).] 

The following error is thrown in the UI:

com.google.template.soy.tofu.SoyTofuException: In 'foreach' command {foreach $entry in $emailSetting.entries}<tr class="sd-email-setting" data-id="{$entry.emailSettingsId |escapeHtml}" data-project-id="{$entry.projectId |escapeHtml}" data-email="{$entry.email |escapeHtml}" data-channel-key="{$entry.channelKey |escapeHtml}">{if isFirst($entry) and length($emailSetting.entries) > 1}<th headers="sd-col" rowspan="{length($emailSetting.entries) |escapeHtml}" class="font-normal">{$emailSetting.serviceDeskName |escapeHtml}</th>{elseif length($emailSetting.entries) == 1}<td headers="sd-col">{$emailSetting.serviceDeskName |escapeHtml}</td>{/if}<td headers="sd-address-col">{$entry.email |escapeHtml}</td><td headers="sd-request-type-col">{if $entry.requestName}<div>{$entry.requestName |escapeHtml}</div>{else}<div class="sd-status-bad">{getText('sd.admin.email.invalid.request') |escapeHtml}</div>{/if}</td><td headers="sd-status-col" class="js-test-server-status">{if $entry.lastSentRaw == 0}<div class="sd-status-bad">{$entry.lastSentMessage |escapeHtml}</div>{else}<div>{$entry.lastSentMessage |escapeHtml}</div>{/if}</td><td headers="sd-test-server-col"><div class="js-action-buttons"><button class="aui-button aui-button-compact js-test-server">{getText('sd.admin.email.table.column.test') |escapeHtml}</button><button class="aui-button aui-button-compact aui-button-link js-view-log">{getText('sd.admin.email.table.column.log') |escapeHtml}</button><button class="js-tooltip aui-button aui-button-compact aui-button-link js-delete-settings" title="{getText('sd.admin.email.confirm.delete.tipsy') |escapeHtml}">{getText('sd.admin.email.table.column.delete') |escapeHtml}</button><span class="cv-sd-status js-status"></span></div></td></tr>{/foreach}, the data reference does not resolve to a SoyList (encountered type com.google.template.soy.data.restricted.UndefinedData).

期待される挙動

The Email Requests configuration page should load properly:

原因

This issue occurs because of some inconsistency in the database: some Service Management mail handlers are associated to a project that no longer exists, and triggers the 500 error when trying to open the Email Requests page. The exact root cause of this data inconsistency has not been determined yet.

診断ステップ

To check if you are impacted by this issue and if the resolution steps apply to you, run the following queries:

  • Query 1

    select * from "AO_2C4E5C_MAILCHANNEL";
  • Query 2

    select * from project where id in (select "PROJECT_ID" from "AO_2C4E5C_MAILCHANNEL");

The 1st query should return at least one row. Each row corresponds to one mail handler:

  • the ID column corresponds to the id of the mail handler
  • the PROJECT_ID column corresponds to the id of the project that this mail handler is associated with

Please find below an example of result form the 1st query:

CREATED_BY | CREATED_TIMESTAMP | ENABLED | ID |  MAIL_CHANNEL_KEY   | MAIL_CONNECTION_ID | MAX_RETRY_ON_FAILURE | MODIFIED_BY | PROJECT_ID | UPDATED_TIMESTAMP 

------------+-------------------+---------+----+---------------------+--------------------+----------------------+-------------+------------+-------------------

 julien     |     1542372618305 | t       |  1 | CHANNELf0887e5ff769 |                  1 |                    0 |             
|      10000 |     1547030782416
 julien     |     1542372618305 | t       |  2 | CHANNELf0887e5ff768 |                  2 |                    0 |             
|      10001 |     1547030782416

We can see in that example that:

  • the Mail Handler with ID 1 is associated to the PROJECT_ID 10000
  • the Mail Handler with ID 2 is associated to the PROJECT_ID 10001

Take a look at the result from the 2nd query: if a row is missing for any of the PROJECT_ID found in the 1st query, then the resolution steps can be applied to your situation.

Below is an example of result from the 2nd query:

id   |  pname  | url |  lead  | description |  pkey  | pcounter | assigneetype | avatar | originalkey | projecttype  

-------+---------+-----+--------+-------------+--------+----------+--------------+--------+-------------+--------------

 10000 | SD_TEST |     | julien |             | SDTEST |       54 |            3 |  10324 | SDTEST      | service_desk

We can see in this example that there is a project with ID 10000 (which corresponds to the PROJECT_ID column from the 1st query), but that there is no project with ID 10001. In this case, the mail handler that is causing the 500 error is the mail handler which is associated to the PROJECT_ID 10001, because this project no longer exists in the Jira database.

Therefore in this example, the mail handler that needs to be removed from the database is the mail handler with ID 2 (according to the ID column from the 1st query).

ソリューション

The resolution consists in deleting in the Jira Database any trace of the mail handler which is associated to the project which no longer exists in the database.

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

Here are the resolution steps:

  • Identify the ID(s) of the mail handler(s) which need to be deleted, based on the Diagnosis steps
  • Backup the Jira Database
  • Jira を停止します。
  • Run the following queries in the database to delete any trace of the invalid mail handlers (make sure to respect the order of the queries below). Note that this query was written for a PostgreSQL database, and might differ for other types of database. 

    DELETE from "AO_2C4E5C_MAILHANDLER" where "MAIL_CHANNEL_ID" in (<ID_LIST>);
    DELETE from "AO_2C4E5C_MAILCHANNEL" where "MAIL_CONNECTION_ID" in (<ID_LIST>);
    DELETE from "AO_2C4E5C_MAILCONNECTION" where "ID" in (<ID_LIST>);
    DELETE from "AO_54307E_EMAILCHANNELSETTING" where "ID" in (<ID_LIST>);

    (i) Replace <ID_LIST>  with the list of IDs found during the diagnosis steps

  • Jira の起動

If the problem persists, or if the query from the diagnosis step did not return any row, please reach out to Atlassian Support via https://support.atlassian.com/ja/.


最終更新日 2020 年 11 月 23 日

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

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