Delete Old Attachments

rsclmumbai

osTicket Version v1.12.3
Web Server Software Apache/2.4.6 (CentOS) PHP/7.3.5
MySQL Version 5.5.60
PHP Version 7.3.5

I'm attempting to write a simple cron script that will delete all old attachments.
I believe I have to delete content from the following tables:
ostjobs_file
ostjobs_file_chunk
ostjobs_attachment

Q1 = SELECTidFROMostjobs_fileWHEREcreated< NOW() - INTERVAL 18 MONTH order by id
This will get me all tickets which are older than 18 months and candidates for deletion.

How do I identify the corresponding records from the other 2 tables:
ostjobs_attachment
idint(10) unsigned NOT NULL AUTO_INCREMENT,object_idint(11) unsigned NOT NULL,typechar(1) NOT NULL,file_idint(11) unsigned NOT NULL,namevarchar(255) DEFAULT NULL,inlinetinyint(1) unsigned NOT NULL DEFAULT '0',langvarchar(16) DEFAULT NULL, PRIMARY KEY (id),

ostjobs_file_chunk
file_idint(11) NOT NULL,chunk_idint(11) NOT NULL,filedatalongblob NOT NULL,

Are there any other tables I should delete records from ?

Thanks~

nortelvoip

I too would be interested in this. The built in cron is not working properly on my database. We have deleted thousands of tickets, most with attachments on them, and the database size never goes down.

rsclmumbai

After reading multiple posts on this topic, this is what I've figured out.

Query1 = SELECT 'id' as 'id2del' FROM 'ost_file' WHERE YOUR-WHERE-CONDITION;
{
QUERY2 = DELETE FROM 'ost_file_chunk' WHERE 'file_id' = 'id2del';
QUERY3 = DELETE FROM 'ost_file' WHERE 'id' = 'id2del';
}

This has worked well but I believe we have to delete records from "ost_attachment" table too.
It has 3 fields on which I can fire the delete query --> id, object_id, file_id.

Can someone suggest which query of the following is correct?
QUERY4 = DELETE FROM 'ost_attachment' WHERE 'id' = 'id2del';
QUERY4 = DELETE FROM 'ost_attachment' WHERE 'object_id' = 'id2del';
QUERY4 = DELETE FROM 'ost_attachment' WHERE 'file_id' = 'id2del';

Is there any other table which needs deleting of records?

Thanks

AllanKikker

Here is what I found so far through trial and error :

ost_ticket.ticket_id = ost_thread.object_id
ost_thread.id = ost_thread_entry.thread_id
ost_thread_entry.id = ost_attachment.object_id
ost_attachment.file_id = ost_file.id
ost_file.id = ost_chunk.file_id

So if I need to delete tickets closed before a certain date, I can get their ticket ID, file and file_chunk info with a query like

SELECT ticket.ticket_id, file.id as file_id, chunk.file_id as chunk_file, chunk.chunk_id as chunk_id FROM 
`ost_ticket` as ticket, 
`ost_thread` as thread,
`ost_thread_entry` as entry,
`ost_attachment` as attachment,
`ost_file` as file,
`ost_file_chunk` as chunk 
WHERE ticket.closed IS NOT NULL and ticket.closed < '2019-01-01' 
and ticket.ticket_id = thread.object_id 
and thread.id = entry.thread_id
and entry.id = attachment.object_id
and attachment.file_id = file.id
and file.id = chunk.file_id 
order by ticket.ticket_id ASC

rsclmumbai

Thanks@AllanKikker
So the final set of queries to delete attachments would be as follows.

Query1 = SELECT 'id' as 'id2del' FROM 'ost_file' WHERE YOUR-WHERE-CONDITION; { QUERY2 = DELETE FROM 'ost_file_chunk' WHERE 'file_id' = 'id2del'; QUERY3 = DELETE FROM 'ost_file' WHERE 'id' = 'id2del'; QUERY4 = DELETE FROM 'ost_attachment' WHERE 'file_id' = 'id2del'; }

Before I go ahead and delete attachments from 100k odd emails, can someone confirm that the above queries are correct and all that we need?

Thanks

Anton2023

Is the script working?

Please tell me how to delete files from messages from the database? It also slows down for storing files in the database.
I just now set up saving to the right directory.

KevinTheJedi

Anton2023

You don’t need to delete them you can simply migrate existing attachments in the database to the configured Filesystem. Please lookup relevant threads that explain how to do this.

Cheers.