Searching for users takes forever

Sheeri_Kritzer2 · October 20, 2004, 5:32pm

Hey all,

When I attempt to add a user to a group, it takes forever searching for the
possible members to add. This is because there are so many names to search for
– there are almost 6000 names in our database, since RT makes an entry for each
distinct requestor.

I searched the archives, and a contract with RT support is in the works (it
takes a long long long time, as I work for a university). Anyway, I was
wondering if there’s an easy fix to get RT to include, when adding members to a
group, ONLY users who have access AND rights to RT – otherwise it goes to look
for way too many users.

I also can’t really imagine why anyone would want to add someone into a group,
who doesn’t have rights to access rt. Can someone give me a scenario?

-Sheeri Kritzer
Systems Administrator
University Systems Group
Tufts University
617-627-3925
sheeri.kritzer@tufts.edu

Jesse_Vincent · October 20, 2004, 7:55pm

Hey all,

When I attempt to add a user to a group, it takes forever searching for the
possible members to add. This is because there are so many names to search for
– there are almost 6000 names in our database, since RT makes an entry for each
distinct requestor.

Have you turned on mysql’s logging? If so, what database queries is RT
doing?

-j

I also can’t really imagine why anyone would want to add someone into a group,
who doesn’t have rights to access rt. Can someone give me a scenario?

Managing all your customers by adding them to groups for the
organizations they work for is a common case. That wy you can grant
unprivileged users more rights like “SeeQueue” and “CreateTicket” for
queues that are relevant to them.

Todd_Chapman · October 20, 2004, 8:07pm

Did you just say that you can add an unprivileged user to a group?On Wed, Oct 20, 2004 at 03:55:58PM -0400, Jesse Vincent wrote:

I also can’t really imagine why anyone would want to add someone into a group,
who doesn’t have rights to access rt. Can someone give me a scenario?

Managing all your customers by adding them to groups for the
organizations they work for is a common case. That wy you can grant
unprivileged users more rights like “SeeQueue” and “CreateTicket” for
queues that are relevant to them.

Jesse_Vincent · October 20, 2004, 8:51pm

Did you just say that you can add an unprivileged user to a group?

Yeah. Though the UI may currently prevent it.> On Wed, Oct 20, 2004 at 03:55:58PM -0400, Jesse Vincent wrote:

I also can’t really imagine why anyone would want to add someone into a group,
who doesn’t have rights to access rt. Can someone give me a scenario?

Managing all your customers by adding them to groups for the
organizations they work for is a common case. That wy you can grant
unprivileged users more rights like “SeeQueue” and “CreateTicket” for
queues that are relevant to them.

Todd_Chapman · October 20, 2004, 8:21pm

It’s does prevent it.On Wed, Oct 20, 2004 at 04:51:37PM -0400, Jesse Vincent wrote:

On Wed, Oct 20, 2004 at 04:07:53PM -0400, Todd Chapman wrote:

Did you just say that you can add an unprivileged user to a group?

Yeah. Though the UI may currently prevent it.

On Wed, Oct 20, 2004 at 03:55:58PM -0400, Jesse Vincent wrote:

I also can’t really imagine why anyone would want to add someone into a group,
who doesn’t have rights to access rt. Can someone give me a scenario?

Managing all your customers by adding them to groups for the
organizations they work for is a common case. That wy you can grant
unprivileged users more rights like “SeeQueue” and “CreateTicket” for
queues that are relevant to them.

–

Sheeri_Kritzer2 · October 20, 2004, 10:11pm

Actually, my problem is that the UI is NOT specifically preventing it.

(we’re running 3.0.11, by the way)

The query seems to be this (from the slow queries log):

SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
CachedGroupMembers CachedGroupMembers_2 WHERE ((CachedGroupMembers_2.GroupId =
‘4’)) AND ((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType =
‘User’)) AND ((Principals_1.id = CachedGroupMembers_2.MemberId)) AND ((main.id =
Principals_1.id)or(main.id != ‘1’)or(main.id != ‘10’)) ORDER BY main.Name ASC;

We’re using mysql, and when I run the query from the commandline, there are:
5900 rows in set and it takes 2 min 22.01 sec.

I hope the following is readable. I think that’s all you need (or more than you
need). . .we’re using MySQL 4.0.14.

-Sheeri Kritzer
Systems Administrator
University Systems Group
Tufts University
617-627-3925
sheeri.kritzer@tufts.edu

Quoting Jesse Vincent jesse@bestpractical.com:

Jesse_Vincent · October 20, 2004, 10:16pm

Actually, my problem is that the UI is NOT specifically preventing it.

(we’re running 3.0.11, by the way)

The query seems to be this (from the slow queries log):

SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
CachedGroupMembers CachedGroupMembers_2 WHERE ((CachedGroupMembers_2.GroupId =
‘4’)) AND ((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType =
‘User’)) AND ((Principals_1.id = CachedGroupMembers_2.MemberId)) AND ((main.id =
Principals_1.id)or(main.id != ‘1’)or(main.id != ‘10’)) ORDER BY main.Name ASC;

We’re using mysql, and when I run the query from the commandline, there are:
5900 rows in set and it takes 2 min 22.01 sec.

I hope the following is readable. I think that’s all you need (or more than you
need). . .we’re using MySQL 4.0.14.

Well, we’d also need “EXPLAIN SELECT …” if the problem can’t easily
be solved by upgrading to a current DBIx::SearchBuilder and/or RT
3.0.12.

AND ((main.id = Principals_1.id)or(main.id != ‘1’)or(main.id != ‘10’))

I know that query has been fixed already. Those ORs should be ANDs