[Upgrade to >= 3.4] | I8866_DispatchRegionCodesFixingJobs - FixRegionCodes Job

[bozana]

Update (07.04.26): for the final solution see #11758 (comment)

Update (31.0326): for the final solution see #11758 (comment)

---

Valid Title

• I have updated the title to accurately reflect the bug description

Description

S. the messages in this issue, from here on: #8866 (comment)
The lib/pkp/lib/regionMapping.php contains fixes for the old region codes. For some regions, the fix cannot be applied twice, because the fixed region code would actually be seen as the code to be fixed, in some cases.
If the job FixRegionCodes fails, it will be re-tried again, which could then lead to invalid region codes in the DB.
Thus, a new solution is needed, that inserts a marker, e.g. a prefix for the codes to be fixed (so that only this entries with the marker are considered for update) and that additionally splits the lines to update into chunks (so that no timeout happens in huge installations).

The new solution uses smaller and chained jobs, that can be re-tried. All the region that needs to be fixed are marked with the prefix 'pkp-'. Then the regions containing the prefix are fixed. The jobs are per country and 100000 rows.

Steps to Reproduce

1. Have entries in the DB table metrics_submission_geo_daily and/or metrics_submission_geo_monthly with some of those region codes, e.g.
   country = 'KH', region = '09'
2. run the FixRegionCodes job
3. the region should now be '10'
4. run the FixRegionCodes job
5. the region is now '11', which is wrong, it should be '10'

Expected Result

After running the job several times the region code should still be correct. From example above: region should be '10'

Actual Result

After running the job several times the region code is wrongly fixed again. From example above: region is '11'

Environment Details

No response

Application Version

OJS, OMP, OPS version >= 3.4

Logs

No response

Additional Information

No response

---

PRs:

stable-3_4_0:

• pkp/pkp-lib11758 fix region codes #11899
• pkp/pkp-lib#11758 submodule update ojs#5454 (only submodule update)
• pkp/pkp-lib#11758 submodule update omp#2290 (only submodule update)
• pkp/pkp-lib#11758 submodule update ops#1252 (only submodule update)

stable-3_5_0:

• pkp/pkp-lib#11758 fix FIPS region codes via chained background jobs: … #12469
• pkp/pkp-lib#11758 submodule update ojs#5441 (only submodule update)

Fix 1:

• pkp/pkp-lib#11758 Refactor region code fix to use self-requeuing job … #12536
• pkp/pkp-lib#11758 submodule update ojs#5475 (only submodule update)

main:

• pkp/pkp-lib#11758 fix FIPS region codes via chained background jobs: … #12512

Fix 1:

• pkp/pkp-lib#11758 Refactor region code fix to use self-requeuing job … #12541

[bozana]

Hi @jonasraoni and @jnugent,
What do you think is the chunk updated needed? -- We update only country by country, so it will never be that much rows needed to be updated. And once I chain the jobs, so that they cannot be run in parallel, I add the prefixes so that this 'recursion' is not a problem, and always clean the temporary table before starting a job (which was the main problem for timeout), I have a feeling we do not need to even chunk it all.
I am thinking to first implement a solution without chunk update, if you do not disagree. Maybe there will be a possibility to test it then and for us to see how it behaves with such big DB tables and if we need to chunk it further?
Thanks a lot!

[bozana]

Hi @jonasraoni and @jnugent,

I provided a PR: The PR now splits the job into several smaller jobs, that are chained (i.e. cannot be run parallel). I am also doing the updates in chunks of 100000 rows. (I have a huge DB table metrics with million entries, that I have tested it all with, and it looked fine for me). Let me know if you think the chunk size should be different. Now first I mark all regions that need to be fixed with the prefix 'pkp-'. Then I fix only those with the prefix. Because of the prefix, and because I consider the last DB tables IDs, the jobs can now be re-run without the problem, and would consider only the migrated rows -- the new rows would not be affected, if the jobs would be run at some later point. I also clear the temporary DB table before insertion, so that it does not grow, if something fails.

Could you please review the PR above?

Maybe also a note: maybe you as admin could run all the jobs directly after the upgrade, before putting the installation into production.

[jnugent]

Hi @bozana, I'm probably not qualified to really review the code but I can certainly attempt a test upgrade from 3.3 to 3.4 with a large client and apply the PR and see how it goes. This might take me a few days to get done, though.

[asmecher]

Deferring to 3.5.0-3 -- but please don't forget this one, it'll be necessary to merge before the LTS stamp goes on!