Chuck Caplan: Bugzilla and mySQL 5.0

« Just Added - Del.icio.us Bookmarks on Sidebar | Main | The Last Minute of 2005 to Have 61 Seconds »

October 26, 2005

Bugzilla and mySQL 5.0

I have been using Bugzilla for several months as a way to track bugs and features in various projects.

Here is the official description from bugzilla.org:

"Bugzilla is server software designed to help you manage software development."

It is not designed to work with mySQL 5.0 (yet), but I wanted to get it to work so I could start playing around with some of mySQL 5's new features like triggers, stored procedures and views. Here is how I did it.

The key problem is the way mySQL 5 handles JOIN's compared to mySQL 4.1. From the Release Notes:

Incompatible change: Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see Section 13.2.7.1, 'JOIN Syntax'.

Let me say that I do not use nearly all of the features available in Bugzilla. I do however open, look at and re-assign bugs, make comments, etc. Even though these changes worked for me, install them at your own risk.

The following code allowed Bugzilla Version 2.18.3 to work with mySQL 5.0.15-NT on my PC. Basically, I just had to put parentheses around the table names after the FROM but before the JOIN.

C:\Bugzilla\Bugzilla\Search.pm, line 1110
change " FROM $suppstring"
to " FROM ($suppstring)"

C:\Bugzilla\Bugzilla\FlagType.pm, line 104
change "FROM flagtypes, flag${type}clusions " .
to "FROM (flagtypes, flag${type}clusions) " .

C:\Bugzilla\Bugzilla\FlagType.pm, line 59
change my @base_tables = ("flagtypes");
to my @base_tables = ("(flagtypes");

C:\Bugzilla\Bugzilla\FlagType.pm, line 363
change push(@$tables, ", flaginclusions");
to push(@$tables, ", flaginclusions)");

I also had to run this SQL:

alter table bugs change keywords keywords mediumtext null;
alter table bugs change status_whiteboard status_whiteboard mediumtext
null;

Good luck!

Posted by Chuck at October 26, 2005 07:31 PM

Trackback Pings

TrackBack URL for this entry:
http://www.chuckcaplan.com/blog/mt-tb.cgi/29

Comments

Chuck, thanks for the info... that worked!!

Just upgraded to MySQL v5.0.15 with Bugzilla 2.18. Was worried that I had to downgrade. If you come across other issues, please post it. Thanks.

Posted by: bim at November 3, 2005 03:01 PM

Great. I am glad to hear it. I have been using Bugzilla with mySQL 5 now for about 2 weeks with those modifications and have not noticed any other issues.

Posted by: Chuck Caplan at November 3, 2005 03:10 PM

You rock!!

Posted by: Adi at November 10, 2005 01:39 PM

chuck, came across the error below when searching for a bug using a keyword. Any ideas on what file to modify?

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, (SUM(MATCH(longdescs_0.thetext) AGAINST('header'))/COUNT(MATCH(longdescs_0.thetext) AGAINST('header')) + MATCH(bugs.short_desc) AGAINST('header')) AS relevance FROM (bugs, profiles AS map_assigned_to INNER JOIN longdescs longdescs_0 ON (bugs.bug_id = longdescs_0.bug_id)) LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id AND bug_group_map.group_id NOT IN (7,6,3,5,2,4,8,1) LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 3 WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND (bugs.product_id IN (2)) AND ((MATCH(longdescs_0.thetext) AGAINST('header')) OR (lower(bugs.short_desc) regexp '(^|[^a-z0-9])header($|[^a-z0-9])')) AND ((bug_group_map.group_id IS NULL) OR (bugs.reporter_accessible = 1 AND bugs.reporter = 3) OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR (bugs.assigned_to = 3) ) GROUP BY bugs.bug_id ORDER BY relevance desc LIMIT 200"] at Bugzilla/DB.pm line 62
Bugzilla::DB::SendSQL('SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bu...') called at /usr/local/httpd/wwwroot/others/bugzilla/buglist.cgi line 766

Posted by: bim at November 10, 2005 08:40 PM

Hmm, that's a tough one. It looks like the offending code is in Search.pm around the foreach statement starting at line 1087:
foreach my $str (@supptables) {
...
}
The issue is that you would need to code parenthesis around the table names but before the join, i.e.
... FROM ((bugs, profiles AS map_assigned_to) INNER JOIN ...
However, the code does not make that easy. I don't use that feature from that screen so I never noticed it before, unfortunately. If someone does figure it out, please post it here if possible. Also, I posted a msg on the Bugzilla newsgroup and it looks like all this will be fixed in 2.22.
Chuck

Posted by: Chuck Caplan at November 11, 2005 12:42 PM

Hi, lots of thanks to Chuck for the workaround but, I was in the need to do a fresh install using mysql 5 so, realized that checksetup.pl script that creates the tables in the DB was giving some errors about the default values in some fields. Using Chuck's solution I was able to modify checksetup.pl to create all the tables and admin user without errors on mysql 5.

You can download the modified script in the URL given with this comment.

note: I'm not sure if this solution is working 100% correctly, I'm afraid that no, so don't use it for production, we'll have to wait until mysql5 compatible bugzilla release.

Posted by: victor at November 22, 2005 10:26 AM

Thank you Chuck for the good work and fine explanation! It really saved my day, not only when modifying Bugzilla (ver 2.18 to wit.) but also some other .JSP and .CGI-scripts that I had problems with, while upgrading to MySQL 5.0.16.

Posted by: Normann Aa. Nielsen at November 24, 2005 05:02 AM

I am running MySQL 5.0.16

I get this same error:

Software error:

I fixed it by changing line 1110 of my Search.PM near:

line 1109: my $query = "SELECT " . join(', ', @fields) .
line 1110: " FROM $suppstring" .
line 1111: " LEFT JOIN bug_group_map " .
line 1112: " ON bug_group_map.bug_id = bugs.bug_id ";

change line 1110 to:
" FROM ($suppstring)" .

Posted by: David Yerrington at December 13, 2005 05:17 PM

Scratch that last comment (and this one). It was the first thing you suggested fixing!

DUR for me hehe!

Posted by: Dave at December 13, 2005 05:20 PM

Hi Chuck,

Seems everyone had this problem right about december :), just wanna thank you for the post, you cut my sleepness night of 24hrs to lots of sleep, 2mins after i saw your post

Posted by: Kalu Chijioke at December 16, 2005 04:43 AM

I've just follwed your instructions to get my bugzilla 2.18.3 working with a mysql 5.0.16 upgrade. Seems to be working just fine, so a big thank you!

Posted by: Richard Hall at December 22, 2005 11:06 AM

Dude, you just saved me some major headache!

Thank you!

Posted by: dave at December 28, 2005 09:43 AM

I wish I had found this before I arrived at the same fixes! In any case I am still trying to figure out how to get searching by cc list memeber or commenter I get a sql error. It is quite troublesome to fix as fixes for it seem to break my bugs. Has anyone come up with a fix?

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, profiles AS map_reporter LEFT JOIN cc cc_CC0 ON (bugs.bug_id = cc_CC0.bug_id AND cc_CC0.who IN(54))) LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id AND bug_group_map.group_id NOT IN (6,7) LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 54 WHERE bugs.assigned_to = map_assigned_to.userid AND bugs.assigned_to = map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND ((map_assigned_to.login_name IN ('removed_for_security')) OR (map_reporter.login_name IN ('removed_for_security')) OR (cc_CC0.who IS NOT NULL)) AND ((bug_group_map.group_id IS NULL) OR (bugs.reporter_accessible = 1 AND bugs.reporter = 54) OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR (bugs.assigned_to = 54) ) GROUP BY bugs.bug_id ORDER 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 /var/www/html/bugzilla/buglist.cgi line 766

Posted by: James Nobis at January 13, 2006 01:21 PM

Also, if you do a quick search and type anything in words you will bomb:

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, (SUM(MATCH(longdescs_0.thetext) AGAINST('avid'))/COUNT(MATCH(longdescs_0.thetext) AGAINST('avid')) + MATCH(bugs.short_desc) AGAINST('avid')) AS relevance FROM (bugs, profiles AS map_assigned_to INNER JOIN longdescs longdescs_0 ON (bugs.bug_id = longdescs_0.bug_id)) LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id AND bug_group_map.group_id NOT IN (6,7) LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 54 WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND ((MATCH(longdescs_0.thetext) AGAINST('avid')) OR (lower(bugs.short_desc) regexp '(^|[^a-z0-9])avid($|[^a-z0-9])')) AND ((bug_group_map.group_id IS NULL) OR (bugs.reporter_accessible = 1 AND bugs.reporter = 54) OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR (bugs.assigned_to = 54) ) GROUP BY bugs.bug_id ORDER BY relevance desc LIMIT 200"] at Bugzilla/DB.pm line 62

Adding a ) to the end of line 80 in Search.pm will break other searches.

Posted by: James Nobis at January 13, 2006 05:40 PM

Hi Chuck,

Many thanks for your sagely advice. Could not have got this to work otherwise.

BTW most folks visiting here probably already know this but firing up bugzilla over MySQL 5 can give a DB error along the lines of

'can't find mysql.so from DBD:mysql libmysqclient.so.14' .

Installing DBD::mysql via CPAN
puts libmysqclient.so.15 libs in /usr/lib and /usr/lib/mysql. Creating softlinks using

'ln -s libmysqlclient.so.15 libmysqlclient.so.14'

seems to fix the problem.

Thanks again Chuck !

Posted by: lance at January 18, 2006 12:40 PM

This rocks a lot, thank u for your help

Posted by: Rangel at June 29, 2006 10:35 AM

Got this email from Moin:

Thanks for great tips chuck... however like anybody else i was getting error while searching by keywords...

I did a small research and i was able to solve this problem, i dunno anybody would need it or not.but here it is.

Go to Search.pm and find the following code

1. my $suppstring = "bugs";

2. foreach my $str (@supptables) {

3. if (!$suppseen{$str}) {

4. if ($str !~ /^(LEFT|INNER) JOIN/i) {

6. }

7. $suppstring .= " $str";

8. $suppseen{$str} = 1;

Replace line number 7 with this

7. $suppstring .= " CROSS JOIN ";

I didn't put actual line numbers because i have things on different line numbers than standard because of some customizations ...

Posted by: Chuck Caplan at May 8, 2007 09:36 PM

Chuck Caplan

Because there are just not enough blogs out there.

October 26, 2005

Bugzilla and mySQL 5.0

Trackback Pings

Comments

Post a comment