Viewing and searching ticket issue after upgrade from v1.9.1 to v.1.18.1

st512

I have successfully uploaded the sql database dump of osTicket v.1.9.1 in my phpmydamin (XAMPP local server) and used the osticket v1.18.1 files. After browsing the landing page, I have successfully upgraded the osTicket to v1.18.1 from v1.9.1 and everything works perfectly except:

When visiting the agent panel and clicking the tickets menu, the menu is distorted until the page is fully loaded.
The tickets page responds slowly and the page is displayed after some minutes and sometimes it does not show any ticket entries.
When i try to search tickets via advanced search option with keywords related to existing tickets, it does not show any tickets related to it.

KevinTheJedi

st512

That means the system is waiting on the database to return results. It sounds like your database server is too slow to run the latest queries. You can search older posts as this has been asked and answered.

Example:

https://forum.osticket.com/d/104196-slow-ticket-queue-after-update-from-1912

Cheers.

st512

I tried with basic SQL commands provided for MyISAM -> InnoDB, ROW_FORMAT=DYNAMIC and Analyze Tables but with no success and I could not resolve the response time issue. If you can help me further please @KevinTheJedi

KevinTheJedi

st512

Please review MySQL optimization guide linked in that thread or dedicate more resources to MySQL.

Cheers.

st512

KevinTheJedi

I have migrated my osticket 1.18 and imported the sql dump file to the above server configuration and tested the tickets load time. It has drastically improved from loading tickets from 2 minutes to 4-5 secs but the advanced search is not displaying any information while searching the record.

st512

While searching the ticket, no data is fetched even if the record is there in the ticket information and the screen shows like the one attached below:

KevinTheJedi

st512

That means fatal error or long running query. Check your error logs and MySQL slow query logs to see.

Cheers.

st512

KevinTheJedi

The content of my slow query log:

..............................................................................
# Time: 2024-01-26T11:03:10.034600Z
# User@Host: root[root] @ localhost [::1]  Id:   839
# Query_time: 10.592331  Lock_time: 0.000016 Rows_sent: 20  Rows_examined: 683339
use ostmyappdb;
SET timestamp=1706266979;
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.`isanswered` = '0' AS `2GkBZxz`, (SELECT COUNT(R0.`id`) AS `count` FROM `boichd_thread` Q7 JOIN `boichd_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `boichd_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 `boichd_thread` Q7 JOIN `boichd_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `boichd_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `boichd_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, C0.`expire` > NOW() AND NOT C0.`staff_id` = 1 AS `_locked`, (SELECT COUNT(R0.`id`) AS `count` FROM `boichd_thread` Q7 JOIN `boichd_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `boichd_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs, COALESCE(B7.`firstname`, B7.`lastname`, B8.`name`, 'zzz') AS `assignee` FROM `boichd_ticket` A1 JOIN `boichd_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `boichd_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `boichd_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `boichd_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `boichd_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') LEFT JOIN `boichd_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) LEFT JOIN `boichd_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) LEFT JOIN `boichd_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`) LEFT JOIN `boichd_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`) LEFT JOIN `boichd_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`) LEFT JOIN `boichd_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`) LEFT JOIN `boichd_ticket__cdata` B4 ON (A1.`ticket_id` = B4.`ticket_id`) LEFT JOIN `boichd_ticket_priority` B5 ON (B4.`priority` = B5.`priority_id`) LEFT JOIN `boichd_user` B6 ON (A1.`user_id` = B6.`id`) LEFT JOIN `boichd_staff` B7 ON (A1.`staff_id` = B7.`staff_id`) LEFT JOIN `boichd_team` B8 ON (A1.`team_id` = B8.`team_id`) LEFT JOIN `boichd_lock` C0 ON (A1.`lock_id` = C0.`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 (1, 2, 3, 5) OR B0.`team_id` IN (1, 2, 3, 5) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1, 2, 3, 5))) OR A1.`dept_id` IN (1, 2, 3, 4, 5, 6, 13, 14, 16) OR B2.`id` IN (1, 2, 3, 4, 5, 6, 13, 14, 16) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 2, 3, 4, 5, 6, 13, 14, 16)) 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 20;

KevinTheJedi

st512

The only relevant one would be the one I just left in your post above. The others were not related and actually had some potentially sensitive info so I removed them.

As you can see that query doesn’t relate to the change you made so without more info or logs I wouldn’t be able to get to the root of the issue.

Cheers.