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

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Problem

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).

Expected behavior

The Email Requests configuration page should load properly:

Cause

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.

Diagnosis steps

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).

Resolution

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.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

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

  • Start 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/.


Last modified on Nov 23, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.