Hi. I was using osTicket 1.18.0 and yesterday a problem when accessing the tickets page has started occurring.
The problem is that a SQL query, executed when accessing scp/index.php, hangs and keep executing for indefinite time. The query is the following:
SELECT A1.number AS number,
A1.ticket_id AS ticket_id,
A1.source AS source,
A1.lastupdate AS lastupdate,
B4.subject AS cdata__subject,
A1.isoverdue AS isoverdue,
B6.name AS user__name,
B4.priority AS cdata__priority,
B7.firstname AS staff__firstname,
B7.lastname AS staff__lastname,
B8.name AS team__name,
A1.team_id AS team_id,
A1.dept_id AS dept_id,
C0.name AS dept__name,
A2.id AS status__id,
A1.isanswered = '0' AS 2GkBZxz,
(
SELECT COUNT(R0.id) AS count
FROM ost_thread Q7
JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id)
LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id)
WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS _thread_count,
(
SELECT COUNT(R1.id) AS count
FROM ost_thread Q7
JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id)
LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id)
LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id)
WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS _att_count,
C1.expire > NOW() AND NOT C1.staff_id = 1 AS _locked,
(
SELECT COUNT(R0.id) AS count
FROM ost_thread Q7
JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id)
LEFT JOIN ost_thread_collaborator R0 ON (Q7.id = R0.thread_id)
WHERE Q8.ticket_id = A1.ticket_id) AS _collabs,
COALESCE(B7.lastname, B7.firstname, B8.name, 'zzz') AS assignee
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)
LEFT JOIN ost_ticket__cdata B4 ON (A1.ticket_id = B4.ticket_id)
LEFT JOIN ost_ticket_priority B5 ON (B4.priority = B5.priority_id)
LEFT JOIN ost_user B6 ON (A1.user_id = B6.id)
LEFT JOIN ost_staff B7 ON (A1.staff_id = B7.staff_id)
LEFT JOIN ost_team B8 ON (A1.team_id = B8.team_id)
LEFT JOIN ost_department C0 ON (A1.dept_id = C0.id)
LEFT JOIN ost_lock C1 ON (A1.lock_id = C1.lock_id)
WHERE
A2.state = 'open' AND
(
(
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 (2) OR
B0.team_id IN (2) OR
A6.object_type = 'C'
AND B1.team_id IN (2)
)
) OR
A1.dept_id IN (1, 3, 4, 5, 6, 7, 9, 11, 12, 13) OR
B2.id IN (1, 3, 4, 5, 6, 7, 9, 11, 12, 13) OR
A6.object_type = 'C' AND
B3.id IN (1, 3, 4, 5, 6, 7, 9, 11, 12, 13)
) AND
(
A1.ticket_pid IS NULL OR
A1.flags & 8 != 0
)
GROUP BY A1.ticket_id ORDER BY B5.priority_urgency ASC, A1.lastupdate DESC
LIMIT 50 OFFSET 50
We haven't done any changes in the server, or any system updates, so I guess it should be some problem with stored data in the ticket (charset/rare characters...?) or size of the tables, but the latter should slow down the system little by little.
The rest of the application looks like works like a charm. I can access an individual ticket using its link, or browse the admin pages perfectly fine.
I tried to fix it updating the system (php, mariadb), and restarting, also updating osTickets version, and I find the same problem in 1.18.1 version.
Can someone give me any tip to solve this?
Thanks in advance.
Mario
