Mysql2::Error: Unknown column '6' in 'where clause': SELECT

Question

I am upgrading my Rails app from Rails 2.3 to 3.2. and I am getting this weird error

Mysql2::Error: Unknown column '6' in 'where clause':

above error is due to `(backtick) which is applied to data in the where clause along with the column names see the following query.

Mysql2::Error: Unknown column '6' in 'where clause': SELECT `users`.`username`,`users`.`password` FROM `users` WHERE `id` IN (`6`)

see the 6 even though it is data activerecord is applying backtick to it.because of that mysql is raising exception.

NOTE:This is usually seen while calling related object(one to one,many to many)

eg:

 u = User.where(:active =>true).each{|u|
   //some code
    u.user_role #error will raise in this line 
    }

if I reinitiate the object it will work fine.

  u = User.where(:active =>true).each{|u|
   //some code
    u.user_role #error will raise in this line
    u1 = User.find(u.id)
    u1.user_role #works fine. 

    }

This is happening throughout the application and with other models as well,above code snippet is just one instance.

Environment details:

OS: Ubuntu 13.10,Ruby 1.9.3-p545,Rails 3.2.17 mysql 5.5

Anyone knows what going on here ? is it because of gem incompatibility ? Please let me know if you need some more details.

Answer 1

I am not sure why this may be coming, but I think you can avoid this and an extra query to sql while accessing user_role by using include in your query. This will eager load user_role in the first query itself and will not make extra query in each loop.

Code will be something like following:

  u = User.where(:active =>true).includes(:user_roles).each{|u|
    //some code
     u.user_role #error will raise in this line 
     }

More details on include is here: http://apidock.com/rails/ActiveRecord/QueryMethods/includes

Answer 2

This issue is due to gem incompatibility.I was using 'slim_scrooge' gem to optimize queries in Rails 2.X doesn't gem well with Rails 3 or higher.