Hi, we’re experiencing this issue with OJS 3.3.0.21.
What happens is that the SQL query I’ve copied below starts running, and MySQL ends up consuming all the CPU. I assume it might be a problem caused by bots ‘overloading’ the server, but suspect there’s an underlying issue. We haven’t been able to reproduce it ourselves.
To solve the problem we need to restart the server.
Do you have any idea what might be causing it?
What should I check to prevent it from happening again?
Thank you very much!
SQL Query
SELECT
o.submission_id,
MAX(s.context_id) AS journal_id,
MAX(i.date_published) AS i_pub,
MAX(p.date_published) AS s_pub,
COUNT(*) AS count
FROM
submissions s
JOIN publications p ON (p.publication_id = s.current_publication_id)
JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’ AND ps.locale=‘’)
JOIN issues i ON (CAST(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)
JOIN submission_search_objects o ON (s.submission_id = o.submission_id)
JOIN journals j ON j.journal_id = s.context_id
LEFT JOIN journal_settings js ON j.journal_id = js.journal_id AND js.setting_name = ‘publishingMode’
NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0, submission_search_object_keywords o1 NATURAL JOIN submission_search_keyword_list k1, submission_search_object_keywords o2 NATURAL JOIN submission_search_keyword_list k2, submission_search_object_keywords o3 NATURAL JOIN submission_search_keyword_list k3, submission_search_object_keywords o4 NATURAL JOIN submission_search_keyword_list k4, submission_search_object_keywords o5 NATURAL JOIN submission_search_keyword_list k5, submission_search_object_keywords o6 NATURAL JOIN submission_search_keyword_list k6, submission_search_object_keywords o7 NATURAL JOIN submission_search_keyword_list k7, submission_search_object_keywords o8 NATURAL JOIN submission_search_keyword_list k8
WHERE
(js.setting_value <> ‘2’ OR
js.setting_value IS NULL) AND j.enabled = 1 AND
s.status = 3 AND
i.published = 1 AND k0.keyword_text = ? AND k1.keyword_text = ? AND o0.object_id = o1.object_id AND o0.pos+1 = o1.pos AND k2.keyword_text = ? AND o0.object_id = o2.object_id AND o0.pos+2 = o2.pos AND k3.keyword_text = ? AND o0.object_id = o3.object_id AND o0.pos+3 = o3.pos AND k4.keyword_text = ? AND o0.object_id = o4.object_id AND o0.pos+4 = o4.pos AND k5.keyword_text = ? AND o0.object_id = o5.object_id AND o0.pos+5 = o5.pos AND k6.keyword_text = ? AND o0.object_id = o6.object_id AND o0.pos+6 = o6.pos AND k7.keyword_text = ? AND o0.object_id = o7.object_id AND o0.pos+7 = o7.pos AND k8.keyword_text = ? AND o0.object_id = o8.object_id AND o0.pos+8 = o8.pos AND i.journal_id = ?
GROUP BY o.submission_id
ORDER BY count DESC
LIMIT 500