Defect #27153

Custom query breaks calendar view with error 500

Added by Bernhard Rohloff 11 months ago. Updated 2 months ago.

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

0%

Category:Calendar
Target version:3.4.4
Resolution:Fixed Affected version:3.4.2

Description

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 8 months ago

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

Patch by Bernhard Rohloff.

Revision 17156
Added by Jean-Philippe Lang 8 months ago

Adds tests for #27153.

Patch by Marius BALTEANU.

Revision 17157
Added by Jean-Philippe Lang 8 months ago

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

History

#1 Updated by Toshi MARUYAMA 11 months ago

  • Status changed from New to Needs feedback

Please see submissions.

#2 Updated by Bernhard Rohloff 11 months 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:

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

#3 Updated by Marius BALTEANU 11 months ago

The same error was reported in #26544.

#4 Updated by Bernhard Rohloff 11 months 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 10 months 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 10 months 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 10 months ago

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

#8 Updated by Bernhard Rohloff 10 months 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.
Cheers!

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 10 months 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 10 months 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 10 months 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.

#12 Updated by Jean-Philippe Lang 8 months 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 3 months 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).

#14 Updated by Nikita Remizov 2 months ago

Created issue in issue_hot_buttons plugin https://github.com/panicinc/issue_hot_buttons/issues/15

Also available in: Atom PDF