Incorrect primary locale after upgrading from OJS 3.3.0.13 to 3.4.0.5

I created a sandbox environment. It is a fresh install of 3.4.0.5 with the database and files from 3.3.0.13. The upgrade script seemed to have run without incident, but I think something has gone wrong. Any ideas where I can start looking?

I had to manually change the primaryLocale setting on my journals from en_US to en. Without that I was get the raw ##…## text in pages

UPDATE journal_settings SET setting_value = 'en' WHERE setting_name = 'primaryLocale' AND setting_value = 'en_US';

I have 139 jobs with display name PKP\migration\upgrade\v3_4_0\jobs\FixRegionCodes.

When I run php lib/pkp/tools/jobs.php work, I get these stack traces. There doesn’t appear to be an actual error message. The jobs table shows increased attempt counts. (I elided the intermediate stack traces from Illuminate.)

2024-07-09T03:45:38.972515555Z [2024-07-09 13:45:38][156] Processing: PKP\migration\upgrade\v3_4_0\jobs\FixRegionCodes
2024-07-09T03:45:40.925303208Z #0 /var/www/html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\Database\Connection->runQueryCallback()
2024-07-09T03:45:40.925315541Z #1 /var/www/html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(534): Illuminate\Database\Connection->run()
2024-07-09T03:45:40.925317041Z #2 /var/www/html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php(469): Illuminate\Database\Connection->statement()
2024-07-09T03:45:40.925318125Z #3 /var/www/html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(338): Illuminate\Database\DatabaseManager->__call()
2024-07-09T03:45:40.925319125Z #4 /var/www/html/lib/pkp/classes/migration/upgrade/v3_4_0/jobs/FixRegionCodes.php(83): Illuminate\Support\Facades\Facade::__callStatic()
…
2024-07-09T03:45:40.925340666Z #23 /var/www/html/lib/pkp/classes/core/PKPQueueProvider.php(83): Illuminate\Queue\Worker->daemon()
2024-07-09T03:45:40.925341458Z #24 /var/www/html/lib/pkp/tools/jobs.php(408): PKP\core\PKPQueueProvider->runJobsViaDaemon()
2024-07-09T03:45:40.925342291Z #25 /var/www/html/lib/pkp/tools/jobs.php(693): PKP\tools\commandJobs->work()
2024-07-09T03:45:40.925343083Z #26 /var/www/html/lib/pkp/tools/jobs.php(699): PKP\tools\commandJobs->execute()
2024-07-09T03:45:40.925343833Z #27 {main}

Alpine Linux: 3.20
PHP: 8.2.21
MySQL: 8.0.35

I ran this query, which the FixRegionCodes job is trying to run.

UPDATE metrics_submission_geo_monthly gm
INNER JOIN region_mapping_tmp rm ON (rm.country = gm.country AND rm.fips = gm.region)
SET gm.region = rm.iso

I get this error.
[23000][1062] Duplicate entry '4-495-GB-BDF-Bedford-201605' for key 'metrics_submission_geo_monthly.msgm_uc_context_submission_c_r_c_month'

However this query produces no rows.

SELECT context_id, submission_id, country, region, city, month, COUNT(*)
FROM metrics_submission_geo_monthly
GROUP BY 1, 2, 3, 4, 5, 6
HAVING COUNT(*) > 1
ORDER BY 1, 2, 3, 4, 5, 6
;

This query — which if I’ve done it correctly would be table after the update above completes — does show duplicates.

Query
SELECT gm.context_id, gm.submission_id, gm.country, rm.iso, gm.city, gm.month, COUNT(*)
FROM metrics_submission_geo_monthly AS gm
INNER JOIN region_mapping_tmp AS rm ON (rm.country = gm.country AND rm.fips = gm.region)
GROUP BY 1, 2, 3, 4, 5, 6
HAVING COUNT(*) > 1
ORDER BY 1, 2, 3, 4, 5, 6
;
Rows
context_id submission_id country iso city month COUNT(*)
4 383 SC 08 Victoria 201702 2
4 407 SC 08 Mahé 201611 2
4 495 GB BDF Bedford 201605 2
4 495 SC 08 Mahé 201612 2
4 590 SC 08 Victoria 201604 2
17 6386 SC 08 Mahé 201702 2
23 5014 SC 08 Mahé 201610 2
34 5976 SC 08 Mahé 201701 2
39 8042 SC 08 Mahé 201612 2
48 9382 SC 08 Mahé 201702 2
49 9943 SC 08 Mahé 201610 2
49 9943 SC 08 Mahé 201611 2
49 9943 SC 08 Mahé 201701 2
49 10149 SC 08 Mahé 201611 2

The unaggregated duplicate row seem to be these.

Query
SELECT t1.metrics_submission_geo_monthly_id, t1.context_id, t1.submission_id, t1.country, t1.region, t2.iso, t1.city, t1.month, t1.metric, t1.metric_unique
FROM metrics_submission_geo_monthly AS t1
INNER JOIN region_mapping_tmp AS t2 ON (t2.country = t1.country AND t2.fips = t1.region)
JOIN (
    SELECT gm.context_id, gm.submission_id, gm.country, rm.iso, gm.city, gm.month
    FROM metrics_submission_geo_monthly AS gm
    INNER JOIN region_mapping_tmp AS rm ON (rm.country = gm.country AND rm.fips = gm.region)
    GROUP BY 1, 2, 3, 4, 5, 6
    HAVING COUNT(*) > 1
) AS t3
ON t1.context_id = t3.context_id AND t1.submission_id = t3.submission_id AND t1.country = t3.country AND t2.iso = t3.iso AND t1.city = t3.city AND t1.month = t3.month
ORDER BY 2, 3, 4, 5, 6, 7
;
Rows
metrics_submission_geo_monthly_id context_id submission_id country region iso city month metric metric_unique
10354 4 383 SC 08 08 Victoria 201702 1 0
10354 4 383 SC 08 08 Victoria 201702 1 0
12494 4 407 SC 08 08 Mahé 201611 1 0
12494 4 407 SC 08 08 Mahé 201611 1 0
23175 4 495 GB A5 BDF Bedford 201605 1 0
23262 4 495 GB Z5 BDF Bedford 201605 1 0
23358 4 495 SC 08 08 Mahé 201612 4 0
23358 4 495 SC 08 08 Mahé 201612 4 0
46947 4 590 SC 08 08 Victoria 201604 1 0
46947 4 590 SC 08 08 Victoria 201604 1 0
109731 17 6386 SC 08 08 Mahé 201702 1 0
109731 17 6386 SC 08 08 Mahé 201702 1 0
140401 23 5014 SC 08 08 Mahé 201610 1 0
140401 23 5014 SC 08 08 Mahé 201610 1 0
180973 34 5976 SC 08 08 Mahé 201701 1 0
180973 34 5976 SC 08 08 Mahé 201701 1 0
218015 39 8042 SC 08 08 Mahé 201612 1 0
218015 39 8042 SC 08 08 Mahé 201612 1 0
237799 48 9382 SC 08 08 Mahé 201702 1 0
237799 48 9382 SC 08 08 Mahé 201702 1 0
257675 49 9943 SC 08 08 Mahé 201610 2 0
257676 49 9943 SC 08 08 Mahé 201611 1 0
257677 49 9943 SC 08 08 Mahé 201701 1 0
257677 49 9943 SC 08 08 Mahé 201701 1 0
257676 49 9943 SC 08 08 Mahé 201611 1 0
257675 49 9943 SC 08 08 Mahé 201610 2 0
260636 49 10149 SC 08 08 Mahé 201611 1 0
260636 49 10149 SC 08 08 Mahé 201611 1 0

When I look at region_mapping_tmp, I do have duplicate rows for countries GB and SC.

Correction. I do have duplicate rows in region_mapping_tmp for SC.

For GB, the FIPS regions A5 and Z5 both seem to map to ISO region BDF.

I combined my row for GB regions A5 and Z5 into one, adding the metric count.

Query
DELETE FROM metrics_submission_geo_monthly
WHERE metrics_submission_geo_monthly_id = 23262;

UPDATE metrics_submission_geo_monthly
SET metric = 2
WHERE metrics_submission_geo_monthly_id = 23175;

And I truncated my region_mapping_tmp table.

TRUNCATE TABLE region_mapping_tmp;

After that the FixRegionCodes jobs completed successfully.