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:

  1. When visiting the agent panel and clicking the tickets menu, the menu is distorted until the page is fully loaded.
  2. The tickets page responds slowly and the page is displayed after some minutes and sometimes it does not show any ticket entries.
  3. 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.



    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

      st512

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

      Cheers.

          a month later

          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.

            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:

              st512

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

              Cheers.

                  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;

                      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.

                        Write a Reply...