<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Todd Chapman wrote:
<blockquote cite="mid20060301045509.GH26666@chaka.net" type="cite">
<pre wrap="">On Wed, Mar 01, 2006 at 12:19:10PM +1030, Luke Vanderfluit wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Not sure whether web-interface invoked actions and RT API processed
requests will be equal in performance to straigh SQL from perl script.
Would have to test both to give objective reply.
I have tried both API and direct sql, both _are_ slow.
BTW.
The stuff that I put in my script comes from the WIKI.
<a class="moz-txt-link-freetext" href="http://wiki.bestpractical.com/index.cgi?DatabaseAdmin">http://wiki.bestpractical.com/index.cgi?DatabaseAdmin</a>
Kind regards.
Luke
</pre>
</blockquote>
<pre wrap=""><!---->
I would guess that it's the database work that is taking
the most time. You are guessing that it is the script
overhead. It would be better to benchmark Shredder before
you do a bunch of potentially wasted work. Perhaps adding
an index or two would speed things up...
-Todd
</pre>
</blockquote>
<pre wrap="">Bill R. Williams <a class="moz-txt-link-rfc2396E"
href="mailto:brw@etsu.edu"><brw@etsu.edu></a> Wrote:
How about (for massive cleanup)
* Dump the rt3 database (mysqldump -A rt3 >rt3.sql) plus whatever
other switches might be needed to include the IF/DROP CREATE
directives.
* Stop MySQL or at least the RT database.
* Process that dump file with a perl script which drops the things
that associate with the tickets you want to delete -- a filter to
omit the stuff as mentioned in your notes (below).
(massDelete.pl <rt3.sql >rt3clean.sql)
* Start MySQL (if stopped)
* mysql -u rt_user <rt3clean.sql
which will recreate the entire DB without the stuff you deleted.
I know that this sounds like killing a fly with a shotgun, but it
<b class="moz-txt-star"><span class="moz-txt-tag">*</span>might<span
class="moz-txt-tag">*</span></b> actually be the easiest (quickest?) way to go. Of course the
downside is that you would want to stop your RT database unitl you
filtered your dumped .sql, cause we're talking DROP/CREATE every
table. BUT it might not take that long to process.
On the upside, you DB would be nice, clean, and organized.
Just a thought. I'd certainly be interested in your thoughts on the
method. 'Cause I am uncomfortable with data that I can't clean.</pre>
My response:<br>
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
<br>
This is a good approach in theory, however the drawback here is how to
delete stuff, for example the attachements in the dump file.<br>
I have tried editing the dump file. Given that my dump file is 14Gig, I
haven't found an editor that can buffer that size file.<br>
I use vi, it can't do that, or maybe it can but I haven't found out how.<br>
I've tried several other editors which claim to be able to do it, but
none effectively can.<br>
By far the largest proportion of data in the dump file is in the
attachments table. <br>
It would take some experimentation before a watertight script can be
run over the dump file. Given the amount of time it takes to do that,
it would be very cumbersome to test a script. Testing a smaller dump
file may be an option but it wouldn't be wise to apply the outcomes of
that to the larger file and _assume_ that all is well. <br>
<br>
\<small>_____________________________________________________________________<br>
<br>
</small>Kind regards.<br>
<br>
<br>
<br>
<pre class="moz-signature" cols="72">--
Luke
</pre>
</body>
</html>