[Bug][Jira] collectWorklogs requests every page without startAt, so each page returns the same worklogs (raw table grows quadratically) #9323

For issues with more than 20 worklogs, collectWorklogs determines the number of pages but never sends a page offset, so every page request is identical and returns the same worklogs again.

backend/plugins/jira/tasks/worklog_collector.go (v1.0.3-beta19, 210dcd4):

UrlTemplate:   "api/2/issue/{{ .Input.IssueId }}/worklog",
PageSize:      50,
GetTotalPages: GetTotalPagesFromResponse,   // shared.go: pages = total / PageSize (+1)
Query: func(reqData *api.RequestData) (url.Values, errors.Error) {
    // According to the following resources, the worklogs API returns all worklogs without pagination
    ...
    return nil, nil                          // no startAt / maxResults
},

ApiCollector.fetchPagesDetermined therefore fires ceil(total/50) requests. The URL template does not use the pager, and Query returns nil, so every request goes to the same URL with an empty query (api_collector.go, generateUrl / fetchAsync). Each response starts at offset 0 and is stored in _raw_jira_api_worklogs. If the default page size covers all n worklogs, which the code comment assumes, an issue with n worklogs produces n * ceil(n/50) raw rows instead of n.

Incremental collections do not delete existing raw rows (ApiCollectorArgs.Incremental: "the existing data won't get deleted"), so this is appended again every time such an issue is updated. That happens daily for issues that people book time on.

extractWorklogs uses the non-stateful api.NewApiExtractor, so it reads the whole raw table on every run.

Numbers from one Jira Cloud board (about 8,700 issues, nightly blueprint):

Re-collection alone cannot explain this. This installation has run 32 pipelines in total. After we emptied the raw table, extractWorklogs removed 16,692 tool rows, which are the worklogs that came only from that table. The table also held the first 20 worklogs of each affected issue, which the issue extractor still produces, so the true number of distinct worklogs is somewhat higher. Even so, that is hundreds of raw copies per worklog, against at most one copy per pipeline run, so the duplication has to happen within a single run.

The tool and domain data were correct, because duplicates collapse on the primary key (connection_id, issue_id, worklog_id). The cost was disk space and a 13 h extractor. It probably also contributed to the innodb_redo_log_capacity warnings MySQL logged all night.

What do you expect to happen

Each worklog is collected once per issue update, and the raw table grows linearly with the number of worklogs.

How to reproduce

1. Jira Cloud connection, board with an issue that has more than 50 worklogs (for example 500).
2. Run the blueprint once.
3. SELECT COUNT(*) FROM _raw_jira_api_worklogs WHERE url LIKE '%/issue/<id>/worklog%' is expected to return about 5,000 rows (10 pages × 500) instead of 500.
4. Add a worklog to that issue and run the blueprint again. About another 5,000 rows are expected to be appended.

(Steps 3 and 4 follow from the code. We have not run them in isolation; the evidence is the aggregate numbers above.)

Anything else

The Jira Cloud endpoint is paginated and supports startAt and maxResults (https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-worklogs/#api-rest-api-3-issue-issueidorkey-worklog-get). The fix is the same Query that issue_changelog_collector.go already uses:

Query: func(reqData *api.RequestData) (url.Values, errors.Error) {
    query := url.Values{}
    query.Set("startAt", fmt.Sprintf("%v", reqData.Pager.Skip))
    query.Set("maxResults", fmt.Sprintf("%v", reqData.Pager.Size))
    return query, nil
},

We have not tested this fix. On Jira Server/DC, please check that startAt is honoured there as well. If it is ignored, the requests stay identical and the duplication remains.

Not a duplicate of #9122, which is about issues with fewer than 20 worklogs on Jira Server.

Related: #7466 (May 2024, "jira worklog collector fetches only the 1st page") added the empty Query with the "returns all worklogs" comment. It kept PageSize and GetTotalPages, so the paging loop still runs, but every request in it is identical.

A follow-up worth considering: switch extractWorklogs to NewStatefulApiExtractor, so that a large raw table is not re-read every night.

Workaround: ENABLE_SUBTASKS_BY_DEFAULT="jira:collectWorklogs:false,jira:extractWorklogs:false,jira:convertWorklogs:false" (we take worklogs from the Tempo plugin), then TRUNCATE _raw_jira_api_worklogs.

Version

v1.0.3-beta19 (code unchanged since at least v1.0.3-beta18), MySQL 8.4, Helm chart deployment

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct