500 Error Accessing Email Requests in JSM Data Center
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
When navigating to the ⚙ > 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".
Identifying the Cause of Jira 500 Errors
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).This issue occurs because of an inconsistency in the database: some Service Management mail handlers are associated with a project that no longer exists, triggering 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:
クエリ 1
select * from "AO_2C4E5C_MAILCHANNEL";クエリ 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 | 1547030782416We 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_deskWe 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).
Resolution steps
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>);Replace <ID_LIST> with the list of IDs found during the diagnosis steps
Jira を起動します。
問題が引き続き発生する場合、あるいは診断ステップのクエリが結果を返さなかった場合は、https://support.atlassian.com/ja/ からアトラシアン サポートにお問い合わせください。