[rt-users] Stale data in CachedGroupMembers

[Kenneth Crocker]

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
LBNL

On 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
> 
>