[rt-users] RT2 database cleanup before rt2->rt3 migration

[brianBOFH]

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.

-Brian


On Fri, 24 Sep 2004 18:19:54 +0400, Ruslan U. Zakirov
<ruslan.zakirov at 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.
> 
>