[rt-users] Ticket Query causes mysql to spin endlessly consuming all CPU

[Aaron Nichols]

Hello again,
     We seem to have stumbled into a situation that has dire
concequences for mysql. If we run the following query in RT, mysql
will consume 100% of the CPU and RT will end up returning a "500
Internal Server Error" due to a timeout waiting for a response from
the mysql server. This seems to only impact the current session, if
the browser is closed and opened again RT works ok but the mysql
process is left consuming CPU time.

Query in RT:
 Subject LIKE 'test' OR Content LIKE 'test'

Mysql Query According to mytop:
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId =
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
'ticket')) AND ((main.Subject LIKE '%test%')OR (
(Attachments_2.Content LIKE '%test%')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) )

The system is running on FreeBSD 6.0-RELEASE, RT 3.4.4, mysql 4.1.15
w/ linuxthreads, apache2.0.55, fastcgi 2.4.2 - all built from FreeBSD
ports collection.
Hardware is a dual Xeon 2.8Ghz, 2Gig ram running my-large.cnf from
mysql port w/ minor modifications.

When RT eventually errors out after 120 seconds it gives the following
error (which makes total sense):
[Tue Nov 15 09:09:59 2005] [error] [client 10.1.46.207] FastCGI: comm
with server "/usr/local/rt3/bin/mason_handler.fcgi" aborted: idle
timeout (120 sec), referer:
http://rt-stage.corp.netopia.com/Search/Build.html
[Tue Nov 15 09:09:59 2005] [error] [client 10.1.46.207] FastCGI:
incomplete headers (0 bytes) received from server
"/usr/local/rt3/bin/mason_handler.fcgi", referer:
http://rt-stage.corp.netopia.com/Search/Build.html

I believe this query is redundant and I've instructed folks not to do
this, but it seems like mysql shouldn't endlessly spin on the query
the way it does. I'm not sure if this is an RT problem in the way the
query is performed or a mysql problem in the way it responds (or
both). Any suggestions on how to prevent this in the future?

Thanks,
Aaron