Can't see tickets after upgrading from 1.16 to 1.18.

KevinTheJedi

That typically means one of two things:

The system is endlessly waiting on a long running SQL query.
The system is encountering a fatal error.

You should enable MySQL slow query logs to see if there is a long running query. If so and I suspect there will be you’ll need to try this and/or optimize your MySQL/MariaDB instance and/or increase the resources on the server and dedicate more to the database server.

If it’s the latter issue then you should see a fatal error in your PHP error logs and or webserver logs. Depending on your host and setup you could have multiple error logs so make sure you check your PHP and webserver configurations to see if there are multiple logs to check.

Cheers.

jbakhos

Thank you Kevin, I am going to try and implement that "hack/fix" you recommended.

Just to ensure I am applying it correctly, I only need to complete the following?

Edit the file in the following directory, "include/class.orm.php", and add the lines 2629 through 2634?

I am not seeing any fatal errors in our PHP or web server logs.

KevinTheJedi

jbakhos

Correct, add those lines but without the + signs at the beginning of each line. Those + signs are a git thing basically showing you those lines were newly added.

Idk if you are using a shared host or running your own server but there could be multiple error log files depending on your configurations. Shared hosts with CPanel typically have an error log in the site directory and I’ve even seen some with additional error logs within subdirectories of the main site directory. You should also check your web server error logs as sometimes they bubble up to that level as well.

Other than that it’d be a slow query which you’d be able to identify by enabling your MySQL slow query logs.

Cheers.

jbakhos

Hi Kevin,

I was able to re-attempt the upgrade today however the hack/fix did not resolve the issue. I was however able to 100% identify a long running query in our MySQL env which confirms what you said above. I can send that log over shortly, just need our security team to allow me to upload it.

I assume this means I will need to optimize our MySQL server, I took a look at the link but most of the info seems to be more related to optimizing the queries over the server itself, could you maybe point me toward a specific chapter or section you recommend for this type of optimization?

Thank you.

KevinTheJedi

jbakhos

Sure:

https://dev.mysql.com/doc/refman/8.4/en/optimizing-server.html

Other than that it’s archiving (deleting) some data and/or increasing hardware (wether it be physical or virtual) + increasing resource usage for MySQL (as in configuring MySQL to use the newly added resources).

Cheers.

jbakhos

KevinTheJedi

Do you have any info on the best way to archive/delete old tickets? I feel like doing it via the web portal would take a long time. Is there maybe a query we could run to archive all tickets before a certain date?

KevinTheJedi

jbakhos

Doing this via the UI is the only recommended way to do it. There is so much related data it'd be insane to write queries matching/finding all related data and removing them. Doing it via the UI triggers all needed cleanup automatically and queues things like orphaned files for deletion.

Cheers.

jbakhos

Thanks for the info Kevin! It seems I've resolved the long query issue. We're not longer seeing the query in the long query log. It seems to be a combo of that fix posted above plus some optimizations to our MySQL server.

However, the tickets still wont load. I'm using IIS 10, web server logs show a 200 response when requesting the tickets and PHP logs are empty.

I'm contemplating starting a fresh DB with no tickets, but I don't want to lose our help topics and filters. Is there a way to import those settings either via the UI or on the backend via the DB?

Any other ideas?

KevinTheJedi

jbakhos

It'd be too difficult to do that as they could include images/attachments and that's insane to match up via manual regex statements, etc. Edit your bootstrap.php file, set display_errors and display_startup_errors to 1, and reload your page to see if any errors are output on the screen. Other than that you'd need to check all other logs (general server logs, webserver error logs, PHP error logs, MySQL/MariaDB error logs, osTicket System Logs, Browser Console logs, etc.) for any related errors.

Cheers.

jbakhos

Would we be able to just import the tables that have our help topics/filters/users into a new DB though? That way we don't need to match anything? This is just a backup solution I am trying to come up with if I can't resolve the issue.

Otherwise, further testing this morning is actually showing the long queries are still there. However, I'm not sure if this is our problem exactly because I am seeing the same long query on our production osticket instance and all of our tickets load fine there. One thing I did notice is that on the production instance (running 1.16) the long query only returns 1 row, vs the testing instance (running 1.18) returns 121740.

Production (1.16)
# Query_time: 3.703216  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1224791
use osticket;
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 (22) AND A2.`state` = 'open' AND A1.`staff_id` IN (22) AND A2.`state` = 'open' AND A1.`staff_id` IN (22) 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 '2025-10-31 00:00:00' AND '2025-10-31 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 (22) AND A2.`state` = 'open' AND A1.`staff_id` IN (22) AND A2.`state` = 'open' AND NOT A1.`staff_id` IN (22) 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 '2025-10-30 00:00:00' AND '2025-10-30 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 (22) 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 '2025-10-27 00:00:00' AND '2025-11-02 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 '2025-10-01 00:00:00' AND '2025-10-31 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 '2025-10-01 00:00:00' AND '2025-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 '2025-01-01 00:00:00' AND '2025-12-31 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q14` FROM `osticket_ticket` A1 JOIN `osticket_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `osticket_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `osticket_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `osticket_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `osticket_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') LEFT JOIN `osticket_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) LEFT JOIN `osticket_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) LEFT JOIN `osticket_department` B0 ON (A4.`object_type` = 'D' AND A4.`object_id` = B0.`id`) LEFT JOIN `osticket_department` B1 ON (A7.`object_type` = 'D' AND A7.`object_id` = B1.`id`) WHERE ((A2.`state` = 'open' AND (A1.`staff_id` = 22 OR A5.`staff_id` = 22 OR A6.`object_type` = 'C' AND A8.`staff_id` = 22)) OR (A2.`state` IN ('open', 'closed') AND (A1.`dept_id` IN (1, 5, 7, 9, 10) OR B0.`id` IN (1, 5, 7, 9, 10))) OR A6.`object_type` = 'C' AND B1.`id` IN (1, 5, 7, 9, 10));


Testing (1.18)
# Query_time: 5.610017  Lock_time: 0.250024 Rows_sent: 121740  Rows_examined: 1467963
use osticket_test;
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 (34) AND A2.`state` = 'open' AND A1.`staff_id` IN (34) AND A2.`state` = 'open' AND A1.`staff_id` IN (34) 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 '2025-10-31 00:00:00' AND '2025-10-31 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 (34) AND A2.`state` = 'open' AND A1.`staff_id` IN (34) AND A2.`state` = 'open' AND NOT A1.`staff_id` IN (34) 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 '2025-10-30 00:00:00' AND '2025-10-30 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 (34) 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 '2025-10-27 00:00:00' AND '2025-11-02 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 '2025-10-01 00:00:00' AND '2025-10-31 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 '2025-10-01 00:00:00' AND '2025-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 '2025-01-01 00:00:00' AND '2025-12-31 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q14` FROM `osticket_ticket` A1 JOIN `osticket_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `osticket_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `osticket_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `osticket_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `osticket_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') LEFT JOIN `osticket_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) LEFT JOIN `osticket_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) LEFT JOIN `osticket_department` B0 ON (A4.`object_type` = 'D' AND A4.`object_id` = B0.`id`) LEFT JOIN `osticket_department` B1 ON (A7.`object_type` = 'D' AND A7.`object_id` = B1.`id`) WHERE ((A2.`state` IN ('open', 'closed') AND (A1.`staff_id` = 34 OR A5.`staff_id` = 34 OR A6.`object_type` = 'C' AND A8.`staff_id` = 34)) OR (A2.`state` IN ('open', 'closed') AND (A1.`dept_id` IN (1, 9, 10) OR B0.`id` IN (1, 9, 10))) OR A6.`object_type` = 'C' AND B1.`id` IN (1, 9, 10)) GROUP BY A1.`ticket_id`;

jbakhos

Good news! I think I've fixed it.

It was a combo of multiple things. MySQL optimizations, mainly increasing our innodb_buffer_pool_size from the default. As well as increasing the memory_limit in the php.ini configuration file. The memory limit in the PHP config was the final change that got the tickets to load. I figured if before we were only sending 1 row vs now sending over 100k we should probably increase the memory_limit on PHP. The reason I didn't do this before was because I didn't see any memory related errors in our PHP logs, you would think it would've logged it?

KevinTheJedi

jbakhos

Not necessarily, these logs can be finicky and not log/point to actual issues. Welcome to the world of debugging my friend.

Cheers.