500 error when accessig Service desk email request

[Goran Nogic]

Hi,

my name is Goran.

When I'm accessing Jira Service Desk > Email request I'm receiving 500 error. 

Jira Service Desk version is 3.15.3 and Jira Software is 7.12.3. Applications are running on our server.

Here are technical details from web page:

Technical details

Log's referral number: a5b676b1-62f0-458d-9a28-300202852477

Cause

Referer URL: https://xxxx.xxxx.xx/plugins/servlet/applications/versions-licenses

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

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).
	at ServiceDesk.Templates.Admin.email.emailSettings(Unknown Source) [?:?]
	at com.google.template.soy.tofu.internal.BaseTofu.renderMainHelper(BaseTofu.java:369) [?:?]
	at com.google.template.soy.tofu.internal.BaseTofu.renderMain(BaseTofu.java:322) [?:?]
	at com.google.template.soy.tofu.internal.BaseTofu.access$100(BaseTofu.java:66) [?:?]
	at com.google.template.soy.tofu.internal.BaseTofu$RendererImpl.render(BaseTofu.java:476) [?:?]
	at com.atlassian.soy.impl.DefaultSoyManager.render(DefaultSoyManager.java:133) [?:?]
	at com.atlassian.soy.impl.DefaultSoyTemplateRenderer.render(DefaultSoyTemplateRenderer.java:45) [?:?]
	at com.atlassian.soy.impl.DefaultSoyTemplateRenderer.render(DefaultSoyTemplateRenderer.java:39) [?:?]
	at sun.reflect.GeneratedMethodAccessor1989.invoke(Unknown Source) [?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_181]
	at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_181]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56) [?:?]
	at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60) [?:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70) [?:?]
	at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53) [?:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57) [?:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.5.RELEASE.jar:4.2.5.RELEASE]
	at com.sun.proxy.$Proxy3408.render(Unknown Source) [?:?]
	...
Caused by: com.google.template.soy.sharedpasses.render.RenderException: 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).
	at ServiceDesk.Templates.Admin.email.emailSettings(Unknown Source) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitForeachNode(RenderVisitor.java:384) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:104) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:56) [?:?]
	at com.google.template.soy.basetree.AbstractNodeVisitor.visitChildren(AbstractNodeVisitor.java:59) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visitChildren(AbstractSoyNodeVisitor.java:129) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitForeachNode(RenderVisitor.java:403) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:104) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:56) [?:?]
	at com.google.template.soy.basetree.AbstractNodeVisitor.visitChildren(AbstractNodeVisitor.java:59) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visitChildren(AbstractSoyNodeVisitor.java:129) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitBlockHelper(RenderVisitor.java:702) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitSoyNode(RenderVisitor.java:649) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visitIfElseNode(AbstractSoyNodeVisitor.java:268) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:98) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitIfNode(RenderVisitor.java:344) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:96) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:56) [?:?]
	at com.google.template.soy.basetree.AbstractNodeVisitor.visitChildren(AbstractNodeVisitor.java:59) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visitChildren(AbstractSoyNodeVisitor.java:129) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitBlockHelper(RenderVisitor.java:702) [?:?]
	at com.google.template.soy.sharedpasses.render.RenderVisitor.visitTemplateNode(RenderVisitor.java:220) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visitTemplateBasicNode(AbstractSoyNodeVisitor.java:160) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:66) [?:?]
	at com.google.template.soy.soytree.AbstractSoyNodeVisitor.visit(AbstractSoyNodeVisitor.java:56) [?:?]
	at com.google.template.soy.basetree.AbstractNodeVisitor.exec(AbstractNodeVisitor.java:40) [?:?]
	at com.google.template.soy.tofu.internal.BaseTofu.renderMainHelper(BaseTofu.java:366) [?:?]
	... 312 more

...and this is atlassian-jira.log

2019-01-08 09:44:02,392 http-nio-8081-exec-14 ERROR      [c.a.j.web.servlet.InternalServerErrorServlet] {errorId=a5b676b1-62f0-458d-9a28-300202852477, interpretedMsg=, cause=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)., stacktrace=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).
    	at ServiceDesk.Templates.Admin.email.emailSettings(Unknown Source) [?:?]
    	at com.google.template.soy.tofu.internal.BaseTofu.renderMainHelper(BaseTofu.java:369) [?:?]
    	at com.google.template.soy.tofu.internal.BaseTofu.renderMain(BaseTofu.java:322) [?:?]
    	at com.google.template.soy.tofu.internal.BaseTofu.access$100(BaseTofu.java:66) [?:?]
    	at com.google.template.soy.tofu.internal.BaseTofu$RendererImpl.render(BaseTofu.java:476) [?:?]
    	at com.atlassian.soy.impl.DefaultSoyManager.render(DefaultSoyManager.java:133) [?:?]
    	at com.atlassian.soy.impl.DefaultSoyTemplateRenderer.render(DefaultSoyTemplateRenderer.java:45) [?:?]
    	at com.atlassian.soy.impl.DefaultSoyTemplateRenderer.render(DefaultSoyTemplateRenderer.java:39) [?:?]
    	at sun.reflect.GeneratedMethodAccessor1989.invoke(Unknown Source) [?:?]
    ...

I have tried several solutions posted here:

• https://confluence.atlassian.com/jirakb/the-jira-application-statuses-page-throws-a-500-error-608731194.html?_ga=2.135146609.1108616993.1546933816-2065509699.1527578393
• https://community.atlassian.com/t5/Jira-Service-Desk-questions/Email-Requests-in-Service-Desk-not-working-v2/qaq-p/638922

...but without success.

Can you point me to the right direction?

Thx!

[Goran Nogic · Accepted solution]

Hi @Shannon S,

here are database queries:

Query1:

Query2:

Query3:

Query4:

The last query gives a blank table.

I noticed that this email address: service.desk@domain.com appears in the tables database, but  in Jira service desk as the default address we use: helpdesk@domain.com. Also, "someuser" is disabled, this is not an active user.

Regards,

[Shannon S]

Thank you for confirming that.

Could you backup your database, stop Jira, and then run the following queries?

DELETE from "AO_2C4E5C_MAILHANDLER" where "MAIL_CHANNEL_ID" = 1;
DELETE from "AO_2C4E5C_MAILCHANNEL" where "MAIL_CONNECTION_ID" = 1;
DELETE from "AO_2C4E5C_MAILCONNECTION" where "ID" = 1;
DELETE from "AO_54307E_EMAILCHANNELSETTING" where "ID" = 1;

Once complete, you can start Jira again and see if the problem is resolved.

Regards,

Shannon

[Goran Nogic]

Hi @Shannon S,

thx for your help,problem solved!

[Shannon S]

That's really great to hear, happy to help!

You can thank my colleague @Julien Rey, as of yesterday he created a new knowledge base article for this very issue.

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

Please feel free to review that and do let us know if you have any feedback.

Regards,

Shannon

[Shannon S · Accepted solution]

Goran,

Can you please run the following queries? For your information, they are written for Postgres database so may need to be adjusted according to your database version.

• Query 1:

  select * from "AO_2C4E5C_MAILHANDLER";
  

• Query 2:

  select * from "AO_2C4E5C_MAILCHANNEL";
  

• Query 3:

  select * from "AO_2C4E5C_MAILCONNECTION";
  

• Query 4:

  select * from "AO_54307E_EMAILCHANNELSETTING";
  

• Query 5:

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

We are doing this to see if there are any duplicate entries which is often the cause of this issue.

Please be advised you may want to sanitize the output of your database queries in case there is any private information before sharing here.

Regards,

Shannon

[Goran Nogic]

Hi @Shannon S,

I can't post an answer...constantly receiving an error that my message is not accepted ( check the pic please ).