Defect #5371

MySQL error generating gantt chart

Added by Max Meier about 10 years ago. Updated about 6 years ago.

Status:ClosedStart date:2010-04-22
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Gantt
Target version:-
Resolution: Affected version:0.9.3

Description

Hello, when I try to view a gantt chart, I get an error. The logfile shows the following:

Processing IssuesController#gantt (for 10.10.10.41 at 2010-04-22 14:49:55) [GET]
  Parameters: {"project_id"=>"pf", "action"=>"gantt", "controller"=>"issues"}
Query::StatementInvalid: Mysql::Error: Column 'start_date' in where clause is ambiguous: SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`position` AS t0_r22, `issues`.`story_points` AS t0_r23, `issues`.`remaining_hours` AS t0_r24, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `trackers`.`id` AS t3_r0, `trackers`.`name` AS t3_r1, `trackers`.`is_in_chlog` AS t3_r2, `trackers`.`position` AS t3_r3, `trackers`.`is_in_roadmap` AS t3_r4, `users`.`id` AS t4_r0, `users`.`login` AS t4_r1, `users`.`hashed_password` AS t4_r2, `users`.`firstname` AS t4_r3, `users`.`lastname` AS t4_r4, `users`.`mail` AS t4_r5, `users`.`mail_notification` AS t4_r6, `users`.`admin` AS t4_r7, `users`.`status` AS t4_r8, `users`.`last_login_on` AS t4_r9, `users`.`language` AS t4_r10, `users`.`auth_source_id` AS t4_r11, `users`.`created_on` AS t4_r12, `users`.`updated_on` AS t4_r13, `users`.`type` AS t4_r14, `users`.`identity_url` AS t4_r15, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `versions`.`id` AS t6_r0, `versions`.`project_id` AS t6_r1, `versions`.`name` AS t6_r2, `versions`.`description` AS t6_r3, `versions`.`effective_date` AS t6_r4, `versions`.`created_on` AS t6_r5, `versions`.`updated_on` AS t6_r6, `versions`.`wiki_page_title` AS t6_r7, `versions`.`status` AS t6_r8, `versions`.`sharing` AS t6_r9, `versions`.`start_date` AS t6_r10 FROM `issues`  LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id  LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id  LEFT OUTER JOIN `trackers` ON `trackers`.id = `issues`.tracker_id  LEFT OUTER JOIN `users` ON `users`.id = `issues`.assigned_to_id AND  (`users`.`type` = 'User' OR `users`.`type` = 'AnonymousUser' )  LEFT OUTER JOIN `enumerations` ON `enumerations`.id = `issues`.priority_id AND  (`enumerations`.`type` = 'IssuePriority' )  LEFT OUTER JOIN `versions` ON `versions`.id = `issues`.fixed_version_id WHERE (((issues.tracker_id IN ('4')) AND (issue_statuses.is_closed=0) AND projects.id IN (1,2,4) AND projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((((start_date>='2010-04-01' and start_date<='2010-09-30') or (effective_date>='2010-04-01' and effective_date<='2010-09-30') or (start_date<'2010-04-01' and effective_date>'2010-09-30')) and start_date is not null and due_date is null and effective_date is not null)))  ORDER BY start_date, effective_date
Rendering template within layouts/base

Environment (ruby script/about) is:

About your application's environment
Ruby version              1.8.7 (i686-linux)
RubyGems version          1.3.5
Rack version              1.0
Rails version             2.3.5
Active Record version     2.3.5
Active Resource version   2.3.5
Action Mailer version     2.3.5
Active Support version    2.3.5
Application root          /var/lib/redmine.stable
Environment               development
Database adapter          mysql
Database schema version   20100221100219

The build of redmine is Redmine 0.9.3.stable.3625

Thanks in advance.

Max

5371.patch Magnifier (5.17 KB) Gregor Schmidt, 2010-06-21 21:50

5371.patch Magnifier (5.18 KB) Gregor Schmidt, 2010-06-21 23:09

History

#1 Updated by Jean-Philippe Lang about 10 years ago

A start_date must have been added to one of your table, maybe by a plugin or manually.

#2 Updated by Max Meier about 10 years ago

That's true, ist is in this case the backlogs plugin. Nevertheless I think every column access in a statement should be qualified with its table name, since it is always possible, tthat two tables share the same column name.

#3 Updated by Mischa The Evil about 10 years ago

Added pre-tags in description.

#4 Updated by Gregor Schmidt about 10 years ago

If added the table names to the query as suggested. I took the opportunity to move the SQL down into the model layer and out of the controller.

Max: Please test the added patch to see if it works with your plugin setup.

#5 Updated by Gregor Schmidt about 10 years ago

Fixing method names in patch

#6 Updated by Max Meier about 10 years ago

Thx for the patch, but how can I apply it? I tried "patch -n 1 < 5371.patch" but it tells me there is only garbage in the patch. Perhaps this is, because I used SVN for checkout of redmine, not GIT? (my proxy does not allow git)

#7 Updated by Gregor Schmidt about 10 years ago

Try cat 5371.patch | patch -p1.

#8 Updated by Max Meier about 10 years ago

I finally managed to get git working, cloned the repo and switched to 0.9-stable via git checkout 0.9-stable, right?
Then I copied the patch into the redmine dir and tried patching:

machine redmine # cat 5371.patch | patch -p1
can't find file to patch at input line 5
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:
--------------------------
|diff --git a/app/controllers/gantts_controller.rb b/app/controllers/gantts_controller.rb
|index bc2d635..7ccafd1 100644
|--- a/app/controllers/gantts_controller.rb
|+++ b/app/controllers/gantts_controller.rb
--------------------------
File to patch:

The same with patch -p1 < 5371.patch

#9 Updated by Gregor Schmidt about 10 years ago

Hi Max,

the patch was written against Redmine trunk. There has been a refactoring of the gantt generation into a separate controller in r3695. This controller is not yet present in 0.9 and probably never will be. So you would need to switch to trunk (a.k.a. master in git) to apply and test the patch. Is this possible for you?

Otherwise I would need to port it back to 0-9-stable, which would be not that hard. Since the 1.0 release is nearly done, I did not bother about it in the first place.

#10 Updated by Max Meier about 10 years ago

I tried the patch against redmine trunk. The error is no longer present. Thank You.
For Your information: The plugin wich caused the error was "redmine_backlogs", which has a column "start_date" in its table "backlogs".

#11 Updated by Gregor Schmidt about 10 years ago

With 'the error is no longer present' you're referring the patch error or the reported error?

#12 Updated by Max Meier about 10 years ago

The reported error is gone, patchin also worked against trunk.

#13 Updated by Gregor Schmidt about 10 years ago

Thanks Max for the report. I will wrap things up, so that the current status of this issue is better reflected. This will hopefully make it easier for committers having a look at this issue.


The basic problem is, that the SQL generated to get GANTT chart data is not overly specific concerning column names. This led to an incompatibility with the redmine_backlogs Plugin, which adds some columns to one of the tables included in a JOIN, resulting in ambigious column names.

The attached patch (the newer one) refactors the data mining, i.e. SQL generation, into the Redmine::Helpers::Gantt class. To fix the actual problem, all columns are prefixed with the desired table name using ClassName.quoted_table_name to ensure proper escaping.

One may argue, that plugins should ensure compatibility to Redmine on their own, and the reported issue is really a bug in the redmine_backlogs Plugin. On the other hand the proposed fix would make schema extensions and therefore as well plugin development easier, which is regarded as on of Redmine's future goals.

#14 Updated by Mischa The Evil almost 10 years ago

  • Category set to Gantt

#15 Updated by Toshi MARUYAMA about 6 years ago

  • Status changed from New to Closed

Too old.

Also available in: Atom PDF