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: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
