Just posting this solution for anyone who might need it in the future.
Background: Probably as a result of a version upgrade many moons ago, my system stopped purging file attachments (stored in the database). My file_chunk table grew to over 5GB despite having 200 total tickets and maybe 100 FAQ articles.
Unable to figure why the OsTicket was not purging them 24 hours after the ticket was deleted, I had to resort to some MacGuyver SQL.
These are the statements that worked for me. You must run them in this order. Always do your own research and make a database backup before doing anything. This code will ensure that other important images are not accidentally purged, i.e. images in your FAQs or used by the system (such as the osTicket logo, etc).
`
DELETE FROM ost_attachment WHERE type = 'H' AND object_id NOT IN (SELECT id FROM ost_thread_entry);
DELETE FROM ost_file WHERE ft = 'T' AND id NOT IN (SELECT file_id FROM ost_attachment);
DELETE FROM ost_file_chunk WHERE file_id NOT IN (SELECT id FROM ost_file) LIMIT 500;
`
Note that I put a limit of 500 records at a time on the last statement because when I ran it on my local copy, MariaDB went to sleep for quite a while while it processed all the data in one fell swoop. Doing 500 at a time made it painless. Just keep executing the statement until it says that 0 records were affected.
Finally, the table is InnoDB and it will not automatically reclaim the space on the disk. You have to run the mysql Optimize statement on the table. There are all kinds of caveats about having enough free disk space before doing this because the process involves:
- Making a new empty table
- Copy all valid records from the existing table to the new table
- delete the old table and replace it with the new.
So, if you still have a ton of good records, there needs to be enuf space on disk for both tables to momentarily exist.
In my case, I went from 16,486 file_chunk records to 215. So, it was not an issue.
