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?
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
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 [EMAIL PROTECTED] or call us at +1 617 812 0745.
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
