Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 / Apache 2.2.3 /FastCGI

RT Users
1

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

2

Hi Together,
ok i found the cause of this long queries, it’s the Bulk Update Page…

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@lists.bestpractical.com [mailto:rt-users-bounces@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@bestpractical.com

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

3

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@lists.bestpractical.com [mailto:rt-users-bounces@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@bestpractical.com

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

4

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?

Because somewhere in your ACLs they gain OwnTicket right?

5

Hi Emmanuel,
just for my understanding: If they have somewhere the right to own ticket and i do a bulk update only in one queue where only 5 people have own ticket rights, all the users will be queried?

How can i easily check if they (especially the unpriviledged users) have own ticket rights?

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@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Emmanuel Lacour
Gesendet: Freitag, 23. Januar 2009 14:51
An: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI

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?

Because somewhere in your ACLs they gain OwnTicket right?

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

6

Hi Emmanuel,
just for my understanding: If they have somewhere the right to own
ticket and i do a bulk update only in one queue where only 5 people
have own ticket rights, all the users will be queried?

How can i easily check if they (especially the unpriviledged users) have own ticket rights?

you can use bulk update on a search result involving more than one
queue. And so in Search/Bulk.html, no queue is passed to
Elements/SelectOwner, and so all people that can own ticket are
displayed.

maybe we can try to get the list of queues from search result and pass
it to SelectOwner to reduce the list… but maybe gt of this list will
slow down Bulk.html to much ??? I need to try this.

7

Hi Emmanuel,
just for my understanding: If they have somewhere the right to own
ticket and i do a bulk update only in one queue where only 5 people
have own ticket rights, all the users will be queried?

How can i easily check if they (especially the unpriviledged users) have own ticket rights?

you can use bulk update on a search result involving more than one
queue. And so in Search/Bulk.html, no queue is passed to
Elements/SelectOwner, and so all people that can own ticket are
displayed.

maybe we can try to get the list of queues from search result and pass
it to SelectOwner to reduce the list… but maybe gt of this list will
slow down Bulk.html to much ??? I need to try this.

There is already (in 3.8 at least) a $seen_queues in Bulk.html that
contains the list of QueuesObj of all the ticket results. But it’s not
sent to SelectOwner. Maybe because it’s possible to change tickets in
other queues and so it’s better to show owners of all accessible queues,
not only those from the ticket result.

But … we can probably reduce the list of owners to those corresponding
to the list of queues displayed in Elements/SelectQueues instead of all
Owners of the RT system.

8

But … we can probably reduce the list of owners to those corresponding
to the list of queues displayed in Elements/SelectQueues instead of all
Owners of the RT system.

humm I’m a bit busy now, I open a bug for you to try to enhance this on
rt3.fsck.com.

9

Hi Emmanuel,
now back home and try on monday. so it is coming form the SelectOwner
Dropdown? I remember some years ago something similar at the querybuilder
page and i think jesse fixed this by replacing the dropdown with a normal
textbox…will try it out and keep you up2date.

Have a nice weekend

Torsten2009/1/23 Emmanuel Lacour elacour@easter-eggs.com

On Fri, Jan 23, 2009 at 03:37:17PM +0100, Emmanuel Lacour wrote:

But … we can probably reduce the list of owners to those corresponding
to the list of queues displayed in Elements/SelectQueues instead of all
Owners of the RT system.

humm I’m a bit busy now, I open a bug for you to try to enhance this on
rt3.fsck.com.

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

MFG

Torsten Brumm

http://www.torsten-brumm.de

10

Hi Emmanuel,
now back home and try on monday. so it is coming form the SelectOwner
Dropdown? I remember some years ago something similar at the querybuilder
page and i think jesse fixed this by replacing the dropdown with a normal
textbox…will try it out and keep you up2date.

just for my information how many users have you in the dropdown owner
list on Bulk.html ?

Have a nice weekend

You too :slight_smile:

11

Do you know which file controls this drop down box? “…replacing the dropdown with a normal textbox…”

I am having exactly the same problem, but cannot find which file / code is controlling this.

Thanks for your help. From: Torsten Brumm torsten.brumm@googlemail.com
Subject: Re: [rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI
To: “Emmanuel Lacour” elacour@easter-eggs.com
Cc: rt-users@lists.bestpractical.com
Date: Friday, January 23, 2009, 9:01 AM

Hi Emmanuel,
now back home and try on monday. so it is coming form the SelectOwner Dropdown? I remember some years ago something similar at the querybuilder page and i think jesse fixed this by replacing the dropdown with a normal textbox…will try it out and keep you up2date.

Have a nice weekend

Torsten

12

Do you know which file controls this drop down box? “…replacing the dropdown with a normal textbox…”
�
I�am having�exactly the same problem, but�cannot find which file / code is controlling this.
�

share/html/Search/Bulk.html, replace the line with /Elements/SelectOwner
with a text input field using the same name.

13

Emmanuel,
I also wanted to know if there is a way to edit the query that runs when QueryBuilder page loads? (Search/Build.html) I am unable to find this, as I am not quite a perl expert.

This is because we have an issue in RT where it displays many many users as “Owner” in QueryBuilder page, but when I run a query from mysql prompt, it only shows me handful of users with “OwnTicket” right. Based on this, to me it feels like my data is correct, but I could be wrong, as I have been working with RT software for only a month now.

Thanks for your help.

From: Emmanuel Lacour elacour@easter-eggs.com
Subject: Re: [rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI
To: rt-users@lists.bestpractical.com
Date: Friday, January 23, 2009, 11:51 AM

Do you know which file controls this drop down box? “…replacing the
dropdown with a normal textbox…”

I am having exactly the same problem, but cannot find which file / code
is controlling this.

share/html/Search/Bulk.html, replace the line with /Elements/SelectOwner
with a text input field using the same name.

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

14

Hi Emmanuel,
just for your information, the owner drop down is really long, i didn’t count all, but i scrolled down several seconds, looks like all the People from the SQL Query!

I’m going now to replace first the SelectOwner Dropdown by a text box…

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@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Emmanuel Lacour
Gesendet: Freitag, 23. Januar 2009 16:14
An: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Hi Emmanuel,
now back home and try on monday. so it is coming form the SelectOwner
Dropdown? I remember some years ago something similar at the
querybuilder page and i think jesse fixed this by replacing the
dropdown with a normal textbox…will try it out and keep you up2date.

just for my information how many users have you in the dropdown owner list on Bulk.html ?

Have a nice weekend

You too :slight_smile:

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

15

Hi Kem,
make first a local copy of /rthome/share/html/Search/Bulk.html to /rthome/local/html/Search/Bulk.html

Then open local Bulk.html and search for:

<&|/l&>Make Owner:

Then replace the Line:

<& /Elements/SelectOwner, Name => "Owner" &> ( <&|/l&>Force change)

With:

( <&|/l&>Force change)

Torsten

And now. my bulk update page is from 200sec down to 0,5sec !!!

Thanks for any hints so far

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 KuehneVon: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von kem cho
Gesendet: Freitag, 23. Januar 2009 18:41
An: torsten.brumm@googlemail.com
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Do you know which file controls this drop down box? “…replacing the dropdown with a normal textbox…”

I am having exactly the same problem, but cannot find which file / code is controlling this.

Thanks for your help.

From: Torsten Brumm <torsten.brumm@googlemail.com>
Subject: Re: [rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI
To: "Emmanuel Lacour" <elacour@easter-eggs.com>
Cc: rt-users@lists.bestpractical.com
Date: Friday, January 23, 2009, 9:01 AM


Hi Emmanuel,
now back home and try on monday. so it is coming form the SelectOwner Dropdown? I remember some years ago something similar at the querybuilder page and i think jesse fixed this by replacing the dropdown with a normal textbox...will try it out and keep you up2date.

Have a nice weekend

Torsten
16

Hi Kem,

same problem here, the querybuilder page was also slow until i got a hint from jesse:

Make first a local copy of /rthome/share/html/Search/Elements/PickBasics to /rthome/local/html/Search/Elements/PickBasics

Replace here:

<& /Elements/SelectOwner, Name => “ValueOfActor”, ValueAttribute => ‘Name’ &>

With:

From now QueryBuilder Page is much, much faster!

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 KuehneVon: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von kem cho
Gesendet: Freitag, 23. Januar 2009 22:19
An: Emmanuel Lacour
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Emmanuel,
I also wanted to know if there is a way to edit the query that runs when QueryBuilder page loads? (Search/Build.html) I am unable to find this, as I am not quite a perl expert.

This is because we have an issue in RT where it displays many many users as “Owner” in QueryBuilder page, but when I run a query from mysql prompt, it only shows me handful of users with “OwnTicket” right. Based on this, to me it feels like my data is correct, but I could be wrong, as I have been working with RT software for only a month now.

Thanks for your help.

From: Emmanuel Lacour <elacour@easter-eggs.com>
Subject: Re: [rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI
To: rt-users@lists.bestpractical.com
Date: Friday, January 23, 2009, 11:51 AM



> Do you know which file controls this drop down box? "...replacing the
dropdown with a normal textbox..."
>  
> I am having exactly the same problem, but cannot find which file / code
is controlling this. 
>  

share/html/Search/Bulk.html, replace the line with /Elements/SelectOwner
with a text input field using the same name.

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
17

Hi Emmanuel,
just got also some information from our DBA’s, they had a look (on Friday night! Wow!) to the query:

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;

And they came back with a much more faster query doing the same:

SELECT STRAIGHT_JOIN DISTINCT main.* FROM
ACL ACL_4,
Groups Groups_3,
CachedGroupMembers CachedGroupMembers_2,
Principals Principals_1,
Users main
WHERE ((ACL_4.PrincipalType = Groups_3.Type))
AND ((ACL_4.RightName = ‘OwnTicket’))
AND ((CachedGroupMembers_2.MemberId = Principals_1.id))
AND ((Groups_3.id = CachedGroupMembers_2.GroupId))
AND ((Principals_1.Disabled = ‘0’))
AND ((Principals_1.PrincipalType = ‘User’))
AND ((Principals_1.id != ‘1’))
AND ((main.id = Principals_1.id))
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.RealName ASC;

As you can see, less JOINS/CROSS JOINS. This Query is done in a few seconds compared to the one from SearchBuilder with 200sec and more.

Possibly a starting point for the RTDB Guys to review Searchbuilder…?!? But i’m not a DBA—

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@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Emmanuel Lacour
Gesendet: Freitag, 23. Januar 2009 15:25
An: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Hi Emmanuel,
just for my understanding: If they have somewhere the right to own
ticket and i do a bulk update only in one queue where only 5 people
have own ticket rights, all the users will be queried?

How can i easily check if they (especially the unpriviledged users) have own ticket rights?

you can use bulk update on a search result involving more than one queue. And so in Search/Bulk.html, no queue is passed to Elements/SelectOwner, and so all people that can own ticket are displayed.

maybe we can try to get the list of queues from search result and pass it to SelectOwner to reduce the list… but maybe gt of this list will slow down Bulk.html to much ??? I need to try this.

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

18

Hi Torsten,
I followed what you mention about Bulk update page and querybuilder page. It works great on old release of RT. I also have a test machine (running Fedora 9, perl 5.10, mysql 5.0), where I installed RT3.8.1

In RT 3.8.1, it looks like PickBasics program was modified, and it does not have the line you mention to modify. So, as a work around, I copied PickBasics file from older release of RT, and it seems to be working. Now, I do not have a drop down with the list of all RT users, it displays a text input box, which is fine (atleast the page loads in fraction of a second).

This week, I will work on installing RT 3.8.2 back on my test machine and see if my work around works with it.

I (and the users) really do appreciate your help with this…

From: Ham MI-ID, Torsten Brumm torsten.brumm@Kuehne-Nagel.com
Subject: AW: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Cc: rt-users@lists.bestpractical.com
Date: Saturday, January 24, 2009, 2:43 AM

Hi Kem,

same problem here, the querybuilder page was also slow until i got a hint from jesse:

Make first a local copy of /rthome/share/html/Search/Elements/PickBasics to /rthome/local/html/Search/Elements/PickBasics

Replace here:

<& /Elements/SelectOwner, Name => “ValueOfActor”, ValueAttribute => ‘Name’ &>

With:

From now QueryBuilder Page is much, much faster!

Torsten

Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Dirk Blesius (Stellv.), Bruno Mang, Alfred Manke, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg, Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne

Von: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von kem cho
Gesendet: Freitag, 23. Januar 2009 22:19
An: Emmanuel Lacour
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Emmanuel,
I also wanted to know if there is a way to edit the query that runs when QueryBuilder page loads? (Search/Build.html) I am unable to find this, as I am not quite a perl expert.

This is because we have an issue in RT where it displays many many users as “Owner” in QueryBuilder page, but when I run a query from mysql prompt, it only shows me handful of users with “OwnTicket” right. Based on this, to me it feels like my data is correct, but I could be wrong, as I have been working with RT software for only a month now.

Thanks for your help.

From: Emmanuel Lacour elacour@easter-eggs.com
Subject: Re: [rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI
To: rt-users@lists.bestpractical.com
Date: Friday, January 23, 2009, 11:51 AM

Do you know which file controls this drop down box? “…replacing the
dropdown with a normal textbox…”

I am having exactly the same problem, but cannot find which file / code
is controlling this.

share/html/Search/Bulk.html, replace the line with /Elements/SelectOwner
with a text input field using the same name.

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

19

Hi,
This works really well, even with RT 3.8.1. I will test this with 3.8.2 sometime this week, and post the results.
Thanks for your help…From: Ham MI-ID, Torsten Brumm torsten.brumm@Kuehne-Nagel.com
Subject: AW: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI
To: kemcho12345@yahoo.com, torsten.brumm@googlemail.com
Cc: rt-users@lists.bestpractical.com
Date: Saturday, January 24, 2009, 2:37 AM

Hi Kem,
make first a local copy of /rthome/share/html/Search/Bulk.html to /rthome/local/html/Search/Bulk.html

Then open local Bulk.html and search for:

<&|/l&>Make Owner:

Then replace the Line:

<& /Elements/SelectOwner, Name => "Owner" &> ( <&|/l&>Force change)

With:

( <&|/l&>Force change)

Torsten

And now. my bulk update page is from 200sec down to 0,5sec !!!

Thanks for any hints so far

Torsten

Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Dirk Blesius (Stellv.), Bruno Mang, Alfred Manke, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg, Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne

Von: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von kem cho
Gesendet: Freitag, 23. Januar 2009 18:41
An: torsten.brumm@googlemail.com
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

Do you know which file controls this drop down box? “…replacing the dropdown with a normal textbox…”

I am having exactly the same problem, but cannot find which file / code is controlling this.

Thanks for your help.

From: Torsten Brumm torsten.brumm@googlemail.com
Subject: Re: [rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3 /FastCGI
To: “Emmanuel Lacour” elacour@easter-eggs.com
Cc: rt-users@lists.bestpractical.com
Date: Friday, January 23, 2009, 9:01 AM

Hi Emmanuel,
now back home and try on monday. so it is coming form the SelectOwner Dropdown? I remember some years ago something similar at the querybuilder page and i think jesse fixed this by replacing the dropdown with a normal textbox…will try it out and keep you up2date.

Have a nice weekend

Torsten

20
  1. CROSS JOIN is equal to “,”
  2. STRAIGHT JOIN is workaround for mysql bugs/problems.
  3. Start by complaining into mysql’s bug tracker
  4. There is only one thing I can do, but that will need more testing
    from users and only on 3.8.On Sat, Jan 24, 2009 at 12:37 PM, Ham MI-ID, Torsten Brumm torsten.brumm@kuehne-nagel.com wrote:

Hi Emmanuel,
just got also some information from our DBA’s, they had a look (on Friday night! Wow!) to the query:

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;

And they came back with a much more faster query doing the same:

SELECT STRAIGHT_JOIN DISTINCT main.* FROM
ACL ACL_4,
Groups Groups_3,
CachedGroupMembers CachedGroupMembers_2,
Principals Principals_1,
Users main
WHERE ((ACL_4.PrincipalType = Groups_3.Type))
AND ((ACL_4.RightName = ‘OwnTicket’))
AND ((CachedGroupMembers_2.MemberId = Principals_1.id))
AND ((Groups_3.id = CachedGroupMembers_2.GroupId))
AND ((Principals_1.Disabled = ‘0’))
AND ((Principals_1.PrincipalType = ‘User’))
AND ((Principals_1.id != ‘1’))
AND ((main.id = Principals_1.id))
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.RealName ASC;

As you can see, less JOINS/CROSS JOINS. This Query is done in a few seconds compared to the one from SearchBuilder with 200sec and more.

Possibly a starting point for the RTDB Guys to review Searchbuilder…?!? But i’m not a DBA—

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@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Emmanuel Lacour
Gesendet: Freitag, 23. Januar 2009 15:25
An: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

On Fri, Jan 23, 2009 at 02:55:21PM +0100, Ham MI-ID, Torsten Brumm wrote:

Hi Emmanuel,
just for my understanding: If they have somewhere the right to own
ticket and i do a bulk update only in one queue where only 5 people
have own ticket rights, all the users will be queried?

How can i easily check if they (especially the unpriviledged users) have own ticket rights?

you can use bulk update on a search result involving more than one queue. And so in Search/Bulk.html, no queue is passed to Elements/SelectOwner, and so all people that can own ticket are displayed.

maybe we can try to get the list of queues from search result and pass it to SelectOwner to reduce the list… but maybe gt of this list will slow down Bulk.html to much ??? I need to try this.

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

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

Best regards, Ruslan.