Answered or Overdue does not load

jovanelitework

Whenever I access either Answered or Overdue tickets in this part:

It won't load and the page will timeout.
However, when I click "Open" the tickets will load.
Example links that won't load:
/tickets.php?queue=3
/tickets.php?queue=4

Example links that will load:
/tickets.php?queue=2

Any ideas on how to resolve this issue?

Server Information:

KevinTheJedi

jovanelitework

You are using a very old and unsupported version of osTicket. You will need to upgrade to the latest version and retest. If you cannot do so or if you upgrade and still have issues you'll likely need to optimize MySQL and/or dedicate more resources to MySQL so it can run queries better. Here is a related discussion:

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

Cheers.

jovanelitework

KevinTheJedi I've upgraded our OSTicket to 1.15.8. but I still experienced that problem. Can you guide me here on how to resolve this issue?

KevinTheJedi

jovanelitework

1.15.x is also old and unsupported. 1.17.x and 1.18.x are the latest supported major releases.

Also, I said this above:

…if you still have issues you'll likely need to optimize MySQL and/or dedicate more resources to MySQL so it can run queries better. Here is a related discussion:

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

Cheers.

jovanelitework

KevinTheJedi Isn't 1.17.x only works on PHP8?

I've also transformed all of my tables into INNODB and ROW_FORMAT=DYNAMIC. I've also increased innodb_buffer_pool_size and innodb_file_per_table is enabled. I still got a timeout.

Any more suggestions?

KevinTheJedi

jovanelitework

Yes, but anything below PHP 8.0 is no longer supported anyways.

Maybe adjust your webserver and php timeouts?

Cheers.

jovanelitework

Hmmm, I don't think increasing the php timeouts is gonna help.

Since these queries are still running until now:

This is the display when I explain those queries:

Any ideas?

KevinTheJedi

jovanelitework

Then it sounds like you need to run the ANALYZE and OPTIMIZE statements to see if that helps. If not then you can try exporting the database, deleting the database, re-creating the database, and re-importing the database to see if that helps. Lastly, if all that fails then you will simply need to dedicate more resources to MySQL so it performs better/faster.

Cheers.

jovanelitework

KevinTheJedi After I cleaned ost_ticket__cdata, I was able to access the answered and overdue though it is still slow. Should we really upgrade our resources?

Currently, we have 11.3GB of RAM.

ntozier

Out of curiosity... is there some reason why you aren't closing tickets and are leaving them open (Answered)?

jovanelitework

ntozier Good question. I asked our support team and this is their response:

We leave them open waiting for them to reply. We only close spam tickets and the like, others will stay open.

Are there any benefits in closing the tickets?

KevinTheJedi

jovanelitework

If your MySQL is struggling to handle such queries and you cannot optimize MySQL yourself, then yes, that's the only thing you can really do.

Cheers.

jovanelitework

Our server administrators have tried increasing our resources. However, we still cannot access the Answered and Overdue tickets. Today, I did in-depth research regarding how OSTicket builds its query. As a result, I have found this similar issue posted on github (November 24, 2023):
https://github.com/osTicket/osTicket/issues/6634

There are two solutions presented to this issue:

Change LEFT JOIN on ticket__cdata to an INNER JOIN
Setting automatic optimizer search depth in MariaDB config: optimizer_search_depth = 0

Since I cannot change our MariaDB Config, I have found a way to implement the first solution.

On includes/class.ticket.php Line 94, I have added this item:

On includes/class.orm.php Line 2818 under compileJoin() method, I have added another condition here:

Now, I can access Answered and Overdue tickets. It is also loading more quickly than before.