This issue affects Jira Server and single-node Jira DC only

See "Workaround" for more details.

Issue Summary

DefaultServerIndexRepairService periodically queries the version tables to get all the issues updated in the past 25s. On Oracle DB this "25s" argument is passed as TIMESTAMP datatype, while update_time column has datatype DATE. This mismatch causes the DB to perform a full table scan. This causes unnecessary DB load.

The same issue applies to the three versioning tables: ISSUE_VERSION, COMMENT_VERSION and WORKLOG_VERSION.

The query run is

select 
    ISSUE_VERSION.issue_id, 
    ISSUE_VERSION.parent_issue_id,
    ISSUE_VERSION.update_time,
    ISSUE_VERSION.index_version,
    ISSUE_VERSION.deleted
from issue_version ISSUE_VERSION
where ISSUE_VERSION.update_time > current_timestamp + interval '-25' second

The corresponding Java code is com.atlassian.jira.versioning.VersioningDao#findVersionsUpdatedInTheLast:

                        .where(updateTimePath.after(
                                SQLExpressions.addSeconds(
                                        DateTimeExpression.currentTimestamp(Timestamp.class),
                                        (int) -duration.getSeconds())))

Potentially (not tested!) changing this to

DateTimeExpression.currentDate(Timestamp.class)

would solve the problem, but the impact on DBs from other vendors would need to be checked.

This was reproduced on Oracle DB. At this moment we don't know if other DB types are affected by this issue.

Steps to Reproduce

1. Run Jira Server on Oracle DB.
2. DefaultServerIndexRepairService will query the DB inefficiently

Expected Results

iv_update_time / cv_update_time / wv_update_time indexes are used.

Actual Results

Full table scan is performed.

Impact

The query is inefficient, but does not impact Jira nor the DB significantly.

Workaround

This issue affects Jira Server and Jira single-node DC only. In order to avoid this issue, migrate to Jira DC cluster following this guide. Mind you, you do not need to set up a second node. It's enough to set up the shared home and cluster.properties file.