Tim,
Do NOT delete those rows. The USERS, GROUPS, GROUPMEMBERS,
CACHEDGROUPMEMBERS may have realtionships with PRINCIPALS, ACL,
ATTACHEMENTS (IF there are any attachments sent via email by any of
them), ATTRIBUTES (If they have any saved searches), and, of course,
TICKETS. I found this out the HARD way. I had a bunch of unprivileged
Users I wanted to get rid of and didn’t fully understand those possible
relationships. Now, I check ALL those tables for relationships before I
start blowing them away.
First, I would find the UserID for the User that was supposed to be the
AdminCC of the Queue. Using that UserID, I would run an SQL select
against the GROUPS table like this:
Select *
from GROUPS
where TYPE = ‘UserEquiv’
and INSTANCE = 116;
Where INSTANCE is the UserID. You will see 2 IDs. RT always creates a
“UserEquiv” Id for every user. That’s why when you add a new user, the
IDs always getr bumped by 2 numbers. The UserEquive Id is used by RT for
various memberships. Then look at the GROUPMEMBERS Table:
Select *
from GROUPMEMBERS
where MEMBERID in (116, 117);
AND
select *
from PRINCIPALS
where OBJECTID in (116, 117);
AND
Select *
from ACL
where PRINCIPALID in (116, 117);
AND
select *
from CACHEDGROUPMEMBERS
where MEMBERID in (116, 117);
If I were to remove ANY row from any of these tables without
maintaining the corresponding relationship with the others, it could be
disasterous. Then, I look for any ticket relationships:
select *
from GROUPS;
Here you will see how RT maintains some of these relationships. Notice
how the “DOMAIN” field shows these relationships. For example if the
domain shows “RT::Ticket-Role”, then you can be sure that the INSTANCE
field is showing the ticket number that has a relationship with the
UserID. That’s just one example. If you were to removean ID that had a
relationship with a ticket, then that ticket would be pointing to an ID
that no longer exists. Not good.
Not knowing exactly what your database looks like or what IDs are
missing and what AdminCc’s are missing in action, I can’t tell you
exactly what to do. It could all be resolved by just going to the queue
with the problem and making sure that the Group Rights for that Queue
has rights associated with the various roles.
The main thing I wanted to stress was that "fooling around with mother
nature" can be a very dangerous thing to do. I would try asking Ruslan
or one of the RT DataBase Guru’s about what to do.
Hope this helped.
Kenn
LBNLOn 10/23/2008 4:29 AM, Tim Cutts wrote:
Hi folks,
I’ve got a problem with my RT 3.4.4 instance (yes, I know, I really am
planning to upgrade soon). New tickets entering our main entrypoint
queue are not emailing the right Queue AdminCc people. When I started
poking around in the database to work out why, I discovered that the
CachedGroupMembers table has incorrect data for the Queue AdminCc
group role for the queue in question. Quite apart from wondering how
it got like that, what’s the correct procedure for fixing the data?
If I delete the rows for that particular GroupId from that table, will
it be updated the next time something in RT needs to know about that
group?
Thanks,
Tim