How can i make ruby on rails handle duplicate record errors from MySQL

Question

I have a unique index on multiple fields on my database. So if you try to call save on a duplicate record, it raises ActiveRecord::StatementInvalid and shows the mysql error. Is there a way to handle this within rails either by creating the unique constraint, or otherwise have it return a relevant error message when this happens?

heres the trace:

ActiveRecord::StatementInvalid: Mysql::Error: Duplicate entry '2010-12-09-2-0-1-1' for key 2: INSERT INTO `entries` (`rejected_at`, `created_at`, `comments`, `overtime`, `submitted_at`, `updated_at`, `time`, `approved`, `day`, `user_id`, `approved_at`, `job_id`, `submitted`, `rejected`) VALUES(NULL, '2010-12-09 21:50:46', NULL, 0, NULL, '2010-12-09 21:50:46', 2.0, NULL, '2010-12-09', 1, NULL, 1, NULL, NULL)
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:319:in `execute'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:259:in `insert_sql'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:329:in `insert_sql'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert_without_query_dirty'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:18:in `insert'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/base.rb:2901:in `create_without_timestamps'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/timestamp.rb:53:in `create_without_callbacks'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/callbacks.rb:266:in `create'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/base.rb:2867:in `create_or_update_without_callbacks'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/callbacks.rb:250:in `create_or_update'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/base.rb:2538:in `save_without_validation'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/validations.rb:1078:in `save_without_dirty'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/dirty.rb:79:in `save_without_transactions'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:229:in `send'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:229:in `with_transaction_returning_status'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `transaction'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:182:in `transaction'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:228:in `with_transaction_returning_status'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:196:in `save'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:208:in `rollback_active_record_state!'
 from /home/cmatthews/src/cannon/vendor/rails/activerecord/lib/active_record/transactions.rb:196:in `save'

Michael Voigt · Accepted Answer · 2011-04-20 08:22:01Z

18

begin
  Event.create!(:name => 'Ironman Lanzarote 2011')
rescue ActiveRecord::RecordNotUnique => e
  # handle duplicate entry 
end

This works for me under Rails 3.

answered Apr 20, 2011 at 8:22

Michael Voigt

1871 silver badge4 bronze badges

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

3 Comments

maletor Over a year ago

This violates the principle of least astonishment and does not follow conventions in Rails.

scones Over a year ago

When you use a master/slave-setup, you will run into drifting problems (and the resulting race conditions), which can easily be resolved using this approach, instead of using rails' unique validation, which might check it on the slave.

Papipo Over a year ago

This is fine, but I would put this behaviour on a model method with a descriptive name.

Pan Thomakos · Accepted Answer · 2010-12-09 22:37:45Z

4

You can add a uniqueness constraint to your model so that the record returns with errors and is invalid.

For example (Rails 2):

class User < ActiveRecord::Base
  validates_uniqueness_of :email, :scope => [:name, :age]
end

or (Rails 3)

class User < ActiveRecord::Base
  validates :email, :uniqueness => {:scope => [:name, :age]}
end

This should result in the following:

user1 = User.create :email => "[email protected]", :name => "one", :age => 20
user2 = User.create :email => "[email protected]", :name => "one", :age => 20
user2.valid? # false

edited Dec 9, 2010 at 22:37

answered Dec 9, 2010 at 22:31

Pan Thomakos

34.4k9 gold badges90 silver badges86 bronze badges

4 Comments

loosecannon Over a year ago

i know i can do that for one field but can i do it for multiple ones as in can i have it check to see if they have the same: email, name,favorite color, and job. There can be 5 people named john, but only one can have [email protected], orange, and be a programmer. Does that make sense?

Pan Thomakos Over a year ago

Hey, I updated the post with the answer for multiple attributes.

Omar Qureshi Over a year ago

Whilst that covers most of the use cases, there is and always has been a race condition in validates_uniqueness_of - it doesnt stop duplicate entries completely due to the way it works.

Pan Thomakos Over a year ago

That's correct Omar. You should still have a unique index in MySQL on (name, age, email) and it's best to surround your updates with begin..rescue..end statements to catch these cases if you suspect this might be an issue for your application.

Riedsio · Accepted Answer · 2010-12-09 22:15:38Z

1

Can you change the query to

INSERT IGNORE INTO `entries` ...

Alternatively, you could consider INSERT... ON DUPLICATE KEY UPDATE....

The INSERT Documentation is here.

answered Dec 9, 2010 at 22:15

Riedsio

9,9661 gold badge26 silver badges33 bronze badges

1 Comment

loosecannon Over a year ago

i think that would somewhat solve the problem, but the SQL query is auto generated. I could alter it, except i want it to raise an error, just one that I can catch and have a message, this just makes the server say 500 internal server error.

Collectives™ on Stack Overflow

How can i make ruby on rails handle duplicate record errors from MySQL

3 Answers 3

3 Comments

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

1 Comment

Related