bugruQueue - Bugs: bug #25564, MySQL Join syntax changed in...

 
 

bug #25564: MySQL Join syntax changed in version 5, Caused search query to fail

Submitter:  None
Submitted:  Wed 11 Feb 2009 04:29:31 PM UTC
   
 
Category:  None Severity:  3 - Normal
Item Group:  None Status:  None
Privacy:  Public Assigned to:  None
Open/Closed:  Open
* Mandatory Fields

Add a New Comment Rich Markup
   

Wed 11 Feb 2009 04:32:18 PM UTC, comment #1: 

questions:  d a n m i l l -at- u m i c h -dot-  e d u

Anonymous
Wed 11 Feb 2009 04:29:31 PM UTC, original submission:  

Since mysql 5, the search function in RUQueue started throwing an error. The following query:

error with query: SELECT *,
      ticket.id as 'table_id',
      ticket.id as 'ticket.id',
      first_comment.subject as 'first_comment.subject',
      first_comment.subject as 'table_subject',
      #ticket.requester as 'ticket.requester',
      user.name as 'table_requester',
      ticket.current_status as 'ticket.current_status',
      ticket.current_status as 'table_status',
      ticket.queue as 'ticket.queue',
      ticket.queue as 'table_queue',
      ticket.date_created as 'ticket.date_created',
      DATE_FORMAT(ticket.date_created, '%m-%d-%Y') as 'table_created',
      last_comment.date_created as 'last_comment.date_created',
      DATE_FORMAT(last_comment.date_created, '%m-%d-%Y') as 'table_last',
      ticket.owner as 'table_owner',
      ticket.owner as 'ticket.owner' FROM ticket, comment as last_comment, comment as first_comment
      LEFT JOIN user on (ticket.user_id = user.id) WHERE 1=1
      and ticket.last_comment_id=last_comment.id
      and ticket.first_comment_id=first_comment.id  and ticket.owner = 'username' GROUP BY ticket.id ORDER BY ticket.id DESC

Would give you this error:

MySQL said: #1054 - Unknown column 'ticket.user_id' in 'on clause'


The problem was with a change in mysql 5 join syntax. To fix this error, open the /web-interface/search.php with your text editor, and replace the following lines:

// Create the tables part of the query
   $tables = "ticket, comment as last_comment, comment as first_comment
      LEFT JOIN user on (ticket.user_id = user.id)";

with this (add parentheses):

// Create the tables part of the query
   $tables = "(ticket, comment as last_comment, comment as first_comment)
      LEFT JOIN user on (ticket.user_id = user.id)";

And you should be all set.

(Or I've attached the updated search.php file)

Anonymous

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

Attached Files
file #17439:  search.php added by None (18KiB - application/octet-stream)

 

Depends on the following items: None found

Items that depend on this one: None found

 

CC list is empty

 

There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

Only logged-in users can vote.

 

Follows 1 latest change.

Date Changed by Updated Field Previous Value => Replaced by
2009-02-11 None Attached File- Added search.php, #17439

Back to the top

Powered by Savane 3.13-758e.
Corresponding source code