osTicket (v1.9.8.1)My osTicket database is 350mb and I would like to delete all tickets or attachments from the database.I have deleted the tickets, using the agent portal, but the database filesize didn't change. So I assume deleting a ticket doesn't delete the DB entry.Can I delete all entries in the 'naq_ticket_attachment' to delete the attachments in all tickets?I would like to delete the attachments for old tickets or delete all old tickets.
This is not really an osTicket question, its a MySQL one.http://dev.mysql.com/doc/refman/5.7/en/optimize-table.htmlQ: Can I delete all entries in the 'naq_ticket_attachment' to delete the attachments in all tickets?A: If you already deleted all the tickets, you might as well also delete the attachments. The SQL query to do so is:TRUNCATE naq_ticket_attachment;Personally I would have nuked the attachments and left the tickets but I like historical data and statistics... but that might just be me.
No you're totally right. That's how I would have preferred it too. I'm impatient and self-destructive.I've deleted all 2014 tickets and kept attachments from October 2015 till present.Instead of truncating the table, I'm sorting by date and deleting those dates prior to the first ticket in october 2015. I don't know the sql query for that, so I'm manually doing it.I must be missing something, because the database size only reduced by 50 mb. 350mb - 302mbI read this in another forum post:http://forum.osticket.com/d/discussion//how-to-remove-attachments"TRUNCATE TABLE ost_file""TRUNCATE TABLE ost_file_chunk""TRUNCATE TABLE ost_attachment" "TRUNCATE TABLE ost_ticket_attachment"
WARNINGthese instructions are not complete or guaranteed to be accurate... I would need to look at the database tables to really come up with a complete answer. To do a selective delete before a certain date is a little trickier.This query would get you r list of attachments.SELECT if FROM ost_file WHERE created < '2015-09-01 00';save the resulting list. Then:DELETE FROM ost_file WHERE created < '2015-09-01 00';and using the list you would execute the following:DELETE FROM ost_file_chuck WHERE id=#Where # is from the list above.It might be less time consuming to write a simple script to automate this since depending on how many there are...
You should be able to use phpMyAdmin and run this sql on your osticket database.SELECT * FROM `ost_file` LEFT JOIN `ost_file_chunk` ON ost_file.id = ost_file_chunk.file_id WHERE ost_file.created < '2015-09-01 00You should get a combined list of file links and the related data chunks to delete.Good Luck.
The following queries worked for me on MySQL in order to delete closed tickets and its attachments older than 15 months, hope this be useful for you
DELETE FROM ost_ticket WHERE closed < DATE_SUB(NOW(),INTERVAL 15 MONTH);
DELETE FROM ost_thread WHERE object_type = 'T' AND object_id NOT IN (SELECT ticket_id FROM ost_ticket);
DELETE FROM ost_thread_collaborator WHERE thread_id NOT IN (SELECT id FROM ost_thread);
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 FROM ost_thread_event WHERE thread_id NOT IN (SELECT id FROM ost_thread);
DELETE FROM ost_ticket__cdata WHERE ticket_id NOT IN (SELECT ticket_id FROM ost_ticket);
DELETE FROM ost_task WHERE object_type = 'T' AND object_id NOT IN (SELECT ticket_id FROM ost_ticket);
DELETE FROM ost_task__cdata WHERE task_id NOT IN (SELECT id FROM ost_task);
DELETE FROM ost_attachment WHERE object_id NOT IN (SELECT id FROM ost_thread_entry);
DELETE FROM ost_file WHERE id NOT IN (SELECT file_id FROM ost_attachment);
DELETE FROM ost_file_chunk WHERE file_id NOT IN (SELECT id FROM ost_file);
I do not think that some of those tables existed 6 years ago in v1.9.8.x.
Please do not be a necromancer. 
Killing zombie threads with a head shot.
