Speeding up rt-shredder

G.Booth · July 27, 2010, 8:35pm

OMFG indeed

I’m use to working with much smaller DB’s, this has been
a challenge
to say the least.

We’re using sendmail for RT, but RT itself is sitting
behind
MailMarshall & Exchange and
i believe the MS Admins have set a max message of 10MB
so should be good.

ok are you allowing html email or outlook rtf? in RT_SiteConfig.pm

also this doesnt stop priv users doing whatever they like via the web UI.
we’ve had support teams adding huge iso files to tickets.

shall we go off list, so as not to spam it?

regards
Garry

Mike_Johnson1 · July 27, 2010, 8:35pm

It’s really a safeguard, because not everyone that uses your RT instance is
“smart” enough to prevent loops from happening.

And my example showed 2 queues… but you only need email address and a
“goofy” user for a loop to happen that will cripple the system.

I’ve had end-users reply to an email coming from (which
the Reply-To: on those emails is helpdesk@nosm.ca) and cc helpdesk@nosm.ca.

If you use the ParseNewMessageForTicketCcs, the above can become quite
troublesome without RTAddressRegexp, as it would append helpdesk@nosm.ca as
a Cc email address, which would then email out to helpdesk@nosm.ca whenever
you do correspondence that a Cc would see…

Hope that helps!
Mike.On Tue, Jul 27, 2010 at 4:29 PM, Joseph Spenner joseph85750@yahoo.comwrote:

— On Tue, 7/27/10, Mike Johnson mike.johnson@nosm.ca wrote:

You need to include both, the queue email addresses, AND anything that
forwards email to RT.

That setting prevents RT from sending emails that will “loop” infinitely in
your system.

For example.

RT is setup with the basic autoreply, and reply on correspondence etc.

RT has 2 queues, support@here.com http://mc/compose?to=support@here.comgoes to general, and
it@here.com http://mc/compose?to=it@here.com goes to IT queue.

If it@here.com http://mc/compose?to=it@here.com emails support@here.com http://mc/compose?to=support@here.comthe general queue will autoreply to
it@here.com http://mc/compose?to=it@here.com which will create a ticket
and autoreply to support@here.com http://mc/compose?to=support@here.comwhich will create a ticket and auto-reply to
it@here.com http://mc/compose?to=it@here.com etc etc etc…

Big loop, never ending, blow up RT

If you set the regular expression to support@here.com http://mc/compose?to=support@here.comwhen RT emails out, it’ll filter any emails going to
support@here.com http://mc/compose?to=support@here.com. This will
ensure no loop happens.

SO to recap, RTAddressRegexp has to be a regular expression that ALL email
addresses that send stuff to RT will validate through.

Hope this helps!
Mike.

On Tue, Jul 27, 2010 at 1:35 PM, Joseph Spenner <joseph85750@yahoo.com http://mc/compose?to=joseph85750@yahoo.com wrote:

Upon nearly completing my RT installation, and running:

make initialize-database

I got the message:

==
[Tue Jul 27 17:12:29 2010] [error]: The RTAddressRegexp option is not set
in the config. Not setting this option results in additional SQL queries to
check whether each address belongs to RT or not. It is especially important
to set this option if RT recieves emails on addresses that are not in the
database or config. (/home/packages/rt-3.8.8/sbin/…/lib/RT/Config.pm:343)
Now inserting data
Done inserting data
Done.

If I have 3 queues, ie:
support-help@bob.domain.com http://mc/compose?to=support-help@bob.domain.com
sales-help@bob.domain.com http://mc/compose?to=sales-help@bob.domain.com
it-requests@bob.domain.com http://mc/compose?to=it-requests@bob.domain.com
Do I need to list all those addresses (and any future addresses) in that
RTAddressRegexp option ? Or is this only if I have something at (ie:)
help@jack.somewhere.com http://mc/compose?to=help@jack.somewhere.comforwarding to my RT system in which case I’d want to add:
help@jack.somewhere.com http://mc/compose?to=help@jack.somewhere.com to
the RTAddressRegexp option ?

So this ‘loop’ should only occur if:

auto respond/reply is enabled for the queue defined in the scrips

somehow, an RT queue address (with auto reply enabled) somehow gets
included into another queues ticket

?

Is this potential something new? I’ve been using RT2 since about 2001 and
never seen this happen. Or is it just a safeguard?

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

Mike Johnson
Datatel Programmer/Analyst
Northern Ontario School of Medicine
955 Oliver Road
Thunder Bay, ON P7B 5E1
Phone: (807) 766-7331
Email: mike.johnson@nosm.ca

Joseph_Spenner · July 27, 2010, 9:02pm

If life gives you lemons, keep them-- because hey… free lemons.— On Tue, 7/27/10, Mike Johnson mike.johnson@nosm.ca wrote:

From: Mike Johnson mike.johnson@nosm.ca
Subject: Re: [rt-users] RTAddressRegexp - not clear to me
To: “Joseph Spenner” joseph85750@yahoo.com
Cc: rt-users@lists.bestpractical.com
Date: Tuesday, July 27, 2010, 2:35 PM

It’s really a safeguard, because not everyone that uses your RT instance is “smart” enough to prevent loops from happening.

And my example showed 2 queues… but you only need email address and a “goofy” user for a loop to happen that will cripple the system.

I’ve had end-users reply to an email coming from (which the Reply-To: on those emails is helpdesk@nosm.ca) and cc helpdesk@nosm.ca.

If you use the ParseNewMessageForTicketCcs, the above can become quite troublesome without RTAddressRegexp, as it would append helpdesk@nosm.ca as a Cc email address, which would then email out to helpdesk@nosm.ca whenever you do correspondence that a Cc would see…

Hope that helps!
Mike.

Why couldn’t the application simply query the database to figure out what the queue email addresses are, and don’t permit them to be a recipient?

ronald.higgins · July 27, 2010, 10:26pm

Well, @ 1 ticket deleted per 40 seconds it will only take 324 days to
clean up the DB…

This is a bit crazy.On Tue, Jul 27, 2010 at 10:33 PM, Maxwell Rathbone mrathbone@sagonet.com wrote:

I’d love the ability to export the attachments and store them on the
filesystem instead of in the database. Would certainly cut down the database
size!

I did find this, if it helps anyone:
rt-extension-utils/sbin/rt-extract-attachment at master · bestpractical/rt-extension-utils · GitHub

This allows the extraction or exporting of the attachments. However that
still doesn’t solve the problem of the attachment being in the database, or
how to reference it from the ticket.

I read on this mailing list on 4/29, regarding “Attachments on disk?”:
Hi Thierry,
there is a addon bps wrote but it is not available public at the moment.
Hopefully Jesse will push it out some day.

Torsten

So as it stands, it appears there still is no method of doing this. So if
anyone succeeds in writing a patch, please share it!

Max

On 7/27/2010 4:08 PM, Asif Iqbal wrote:

On Tue, Jul 27, 2010 at 3:54 PM, G.BoothG.Booth@lboro.ac.uk wrote:

On Tue, 27 Jul 2010 21:28:37 +0200 ronald higginsronald.higgins@gmail.com wrote:

Hi Garry,

The RT DB is currently 215GB in size, 1.5 Million Tickets (Attachments
table has 7 million entries).
Yes, this badboy is what I inherited with the new job. I’ve already
built another MySQL DB with the tables partitioned intended for
eventual cut over
but the last few days have seen the current prod db taking a hit
performance wise which is when i really started digging through the
tickets looking for a stop gap until we can cut over, and i’m sure
cutting the Tickets down by 700 000 will make a difference.

By all means, send it All tools of the trade are always gladly
received

OMFG!
Mine is 4GB, 150000 tickets, so it looks like you’ve got some big
attachments!

Think this will be painful!
will send script with explanation when I get back.
I’d seriously look at the attachments table as my RT is 10% of yours in
tickets, but only 2% the size.

Can you zap attachments above 1Gb?

would be nice if the attachments could be converted to link.
we have a ftp server in same network that has lots of space.

Anyone has done anything like this?

If you reply please change the subject too.

regards
Garry

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

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

Mark_Jenks · July 28, 2010, 12:56am

Here is a crazy idea that I’m sure will have some kickback. But you can run more than one version of RT on the same box. Archive the old one and start a new one and learn from the lessons?

-MarkFrom: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of ronald higgins
Sent: Tuesday, July 27, 2010 5:26 PM
To: Maxwell Rathbone
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] speeding up rt-shredder

Well, @ 1 ticket deleted per 40 seconds it will only take 324 days to
clean up the DB…

This is a bit crazy.

Mike_Johnson1 · July 28, 2010, 12:47pm

RT could do that, but that wouldn’t catch any email addresses that forward
stuff to an RT email address, or if using fetchmail, that forward stuff to
an email address RT pulls from.On Tue, Jul 27, 2010 at 5:02 PM, Joseph Spenner joseph85750@yahoo.comwrote:

If life gives you lemons, keep them-- because hey… free lemons.

— On Tue, 7/27/10, Mike Johnson mike.johnson@nosm.ca wrote:

From: Mike Johnson mike.johnson@nosm.ca
Subject: Re: [rt-users] RTAddressRegexp - not clear to me
To: “Joseph Spenner” joseph85750@yahoo.com
Cc: rt-users@lists.bestpractical.com
Date: Tuesday, July 27, 2010, 2:35 PM

It’s really a safeguard, because not everyone that uses your RT instance
is “smart” enough to prevent loops from happening.

And my example showed 2 queues… but you only need email address and a
“goofy” user for a loop to happen that will cripple the system.

I’ve had end-users reply to an email coming from (which the Reply-To: on those emails is helpdesk@nosm.cahttp://mc/compose?to=helpdesk@nosm.ca) and
cc helpdesk@nosm.ca http://mc/compose?to=helpdesk@nosm.ca.

If you use the ParseNewMessageForTicketCcs, the above can become quite
troublesome without RTAddressRegexp, as it would append helpdesk@nosm.cahttp://mc/compose?to=helpdesk@nosm.caas a Cc email address, which would then email out to
helpdesk@nosm.ca http://mc/compose?to=helpdesk@nosm.ca whenever you do
correspondence that a Cc would see…

Hope that helps!
Mike.

Why couldn’t the application simply query the database to figure out what
the queue email addresses are, and don’t permit them to be a recipient?

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

Mike Johnson
Datatel Programmer/Analyst
Northern Ontario School of Medicine
955 Oliver Road
Thunder Bay, ON P7B 5E1
Phone: (807) 766-7331
Email: mike.johnson@nosm.ca

Joseph_Spenner · July 29, 2010, 10:21pm

From: Mike Johnson mike.johnson@nosm.ca
Subject: Re: [rt-users] RTAddressRegexp - not clear to me
To: “Joseph Spenner” joseph85750@yahoo.com
Cc: rt-users@lists.bestpractical.com
Date: Tuesday, July 27, 2010, 11:56 AM

You need to include both, the queue email addresses, AND anything that >forwards email to RT.

That setting prevents RT from sending emails that will “loop” infinitely >in your system.

For example.

RT is setup with the basic autoreply, and reply on correspondence etc.

RT has 2 queues, support@here.com goes to general, and it@here.com goes >to IT queue.

If it@here.com emails support@here.com the general queue will autoreply >to it@here.com which will create a ticket and autoreply to >support@here.com which will create a ticket and auto-reply to it@here.com >etc etc etc…

Big loop, never ending, blow up RT

If you set the regular expression to support@here.com when RT emails out, >it’ll filter any emails going to support@here.com. This will ensure no >loop happens.

SO to recap, RTAddressRegexp has to be a regular expression that ALL >email addresses that send stuff to RT will validate through.

Hope this helps!
Mike.

Mike:
It makes sense, and I suspect my RT2 was vulnerable to this sort of thing. But I’ve run it since 2001 and never had this happen. But I can see how it could. I would imagine people with evil “out of office” responders would/could create tickets as well, but I think most mail servers (even Exchange) is smart enough to only send 1 “out of office” message.

So, what does RT do with the email/ticket if it matches? Does it get silently dropped?

Also, I ~thought~ sendmail/postfix was smart enough to kill loops (or prevent them from starting) based on the information in the email header. Apparently not the case? If RT doesn’t retain any of that information, sendmail/postfix might see it as a new message every time…

Joseph_Spenner · August 2, 2010, 3:28pm

You need to include both, the queue email addresses, AND anything that forwards email to RT.

That setting prevents RT from sending emails that will “loop” infinitely in your system.

For example.

RT is setup with the basic autoreply, and reply on correspondence etc.

RT has 2 queues, support@here.com goes to general, and it@here.com goes to IT queue.

If it@here.com emails support@here.com the general queue will autoreply to it@here.com which will create a ticket and autoreply to support@here.com which will create a ticket and auto-reply to it@here.com etc etc etc…

Big loop, never ending, blow up RT

If you set the regular expression to support@here.com when RT emails out, it’ll filter any emails going to support@here.com. This will ensure no loop happens.

SO to recap, RTAddressRegexp has to be a regular expression that ALL email addresses that send stuff to RT will validate through.

Hope this helps!
Mike.On Tue, Jul 27, 2010 at 1:35 PM, Joseph Spenner joseph85750@yahoo.com wrote:

Upon nearly completing my RT installation, and running:

make initialize-database

I got the message:

[Tue Jul 27 17:12:29 2010] [error]: The RTAddressRegexp option is not set in the config. Not setting this option results in additional SQL queries to check whether each address belongs to RT or not. It is especially important to set this option if RT recieves emails on addresses that are not in the database or config. (/home/packages/rt-3.8.8/sbin/…/lib/RT/Config.pm:343)

Now inserting data
Done inserting data
Done.
If I have 3 queues, ie:
support-help@bob.domain.com
sales-help@bob.domain.com

it-requests@bob.domain.com
Do I need to list all those addresses (and any future addresses) in that RTAddressRegexp option ? Or is this only if I have something at (ie:) help@jack.somewhere.com forwarding to my RT system in which case I’d want to add: help@jack.somewhere.com to the RTAddressRegexp option ?

So this ‘loop’ should only occur if:

auto respond/reply is enabled for the queue defined in the scrips
somehow, an RT queue address (with auto reply enabled) somehow gets included into another queues ticket

?

Is this potential something new? I’ve been using RT2 since about 2001 and never seen this happen. Or is it just a safeguard?

-----Inline Attachment Follows-----

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
After further research, I found information indicating RTAddressRegexp is only needed if the “ParseNewMessageForTicketCcs” option is enabled. Can anyone confirm this?