Open ticket list not displaying - sometimes - after upgrade to 1.18.3

cmgibbs

We upgraded PHP to 8.4.20 and OSTicket to 1.18.3 a couple weeks ago. Mostly no issues.

One users who made extensive use of "Personal Queues" had issues with that feature - as a matter of fact, that user could never get the ticket list to display, and it appeared that the whole page didn't even load. I was able to get that user's access fixed by forced removal of the Personal Queues definition from the underlying database table.

Come forward a week or so, and now that same issue has cropped up for several users, and Personal Queues aren't part of it. I've poked around for stuff in logs, but can't seem to find anything that tells me what is causing it.

Not sure what to do next. Anyone got ideas?

KevinTheJedi

cmgibbs

You need to look deeper at all possible logs (general server logs, webserver error logs, PHP error logs, MySQL/MariaDB error logs, osTicket System Logs, Browser Console logs, etc.) for related errors. When it doesn't display like that there is 9/10 a fatal error being logged somewhere. Likely PHP/database error logs but sometimes in the webserver error logs and general server logs.

Cheers.

cmgibbs

I've dug fairly deeply in the logs, and I've not been able to identify anything useful. I can tell you that when the list of tickets does not show up, the section "<div id="content"> ... </div>" does not show up in the source at all. When the problem becomes manifest, it's typically when one has the Open tickets header clicked, and have waited until the refresh interval has elapsed. When it happens, if one clicks on the My Tickets header, and then the Open Tickets header, sometimes it will work. Sometimes reloading the page will work. Sometimes, neither of them work.

KevinTheJedi

cmgibbs

That almost certainly means there is a fatal error and it’s crashing before getting to that part. Again, this should be logged somewhere unless you have logging disabled, or it’s going to a different file than you think, etc.

You could modify an admin-protected file, add debug statements, and visit the file to get the log location. This is the most secure but you must know php. You can also try creating a phpinfo file in the site root and visit that to see the site’s specific log location. Just be warned that anyone could access that file and it contains a hell of a lot of php and server info. Although you could gate it with auth and admin checks if you know our codebase.

As a last resort you can try enabling the display error settings via your bootstrap.php file. Set display_errors & display_startup_errors to 1 and reload php-fpm (or your webserver if not running fpm). You can then navigate to the broken area and see if it spits out the errors on the screen. Just be warned this is a huge security risk as it will display errors to everyone that visits the site including unauthenticated users (of course if there are any). Errors can sometimes display full server paths, etc. which is a big no-no. So just be warned and proceed at your own risk.

Cheers.

cmgibbs

This is driving me nuts. I've enabled every error output I can - nothing shows in the web interface when the problem happens. I do note that occasionally CPU hits 100% on the server, and it's the mysql process driving it up. Could that indicate a DB issue?

KevinTheJedi

cmgibbs

Perhaps long running query then that should eventually timeout and leave the page blank. You can enable slow query logs in mysql/mariadb and see. Or show the process list when the issue is occurring to see active queries and execution times. You can run the command multiple times to see if one runs and runs.

Cheers.

cmgibbs

How long does the application wait for SQL response before failing to display? How can I chase those settings?

KevinTheJedi

cmgibbs

Depends on all kinds of settings in your webserver, PHP/PHP-FPM, and your database. Different timeout settings, etc.

Cheers.

KevinTheJedi

cmgibbs

But queries shouldn't be taking long so you need to figure out if it is a long running query, what the query is, and see if it's bugged or if it's simply taking a while to execute. If the latter you'd need to optimize your database and/or add more resources to mysql. There are many guides online that go over different settings, methods, etc.

Cheers.

cmgibbs

Update: it appears that this problem only occurs when an agent has extended access to one or more departments. Does that change the approach to diagnosing the issue?

KevinTheJedi

cmgibbs

No, you still need to find out if it’s due to a long running query, error, etc. Then we can determine next steps.

Cheers.

cmgibbs

here's the long running query - I've set the parameter long_query_time to 4 seconds:

SELECT
  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 A1.`isanswered` = '0' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q2`, 
  COUNT(DISTINCT CASE WHEN A1.`staff_id` IN (7) AND A2.`state` = 'open' AND A1.`staff_id` IN (7) AND A2.`state` = 'open' AND A1.`staff_id` IN (7) AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q6`, 
  COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2026-06-08 00:00:00' AND '2026-06-08 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q9`, 
  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 A1.`staff_id` IN (7) AND A2.`state` = 'open' AND A1.`staff_id` IN (7) AND A2.`state` = 'open' AND NOT A1.`staff_id` IN (7) AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q7`, 
  COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2026-06-07 00:00:00' AND '2026-06-07 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q10`, 
  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.`staff_id` IN (7) AND A2.`state` = 'open' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q5`, 
  COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2026-06-08 00:00:00' AND '2026-06-14 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q11`, 
  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.`closed` BETWEEN '2026-06-01 00:00:00' AND '2026-06-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 '2026-04-01 00:00:00' AND '2026-06-30 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 '2026-01-01 00:00:00' AND '2026-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_department` B0 ON (A4.`object_type` = 'D' AND A4.`object_id` = B0.`id`) 
LEFT JOIN `ost_department` B1 ON (A7.`object_type` = 'D' AND A7.`object_id` = B1.`id`) 
WHERE ((A2.`state` IN ('open', 'closed') AND (A1.`staff_id` = 7 OR A5.`staff_id` = 7 OR A6.`object_type` = 'C' AND A8.`staff_id` = 7)) OR (A2.`state` IN ('open', 'closed') AND (A1.`dept_id` IN (4) OR B0.`id` IN (4))) OR A6.`object_type` = 'C' AND B1.`id` IN (4)) 
GROUP BY A1.`ticket_id`;

KevinTheJedi

cmgibbs

The query you posted is the standard queue count query. It's a single SELECT query that calculates the counts for all queues the agent has access to. It's ran once on login, cached, then re-executed every 5 minutes thereafter (until logout or session kill). I have seen instances with basic resources and setups to instances with beefy setups and resources struggle to run the query. The main offenders are typically low resources, non-allocated resources, and most importantly because they didn't have their databases and/or servers optimized. You can't just install MySQL/MariaDB and expect it to handle everything you throw at it. There are many settings in multiple places to fine tune to ensure the database runs most optimally.

Sorry for the TED Talk.. 😅 You should try this patch to see if it improves your situation. It essentially breaks the single query into multiple queries. This is just a bandaid though. Optimizing your server and database would be the best long-term situation. That can't go without saying, yes, we can do better with optimizing queries, etc. I can tell you that's all coming with v2.0. Using Laravel's Eloquent ORM we can leverage speed and queries not possible with legacy's hand-made ORM. Yes, the ORM we run today was made by a former employee over a decade ago.

Cheers.

cmgibbs

I have applied the patch and am monitoring - seems to have improved somewhat, at least, as I'm no longer seeing new entries in the slow queries log and Users who have experienced the problem indicate OK so far.