Delete tickets

RT Users
1

I need to completely delete tickets from one of our queues. This queue deals
with HR issues so there are privacy legislation considerations: We cannot
store the data even as “dead” tickets.

Is there a way to safely remove tickets from the MySQL DB?

I am using RT 2.13 on Redhat 7.2

Thanks in advance

Fran

2

I need to completely delete tickets from one of our queues. This queue deals
with HR issues so there are privacy legislation considerations: We cannot
store the data even as “dead” tickets.

Is there a way to safely remove tickets from the MySQL DB?

I am using RT 2.13 on Redhat 7.2

Thanks in advance

You can do:

select id from Queues where Name=‘hr’; # Assuming the hr queue is called “hr”
select * from Tickets where Queue=‘11’; # Assuming the first query gave 11

Actually, in the second query, you can use DELETE instead of select.
I accept no responsibility for any damage!!

:slight_smile:

    cheers
   - wash

Odhiambo Washington, wash@wananchi.com . WANANCHI ONLINE LTD (Nairobi, KE) |
http://ns2.wananchi.com/~wash/ . 1ere Etage, Loita Hse, Loita St., |
GSM: (+254) 722 743 223 . # 10286, 00100 NAIROBI |
“Oh My God! They killed init! You Bastards!”
–from a /. post

3

“FR” == Frances Russell frussell@msi.com.au writes:

FR> I need to completely delete tickets from one of our queues. This
FR> queue deals with HR issues so there are privacy legislation
FR> considerations: We cannot store the data even as “dead” tickets.

Mark them as dead, then use the delete dead tickets script. Be sure
to use the updated one posted last week.

If you use Postgres, you can add the foreign keys I posted a couple of
weeks ago and then just do a delete from tickets for dead tickets and
it will cascade down to the other tables.

4

“FR” == Frances Russell frussell@msi.com.au writes:

FR> I need to completely delete tickets from one of our queues. This
FR> queue deals with HR issues so there are privacy legislation
FR> considerations: We cannot store the data even as “dead” tickets.

Mark them as dead, then use the delete dead tickets script. Be sure
to use the updated one posted last week.

If you use Postgres, you can add the foreign keys I posted a couple of
weeks ago and then just do a delete from tickets for dead tickets and
it will cascade down to the other tables.

Can you point me to where I might find the postings you mention?

Sorry if this is a dumb request.

Frances

5

“FR” == Frances Russell frussell@msi.com.au writes:

If you use Postgres, you can add the foreign keys I posted a couple of
weeks ago and then just do a delete from tickets for dead tickets and
it will cascade down to the other tables.

FR> Can you point me to where I might find the postings you mention?

Here’s the full set of indexes I had to add. Somewhere along the line
the primary keys were not added to my DB during the various upgrades,
so if you already have them, you don’t need to recreate them. The
foreign keys, though, are useful for making ticket deletion easy.

My main wish is that a complete set of foreign keys be added to the
schema so that we can clearly see what all the relations are, and
ensure that they are valid at all times.

ALTER TABLE KeywordSelects ADD PRIMARY KEY (id);
ALTER TABLE Attachments ADD PRIMARY KEY (id);
ALTER TABLE Queues ADD PRIMARY KEY (id);
ALTER TABLE Links ADD PRIMARY KEY (id);
ALTER TABLE Groups ADD PRIMARY KEY (id);
ALTER TABLE Watchers ADD PRIMARY KEY (id);
ALTER TABLE ScripConditions ADD PRIMARY KEY (id);
ALTER TABLE Transactions ADD PRIMARY KEY (id);
ALTER TABLE Scrips ADD PRIMARY KEY (id);
ALTER TABLE ACL ADD PRIMARY KEY (id);
ALTER TABLE GroupMembers ADD PRIMARY KEY (id);
ALTER TABLE ObjectKeywords ADD PRIMARY KEY (id);
ALTER TABLE Keywords ADD PRIMARY KEY (id);
ALTER TABLE Users ADD PRIMARY KEY (id);
ALTER TABLE Tickets ADD PRIMARY KEY (id);
ALTER TABLE ScripActions ADD PRIMARY KEY (id);
ALTER TABLE Templates ADD PRIMARY KEY (id);

ALTER TABLE Transactions ADD CONSTRAINT transfk1 FOREIGN KEY (Ticket) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Attachments ADD CONSTRAINT attachfk1 FOREIGN KEY (TransactionID) REFERENCES Transactions(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Watchers ADD CONSTRAINT watchfk1 FOREIGN KEY (Value) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE ObjectKeywords ADD CONSTRAINT objectfk1 FOREIGN KEY (ObjectId) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk1 FOREIGN KEY (LocalTarget) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk2 FOREIGN KEY (LocalBase) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;

6

Hi All,

I’m using RT version 3.4.6 on Fedora Core 5 and all is running and well
except that, from time to time, spam messages get to the machine
(filtering is NOT perfect) and I have a few ‘spam’ tickets open.

I was looking into a way to get ride of those completely (without
archiving them in the database) but so far I haven’t being able to see
anything of that nature.

Is there a way to delete those tickets so that no ‘tracks’ are left
behind? Do I need to do this via MySQL or RT does have some sort of add on
that will take care of this for me?

Thank you
Best regards
David

7

Hi David,On Mon, 10 Sep 2007, Davide wrote:

Is there a way to delete those tickets so that no ‘tracks’ are left
behind? Do I need to do this via MySQL or RT does have some sort of
add on that will take care of this for me?

The answer is in the FAQ :slight_smile:
http://wiki.bestpractical.com/view/FAQ

I have been using RTx-Shredder-0.06 successfully with RT 3.4.5, for
instance. The new version is available at

You probably don’t want to edit the MySQL directly.

HTH,

– Andreas