So we have now deleted 700k spam users on our site by using mergeusers.php and input a list of ID as parameter.
which is officially recommended on the fourm. nothing was deleted in the DB directly.
after this has been done we discovered that when the editors assign a reviewer, the reviewer list is unable to display anymore. (it was slow but working before the spam users delete)
The error is: “Ajax request or invalid JSON returned”
link: “/$$$call$$$/grid/users/reviewer/reviewer-grid/show-reviewer-form?submissionId=70654&stageId=3&reviewRoundId=20974&selectionType=1&_=1628845155984”
in apache2 error log:
PHP Notice: Undefined index: stageId in /var/www/html/lib/pkp/controllers/grid/eventLog/SubmissionEventLogGridHandler.inc.php on line 111
When i looked into DB:
The loading step of reviewer seems taking ages to complete While for some of the journals there is only 200 reviewers.
Only one of the journal with 13 reviewers is able to load.
I think there is some data error in the DB after mergeusers.php clean spam.
Any idea what i can do to solve this issue? As so many editors are asking the question.
select u.*, COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given, CASE WHEN ugl.setting_value <> ‘’ THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family, MAX(ra.date_assigned) as last_assigned, (SELECT SUM(CASE WHEN ra.date_completed IS NULL AND ra.declined <> 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as incomplete_count, (SELECT SUM(CASE WHEN ra.date_completed IS NOT NULL AND ra.declined <> 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as complete_count, (SELECT SUM(CASE WHEN ra.declined = 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as declined_count, (SELECT SUM(CASE WHEN ra.cancelled = 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as cancelled_count, AVG(DATEDIFF(ra.date_completed, ra.date_notified)) as average_time, (SELECT AVG(ra.quality) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id AND ra.quality IS NOT NULL) as reviewer_rating from users as u left join user_user_groups as uug on uug.user_id = u.user_id left join user_groups as ug on ug.user_group_id = uug.user_group_id left join user_settings as ugl on ugl.user_id = u.user_id and ugl.setting_name = ‘givenName’ and ugl.locale = ‘en_US’ left join user_settings as ugpl on ugpl.user_id = u.user_id and ugpl.setting_name = ‘givenName’ and ugpl.locale = ‘en_US’ left join user_settings as ufl on ufl.user_id = u.user_id and ufl.setting_name = ‘familyName’ and ufl.locale = ‘en_US’ left join user_settings as ufpl on ufpl.user_id = u.user_id and ufpl.setting_name = ‘familyName’ and ufpl.locale = ‘en_US’ left join user_group_stage as ugs on uug.user_group_id = ugs.user_group_id left join review_assignments as ra on u.user_id = ra.reviewer_id where ug.context_id = 8 and ug.role_id in (4096) and u.disabled = 0 and ugs.stage_id = 3 group by u.user_id, user_given, user_family order by u.user_id desc LIMIT 30 OFFSET 0
