Skip to content
This repository

StatementInvalid error Rails 3.2.1, MySQL2 and MySQL ANSI_QUOTES #4977

Closed
cbailster opened this Issue February 09, 2012 · 1 comment

2 participants

Chris Bailey Jon Leighton
Chris Bailey

I've just tried upgrading from 3.1.1 to 3.2.1 and have discovered that any db query that requires use of an id column are now failing when I have mysql configured to use ANSI_QUOTES (i.e. "id" is the name of a column 'id' is a 2 character string - normally under mysql both are the same)

e.g. Using console

irb(main):001:0> d = Doctor.find(27531)

Generates the following SQL:

Doctor Load (0.7ms)  SELECT `entities`.* FROM `entities` WHERE `entities`.`type` IN ('Doctor') AND `entities`.`"id"` = 27531 LIMIT 1

Which causes the following error:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'entities."id"' in 'where clause': SELECT  `entities`.* FROM `entities`  WHERE `entities`.`type` IN ('Doctor') AND `entities`.`"id"` = 27531 LIMIT 1

Stack trace is as follows:

from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `query'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `block in execute'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
from /usr/lib/ruby/gems/1.9.1/gems/activesupport-3.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `execute'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:214:in `execute'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:218:in `exec_query'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:228:in `select'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:16:in `select_all'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:38:in `block in find_by_sql'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/explain.rb:40:in `logging_query_plan'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:37:in `find_by_sql'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation.rb:170:in `exec_queries'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation.rb:159:in `block in to_a'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/explain.rb:40:in `logging_query_plan'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation.rb:158:in `to_a'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:377:in `find_first'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:122:in `first'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:335:in `find_one'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:311:in `find_with_ids'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:107:in `find'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:5:in `find'
from (irb):1
from /usr/lib/ruby/gems/1.9.1/gems/railties-3.2.1/lib/rails/commands/console.rb:47:in `start'
from /usr/lib/ruby/gems/1.9.1/gems/railties-3.2.1/lib/rails/commands/console.rb:8:in `start'
from /usr/lib/ruby/gems/1.9.1/gems/railties-3.2.1/lib/rails/commands.rb:41:in `<top (required)>'
from script/rails:6:in `require'
from script/rails:6:in `<main>

Turn off ANSI_QUOTES and the error goes away. Queries that don't use an ID column are unaffected, as are tables where the primary key is overridden using set_primary_key

Jon Leighton
Owner

Dupe of #4754. A fix will be in the next release.

Jon Leighton jonleighton closed this February 09, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.