[rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 / Apache2.2.3 /FastCGI

[Ham MI-ID, Torsten Brumm]

Hi Together,
it's me again. Has anyone a idea why this query starts on each BulkUpdate Page? I have just fired up this query in query browser and the result is a big list of 1255 Users, mixed priviledged and unpriviledged users, not all user of our DB are listed and i also cross checked, also users are included which have NEVER created a ticket in the queue i started the bulk update (also have no rights here).

I'm a little lost in space at this moment, any ideas?

Torsten 


Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Dirk Blesius (Stellv.), Alfred Manke, Christian Marnetté (Stellv.),  Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne



-----Urspruengliche Nachricht-----
Von: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] Im Auftrag von Ham MI-ID, Torsten Brumm
Gesendet: Freitag, 23. Januar 2009 10:03
An: rt-users
Betreff: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 / Apache2.2.3 /FastCGI

Hi RT Users and MySQL Gurus,
since some days i have several times the day many queries like this:

Id: 1174494 User: rt_user Host: 10.61.2.98:59549 Db: rt3 Time: 190
Command: Query State: Copying to tmp table

SELECT DISTINCT main.*
  FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals Principals_1 ON (Principals_1.id = main.id) JOIN CachedGroupMembers CachedGroupMembers_2 ON
     (CachedGroupMembers_2.MemberId = Principals_1.id) JOIN Groups Groups_3 ON (Groups_3.id = CachedGroupMembers_2.GroupId)
  WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND
     (ACL_4.RightName = 'OwnTicket') AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain = 'RT::Queue-Role') OR
     (Groups_3.Domain = 'RT::System-Role'))
  ORDER BY main.Name ASC

table       |type    |possible_keys       |key         | ken_len|ref     |  rows|
1           |SIMPLE  |Groups_3            |range       |PRIMARY,|Groups1 |    67|Using where; Using index; Using temporary; Using filesort
1           |SIMPLE  |CachedGroupMembers_2|ref         |DisGrouM|DisGrouM|     5|Using where; Using index
1           |SIMPLE  |main                |eq_ref      |PRIMARY,|PRIMARY |     4|
1           |SIMPLE  |ACL_4               |range       |    ACL1|ACL1    |    54|Using where; Using index; Distinct
1           |SIMPLE  |Principals_1        |eq_ref      | PRIMARY|PRIMARY |     4|Using where; Distinct

At this moment i can't figure out why this query is running, who is starting this and how to fix this. All this Queries running around 200sec.

Any ideas where to start?

Thanks

Torsten

Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Dirk Blesius (Stellv.), Alfred Manke, Christian Marnetté (Stellv.),  Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne



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

Community help: http://wiki.bestpractical.com Commercial support: sales at bestpractical.com


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com