Support Tickets: Removing tickets older than 3 years

Owned by HostBill
Oct 25, 2022
1 min read

Using this procedure you can remove all tickets and their relationships for tickets that are older than 3 years (based on ticket creation date).

  • Ensure to take full HostBill database backup before using this procedure
  • You may want to keep mentioned sql backup somewhere safe, in case you'd need to find information from past

  • Run following SQL queries in single session on your HostBill database:

    SET @ticket_ids := (SELECT GROUP_CONCAT(id) FROM hb_tickets WHERE status = 'Closed' AND date < DATE_SUB(NOW(),INTERVAL 3 YEAR));
DELETE FROM hb_ticket_replies WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_ticket_rating WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_ticket_billing_items WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_ticket_subscriptions  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_log  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_notes  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_public  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_tags  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_timers  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_attachments  WHERE FIND_IN_SET(ticket_id, @ticket_ids);
DELETE FROM hb_tickets_emails  WHERE rel='ticket' AND FIND_IN_SET(rel_id, @ticket_ids);
DELETE FROM hb_tickets  WHERE FIND_IN_SET(id, @ticket_ids);