<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Todd Chapman wrote:
<blockquote cite="mid20060228234957.GG26666@chaka.net" type="cite">
<pre wrap="">On Tue, Feb 28, 2006 at 06:46:38PM -0500, Jim Faulkner wrote:
</pre>
<blockquote type="cite">
<pre wrap="">On Wed, 1 Mar 2006, Luke Vanderfluit wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I'm afraid I have a similar story, no spam queue (spam is filtered
before it reaches RT), but large amounts of redundant data.
I have a similar size RT installation.
I've installed rtx-shredder and tried that.
See my earlier post of 27Feb2006.
I thought that doing the deletes to mysql directly (that is, not using
the RT api, might be faster) but I'm afraid that isn't necessarily so.
I have created a script that does this and it shows considerably long
processing times.
What needs to be done to delete a ticket is this:
/~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from Tickets where id = <ticketID>
<1>select id from Transactions where ObjectId = <ticketID>;
<4>select id from ObjectCustomFieldValues where ObjectId = <ticketID>;
<3>select id from Links where LocalTarget = <ticketID>;
<2>select id from Attachments where TransactionId = ( for each
transactionid retrieved with <1> )
-delete from Attachments where id in ( for each Attachment retrieved
with <2> )
-delete from Links where id = ( for each link in <3> )
-delete from ObjectCustomFieldValues where id in ( for each
ObjectCustomFieldValue in <4> )
-delete from Transactions where id in ( for each Transaction in <1> )
-delete from Tickets where id = <ticketID>;
Additionally Stuff needs to be deleted from
principals, cachedgroupmembers, groupmembers, groups.
For each ticket:
-delete from principals where
id=groups.id and groups.instance=tickets.id as int
-delete from cachedgroupmembers where groupid=groups.id
and groups.instance=tickets.id
-delete from groupmembers where groupid=groups.id and
groups.instance=tickets.id
-delete from groups where instance=tickets.id
\____________________________________________
There may be more that could be deleted, namely Users(requestors) who
created the ticket etc.
I'm toying with the idea of a script that runs each night and cleans out
1000 tickets or so, I have 90000 tickets to delete in first instance,
after that a regular cleanup of resolved tickets.
Hope this contributes.
Kind regards.
Luke.
</pre>
</blockquote>
<pre wrap="">That helps a lot! I'm in the midst of writing my own tool to clean out
the database, and I was just trying to figure out that exact information.
Thank you very much!
</pre>
</blockquote>
<pre wrap=""><!---->
Why? When your done you will have RTx::Shredder, except not
as well tested and likely to break on some future version of RT.
</pre>
</blockquote>
Not sure whether web-interface invoked actions and RT API processed
requests will be equal in performance to straigh SQL from perl script.<br>
Would have to test both to give objective reply. <br>
I have tried both API and direct sql, both _are_ slow.<br>
<br>
BTW.<br>
The stuff that I put in my script comes from the WIKI.<br>
<a class="moz-txt-link-freetext" href="http://wiki.bestpractical.com/index.cgi?DatabaseAdmin">http://wiki.bestpractical.com/index.cgi?DatabaseAdmin</a><br>
<br>
Kind regards.<br>
Luke<br>
<br>
<blockquote cite="mid20060228234957.GG26666@chaka.net" type="cite">
<pre wrap="">-Todd
</pre>
</blockquote>
<br>
<br>
<pre class="moz-signature" cols="72">--
Luke
</pre>
</body>
</html>