Cleanup osTicket tables when a ticket is deleted

Please let our ADS show!

This sites offers only FREE software and it's supported by a few advertisement boxes (no intrusive popups).

  • disable your AdBlocker by adding CoolSoft website to whitelist
  • give the proper cookie consent
  • enable JavaScript for this website

This seconds wait is to let you update your browser configuration...

Ok, I've done the required changes... now show me your content!
!!! Please enable JavaScript !!!

osTicket allows administrator to delete a ticket and (supposedly) all of its related entities.

Sadly I've found that ticket deletion is only partially implemented: ticket events (threads) are left untouched and orphaned.
This leads to wrong statistics in dashboard and, worst, lot of garbage left in database.

Additional tables that must be cleaned up are:

  • ost_thread
  • ost_thread_collaborator
  • ost_thread_entry
  • ost_thread_entry_email
  • ost_thread_event
  • ost_thread_referral

I've temporarily fixed the issue with the SQL script below.
You can run it with your preferred MySQL client (like phpMyAdmin) when needed, or schedule its execution with cron...

# delete orphaned threads
DELETE FROM ost_thread
WHERE object_type = 'T' AND object_id NOT IN (SELECT ticket_id FROM ost_ticket);
# delete orphaned thread collaborators
DELETE FROM ost_thread_collaborator
WHERE thread_id NOT IN (SELECT id FROM ost_thread);
# delete orphaned thread entries and emails
DELETE FROM ost_thread_entry
WHERE thread_id NOT IN (SELECT id FROM ost_thread);
DELETE FROM ost_thread_entry_email
WHERE thread_entry_id NOT IN (SELECT id FROM ost_thread_entry);
# delete orphaned thread events
DELETE FROM ost_thread_event
WHERE thread_id NOT IN (SELECT id FROM ost_thread);
# delete orphaned thread referrals
DELETE FROM ost_thread_referral
WHERE thread_id NOT IN (SELECT id FROM ost_thread);

Add new comment