Stale data in CachedGroupMembers

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

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

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

New reimplementation of rt-validator landed in the repository and will
be shipped with RT 3.8.2. It can resurrect CachedGroupMembers table
from information in other tables.On Thu, Oct 23, 2008 at 2:29 PM, Tim Cutts tjrc@sanger.ac.uk 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


The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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.

New reimplementation of rt-validator landed in the repository and will
be shipped with RT 3.8.2. It can resurrect CachedGroupMembers table
from information in other tables.

Is it safe to run this new rt-validator in “resolve” mode on an older RT
database? In particular we have a version 3.4.2 database that has many
inconsistencies according to rt-validator. Or would it be better to
upgrade RT first?

thanks,
Dave
** Dave Holland ** Systems Support – Infrastructure Management **
** 01223 496923 ** The Sanger Institute, Hinxton, Cambridge, UK **
“It is often easier to not do something dumb than it is to do
something smart.”

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

I made a few tests on 3.6 and think it’s possible to use it with 3.6,
but I am not 100% sure it will work with 3.4.On Wed, Oct 29, 2008 at 1:14 PM, Dave Holland dh3@sanger.ac.uk wrote:

On Mon, Oct 27, 2008 at 08:39:59PM +0300, Ruslan Zakirov wrote:

New reimplementation of rt-validator landed in the repository and will
be shipped with RT 3.8.2. It can resurrect CachedGroupMembers table
from information in other tables.

Is it safe to run this new rt-validator in “resolve” mode on an older RT
database? In particular we have a version 3.4.2 database that has many
inconsistencies according to rt-validator. Or would it be better to
upgrade RT first?

thanks,
Dave

** Dave Holland ** Systems Support – Infrastructure Management **
** 01223 496923 ** The Sanger Institute, Hinxton, Cambridge, UK **
“It is often easier to not do something dumb than it is to do
something smart.”


The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

Best regards, Ruslan.