Scrum Statistics: mysql2 SQL syntax error

[JimmyCHNE]

platform: # supported: 2.2.4, 2.3.2 backlogs: # supported: 1.0.5 ruby: # supported: 1.9.3, 2.0.0

My backlogs and platform information are

Redmine version 2.2.2.stable Ruby version 1.9.3 (x86_64-linux) Rails version 3.2.11 Backlogs v0.9.37

When some of users click scrum statistics, they will get the following error message in log:

ActiveRecord::StatementInvalid (Mysql2::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 ') and tracker_id in (11) and release_id is NULL and fixed_vers' at line 2: 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.`categ........ project_id in () and tracker_id in (11) and release_id is NULL and fixed_version_id is NULL and is_closed = 0) ORDER BY issues.position LIMIT 10):

However, there is no problems for administrator. What can be wrong? Who can help me out? Thanks in advance.

[bohansen]

I'm seeing something similar on 2.3.0 + latest master.

[mirland-zz]

platform: # redmine-2.3.0 stable ruby: # 2.0.0 rails # 3.2.13 backlogs # 1.0.1

I have the same problem with my installation. Any suggestion?

[bohansen]

Latest master contains a fix for only showing visible projects. The sql error above might be related to a permission problem. Greatly appreciated if someone could test if it solves the problem.

[mirland-zz]

thanks a lot i'm going to test it

[mirland-zz]

Hi! Bohansen i change the source code but it doesn't work

[bohansen]

Unable to reproduce here - could you paste the output from the log file?

[mirland-zz]

Sure Bohansen!

This is the sentence:

ActiveRecord::StatementInvalid (Mysql2::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 ') and tracker_id in (3) and release_id is NULL and fixed_versi' at line 2: 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.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, issues.closed_on AS t0_r23, issues.position AS t0_r24, issues.remaining_hours AS t0_r25, issues.release_id AS t0_r26, issues.story_points AS t0_r27, issues.release_relationship AS t0_r28, 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, projects.inherit_members AS t1_r12 FROM issues INNER JOIN issue_statuses ON issue_statuses.id = issues.status_id INNER JOIN projects ON projects.id = issues.project_id WHERE (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.is_public = 1 AND ((issues.is_private = 0 OR issues.author_id = 170 OR issues.assigned_to_id IN (170)))) OR (projects.id IN (321,232,304,219,121,281,265,241,242,271,159,147,59,264,73,49,250,209,237,258,290,108,58,313,4,76) AND ((issues.is_private = 0 OR issues.author_id = 170 OR issues.assigned_to_id IN (170)))) OR (projects.id IN (259,243,370,255,371) AND ((issues.is_private = 0 OR issues.author_id = 170 OR issues.assigned_to_id IN (170))))))) AND ( project_id in () and tracker_id in (3) and release_id is NULL and fixed_version_id is NULL and is_closed = 0) ORDER BY issues.position LIMIT 10): vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in query' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:inblock in execute' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:280:in block in log' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:ininstrument' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:275:in log' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:inexecute' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:211:in execute' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:215:inexec_query' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:224:in select' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:18:inselect_all' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:61:in block in select_all' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:75:incache_sql' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:61:in select_all' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/relation/finder_methods.rb:212:infind_with_associations' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/relation.rb:174:in block in exec_queries' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/identity_map.rb:72:inwithout' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/relation.rb:173:in exec_queries' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/relation.rb:160:inblock in to_a' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/explain.rb:41:in logging_query_plan' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/relation.rb:159:into_a' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/relation/delegation.rb:6:in length' plugins/redmine_backlogs/lib/backlogs_project_patch.rb:93:intest_product_backlog_filled' plugins/redmine_backlogs/lib/backlogs_project_patch.rb:43:in block in initialize' plugins/redmine_backlogs/lib/backlogs_project_patch.rb:42:ineach' plugins/redmine_backlogs/lib/backlogs_project_patch.rb:42:in initialize' plugins/redmine_backlogs/lib/backlogs_project_patch.rb:188:innew' plugins/redmine_backlogs/lib/backlogs_project_patch.rb:188:in scrum_statistics' plugins/redmine_backlogs/app/controllers/rb_all_projects_controller.rb:12:inblock in statistics' plugins/redmine_backlogs/app/controllers/rb_all_projects_controller.rb:12:in sort!' plugins/redmine_backlogs/app/controllers/rb_all_projects_controller.rb:12:instatistics' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/implicit_render.rb:4:in send_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/abstract_controller/base.rb:167:inprocess_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/rendering.rb:10:in process_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/abstract_controller/callbacks.rb:18:inblock in process_action' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:469:in _run__742952547885274684__process_action__769974810546140622__callbacks' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:405:inrun_callback' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:385:in _run_process_action_callbacks' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:81:inrun_callbacks' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/abstract_controller/callbacks.rb:17:in process_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/rescue.rb:29:inprocess_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/instrumentation.rb:30:in block in process_action' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/notifications.rb:123:inblock in instrument' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:in instrument' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/notifications.rb:123:ininstrument' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/instrumentation.rb:29:in process_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/params_wrapper.rb:207:inprocess_action' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/railties/controller_runtime.rb:18:in process_action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/abstract_controller/base.rb:121:inprocess' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/abstract_controller/rendering.rb:45:in process' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal.rb:203:indispatch' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal/rack_delegation.rb:14:in dispatch' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_controller/metal.rb:246:inblock in action' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:73:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:73:indispatch' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:36:in call' vendor/bundle/ruby/2.0.0/gems/journey-1.0.4/lib/journey/router.rb:68:inblock in call' vendor/bundle/ruby/2.0.0/gems/journey-1.0.4/lib/journey/router.rb:56:in each' vendor/bundle/ruby/2.0.0/gems/journey-1.0.4/lib/journey/router.rb:56:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:612:in call' vendor/bundle/ruby/2.0.0/gems/rack-openid-1.3.1/lib/rack/openid.rb:98:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/best_standards_support.rb:17:in call' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/etag.rb:23:incall' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/conditionalget.rb:25:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/head.rb:14:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/params_parser.rb:21:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/flash.rb:242:incall' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/session/abstract/id.rb:210:in context' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/session/abstract/id.rb:205:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/cookies.rb:341:in call' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/query_cache.rb:64:incall' vendor/bundle/ruby/2.0.0/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/connection_pool.rb:479:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/callbacks.rb:28:inblock in call' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:405:in _run__372603126347759316__call__4072329546444821552__callbacks' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:405:inrun_callback' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:385:in _run_call_callbacks' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:81:inrun_callbacks' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/callbacks.rb:27:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/reloader.rb:65:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/remote_ip.rb:31:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/debug_exceptions.rb:16:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/show_exceptions.rb:56:in call' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/rack/logger.rb:32:incall_app' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/rack/logger.rb:16:in block in call' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/tagged_logging.rb:22:intagged' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/rack/logger.rb:16:in call' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/request_id.rb:22:incall' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/methodoverride.rb:21:in call' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/runtime.rb:17:incall' vendor/bundle/ruby/2.0.0/gems/activesupport-3.2.13/lib/active_support/cache/strategy/local_cache.rb:72:in call' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/lock.rb:15:incall' vendor/bundle/ruby/2.0.0/gems/actionpack-3.2.13/lib/action_dispatch/middleware/static.rb:63:in call' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/engine.rb:479:incall' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/application.rb:223:in call' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/content_length.rb:14:incall' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/rack/log_tailer.rb:17:in call' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/connection.rb:81:inblock in pre_process' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/connection.rb:79:in catch' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/connection.rb:79:inpre_process' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/connection.rb:54:in process' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/connection.rb:39:inreceive_data' vendor/bundle/ruby/2.0.0/gems/eventmachine-1.0.3/lib/eventmachine.rb:187:in run_machine' vendor/bundle/ruby/2.0.0/gems/eventmachine-1.0.3/lib/eventmachine.rb:187:inrun' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/backends/base.rb:63:in start' vendor/bundle/ruby/2.0.0/gems/thin-1.5.1/lib/thin/server.rb:159:instart' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/handler/thin.rb:13:in run' vendor/bundle/ruby/2.0.0/gems/rack-1.4.5/lib/rack/server.rb:268:instart' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/commands/server.rb:70:in start' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/commands.rb:55:inblock in <top (required)>' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/commands.rb:50:in tap' vendor/bundle/ruby/2.0.0/gems/railties-3.2.13/lib/rails/commands.rb:50:in<top (required)>' script/rails:6:in require' script/rails:6:in

'

Thanks a lot for your help

[mirland-zz]

sorry! I forget it----- mysql version: mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1