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

[Roy El-Hames]

I 'll be looking forward to the solution  ..I have a similar issue with 
content + subject only searches .. with RT-3.4.4
search builder 1.33 mysql 4.1.15

Roy


Aaron Nichols wrote:

>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
>  
>
>------------------------------------------------------------------------
>
>_______________________________________________
>http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
>Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
>Download a free sample chapter of RT Essentials from O'Reilly Media at http://rtbook.bestpractical.com
>
>WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
>San Francisco - Find out more at http://bestpractical.com/services/training.html
>