Custom Groovy Script causing JIRA to stop responding

[DOT-COMmunications]

Hi

We are using your SQL Query example ( https://studio.plugins.atlassian.com/wiki/display/GRV/Miscellaneous+Groovy+Scripts#MiscellaneousGroovyScripts-ExecuteaSQLquery ) as the basis for a Scripted Field. In our modified version we make multiple statement calls (4 per loop) to 2 different tables (jiraissues and customfieldvalues). We are using JIRA 4.4.4 on a Windows 2008 server with mySQL database with the following plugin:

Plugin key:
com.onresolve.jira.groovy.groovyrunner
Developer:
OnResolve Ltd
Plugin version:
2.0.3

The field displays fine but i have a problem and a question:

1 - Problem) When doing a re-index or after a few issue views or queries eventually our JIRA instance stops responding, in the log files it says it has run out of connections in the pool. Our pool size was set to 15 and we do have an sql.close() statement (just 1) at the end of the script. Is there any way to resolve this? - Code and the error from the log are attached.

(logsnippet.txt) (sqlfieldcode.txt)

2 - Question) We are wanting to display the result of this field on a dashboard (Filter result gadget) but the value shows up blank on the dashboard and fine in the issue view, is there any way to resolve this so the value shows up?

Thanks for reading and hope to hear from you

Adam

[Dieter · Accepted solution]

Could be leaked connections or a deadlock somewhere. Please take a stack trace as documented in http://confluence.atlassian.com/display/JIRA/Generating+a+Thread+Dump and post it here

[Dieter]

Have you ever thought of writing this in JQL like here: https://answers.atlassian.com/questions/24285/executing-a-jql-string-in-plugin-and-getting-result-issues ? To me this sees a lot more maintainable because you can write the query in one line without using ids and you can even get the total number of issues using searchResults.getTotalCount(). no need to loop through the issues.

More important even: by using Jql you avoid database access since Jql search use the Lucene Index only. This won't use any of your database connections.

i know i don't give a. answer that helps you out immediately but please consider using Jql if it's acceptable from a performance point of view

[JamieA]

I'll double check when I can, but I know this works for me... what jira version?

[DOT-COMmunications]

Atlassian JIRA (v4.4.4#664-r167664)

[JamieA]

Strange... this definitely works for me. Did you select the "number" template or some other?

[DOT-COMmunications]

I set it to use the number template and the number searcher for the field.

[DOT-COMmunications]

@Jamie

No i do not see it in issue navigator but it does show up fine when viewing the issue itself.

Image above is from issue navigator

Image below is from the issue view

[JamieA]

As I asked you on the other place, do you see the value in the issue navigator when you add the column to the output?

[DOT-COMmunications]

@Jamie - That appears to be working okay but will keep an eye on JIRA and the logs for any problems.

I have updated to 2.0.4 but when i try showing the column on a filter result on the dashboard the column is just empty, any ideas?

[JamieA]

OK, just remove the conn.close line. I would reindex and look for errors in the logs.

[Dieter]

Sorry, for my answer. I didn't see the part ' AND issue = '${it.issue} in the where condition of Jamies solitiom on my iPad. Today i can see that, really don't know what happened yesterday. For this reason i have deleted my answer though it still seems problematic to have such deeply nested loops.

[DOT-COMmunications]

Thanks for your help anyway @Dieter, it probably is a problem having such deep loops but not sure of any other way to do it. It is adapted from a PHP & mySQL query that i run on the database itself to produce an e-mail report but i am wanting to display the data inside JIRA so thought i could just adapt the query.

@Jamie - Thank you for the code but i keep getting this error in the logs when trying it:

/plugins/servlet/com.onresolve.ScriptedFieldPreviewServlet [onresolve.jira.groovy.GroovyCustomField] javax.script.ScriptException: java.sql.SQLException: Connection is closed.

It happens when it gets to the finally block on the conn.close() statement

[DOT-COMmunications]

We have about 5 users at the moment. I dont think it is a JIRA deadlock though as now that i have taken the field out it has not stopped responding once.

@Dieter/Jamie - Probably could do in JQL but i need a count of records rather than a list of records if that is possible without writing a custom plugin to do it?

@Dieter - sqlStmt2 returns a set of issue records than the inital statement, what it is doing is checking that the custom field applies to the correct record (summary and sClient match) but returns a set of issues from the customfieldvalue table instead so that i can iterate over them in the next SQL statement.

@Jamie - Thank you for the code i will take a look at it and get back to you about if it works etc.

[Norman Abramovitz]

How many users do you have? I would first try increasing the number of connections. Also I would contact support since it sounds like Jira might have a deadlock issue given that other people are starting to complain about running out of connections as well.

[JamieA]

I agree with Dieter, if it's possible to do it in JQL then I would use that. Is it? If so we could give an example for using JQL.

If not, I would write it like this: https://gist.github.com/2438750 (not tested).

I'm not sure about closing the connection, the javadoc implies that closing the Sql object should close the connection. Perhaps for some issues it's failing, so you're never getting to the code for closing stuff. Check the logs for errors.

> We are wanting to display the result of this field on a dashboard (Filter result gadget) but the value shows up blank on the dashboard and fine in the issue view

Can you try 2.0.4, I seem to remember a previous version was missing the column-view.vm template.