JQL queries involving the worklogAuthor function are missing some Jira issues

Summary

The worklogAuthor function can be used in JQL queries to search for issues where a user logged some work using the time tracking feature.

The purpose of this article is to describe a scenario where a JQL query using the statement worklogAuthor = <SOME_JIRA_USER> is missing some Jira issues where the Jira user actually logged some work.

Environment

Jira Server / Data Center on any version from 9.0.0.

Diagnosis

To verify if this article is relevant, check if the points listed below:

• The issue started to occur after an upgrade from Jira 8.x to 9.x

• When opening an example of Jira issue that is missing from the JQL query, we can see that this issue has more than 100 entires in the Work Log tab

  • This can also be confirmed from the Jira Database by running the SQL query below after replacing ABC with the project key, and 123 with the issue number in the project, and checking the number of rows returned by this query:

    1 2 3 4 5 SELECT created, updated, startdate, timeworked FROM worklog WHERE issueid= ( select j.id from project p, jiraissue j where p.id=j.project and j.issuenum=123 and p.pkey='ABC' );

Cause

Since Jira 9.0.0, some safeguards were introduced in the Jira application in order to improve the performance of the Jira search, as explained in Introducing safeguards to Jira index.

To improve the issue search speed, the Jira indexes only include the "Top N" most recent entries in each Jira issue for the following parameters:

• comments: 500

• changehistory: 100

• worklog: 100

As a result, if there are more than 100 work log entries in a given ticket, JQL queries using functions such as worklogAuthor or worklogDate will only search for the 100 most recent entries.

Because of that, if the 2 conditions below in a Jira issue are met, this issue will not be found by the JQL query worklogAuthor = <SOME_JIRA_USER>:

• The issue contains more than 100 work log entries

• The work log entries added by the Jira user are not within the 100 most recent entries

Solution

To fix this issue, you can add a JVM parameter to the Jira application to disable the limit on the number of work log entries that can be searched by JQL queries.

⚠️Please note that adding this JVM parameter might impact the performances of the Jira issue search feature. Therefore, we highly recommend to try this workaround on a test Jira instance first.

Here are the steps:

• Stop the Jira application (or all the Jira nodes in case of a Data Center cluster)

• Add the JVM parameter below by following the instructions from Setting properties and options on startup (make sure to add this parameter to all Jira nodes in case of a cluster)

  1 -Djira.safeguards.indexing.issue.worklogs=-1

• Start the Jira application (or all the Jira nodes in case of a Data Center cluster)

• Run a reindex for the desired project or run a full reindex