search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
972 stars 558 forks source link

Invalid SQL generated in Redmine #204

Closed sussexrick closed 12 years ago

sussexrick commented 12 years ago

I'm setting up Redmine 2.0.0 on Ruby 1.8.7 and SQL Server 2005. "gem list" reports TinyTDS version as tiny_tds (0.5.1 x86-mingw32, 0.4.5 x86-mingw32).

If I create a view sorted by one of Redmine's standard fields, it works. If I create a view sorted or grouped by any custom field, it fails with invalid SQL. It includes a LIMIT clause which should be TOP. Here's an example query with the error highlighted:

EXEC sp_executesql N'SELECT TOP (25) t0_r0, t0_r1, t0_r2, t0_r3, t0_r4, t0_r5, t0_r6, t0_r7, t0_r8, t0_r9, t0_r10, t0_r11, t0_r12, t0_r13, t0_r14, t0_r15, t0_r16, t0_r17, t0_r18, t0_r19, t0_r20, t0_r21, t0_r22, t1_r0, t1_r1, t1_r2, t1_r3, t1_r4, t1_r5, t1_r6, t1_r7, t1_r8, t1_r9, t1_r10, t1_r11, t2_r0, t2_r1, t2_r2, t2_r3, t2_r4, t2_r5, t3_r0, t3_r1, t3_r2, t3_r3, t3_r4, t3_r5, t3_r6, t3_r7, t3_r8, t3_r9, t3_r10, t3_r11, t3_r12, t3_r13, t3_r14, t3_r15, t3_r16, t4_r0, t4_r1, t4_r2, t4_r3, t4_r4, t5_r0, t5_r1, t5_r2, t5_r3, t5_r4, t5_r5, t5_r6, t5_r7, t6_r0, t6_r1, t6_r2, t6_r3, t7_r0, t7_r1, t7_r2, t7_r3, t7_r4, t7_r5, t7_r6, t7_r7, t7_r8, t7_r9 FROM ( SELECT ROW_NUMBER() OVER (ORDER BY COALESCE((SELECT cv_sort.value FROM custom_values cv_sort WHERE cv_sort.customized_type=''Issue'' AND cv_sort.customized_id=issues.id AND cv_sort.custom_field_id=16 LIMIT 1), '''') ASC) AS [rn], [issues].[id] AS t0_r0, [issues].[tracker_id] AS t0_r1, [issues].[project_id] AS t0_r2, [issues].[subject] AS t0_r3, [issues].[description] AS t0_r4, [issues].[due_date] AS t0_r5, [issues].[category_id] AS t0_r6, [issues].[status_id] AS t0_r7, [issues].[assigned_to_id] AS t0_r8, [issues].[priority_id] AS t0_r9, [issues].[fixed_version_id] AS t0_r10, [issues].[author_id] AS t0_r11, [issues].[lock_version] AS t0_r12, [issues].[created_on] AS t0_r13, [issues].[updated_on] AS t0_r14, [issues].[start_date] AS t0_r15, [issues].[done_ratio] AS t0_r16, [issues].[estimated_hours] AS t0_r17, [issues].[parent_id] AS t0_r18, [issues].[root_id] AS t0_r19, [issues].[lft] AS t0_r20, [issues].[rgt] AS t0_r21, [issues].[is_private] AS t0_r22, [projects].[id] AS t1_r0, [projects].[name] AS t1_r1, [projects].[description] AS t1_r2, [projects].[homepage] AS t1_r3, [projects].[is_public] AS t1_r4, [projects].[parent_id] AS t1_r5, [projects].[created_on] AS t1_r6, [projects].[updated_on] AS t1_r7, [projects].[identifier] AS t1_r8, [projects].[status] AS t1_r9, [projects].[lft] AS t1_r10, [projects].[rgt] AS t1_r11, [issue_statuses].[id] AS t2_r0, [issue_statuses].[name] AS t2_r1, [issue_statuses].[is_closed] AS t2_r2, [issue_statuses].[is_default] AS t2_r3, [issue_statuses].[position] AS t2_r4, [issue_statuses].[default_done_ratio] AS t2_r5, [users].[id] AS t3_r0, [users].[login] AS t3_r1, [users].[hashed_password] AS t3_r2, [users].[firstname] AS t3_r3, [users].[lastname] AS t3_r4, [users].[mail] AS t3_r5, [users].[admin] AS t3_r6, [users].[status] AS t3_r7, [users].[last_login_on] AS t3_r8, [users].[language] AS t3_r9, [users].[auth_source_id] AS t3_r10, [users].[created_on] AS t3_r11, [users].[updated_on] AS t3_r12, [users].[type] AS t3_r13, [users].[identity_url] AS t3_r14, [users].[mail_notification] AS t3_r15, [users].[salt] AS t3_r16, [trackers].[id] AS t4_r0, [trackers].[name] AS t4_r1, [trackers].[is_in_chlog] AS t4_r2, [trackers].[position] AS t4_r3, [trackers].[is_in_roadmap] AS t4_r4, [enumerations].[id] AS t5_r0, [enumerations].[name] AS t5_r1, [enumerations].[position] AS t5_r2, [enumerations].[is_default] AS t5_r3, [enumerations].[type] AS t5_r4, [enumerations].[active] AS t5_r5, [enumerations].[project_id] AS t5_r6, [enumerations].[parent_id] AS t5_r7, [issue_categories].[id] AS t6_r0, [issue_categories].[project_id] AS t6_r1, [issue_categories].[name] AS t6_r2, [issue_categories].[assigned_to_id] AS t6_r3, [versions].[id] AS t7_r0, [versions].[project_id] AS t7_r1, [versions].[name] AS t7_r2, [versions].[description] AS t7_r3, [versions].[effective_date] AS t7_r4, [versions].[created_on] AS t7_r5, [versions].[updated_on] AS t7_r6, [versions].[wiki_page_title] AS t7_r7, [versions].[status] AS t7_r8, [versions].[sharing] AS t7_r9 FROM [issues] LEFT OUTER JOIN [projects] ON [projects].[id] = [issues].[project_id] LEFT OUTER JOIN [issue_statuses] ON [issue_statuses].[id] = [issues].[status_id] LEFT OUTER JOIN [users] ON [users].[id] = [issues].[assigned_to_id] LEFT OUTER JOIN [trackers] ON [trackers].[id] = [issues].[tracker_id] LEFT OUTER JOIN [enumerations] ON [enumerations].[id] = [issues].[priority_id] AND [enumerations].[type] IN (N''IssuePriority'') LEFT OUTER JOIN [issue_categories] ON [issue_categories].[id] = [issues].[category_id] LEFT OUTER JOIN [versions] ON [versions].[id] = [issues].[fixed_version_id] WHERE (projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name=''issue_tracking'')) AND (projects.id IN (59,61,60)) ) AS [rnt] WHERE [rnt].[rn] > (0) ORDER BY [rnt].[rn] ASC'

I'm new to Ruby and I'm not sure how the pieces fit together, but I'm told this might be a problem with the adapter rather than Redmine.

rails-sqlserver commented 12 years ago

What version of the ActiveRecord and what version of the adapter are you using?

On May 24, 2012, at 4:46 AM, caterwomtious wrote:

I'm setting up Redmine 2.0.0 on Ruby 1.8.7 and SQL Server 2005. "gem list" reports TinyTDS version as tiny_tds (0.5.1 x86-mingw32, 0.4.5 x86-mingw32).

If I create a view sorted by one of Redmine's standard fields, it works. If I create a view sorted or grouped by any custom field, it fails with invalid SQL. It includes a LIMIT clause which should be TOP. Here's an example query with the error highlighted:

EXEC sp_executesql N'SELECT TOP (25) t0_r0, t0_r1, t0_r2, t0_r3, t0_r4, t0_r5, t0_r6, t0_r7, t0_r8, t0_r9, t0_r10, t0_r11, t0_r12, t0_r13, t0_r14, t0_r15, t0_r16, t0_r17, t0_r18, t0_r19, t0_r20, t0_r21, t0_r22, t1_r0, t1_r1, t1_r2, t1_r3, t1_r4, t1_r5, t1_r6, t1_r7, t1_r8, t1_r9, t1_r10, t1_r11, t2_r0, t2_r1, t2_r2, t2_r3, t2_r4, t2_r5, t3_r0, t3_r1, t3_r2, t3_r3, t3_r4, t3_r5, t3_r6, t3_r7, t3_r8, t3_r9, t3_r10, t3_r11, t3_r12, t3_r13, t3_r14, t3_r15, t3_r16, t4_r0, t4_r1, t4_r2, t4_r3, t4_r4, t5_r0, t5_r1, t5_r2, t5_r3, t5_r4, t5_r5, t5_r6, t5_r7, t6_r0, t6_r1, t6_r2, t6_r3, t7_r0, t7_r1, t7_r2, t7_r3, t7_r4, t7_r5, t7_r6, t7_r7, t7_r8, t7_r9 FROM ( SELECT ROW_NUMBER() OVER (ORDER BY COALESCE((SELECT cv_sort.value FROM custom_values cv_sort WHERE cv_sort.customized_type=''Issue'' AND cv_sort.customized_id=issues.id AND cv_sort.custom_field_id=16 LIMIT 1), '''') ASC) AS [rn], [issues].[id] AS t0_r0, [issues].[tracker_id] AS t0_r1, [issues].[project_id] AS t0_r2, [issues].[subject] AS t0_r3, [issues].[description] AS t0_r4, [issues].[due_date] AS t0_r5, [issues].[category_id] AS t0_r6, [issues].[status_id] AS t0_r7, [issues].[assigned_to_id] AS t0_r8, [issues].[priority_id] AS t0_r9, [issues].[fixed_version_id] AS t0_r10, [issues].[author_id] AS t0_r11, [issues].[lock_version] AS t0_r12, [issues].[created_on] AS t0_r13, [issues].[updated_on] AS t0_r14, [issues].[start_date] AS t0_r15, [issues].[done_ratio] AS t0_r16, [issues].[estimated_hours] AS t0_r17, [issues].[parent_id] AS t0_r18, [issues].[root_id] AS t0_r19, [issues].[lft] AS t0_r20, [issues].[rgt] AS t0_r21, [issues].[is_private] AS t0_r22, [projects].[id] AS t1_r0, [projects].[name] AS t1_r1, [projects].[description] AS t1_r2, [projects].[homepage] AS t1_r3, [projects].[is_public] AS t1_r4, [projects].[parent_id] AS t1_r5, [projects].[created_on] AS t1_r6, [projects].[updated_on] AS t1_r7, [projects].[identifier] AS t1_r8, [projects].[status] AS t1_r9, [projects].[lft] AS t1_r10, [projects].[rgt] AS t1_r11, [issue_statuses].[id] AS t2_r0, [issue_statuses].[name] AS t2_r1, [issue_statuses].[is_closed] AS t2_r2, [issue_statuses].[is_default] AS t2_r3, [issue_statuses].[position] AS t2_r4, [issue_statuses].[default_done_ratio] AS t2_r5, [users].[id] AS t3_r0, [users].[login] AS t3_r1, [users].[hashed_password] AS t3_r2, [users].[firstname] AS t3_r3, [users].[lastname] AS t3_r4, [users].[mail] AS t3_r5, [users].[admin] AS t3_r6, [users].[status] AS t3_r7, [users].[last_login_on] AS t3_r8, [users].[language] AS t3_r9, [users].[auth_source_id] AS t3_r10, [users].[created_on] AS t3_r11, [users].[updated_on] AS t3_r12, [users].[type] AS t3_r13, [users].[identity_url] AS t3_r14, [users].[mail_notification] AS t3_r15, [users].[salt] AS t3_r16, [trackers].[id] AS t4_r0, [trackers].[name] AS t4_r1, [trackers].[is_in_chlog] AS t4_r2, [trackers].[position] AS t4_r3, [trackers].[is_in_roadmap] AS t4_r4, [enumerations].[id] AS t5_r0, [enumerations].[name] AS t5_r1, [enumerations].[position] AS t5_r2, [enumerations].[is_default] AS t5_r3, [enumerations].[type] AS t5_r4, [enumerations].[active] AS t5_r5, [enumerations].[project_id] AS t5_r6, [enumerations].[parent_id] AS t5_r7, [issue_categories].[id] AS t6_r0, [issue_categories].[project_id] AS t6_r1, [issue_categories].[name] AS t6_r2, [issue_categories].[assigned_to_id] AS t6_r3, [versions].[id] AS t7_r0, [versions].[project_id] AS t7_r1, [versions].[name] AS t7_r2, [versions].[description] AS t7_r3, [versions].[effective_date] AS t7_r4, [versions].[created_on] AS t7_r5, [versions].[updated_on] AS t7_r6, [versions].[wiki_page_title] AS t7_r7, [versions].[status] AS t7_r8, [versions].[sharing] AS t7_r9 FROM [issues] LEFT OUTER JOIN [projects] ON [projects].[id] = [issues].[project_id] LEFT OUTER JOIN [issue_statuses] ON [issue_statuses].[id] = [issues].[status_id] LEFT OUTER JOIN [users] ON [users].[id] = [issues].[assigned_to_id] LEFT OUTER JOIN [trackers] ON [trackers].[id] = [issues].[tracker_id] LEFT OUTER JOIN [enumerations] ON [enumerations].[id] = [issues].[priority_id] AND [enumerations].[type] IN (N''IssuePriority'') LEFT OUTER JOIN [issue_categories] ON [issue_categories].[id] = [issues].[category_id] LEFT OUTER JOIN [versions] ON [versions].[id] = [issues].[fixed_version_id] WHERE (projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name=''issue_tracking'')) AND (projects.id IN (59,61,60)) ) AS [rnt] WHERE [rnt].[rn] > (0) ORDER BY [rnt].[rn] ASC'

I'm new to Ruby and I'm not sure how the pieces fit together, but I'm told this might be a problem with the adapter rather than Redmine.

Reply to this email directly or view it on GitHub: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/204

sussexrick commented 12 years ago

Here's the output from "gem list":

* LOCAL GEMS *

abstract (1.0.0) actionmailer (3.2.3, 3.0.9) actionpack (3.2.3, 3.0.9) activemodel (3.2.3, 3.0.9) activerecord (3.2.3, 3.0.9) activerecord-sqlserver-adapter (3.2.4, 3.0.15) activeresource (3.2.3, 3.0.9) activesupport (3.2.3, 3.0.9) arel (3.0.2, 2.0.10) builder (3.0.0, 2.1.2) bundler (1.0.15) coderay (1.0.6) daemons (1.1.8) erubis (2.7.0, 2.6.6) eventmachine (1.0.0.beta.4.1 x86-mingw32, 1.0.0.beta.4 x86-mingw32) fastercsv (1.5.5) hike (1.2.1) i18n (0.6.0, 0.5.0) journey (1.0.3) json (1.7.3) mail (2.4.4, 2.2.19) mime-types (1.18, 1.16) multi_json (1.3.5) mysql (2.8.1 x86-mingw32) mysql2 (0.3.11 x86-mingw32) net-ldap (0.3.1) open4 (1.1.0) pg (0.13.2 x86-mingw32, 0.11.0 x86-mingw32) Platform (0.4.0) polyglot (0.3.3, 0.3.1) POpen4 (0.1.4) prototype-rails (3.2.1) rack (1.4.1, 1.2.3) rack-cache (1.2) rack-mount (0.6.14) rack-openid (1.3.1) rack-ssl (1.3.2) rack-test (0.6.1, 0.5.7) rails (3.2.3, 3.0.9) railties (3.2.3, 3.0.9) rake (0.9.2.2, 0.8.7) rb-readline (0.4.0) rdoc (3.12, 3.8) ruby-odbc (0.99994) ruby-openid (2.1.8) rubyzip2 (2.0.1) sprockets (2.1.3) sqlite3 (1.3.6 x86-mingw32, 1.3.3 x86-mingw32) sqlite3-ruby (1.3.3) thin (1.3.1) thor (0.14.6) tilt (1.3.3) tiny_tds (0.5.1 x86-mingw32, 0.4.5 x86-mingw32) treetop (1.4.10, 1.4.9) tzinfo (0.3.33, 0.3.29) win32-open3 (0.3.2 x86-mingw32)

rails-sqlserver commented 12 years ago

That just shows me all the gems on your system which is of no help. Which versions are you bundled to and using in the said example you provided.

On May 24, 2012, at 8:35 AM, caterwomtious wrote:

Here's the output from "gem list":

* LOCAL GEMS *

abstract (1.0.0) actionmailer (3.2.3, 3.0.9) actionpack (3.2.3, 3.0.9) activemodel (3.2.3, 3.0.9) activerecord (3.2.3, 3.0.9) activerecord-sqlserver-adapter (3.2.4, 3.0.15) activeresource (3.2.3, 3.0.9) activesupport (3.2.3, 3.0.9) arel (3.0.2, 2.0.10) builder (3.0.0, 2.1.2) bundler (1.0.15) coderay (1.0.6) daemons (1.1.8) erubis (2.7.0, 2.6.6) eventmachine (1.0.0.beta.4.1 x86-mingw32, 1.0.0.beta.4 x86-mingw32) fastercsv (1.5.5) hike (1.2.1) i18n (0.6.0, 0.5.0) journey (1.0.3) json (1.7.3) mail (2.4.4, 2.2.19) mime-types (1.18, 1.16) multi_json (1.3.5) mysql (2.8.1 x86-mingw32) mysql2 (0.3.11 x86-mingw32) net-ldap (0.3.1) open4 (1.1.0) pg (0.13.2 x86-mingw32, 0.11.0 x86-mingw32) Platform (0.4.0) polyglot (0.3.3, 0.3.1) POpen4 (0.1.4) prototype-rails (3.2.1) rack (1.4.1, 1.2.3) rack-cache (1.2) rack-mount (0.6.14) rack-openid (1.3.1) rack-ssl (1.3.2) rack-test (0.6.1, 0.5.7) rails (3.2.3, 3.0.9) railties (3.2.3, 3.0.9) rake (0.9.2.2, 0.8.7) rb-readline (0.4.0) rdoc (3.12, 3.8) ruby-odbc (0.99994) ruby-openid (2.1.8) rubyzip2 (2.0.1) sprockets (2.1.3) sqlite3 (1.3.6 x86-mingw32, 1.3.3 x86-mingw32) sqlite3-ruby (1.3.3) thin (1.3.1) thor (0.14.6) tilt (1.3.3) tiny_tds (0.5.1 x86-mingw32, 0.4.5 x86-mingw32) treetop (1.4.10, 1.4.9) tzinfo (0.3.33, 0.3.29) win32-open3 (0.3.2 x86-mingw32)

Reply to this email directly or view it on GitHub: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/204#issuecomment-5898994

sussexrick commented 12 years ago

How do I find that out?

KDGundermann commented 12 years ago

Use the 'bundle' command

sussexrick commented 12 years ago

Using rake (0.9.2.2) Using i18n (0.6.0) Using multi_json (1.3.5) Using activesupport (3.2.3) Using builder (3.0.0) Using activemodel (3.2.3) Using erubis (2.7.0) Using journey (1.0.3) Using rack (1.4.1) Using rack-cache (1.2) Using rack-test (0.6.1) Using hike (1.2.1) Using tilt (1.3.3) Using sprockets (2.1.3) Using actionpack (3.2.3) Using mime-types (1.18) Using polyglot (0.3.3) Using treetop (1.4.10) Using mail (2.4.4) Using actionmailer (3.2.3) Using arel (3.0.2) Using tzinfo (0.3.33) Using activerecord (3.2.3) Using activerecord-sqlserver-adapter (3.2.4) Using activeresource (3.2.3) Using bundler (1.0.15) Using coderay (1.0.6) Using daemons (1.1.8) Using eventmachine (1.0.0.beta.4.1) Using fastercsv (1.5.5) Using json (1.7.3) Using mysql (2.8.1) Using net-ldap (0.3.1) Using pg (0.13.2) Using rack-ssl (1.3.2) Using rdoc (3.12) Using thor (0.14.6) Using railties (3.2.3) Using rails (3.2.3) Using prototype-rails (3.2.1) Using ruby-openid (2.1.8) Using rack-openid (1.3.1) Using ruby-odbc (0.99994) Using sqlite3 (1.3.6) Using thin (1.3.1) Using tiny_tds (0.5.1) Your bundle is complete! Use bundle show [gemname] to see where a bundled gem is installed.

KDGundermann commented 12 years ago

Sorry for my dumb questions but I don't know redmine.

sussexrick commented 12 years ago

Q1. Redmine is issue management software, like the one we're using right now. The standard fields are ones like subject, description, start date, due date and so on that come with the product and each have a field in the "issues" table.

You can also create a custom field definition using your own field names, and use those custom fields to gather more information about issues. They're stored differently, therefore the query to access them will be different: there's a separate table of custom field definitions, another with values saved in the fields, and others linking the fields to where they're used.

In the query quoted above you can see that the error occurs in a query on the custom_values table. If you sort a list of issues by one of the built-in fields, it probably never looks at that table. It you sort by a custom field, it has to look at the values in the field.

I can generate a script of the database structure if that would help.

Q2. Thin logs "Processing by IssuesController#index as HTML" to the console, so I guess it's a controller? Sorry, I don't know my way around a Ruby application to be sure of that.

Q3. https://github.com/redmine/redmine/blob/master/app/controllers/issues_controller.rb

Commenting out line 73, ":order => sort_clause," stops the error so that's the line generating the invalid SQL.

It looks like sort_clause comes from the following file, but I can't follow how it's building up that clause:

https://github.com/edavis10/redmine/blob/master/app/helpers/sort_helper.rb

Q4. Yes, it supports MySQL, PostgresSQL and SqlLite. I'm not using any of those, though I did have to install the mysql gem to get past an error message. Redmine doesn't officially support MS SQL server, but nonetheless almost all the functionality works with MS SQL server. My Ruby on Rails installation is from http://railsinstaller.org/ (version 1.3.0 linked right at the bottom of the page) so that may account for some things being installed by default.

Q5. Done. I ran "gem uninstall ruby-odbc"

rails-sqlserver commented 12 years ago
  1. Done. I ran "gem uninstall ruby-odbc"

FYI, Redmine, like any other modern Rails application uses a Gemfile to control what dependencies are pulled in. Uninstall the gem is moot. It is the contents of the Gemfile that is important. To that end, you should learn how to use these tools. I suggest examining the Gemfile for the project you are working with https://github.com/edavis10/redmine/blob/master/Gemfile and then learning a lit bit about bundler http://gembundler.com/

After that you should ask the Redmine group on some best practices on how to use SQL Server. Basically, you would edit the Gemfile and put in a 'activerecord-sqlserver-adapter' gem dep. To avoid installing all the other adapters, you would either remove them from the Gemfile or use bundler's without feature (http://gembundler.com/bundle_install.html) to exclude certain group. This is likely your issue because you likely have multiple adapters loaded and the generated SQL is getting messed up.

Honestly tho, happy to help. But this is not a topic for the adapter. Any issue you run into is going to be some basic learning like above or Redmine's group after that.

FYI, you can search the adpater issues where people have found this out before. Redmine does not do cross DB ActiveRecord well at all. There are many places in their code base which could be cleaned up to be more DB agnostic. This is on them.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/search?q=redmine

metaskills commented 12 years ago

I am closing this issue.