794586 - Malformed SQL query results in HTTP server error: field not qualified by table

Status: RESOLVED INVALID

(Bugzilla :: Query/Bug List, defect)

Description

[Pickle-san]

Attachment: Error apparently related to column set.htm

User Agent: Mozilla/5.0 (Windows NT 5.2; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.89 Safari/537.1

Steps to reproduce:

This bug is a follow-up to an e-mail to the user list sent on 20 August 2012, entitled "Error apparently related to column set".  For convenience, I'm including the original e-mail as an attachment, which details our investigations into the problem. I have new information to report.

The problem is observed when users execute saved searches.  This is observed when executing from both the footer of the page as well as from the "Saved Searches" tab of "Preferences."  The user encounters an "Internal Server Error" in the browser.

We are getting frequent reports of this problem.  Various approaches have been tried reported to clear the problem:

o Changing the column set.
o Clearing the browser cache.
o Constructing and running a query once using the "Simple Search," then returning to the saved query.

The odd hallmark of this bug is that a query might be working fine earlier in the day, then stop working, then start working again.  I wonder whether the behavior has something to do with differences in the underlying data being retrieved.

This has been observed ever since we upgraded from 4.0.2 to 4.2.0.


Actual results:

HTTP throws an "Internal Server Error" due to malformed SQL -- see the attachment.  Specifically, although the query spans multiple joined tables, one of the fields in those tables is referenced without qualifying the table where it resides.  This is verified by examining the HTTPD error_log.

Mostly commonly, the unqualified field appearing in the SQL query is "keywords."  However, keywords is not necessarily an element of the query or the column set when the problem is observed.  Another case that sometimes becomes an "unknown column" is component.  They always appear in an "ORDER BY" clause.

We're happy to cooperate with efforts to further characterize the problem.  We'll update this bug as we learn more.  We may need to do some in-depth investigation of the data being returned when the queries succeed and what would have been returned when they fail.


Expected results:

Of course, Internal Server Errors are undesirable when running a query.

Comment 1

[Frédéric Buclin]

The fact that this issue cannot be repeated in a reproducible way but only happens from time to time makes me think that your Bugzilla accesses several databases, typically one master + several slaves. I would investigate if a slave hasn't been upgraded correctly and has a mismatched DB schema. Another possibility is that some of your files have been edited/customized by your admins (for instance Bugzilla/Search.pm), or that they have been incorrectly upgraded. If a column is used in ORDER BY, then it is automatically included in the SQL query, but the SQL query you pasted has no definition for 'keywords'. I doubt this is an upstream bug.

For more help, please visit http://www.bugzilla.org/support. You should also upgrade to Bugzilla 4.2.4 as it contains many fixes since 4.2.0.

Comment 2

[Pickle-san]

Thank you.  We'll plan to upgrade at our first opportunity.

Our Bugzilla instances does not access multiple databases -- there is only one database, with one schema.