[rt-users] Delete large attachment

[Gene LeDuc]

Hi Tim,

Best to try this on something harmless before assaulting your production 
database.

Disclaimer - I don't know anything about PostgreSQL other than it's a 
database.  I use MySQL, so my answer probably needs to be translated from 
MySQL to PostgreSQL.  I also use RT 3.6.3, so there might be schema 
differences as well.

Attachments are stored in the Attachments table.  The contents are stored 
in a column called Content.  This column is a longtext (in MySQL), meaning 
it can store up to 4GB.

If you were using MySQL, the following statement should replace all 
attachments greater than a certain size (4MB for example) with an 
"Attachment deleted" statement.  I have no idea what the syntax would be 
using PostgreSQL.

UPDATE Attachments SET Content = 'Attachment deleted due to space 
constraints.', ContentType = 'text/plain', ContentEncoding = 'none' WHERE 
LENGTH(Content) > 4000000;

If you want to do it for specific tickets, you need to get the ticket 
number indirectly.  The Attachments:TransactionId column points to a 
Transactions record whose Transactions:ObjectId column contains the ticket 
number.

Have fun and be careful out there!
Gene

At 04:42 AM 6/14/2007, Tim Hill wrote:
>Hi,
>
>I have a member of staff who has decided to attach docuements to his 
>tickets in bitmap format, unfortunately these bitmap images are between 4 
>and 8 MB in size and they have taken up more space in a day of doing this 
>than the rest of the RT system has taken up in almost a year of operation.
>
>I don't mind if the tickets have to be deleted, they can be recreated 
>easily enough. How can I get rid of these attachments from the RT 
>database? Where are the attachments stored and what are my options for 
>getting rid of them?
>
>I am using the following:
>RT version 3.4.4 from an ubuntu package.
>Server version of dapper drake 6.06 LTS (webmin shows this as 6.06.1)
>Webmin version 1.320
>PostgreSQL version 7.4.12 (With schemas)
>
>Please let me know if you require any other information.
>
>Thanks in advance,
>Tim


-- 
Gene LeDuc, GSEC
Security Analyst
San Diego State University