plainblack.com
Username Password
search
Bookmark and Share
View All Tickets
Inbox Failing  (#10162)
Issue

I was receiving an error when trying to open my inbox.  The error message in webgui.log ended with:

-----------------------------------------------------------------
        LEFT JOIN userProfileData on userProfileData.userId=ibox.sentBy
        
        ORDER BY messageStatus='pending' DESC, dateStamp DESC
        
     : With place holders: .  Root cause: Unknown column 'messageStatus' in 'order clause'
-----------------------------------------------------------------

I made the following change to WebGUI/Inbox.pm and the problem seems to be resolved:

*** Inbox.pm.ek	2009-04-12 23:24:41.000000000 -0500
--- Inbox.pm.orig 2009-04-12 23:17:41.000000000 -0500
***************
*** 363,369 ****
$sortBy = qq{ibox.$sortBy};
}
else {
! $sortBy = q{messageStatus DESC, dateStamp DESC};
$sortDir = q{};
}

--- 363,369 ----
$sortBy = qq{ibox.$sortBy};
}
else {
! $sortBy = q{messageStatus='pending' DESC, dateStamp DESC};
$sortDir = q{};
}
Solution Summary
Comments
perlDreamer
0
4/14/2009 11:35 am
Which version of MySQL are you running?  I'm not having any problems with that query.
perlDreamer
0
4/14/2009 11:35 am
Feedback Requested by perlDreamer
ekennedy
0
4/14/2009 6:01 pm
MySQL Version: mysql  Ver 14.12 Distrib 5.0.21, for pc-linux-gnu (i686) using readline 5.0

is "messageStatus='pending'" a valid expression in an order by clause.  I'm not a SQL guru by any stretch of the imagination but I haven't seen that before.
perlDreamer
0
4/14/2009 6:13 pm
I'll ask around.  What bothers me most is that it works in some versions of mysql, but not in others.

That particular clause is the default one, so it gets hit pretty often.
perlDreamer
0
4/14/2009 6:31 pm
I can't find where ORDER BY column=value is valid, anywhere, so I'm accepting your patch.
Fixed in 7.7.4 and 7.6.20.
Resolved by perlDreamer
preaction
0
4/15/2009 10:08 am
Just a note, it is a valid construct, but I do not see a "messageStatus" column in the inbox anymore. I do see "status" though. Perhaps that it the reason why it doesn't work?
preaction
0
4/15/2009 10:12 am
Nevermind I see the query and see where messageStatus is defined now. Perhaps the reason it doesn't work is because messageStatus isn't a real column, it's a generated column.
perlDreamer
0
4/16/2009 6:22 pm
I reverted the change to Inbox.pm.  We're going to consider this a MySQL bug since it's valid SQL and working in other versions of MySQL.

Eric, for reference, FC10 has 5.0.77.
perlDreamer
0
4/16/2009 6:22 pm
Closing as not a WebGUI bug, but  a bug in MySQL 5.0.22
Resolved by perlDreamer
ekennedy
0
4/16/2009 9:07 pm
I'm not doubting that you are correct that it is valid but I can't find it documented anywhere.  What does it mean?  Does it  sort pending messages from non-pending messages with each grouping ordered by date?
preaction
0
4/16/2009 9:19 pm
Yes. Imagine that it's like Perl where a boolean can resolve to either 0 or 1. If "messageState=pending" then it's 1, otherwise it's 0. We order DESC so that 1 goes above 0.

Try it with a real column in your instance. SELECT * FROM asset ORDER BY assetId LIKE "PB%" DESC LIMIT 50; If that works, then we know it has to do with generated columns in the SELECT.
ekennedy
0
4/16/2009 11:50 pm
Thanks everyone.  I upgrade to 5.0.77 and everything is working as advertised.
Details
Ticket Status Closed  
Rating0.0 
Submitted Byekennedy 
Date Submitted2009-04-12 
Assigned To unassigned  
Date Assigned2010-07-31 
Assigned By 
Severity Critical (mostly not working)  
What's the bug in? WebGUI Stable  
WebGUI / WRE Version 7.6.17-stable  
URLbugs/tracker/10162
Keywords
Ticket History
4/16/2009
9:07 PM
Closed ekennedy
4/16/2009
6:22 PM
Resolved perlDreamer
4/15/2009
10:08 AM
Pending DBell
4/14/2009
6:31 PM
Resolved perlDreamer
4/14/2009
6:01 PM
Pending ekennedy
4/14/2009
11:35 AM
Feedback Requested perlDreamer
4/12/2009
11:22 PM
Ticket created ekennedy
© 2010 Plain Black Corporation | All Rights Reserved