Defect #27153
closedCustom query breaks calendar view with error 500
0%
Description
When the custom query includes a second sort criteria the calendar view breaks with an error 500.
Steps to reproduction:
- Create a custom query with a second sort criteria
- Select the custom query in the calendar view
This results into:
!Screenshot-2017-10-10 500 - Redmine.png!
Files
Related issues
Updated by Toshi MARUYAMA about 7 years ago
- Status changed from New to Needs feedback
Please see submissions.
Updated by Bernhard Rohloff about 7 years ago
- File calendar_error500_production_bitnami.log calendar_error500_production_bitnami.log added
- File calendar_error500_production_trunk.log calendar_error500_production_trunk.log added
Sorry for the useless screenshot. It's obvious that this error message was worth nothing...
I've experienced this issue on three separate Redmine instances.
A Bitnami VM V3.4.2 a manual installation with V3.4.2 and a development installation (trunk r16970).
You can find the production logs for the Bitnami VM and the trunk installation in the attachments.
Bitnami Environment:
Environment: Redmine version 3.4.2.stable Ruby version 2.3.4-p301 (2017-03-30) [x86_64-linux] Rails version 4.2.8 Environment production Database adapter Mysql2 SCM: Subversion 1.9.7 Cvs 1.12.13 Filesystem Redmine plugins: clipboard_image_paste 1.12 redmine_custom_macros 0.0.1 redmine_lightbox2 0.4.3
Trunk Installation:
Environment: Redmine version 3.4.2.devel.16970 Ruby version 2.2.7-p470 (2017-03-28) [x86_64-linux] Rails version 5.1.2 Environment production Database adapter Mysql2 SCM: Subversion 1.6.11 Mercurial 2.2.2 Bazaar 2.1.1 Git 2.7.4 Filesystem Redmine plugins: no plugin installed
Updated by Marius BĂLTEANU about 7 years ago
The same error was reported in #26544.
Updated by Bernhard Rohloff about 7 years ago
Yes, this seems to be the same error.
On all my installations it is 100% reproducible.
These cases I've tested right now:
Standard view (no filters) --> works
Custom query (standard view) with single sort criteria --> works
Custom query (standard view) with second sort criteria --> fails
MySQL Versions
Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 (trunk version)
Ver 14.14 Distrib 5.6.37, for linux-glibc2.12 (x86_64) using EditLine wrapper (Bitnami V3.4.2.stable)
Updated by Go MAEDA about 7 years ago
- Status changed from Needs feedback to Confirmed
- Target version set to 3.4.3
I confirmed the problem. 3.4-stable and trunk are affected by the problem. No problem in 3.3-stable.
According to the result of `git bisect`, r16390 caused the problem.
Updated by Bernhard Rohloff about 7 years ago
That's right. r16390 is the bad revisison. I wrote my debugging results in #26544#note-28, yesterday. The issue seems to be a duplicate and there are more guys affected by this in #26544. I also wrote two possible workarounds there, but until now, there isn't any feedback on it. Because there are two slightly different ways to fix this, I'm a bit double-minded for which solution I should create a patch.
Updated by Marius BĂLTEANU about 7 years ago
- Has duplicate Defect #26544: SQL-Error occurs sometimes when clicking on "calendar" tab added
Updated by Bernhard Rohloff about 7 years ago
- File calendars_order_error_500_cleanup.diff calendars_order_error_500_cleanup.diff added
- File calendars_order_error_500_quick.diff calendars_order_error_500_quick.diff added
I've made a patch with the second solution of my fixing proposals in #26544#note-28. Although it's not my prefered solution, it fits more to the workaround JP made in r3379.
To fix it in a cleaner way, you would have to modify the issues
method in app/models/issue_query.rb
which could break other modules (Gantt) and I have no clue how the method is intended to work.
# Returns the issues ids
# Valid options are :order, :offset, :limit, :include, :conditions
def issue_ids(options={})
order_option = [group_by_sort_order, (options[:order] || sort_clause)].flatten.reject(&:blank?)
scope = Issue.visible.
...
To make the choice of order (query or options) explicit would make more sense to me.
In this case we could remove the ugly workarounds in app/controllers/calendards_controller.rb
and app/controllers/gantts_controller.rb
(where it works already, because there are :order
options).
# Returns the issues
# Valid options are :order, :offset, :limit, :include, :conditions
def issues(options={})
order_option = ([options[:order]] || [group_by_sort_order, sort_clause]).flatten.reject(&:blank?)
scope = Issue.visible.
...
I've attached both patches, so you can choose which fits best.
Cheers!
PS: As far as I was able to test it, Gantt also seems to work well, with the "cleanup" patch.
Updated by Jean-Philippe Lang about 7 years ago
- Target version changed from 3.4.3 to 3.4.4
Can we add a failing test in the patch?
Updated by Marius BĂLTEANU about 7 years ago
- File tests_for_custom_query_breaks_calendar_view.patch tests_for_custom_query_breaks_calendar_view.patch added
Jean-Philippe Lang wrote:
Can we add a failing test in the patch?
Here is a patch with two failing tests.
Updated by Anonymous about 7 years ago
For information, this issue is triggered on all issues by the Issue Hot Buttons plugin in redmine 3.4.2 because of the way it adds "previous/next" buttons on issues:
module IssueHotButtons
module IssuesControllerPatch
def self.included(base)
base.send(:helper, :sort)
base.send(:include, SortHelper)
base.send(:include, InstanceMethods)
base.class_eval do
before_filter :nearby_issues, :only => :show
before_filter :store_last_seen_project, :only => :index
end
end
module InstanceMethods
def nearby_issues
restore_project = nil
unless session[:last_seen_project].nil?
last_seen_project = Project.find(session[:last_seen_project])
if @project.self_and_ancestors.include? last_seen_project
restore_project = @project
@project = last_seen_project
end
end
session['issues_show_sort'] = session['issues_index_sort'] unless session['issues_index_sort'].nil?
retrieve_query
sort_init(@query.sort_criteria.empty? ? [['id', 'desc']] : @query.sort_criteria)
sort_update(@query.sortable_columns)
@nearby_issues = [];
if @query.valid?
@issues = @query.issues(
:include => [:assigned_to, :tracker, :priority, :category, :fixed_version],
:order => sort_clause
)
@issues.uniq!
@issues.each {|issue| @nearby_issues.push issue.id}
end
@project = restore_project unless restore_project.nil?
end
def store_last_seen_project
session[:last_seen_project] = @project.id unless @project.nil?
end
end
end
end
If that's somewhat standard, this issue might trigger for other plugins too.
Updated by Jean-Philippe Lang almost 7 years ago
- Status changed from Confirmed to Closed
- Assignee set to Jean-Philippe Lang
- Resolution set to Fixed
Fix applied with the tests, thanks.
Updated by Matthew Paul over 6 years ago
Addition here for anyone having the same problem and can't or doesn't want to upgrade. The issue is, indeed, the issue_hot_buttons plugin (or at least it was for me). Which is a really useful addition to redmine that I didn't want to lose. So the easy fix is right here -
https://github.com/Silex/issue_hot_buttons/commit/069c4bf89c8626475566d3e40d3a45fe3f2d53f0
You can simply delete 3 lines from the plugin init.rb and then replace the issue_controller_patch.rb in the plugin with the one from that link above. So, this fix hasn't been merged into the main plugin code which is here -
https://github.com/panicinc/issue_hot_buttons
...or at least I don't think it has. So that's why I wanted to take the most advanced version from panicinc and add the fix from Silex. Full disclosure - I'm clueless at ruby/rails etc - so a) this might not be the correct way to do this and b) if I can do it then anyone can.
(Thanks to the plugin developers and as always to Jean-Philippe Lang who is a God as far as I'm concerned - Redmine is so far and away better than Jira, Service Now, and anything else, it's actually comical. Trying to explain it to people who immediately say 'oh it's a bug tracker' and I just shake my head because it is SO much more).
Updated by Nikita Remizov over 6 years ago
Created issue in issue_hot_buttons plugin https://github.com/panicinc/issue_hot_buttons/issues/15
Updated by Matthew Paul over 5 years ago
Additional info - for anyone who navs here, and uses teh hot buttons plugin. This can cause problems which may suddenly appear and not go away. This is because of the issue I mentioned above but now in some of the versions the fix doesn't work. If you want to do a quick and dirty fix just do this -
.../plugins/issue_hot_buttons/lib/issues_controller_patch.rb Line 33 or thereabouts...
@issues = @query.issues(
#WAS :include => [:assigned_to, :tracker, :priority, :category, :fixed_version],
:include => [:assigned_to, :tracker, :category, :fixed_version],
:order => sort_clause
)
That's it - just get rid of the priority, which removes the double join on the enumerations table, and seems to fix things.