A bug? Searchbuilder?

Torsten_Brumm3 · May 8, 2006, 11:44am

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::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;
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

Rangarajan_Radhakris · May 8, 2006, 12:49pm

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::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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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:
Careers — Best Practical Solutions

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?

Torsten_Brumm3 · May 8, 2006, 1:32pm

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:

Rangarajan_Radhakris · May 8, 2006, 1:50pm

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:

Winn_Johnston · May 8, 2006, 1:58pm

hello all,

I have up graded to RT 3.4.5, everything seems to be working just fine. Except for the Quick Ticket Creation Box on the main page. After using the Quick Create ticket option, the ticket is never created. Using the long form it works. Am i missing an option that needs to be turned on in one of the configuration files? Thanks in advance.

Also here below is some code you might find usefull. The RT 3.4.5 uses a hex encoded password, this is diffrent than the previous versions that use a base64 encoded password. If your shop is like ours then you copy the RT users database around the network and use it to authenticate users for other apps. Below is some perl code that will re-encode the new Hex format back to the base64.

#!/usr/bin/perl

load module

use DBI;
use MIME::Base64 qw();
use bytes;
@mysqlcopy =(/usr/bin/mysqldump --host=rt.dnc.org -uroot --add-drop-table --compress --add-locks -e rt3 Users Groups CachedGroupMembers | /usr/bin/mysql --host=192.168.10.47 -uroot -plemon -C rt3);
system (@args);
#query
my $query = qq(
SELECT *
From Users
WHERE CHAR_LENGTH(Password)< 33 AND (CHAR_LENGTH(Password) != 13)
);

connect

my $dbh = DBI->connect(“DBI:mysql:database=rt3;host=localhost”, “root”, “”, {‘RaiseError’ => 1});
my $sth = $dbh->prepare($query);
$sth->execute();

iterate through resultset

print values

while(my $ref = $sth->fetchrow_hashref()) {
$old_encoded_pwd = $ref->{‘Password’};
my $md5 = MIME::Base64::encode(pack “H*”, “$old_encoded_pwd”);
print “User: $ref->{‘Name’}\n”;
print “Password: $ref->{‘Password’}\n”;
substr($md5,-3) =‘’;
print “New Password: ‘$md5’\n”;
# execute INSERT query
my $rows = $dbh->do(“update Users SET Password= ‘$md5’ WHERE Name=‘$ref->{Name}’;”);
print “$rows row(s) affected\n”;
print “----------\n”;
}

clean up

$dbh->disconnect();

Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.

Torsten_Brumm3 · May 8, 2006, 2:29pm

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:

Jesse_Vincent · May 8, 2006, 3:25pm

hello all,

I have up graded to RT 3.4.5, everything seems to be working just fine. Except for the Quick Ticket Creation Box on the main page. After using the Quick Create ticket option, the ticket is never created. Using the long form it works. Am i missing an option that needs to be turned on in one of the configuration files? Thanks in advance.

That should “just work.” Is anything showing up in RT’s logs?

Rangarajan_Radhakris · May 8, 2006, 7:19pm

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: