[rt-users] Delete large attachment

[Jeff Stark]

Thanks Jeff, we do have Shredder installed and it works great for
Tickets, etc...but you can't shred Attachments in Custom Fields with it,
so we are left with shredding the ticket or dealing with the horrid
performance of the system with the large attachment... 


 
Jeff Stark, Sr Systems Administrator      
SumTotal Systems, Inc. (Nasdaq; SUMT)                       


OFFICE    +1 919 326 7548
MOBILE  +1 919 622 0418

EMAIL     jstark at sumtotalsystems.com

 
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: Jeff Platter [mailto:jplatter at vortexit.net] 
Sent: Thursday, June 14, 2007 1:23 PM
To: Jeff Stark; 'Gene LeDuc'; 'Tim Hill'
Cc: rt-users at lists.bestpractical.com
Subject: RE: [rt-users] Delete large attachment

Hey you guys might want to try the RTx::Shredder extension. You can find
it on cpan:
http://search.cpan.org/~ruz/RTx-Shredder-0.06/lib/RTx/Shredder.pm

I've installed it and used it before. It works great with tickets and
there is an option in there for attachments as well. There is a WebUI
for it so you don't really need to mess with any SQL directly.

Just a thought. Hope it helps.
-Jeff

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

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

_______________________________________________
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

--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.