Clean up user table

RT Users
1

A while back I inherited an RT3 installation that used the mail gateway
without any filtering. For several years, the users manually “deleted”
tickets from their queues every day. Naturally, this let to some very
bloated tables in the SQL database. I was able to use RTx-Shredder to
remove all of the “deleted” tickets, which pruned the tickets,
attachments, and transactions tables. However, even with the newest
RTx-shredder and RT3, the “users” module won’t help me delete users who
aren’t associated with tickets.

Under ideal conditions, RT3 performance is still acceptable, but I’m
tired of trying to match up the magical versions. 3.6.5 works with
MySQL 5.0, but whichever 3.6.x we used previously was awfully slow.
With MySQL 4.0, the older RT3 was quick. I’m currently running on
PostgreSQL 8.1, which is not quite as quick as MySQL 5.0 was, but
doesn’t seem to need the “right” release of RT3 to work correctly. BLAH

To restore sanity permanently, I need to clean out the
CachedGroupMembers (254475 rows), GroupMembers (87456 rows), Groups
(95138 rows), Principals (110714 rows), and Users (15574 rows) tables.
(ACLs, too?)

I presume that people need to do this, and its been done before, but I
can’t find instructions on how to do so. Is there something that I need
to fix in order to use RTx-Shredder? Is there a set of SQL commands
that will delete users who aren’t associated with tickets? The farthest
I’ve yet come is this query, which gives me the list of users who don’t
own tickets. However, it doesn’t include users who opened existing
tickets, which it needs to, or other valid relations which should be
preserved:

SELECT DISTINCT
users.id,
users.name,
users.password,
users.comments,
users.emailaddress,
users.realname
FROM users
INNER JOIN principals ON users.id = principals.id
LEFT JOIN tickets ON principals.id = tickets.Owner
WHERE Tickets.id IS NULL;

Any suggestions?

2

Are you having preformance problems related to the users? If not why
clean it up? Do you like to play with fire?On 11/26/07, Gordon Messmer gmessmer@u.washington.edu wrote:

A while back I inherited an RT3 installation that used the mail gateway
without any filtering. For several years, the users manually “deleted”
tickets from their queues every day. Naturally, this let to some very
bloated tables in the SQL database. I was able to use RTx-Shredder to
remove all of the “deleted” tickets, which pruned the tickets,
attachments, and transactions tables. However, even with the newest
RTx-shredder and RT3, the “users” module won’t help me delete users who
aren’t associated with tickets.

Under ideal conditions, RT3 performance is still acceptable, but I’m
tired of trying to match up the magical versions. 3.6.5 works with
MySQL 5.0, but whichever 3.6.x we used previously was awfully slow.
With MySQL 4.0, the older RT3 was quick. I’m currently running on
PostgreSQL 8.1, which is not quite as quick as MySQL 5.0 was, but
doesn’t seem to need the “right” release of RT3 to work correctly. BLAH

To restore sanity permanently, I need to clean out the
CachedGroupMembers (254475 rows), GroupMembers (87456 rows), Groups
(95138 rows), Principals (110714 rows), and Users (15574 rows) tables.
(ACLs, too?)

I presume that people need to do this, and its been done before, but I
can’t find instructions on how to do so. Is there something that I need
to fix in order to use RTx-Shredder? Is there a set of SQL commands
that will delete users who aren’t associated with tickets? The farthest
I’ve yet come is this query, which gives me the list of users who don’t
own tickets. However, it doesn’t include users who opened existing
tickets, which it needs to, or other valid relations which should be
preserved:

SELECT DISTINCT
users.id,
users.name,
users.password,
users.comments,
users.emailaddress,
users.realname
FROM users
INNER JOIN principals ON users.id = principals.id
LEFT JOIN tickets ON principals.id = tickets.Owner
WHERE Tickets.id IS NULL;

Any suggestions?

The rt-users Archives

SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:

If you sign up for a new RT support contract before December 31, we’ll take
up to 20 percent off the price. This sale won’t last long, so get in touch today.
Email us at sales@bestpractical.com or call us at +1 617 812 0745.

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

3

Todd Chapman wrote:

Are you having preformance problems related to the users? If not why
clean it up? Do you like to play with fire?

Yes, I am. RT3 makes multiple queries per ticket displayed that join
the very large principals and cachedgroupmembers tables. It causes
tickets to display somewhat slowly. There’s a small but notable delay
with RT3 3.6.5 and Pg 8.1. The delay was intolerably long with some
earlier minor revision of 3.6 and MySQL 5.0.

I’m tired of running into these problems when I update some component of
the system, and would like to prune out the tens of thousands of “user”
records created by spam through the mail gateway. I’d be grateful for
any help that the list can offer.

4

Try to combine no_tickets option with another option.On Nov 27, 2007 12:29 AM, Gordon Messmer gmessmer@u.washington.edu wrote:

A while back I inherited an RT3 installation that used the mail gateway
without any filtering. For several years, the users manually “deleted”
tickets from their queues every day. Naturally, this let to some very
bloated tables in the SQL database. I was able to use RTx-Shredder to
remove all of the “deleted” tickets, which pruned the tickets,
attachments, and transactions tables. However, even with the newest
RTx-shredder and RT3, the “users” module won’t help me delete users who
aren’t associated with tickets.

Under ideal conditions, RT3 performance is still acceptable, but I’m
tired of trying to match up the magical versions. 3.6.5 works with
MySQL 5.0, but whichever 3.6.x we used previously was awfully slow.
With MySQL 4.0, the older RT3 was quick. I’m currently running on
PostgreSQL 8.1, which is not quite as quick as MySQL 5.0 was, but
doesn’t seem to need the “right” release of RT3 to work correctly. BLAH

To restore sanity permanently, I need to clean out the
CachedGroupMembers (254475 rows), GroupMembers (87456 rows), Groups
(95138 rows), Principals (110714 rows), and Users (15574 rows) tables.
(ACLs, too?)

I presume that people need to do this, and its been done before, but I
can’t find instructions on how to do so. Is there something that I need
to fix in order to use RTx-Shredder? Is there a set of SQL commands
that will delete users who aren’t associated with tickets? The farthest
I’ve yet come is this query, which gives me the list of users who don’t
own tickets. However, it doesn’t include users who opened existing
tickets, which it needs to, or other valid relations which should be
preserved:

SELECT DISTINCT
users.id,
users.name,
users.password,
users.comments,
users.emailaddress,
users.realname
FROM users
INNER JOIN principals ON users.id = principals.id
LEFT JOIN tickets ON principals.id = tickets.Owner
WHERE Tickets.id IS NULL;

Any suggestions?

The rt-users Archives

SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:

If you sign up for a new RT support contract before December 31, we’ll take
up to 20 percent off the price. This sale won’t last long, so get in touch today.
Email us at sales@bestpractical.com or call us at +1 617 812 0745.

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

5

Ruslan Zakirov wrote:

Try to combine no_tickets option with another option.

Ah, I see. Now that you’ve pointed that out, I notice that
“status=disabled” is used as a default filter. I missed that,
previously. If I specify “status,any”, I get many results. I’ll give
this a shot later on. Thanks very much.

$ rtx-shredder --plugin
‘Users=no_tickets,true;limit,20000;status,any;replace_relations,Nobody’