I deleted the contents of the plugins folder to leave just the files in a raw install, removed entries from the <ost_prefix>plugins table and tried but it failed. Assuming the DB might be corrupt or at least in a less than ideal condition after the failed attempts I dropped all the tables, reimported them, re deleted the plug entry, reran the update and it worked within about 5 seconds, which seemed too quick, but it said completed without errors.
OK off to the admin pages to check...
And they won't load, I get an empty response.
Looking in the process table for Mariadb I see a number of processes similar to this
SELECT COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A2.state
= 'open' AND A1.number
LIKE '%vuln%' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q15
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A1.staff_id
IN (1) AND A1.number
LIKE '%vuln%' THEN A1.ticket_id
END) AS q16
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A2.state
= 'open' AND A2.id
IN (9) AND A1.number
LIKE '%Vuln%' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q20
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A2.id
IN (32) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q21
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.topic_id
= 70 AND A1.created
>= '2019-09-30 01:00:00' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q23
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.topic_id
= 70 AND A1.created
>= '2019-09-30 01:00:00' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q24
, COUNT(DISTINCT CASE WHEN A1.topic_id
= 70 AND A1.created
>= '2019-09-30 01:00:00' THEN A1.ticket_id
END) AS q25
, COUNT(DISTINCT CASE WHEN A1.topic_id
= 70 AND A1.created
>= '2019-09-30 01:00:00' AND NOT B5.value
IS NOT NULL THEN A1.ticket_id
END) AS q26
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A2.state
= 'closed' AND A1.number
LIKE '%vuln%' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q27
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A1.isanswered
= '0' AND A2.state
= 'open' AND A1.isanswered
= '0' AND A2.state
= 'open' AND A1.isanswered
= '0' AND A1.topic_id
= 64 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q29
, COUNT(DISTINCT CASE WHEN A1.topic_id
= 64 THEN A1.ticket_id
END) AS q31
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 15 THEN A1.ticket_id
END) AS q32
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A2.state
= 'open' AND A1.dept_id
= 12 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q42
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A1.created
>= '2020-01-01 00:00:00' AND A1.created
<= '2020-12-31 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q44
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A2.state
= 'closed' AND A1.dept_id
= 12 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q54
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A2.state
= 'open' AND A2.id
IN (9) AND A1.number
LIKE '%Vuln%' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q55
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A2.state
= 'open' AND A1.number
LIKE '%Vuln%' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q56
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q1
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND NOT A1.topic_id
= 62 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q17
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A2.id
IN (9) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q18
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.topic_id
= 75 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q33
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.isanswered
= '0' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q2
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.isanswered
= '1' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q3
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.isoverdue
= '1' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q4
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q5
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A2.id
IN (9, 6) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q19
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A1.staff_id
IN (1) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q6
, COUNT(DISTINCT CASE WHEN (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (5, 6, 7, 10) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND NOT A1.staff_id
IN (1) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q7
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q8
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.topic_id
= 75 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q35
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q47
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A1.closed
>= '2021-01-01 00:00:00' AND A1.closed
<= '2022-01-01 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q51
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A1.closed
>= '2020-01-01 00:00:00' AND A1.closed
<= '2021-01-01 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q48
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A1.closed
>= '2019-01-01 00:00:00' AND A1.closed
<= '2020-01-01 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q49
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A1.closed
>= '2018-01-01 00:00:00' AND A1.closed
<= '2019-01-01 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q52
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)$' AND A2.state
= 'closed' AND A1.topic_id
= 14 AND B6.subject
REGEXP '^((?!APT Up).)*$' AND A1.closed
< '2018-01-01 00:00:00' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q50
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2022-11-01 00:00:00' AND '2022-11-01 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q9
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2022-10-31 00:00:00' AND '2022-10-31 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q10
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2022-10-31 00:00:00' AND '2022-11-06 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q11
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2022-11-01 00:00:00' AND '2022-11-30 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q12
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2022-10-01 00:00:00' AND '2022-12-31 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q13
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2022-01-01 00:00:00' AND '2022-12-31 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q14
FROM ost_ticket
A1 JOIN ost_ticket_status
A2 ON (A1.status_id
= A2.id
) LEFT JOIN ost_thread
A3 ON (A3.object_type
= 'T' AND A1.ticket_id
= A3.object_id
) LEFT JOIN ost_thread_referral
A4 ON (A3.id
= A4.thread_id
) LEFT JOIN ost_staff
A5 ON (A4.object_type
= 'S' AND A4.object_id
= A5.staff_id
) LEFT JOIN ost_thread
A6 ON (A1.ticket_id
= A6.object_id
AND A6.object_type
= 'C') LEFT JOIN ost_thread_referral
A7 ON (A6.id
= A7.thread_id
) LEFT JOIN ost_staff
A8 ON (A7.object_type
= 'S' AND A7.object_id
= A8.staff_id
) LEFT JOIN ost_team
B0 ON (A4.object_type
= 'E' AND A4.object_id
= B0.team_id
) LEFT JOIN ost_team
B1 ON (A7.object_type
= 'E' AND A7.object_id
= B1.team_id
) LEFT JOIN ost_department
B2 ON (A4.object_type
= 'D' AND A4.object_id
= B2.id
) LEFT JOIN ost_department
B3 ON (A7.object_type
= 'D' AND A7.object_id
= B3.id
) JOIN ost_form_entry
B4 ON (B4.object_type
= 'T' AND A1.ticket_id
= B4.object_id
) LEFT JOIN ost_form_entry_values
B5 ON (B4.id
= B5.entry_id
) LEFT JOIN ost_ticket__cdata
B6 ON (A1.ticket_id
= B6.ticket_id
) WHERE ((A2.state
= 'open' AND (A1.staff_id
= 1 OR A5.staff_id
= 1 OR A6.object_type
= 'C' AND A8.staff_id
= 1 OR A1.team_id
IN (5, 6, 7, 10) OR B0.team_id
IN (5, 6, 7, 10) OR A6.object_type
= 'C' AND B1.team_id
IN (5, 6, 7, 10))) OR (A2.state
IN ('open', 'closed') AND (A1.dept_id
IN (2, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15, 16) OR B2.id
IN (2, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15, 16))) OR A6.object_type
= 'C' AND B3.id
IN (2, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15, 16))
Which I think is building the ticket count to display against each ticket queue
CPU load is maxed out until I kill these processes. I tried again, got the same results, no change in 15 minutes
How can I get past this? Is there an index that can be rebuilt or a script I can run from the command line to address this or something similar? There are 90k+ tickets in the system. Yes its a lot. Yes it works fine n the old server. Yes we're starting a new ticket system, but thats still at the design stage and I need to carry out the upgrade to get email working again due to the authentication plugin requirement for MS.