[OJS 3.3.0.8] User search very slow

ramon · January 1, 2022, 4:56pm

Hello everyone,

Description of issue or problem I’m having:
My user search is terribly slow! Especially when searching for a specific user.
Searching directly in the database is much faster (command-line), but I noticed a few fake users registered with the same givenName. I’m not sure if those records are causing trouble in the query, but I can’t delete them directly without probably corrupting my database.

Steps I took leading up to the issue:
While I have direct access to the database, I can search required data. However, I need a solution to delete undesired users, faster than a merge as the search doesn’t end.
And I posted the problem here for a solution.

What I tried to resolve the issue:
Nothing yet.

Application Version - e.g., OJS 3.1.2:
OJS 3.3.0.8

Additional information, such as screenshots and error log messages if applicable:
First access to users list is quite fast.

Searching for any specific user, specially this Aurora doesn’t end!

Database search for givenName

Couple of fake users records

kerimsarigul · January 1, 2022, 10:34pm

It is a very serious problem to be able to delete unnecessary users, spam users, unauthorized users. I hope someone finds a solution to this problem in 2022.

abadan · January 3, 2022, 3:04pm

Please see:

github.com/pkp/pkp-lib

[OJS] 3.2.1 - Slow or not loading search in "Add Reviewer" form

opened 09:50AM - 23 Jul 20 UTC

closed 08:45PM - 10 Dec 21 UTC

KRONWALLED1134

Dev Housekeeping

Hi We recently upgraded our OJS installation to 3.2.1. Everything works fine… except for the Add reviewer form. The users reported that it loads performs searches very slow or not at all. I checked the database then and saw very long running queries (about 14-15 minutes) regarding the searches in the Add Reviewer form. Example (Search for 'he' in the Add reviewer form) `SHOW FULL PROCESSLIST;` ``` +-----+------+-----------+------+---------+------+----------+-----------------------+| Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+----------+-----------------------+ | 359 | xxx | 172.20.0.2:58262 | xxx | Query | 2 | Sending data | 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 `user_settings` as `us` on `u`.`user_id` = `us`.`user_id` left join `user_interests` as `ui` on `u`.`user_id` = `ui`.`user_id` left join `controlled_vocab_entry_settings` as `cves` on `ui`.`controlled_vocab_entry_id` = `cves`.`controlled_vocab_entry_id` left join `review_assignments` as `ra` on `u`.`user_id` = `ra`.`reviewer_id` where `ug`.`context_id` = 1 and `ug`.`role_id` in (4096) and `u`.`disabled` = 0 and `ugs`.`stage_id` = 3 and (lower(u.username) LIKE '%he%' or lower(u.email) LIKE '%he%' or (`us`.`setting_name` = 'givenName' and lower(us.setting_value) LIKE '%he%') or (`us`.`setting_name` = 'familyName' and lower(us.setting_value) LIKE '%he%') or (`us`.`setting_name` = 'affiliation' and lower(us.setting_value) LIKE '%he%') or (`us`.`setting_name` = 'biography' and lower(us.setting_value) LIKE '%he%') or (`us`.`setting_name` = 'orcid' and lower(us.setting_value) LIKE '%he%') or lower(cves.setting_value) LIKE '%he%') group by `u`.`user_id`, `user_given`, `user_family` order by `u`.`user_id` desc LIMIT 0,30 | +-----+------+-----------+------+---------+------+----------+-----------------------+ ``` So the query is already running for about 840 seconds right now. It finished about 30 seconds later. I then run an EXPLAIN on this query. `EXPLAIN <query>` ``` +----+--------------------+-------+------------+--------+----------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+----------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------------+------+----------+----------------------------------------------+ | 1 | PRIMARY | ug | NULL | ref | PRIMARY,user_groups_user_group_id,user_groups_context_id,user_groups_role_id | user_groups_role_id | 8 | const | 1 | 94.44 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | ugs | NULL | ref | user_group_stage_user_group_id,user_group_stage_stage_id | user_group_stage_user_group_id | 8 | xxx.ug.user_group_id | 2 | 19.35 | Using where; Using index | | 1 | PRIMARY | uug | NULL | ref | user_user_groups_pkey,user_user_groups_user_group_id,user_user_groups_user_id | user_user_groups_user_group_id | 8 | xxx.ug.user_group_id | 685 | 100.00 | Using where; Using index | | 1 | PRIMARY | u | NULL | eq_ref | PRIMARY | PRIMARY | 8 | xxx.uug.user_id | 1 | 10.00 | Using where | | 1 | PRIMARY | ugl | NULL | ref | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey | 819 | xxx.uug.user_id,const,const | 1 | 100.00 | NULL | | 1 | PRIMARY | ugpl | NULL | ref | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey | 819 | xxx.uug.user_id,const,const | 1 | 100.00 | NULL | | 1 | PRIMARY | ufl | NULL | ref | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey | 819 | xxx.uug.user_id,const,const | 1 | 100.00 | NULL | | 1 | PRIMARY | ufpl | NULL | ref | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey | 819 | xxx.uug.user_id,const,const | 1 | 100.00 | NULL | | 1 | PRIMARY | us | NULL | ref | user_settings_pkey,user_settings_user_id | user_settings_pkey | 8 | xxx.uug.user_id | 3 | 100.00 | NULL | | 1 | PRIMARY | ui | NULL | ref | u_e_pkey | u_e_pkey | 8 | xxx.uug.user_id | 4 | 100.00 | Using index | | 1 | PRIMARY | cves | NULL | ref | c_v_e_s_pkey,c_v_e_s_entry_id | c_v_e_s_pkey | 8 | xxx.ui.controlled_vocab_entry_id | 1 | 100.00 | Using where | | 1 | PRIMARY | ra | NULL | ref | review_assignments_reviewer_id,review_assignments_reviewer_review | review_assignments_reviewer_id | 8 | xxx.uug.user_id | 26 | 100.00 | NULL | | 6 | DEPENDENT SUBQUERY | ra | NULL | ref | review_assignments_reviewer_id,review_assignments_reviewer_review | review_assignments_reviewer_id | 8 | func | 26 | 90.00 | Using where | | 5 | DEPENDENT SUBQUERY | ra | NULL | ref | review_assignments_reviewer_id,review_assignments_reviewer_review | review_assignments_reviewer_id | 8 | func | 26 | 100.00 | NULL | | 4 | DEPENDENT SUBQUERY | ra | NULL | ref | review_assignments_reviewer_id,review_assignments_reviewer_review | review_assignments_reviewer_id | 8 | func | 26 | 100.00 | NULL | | 3 | DEPENDENT SUBQUERY | ra | NULL | ref | review_assignments_reviewer_id,review_assignments_reviewer_review | review_assignments_reviewer_id | 8 | func | 26 | 100.00 | NULL | | 2 | DEPENDENT SUBQUERY | ra | NULL | ref | review_assignments_reviewer_id,review_assignments_reviewer_review | review_assignments_reviewer_id | 8 | func | 26 | 100.00 | NULL | +----+--------------------+-------+------------+--------+----------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------------+------+----------+----------------------------------------------+ ``` Stats: ``` mysql> select count(*) from review_assignments; +----------+ | count(*) | +----------+ | 939 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from users; +----------+ | count(*) | +----------+ | 4557 | +----------+ 1 row in set (0.00 sec) ``` If I can provide more information for you just let me know. Thanks in advance and best regards

ramon · January 6, 2022, 9:39pm

Hello all,

Could HTML inside biography and signature fields, for example, break the search?

Some of this data may have com from previous versions and not necessarly cleaned or correc/valid HTML.

ramon · January 12, 2022, 8:42pm

Hello @abadan ,

My version of classes/services/QueryBuilders/PKPUserQueryBuilder.php is very different…

<?php
/**
 * @file classes/services/QueryBuilders/PKPUserQueryBuilder.php
 *
 * Copyright (c) 2014-2021 Simon Fraser University
 * Copyright (c) 2000-2021 John Willinsky
 * Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
 *
 * @class PKPUserQueryBuilder
 * @ingroup query_builders
 *
 * @brief Submission list Query builder
 */

namespace PKP\Services\QueryBuilders;

use Illuminate\Database\Capsule\Manager as Capsule;
use PKP\Services\QueryBuilders\Interfaces\EntityQueryBuilderInterface;

class PKPUserQueryBuilder implements EntityQueryBuilderInterface {

Code version:      3.3.0.8
Database version:  3.3.0.8
Latest version:    3.3.0.8
Your system is up-to-date

abadan · January 12, 2022, 10:36pm

Hello @ramon .

Yes, it looked like this in the original OJS 3.3.0-8:

github.com

pkp/pkp-lib/blob/1eaf932fc892ad5fac113edc9ad44b5918f42b49/classes/services/queryBuilders/PKPUserQueryBuilder.inc.php

<?php
/**
 * @file classes/services/QueryBuilders/PKPUserQueryBuilder.php
 *
 * Copyright (c) 2014-2021 Simon Fraser University
 * Copyright (c) 2000-2021 John Willinsky
 * Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
 *
 * @class PKPUserQueryBuilder
 * @ingroup query_builders
 *
 * @brief Submission list Query builder
 */

namespace PKP\Services\QueryBuilders;

use Illuminate\Database\Capsule\Manager as Capsule;
use PKP\Services\QueryBuilders\Interfaces\EntityQueryBuilderInterface;

class PKPUserQueryBuilder implements EntityQueryBuilderInterface {

This file has been truncated. show original

And it looks like this after the updates that will come out in OJS 3.3.0-9:

github.com

pkp/pkp-lib/blob/stable-3_3_0/classes/services/queryBuilders/PKPUserQueryBuilder.inc.php

<?php
/**
 * @file classes/services/QueryBuilders/PKPUserQueryBuilder.php
 *
 * Copyright (c) 2014-2021 Simon Fraser University
 * Copyright (c) 2000-2021 John Willinsky
 * Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
 *
 * @class PKPUserQueryBuilder
 * @ingroup query_builders
 *
 * @brief Submission list Query builder
 */

namespace PKP\Services\QueryBuilders;

use Application;
use AppLocale;
use Carbon\Carbon;
use DomainException;

This file has been truncated. show original