[Bug][jira] Jira Full Sync: Issue Exists in Domain Layer but Missing in Raw & Tool Layers

[narrowizard]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

After performing a full synchronization of Jira data, we found an issue that does not exist in the raw and tool layer tables. However, this issue is still present in the issues and board_issues tables in the domain layer.

What do you expect to happen

The issue should not exist in the domain layer if it does not appear in the raw or tool layers. The data in the domain layer should correctly reflect the tool layer's state.

How to reproduce

1. Perform a Jira data sync with a longer time range.
2. Set the timeAfter for the next sync to a more recent time (to reduce the data range).
3. Perform a full sync again.
4. Observe that some outdated issues (outside of the time range set in Step 2) still remain in the domain layer (issues and board_issues tables), even though they do not exist in the raw and tool layers.

Anything else

It is suspected that the domain layer data for the corresponding scope is not being properly deleted during a full sync, leading to stale data remaining in the domain tables.

Version

main

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

[d4x1]

Issue convertor in jira use a StatefulDataConverter and it will delete data in domain tables with codes as below

if d.table != "" && d.params != "" {
	err = d.db.Delete(
		row,
		dal.Where("_raw_data_table = ? AND _raw_data_params = ?", d.table, d.params),
	)
	if err != nil {
		return nil, err
	}
}

So if some issues are cleared and some are remained. I think something went wrong with _raw_data_params and _raw_data_table.

When deleting,_raw_data_table is _raw_jira_api_issues, and when inserting, _raw_data_table will be _raw_jira_api_epics for these issues from _raw_jira_api_epics.

Can you help me to check whether all issues with _raw_data_table = _raw_jira_api_epics are all kept? @narrowizard

[d4x1]

Issues from _raw_jira_api_issues are extracted to _tool_jira_issues with _raw_data_table == _raw_jira_api_issues and issuef from _raw_jira_api_epics are extracted to _tool_jira_issues with _raw_data_table == _raw_jira_api_epics.

When issue convertor is executed, it will delete data with _raw_data_table = _raw_jira_api_issues AND _raw_data_params = xxx", so _raw_data_table == _raw_jira_api_epics are kept.

It's a bug for certian.

We can fix it by update https://github.com/apache/incubator-devlake/blob/main/backend/helpers/pluginhelper/api/batch_save_divider.go#L88 to
dal.Where("_raw_data_table = ? AND _raw_data_params = ?", field.RawDataTable, d.params),.

However, this function is too fundamental.
@klesh Do you have any advice?