CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecorddevise
Maveric
Maveric

Reputation: 159

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

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?

Upvotes: 0

Views: 133

Answers (1)

Frederick Cheung
Frederick Cheung

Reputation: 84124

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

Upvotes: 1

Related Questions