Removing Attachments

GTC

We are currently running version 1.14.1, and our database has been showing warnings that we are about to exceed the storage limit. We have tried a plug-in to remove attachments, as the attachments are most likely what's taking up a majority of the space, but it would not work. What is the best way to query the database to remove all attachments from tickets after a certain date / time period, or what is the best method to do this?

ntozier

There is a communityp ticket archiver plugin (3rd party) that you could try.

Although i would recommend that you take a look at the Attachment::Filesystem plugin put out by the development team. This plugin would let you store attachments on the file syste, isntead of in the DB. Then you can migrate the files from the DB to the filesystem to free up space.

GTC

ntozier

We have looked into the plugin for archiving tickets, but the latest release for the one we looked at was for version 1.11; we got it to show up in the plugins menu but could not get it to do anything. At that point I figured it would be best to manually query the database to remove the attachments, but I have not found an answer in any other posts to do so, so I have been trying to figure out the queries by trial and error - no luck as of yet, however.

GTC

ntozier

I looked around a bit more and found some queries that can clear out attachments from tickets closed before a certain date, but keep the ticket itself. This worked well for both of our installations; I figured I'd let you know in case it is useful info for you.

DELETE FROM ost_file_chunk WHERE ost_file_chunk.file_id IN( SELECT DISTINCT ostt.file_id FROM ost_ticket ost, ost_thread osth, ost_ticket_status ots, ost_thread_entry oste, ost_attachment ostt, ost_file ostf WHERE ost.ticket_id = osth.object_id AND oste.thread_id = osth.id AND oste.id = ostt.object_id AND ostf.id = ostt.file_id AND ots.id = ost.status_id and ots.state not like '%open%' and ost.closed < 'yyyy-mm-dd')

DELETE FROM ost_file WHERE ost_file.id IN( SELECT DISTINCT ostt.file_id FROM ost_ticket ost, ost_thread osth, ost_ticket_status ots, ost_thread_entry oste, ost_attachment ostt WHERE ost.ticket_id = osth.object_id AND oste.thread_id = osth.id AND oste.id = ostt.object_id AND ots.id = ost.status_id and ots.state not like '%open%' and ost.closed < 'yyyy-mm-dd')

DELETE FROM ost_attachment WHERE ost_attachment.object_id IN( SELECT DISTINCT oste.id FROM ost_ticket ost, ost_thread osth, ost_ticket_status ots, ost_thread_entry oste WHERE ost.ticket_id = osth.object_id AND oste.thread_id = osth.id AND ots.id = ost.status_id and ots.state not like '%open%' and ost.closed < 'yyyy-mm-dd')

OPTIMIZE TABLE ost_attachment, ost_file_chunk, ost_file