Cannot Steal or Reassign Tickets

Camron_W_Fox · May 9, 2002, 12:14am

Alle,

Since upgrading to 2.X, we can no longer steal tickets (even though I have

full privileges) and, therefore, cannot reassign them. I’ve been looking in
the archives for something on this, but maybe I am the only one experiencing
it.

Best Regards,
Camron

Camron W. Fox
Hilo Office
High Performance Computing Group
Fujitsu America, INC.
E-mail: cwfox@fujitsu.com
Phone: (808) 934-4102
Pager: (808) 934-1290
Cell: (808) 937-5026

Robin_Lee_Powell · May 9, 2002, 4:28pm

Since upgrading to 2.X, we can no longer steal tickets (even though I
have full privileges) and, therefore, cannot reassign them. I’ve been
looking in the archives for something on this, but maybe I am the only
one experiencing it.

Works fine for me using a user that has global rights.

-Robin

Robin Powell's Old Home Page BTW, I’m male, honest.
le datni cu djica le nu zifre .iku’i .oi le so’e datni cu to’e te pilno
je xlali – RLP http://www.lojban.org/

Joshua_Mandelberger · May 9, 2002, 7:36pm

Hi, I’ve been on the list for quite some time now but up until now have
been lurking and reading. Lots of great information, a good deal of which
as been valuable to myself and the admins who over see our RT2 installation.
I must also say its an awesome program, its improved the efficiency of our
tech support more than I can possibly describe.

That having been said, we have been seeing some problems with our RT2
database over the last week or two that is cause for some concern.

Every so often, and it is intermittent but this problem occurs at least
once ever couple days, the MySQL thread for the RT2 database will spike up
to 85-99% of the servers CPU power and just stay put there. This causes
all the sessions in Tracker to move very slowly naturally.

Is there anywhere to look for a way to pin down what is causing the
problem, or some kind of tuning we can do on the database that might
alleviate the problems we’re having?

Here is the present configuration.

Dual P-III 800
768MB of Ram
RedHat Linux version 6.1
Running the 2.4.17 kernel
mysql version 3.23.39
RT version 2.0.13

There are also around 11000-12000 tickets in our database if that makes a
difference.

If there is anything else I can provide that would help with information
please let me know. Also if this has come up before (I hadn’t seen
anything like it) and there is already messages covering this, let me know
where in the archives to look. I went through them and tried to find some
answers but didn’t see anything similar to what is happening. But I could
have missed them.

Thanks,

Joshua Mandelberger
Pegasus Web Technologies

Joshua_Mandelberger · May 9, 2002, 7:43pm

Hi, I’ve been on the list for quite some time now but up until now have
been lurking and reading. Lots of great information, a good deal of which
as been valuable to myself and the admins who over see our RT2 installation.
I must also say its an awesome program, its improved the efficiency of our
tech support more than I can possibly describe.

That having been said, we have been seeing some problems with our RT2
database over the last week or two that is cause for some concern.

Every so often, and it is intermittent but this problem occurs at least
once ever couple days, the MySQL thread for the RT2 database will spike up
to 85-99% of the servers CPU power and just stay put there. This causes
all the sessions in Tracker to move very slowly naturally.

Is there anywhere to look for a way to pin down what is causing the
problem, or some kind of tuning we can do on the database that might
alleviate the problems we’re having?

Here is the present configuration.

Dual P-III 800
768MB of Ram
RedHat Linux version 6.1
Running the 2.4.17 kernel
mysql version 3.23.39
RT version 2.0.13

There are also around 11000-12000 tickets in our database if that makes a
difference.

If there is anything else I can provide that would help with information
please let me know. Also if this has come up before (I hadn’t seen
anything like it) and there is already messages covering this, let me know
where in the archives to look. I went through them and tried to find some
answers but didn’t see anything similar to what is happening. But I could
have missed them.

Thanks,

Joshua Mandelberger
Pegasus Web Technologies

Adam_Morton · May 9, 2002, 7:49pm

What does ‘show processlist’ in mySQL look like when this is happening?

When you say ‘just stay put there’, do you mean forever, and you have to
restart mySQL or the server, or what? Or does it stop by itself at some
point?From: “Joshua Mandelberger” josh@pwebtech.com
To: rt-users@fsck.com
Sent: Thursday, May 09, 2002 3:36 PM
Subject: [rt-users] RT2 and MySQL

Hi, I’ve been on the list for quite some time now but up until now have
been lurking and reading. Lots of great information, a good deal of which
as been valuable to myself and the admins who over see our RT2
installation.
I must also say its an awesome program, its improved the efficiency of our
tech support more than I can possibly describe.

That having been said, we have been seeing some problems with our RT2
database over the last week or two that is cause for some concern.

Every so often, and it is intermittent but this problem occurs at least
once ever couple days, the MySQL thread for the RT2 database will spike up
to 85-99% of the servers CPU power and just stay put there. This causes
all the sessions in Tracker to move very slowly naturally.

Is there anywhere to look for a way to pin down what is causing the
problem, or some kind of tuning we can do on the database that might
alleviate the problems we’re having?

Here is the present configuration.

Dual P-III 800
768MB of Ram
RedHat Linux version 6.1
Running the 2.4.17 kernel
mysql version 3.23.39
RT version 2.0.13

There are also around 11000-12000 tickets in our database if that makes a
difference.

If there is anything else I can provide that would help with information
please let me know. Also if this has come up before (I hadn’t seen
anything like it) and there is already messages covering this, let me know
where in the archives to look. I went through them and tried to find some
answers but didn’t see anything similar to what is happening. But I could
have missed them.

Thanks,

Joshua Mandelberger
Pegasus Web Technologies

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Joshua_Mandelberger · May 22, 2002, 5:36pm

when I say just stays put there, I mean it continually takes 99.9% of the
CPU power of the box, or at least as much of it as it can get.

To stop it we have to restart the mysql server.

Sorry about the delayed response, the problem didn’t occur again until now.

Josh

At 03:49 PM 5/9/2002, Adam Morton wrote:

Adam_Morton · May 22, 2002, 6:17pm

Here’s the bad query, which has at least 1 more join than the others in the
list and is swapping to disk and locking up all the incoming ones.

| 4253989 | rt_user | localhost | rt2 | Query | 1199 | Copying to
tmp table | SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1,
Watchers Watchers_3 LEFT JOIN Users

Workaround: Whatever you did that generates that query with the extra join
(20 minutes prior to running this processlist), don’t do it.

A fix would involve figuring out how to do whatever is being done here more
efficiently, or tweaking MySQL so that it can handle this query without
swapping out to the tmp table (check tmp_table_size), or so that locking is
less of a problem (look at http://www.mysql.com/doc/T/a/Table_locking.html).
A ‘show full processlist’ will list the complete SQL statement which may
help you pin down what it is a little better. Or, you could turn on query
logging or slow query logging.From: “Joshua Mandelberger” josh@pwebtech.com
To: “Adam Morton” lists@adammorton.com; rt-users@fsck.com
Sent: Wednesday, May 22, 2002 1:36 PM
Subject: Re: [rt-users] RT2 and MySQL

This is what it looks like.

| Id | User | Host | db | Command | Time |
State |
Info
|

| 4253090 | rt_user | localhost | rt2 | Sleep | 1095
| |
NULL
|
| 4253105 | rt_user | localhost | rt2 | Query | 188 |
Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
((main.Status = ‘open’)OR(main.Status = ‘new’)) AND |
| 4253135 | rt_user | localhost | rt2 | Query | 1026 |
Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
((main.Status = ‘open’)OR(main.Status = ‘new’)) AND |
| 4253260 | rt_user | localhost | rt2 | Query | 928 |
Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
((main.Status = ‘open’)OR(main.Status = ‘new’)) AND |
| 4253385 | rt_user | localhost | rt2 | Query | 495 |
Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
((main.Status = ‘new’)OR(main.Status = ‘open’)) AND |
| 4253393 | rt_user | localhost | rt2 | Query | 921 |
Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
((main.Status = ‘open’)OR(main.Status = ‘new’)) AND |
| 4253417 | rt_user | localhost | rt2 | Query | 1096 |
Locked | SELECT DISTINCT main.* FROM Tickets main, Watchers
Watchers_1 LEFT JOIN Users as Users_2 ON Watch |
| 4253428 | rt_user | localhost | rt2 | Query | 1096 |
Locked | SELECT DISTINCT main.* FROM Tickets main, Watchers
Watchers_1 LEFT JOIN Users as Users_2 ON Watch |
| 4253989 | rt_user | localhost | rt2 | Query | 1199 | Copying to
tmp table | SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1,
Watchers Watchers_3 LEFT JOIN Users |
| 4273904 | rt_user | localhost | rt2 | Query | 718 |
Locked | SELECT DISTINCT main.* FROM Tickets main WHERE
((main.Status = ‘open’)OR(main.Status = ‘new’)) AND |
| 4331604 | rt_user | localhost | rt2 | Query | 1096 |
Locked | UPDATE Tickets SET LastUpdatedBy=‘4366’ WHERE
id=‘12598’ |
| 4332533 | rt_user | localhost | rt2 | Query | 1084 |
Locked | INSERT INTO Tickets (Creator, Due, Status,
LastUpdatedBy, Resolved, Type, TimeWorked, LastUpdated, S |
| 4335019 | rt_user | localhost | rt2 | Query | 719 |
Locked | SELECT * FROM Tickets WHERE id =
‘12556’ |
| 4336413 | rt_user | localhost | rt2 | Query | 511 |
Locked | INSERT INTO Tickets (Creator, Due, Status,
LastUpdatedBy, Resolved, Type, TimeWorked, LastUpdated, S |
| 4337214 | rt_user | localhost | rt2 | Query | 397 |
Locked | INSERT INTO Tickets (Creator, Due, Status,
LastUpdatedBy, Resolved, Type, TimeWorked, LastUpdated, S |
| 4339696 | root | localhost | NULL | Query | 0 |
NULL | show
processlist
|
| 4339886 | root | localhost | vpopmail | Sleep |
0 | |
NULL
|
| 4339887 | root | localhost | vpopmail | Sleep |
0 | | NULL

when I say just stays put there, I mean it continually takes 99.9% of the
CPU power of the box, or at least as much of it as it can get.

To stop it we have to restart the mysql server.

Sorry about the delayed response, the problem didn’t occur again until
now.

Josh

At 03:49 PM 5/9/2002, Adam Morton wrote:

What does ‘show processlist’ in mySQL look like when this is happening?

When you say ‘just stay put there’, do you mean forever, and you have to
restart mySQL or the server, or what? Or does it stop by itself at some
point?

----- Original Message -----
From: “Joshua Mandelberger” josh@pwebtech.com
To: rt-users@fsck.com
Sent: Thursday, May 09, 2002 3:36 PM
Subject: [rt-users] RT2 and MySQL

Hi, I’ve been on the list for quite some time now but up until now
have
been lurking and reading. Lots of great information, a good deal of
which
as been valuable to myself and the admins who over see our RT2
installation.
I must also say its an awesome program, its improved the efficiency of
our
tech support more than I can possibly describe.

That having been said, we have been seeing some problems with our RT2
database over the last week or two that is cause for some concern.

Every so often, and it is intermittent but this problem occurs at
least
once ever couple days, the MySQL thread for the RT2 database will
spike up
to 85-99% of the servers CPU power and just stay put there. This
causes
all the sessions in Tracker to move very slowly naturally.

Is there anywhere to look for a way to pin down what is causing the
problem, or some kind of tuning we can do on the database that might
alleviate the problems we’re having?

Here is the present configuration.

Dual P-III 800
768MB of Ram
RedHat Linux version 6.1
Running the 2.4.17 kernel
mysql version 3.23.39
RT version 2.0.13

There are also around 11000-12000 tickets in our database if that
makes a
difference.

If there is anything else I can provide that would help with
information
please let me know. Also if this has come up before (I hadn’t seen
anything like it) and there is already messages covering this, let me
know
where in the archives to look. I went through them and tried to find
some
answers but didn’t see anything similar to what is happening. But I
could
have missed them.

Thanks,

Joshua Mandelberger
Pegasus Web Technologies

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at
http://fsck.com/rtfm

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Sebastian_Flothow · May 22, 2002, 7:01pm

At 13:36 Uhr -0400 22.5.2002, Joshua Mandelberger wrote:

This is what it looks like.
±--------±--------±----------±---------±--------±-----±---------------------±-----------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info |
±--------±--------±----------±---------±--------±-----±---------------------±-----------------------------------------------------------------------------------------------------+
| 4253090 | rt_user | localhost | rt2 | Sleep | 1095 |
| NULL |
[…]

This is quite ugly. If you need to post such MySQL output again,
please use \G instead of ; at the end of the query (eg., show
processlist\G).

Sebastian Flothow
sebastian@flothow.de
#include <stddisclaimer.h>

Joshua_Mandelberger · May 22, 2002, 7:54pm

Sorry about that, it was the first time I had done such a thing, I’ll make
sure to use the other method if I have to do so again.

Josh

At 03:01 PM 5/22/2002, Sebastian Flothow wrote:

Byron_Ellacott · May 23, 2002, 6:25am

tmp table | SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1,
Watchers Watchers_3 LEFT JOIN Users |

Adam, this is caused by the Search facility.

Due to the design of the Watchers and Users tables, a join must be done
across them to select an email address. One table or the other has an
apparently unused email address column that would solve the issue, but I
don’t recall which now, and never investigated why it’s unused…

But that aside, what you’ll find is happening will be a user who is
searching for two email addresses, most likely because they don’t
understand how the RT Search works and are adding a search by email
address twice. This causes DBIx to produce the above ugly query, which
in turn causes MySQL to lock up tight. The same problem occurs if you
attempt to search ticket content, for much the same reason.

I don’t know offhand if this is a problem with the queries being
generated by DBIx, or an insurmountable problem caused by the table
design, or a problem with the way MySQL is handling the problem; instead
I hacked around it to prevent users from querying email address or
content more than once per search. Attached is a patch to
…/lib/RT/Interface/Web.pm which will solve your immediate problem.

bje

unbreak.patch (1.99 KB)

Matthew_Watson2 · May 23, 2002, 6:34am

haha. so I’m not the only person to be bitten by this one… RT2 is REALLY
slow
at searching for tickets by requestor… and i’ve had no luck in indexing
this
query at all…and it just goes into a downward spiral if you let
staff try and search on more than one requestor…