Plugin throws exception when using Microsoft SQL Server database

[Birgos]

Hello!

I'm using Redmine 4.2.3-stable from docker image with database on Microsoft SQL Server 2019. Environment: Redmine version 4.2.3.stable Ruby version 2.7.5-p203 (2021-11-24) [x86_64-linux] Rails version 5.2.6 Environment production Database adapter SQLServer Mailer queue ActiveJob::QueueAdapters::AsyncAdapter Mailer delivery smtp SCM: Subversion 1.14.1 Mercurial 5.6.1 Bazaar 3.1.0 Git 2.30.2 Filesystem
Redmine plugins (only this one installed): redmine_extended_watchers 4.2.3

After installing the plugin I see the following error on the website if I try to open any project:

Internal error
An error occurred on the page you were trying to access.
If you continue to experience problems please contact your Redmine administrator for assistance.

If you are the Redmine administrator, check your log files for details about the error.

The logfile states: ActiveRecord::StatementInvalid (TinyTds::Error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.: EXEC sp_executesql N'SELECT [projects].* FROM [projects] WHERE [projects].[parent_id] = @0 AND ((projects.status <> 9 OR (projects.id IN (SELECT [projects].[id] FROM [projects] INNER JOIN [issues] ON [issues].[project_id] = [projects].[id] WHERE [projects].[parent_id] = 5 AND ((projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name = '' issue_tracking '') OR ((issues.id IN (SELECT [issues].[id] FROM [issues] INNER JOIN [watchers] ON [watchers].[watchable_id] = [issues].[id] AND [watchers].[watchable_type] = N'' Issue '' WHERE [watchers].[user_id] = 3)) AND (1 = 1)))) ORDER BY [projects].[lft] ASC)))) ORDER BY [projects].[lft] ASC', N'@0 int', @0 = 5): FATAL -- : FATAL -- : app/controllers/projects_controller.rb:177:inshow' lib/redmine/sudo_mode.rb:61:in sudo_mode'

Installed tiny-tds version: 2.1.5

If I configure the plugin to use Issue watchers policy: Default, I can open a project and list issues. A soon as I reactivate Issue watchers policy: Extended again, the above error occurs.

Is it possible that the plugin is not compatible with a microsoft database, like the error statement implies? ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

[maxrossello]

Yes, this is quite possible... unfortunately I can't test against mssql and most sql statements are hidden behind ActiveRecord APIs which offer the same interface towards multiple DB engines.

Just as a hint (please consider this as such) you may try to add an offset(0) statement to the following line in lib/extended_watchers_issue_patch.rb line 91:

   scope = scope.reorder("#{table_name}.id DESC").limit(options[:limit])

[Birgos]

Just as a hint (please consider this as such) you may try to add an offset(0) statement to the following line in lib/extended_watchers_issue_patch.rb line 91:

   scope = scope.reorder("#{table_name}.id DESC").limit(options[:limit])

Sorry, I am not a ruby programmer, so I tried the following changes:

   scope = scope.reorder("#{table_name}.id DESC").limit(options[:limit]).offset(0)

   scope = scope.reorder("#{table_name}.id DESC").limit(options[:limit][offset="0"])

   scope = scope.reorder("#{table_name}.id DESC").limit(options[:limit])[offset="0"]

   scope = scope.limit(options[:limit])

I also tried to disable (comment) the whole line. Still the same error. After every change i restarted (touch tmp/restart.txt) redmine also as restarting the whole container. Nothing except than deleting the rb-file finally "changed the log message", logically resulting in redmine not starting any more.

Does it need something like a recompile or something else so that the changes takes effect?

[maxrossello]

No, no recompilation is needed. The first attempt should be the correct one, but I just tried to guess where an ORDER BY might be triggered.

Looks like a problem in SQLServer adapter. Sorry I'm short of ideas.