Project

General

Profile

Actions

Defect #27153

closed

Custom query breaks calendar view with error 500

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

Status:
Closed
Priority:
Normal
Category:
Calendar
Target version:
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected version:

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!


Files

Screenshot-2017-10-10 500 - Redmine.png (13.7 KB) Screenshot-2017-10-10 500 - Redmine.png Bernhard Rohloff, 2017-10-10 10:43
calendar_error500_production_trunk.log (3.95 KB) calendar_error500_production_trunk.log Bernhard Rohloff, 2017-10-10 15:48
calendar_error500_production_bitnami.log (4.63 KB) calendar_error500_production_bitnami.log Bernhard Rohloff, 2017-10-10 15:48
calendars_order_error_500_quick.diff (557 Bytes) calendars_order_error_500_quick.diff Workaround like in r3379 for sort_criteria Bernhard Rohloff, 2017-10-12 10:50
calendars_order_error_500_cleanup.diff (2.13 KB) calendars_order_error_500_cleanup.diff Removes confusing workarounds in the controllers. Bernhard Rohloff, 2017-10-12 10:50
tests_for_custom_query_breaks_calendar_view.patch (1.1 KB) tests_for_custom_query_breaks_calendar_view.patch Marius BĂLTEANU, 2017-10-16 00:12

Related issues

Has duplicate Redmine - Defect #26544: SQL-Error occurs sometimes when clicking on "calendar" tabClosed

Actions
Actions #1

Updated by Toshi MARUYAMA over 6 years ago

  • Status changed from New to Needs feedback

Please see submissions.

Actions #2

Updated by Bernhard Rohloff over 6 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:

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

Actions #3

Updated by Marius BĂLTEANU over 6 years ago

The same error was reported in #26544.

Actions #4

Updated by Bernhard Rohloff over 6 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)

Actions #5

Updated by Go MAEDA over 6 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.

Actions #6

Updated by Bernhard Rohloff over 6 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.

Actions #7

Updated by Marius BĂLTEANU over 6 years ago

  • Has duplicate Defect #26544: SQL-Error occurs sometimes when clicking on "calendar" tab added
Actions #8

Updated by Bernhard Rohloff over 6 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.
Cheers!

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

Actions #9

Updated by Jean-Philippe Lang over 6 years ago

  • Target version changed from 3.4.3 to 3.4.4

Can we add a failing test in the patch?

Actions #10

Updated by Marius BĂLTEANU over 6 years ago

Jean-Philippe Lang wrote:

Can we add a failing test in the patch?

Here is a patch with two failing tests.

Actions #11

Updated by Anonymous over 6 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.

Actions #12

Updated by Jean-Philippe Lang about 6 years ago

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

Fix applied with the tests, thanks.

Actions #13

Updated by Matthew Paul almost 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).

Actions #14

Updated by Nikita Remizov over 5 years ago

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

Actions #15

Updated by Matthew Paul almost 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.

Actions

Also available in: Atom PDF