Hi,
I am using RT 3.6.4 with RTFM 2.2.0 RC7.
Simple searching (ie, clicking on the simple search link in the top menu)
fails when I search using an email address.
No results are returned. Searching by subject name, Ticket number, queue name
and fulltext work OK.
When searching by email address I get the following error in the logs (NOTE
I’ve changed the email address and server name I used in the example to
protect the innocent).
FastCGI: server “/usr/local/rt3/bin/mason_handler.fcgi” stderr: DBD::mysql::st
execute failed: Unknown column ‘Users_3.id’ in ‘on clause’
at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line
492., referer: http://internalserver/rt/Search/Simple.html
FastCGI: server “/usr/local/rt3/bin/mason_handler.fcgi” stderr:
RT::Handle=HASH(0x2523023c) couldn’t execute the query 'SELECT COUNT(DISTINCT
main.id) FROM ((Tickets main JOIN Groups Groups_1 ON ((Groups_1.Domain
= ‘RT::Ticket-Role’)) AND ( (Groups_1.Type = ‘Requestor’)) AND (
Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id) AND (
(CachedGroupMembers_2.MemberId = Users_3.id))) , Users Users_3 WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Users_3.EmailAddress
LIKE ‘%username@somecompany.com%’)AND(CachedGroupMembers_2.id IS NOT
NULL) ) ) ) ’
at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line
505, referer: http://internalserver/rt/Search/Simple.html
I am using mysql 5.0.45 on the server side (4.1.20 on the client side), Perl
5.8.8 and DBD::mysql v4.005, DBI v1.59, DBIx::SearchBuilder v1.43.
I have also tried with a mysql client 5.0.45. The same error is returned.
Any ideas as to why the query above is failing?
Kind regards,
Geoff
Hi,
>
> FastCGI: server "/usr/local/rt3/bin/mason_handler.fcgi" stderr:
> RT::Handle=HASH(0x2523023c) couldn't execute the query 'SELECT COUNT(DISTINCT
> main.id) FROM ((Tickets main JOIN Groups Groups_1 ON ((Groups_1.Domain
> = 'RT::Ticket-Role')) AND ( (Groups_1.Type = 'Requestor')) AND (
> Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers
> CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id) AND (
> (CachedGroupMembers_2.MemberId = Users_3.id))) , Users Users_3 WHERE
> ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
> ((main.Type = 'ticket')) AND ( ( ( (Users_3.EmailAddress
> LIKE '%username@somecompany.com%')AND(CachedGroupMembers_2.id IS NOT
> NULL) ) ) ) '
> at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line
> 505, referer: http://internalserver/rt/Search/Simple.html
Can you run that query yourself on your mysql server? At the very least you
should get an error telling you what mysql thinks is wrong with the query
which might help.
Regards
Huw
s2s company email disclaimer : http://www.s2s.ltd.uk/datasheets/email_disclaimer.pdf
s2s company registration number : 3952958
s2s VAT registration number : GB763132055
Business premises : Ground Floor, Overline House, Crawley, West Sussex, RH10 1JA
Registered address : 29 High Street, Crawley, West Sussex, RH10 1BQ
Place of registration : England
Hi Huw,On Thu, 25 Oct 2007 01:15:44 am Huw Selley wrote:
Can you run that query yourself on your mysql server? At the very least you
should get an error telling you what mysql thinks is wrong with the query
which might help.
I ran the query on the database server itself and got the same error:
ERROR 1054 (42S22): Unknown column ‘Users_3.id’ in ‘on clause’
If it take out: AND ( (CachedGroupMembers_2.MemberId = Users_3.id))
I don’t get the error. Could it be that using Users_3 before it is defined in
the following statement (Users as Users_3 WHERE ) is causing the problem?
Kind regards,
Geoff
From the desk of Geoff Roberts
Implementation Partner
AUSTRALIAN PROJECTS PTY LIMITED
S A F E K N O W L E D G E
IT Security - Data Protection
Email: geoff@apro.com.au
Mobile: 0416 210 111
NATIONAL HELP DESK SUPPORT
Sydney 02 4231 4222
Melbourne 03 9017 8222
Adelaide 08 6461 6222
Perth 08 8463 1222
Brisbane 07 3137 1555
Hobart 03 6281 2555
Canberra 02 6112 8855
Can you test with current versions of RTFM (2.2.1) and SearchBuilder
(1.49)?On Oct 24, 2007, at 10:51 AM, Geoff Roberts wrote:
Hi,
I am using RT 3.6.4 with RTFM 2.2.0 RC7.
Simple searching (ie, clicking on the simple search link in the top
menu)
fails when I search using an email address.
No results are returned. Searching by subject name, Ticket number,
queue name
and fulltext work OK.
When searching by email address I get the following error in the
logs (NOTE
I’ve changed the email address and server name I used in the
example to
protect the innocent).
FastCGI: server “/usr/local/rt3/bin/mason_handler.fcgi” stderr:
DBD::mysql::st
execute failed: Unknown column ‘Users_3.id’ in ‘on clause’
at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/
Handle.pm line
492., referer: http://internalserver/rt/Search/Simple.html
FastCGI: server “/usr/local/rt3/bin/mason_handler.fcgi” stderr:
RT::Handle=HASH(0x2523023c) couldn’t execute the query 'SELECT COUNT
(DISTINCT
main.id) FROM ((Tickets main JOIN Groups Groups_1 ON
((Groups_1.Domain
= ‘RT::Ticket-Role’)) AND ( (Groups_1.Type = ‘Requestor’)) AND (
Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId =
Groups_1.id) AND (
(CachedGroupMembers_2.MemberId = Users_3.id))) , Users Users_3 WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Users_3.EmailAddress
LIKE ‘%username@somecompany.com%’)AND(CachedGroupMembers_2.id IS NOT
NULL) ) ) ) ’
at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/
Handle.pm line
505, referer: http://internalserver/rt/Search/Simple.html
I am using mysql 5.0.45 on the server side (4.1.20 on the client
side), Perl
5.8.8 and DBD::mysql v4.005, DBI v1.59, DBIx::SearchBuilder v1.43.
I have also tried with a mysql client 5.0.45. The same error is
returned.
Any ideas as to why the query above is failing?
Kind regards,
Geoff
The rt-users Archives
SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:
If you sign up for a new RT support contract before December 31,
we’ll take
up to 20 percent off the price. This sale won’t last long, so get
in touch today.
Email us at sales@bestpractical.com or call us at +1 617 812 0745.
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
PGP.sig (186 Bytes)
Hi Jesse,On Thu, 25 Oct 2007 04:30:55 am Jesse Vincent wrote:
Can you test with current versions of RTFM (2.2.1) and SearchBuilder
(1.49)
Thanks for that - SearchBuilder was the problem.
Upgrading RTFM to 2.2.1 didn’t fix the issue, but upgrading SearchBuilder to
1.48 did.
I upgraded to 1.48 as opposed to 1.49 as I’m using FreeBSD so I thought I’d by
lazy and try the latest version of the port first.
I didn’t even notice the RTFM 2.2.1 release at the bottom of the RTFM download
page since the “Preview Releases” now fill more than a page in my browser.
Could I suggest swapping the “Preview Releases” with the “Official releases”
section on the RTFM download page. That way lazy idiots like me will see the
latest official release first.
This order on the RTFM download page would also reflect the order on the RT
download page.
By the way, when upgrading to RTFM 2.2.1 I compared the database structure in
the mysql creation script with my current database structure. I have been
using RTFM for a while and have therefore gone through a number of upgrades.
I have a raft of tables that don’t appear in the 2.2.1 mysql database creation
script. Is it safe to delete these now (FM_ArticleCFValues,
FM_ClassCustomFields, FM_CustomFieldValues, FM_CustomFields,
FM_Transactions)?
Thanks for RT and RTFM - they are great tools.
Kind regards,
Geoff
Hi Jesse,
Can you test with current versions of RTFM (2.2.1) and SearchBuilder
(1.49)
Thanks for that - SearchBuilder was the problem.
Upgrading RTFM to 2.2.1 didn’t fix the issue, but upgrading
SearchBuilder to
1.48 did.
great
I didn’t even notice the RTFM 2.2.1 release at the bottom of the
RTFM download
page since the “Preview Releases” now fill more than a page in my
browser.
Could I suggest swapping the “Preview Releases” with the “Official
releases”
section on the RTFM download page. That way lazy idiots like me
will see the
latest official release first.
This order on the RTFM download page would also reflect the order
on the RT
download page.
We pulled the list of preleases since there are now new official
releases.
Thanks for pointing that out.
By the way, when upgrading to RTFM 2.2.1 I compared the database
structure in
the mysql creation script with my current database structure. I
have been
using RTFM for a while and have therefore gone through a number of
upgrades.
I have a raft of tables that don’t appear in the 2.2.1 mysql
database creation
script. Is it safe to delete these now (FM_ArticleCFValues,
FM_ClassCustomFields, FM_CustomFieldValues, FM_CustomFields,
FM_Transactions)?
That should be safe, assuming you’ve already happily migrated your
data, or otherwise set up new data. Those are left over from when RTFM
didn’t have access to RT’s Custom Fields or Transactions.
-kevin