Defect #3757
closedIssuses assigned to ID problem
0%
Description
Hello,
I need help with resolving my db problem.
When I sending request https://.../issues?assigned_to_id=me
I get an error in production.log:
Parameters: {"set_filter"=>"2", "action"=>"index", "controller"=>"issues", "assigned_to_id"=>"me"}
ActiveRecord::StatementInvalid (Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: SELECT count(DISTINCT `issues`.id) AS count_all FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id WHERE ((issues.assigned_to_id IN ('4')) AND (issue_statuses.is_closed=0) AND (projects.status=1 AND EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id) OR (projects.status=1 AND EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id) AND issues.author_id = 4)) AND ) ):
I know that it's a MySQL query syntax problem, but i don't know how to fix it.
My system settings:
MySQL 5.0.45
ruby 1.8.6
Rails 2.2.2
Thanks in advance.
Updated by François K. about 15 years ago
Hi,
The issues are at the end of the query:- AND without a right operand,
- One close parenthesis in excess - it seams.
I can't reproduce the issue on my redmine install (but I use the 0.8.3 version).
Updated by Jacek Kazimierz Strzałkowski about 15 years ago
This is an old problem on my instalation since version 0.8.1. It could appear after migration from Trac.
I found next problem with MySQL query syntax.
When doing sort by 'milestone' column - https://.../projects/monitoring/issues?sort_key=deliverable_subject&sort_order=asc
i get
Parameters: {"project_id"=>"monitoring", "sort_order"=>"asc", "action"=>"index", "controller"=>"issues", "query_id"=>"18", "sort_key"=>"deliverable_subject"}
ActiveRecord::StatementInvalid (Mysql::Error: Unknown column 'deliverables.subject' in 'order clause': SELECT `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`.`deliverable_id` AS t0_r18, `issues`.`private` AS t0_r19, `users`.`id` AS t1_r0, `users`.`login` AS t1_r1, `users`.`hashed_password` AS t1_r2, `users`.`firstname` AS t1_r3, `users`.`lastname` AS t1_r4, `users`.`mail` AS t1_r5, `users`.`mail_notification` AS t1_r6, `users`.`admin` AS t1_r7, `users`.`status` AS t1_r8, `users`.`last_login_on` AS t1_r9, `users`.`language` AS t1_r10, `users`.`auth_source_id` AS t1_r11, `users`.`created_on` AS t1_r12, `users`.`updated_on` AS t1_r13, `users`.`type` AS t1_r14, `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, `trackers`.`id` AS t3_r0, `trackers`.`name` AS t3_r1, `trackers`.`is_in_chlog` AS t3_r2, `trackers`.`position` AS t3_r3, `trackers`.`is_in_roadmap` AS t3_r4, `projects`.`id` AS t4_r0, `projects`.`name` AS t4_r1, `projects`.`description` AS t4_r2, `projects`.`homepage` AS t4_r3, `projects`.`is_public` AS t4_r4, `projects`.`parent_id` AS t4_r5, `projects`.`projects_count` AS t4_r6, `projects`.`created_on` AS t4_r7, `projects`.`updated_on` AS t4_r8, `projects`.`identifier` AS t4_r9, `projects`.`status` AS t4_r10, `projects`.`customer_id` AS t4_r11, `enumerations`.`id` AS t5_r0, `enumerations`.`opt` AS t5_r1, `enumerations`.`name` AS t5_r2, `enumerations`.`position` AS t5_r3, `enumerations`.`is_default` AS t5_r4, `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 FROM `issues` LEFT OUTER JOIN `users` ON `users`.id = `issues`.assigned_to_id LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id LEFT OUTER JOIN `trackers` ON `trackers`.id = `issues`.tracker_id LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id LEFT OUTER JOIN `enumerations` ON `enumerations`.id = `issues`.priority_id LEFT OUTER JOIN `issue_categories` ON `issue_categories`.id = `issues`.category_id LEFT OUTER JOIN `versions` ON `versions`.id = `issues`.fixed_version_id WHERE ((issues.assigned_to_id IN ('4')) AND (issue_statuses.is_closed=0) AND (projects.status=1 AND EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id) OR (projects.status=1 AND EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id) AND issues.author_id = 4)) AND projects.id IN (4,37,34,32)) ORDER BY deliverables.subject ASC LIMIT 0, 25):
Updated by Jean-Philippe Lang almost 15 years ago
The following URL works with latest trunk and 0.8 stable:
http://localhost:3000/issues?set_filter=1&assigned_to_id=me
Can you give me a way to reproduce ?
Updated by Jacek Kazimierz Strzałkowski almost 15 years ago
My redmine instance said:
Session ID: 252b4e3f4608eef11e71eb728327bdc2
Parameters: {"set_filter"=>"1", "action"=>"index", "controller"=>"issues", "assigned_to_id"=>"me"}
ActiveRecord::StatementInvalid (Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: SELECT count(DISTINCT `issues`.id) AS count_all FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id WHERE ((issues.assigned_to_id IN ('4')) AND (issue_statuses.is_closed=0) AND (projects.status=1 AND EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id) OR (projects.status=1 AND EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id) AND issues.author_id = 4)) AND ) ):
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract_adapter.rb:147:in `log'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/mysql_adapter.rb:302:in `execute'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/mysql_adapter.rb:537:in `select'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/query_cache.rb:59:in `select_all'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/query_cache.rb:80:in `cache_sql'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/query_cache.rb:59:in `select_all'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in `select_one'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in `select_value'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/calculations.rb:225:in `execute_simple_calculation'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/calculations.rb:126:in `calculate'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/calculations.rb:122:in `catch'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/calculations.rb:122:in `calculate'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/calculations.rb:48:in `count'
/app/controllers/issues_controller.rb:59:in `index'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/base.rb:1166:in `send'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/base.rb:1166:in `perform_action_without_filters'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/filters.rb:579:in `call_filters'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/filters.rb:572:in `perform_action_without_benchmark'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue'
/usr/local/lib/ruby/1.8/benchmark.rb:293:in `measure'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/rescue.rb:201:in `perform_action_without_caching'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/caching/sql_cache.rb:13:in `perform_action'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/connection_adapters/abstract/query_cache.rb:33:in `cache'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/query_cache.rb:8:in `cache'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/caching/sql_cache.rb:12:in `perform_action'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/base.rb:529:in `send'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/base.rb:529:in `process_without_filters'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/filters.rb:568:in `process_without_session_management_support'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/session_management.rb:130:in `process'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/base.rb:389:in `process'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/dispatcher.rb:149:in `handle_request'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/dispatcher.rb:107:in `dispatch'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/dispatcher.rb:104:in `synchronize'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/dispatcher.rb:104:in `dispatch'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/dispatcher.rb:120:in `dispatch_cgi'
/usr/local/lib/ruby/gems/1.8/gems/actionpack-2.1.2/lib/action_controller/dispatcher.rb:35:in `dispatch'
/usr/local/lib/ruby/gems/1.8/gems/rails-2.1.2/lib/webrick_server.rb:112:in `handle_dispatch'
/usr/local/lib/ruby/gems/1.8/gems/rails-2.1.2/lib/webrick_server.rb:78:in `service'
/usr/local/lib/ruby/1.8/webrick/httpserver.rb:104:in `service'
/usr/local/lib/ruby/1.8/webrick/httpserver.rb:65:in `run'
/usr/local/lib/ruby/1.8/webrick/server.rb:173:in `start_thread'
/usr/local/lib/ruby/1.8/webrick/server.rb:162:in `start'
/usr/local/lib/ruby/1.8/webrick/server.rb:162:in `start_thread'
/usr/local/lib/ruby/1.8/webrick/server.rb:95:in `start'
/usr/local/lib/ruby/1.8/webrick/server.rb:92:in `each'
/usr/local/lib/ruby/1.8/webrick/server.rb:92:in `start'
/usr/local/lib/ruby/1.8/webrick/server.rb:23:in `start'
/usr/local/lib/ruby/1.8/webrick/server.rb:82:in `start'
/usr/local/lib/ruby/gems/1.8/gems/rails-2.1.2/lib/webrick_server.rb:62:in `dispatch'
/usr/local/lib/ruby/gems/1.8/gems/rails-2.1.2/lib/commands/servers/webrick.rb:66
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
/usr/local/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:510:in `require'
/usr/local/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:355:in `new_constants_in'
/usr/local/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:510:in `require'
/usr/local/lib/ruby/gems/1.8/gems/rails-2.1.2/lib/commands/server.rb:39
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
script/server:3
Rendering /usr/home/backup/redmine-0.8/public/500.html (500 Internal Server Error)
I don't realy know how to reproduce this state.
Updated by Jean-Philippe Lang over 14 years ago
- Status changed from New to Closed
- Resolution set to Cant reproduce