Defect #1289
Error in calendar
Status:
New
Priority:
Normal
Category:
Calendar
Target version:
-
Source:
Customer
Detected on version:
Blocked:
No
Alone:
Milestones:
Sprint:
Description
Hi, thanks for your highly appreciated plugins, they have proven to be a must-have for Redmine time & time again!
I'm trying to enable scrum on an existing environment (thats also running advanced roadmap).
I installed a working redmine 3.3.2/scrum, and then moved our production database (+plugins) to the new instance.
I did apply your diff's from advanced roadmap that didn't make it into the release to our prod environment.
This is what happens when opening calendar view:
ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'issues.id' in 'IN/ALL/ANY subquery': SELECT `sprints`.`id` AS t0_r0, `sprints`.`name` AS t0_r1, `sprints`.`description` AS t0_r2, `sprints`.`sprint_start_date` AS t0_r3, `sprints`.`sprint_end_date` AS t0_r4, `sprints`.`user_id` AS t0_r5, `sprints`.`project_id` AS t0_r6, `sprints`.`created_on` AS t0_r7, `sprints`.`updated_on` AS t0_r8, `sprints`.`is_product_backlog` AS t0_r9, `sprints`.`status` AS t0_r10, `sprints`.`shared` AS t0_r11, `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1, `projects`.`description` AS t1_r2, `projects`.`homepage` AS t1_r3, `projects`.`is_public` AS t1_r4, `projects`.`parent_id` AS t1_r5, `projects`.`created_on` AS t1_r6, `projects`.`updated_on` AS t1_r7, `projects`.`identifier` AS t1_r8, `projects`.`status` AS t1_r9, `projects`.`lft` AS t1_r10, `projects`.`rgt` AS t1_r11, `projects`.`inherit_members` AS t1_r12, `projects`.`default_version_id` AS t1_r13, `projects`.`product_backlog_id` AS t1_r14 FROM `sprints` INNER JOIN `projects` ON `projects`.`id` = `sprints`.`project_id` WHERE ((((projects.lft >= 23 AND projects.rgt <= 26) OR issues.id IN (SELECT issue_id FROM issues_projects WHERE project_id = 8)))) AND `sprints`.`is_product_backlog` = 0 AND (`sprints`.`sprint_start_date` BETWEEN '2016-12-26' AND '2017-02-05')):
plugins/scrum/lib/scrum/calendars_controller_patch.rb:35:in `query_sprints'
plugins/scrum/lib/scrum/calendars_controller_patch.rb:24:in `add_sprints'
plugins/advanced_roadmap/lib/advanced_roadmap/calendars_controller_patch.rb:12:in `add_milestones'
lib/redmine/sudo_mode.rb:63:in `sudo_mode'
For the moment I'm working around it by commenting out in calendars_controller_patch.rb:
#query_sprints(sprints, @query, @calendar, true)
#query_sprints(sprints, @query, @calendar, false)
response.body += view.render(:partial => 'scrum_hooks/calendars/sprints',
:locals => {:sprints => sprints})
end
# private
# def query_sprints(sprints, query, calendar, start)
# date_field = start ? 'sprint_start_date' : 'sprint_end_date'
# query.sprints.where(date_field => calendar.startdt..calendar.enddt,
# is_product_backlog: false).each do |sprint|
# sprints << {:name => sprint.name,
# :url => url_for(:controller => :sprints,
# :action => :show,
# :id => sprint.id,
# :only_path => true),
# :day => sprint.send(date_field).day,
# :week => sprint.send(date_field).cweek,
# :start => start}
# end
# end
Some info:
Environment:
Redmine version 3.3.2.stable.16215
Ruby version 2.2.4-p230 (2015-12-16) [x86_64-linux]
Rails version 4.2.7.1
Environment production
Database adapter Mysql2
SCM:
Subversion 1.7.14
Git 1.8.3.1
Filesystem
GitRemote 1.8.3.1
Redmine plugins:
a_common_libs 2.2.4
advanced_roadmap 0.10.2
issue_charts 1.3.0
redmine_agile 1.4.2
redmine_banner 0.1.2
redmine_base_deface 0.0.1
redmine_checklists 3.1.5
redmine_ckeditor 1.1.4
redmine_git_remote 0.0.1
redmine_image_clipboard_paste 3.3.0
redmine_issue_completion 0.0.2
redmine_issue_templates 0.1.3
redmine_issues_tree 0.0.9
redmine_login_audit 0.3.1
redmine_maintenance_mode 2.0.1
redmine_multiprojects_issue 3.3.0
redmine_my_page 0.1.10
redmine_shady 0.5.0
redmine_silencer 0.4.1
redmine_slack 0.2
redmine_smile_togglesidebar 1.0.4
release_logs 1.0.0
reminderemails 0.0.1
scrum 0.16.2
time_logger 0.5.4
usability 2.1.7
Updated by Emilio González Montaña about 7 years ago
- Category set to Calendar
I cannot reproduce this bug (my calendar view is working), please provide more details about the steps to achieve it...
Updated by Javier Pastor about 1 year ago
Hi, I have the same error.
I have reviewed the results of the query that gives the error and the problem seems to be in the SELECT that is in the WHERE.
If you remove the select the error is no longer thrown in the SQL query.
SELECT `sprints`.`id` AS t0_r0, `sprints`.`name` AS t0_r1, `sprints`.`description` AS t0_r2, `sprints`.`sprint_start_date` AS t0_r3, `sprints`.`sprint_end_date` AS t0_r4, `sprints`.`user_id` AS t0_r5, `sprints`.`project_id` AS t0_r6, `sprints`.`created_on` AS t0_r7, `sprints`.`updated_on` AS t0_r8, `sprints`.`is_product_backlog` AS t0_r9, `sprints`.`status` AS t0_r10, `sprints`.`shared` AS t0_r11, `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1, `projects`.`description` AS t1_r2, `projects`.`homepage` AS t1_r3, `projects`.`is_public` AS t1_r4, `projects`.`parent_id` AS t1_r5, `projects`.`created_on` AS t1_r6, `projects`.`updated_on` AS t1_r7, `projects`.`identifier` AS t1_r8, `projects`.`status` AS t1_r9, `projects`.`lft` AS t1_r10, `projects`.`rgt` AS t1_r11, `projects`.`inherit_members` AS t1_r12, `projects`.`product_backlog_id` AS t1_r13, `projects`.`default_version_id` AS t1_r14, `projects`.`default_assigned_to_id` AS t1_r15, `projects`.`activity_report_settings` AS t1_r16 FROM `sprints` INNER JOIN `projects` ON `projects`.`id` = `sprints`.`project_id` WHERE ((((projects.id = 55) OR issues.id IN (SELECT issue_id FROM issues_projects WHERE project_id = 55)))) AND `sprints`.`sprint_start_date` BETWEEN '2023-01-30' AND '2023-03-05' AND `sprints`.`is_product_backlog` = FALSE
If we review the next piece of the query we see that issues.id is being referenced and that table is not being referenced anywhere.
It may be a typo error that instead of issues.if it is projects.id?
Error:
WHERE ((((projects.id = 55) OR issues.id IN (SELECT issue_id FROM issues_projects WHERE project_id = 55)))) ANDFix???
WHERE ((((projects.id = 55) OR projects.id IN (SELECT issue_id FROM issues_projects WHERE project_id = 55)))) ANDError:
Processing by CalendarsController#show as HTML Rendering calendars/show.html.erb within layouts/base Rendered queries/_filters.html.erb (8.4ms) Rendered common/_calendar.html.erb (7.3ms) Rendered plugins/redmine_issue_templates/app/views/issue_templates/_issue_template_link.html.erb (0.6ms) Rendered issues/_sidebar.html.erb (3.8ms) Rendered calendars/show.html.erb within layouts/base (21.6ms) Rendered plugins/redmine_code_review/app/views/code_review/_html_header.html.erb (1.3ms) Rendering inline template Rendered inline template (0.3ms) Rendered plugins/redmine_wysiwyg_editor/app/views/redmine_wysiwyg_editor/_redmine_wysiwyg_editor_partial.html.erb (0.1ms) Rendered plugins/scrum/app/views/scrum_hooks/_head.html.erb (0.2ms) Rendered plugins/scrum/app/views/scrum_hooks/_scrum_tips.html.erb (0.3ms) Rendered plugins/redmine_code_review/app/views/code_review/_body_bottom.html.erb (0.1ms) Rendered plugins/redmine_drawio/app/views/redmine_drawio/_macro_dialog.html.erb (0.3ms) Rendered plugins/redmine_sticky_messages/app/views/messages/_after_top_menu.html.erb (3.1ms) Completed 500 Internal Server Error in 208ms (Views: 31.9ms | ActiveRecord: 160.3ms) ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'issues.id' in 'IN/ALL/ANY subquery': SELECT `sprints`.`id` AS t0_r0, `sprints`.`name` AS t0_r1, `sprints`.`description` AS t0_r2, `sprints`.`sprint_start_date` AS t0_r3, `sprints`.`sprint_end_date` AS t0_r4, `sprints`.`user_id` AS t0_r5, `sprints`.`project_id` AS t0_r6, `sprints`.`created_on` AS t0_r7, `sprints`.`updated_on` AS t0_r8, `sprints`.`is_product_backlog` AS t0_r9, `sprints`.`status` AS t0_r10, `sprints`.`shared` AS t0_r11, `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1, `projects`.`description` AS t1_r2, `projects`.`homepage` AS t1_r3, `projects`.`is_public` AS t1_r4, `projects`.`parent_id` AS t1_r5, `projects`.`created_on` AS t1_r6, `projects`.`updated_on` AS t1_r7, `projects`.`identifier` AS t1_r8, `projects`.`status` AS t1_r9, `projects`.`lft` AS t1_r10, `projects`.`rgt` AS t1_r11, `projects`.`inherit_members` AS t1_r12, `projects`.`product_backlog_id` AS t1_r13, `projects`.`default_version_id` AS t1_r14, `projects`.`default_assigned_to_id` AS t1_r15, `projects`.`activity_report_settings` AS t1_r16 FROM `sprints` INNER JOIN `projects` ON `projects`.`id` = `sprints`.`project_id` WHERE ((((projects.id = 55) OR issues.id IN (SELECT issue_id FROM issues_projects WHERE project_id = 55)))) AND `sprints`.`sprint_start_date` BETWEEN '2023-01-30' AND '2023-03-05' AND `sprints`.`is_product_backlog` = FALSE): plugins/scrum/lib/scrum/calendars_controller_patch.rb:35:in `query_sprints' plugins/scrum/lib/scrum/calendars_controller_patch.rb:24:in `add_sprints' lib/redmine/sudo_mode.rb:61:in `sudo_mode'
Info:
Environment: Redmine version 4.2.9.stable.22090 Ruby version 2.7.6-p219 (2022-04-12) [x86_64-linux] Rails version 5.2.8.1 Environment production Database adapter Mysql2 Mailer queue ActiveJob::QueueAdapters::AsyncAdapter Mailer delivery smtp SCM: Subversion 1.14.2 Mercurial 6.1.4 Cvs 1.12.12 Git 2.35.1 Filesystem GitMirror 2.35.1 Redmine plugins: custom_tables 1.0.6 redmine_base_deface 1.6.2 redmine_base_stimulusjs 1.1.1 redmine_code_review 1.1.0 redmine_dashboard 2.12.4 redmine_documents_short 0.4.0 redmine_drawio 1.4.6 redmine_git_mirror 0.8.0 redmine_issue_badge 0.1.4 redmine_issue_dynamic_edit 0.9.1 redmine_issue_evm 5.6.1 redmine_issue_favicon 0.1.0 redmine_issue_templates 1.1.0 redmine_issue_todo_lists 1.3 redmine_ldap_sync 2.1.1.devel redmine_logs 0.3.0 redmine_multiprojects_issue 5.0.2 redmine_my_page 0.1.13 redmine_pam_auth 0.1.0 redmine_status_history 1.0.1 redmine_sticky_messages 1.0.1 redmine_theme_changer 0.6.0 redmine_wysiwyg_editor 0.28.0 scrum 0.23.0