Mysql fails to update / select in jira database

[Aleksei Yenin]

Hi All,

We receive tons of errors like:

SQL Exception while executing the following:SELECT NAME, INITIALIZED, STATE FROM OS_WFENTRY WHERE ID=? (Communications link failure

 

The last packet successfully received from the server was 3,145,247 milliseconds ago.  The last packet sent successfully to the server was 9 milliseconds ago.)

        at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:605)

2012-07-13 17:46:26,618 http-80-3 ERROR admin 1014x988x5 nb9kil 194.44.221.46 /secure/CommentAssignIssue.jspa [gdev.plugins.internal.IssueUtil] Unknow invalid exception:
com.atlassian.jira.exception.CreateException: Could not find workflow instance #692740: root cause: SQL Exception while executing the following:SELECT NAME, INITIALIZED, STATE FROM OS_WFENTRY WHERE ID=? (Communications link failure

The last packet successfully received from the server was 3,145,247 milliseconds ago.  The last packet sent successfully to the server was 9 milliseconds ago.)
        at com.atlassian.jira.issue.managers.DefaultIssueManager.createIssue(DefaultIssueManager.java:472)

Also

2012-07-13 17:46:26,606 http-80-3 ERROR admin 1014x988x5 nb9kil 194.44.221.46 /secure/CommentAssignIssue.jspa [atlassian.event.internal.EventPublisherImpl] There was an exception thrown trying to dispatch event 'com.atlassian.jira.event.i
ssue.IssueEvent@6febe1f1[issue=NMR-5288,comment=<null>,worklog=<null>,changelog=<null>,eventTypeId=1,sendMail=true,params={eventsource=workflow, baseurl=http://....},subtasksUpdated=false]' from the invoker 'com.atlassia
n.event.internal.SingleParameterMethodListenerInvoker@35f6002a'.
java.lang.RuntimeException: Missing link detected in Global Rank table: Issue #444244 points to issue #444245 as next value, but that issue is not in the database.

What could be the problem?

[Faisal]

Hi All,

Regarding the following exceptions:

2012-07-13 17:46:26,606 http-80-3 ERROR admin 1014x988x5 nb9kil 194.44.221.46 /secure/CommentAssignIssue.jspa [atlassian.event.internal.EventPublisherImpl] There was an exception thrown trying to dispatch event 'com.atlassian.jira.event.i
ssue.IssueEvent@6febe1f1[issue=NMR-5288,comment=<null>,worklog=<null>,changelog=<null>,eventTypeId=1,sendMail=true,params={eventsource=workflow, baseurl=http://....},subtasksUpdated=false]' from the invoker 'com.atlassia
n.event.internal.SingleParameterMethodListenerInvoker@35f6002a'.
java.lang.RuntimeException: Missing link detected in Global Rank table: Issue #444244 points to issue #444245 as next value, but that issue is not in the database.

The problem may happen because JIRA is unable to find the ISSUE_ID 444245 in the ao_60db71_rank_issue_link table. There's a KB article addressing this issue, kindly refer to it and see if it helps:
https://confluence.atlassian.com/display/JIRAKB/Unable+to+convert+an+issue+into+subtask+due+to+%27Missing+link+detected+in+Global+Rank+table%27+error

Hope this helps!

[Aleksei Yenin]

Update:

I switched off greenhopper plugin, now the system works stable... Is there any way to fix that plugin?

[Radu Dumitriu]

Yes, because MySQL connections are stalled.

User a validation query and a testWhileIdle=true and testOnBorrow=true

http://commons.apache.org/dbcp/configuration.html

[Simone Longoni]

We are suffering similar problems and disabling the plugin that we use the most (GreenHopper) is clearly not the solution nor a feasible workaround.

Radu's answer is promising... Radu, (All,) where should we add this configuration options within a jira+greenhopper installation?
Should we specify it directly into MySQL config? (I don't think so)

Thanks a lot,
Simone

[Aleksei Yenin]

I am just interested - does the disabling of greenhopper helps you with the issue? If so seems there is a problem with GH which doesn't closes transition or connection on something like this if the database contains GH errors, If so, it is possible to be solved within final versions of GH either it should be reported as a bug. Regrettably, we have only production DB with huge amount of issues so it would be hard to reproduce it.

[Simone Longoni]

Cannot tell. We are suffering the issue operating on GreenHopper (don't remember whether happened also from JIRA), i.e. transitioning issues on task board, or assigning it (from task board), or loading time (again from task board).

Cheers

[Radu Dumitriu]

Hi, I'm back so let me clear this out.

1. It's a wrong decision to use MySQL (my personal opinion, it's not a database, it's a data storage solution, I'm making a clear distinction between the two :) )

2. In your JIRA home directory you will have dbconfig.xml

Edit that file and add the necessary config, check:

https://confluence.atlassian.com/display/JIRA/Tuning+Database+Connections

and read carefully: https://confluence.atlassian.com/display/JIRA/Surviving+Connection+Closures

Have fun.

[Radu Dumitriu]

And BTW, if you suspect a problem with some plugin leaking connections, you can instruct the pool with these 2:

pool-remove-abandoned-timeout => 120 (2 minutes)


pool-remove-abandoned => true