MySQL error on 3.6.6

RT Users
1

Ive noticed an error in my RT log about a failed MySQL statement. This
only shows up when I view a ticket (eg /rt/Ticket/Display.html?id=42)
but not all tickets. It seems to only happen for certain requesters.
The query in question is:

SELECT DISTINCT main.* 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
((Users_3.id = ‘54’)) AND ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( (
(CachedGroupMembers_2.id IS NOT NULL) ) AND ( (main.Status = ‘open’) OR
(main.Status = ‘new’) ) ) ORDER BY main.Priority DESC LIMIT 10;

The error is:

Unknown column ‘Users_3.id’ in ‘on clause’

My SQL isnt quite good enough to understand all the join’s in that
statement. Any ideas where to begin on this one?

Thanks,

Jay

Full output from the logs follows (beware linewrapping)

[Fri May 9 18:03:06 2008] [warning]: DBD::mysql::st execute failed:
Unknown column ‘Users_3.id’ in ‘on clause’ at
/usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 505.
(/usr/share/perl5/DBIx/SearchBuilder/Handle.pm:505)
[Fri May 9 18:03:06 2008] [warning]: RT::Handle=HASH(0x9766528)
couldn’t execute the query ‘SELECT DISTINCT main.* 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
((Users_3.id = ‘54’)) AND ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( (
(CachedGroupMembers_2.id IS NOT NULL) ) AND ( (main.Status = ‘open’)
OR (main.Status = ‘new’) ) ) ORDER BY main.Priority DESC LIMIT 10’ at
/usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 518

DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x9766528)’,
‘SELECT DISTINCT main.* FROM ((Tickets main JOIN Groups Group…’)
called at /usr/share/perl5/DBIx/SearchBuilder.pm line 231
DBIx::SearchBuilder::_DoSearch(‘RT::Tickets=HASH(0xa7e26c0)’) called
at /usr/share/perl5/DBIx/SearchBuilder.pm line 491
DBIx::SearchBuilder::Next(‘RT::Tickets=HASH(0xa7e26c0)’) called at
/usr/share/request-tracker3.6/lib/RT/Tickets_Overlay.pm line 2670
RT::tickets::Next(‘RT::Tickets=HASH(0xa7e26c0)’) called at
/usr/share/request-tracker3.6/html/Ticket/Elements/ShowRequestor line 74
HTML::Mason::Commands::ANON() called at
/usr/share/perl5/HTML/Mason/Request.pm line 1329
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1329

HTML::Mason::Request::content(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/request-tracker3.6/html/Widgets/TitleBox line 51
HTML::Mason::Commands::ANON(‘title_href’,
‘/rt/Admin/Users/Modify.html?id=54’, ‘title’, ‘More about
jay.kline@gmail.com’) called at /usr/share/perl5/HTML/Mason/Component.pm
line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa5d4e14)’,
‘title_href’, ‘/rt/Admin/Users/Modify.html?id=54’, ‘title’, ‘More about
jay.kline@gmail.com’) called at /usr/share/perl5/HTML/Mason/Request.pm
line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘title_href’,
‘/rt/Admin/Users/Modify.html?id=54’, ‘title’, ‘More about
jay.kline@gmail.com’) called at
/usr/share/request-tracker3.6/html/Ticket/Elements/ShowRequestor line 83
HTML::Mason::Commands::ANON(‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa7d61d4)’,
‘Ticket’, ‘RT::Ticket=HASH(0xa70b294)’) called at
/usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’) called at
/usr/share/request-tracker3.6/html/Ticket/Elements/ShowSummary line 72
HTML::Mason::Commands::ANON(‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’, ‘Attachments’,
‘RT::Attachments=HASH(0xa762bdc)’) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa79959c)’,
‘Ticket’, ‘RT::Ticket=HASH(0xa70b294)’, ‘Attachments’,
‘RT::Attachments=HASH(0xa762bdc)’) called at
/usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’, ‘Attachments’,
‘RT::Attachments=HASH(0xa762bdc)’) called at
/usr/share/request-tracker3.6/html/Ticket/Display.html line 58
HTML::Mason::Commands::ANON() called at
/usr/share/perl5/HTML/Mason/Request.pm line 1329
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1329

HTML::Mason::Request::content(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/request-tracker3.6/html/Widgets/TitleBox line 51
HTML::Mason::Commands::ANON(‘title’, ‘Ticket metadata’) called
at /usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa5d4e14)’,
‘title’, ‘Ticket metadata’) called at
/usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘title’,
‘Ticket metadata’) called at
/usr/share/request-tracker3.6/html/Ticket/Display.html line 59
HTML::Mason::Commands::ANON(‘id’, 2) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa663560)’,
‘id’, 2) called at /usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘id’, 2)
called at /usr/share/request-tracker3.6/html/autohandler line 291
HTML::Mason::Commands::ANON(‘id’, 2) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa41cc40)’,
‘id’, 2) called at /usr/share/perl5/HTML/Mason/Request.pm line 1251
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘id’, 2)
called at /usr/share/perl5/HTML/Mason/Request.pm line 460
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 460
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 412

HTML::Mason::Request::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/perl5/HTML/Mason/ApacheHandler.pm line 165

HTML::Mason::Request::ApacheHandler::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/perl5/HTML/Mason/ApacheHandler.pm line 832

HTML::Mason::ApacheHandler::handle_request(‘HTML::Mason::ApacheHandler=HASH(0x971580c)’,
‘Apache2::RequestRec=SCALAR(0xa695d5c)’) called at
/usr/share/request-tracker3.6/libexec/webmux.pl line 125
eval {…} called at /usr/share/request-tracker3.6/libexec/webmux.pl
line 125
RT::Mason::handler(‘Apache2::RequestRec=SCALAR(0xa695d5c)’) called
at -e line 0
eval {…} called at -e line 0 (/usr/share/perl/5.8/Carp.pm:105)

2

Begin with README step number 3 - make testdeps.On Fri, May 9, 2008 at 11:59 PM, Jay Kline slushpupie@gmail.com wrote:

Ive noticed an error in my RT log about a failed MySQL statement. This
only shows up when I view a ticket (eg /rt/Ticket/Display.html?id=42)
but not all tickets. It seems to only happen for certain requesters.
The query in question is:

SELECT DISTINCT main.* 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
((Users_3.id = ‘54’)) AND ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( (
(CachedGroupMembers_2.id IS NOT NULL) ) AND ( (main.Status = ‘open’) OR
(main.Status = ‘new’) ) ) ORDER BY main.Priority DESC LIMIT 10;

The error is:

Unknown column ‘Users_3.id’ in ‘on clause’

My SQL isnt quite good enough to understand all the join’s in that
statement. Any ideas where to begin on this one?

Thanks,

Jay

Full output from the logs follows (beware linewrapping)

[Fri May 9 18:03:06 2008] [warning]: DBD::mysql::st execute failed:
Unknown column ‘Users_3.id’ in ‘on clause’ at
/usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 505.
(/usr/share/perl5/DBIx/SearchBuilder/Handle.pm:505)
[Fri May 9 18:03:06 2008] [warning]: RT::Handle=HASH(0x9766528)
couldn’t execute the query ‘SELECT DISTINCT main.* 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
((Users_3.id = ‘54’)) AND ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( (
(CachedGroupMembers_2.id IS NOT NULL) ) AND ( (main.Status = ‘open’)
OR (main.Status = ‘new’) ) ) ORDER BY main.Priority DESC LIMIT 10’ at
/usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 518

DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x9766528)’,
‘SELECT DISTINCT main.* FROM ((Tickets main JOIN Groups Group…’)
called at /usr/share/perl5/DBIx/SearchBuilder.pm line 231
DBIx::SearchBuilder::_DoSearch(‘RT::Tickets=HASH(0xa7e26c0)’) called
at /usr/share/perl5/DBIx/SearchBuilder.pm line 491
DBIx::SearchBuilder::Next(‘RT::Tickets=HASH(0xa7e26c0)’) called at
/usr/share/request-tracker3.6/lib/RT/Tickets_Overlay.pm line 2670
RT::tickets::Next(‘RT::Tickets=HASH(0xa7e26c0)’) called at
/usr/share/request-tracker3.6/html/Ticket/Elements/ShowRequestor line 74
HTML::Mason::Commands::ANON() called at
/usr/share/perl5/HTML/Mason/Request.pm line 1329
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1329

HTML::Mason::Request::content(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/request-tracker3.6/html/Widgets/TitleBox line 51
HTML::Mason::Commands::ANON(‘title_href’,
‘/rt/Admin/Users/Modify.html?id=54’, ‘title’, ‘More about
jay.kline@gmail.com’) called at /usr/share/perl5/HTML/Mason/Component.pm
line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa5d4e14)’,
‘title_href’, ‘/rt/Admin/Users/Modify.html?id=54’, ‘title’, ‘More about
jay.kline@gmail.com’) called at /usr/share/perl5/HTML/Mason/Request.pm
line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘title_href’,
‘/rt/Admin/Users/Modify.html?id=54’, ‘title’, ‘More about
jay.kline@gmail.com’) called at
/usr/share/request-tracker3.6/html/Ticket/Elements/ShowRequestor line 83
HTML::Mason::Commands::ANON(‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa7d61d4)’,
‘Ticket’, ‘RT::Ticket=HASH(0xa70b294)’) called at
/usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’) called at
/usr/share/request-tracker3.6/html/Ticket/Elements/ShowSummary line 72
HTML::Mason::Commands::ANON(‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’, ‘Attachments’,
‘RT::Attachments=HASH(0xa762bdc)’) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa79959c)’,
‘Ticket’, ‘RT::Ticket=HASH(0xa70b294)’, ‘Attachments’,
‘RT::Attachments=HASH(0xa762bdc)’) called at
/usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘Ticket’,
‘RT::Ticket=HASH(0xa70b294)’, ‘Attachments’,
‘RT::Attachments=HASH(0xa762bdc)’) called at
/usr/share/request-tracker3.6/html/Ticket/Display.html line 58
HTML::Mason::Commands::ANON() called at
/usr/share/perl5/HTML/Mason/Request.pm line 1329
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1329

HTML::Mason::Request::content(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/request-tracker3.6/html/Widgets/TitleBox line 51
HTML::Mason::Commands::ANON(‘title’, ‘Ticket metadata’) called
at /usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa5d4e14)’,
‘title’, ‘Ticket metadata’) called at
/usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘title’,
‘Ticket metadata’) called at
/usr/share/request-tracker3.6/html/Ticket/Display.html line 59
HTML::Mason::Commands::ANON(‘id’, 2) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa663560)’,
‘id’, 2) called at /usr/share/perl5/HTML/Mason/Request.pm line 1256
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘id’, 2)
called at /usr/share/request-tracker3.6/html/autohandler line 291
HTML::Mason::Commands::ANON(‘id’, 2) called at
/usr/share/perl5/HTML/Mason/Component.pm line 135

HTML::Mason::Component::run(‘HTML::Mason::Component::FileBased=HASH(0xa41cc40)’,
‘id’, 2) called at /usr/share/perl5/HTML/Mason/Request.pm line 1251
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 1246
HTML::Mason::Request::comp(‘undef’, ‘undef’, ‘undef’, ‘id’, 2)
called at /usr/share/perl5/HTML/Mason/Request.pm line 460
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 460
eval {…} called at /usr/share/perl5/HTML/Mason/Request.pm line 412

HTML::Mason::Request::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/perl5/HTML/Mason/ApacheHandler.pm line 165

HTML::Mason::Request::ApacheHandler::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0xa696494)’)
called at /usr/share/perl5/HTML/Mason/ApacheHandler.pm line 832

HTML::Mason::ApacheHandler::handle_request(‘HTML::Mason::ApacheHandler=HASH(0x971580c)’,
‘Apache2::RequestRec=SCALAR(0xa695d5c)’) called at
/usr/share/request-tracker3.6/libexec/webmux.pl line 125
eval {…} called at /usr/share/request-tracker3.6/libexec/webmux.pl
line 125
RT::Mason::handler(‘Apache2::RequestRec=SCALAR(0xa695d5c)’) called
at -e line 0
eval {…} called at -e line 0 (/usr/share/perl/5.8/Carp.pm:105)

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

Best regards, Ruslan.

3

That was it. I backported the package from debian testing to stable,
and naively assumed that the right dependencies were defined in the
package; they are not.

Thanks,

JayOn Fri, May 9, 2008 at 3:13 PM, Ruslan Zakirov ruz@bestpractical.com wrote:

Begin with README step number 3 - make testdeps.

On Fri, May 9, 2008 at 11:59 PM, Jay Kline slushpupie@gmail.com wrote:

Ive noticed an error in my RT log about a failed MySQL statement. This
only shows up when I view a ticket (eg /rt/Ticket/Display.html?id=42)
but not all tickets. It seems to only happen for certain requesters.

4

That was it. I backported the package from debian testing to stable,
and naively assumed that the right dependencies were defined in the
package; they are not.

We do try to get them right, please file a bug so we know what to fix.
The libdbd-mysql-dependency seems to be in line with
sbin/rt-test-dependencies.in.
Niko Tyni ntyni@debian.org