Bugzilla 2.18.3 Stopped Working

At work we use Mozilla’s Bugzilla as our bug tracking solution. It is a great tool coming from the open-source community, by the way. We were still stuck on version 2.18.3 since we saw no need to upgrade it. However, today my co-worker told me he ran into an error while submitting a new bug. I quickly tried to see what the problem was and as soon as I did a search on open bugs, I saw:

Software error:

DBD::mysql::st execute failed: Unknown column 'bugs.bug_id' in 'on clause' [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to  LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id"] at Bugzilla/DB.pm line 62
	Bugzilla::DB::SendSQL('SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bu...') called at [webroot location]/buglist.cgi line 766

That is odd. First thing I checked was to see if it was a MySQL permission issue. Nope, the user permission settings were just fine. If I ran that SQL statement manually as the Bugzilla user at MySQL console, I got:

ERROR 1054 (42S22): Unknown column 'bugs.bug_id' in 'on clause'

Ok, that showed me something was not right with MySQL, rather than Bugzilla itself (or the Perl DBI and DBD::mysql modules). I did a quick Google search on that “ERROR 1054″ and it turned out that it was a bug in MySQL5 with the way it interprets a SQL statement when JOIN is involved. To confirm that, I changed the SQL statement from:

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to  LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id

To (notice the parentheses surrounding “bugs, profiles AS map_assigned_to”):

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM (bugs, profiles AS map_assigned_to)  LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id

That modified SQL statement worked fine. This also means that to make our version of Bugzilla work with MySQL 5, those statements will need to be changed to work around the bug. That is not an acceptable solution for us to do. I’m sure the Bugzilla team was aware of the problem and should have a solution for it in the latest version.

I went ahead and upgraded to latest version of Bugzilla which is 3.0. Viola! It worked. Bugzilla now works happily with MySQL 5 and my collegeue can finally go back to his bug-crushing activity.