Finding largest Attachments

RT Users
1

Hey folks,

Anyone have a query to run that shows tickets (Attachments) in physical
size from largest to smallest?

Thanks,

Dave D

2

Hey folks,

Anyone have a query to run that shows tickets (Attachments) in physical
size from largest to smallest?

mysql -e’select length(Content), id from Attachments’ |sort -n

3

Jesse,

Thanks, that makes sense, except the output from Attachments.id
is that same as the number of a ticket that one might search on
through RT?

Kind regards,

On Mon, Aug 16, 2004 at 10:10:57AM -0700, Dave Dennis wrote:

Hey folks,

Anyone have a query to run that shows tickets (Attachments) in physical
size from largest to smallest?

mysql -e’select length(Content), id from Attachments’ |sort -n

Thanks,

Dave D

±------------------------

The rt-users Archives

Be sure to check out the RT wiki at http://wiki.bestpractical.com

–

4

Sorry to bother you all, what I guess is happening is
the database is using “TransactionID” and I’m flailing about
trying to connect that back to a ticket number.

Whats needed is a good way to identify jesse’s output back to
ticket number. The wiki shows generic examples, but specific
would be useful.

will keep trying / post something if I figure it out on my own.

Thanks again.

Jesse,

Thanks, that makes sense, except the output from Attachments.id
is that same as the number of a ticket that one might search on
through RT?

Kind regards,

±------------------------

On Mon, 16 Aug 2004, Jesse Vincent wrote:

On Mon, Aug 16, 2004 at 10:10:57AM -0700, Dave Dennis wrote:

Hey folks,

Anyone have a query to run that shows tickets (Attachments) in physical
size from largest to smallest?

mysql -e’select length(Content), id from Attachments’ |sort -n

Thanks,

Dave D

±------------------------

The rt-users Archives

Be sure to check out the RT wiki at http://wiki.bestpractical.com

–

The rt-users Archives

Be sure to check out the RT wiki at http://wiki.bestpractical.com

5

I think you need to join with the Transactions table:

select length(A.Content), A.id, T.Ticket from Attachments A, Transactions T
where A.TransactionId = T.id order by 1

Steve

At Monday 8/16/2004 01:53 PM, Dave Dennis wrote:

6

Try this :

mysql -e’select length(a.Content), t.ticket from Attachments as a join
Transactions as t on a.transactionid=t.id’ |sort -n

Dave Dennis wrote:

7

Absolutely wonderful! This is what I had needed.

Now I have a list of offending large ticket(s), and their corresponding
Transaction.id .

What is my next step towards cleanup – has anybody written/updated
a script that takes ticket ID as input and follows the wiki procedure
for removing everything from every required table?

Thanks folks, big help!!

I think you need to join with the Transactions table:

select length(A.Content), A.id, T.Ticket from Attachments A, Transactions T
where A.TransactionId = T.id order by 1

Steve

At Monday 8/16/2004 01:53 PM, Dave Dennis wrote:

Jesse,

Thanks, that makes sense, except the output from Attachments.id
is that same as the number of a ticket that one might search on
through RT?

Kind regards,

±------------------------

8

Dave Dennis wrote:

Absolutely wonderful! This is what I had needed.

Now I have a list of offending large ticket(s), and their corresponding
Transaction.id .

What is my next step towards cleanup – has anybody written/updated
a script that takes ticket ID as input and follows the wiki procedure
for removing everything from every required table?
RTx::Shredder is on CPAN and it can do it.
I prepare new release, but it dosn’t contain major fixes.

9

Hi all!

I noticed that there are some differences on the database schema

from 3.0 to 3.2.

What special care should I have to migrate the data? Is there any

tool (like dumpfile-to-rt-3.2)? Or any howto?

Paulo Matos

|Sys & Net Admin | Servi�o de Inform�tica |
|Faculdade de Ci�ncias e Tecnologia | Tel: +351-21-2948596 |
|Universidade Nova de Lisboa | Fax: +351-21-2948548 |
|P-2829-516 Caparica | e-Mail: pjsm@fct.unl.pt |

10

I noticed that there are some differences on the database schema
from 3.0 to 3.2.

What special care should I have to migrate the data? Is there any
tool (like dumpfile-to-rt-3.2)? Or any howto?

The 3.0 to 3.2 upgrade doesn’t require any dump/reload. It just
requires reading the upgrading instructions that come with 3.2, and
following the procedure it outlines.