500 error opening issue statuses

[Tom Rasmussen]

From Jira admin, Issues section, Statuses returns the following:

com.google.template.soy.tofu.SoyTofuException: In 'foreach' command {foreach $status in $statuses}{call .statusRow}{param status: $status /}{param first: isFirst($status) /}{param last: isLast($status) /}{/call}{/foreach}, the data reference does not resolve to a SoyList (encountered type com.google.template.soy.data.restricted.UndefinedData). at JIRA.Templates.Statuses.success(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:154) [?:?] at com.atlassian.soy.impl.DefaultSoyTemplateRenderer.render(DefaultSoyTemplateRenderer.java:45) [?:?] at com.atlassian.soy.impl.DefaultSoyTemplateRenderer.render(DefaultSoyTemplateRenderer.java:39) [?:?] at com.atlassian.jira.web.dispatcher.JiraSoyViewDispatcher.render(JiraSoyViewDispatcher.java:48) [classes/:?] at com.atlassian.jira.web.dispatcher.JiraSoyViewDispatcher.dispatch(JiraSoyViewDispatcher.java:38) [classes/:?] at com.atlassian.jira.web.dispatcher.JiraWebworkViewDispatcher.dispatchViaSoyView(JiraWebworkViewDispatcher.java:120) [classes/:?] at com.atlassian.jira.web.dispatcher.JiraWebworkViewDispatcher.dispatchView(JiraWebworkViewDispatcher.java:54) [classes/:?] at com.atlassian.jira.web.dispatcher.JiraWebworkActionDispatcher.service(JiraWebworkActionDispatcher.java:178) [classes/:?] at 
yadda, yadda, yadda....too many characters

I've looked at various community recommendations such as https://confluence.atlassian.com/jirakb/the-jira-application-statuses-page-throws-a-500-error-608731194.html.  I ran the queries and found no duplicates.  I have run Integrity Checks and fixed any problems found.  I have restarted the service and rebuilt the indexes.  

This appears to be the closest thing, but there is no solution listed:  https://community.atlassian.com/t5/Jira-questions/500-error-when-viewing-status-list/qaq-p/714684

[Andy Heinzer]

Hi Tom,

Sorry to hear about this problem.  I believe that the KB of https://confluence.atlassian.com/jirakb/the-jira-application-statuses-page-throws-a-500-error-608731194.html is the correct one for the problem seen here.  But part of the problem is that this particular error can have different causes. All of these causes relate to workflows and how they are being stored in the database.  Four of those causes we know about are documented in that KB.  But right now we don't exactly know what is causing this error just yet.

I understand that you have run some SQL queries to look for duplicates and found none, but that is not the only potential cause.  That said, I'd be interested to know exactly which SQL queries you have run here so far to diagnose this.  Also which database type is your Jira database using? Postgresl, mysql, Oracle, or Microsoft SQL are all supported database types, but sometimes these have differing requirements to run SQL queries against.  Our KB articles tend to default to the postgresql syntax just for reference.

I would be interested to see the results for each of these SQL queries against the Jira database:

SELECT count(*), parentname FROM jiradraftworkflows group by parentname having count(*) > 1;

SELECT count(*), workflowname FROM jiraworkflows group by workflowname having count(*) > 1;

select * from nodeassociation where SOURCE_NODE_ENTITY='Project' AND SOURCE_NODE_ID not in (select ID from project);

SELECT id, workflowname FROM jiraworkflows WHERE descriptor ilike '%<action id="<ID>"%<action id="<ID>"%';

Let me know if you're running a different database type, as you might not get any results back for these queries for databases like mssql if we're not accurately referencing the correct database.

Additionally, it might be helpful to recreate this problem once more (as in try to load that statuses page again, even though you know it will fail), and then immediate go into the $JIRAHOME/log/atlassian-jira.log file.  It is likely that this log file could be generating a stacktrace error that might give us more details about the specific workflow entry that is at fault here OR possibly some other clues as to why this is happening.

Let me know the results.  Interested to see if we can narrow down the exact cause here and offer some next steps to try here.

Andy

[Tom Rasmussen]

Thanks for the response Andy.  I am using MSSQl 2014 Enterprise.  Each of the queries you supplied returned zero results.  Any attempts I've had to post the full stack trace result in error on this page.  The log entry is about 57KB.

[Andy Heinzer]

Hi Tom,

I have created a support case on your behalf over in https://getsupport.atlassian.com/servicedesk/customer/portal/22/JSP-413102

Please generate a support zip from this instance.  If you are unable to do that, then try to zip the logs in $JIRAHOME/log/ folder, and attach those to that support ticket.  Other data might also be requested, but those logs should be helpful so we can investigate this further.

Andy

[Tom Rasmussen]

Ok, here was the resolution for anyone who stumbles across this in the future.  I found that there was a draft workflow that had invalid xml in the descriptor column in the database.  That error prevented certain actions such as trying to delete custom screens.  But nothing was reported in the app.  I found the error in the jira logs referencing a premature end of file in the workflow xml.  The workflows screen did not show any drafts or problems, but there was a draft entry in database.  I was able to delete that draft directly from the DB which in turn let me access the Statuses page.