RT2 database cleanup before rt2->rt3 migration

brianBOFH · September 23, 2004, 2:58pm

RT guru’s:

I would like to clean up my RT2 instance in perparation for migration
to RT3. What is the best way to accomplish this? My biggest concern
is the Users table. We have an enormous amount of “SPAM” users and
only about 120 privileged users. There is also a large amount of dead
Tickets. Any suggestions are appreciated.

Thanks,
Brian

mysql> use rt2;
Database changed
mysql> select count() from Users;
| count() |
| 495693 |
1 row in set (0.00 sec)

mysql> select count() from Watchers;
| count() |
| 320520 |
1 row in set (0.00 sec)

mysql> select count() from Tickets;
| count() |
| 377596 |
1 row in set (26.54 sec)

mysql> select count() from Transactions;
| count() |
| 1096803 |
1 row in set (0.00 sec)

mysql> select count(id) from Users;
| count(id) |
| 495694 |
1 row in set (0.00 sec)

mysql> select count(id) from Watchers;
| count(id) |
| 320521 |
1 row in set (0.00 sec)

mysql> select count(id) from Tickets;
| count(id) |
| 377597 |
1 row in set (0.00 sec)

mysql> select count(id) from Transactions;
| count(id) |
| 1096804 |
1 row in set (0.00 sec)

Ruslan_U_Zakirov1 · September 24, 2004, 2:19pm

brianBOFH wrote:

RT guru’s:

I would like to clean up my RT2 instance in perparation for migration
to RT3. What is the best way to accomplish this? My biggest concern
is the Users table. We have an enormous amount of “SPAM” users and
only about 120 privileged users. There is also a large amount of dead
Tickets. Any suggestions are appreciated.
sponsor BestPractical to help you migrate

hire third party to acomplish one of the next tasks or do it yourself:

hack on rt2-to-rt3 tool to skip dead tickets while export. Easy I think,
but it’s not easy to skip users(and all around it!) that has no
relations with live objects. I say object cause ticket is not only one
DB record but many records. also user can be owner or cc for some live
ticket, but have no tickets requested at all, so you should check all
relations to be sure that your DB is consistent.

as far as I know there is purge-dead.pl script for RT2 that remove
tickets. Nobody right now can guaranty that after using it you’ll
happily migrate, so you have to back up DB before using it, then after
migrate you should test RT3 as much as possible.

If you know how to hack on RT2 API then you can recheck code in
purge-dead.pl

You can migrate to RT3 first and then use RTx::Shredder.
But it doesn’t clean up Users table well yet.
But you should put all that dead tickets into RT3 DB first.

Thanks,
Brian

[snip]
Hmm, huge DB.

				Best regards. Ruslan.

Vivek_Khera1 · September 24, 2004, 2:30pm

not only one DB record but many records. also user can be owner or cc
for some live ticket, but have no tickets requested at all, so you
should check all relations to be sure that your DB is consistent.

Before I did my migration, I ensured all data was “clean” by having
proper foreign keys defined for all the relations. I was then able to
delete tickets marked “dead”. Then, since only my ‘privileged’ users
are allowed to own tickets or be admincc/cc on them, I did a left join
on ticket owners and users list and purged any users with NULL ticket
references unless they were privileged (eg, root doesn’t own any
tickets).

Then the migration from rt2 to rt3 went along quite smoothly.

Once again, I call for Jesse to put in the proper FK’s in the official
schema

Vivek Khera, Ph.D.
+1-301-869-4449 x806

smime.p7s (2.42 KB)

Jesse_Vincent · September 24, 2004, 2:34pm

Once again, I call for Jesse to put in the proper FK’s in the official
schema

And how do you deal with “global” things which reference the
non-existent queue “0” or ACLs which reference a different table by a
compound key that needs a transformation or Watcher groups which may or
may not reference a specific row of another table depending on the
values of multiple keys? Every time I’ve tried someone’s suggestions
for enforced FK relationships (including ones I put together myself),
I’ve gotten bitten quite badly. Hell, I couldn’t even drop the mysql
database after setting up relationships, at one point.

Jesse

Ruslan_U_Zakirov1 · September 24, 2004, 2:52pm

Jesse Vincent wrote:

Once again, I call for Jesse to put in the proper FK’s in the official
schema

References on global queue should be NULLs. It’s way like FK works. Or
you should create fake record with id = 0.

And how do you deal with “global” things which reference the
non-existent queue “0” or ACLs which reference a different table by a
compound key that needs a transformation or Watcher groups which may or
ACLs is not fixable yet, but in good DBs it’s can be controlable by
triggers.

may not reference a specific row of another table depending on the
values of multiple keys? Every time I’ve tried someone’s suggestions
for enforced FK relationships (including ones I put together myself),
Also MySQL is buggy a little. It can’t delete records with id/pid
relationships(like in Attachments) if id == pid.
So it’s better to move to NULLs in parentless attachments.

I’ve gotten bitten quite badly. Hell, I couldn’t even drop the mysql
database after setting up relationships, at one point.
yes, you couldn’t cause droping of table is action that break consistency

But still RT has a lot of relations that can be set right now.

brianBOFH · September 24, 2004, 2:52pm

Thanks for the replies. The biggest problem I have seen was obviously
the export/import time of that huge number of users. rt2-to-dumpfile
would croak on a 4GB machine trying to load everything into memory. I
came up with the following solution to trim “unwanted” Users from my
schema.

mysql> rename table Users to Users_pruneme;

mysql> CREATE TABLE Users (
→ id int(11) NOT NULL auto_increment,
→ Name varchar(120) NOT NULL default ‘’,
→ Password varchar(40) default NULL,
→ Comments blob,
→ Signature blob,
→ EmailAddress varchar(120) default NULL,
→ FreeformContactInfo blob,
→ Organization varchar(200) default NULL,
→ Privileged int(11) default NULL,
→ RealName varchar(120) default NULL,
→ Nickname varchar(16) default NULL,
→ Lang varchar(16) default NULL,
→ EmailEncoding varchar(16) default NULL,
→ WebEncoding varchar(16) default NULL,
→ ExternalContactInfoId varchar(100) default NULL,
→ ContactInfoSystem varchar(30) default NULL,
→ ExternalAuthId varchar(100) default NULL,
→ AuthSystem varchar(30) default NULL,
→ Gecos varchar(16) default NULL,
→ HomePhone varchar(30) default NULL,
→ WorkPhone varchar(30) default NULL,
→ MobilePhone varchar(30) default NULL,
→ PagerPhone varchar(30) default NULL,
→ Address1 varchar(200) default NULL,
→ Address2 varchar(200) default NULL,
→ City varchar(100) default NULL,
→ State varchar(100) default NULL,
→ Zip varchar(16) default NULL,
→ Country varchar(50) default NULL,
→ Creator int(11) default NULL,
→ Created datetime default NULL,
→ LastUpdatedBy int(11) default NULL,
→ LastUpdated datetime default NULL,
→ Disabled smallint(6) NOT NULL default ‘0’,
→ PRIMARY KEY (id),
→ UNIQUE KEY Users1 (Name),
→ KEY Users3 (id,EmailAddress),
→ KEY Users4 (EmailAddress)
→ ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into Users_pruneme (Name, EmailAddress) values
(‘OldTickets’,‘oldtickets.blah.com’);
Query OK, 1 row affected (0.00 sec)

mysql> select id from Users_pruneme where Name = ‘OldTickets’;
| id |
| 409851 |
1 row in set (0.00 sec)

mysql> update Tickets set Creator = 409851 where Created <
‘2004-06-01’ and Status in (‘resolved’, ‘dead’);
Query OK, 42553 rows affected (2.33 sec)
Rows matched: 4255

mysql> insert into Users select u.* from Users_pruneme u, Tickets t
where u.id = t.Creator group by u.id;
Query OK, 99006 rows affected (24.89 sec)
Records: 99006 Duplicates: 0 Warnings: 03 Changed: 42553 Warnings: 0

I will try out RTx-shredder once the migration to RT3 is complete.
The rt2-to-dumpfile script is now chugging away. I’ll follow up with
the results.

-BrianOn Fri, 24 Sep 2004 18:19:54 +0400, Ruslan U. Zakirov ruslan.zakirov@acronis.com wrote:

brianBOFH wrote:

RT guru’s:

I would like to clean up my RT2 instance in perparation for migration
to RT3. What is the best way to accomplish this? My biggest concern
is the Users table. We have an enormous amount of “SPAM” users and
only about 120 privileged users. There is also a large amount of dead
Tickets. Any suggestions are appreciated.
sponsor BestPractical to help you migrate

hire third party to acomplish one of the next tasks or do it yourself:

hack on rt2-to-rt3 tool to skip dead tickets while export. Easy I think,
but it’s not easy to skip users(and all around it!) that has no
relations with live objects. I say object cause ticket is not only one
DB record but many records. also user can be owner or cc for some live
ticket, but have no tickets requested at all, so you should check all
relations to be sure that your DB is consistent.

as far as I know there is purge-dead.pl script for RT2 that remove
tickets. Nobody right now can guaranty that after using it you’ll
happily migrate, so you have to back up DB before using it, then after
migrate you should test RT3 as much as possible.

If you know how to hack on RT2 API then you can recheck code in
purge-dead.pl

You can migrate to RT3 first and then use RTx::Shredder.
But it doesn’t clean up Users table well yet.
But you should put all that dead tickets into RT3 DB first.

Thanks,
Brian

[snip]
Hmm, huge DB.
                                    Best regards. Ruslan.