[rt-users] A bug? Searchbuilder?
Rangarajan Radhakrishnan
rangarajan.radhakrishnan at bms.com
Mon May 8 15:19:50 EDT 2006
Appears that the problem is happening because of presence of "main.name
= 'Tournummer'".
The problem happens because "main" is an alias for Tickets table and the
Tickets table has no column called "name".
What you run this SELECT query against the database (I deal with oracle,
you with MySQL) you are going to get some error:
Try running the following against your DB:
SELECT DISTINCT main.* FROM (((Tickets main LEFT JOIN
ObjectCustomFields ObjectCustomFields_1 ON (
ObjectCustomFields_1.ObjectId = main.Queue)) LEFT JOIN CustomFields
CustomFields_2 ON ( CustomFields_2.id =
ObjectCustomFields_1.CustomField)) LEFT JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_3 ON ((ObjectCustomFieldValues_3.ObjectId =
main.id)) AND ( ObjectCustomFieldValues_3.CustomField =
CustomFields_2.id) AND ( (ObjectCustomFieldValues_3.Disabled = '0')) AND
( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))) WHERE
((CustomFields_2.name = 'Tournummer')) AND ((main.EffectiveId =
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket'))
AND ((main.name = 'Tournummer')) AND ( ( (main.Queue = '5') ) AND ( (
(ObjectCustomFieldValues_3.Content = '03376') ) OR (
(ObjectCustomFieldValues_3.Content = '03361') ) ) AND ( (main.Status =
'new')OR(main.Status = 'open')Or(main.Status = 'stalled')OR(main.Status
= 'resolved') ) )
The trouble is that we need to spend a lot more time trying to figure
out whether its a problem with DBIx::SearchBuilder or the code calling it.
Hopefully, somebody who knows will read our posts....
Torsten Brumm wrote:
> Hi Rangarajan,
>
> Here is the Output at the error log:
>
> [Mon May 08 16:26:20 2006] [error] [client 10.63.253.97] FastCGI:
> server "/opt/rt3/bin/mason_handler.fcgi" stderr: RR: SearchBuilder.pm:
> _DoSearch: QueryString is: SELECT DISTINCT main.* FROM (GroupMembers
> main LEFT JOIN Groups Groups_1 ON ( Groups_1.id = main.GroupId))
> WHERE ((Groups_1.Domain = 'SystemInternal')OR(Groups_1.Domain =
> 'UserDefined')) AND ((main.MemberId = '225')) ORDER BY
> Groups_1.Domain ASC, Groups_1.Name ASC , referer:
> http://astdev.int.kn/Sendungen/index.html
>
> [Mon May 08 16:26:20 2006] [error] [client 10.63.253.97] FastCGI:
> server "/opt/rt3/bin/mason_handler.fcgi" stderr: RR: SearchBuilder.pm:
> _DoSearch: QueryString is: SELECT DISTINCT main.* FROM (((Tickets
> main LEFT JOIN ObjectCustomFields ObjectCustomFields_1 ON (
> ObjectCustomFields_1.ObjectId = main.Queue)) LEFT JOIN CustomFields
> CustomFields_2 ON ( CustomFields_2.id =
> ObjectCustomFields_1.CustomField)) LEFT JOIN ObjectCustomFieldValues
> ObjectCustomFieldValues_3 ON ((ObjectCustomFieldValues_3.ObjectId =
> main.id)) AND ( ObjectCustomFieldValues_3.CustomField =
> CustomFields_2.id) AND ( (ObjectCustomFieldValues_3.Disabled = '0'))
> AND ( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))) WHERE
> ((CustomFields_2.name = 'Tournummer')) AND ((main.EffectiveId =
> main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
> 'ticket')) AND ((main.name = 'Tournummer')) AND ( ( (main.Queue = '5')
> ) AND ( ( (ObjectCustomFieldValues_3.Content = '03376') ) OR (
> (ObjectCustomFieldValues_3.Content = '03361') ) ) AND ( (main.Status
> = 'new')OR(main.Status = 'open')Or(main.Status =
> 'stalled')OR(main.Status = 'resolved') ) ) ORDER, referer:
> http://astdev.int.kn/Sendungen/index.html
>
> This looks strange, i don't understand a word... argh...
>
> Torsten
>
> Rangarajan Radhakrishnan schrieb:
>
>> In SearchBuilder.pm, inside "sub _DoSearch" you can use:
>> print STDERR "RR: SearchBuilder.pm: _DoSearch: QueryString is:
>> $QueryString\n";
>>
>> (right after the statement "my $QueryString =
>> $self->BuildSelectQuery();")
>>
>> When you do this queries against the database will result in the
>> query getting printed in apache error_log (please check your apache
>> config if you are unsure about location and naming of error log).
>>
>> For adding this debug statement, you may have to interact with your
>> sys admin if your DBIx::SearchBuilder was installed by your sys admin
>> and you don't have write privileges. Hope this helps.
>>
>> Torsten Brumm wrote:
>>
>>> Hi Rangarajan,
>>>
>>> I'm using Mysql 4.12, i have also tried to update DBIxSearchbuilder
>>> from the old version to the newest, same result.
>>>
>>> > If you can, you may want to print out the query that is being
>>> generated by putting some debug statement in DBIx::SearchBuilder.
>>> And see if it runs against the database you are using.
>>>
>>> How can i do this? Now idea at this moment.
>>>
>>> Thanks
>>>
>>> Torsten
>>>
>>> PS: The problem looks similar to yours. i also have tried this with
>>> 3.6pre1, same result.
>>>
>>>
>>> Rangarajan Radhakrishnan schrieb:
>>>
>>>>
>>>> Torsten Brumm wrote:
>>>>
>>>>> Hi RT Users,
>>>>>
>>>>> I think, i found a bug, or i'm too dumb.
>>>>>
>>>>> I'm trying the following query (from within a website, but also
>>>>> occurs from the Query Builder)
>>>>>
>>>>> ( Queue = 'GAI_ABF_NW' ) AND ( 'CF.{Tournummer}' = '03376' OR
>>>>> 'CF.{Tournummer}' = '03361' ) AND ( Status = 'new' OR Status =
>>>>> 'open' Or Status = 'stalled' OR Status = 'resolved')
>>>>>
>>>>> In this Query, CF.{Tournummer} is a normal Custom field.
>>>>>
>>>>> If i fire this Query i get the following error:
>>>>>
>>>>> [Mon May 08 13:00:45 2006] [error] [client 10.63.253.97] FastCGI:
>>>>> server "/opt/rt3/bin/mason_handler.fcgi" stderr:
>>>>> \tHTML::Mason::CGIHandler::_handler('HTML::Mason::CGIHandler=HASH(0x9dec5d8)',
>>>>> 'HASH(0x9f06f28)') called at
>>>>> /usr/lib/perl5/vendor_perl/5.8.6/HTML/Mason/CGIHandler.pm line 73,
>>>>> referer: http://astdev.int.kn/Sendungen/index.html
>>>>>
>>>>> I'm running RT 3.5.6 on Fedora Core:
>>>>>
>>>>> Perl v5.8.6 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.23;
>>>>> capitalization v0.03;
>>>>> Carp v1.03;
>>>>> CGI v3.10;
>>>>> CGI::Cookie v1.25;
>>>>> CGI::Fast v1.05;
>>>>> CGI::Util v1.5;
>>>>> Class::Container v0.12;
>>>>> Class::Data::Inheritable v0.04;
>>>>> Class::ReturnValue v0.53;
>>>>> Clone v0.18;
>>>>> constant v1.04;
>>>>> Cwd v3.01;
>>>>> Data::Dumper v2.121_02;
>>>>> Date::Format v2.22;
>>>>> Date::Parse v2.27;
>>>>> DBD::mysql v2.9007;
>>>>> DBI v1.48;
>>>>> DBIx::SearchBuilder v1.38;
>>>>> DBIx::SearchBuilder::Union v0;
>>>>> DBIx::SearchBuilder::Unique v0.01;
>>>>> Devel::StackTrace v1.12;
>>>>> Devel::StackTraceFrame v0.6;
>>>>> Devel::Symdump v2.03;
>>>>> Digest::base v1.00;
>>>>> Digest::MD5 v2.33;
>>>>> DynaLoader v1.05;
>>>>> Encode v2.08;
>>>>> Encode::Alias v2.02;
>>>>> Encode::Config v2.00;
>>>>> Encode::Encoding v2.02;
>>>>> Errno v1.09;
>>>>> Exception::Class v1.21;
>>>>> Exception::Class::Base v1.2;
>>>>> Exporter v5.58;
>>>>> Exporter::Heavy v5.58;
>>>>> FCGI v0.67;
>>>>> Fcntl v1.05;
>>>>> File::Basename v2.73;
>>>>> File::Glob v1.03;
>>>>> File::Path v1.06;
>>>>> File::Spec v3.01;
>>>>> File::Spec::Unix v1.5;
>>>>> File::Temp v0.14;
>>>>> FileHandle v2.01;
>>>>> HTML::Entities v1.29;
>>>>> HTML::Mason v1.3101;
>>>>> 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.45;
>>>>> HTML::Scrubber v0.08;
>>>>> I18N::LangTags v0.35;
>>>>> I18N::LangTags::Detect v1.03;
>>>>> integer v1.00;
>>>>> IO v1.21;
>>>>> IO::File v1.10;
>>>>> IO::Handle v1.24;
>>>>> IO::InnerFile v2.110;
>>>>> IO::Lines v2.110;
>>>>> IO::Scalar v2.110;
>>>>> IO::ScalarArray v2.110;
>>>>> IO::Seekable v1.09;
>>>>> IO::Wrap v2.110;
>>>>> IO::WrapTie v2.110;
>>>>> 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.53;
>>>>> Locale::Maketext::Lexicon::Gettext v0.14;
>>>>> Log::Dispatch v2.11;
>>>>> Log::Dispatch::Base v1.09;
>>>>> Log::Dispatch::Output v1.26;
>>>>> Log::Dispatch::Screen v1.17;
>>>>> Log::Dispatch::Syslog v1.18;
>>>>> Mail::Address v1.67;
>>>>> Mail::Field v1.67;
>>>>> Mail::Field::AddrList v1.67;
>>>>> Mail::Field::Date v1.67;
>>>>> Mail::Header v1.67;
>>>>> Mail::Internet v1.67;
>>>>> MIME::Base64 v3.05;
>>>>> MIME::Body v5.417;
>>>>> MIME::Decoder v5.417;
>>>>> MIME::Entity v5.417;
>>>>> MIME::Field::ContDisp v5.417;
>>>>> MIME::Field::ConTraEnc v5.417;
>>>>> MIME::Field::ContType v5.417;
>>>>> MIME::Field::ParamVal v5.417;
>>>>> MIME::Head v5.417;
>>>>> MIME::Parser v5.417;
>>>>> MIME::QuotedPrint v3.03;
>>>>> MIME::Tools v5.417;
>>>>> MIME::Words v5.417;
>>>>> Module::Refresh v0.08;
>>>>> Module::Versions::Report v1.02;
>>>>> overload v1.02;
>>>>> Params::Validate v0.78;
>>>>> POSIX v1.08;
>>>>> re v0.04;
>>>>> Regexp::Common v2.120;
>>>>> Regexp::Common::delimited v2.104;
>>>>> RT v3.5.6;
>>>>> RT::Interface::Email v1.02;
>>>>> 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.08;
>>>>> Text::Autoformat v1.13;
>>>>> Text::Quoted v1.8;
>>>>> Text::Reform v1.11;
>>>>> Text::Tabs v98.112801;
>>>>> Text::Template v1.44;
>>>>> Text::Wrapper v1.000;
>>>>> Time::HiRes v1.65;
>>>>> Time::JulianDay v2003.1125;
>>>>> Time::Local v1.1;
>>>>> Time::ParseDate v2003.1126;
>>>>> Time::Timezone v2003.0211;
>>>>> Time::Zone v2.22;
>>>>> utf8 v1.04;
>>>>> vars v1.01;
>>>>> warnings v1.03;
>>>>> warnings::register v1.00;
>>>>> XSLoader v0.02;
>>>>>
>>>>> Any Ideas or hints???
>>>>>
>>>>> Thanks
>>>>>
>>>>> Torsten
>>>>> _______________________________________________
>>>>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>>>>
>>>>> Community help: http://wiki.bestpractical.com
>>>>> Commercial support: sales at bestpractical.com
>>>>>
>>>>>
>>>>> Discover RT's hidden secrets with RT Essentials from O'Reilly
>>>>> Media. Buy a copy at http://rtbook.bestpractical.com
>>>>>
>>>>>
>>>>> We're hiring! Come hack Perl for Best Practical:
>>>>> http://bestpractical.com/about/jobs.html
>>>>>
>>>> I think I had come across the same problem using 3.6.0pre0. Seems
>>>> to happen when the same custom field is involved in more than one
>>>> clause in the query. Please see:
>>>> http://lists.bestpractical.com/pipermail/rt-users/2006-May/039046.html
>>>>
>>>> I haven't had a chance to dig deeper to fix this.
>>>>
>>>> If you can, you may want to print out the query that is being
>>>> generated by putting some debug statement in DBIx::SearchBuilder.
>>>> And see if it runs against the database you are using. What
>>>> database are you using?
>>>> _______________________________________________
>>>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>>>
>>>> Community help: http://wiki.bestpractical.com
>>>> Commercial support: sales at bestpractical.com
>>>>
>>>>
>>>> Discover RT's hidden secrets with RT Essentials from O'Reilly
>>>> Media. Buy a copy at http://rtbook.bestpractical.com
>>>>
>>>>
>>>> We're hiring! Come hack Perl for Best Practical:
>>>> http://bestpractical.com/about/jobs.html
>>>
>>>
>>>
>>>
>
>
More information about the rt-users
mailing list