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.
