[rt-users] Repair CachedGroupMembers? was: 3.8.1 -- Ticket Search doesn't find Requestor?

RT rt at ragweed.net
Wed Sep 10 11:08:11 EDT 2008


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 at 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),

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 = 26 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;
+-----------+--------------+-----------+---------------+---------------+----------+
| Ticket_id | RoleGroup_id | Role_Type | RoleMember_id | PrincipalType
| Name     |
+-----------+--------------+-----------+---------------+---------------+----------+
|        26 |          521 | Requestor |           124 | User
| bubbleman |
+-----------+--------------+-----------+---------------+---------------+----------+
1 row in set (0.00 sec)

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!



More information about the rt-users mailing list