Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

How to Clean your Database

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.

Comments

  • This is not really an osTicket question, its a MySQL one.
    http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

    Q: 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 - 302mb

    I read this in another forum post:
    http://osticket.com/forum/discussion/74169/how-to-remove-attachments

    "TRUNCATE TABLE ost_file"
    "TRUNCATE TABLE ost_file_chunk"
    "TRUNCATE TABLE ost_attachment" 
    "TRUNCATE TABLE ost_ticket_attachment"

  • edited January 2016
    WARNING
    these 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:00:00';

    save the resulting list.  Then:
    DELETE FROM ost_file WHERE created < '2015-09-01 00:00: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 00:00:00

    You should get a combined list of file links and the related data chunks to delete.

    Good Luck.
Sign In or Register to comment.