[rt-users] Delete large attachment

Fri Jun 15 04:03:37 EDT 2007

Hi all,

Thanks very much for your responses. I will have a look at the shredder
extension.

I just need to import my rt database into a temporary system before playing
with this as I can't afford our ticketing to go down.

In case of any issue with shredder I might have to try to just try the MySQL
statements given on a test system and see what happens...

Thanks again for your help,
Tim

On 14/06/07, Jeff Stark <JStark at sumtotalsystems.com> wrote:
>
> 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.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070615/305442fb/attachment.htm>