Cleanup osTicket tables when a ticket is deleted

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);

Aggiungi un commento

warning

Warning, JavaScript is disabled!

JavaScript is not available, maybe because you disabled it globally into your browser settings or you are using an addon like NoScript.

We do not have any dangerous JavaScript running here.
Please enable JavaScript; if you're using NoScript this image will help you adding CoolSoft to your whitelist.

Thanks for your comprehension and enjoy CoolSoft.