DB issue in rails? Rails query and SQL query return different results (default_scope's fault?)

Question

We have over 29K users in our database. "User" is one of our tables and has a unique field "email" with an index defined in one of our migrations:

#3434324_devise_create_user.rb

class DeviseCreateUsers < ActiveRecord::Migration
  def change
    create_table(:users) do |t|
      t.string :email, :null => false, :default => ""
    end
    add_index :users, :email, :unique => true
  end
end

We don't have any issues creating users... for the most part. However, with some emails (potentially those that have been previously deleted from the DB at one point or where created long time ago), we are encountering a rare issue:

We can't create them because the "unique" validation fails, however, there is not a single user with that email.

This is an example of what is happening (which does not make any sense if you ask me).

When we try to create a user with the email "[email protected]":

$ p = Devise.friendly_token[0,20]
$ User.create!({email: "[email protected]", password: p})

We get the following result:

(11.6ms)  BEGIN
  User Exists (11.2ms)  SELECT  1 AS one FROM "users"  WHERE "users"."email" = '[email protected]' LIMIT 1
(10.3ms)  ROLLBACK
  ActiveRecord::RecordInvalid: Validation failed: Email has already been taken
  from /Users/example/.rvm/gems/ruby-2.0.0-p451@example/gems/activerecord-4.1.6/lib/active_record/validations.rb:57:in `save!'

At the same time, if we look for that email in the database:

$ User.find_by_email("[email protected]")

It does not exist!

User Load (12.7ms)  SELECT  "users".* FROM "users"  WHERE "users"."disabled" = 'f' AND "users"."email" = '[email protected]' LIMIT 1
=> nil

Does this make sense for any of you?

Thank you for your comment @Nithin: this is what we get with a sql SELECT: result = @connection.exec_query("SELECT 1 AS one FROM users WHERE users.email = '[email protected]’ LIMIT 1") SQL (12.5ms) SELECT 1 AS one FROM users WHERE users.email = ‘example@ example.com’ LIMIT 1 => #<ActiveRecord::Result:0x00000107dc6330 @columns=["one"], @rows=[["1"]], @hash_rows=nil, @column_types={"one"=>#<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Integer:0x00000107a23340>}> It says that there is a row with that email but the same does not happen using ActiveRecord. — Maveric
– Maveric, Commented Jun 13, 2015 at 4:10
Thank you for your comment @BhanuPrakash: would you recommend https://github.com/ankane/searchkick to reindex the email field? — Maveric
– Maveric, Commented Jun 13, 2015 at 4:16

Frederick Cheung · Accepted Answer · 2015-06-13 06:36:39Z

1

There is a big difference between the query you get when you run User.find_by_email and the one that is run by the validation: the former has an extra condition on the disabled column. It would seem that when you've been deleting using you've just be flagging them as deleted rather than actually removing.

Given what you've posted I can't tell where that is coming from (perhaps a default scope or an extension to rails) but it would certainly account for the difference in results. You could confirm this by searching for the user in the psql shell.

You could change the validation to ignore these disabled rows:

validates_uniqueness_of :email, conditions: -> { where(disabled: false) }

However you still wouldn't be able to create this user since the unique index on email would prevent this. If you want to able to have multiple users with the same email (but only one that is not disabled) you would have to make this a multi column index on email and some other attribute that would be different for all the "old" users but would be the same for active users

answered Jun 13, 2015 at 6:36

Frederick Cheung

84.3k9 gold badges157 silver badges175 bronze badges

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

1 Comment

Maveric Over a year ago

You are absolutely right Frederick. There was a default_scope defined long time ago in the user model (default_scope { where(disabled: false) }) and we had forgotten about it. Thank you so much for your help Frederick. You have saved us many hours of being in front of the screen. Very much appreciated (note that I have changed the title of the question to make it more accessible to other readers in the future).

Collectives™ on Stack Overflow

DB issue in rails? Rails query and SQL query return different results (default_scope's fault?)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related