Defect #5371
closed
MySQL error generating gantt chart
0%
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
Files
Updated by Jean-Philippe Lang over 14 years ago
A start_date must have been added to one of your table, maybe by a plugin or manually.
Updated by Max Meier over 14 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.
Updated by
Updated by Gregor Schmidt over 14 years ago
- File 5371.patch 5371.patch added
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.
Updated by Gregor Schmidt over 14 years ago
- File 5371.patch 5371.patch added
Fixing method names in patch
Updated by Max Meier over 14 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)
Updated by Max Meier over 14 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
Updated by Gregor Schmidt over 14 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.
Updated by Max Meier over 14 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".
Updated by Gregor Schmidt over 14 years ago
With 'the error is no longer present' you're referring the patch error or the reported error?
Updated by Max Meier over 14 years ago
The reported error is gone, patchin also worked against trunk.
Updated by Gregor Schmidt over 14 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.
Updated by Toshi MARUYAMA