500 errors when trying to open the Email Requests page from the Jira Applications menu
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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 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 | 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).
ソリューション
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 の起動
問題が引き続き発生する場合、あるいは診断ステップのクエリが結果を返さなかった場合は、https://support.atlassian.com/ja/ からアトラシアン サポートにお問い合わせください。