[rt-users] Delete large attachment

[Jeff Stark]

Hi Gene,

What would you recommend for Custom Field Attachments?  They are stored
in the ObjectCustomFieldValues in the LargeContent field, correct?
Would you do the same for that table?

Thanks, 

- Stark

 
The contents of this communication are considered SumTotal Confidential,
and should not be shared with anyone outside of SumTotal, either
electronically or verbally, without the express permission of the
author(s).

-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Gene
LeDuc
Sent: Thursday, June 14, 2007 12:41 PM
To: Tim Hill
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] Delete large attachment

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 

_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com Commercial support:
sales at bestpractical.com


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com