Redmine

This seems to be a duplicate of #1139 / #3488.
You can do it if you check "Used as a filter" of the custom field and select it in "Administration/Settings/Issue tracking".

This does not help. Tried that.

I still cannot sort by the rank field. Also tried putting a value in that field for all open tickets.

Still happens in Redmine 0.9.5.stable.3824 (MySQL).

This is definitely related to sort in custom filters. As soon as I remove the rank (integer) from the sort the issue filter works fine.

I post the patch to fix #5778, and I assume it also fixes this issue.
Cyber,
can you apply the patch (attatched in #5778) and report the result?

Yuki - Is there a quick way to apply patches, or do I have to hand-edit the code?

Cyber Sprocket wrote:

Yuki - Is there a quick way to apply patches, or do I have to hand-edit the code?

Here is how to apply the patch.

$ cd your-redmine-root
$ patch -p0 < custom_field_filter.patch
patching file app/models/query.rb
patching file app/views/queries/_filters.rhtml

It doesn't crash now, but it does not return results, the table is now blank.

BTW, this happens even without custom queries. I changed the settings to show the Rank field on the default issue output. It shows up as the last column with the proper data intact. As soon as I click on the column heading to sort, the page returns but shows up blank.

You can see the effect (with the new patch in place) by going to our public portion of the site:
http://redmine.cybersprocket.com/projects/wpcafepress/issues

Click on "Rank" on the far right & it will now return the page "wireframe" but no issues table results:
http://redmine.cybersprocket.com/projects/wpcafepress/issues?sort=cf_4%2Cid%3Adesc

I found the problem:

custom_field.rb uses a cast to decimal. This is not supported in MySQL v4.1 which is what we are running on our server. MySQL v4.1 is stated as the minimum supported version in the install docs:
http://www.redmine.org/wiki/redmine/RedmineInstall

custom_field.rb¶

      when 'int', 'float'
        # Make the database cast values into numeric
        # Postgresql will raise an error if a value can not be casted!
        # CustomValue validations should ensure that it doesn't occur
        "(SELECT CAST(cv_sort.value AS decimal(60,3)) FROM #{CustomValue.table_name} cv_sort" +
          " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
          " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
          " AND cv_sort.custom_field_id=#{id} AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)" 

This fixes the problem for us... but you still have an issue of what to do with FLOAT on MySQL 4.1 since cast to decimal is not an option.

  # Returns a ORDER BY clause that can used to sort customized
  # objects by their value of the custom field.
  # Returns false, if the custom field can not be used for sorting.
  def order_statement
    case field_format
      when 'string', 'text', 'list', 'date', 'bool'
        # COALESCE is here to make sure that blank and NULL values are sorted equally
        "COALESCE((SELECT cv_sort.value FROM #{CustomValue.table_name} cv_sort" +
          " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
          " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
          " AND cv_sort.custom_field_id=#{id} LIMIT 1), '')" 
      when 'int'
        # CyberSprocket.com fix...
        # Make the database cast values into numeric
        # Postgresql will raise an error if a value can not be casted!
        # CustomValue validations should ensure that it doesn't occur
        "(SELECT CAST(cv_sort.value AS signed) FROM #{CustomValue.table_name} cv_sort" +
          " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
          " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
          " AND cv_sort.custom_field_id=#{id} AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)" 
      when 'float'
        # Make the database cast values into numeric
        # Postgresql will raise an error if a value can not be casted!
        # CustomValue validations should ensure that it doesn't occur
        "(SELECT CAST(cv_sort.value AS decimal(60,3)) FROM #{CustomValue.table_name} cv_sort" +
          " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
          " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
          " AND cv_sort.custom_field_id=#{id} AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)" 
      else
        nil
    end
  end

Cyber Sprocket wrote:

[...]

MySQL v4.1 is stated as the minimum supported version in the install docs:
http://www.redmine.org/wiki/redmine/RedmineInstall

That info is not entirely true. See e.g. issue #2755. Since Redmine 0.9.0 MySQL 5 is required to fully be compatible with the Redmine Core.

Ok - well I guess that needs to be updated on the RedmineInstall page & a more prominent notice should go out to people when they upgrade.

We started on 0.8X and just went through the normal upgrade process. Never did id complain nor warn about requiring MySQL 5.x. Maybe even put a "we recommend MySQL 5.X, you are on 4.X" on the admin/info screen.

Also, I think the patch I proposed should be considered. At the very least it gets INT custom fields working on MySQL 4 installs and I can't see how it would hurt anyone on MySQL 5.X.

JPL said he didn't want to drop Mysql 4.1 support in #2755 1 year ago, I don't know if it's still the case. I personally think people should upgrade now, Mysql 4 is far too old and not supported at all since 12/2009.

The patch provided will at least allow int support on custom fields on 4.X.

As for the MySQL upgrade to 5.X, that is great in theory however many sites including our own makes that a big undertaking. We have a half-dozen clients on the server with some using systems built on MySQL 4.x.

Side-by-side installs of MySQL can be problematic. Upgrading all of our clients will cost us time, which for us is money. Our clients won't pay for the upgrade. To them it is not broken (their apps work) so why fix it. The simple argument of "MySQL 4 is too old" sounds good on the surface, but out in the real world the issue is not so cut & dried. We too agree that people should be on supported software like MySQL 5 and PHP5, but alas that is not something we can force on our clients... and in our business our clients are our livelihood.