Requestor OR email like query from SearchBuilder sticks in mysql- RT v3.2.2

There is a query built through the SearchBuilder that consistently hangs
the query in mysql. I searched the mail list archives and the wiki and
only found similar situations that dealt with watchers and was fixed in
v3+ I’m hoping someone can reproduce to verify it is not an issue with
my setup and I apologize if I’ve missed prior reports of this.

The particulars-

RT v 3.2.2
Mysql v 4.0.15
Apache v 2.0.45
Perl 5.8.5
I’ve attached the config output from the RT System Configuration for any
other needed details.

To reproduce-
Go to Tickets, New Query- Select OR radio button
Select Owner is (some user in system)
Select Requestor EmailAddress contains (part of email address of above
person)

Variations of the above for the Requestor (RealName, is, etc.) produce
the same results.

The query that gets stuck on my system looks like this in the process
list:
| 355513 | rt_user | localhost | rt3 | Query | 63338 |
Sending data | SELECT COUNT(DISTINCT main.id) FROM Tickets main , Groups
Groups_1, CachedGroupMembers CachedGroupMembers_2, Users Users_3 WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
( ( (Users_3.EmailAddress LIKE ‘%ajcantu%’)AND(Groups_1.Domain =
‘RT::Ticket-Role’)AND(main.id = Groups_1.Instance)AND(Groups_1.Type =
‘Requestor’)AND(Groups_1.id =
CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId =
Users_3.id) ) OR(main.Owner = ‘414’)) |

The 63338 is the time it has been running and they never finish. I
found this after a user kept trying to do a search finding tickets he
owned and requested. There were so many of these that mysql finally
became unrepsonsive to RT and RT started to 500 waiting for it. I’ve
had to manually kill these processes to get rid of them. Any
suggestions or further information that is needed please let me know and
TIA,

BTW, haven’t tried this on any of the RCs for 3.4 yet, have some work to
do on the current version for work before I can upgrade my dev box,
cheers,

Paul Petersen <paul (a) marchex.com>

config (8.48 KB)

IIRC, we did some work on this for 3.2.3. Might be worth tryingOn Thu, Jan 13, 2005 at 11:08:07AM -0800, Paul Petersen wrote:

There is a query built through the SearchBuilder that consistently hangs
the query in mysql. I searched the mail list archives and the wiki and
only found similar situations that dealt with watchers and was fixed in
v3+ I’m hoping someone can reproduce to verify it is not an issue with
my setup and I apologize if I’ve missed prior reports of this.

The particulars-

RT v 3.2.2
Mysql v 4.0.15
Apache v 2.0.45
Perl 5.8.5
I’ve attached the config output from the RT System Configuration for any
other needed details.

To reproduce-
Go to Tickets, New Query- Select OR radio button
Select Owner is (some user in system)
Select Requestor EmailAddress contains (part of email address of above
person)

Variations of the above for the Requestor (RealName, is, etc.) produce
the same results.

The query that gets stuck on my system looks like this in the process
list:
| 355513 | rt_user | localhost | rt3 | Query | 63338 |
Sending data | SELECT COUNT(DISTINCT main.id) FROM Tickets main , Groups
Groups_1, CachedGroupMembers CachedGroupMembers_2, Users Users_3 WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
( ( (Users_3.EmailAddress LIKE ‘%ajcantu%’)AND(Groups_1.Domain =
‘RT::Ticket-Role’)AND(main.id = Groups_1.Instance)AND(Groups_1.Type =
‘Requestor’)AND(Groups_1.id =
CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId =
Users_3.id) ) OR(main.Owner = ‘414’)) |

The 63338 is the time it has been running and they never finish. I
found this after a user kept trying to do a search finding tickets he
owned and requested. There were so many of these that mysql finally
became unrepsonsive to RT and RT started to 500 waiting for it. I’ve
had to manually kill these processes to get rid of them. Any
suggestions or further information that is needed please let me know and
TIA,

BTW, haven’t tried this on any of the RCs for 3.4 yet, have some work to
do on the current version for work before I can upgrade my dev box,
cheers,


Paul Petersen <paul (a) marchex.com>

Perl v5.8.5 under linux
Apache::Session v1.6;
Apache::Session::Generate::MD5 v2.1;
Apache::Session::lock::MySQL v1.00;
Apache::Session::MySQL v1.01;
Apache::Session::Serialize::Storable v1.00;
Apache::Session::Store::DBI v1.02;
Apache::Session::Store::MySQL v1.04;
AutoLoader v5.60;
base v2.06;
Benchmark v1.06;
bytes v1.01;
Cache::Simple::TimedExpiry v0.21;
Carp v1.03;
CGI v3.05;
CGI::Cookie v1.24;
CGI::Fast v1.05;
CGI::Util v1.5;
Class::Container v0.11;
Class::Data::Inheritable v0.02;
Class::ReturnValue v0.52;
constant v1.04;
Cwd v2.19;
Data::Dumper v2.121;
DBD::mysql v2.1028;
DBI v1.45;
DBIx::SearchBuilder v1.11;
Devel::StackTrace v1.11;
Devel::StackTraceFrame v0.6;
Digest::base v1.00;
Digest::MD5 v2.33;
DynaLoader v1.05;
Encode v2.01;
Encode::Alias v2.00;
Encode::Config v2.00;
Encode::Encoding v2.00;
Errno v1.09;
Exception::Class v1.19;
Exception::Class::Base v1.2;
Exporter v5.58;
Exporter::Heavy v5.58;
FCGI v0.67;
Fcntl v1.05;
fields v2.03;
File::Basename v2.73;
File::Glob v1.03;
File::Path v1.06;
File::Spec v0.87;
File::Spec::Unix v1.5;
File::Temp v0.14;
FileHandle v2.01;
HTML::Entities v1.27;
HTML::Mason v1.26;
HTML::Mason::CGIHandler v1.00;
HTML::Mason::Exception v1.1;
HTML::Mason::Exception::Abort v1.1;
HTML::Mason::Exception::Compilation v1.1;
HTML::Mason::Exception::Compilation::IncompatibleCompiler v1.1;
HTML::Mason::Exception::Compiler v1.1;
HTML::Mason::Exception::Decline v1.1;
HTML::Mason::Exception::Params v1.1;
HTML::Mason::Exception::Syntax v1.1;
HTML::Mason::Exception::System v1.1;
HTML::Mason::Exception::TopLevelNotFound v1.1;
HTML::Mason::Exception::VirtualMethod v1.1;
HTML::Mason::Exceptions v1.43;
HTML::Parser v3.36;
HTML::Scrubber v0.08;
I18N::LangTags v0.35;
integer v1.00;
IO v1.21;
IO::File v1.10;
IO::Handle v1.24;
IO::InnerFile v2.102 ;
IO::Lines v2.103 ;
IO::Scalar v2.105 ;
IO::ScalarArray v2.103 ;
IO::Seekable v1.09;
IO::Wrap v2.102 ;
IO::WrapTie v2.102 ;
IPC::Open2 v1.01;
IPC::Open3 v1.0106;
lib v0.5565;
List::Util v1.14;
locale v1.00;
Locale::Maketext v1.09;
Locale::Maketext::Fuzzy v0.02;
Locale::Maketext::Lexicon v0.44;
Locale::Maketext::Lexicon::Gettext v0.12;
Log::Dispatch v2.10;
Log::Dispatch::Base v1.09;
Log::Dispatch::Output v1.26;
Log::Dispatch::Screen v1.17;
Log::Dispatch::Syslog v1.18;
Mail::Address v1.64;
Mail::Field v1.64;
Mail::Field::AddrList v1.64;
Mail::Header v1.64;
Mail::Internet v1.64;
MIME::Base64 v3.05;
MIME::Body v5.414;
MIME::Decoder v5.414;
MIME::Entity v5.414;
MIME::Field::ContDisp v5.414;
MIME::Field::ConTraEnc v5.414;
MIME::Field::ContType v5.414;
MIME::Field::ParamVal v5.414;
MIME::Head v5.414;
MIME::Parser v5.414;
MIME::QuotedPrint v3.03;
MIME::Tools v5.414;
MIME::Words v5.414;
Module::Versions::Report v1.02;
overload v1.01;
Params::Validate v0.74;
POSIX v1.08;
re v0.04;
Regexp::Common v2.117;
Regexp::Common::delimited v2.103;
RT v3.2.2;
Scalar::Util v1.14;
SelectSaver v1.00;
Socket v1.77;
Storable v2.13;
strict v1.03;
Symbol v1.05;
Sys::Hostname v1.11;
Sys::Syslog v0.05;
Text::Autoformat v1.12;
Text::Quoted v1.8;
Text::Reform v1.11;
Text::Tabs v98.112801;
Text::Template v1.44;
Text::WikiFormat v0.72;
Text::Wrapper v1.000;
Time::HiRes v1.59;
Time::JulianDay v2003.1125;
Time::Local v1.1;
Time::ParseDate v2003.1126;
Time::Timezone v2003.0211;
URI v1.34;
URI::Escape v3.26;
utf8 v1.04;
vars v1.01;
warnings v1.03;
warnings::register v1.00;
XSLoader v0.02;

RT Variables
RT::AmbiguousDayInPast 1
RT::BasePath /site/rt
RT::CORE_CONFIG_FILE /site/rt/etc/RT_Config.pm
RT::CanonicalizeEmailAddressMatch subdomain.example.com$
RT::CanonicalizeEmailAddressReplace example.com
RT::CommentAddress RT_CommentAddressNotSet
RT::CorrespondAddress RT_CorrespondAddressNotSet
RT::DatabaseName rt3
RT::DatabasePassword Password not printed
RT::DatabaseType mysql
RT::DatabaseUser rt_user
RT::DateDayBeforeMonth 1
RT::DefaultSearchResultFormat ‘id/TITLE:#’, ‘Subject/TITLE:Subject’, Status, QueueName, OwnerName, Priority, ‘NEWLINE’, ‘’, ‘Requestors’, ‘CreatedRelative’, ‘ToldRelative’, ‘LastUpdatedRelative’, 'TimeLeft
RT::EmailOutputEncoding utf-8
RT::EtcPath /site/rt/etc
RT::FriendlyFromLineFormat “%s via RT” <%s>
RT::FriendlyToLineFormat “%s of Marchex Ticket #%s”:;
RT::LocalEtcPath /site/rt/local/etc
RT::LocalLexiconPath /site/rt/local/po
RT::LocalPath /site/rt/local
RT::LogDir /site/rt/var/log
RT::LogToFileNamed rt.log
RT::LogToScreen error
RT::LogToSyslog debug
RT::LogoURL /NoAuth/images/rt.jpg
RT::LoopsToRTOwner 1
RT::MailCommand sendmailpipe
RT::MasonComponentRoot /site/rt/share/html
RT::MasonDataDir /site/rt/var/mason_data
RT::MasonLocalComponentRoot /site/rt/local/html
RT::MasonSessionDir /site/rt/var/session_data
RT::MaxAttachmentSize 10000000
RT::MaxInlineBody 13456
RT::MessageBoxWidth 72
RT::MessageBoxWrap HARD
RT::MinimumPasswordLength Password not printed
RT::MyRequestsLength 10
RT::MyTicketsLength 10
RT::Organization marchex.com
RT::OwnerEmail monitoralert@marchex.com
RT::RTAddressRegexp ^rt@marchex.com$
RT::RecordOutgoingEmail 1
RT::SITE_CONFIG_FILE /site/rt/etc/RT_SiteConfig.pm
RT::SendmailArguments -oi -t
RT::SendmailPath /usr/sbin/sendmail
RT::Timezone US/Pacific
RT::UseFriendlyFromLine 1
RT::VERSION 3.2.2
RT::VarPath /site/rt/var
RT::WebBaseURL http://rt.marchex.com
RT::WebImagesURL /NoAuth/images/
RT::WebURL http://rt.marchex.com/
RT::rtname Marchex
Perl configuration

Summary of my perl5 (revision 5 version 8 subversion 5) configuration:
Platform:
osname=linux, osvers=, archname=i686-linux-thread-multi
uname=‘linux laster 2.4.20-1-686 #1 sat mar 22 13:16:21 est 2003 i686 gnulinux ‘
config_args=’-f config.sh
hint=previous, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc=‘cc’, ccflags =’-D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64’,
optimize=’-O3’,
cppflags=’-D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64’
ccversion=’’, gccversion=‘2.95.4 20011002 (Debian prerelease)’, gccosandvers=’‘
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12
ivtype=‘long’, ivsize=4, nvtype=‘double’, nvsize=8, Off_t=‘off_t’, lseeksize=8
alignbytes=4, prototype=define
Linker and Libraries:
ld=‘cc’, ldflags =’ -L/usr/local/lib’
libpth=/usr/local/lib /lib /usr/lib
libs=-lnsl -ldl -lm -lpthread -lc -lcrypt -lutil
perllibs=-lnsl -ldl -lm -lpthread -lc -lcrypt -lutil
libc=/lib/libc-2.3.2.so, so=so, useshrplib=false, libperl=libperl.a
gnulibc_version=‘2.2.5’
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=’-rdynamic’
cccdlflags=’-fpic’, lddlflags=’-shared -L/usr/local/lib’

»|« RT 3.2.2 Copyright 1996-2004


Rt-devel mailing list
Rt-devel@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel