Delete large attachment

RT Users
1

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

2

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

3

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).From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Gene
LeDuc
Sent: Thursday, June 14, 2007 12:41 PM
To: Tim Hill
Cc: rt-users@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@bestpractical.com

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

4

Hey you guys might want to try the RTx::Shredder extension. You can find it
on cpan: RTx::Shredder - Cleanup RT database - metacpan.org

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.
-JeffFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Jeff Stark
Sent: Thursday, June 14, 2007 1:12 PM
To: Gene LeDuc; Tim Hill
Cc: rt-users@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).

From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Gene
LeDuc
Sent: Thursday, June 14, 2007 12:41 PM
To: Tim Hill
Cc: rt-users@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@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@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.

5

Sorry, I’ve never used the CF types “Upload one file” or “Upload multiple
files”. There aren’t enough disclaimers to cover any advice I might give
about CF Attachments!

Gene

At 10:11 AM 6/14/2007, Jeff Stark wrote:

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

Gene LeDuc, GSEC
Security Analyst
San Diego State University

6

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@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).From: Jeff Platter [mailto:jplatter@vortexit.net]
Sent: Thursday, June 14, 2007 1:23 PM
To: Jeff Stark; ‘Gene LeDuc’; ‘Tim Hill’
Cc: rt-users@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:

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

From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Jeff
Stark
Sent: Thursday, June 14, 2007 1:12 PM
To: Gene LeDuc; Tim Hill
Cc: rt-users@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).

From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Gene
LeDuc
Sent: Thursday, June 14, 2007 12:41 PM
To: Tim Hill
Cc: rt-users@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@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@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.

7

At Thursday 6/14/2007 07:42 AM, 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

Tim,

As always I’d recommend using a Perl script rather than monkey with
the database - you can delete a transaction through the RT API:

Here’s the script I use. It needs RT_HOME set as an environment
variable, and RT_HOME/lib directory should be on Perl’s search path.

#!/bin/perl

Deletes a transaction from the database.

use lib $ENV{RT_HOME}.“/etc”;

use RT::Interface::CLI qw (CleanEnv GetCurrentUser);
use RT::User;
use RT::Ticket;

die “Usage: $0 ticket_number transaction_number” if scalar (@ARGV) != 2;
my $tktno = shift @ARGV;
my $transno = shift @ARGV;

CleanEnv();
RT::LoadConfig();
RT::Init();

my $CurrentUser = GetCurrentUser();

my $Ticket = new RT::Ticket($CurrentUser);
$Ticket->Load($tktno);

die “Unknown ticket $tktno” if ! $Ticket->id;

my $Transaction = new RT::Transaction($CurrentUser);
$Transaction->Load($transno);

die “No such transaction $transno” if ! $Transaction->id;

die “Wrong ticket number” unless $Transaction->ObjectType eq
‘RT::Ticket’ && $Transaction->ObjectId == $tktno;

print “Transaction Found:\n”;
print " ID: “.$Transaction->Id.”\n";
print " Type: “.$Transaction->Type.”\n";
print " Created: “.$Transaction->Created.”\n";

print "Do you really want to delete the transaction? [N] ";
$| = 1;
$_ = ;

chomp;

if ($_ && uc($_) eq ‘Y’) {
$Transaction->Delete();
print “Transaction Deleted\n”;
} else {
print “OK - Not Deleting\n”;
}

8

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,
TimOn 14/06/07, Jeff Stark JStark@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@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@vortexit.net]
Sent: Thursday, June 14, 2007 1:23 PM
To: Jeff Stark; ‘Gene LeDuc’; ‘Tim Hill’
Cc: rt-users@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:
RTx::Shredder - Cleanup RT database - metacpan.org

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@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Jeff
Stark
Sent: Thursday, June 14, 2007 1:12 PM
To: Gene LeDuc; Tim Hill
Cc: rt-users@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@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Gene
LeDuc
Sent: Thursday, June 14, 2007 12:41 PM
To: Tim Hill
Cc: rt-users@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

The rt-users Archives

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

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

The rt-users Archives

Community help: http://wiki.bestpractical.com Commercial support:
sales@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.