Dropping the RT database

RT Users
1

OK, I’ve played with RT 3 enough now. I’m ready to roll this thing out to the rest of my world.

I want to get rid of all the test tickets, queues, etc. that I’ve been playing with and start fresh.

Is there an easy way to drop the DB and start from scratch? I know there was an option to use with make, but I I’ve wiped the source tree I originally built from (doh!).

Thanks!

Russ

2

OK, I’ve played with RT 3 enough now. I’m ready to roll this thing out to the rest of my world.

I want to get rid of all the test tickets, queues, etc. that I’ve been playing with and start fresh.

Is there an easy way to drop the DB and start from scratch? I know there was an option to use with make, but I I’ve wiped the source tree I originally built from (doh!).

Thanks!

Russ

Redownload the source and do it again.

Andy Harrison
(full headers for details)

3

Is there an easy way to drop the DB and start from scratch? I know there was an option to use with make, but I I’ve wiped the source tree I originally built from (doh!).

Redownload the source and do it again.

Or just use $RTROOT/sbin/rt-setup-database, which is what the make option calls to do the work anyway.

rt-setup-database --action drop --dba --prompt-for-dba-password

followed by

rt-setup-database --action init --dba --prompt-for-dba-password

ought to do the trick i think (though this is from memory, you understand)

hth

Jody Belka
knew (at) pimb (dot) org

4

How about

/usr/local/rt3/sbin/rt-setup-database --force --action drop --dba YOURDBA --dba-password YOURPASSWORD
/usr/local/rt3/sbin/rt-setup-database --force --action init --dba YOURDBA --dba-password YOURPASSWORD

Regards,
Myra

5

I saw them. But If I drop the database, wouldn’t it remove all my settings for each queues/users/script settings? I only want to remove all tickets and nothing more.

-JonathanFrom: Josiah Ritchie [mailto:jritchie@bible.edu]
Sent: Tue 2/17/2004 10:35 AM
To: Jonathan Chen; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] removing deleted tickets

See the replies to message “Dropping the RT database”. I think that’s what you
want.

JSR/

Jonathan Chen scripted ::

This script is interesting. I’m not a perl programmer but I’m
wondering if it can be modified to delete ALL tickets from ALL queues so
RT would start assigning tickets with #1 again. If it is, how would I
go about it? If there is a script like this already available, please
lead me to one.

Thanks!

-Jonathan
Message: 5
Date: Tue, 17 Feb 2004 09:09:41 +0100
From: Dirk Pape pape-rt@inf.fu-berlin.de
Subject: Re: [rt-users] removing deleted tickets
To: matthew zeier mrz@intelenet.net, rt-users@lists.fsck.com
Message-ID: 2147483647.1077008981@eremix
Content-Type: text/plain; charset=“us-ascii”

Hallo matthew,

–Am Montag, 16. Februar 2004 10:41 Uhr -0800 schrieb matthew zeier
mrz@intelenet.net:

Anyone have a method (script) to remove deleted tickets and all it’s
components from the db?

mine is attached. It works for RT 3 upto 3.0.9. You need to change the
first line to the pah of your perl binary.

Dirk.
-------------- next part --------------
#!/export/perl/bin/perl

Steve Poirier - <A HREF="mailto:steve@inet-technologies.com

mailto:steve@inet-technologies.com ">steve@inet-technologies.com

edited by Tim Allwine <A HREF="mailto:tallwine@oreilly.com

mailto:tallwine@oreilly.com ">tallwine@oreilly.com

to use with RT2 and MySQL

edited by Tony Aiuto tony@ics.com to deal with Links

edited by Jonas Lincoln lincoln@unit.liu.se to work with RT3 and

mysql

edited by Dirk Pape pape-rt@inf.fu-berlin.de to cope with

Role-Groups (RT 3)

use strict;
my $date = shift;
my $ticketnbr = 0;

if ( ! $date ) { $date=/bin/date -I -d "3 months ago"; }
print “$date\n”;

Replace this with your RT_LIB_PATH

use lib “/export/rt3/lib”;

Replace this with your RT_ETC_PATH

use lib “/export/rt3/etc”;

use RT::Interface::CLI qw(CleanEnv loc
GetCurrentUser GetMessageContent);
use RT::Group;
use RT;

#Clean out all the nasties from the environment
CleanEnv();

#Load etc/config.pm and drop privs
RT::LoadConfig();

#Connect to the database and get RT::SystemUser and RT::Nobody loaded
RT::Init();

(my $x=RT::Handle->new)->Connect;

my $dbh = $x->dbh() or die “Couldn’t connect to RT database”;

my $sql = “SELECT id FROM Tickets WHERE Status=? AND LastUpdated <
'$date'”;
my $sth = $dbh->prepare($sql);
$sth->execute(‘deleted’);

proceed with deletion

my $d_sth = $dbh->prepare(‘SELECT id FROM Transactions where
Ticket=?’);
my $stid = $dbh->prepare(‘DELETE FROM Attachments WHERE
TransactionId=?’);
my $sttrans = $dbh->prepare( ‘DELETE FROM Transactions WHERE
Ticket=?’);
my $stobjects = $dbh->prepare( ‘DELETE FROM TicketCustomFieldValues
WHERE Ticket=?’);
my $stlinks1 = $dbh->prepare( ‘DELETE FROM Links WHERE LocalBase=?’);
my $stlinks2 = $dbh->prepare( ‘DELETE FROM Links WHERE LocalTarget=?’);
my $d_groups = $dbh->prepare( ‘SELECT id FROM Groups WHERE
Domain=“RT::Ticket-Role” AND Instance=?’);
my $grpmbrs = $dbh->prepare( ‘DELETE FROM GroupMembers WHERE
GroupId=?’);
my $groups = $dbh->prepare( ‘DELETE FROM Groups WHERE
Domain=“RT::Ticket-Role” AND Instance=?’);
my $stticket = $dbh->prepare( ‘DELETE FROM Tickets WHERE id=?’);

while ( my ($ticketid) = $sth->fetchrow_array() ) {
print "\nticket $ticketid is dead and last updated before $date.
Deleting: ";
$d_sth->execute($ticketid);

delete Attachments for this ticket

print "Attachments. ";
while ( my ($transid) = $d_sth->fetchrow_array() ) {
$stid->execute($transid);
}

delete Ticket in Transactions table

print "Transactions. ";
$sttrans->execute($ticketid);

delete TicketRole groups for this ticket

print "TicketRole groupmembers. ";
$d_groups->execute($ticketid);
while ( my ($groupid) = $d_groups->fetchrow_array() ) {
$grpmbrs->execute($groupid);
}
print "TicketRole groups. ";
$groups->execute($ticketid);

delete Ticket in ObjectKeywords table

print "ObjectKeywords. ";
$stobjects->execute($ticketid);

delete Ticket from the Links table

print "Links1. ";
$stlinks1->execute($ticketid);
print "Links2. ";
$stlinks2->execute($ticketid);

delete Ticket from the Tickets table

print "Ticket. ";
$stticket->execute($ticketid);

$ticketnbr++;
}
print “\nDone. Deleted $ticketnbr tickets.\n\n”;

exit;

System Administrator
Washington Bible College/Capital Bible Seminary

Sound words, I know, Timothy is to use,
And old wives’ fables he is to refuse
But yet grave Paul him nowhere doth forbid
The use of parables, in which lay hid
That gold, those pearls, and precious stones that were
Worth digging for, and that with greatest care.
– From “The Author’s Apology For His Book”
“The Pilgrim’s Progress” by John Bunyan

6

I saw them. But If I drop the database, wouldn’t it remove all my settings for each queues/users/script settings? I only want to remove all tickets and nothing more.

Tickets, Search Criteria: Subject “doesn’t contain” howmuchwoodwouldawoodchuckchuck

Choose “Update all these tickets at once”

Make Status “Deleted”

Andy Harrison
(full headers for details)

7

Andy Harrison scripted ::

Tickets, Search Criteria: Subject “doesn’t contain”
howmuchwoodwouldawoodchuckchuck

Choose “Update all these tickets at once”

Make Status “Deleted”

Beautifully simple. Thanks!