Defect #27153

Custom query breaks calendar view with error 500

Added by Bernhard Rohloff almost 5 years ago. Updated over 3 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:


Target version:3.4.4
Resolution:Fixed Affected version:3.4.2


When the custom query includes a second sort criteria the calendar view breaks with an error 500.

Steps to reproduction:

  1. Create a custom query with a second sort criteria
  2. Select the custom query in the calendar view

This results into:

!Screenshot-2017-10-10 500 - Redmine.png!

Screenshot-2017-10-10 500 - Redmine.png (13.7 KB) Bernhard Rohloff, 2017-10-10 10:43

calendar_error500_production_trunk.log (3.95 KB) Bernhard Rohloff, 2017-10-10 15:48

calendar_error500_production_bitnami.log (4.63 KB) Bernhard Rohloff, 2017-10-10 15:48

calendars_order_error_500_quick.diff Magnifier - Workaround like in r3379 for sort_criteria (557 Bytes) Bernhard Rohloff, 2017-10-12 10:50

calendars_order_error_500_cleanup.diff Magnifier - Removes confusing workarounds in the controllers. (2.13 KB) Bernhard Rohloff, 2017-10-12 10:50

tests_for_custom_query_breaks_calendar_view.patch Magnifier (1.1 KB) Marius BALTEANU, 2017-10-16 00:12

Related issues

Duplicated by Redmine - Defect #26544: SQL-Error occurs sometimes when clicking on "calendar" tab Closed

Associated revisions

Revision 17155
Added by Jean-Philippe Lang over 4 years ago

Custom query breaks calendar view with error 500 (#27153).

Patch by Bernhard Rohloff.

Revision 17156
Added by Jean-Philippe Lang over 4 years ago

Adds tests for #27153.

Patch by Marius BALTEANU.

Revision 17157
Added by Jean-Philippe Lang over 4 years ago

Merged r17155 and r17156 to 3.4-stable (#27153).


#1 Updated by Toshi MARUYAMA almost 5 years ago

  • Status changed from New to Needs feedback

Please see submissions.

#2 Updated by Bernhard Rohloff almost 5 years ago

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:

  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
  Subversion                     1.9.7
  Cvs                            1.12.13
Redmine plugins:
  clipboard_image_paste          1.12
  redmine_custom_macros          0.0.1
  redmine_lightbox2              0.4.3

Trunk Installation:

  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
  Subversion                     1.6.11
  Mercurial                      2.2.2
  Bazaar                         2.1.1
  Git                            2.7.4
Redmine plugins:
  no plugin installed

#3 Updated by Marius BALTEANU almost 5 years ago

The same error was reported in #26544.

#4 Updated by Bernhard Rohloff almost 5 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)

#5 Updated by Go MAEDA almost 5 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.

#6 Updated by Bernhard Rohloff almost 5 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.

#7 Updated by Marius BALTEANU almost 5 years ago

  • Duplicated by Defect #26544: SQL-Error occurs sometimes when clicking on "calendar" tab added

#8 Updated by Bernhard Rohloff almost 5 years ago

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.

PS: As far as I was able to test it, Gantt also seems to work well, with the "cleanup" patch.

#9 Updated by Jean-Philippe Lang almost 5 years ago

  • Target version changed from 3.4.3 to 3.4.4

Can we add a failing test in the patch?

#10 Updated by Marius BALTEANU almost 5 years ago

Jean-Philippe Lang wrote:

Can we add a failing test in the patch?

Here is a patch with two failing tests.

#11 Updated by Anonymous almost 5 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

    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

        session['issues_show_sort'] = session['issues_index_sort'] unless session['issues_index_sort'].nil?
        sort_init(@query.sort_criteria.empty? ? [['id', 'desc']] : @query.sort_criteria)

        @nearby_issues = [];
        if @query.valid?
          @issues = @query.issues(
            :include => [:assigned_to, :tracker, :priority, :category, :fixed_version],
            :order => sort_clause
          @issues.each {|issue| @nearby_issues.push}

        @project = restore_project unless restore_project.nil?

      def store_last_seen_project
        session[:last_seen_project] = unless @project.nil?

If that's somewhat standard, this issue might trigger for other plugins too.

#12 Updated by Jean-Philippe Lang over 4 years ago

  • Status changed from Confirmed to Closed
  • Assignee set to Jean-Philippe Lang
  • Resolution set to Fixed

Fix applied with the tests, thanks.

#13 Updated by Matthew Paul about 4 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 -

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 -

...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).

#14 Updated by Nikita Remizov about 4 years ago

Created issue in issue_hot_buttons plugin

#15 Updated by Matthew Paul over 3 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.

Also available in: Atom PDF