Why is my Rails function working in SQLite but not in MySQL?

Question

I have this function in one of my Rails models:

def delete_unactivated_users    
  expiry_time = Time.zone.now - USER_ACTIVATION_TIME
  User.where("admin == ? and activated == ? and created_at < ?", false, false, expiry_time).destroy_all
end

In development mode, with SQLite, it works perfectly well. But in production, with MySQL, I get this syntax error:

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 '== 0 and activated == 0 and created_at < '2014-05-20 10:35:55')' at line 1: SELECT `users`.* FROM `users`  WHERE (admin == 0 and activated == 0 and created_at < '2014-05-20 10:35:55'))

How can my function be fixed so that it works equally well in SQLite and MySQL?

This would be an excerpt of my schema.rb file:

create_table "users", force: true do |t|
  ...
  t.boolean  "admin",                         default: false
  t.boolean  "activated",                     default: false
  ...
end

Thanks for any help.

This is what you get when you use different databases for development and production. Expect more surprises to follow. — Sergio Tulentsev
– Sergio Tulentsev, Commented May 30, 2014 at 10:59

Max Williams · Accepted Answer · 2014-05-30 10:57:08Z

3

MySql doesn't use the == - it uses = to test equality as well as set a value.

Change it to = as that is the SQL standard.

Ie

  User.where("admin = ? and activated = ? and created_at < ?", false, false, expiry_time).destroy_all

answered May 30, 2014 at 10:57

Max Williams

33k34 gold badges137 silver badges205 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is my Rails function working in SQLite but not in MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related