279910 - Query crashed with Software Error: "Unknown column 'bugs.assignee_accessible' "

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Paul]

User-Agent:       Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
Build Identifier: 

Software error:
DBD::mysql::st execute failed: Unknown column 'bugs.assignee_accessible' 
in 'where clause' [for Statement "SELECT bugs.bug_id, bugs.bug_severity, 
bugs.priority, bugs.bug_status, bugs.resolution, bugs.remaining_time, (SUM
(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) AS 
actual_time, bugs.bug_severity, bugs.priority, bugs.rep_platform, 
map_assigned_to.realname, bugs.bug_status, bugs.resolution, 
bugs.estimated_time, bugs.remaining_time, (SUM(ldtime.work_time)*COUNT(DISTINCT 
ldtime.bug_when)/COUNT(bugs.bug_id)) AS actual_time, (100*((SUM
(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))/((SUM
(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))
+bugs.remaining_time))) AS percentage_complete, bugs.short_desc FROM bugs, 
profiles AS map_assigned_to, longdescs AS ldtime  LEFT JOIN bug_group_map  ON 
bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN 
(12,3,8,4,13,1,6,9,11,2,5,10,7)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND 
cc.who = 1 WHERE bugs.assigned_to = map_assigned_to.userid AND ldtime.bug_id = 
bugs.bug_id AND (bugs.bug_id IN 
('1186','590','867','872','1115','853','855','870','913','920','940','1009','101
2','1026','1030','1039','1071','1087','1092','1139','1141','1144','1147','1149',
'1150','1152','1154','1157','1170','1171','1172','415','514','491','1001','1007'
,'1140','1155','1158','1162','1164','1143')) AND (bugs.bug_status IN 
('NEW','ASSIGNED','REOPENED')) AND ((bugs.assignee_accessible 
= '[e-mail]') OR (bugs.assignee_accessible = '[e-mail]')) AND 
((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND 
bugs.reporter = 1)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT 
NULL)     OR (bugs.assigned_to = 1) ) GROUP BY bugs.bug_id ORDER BY 
bugs.resolution,map_assigned_to.realname,bugs.resolution,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

For help, please send mail to the webmaster ([e-mail]), giving this 
error message and the time and date of the error. 


Reproducible: Always

Steps to Reproduce:
1. Enter a query with Advanced Using Boolean Charts
2. Select Assignee_accessible is equal to anything
3. see the error

Actual Results:  
Software error:
DBD::mysql::st execute failed: Unknown column 'bugs.assignee_accessible' 
in 'where clause' [for Statement "SELECT bugs.bug_id, bugs.bug_severity, 
bugs.priority, bugs.bug_status, bugs.resolution, bugs.remaining_time, (SUM
(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) AS 
actual_time, bugs.bug_severity, bugs.priority, bugs.rep_platform, 
map_assigned_to.realname, bugs.bug_status, bugs.resolution, 
bugs.estimated_time, bugs.remaining_time, (SUM(ldtime.work_time)*COUNT(DISTINCT 
ldtime.bug_when)/COUNT(bugs.bug_id)) AS actual_time, (100*((SUM
(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))/((SUM
(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))
+bugs.remaining_time))) AS percentage_complete, bugs.short_desc FROM bugs, 
profiles AS map_assigned_to, longdescs AS ldtime  LEFT JOIN bug_group_map  ON 
bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN 
(12,3,8,4,13,1,6,9,11,2,5,10,7)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND 
cc.who = 1 WHERE bugs.assigned_to = map_assigned_to.userid AND ldtime.bug_id = 
bugs.bug_id AND (bugs.product_id IN (11)) AND (bugs.bug_status IN 
('NEW','ASSIGNED','REOPENED')) AND (bugs.assignee_accessible 
= '[e-mail]') AND ((bug_group_map.group_id IS NULL)    OR 
(bugs.reporter_accessible = 1 AND bugs.reporter = 1)     OR 
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 
1) ) GROUP BY bugs.bug_id ORDER BY 
bugs.resolution,map_assigned_to.realname,bugs.resolution,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

For help, please send mail to the webmaster ([e-mail]), giving this 
error message and the time and date of the error. 


Expected Results:  
a list of bugs without an error

Comment 1

[Marc Schumann [:Wurblzap]]

Paul, is it possible that you have got a very old version of Bugzilla, and that
this is a duplicate of bug 97471?

Comment 2

[Paul]

No, as a matter of fact I am running the just release 2.18

FYI: bugzilla.mozilla.org CRASHES TOO!

Comment 3

[Marc Schumann [:Wurblzap]]

Confirming.

Comment 4

[Marc Schumann [:Wurblzap]]

On http://landfill.bugzilla.org/bugzilla-2.18-branch/query.cgi?format=advanced
there is no assignee_accessible in the advanced query dropdown. On
https://bugzilla.mozilla.org/query.cgi?format=advanced, there is, though.

Your 2.18 has it? Was your query maybe a named query, saved with another version?

Comment 5

[Frédéric Buclin]

Attachment: patch, v1

This patch should fix the problem (untested).

From what I understand reading checksetup.pl is that in older versions of
Bugzilla, the "bugs_activity" table kept track of the name of the fields being
modified when editing bugs. These field names were taken from the "bugs" table.
When upgrading to a newer version of Bugzilla, a new "fielddefs" table is
created (if not yet created) and a "subroutine" (at line 2807 using the latest
CVS version of checksetup.pl) populates it using fields found in the
"bugs_activity" table. The "fielddefs" table contains the field ID and its
corresponding field name. That way, the "bugs_activity" table now points to the
field ID instead of its name, and the field name stored in "bug_activity" has
been dropped.

When bbaetz removed the qacontact_accessible and the assignee_accessible
attributes from the "bugs" table (bug 97471), he forgot to remove the
corresponding entries in the "fielddefs" table, which is used in query.cgi for
boolean charts.

New installations (in opposition to upgraded installations) should not be
affected by this bug as they never had field names stored in the
"bugs_activity" table. If I'm correct, "new" here means 2.16 or later (look at
checksetup.pl v1.120).

Comment 6

[Frédéric Buclin]

Comment on attachment 172490 [details] [diff] [review]
patch, v1

>+if (GetFieldDef("fielddefs", "qacontact_accessible")) {
>+    $dbh->do("DELETE FROM fielddefs WHERE name='qacontact_accessible'");
>+    $dbh->do("DELETE FROM fielddefs WHERE name='assignee_accessible'");
>+}


Oops! I copy paste this, but I should only write:

>+$dbh->do("DELETE FROM fielddefs WHERE name='qacontact_accessible'");
>+$dbh->do("DELETE FROM fielddefs WHERE name='assignee_accessible'");

without the if (). Marc?

Comment 7

[Frédéric Buclin]

Attachment: patch, v2

Should be better...

Comment 8

[Frédéric Buclin]

humm... What happens if an entry in the activity table concerns these obsolete
fields? What should we do with them?

Removing them from the fielddefs table may cause some problems when viewing the
activity table...

Comment 9

[Marc Schumann [:Wurblzap]]

Comment on attachment 172495 [details] [diff] [review]
patch, v2

(In reply to comment #8)
> humm... What happens if an entry in the activity table concerns these obsolete
> fields? What should we do with them?
> 
> Removing them from the fielddefs table may cause some problems when viewing the
> activity table...

Yes, fielddefs either needs to contain historical fields, or checksetup.pl must
migrate. There are other places in checksetup.pl where that happens.
Unconditional fielddefs deletions occur on wrongly named fields only.

So, as I have no idea where to migrate historical bugs_activity records about
assignee_accessible -- is maybe query.cgi wrong in getting its field list from
GetFieldDefs?

Comment 10

[Frédéric Buclin]

OK, following my long and interesting discussion with Marc in IRC, I suggest to
change the ID of deprecated fields in the "fielddefs" table (and of course in
"bugs_activity" for consistency), using an easy ID -> -ID (negative value). This
way:
1) there is no need to add a "retired" column in "fielddefs";
2) there is no need to delete a part of the history from the activity table;
3) there is no need to look at the "bugs" table to see if a field still exists;
4) hum... I keep this one for people who were in the main channel. (scary idea) ;)

This way, the full history information is kept (in opposition to (2)) and we
have an easy way to know if a field is still valid (compared to (1) or (3)).
Moreover, we only need to alter GetFieldDefs() a bit, adding "WHERE fieldid>0"
in order to have valid field names for query.cgi.

Maybe I will also have to hack sanitycheck.cgi and checksetup.pl a bit, but that
should be straightforward.

I will try to resubmit a patch this week-end.

Comment 11

[Frédéric Buclin]

Attachment: patch, v3

As per comment 10.

Comment 12

[Frédéric Buclin]

Attachment: patch, v4

Using a new 'obsolete' column to mark obsolete fields, as per discussion with
myk in IRC.

Comment 13

[Myk Melez [:myk] [@mykmelez]]

Comment on attachment 175041 [details] [diff] [review]
patch, v4

>+# table should therefor be marked as obsolete, meaning that they cannot

Nit: therefor -> therefore


>-        $extra = "WHERE name NOT IN ('estimated time', 'remaining_time', " .
>+        $extra = "AND name NOT IN ('estimated time', 'remaining_time', " .

Incidentally, although this is a separate bug, shouldn't "estimated time" ->
"estimated_time"?

Otherwise, this looks good, r=myk.  I guess someone could argue that a positive
flag would work better here (f.e. "active", which positively marks extant
fields), but I'm OK with "obsolete".

Comment 14

[Frédéric Buclin]

Attachment: patch, v4.1

therefor -> therefore
estimated time -> estimated_time

Comment 15

[Frédéric Buclin]

Attachment: backport for 2.18.1, v1

bugs.assignee_accessible and bugs.qacontact_accessible make Bugzilla to crash,
so it should go in 2.18.1 too.

Moreover, the estimated time should not be accessible to users who are not in
the timetracking group, which is due to the bad SQL request which has
"estimated time" instead of "estimated_time", as mentionned by myk in his
previous review.

myk, this patch is exactly the same as the previous one, but backported to
2.18.

Comment 16

[Myk Melez [:myk] [@mykmelez]]

Comment on attachment 175443 [details] [diff] [review]
backport for 2.18.1, v1

r=myk

Comment 17

[Max Kanat-Alexander]

Note that because of fuzz, the checksetup changes ended up in a *very slightly*
different place in 2.18 than they are in 2.19. It doesn't affect anything
important, though. (It's actually either before or after another one of
LpSolit's changes that was checked-in today. :-))

Tip:

Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.354; previous revision: 1.353
done
Checking in Bugzilla/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB.pm,v  <--  DB.pm
new revision: 1.22; previous revision: 1.21
done

2.18:

Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.289.2.27; previous revision: 1.289.2.26
done
Checking in globals.pl;
/cvsroot/mozilla/webtools/bugzilla/globals.pl,v  <--  globals.pl
new revision: 1.270.2.7; previous revision: 1.270.2.6
done