Repair CachedGroupMembers? was: 3.8.1 -- Ticket Search doesn't find Requestor?

RT18 · September 10, 2008, 3:08pm

RT Users -

After some digging, I believe I’ve discovered why some tickets do not
appear when searched by Requestor. It appears that some records are
missing from CachedGroupMembers.

Can anyone recommend a way of querying for the requestor from
GroupMembers and using that to populate CachedGroupMembers?
GroupMembers contains the correct requestors – but that doesn’t seem
to apply to the standard Search functions.

Here’s the dirt – I have two tickets:

Ticket #1 – Does not display a requestor in Simple.html and is not
found using a Requestor search, but does show a Requestor in the
Ticket View (under People).
Ticket #26 – Shows a requestor and is found using a Requestor search.

For Ticket #1 –

mysql> select id from Groups where instance=‘1’ and Domain =
‘RT::Ticket-Role’ and Type = ‘Requestor’;
| id |
| 52 |
1 row in set (0.00 sec)

mysql> select MemberId from GroupMembers where GroupId = ‘52’;
| MemberId |
| 34 |
1 row in set (0.00 sec)

mysql> select EmailAddress from Users where id = ‘34’;
| EmailAddress |
| user@emailaddress.com |

However,

mysql> SELECT DISTINCT t1.id Ticket_id, g2.id RoleGroup_id, g2.Type
Role_Type, cgm3.MemberId RoleMember_id, p4.PrincipalType, u5.Name FROM
Tickets t1, Groups g2, CachedGroupMembers cgm3, Principals p4, Users
u5 WHERE t1.id = 1 AND g2.Domain = ‘RT::Ticket-Role’ AND g2.Instance =
t1.id AND cgm3.GroupId = g2.id AND p4.id = cgm3.MemberId AND
p4.Disabled = 0 AND p4.PrincipalType = ‘User’ AND u5.id = p4.id;
Empty set (0.00 sec)

On ticket #26 (the ticket that is discoverable using search),

Obviously the ticket view uses something more closely related to the
initial query, and the Search functions use CachedGroupMembers.

Any help is appreciated. I’ve modified Googlish.pm to do a fulltext
search on Content, but that’s a /really/ ugly hack.

Thanks so much!

RT18 · September 10, 2008, 4:00pm

All -

I would like to get opinions on repairing my database using SQL. If
there is a more graceful way to accomplish this using the RT perl
modules (RT::CachedGroupMembers, etc) any help is appreciated.

From the “RT Essentials” book (RT Essentials: Managing Your Team and Projects with Request Tracker - Jesse Vincent, Robert Spier, Dave Rolsky, Darren Chamberlain, Richard Foley - Google Books), we find that in
CachedGroupMembers:

id == any incremental value
GroupId == requestor group id
MemberId == MemberId in question (requestor)
Via == usually equal to the CachedGroupMembers id
ImmediateParentId == requestor group id
Disabled == 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
| 81550 | 52 | 34 | 0 | 52 | 0 |

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.On Wed, Sep 10, 2008 at 10:08 AM, RT rt@ragweed.net wrote:

RT Users -

After some digging, I believe I’ve discovered why some tickets do not
appear when searched by Requestor. It appears that some records are
missing from CachedGroupMembers.

Can anyone recommend a way of querying for the requestor from
GroupMembers and using that to populate CachedGroupMembers?
GroupMembers contains the correct requestors – but that doesn’t seem
to apply to the standard Search functions.

RT18 · September 10, 2008, 4:03pm

(For some reason, my message was cut off in the previous post.)

All -

I would like to get opinions on repairing my database using SQL. If
there is a more graceful way to accomplish this using the RT perl
modules (RT::CachedGroupMembers, etc) any help is appreciated.

From the “RT Essentials” book, we find that in
CachedGroupMembers:

id == any incremental value
GroupId == requestor group id
MemberId == MemberId in question (requestor)
Via == usually equal to the CachedGroupMembers id
ImmediateParentId == requestor group id
Disabled == 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
| 81550 | 52 | 34 | 0 | 52 | 0 |

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.

RT18 · September 10, 2008, 4:04pm

From the “RT Essentials” book, we find that in CachedGroupMembers:

id == any incremental value
GroupId == requestor group id
MemberId == MemberId in question (requestor)
Via == usually equal to the CachedGroupMembers id
ImmediateParentId == requestor group id
Disabled == 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
| 81550 | 52 | 34 | 0 | 52 | 0 |

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.

RT18 · September 10, 2008, 4:28pm

All -

I would like to get opinions on repairing my database using SQL. If
there is a more graceful way to accomplish this using the RT perl
modules (RT::CachedGroupMembers, etc) any help is appreciated.

From the “RT Essentials” book (RT Essentials: Managing Your Team and Projects with Request Tracker - Jesse Vincent, Robert Spier, Dave Rolsky, Darren Chamberlain, Richard Foley - Google Books), we find that in
CachedGroupMembers:

id = any incremental value
GroupId = requestor group id
MemberId = MemberId in question (requestor)
Via = usually equal to the CachedGroupMembers id
ImmediateParentId = requestor group id
Disabled = 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
| 81550 | 52 | 34 | 0 | 52 | 0 |

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.

Ruslan_Zakirov1 · September 10, 2008, 4:28pm

And now you should do:
UPDATE CachedGroupMembers SET Via = 81550 WHERE id = 81550;

But major question is "how did it happen that there is no this record
in CachedGroupMembers table?"On Wed, Sep 10, 2008 at 8:00 PM, RT rt@ragweed.net wrote:

All -

I would like to get opinions on repairing my database using SQL. If
there is a more graceful way to accomplish this using the RT perl
modules (RT::CachedGroupMembers, etc) any help is appreciated.

From the “RT Essentials” book (RT Essentials: Managing Your Team and Projects with Request Tracker - Jesse Vincent, Robert Spier, Dave Rolsky, Darren Chamberlain, Richard Foley - Google Books), we find that in
CachedGroupMembers:

id == any incremental value
GroupId == requestor group id
MemberId == MemberId in question (requestor)
Via == usually equal to the CachedGroupMembers id
ImmediateParentId == requestor group id
Disabled == 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
±------±--------±---------±-----±------------------±---------+
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
±------±--------±---------±-----±------------------±---------+
| 81550 | 52 | 34 | 0 | 52 | 0 |
±------±--------±---------±-----±------------------±---------+

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.

On Wed, Sep 10, 2008 at 10:08 AM, RT rt@ragweed.net wrote:

RT Users -

After some digging, I believe I’ve discovered why some tickets do not
appear when searched by Requestor. It appears that some records are
missing from CachedGroupMembers.

Can anyone recommend a way of querying for the requestor from
GroupMembers and using that to populate CachedGroupMembers?
GroupMembers contains the correct requestors – but that doesn’t seem
to apply to the standard Search functions.

Best regards, Ruslan.

RT18 · September 10, 2008, 4:37pm

Thanks so much for the response! It is very much appreciated.

How did it end up there was no record in CachedGroupMembers? To be honest, I
don’t know. I inherited this RT database from someone else, and I believe
they made a number of changes blindly via SQL.

I sincerely appreciate your help. I apologize for the multiple posts – my
messages weren’t appearing in the list archive, so I assumed they weren’t
going out to the list. If list members received multiples, I apologize
profusely!

Hopefully this will help others should they find CachedGroupMembers missing
records.On Wed, Sep 10, 2008 at 11:28 AM, Ruslan Zakirov ruz@bestpractical.comwrote:

And now you should do:
UPDATE CachedGroupMembers SET Via = 81550 WHERE id = 81550;

But major question is “how did it happen that there is no this record
in CachedGroupMembers table?”

On Wed, Sep 10, 2008 at 8:00 PM, RT rt@ragweed.net wrote:

All -

I would like to get opinions on repairing my database using SQL. If
there is a more graceful way to accomplish this using the RT perl
modules (RT::CachedGroupMembers, etc) any help is appreciated.

From the “RT Essentials” book (RT Essentials: Managing Your Team and Projects with Request Tracker - Jesse Vincent, Robert Spier, Dave Rolsky, Darren Chamberlain, Richard Foley - Google Books), we find that in
CachedGroupMembers:

id == any incremental value
GroupId == requestor group id
MemberId == MemberId in question (requestor)
Via == usually equal to the CachedGroupMembers id
ImmediateParentId == requestor group id
Disabled == 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
±------±--------±---------±-----±------------------±---------+
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
±------±--------±---------±-----±------------------±---------+
| 81550 | 52 | 34 | 0 | 52 | 0 |
±------±--------±---------±-----±------------------±---------+

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.

On Wed, Sep 10, 2008 at 10:08 AM, RT rt@ragweed.net wrote:

RT Users -

After some digging, I believe I’ve discovered why some tickets do not
appear when searched by Requestor. It appears that some records are
missing from CachedGroupMembers.

Can anyone recommend a way of querying for the requestor from
GroupMembers and using that to populate CachedGroupMembers?
GroupMembers contains the correct requestors – but that doesn’t seem
to apply to the standard Search functions.

Kenn_Crocker · September 10, 2008, 6:00pm

To whomever (can’t find name in address),

There might be a way IF the person that created the ticket is always

the requestor. IF so, you could walk thru the TICKETS table and take
every CREATOR and check to see if there is a MemberID for that CREATOR
in the CACHEDGROUPMEMBERS Table for the ticket created. IF not, then you
could re-establish the link via CLI OR you could do it manually using
WebUI by printing out a list of those Tickets/Creators and using that
list to go to each affected ticket and remove/add the correct requestor.
I would not recommend re-creating the records with SQL because there may
be ACL issues, etc that RT would normally take care of, hence using CLI.
That’s all I can think of at the moment. Hope this helps.

Kenn
LBNLOn 9/10/2008 9:03 AM, RT wrote:

(For some reason, my message was cut off in the previous post.)

All -

I would like to get opinions on repairing my database using SQL. If
there is a more graceful way to accomplish this using the RT perl
modules (RT::CachedGroupMembers, etc) any help is appreciated.

From the “RT Essentials” book, we find that in
CachedGroupMembers:

id == any incremental value
GroupId == requestor group id
MemberId == MemberId in question (requestor)
Via == usually equal to the CachedGroupMembers id
ImmediateParentId == requestor group id
Disabled == 0

I’m going to work from the values in Ticket #1 (see previous message).

insert into CachedGroupMembers values (‘’, ‘52’, ‘34’, ‘’, ‘52’, ‘0’);

** And Voila! ** The requestor appears in the regular Search as
expected, and is searchable by requestor in Query Builder.

And here is what it looks like in the end:

mysql> select * from CachedGroupMembers where GroupId=‘52’ and
MemberId=‘34’ and ImmediateParentId=‘52’;
±------±--------±---------±-----±------------------±---------+
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
±------±--------±---------±-----±------------------±---------+
| 81550 | 52 | 34 | 0 | 52 | 0 |
±------±--------±---------±-----±------------------±---------+

Should the Disabled status be pulled from Principals? (select Disabled
from Principals where id = ‘34’)

Any opinions on making these modifications is appreciated.

The rt-users Archives

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