Ruby on Rails
HowtoHandleStatementInvalid (Version #9)

This howto explains how to deal with exceptions that result from violating a database constraint, such as the uniqueness of a key.

If you are encountering an error like Invalid Argument: SELECT COUNT(*) FROM table (Win MySQL 4.1) see MySQL Database access problem.

Suppose you have a database table containing accounts with an unique key for the username, because you want to make absolutely sure all usernames are unique:

<pre> CREATE TABLE `accounts` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(64) NOT NULL default '', `password` varchar(64) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), ); </pre>

Although it is a lot nicer to use validation (see HowtoValidate) to check for duplicate usernames before trying to save a new account, you could run into situations where the database responds with a ‘duplicate key entry’ error, triggering an ActiveRecord::StatementInvalid exception in Rails.

You can handle this by overwriting the save method of \ActiveRecord::Base, calling super in a begin … rescue … end block:

<pre> class Account < <a href="http://wiki.rubyonrails.org/rails/pages/ActiveRecord" class="existingWikiWord">ActiveRecord</a>::Base


def save
begin
super
rescue ActiveRecord::StatementInvalid
errors.add(“username”, “Username has been taken, please choose another.”)
return false
end
end
…

Be warned, this could be considered to be somewhat of a hack.

—-
That’s fine (in a hackish way as you point out) if the error is indeed due to a unique constraint problem. What if the cause of the StatementInvalid is something else – perhaps a foreign key constraint problem?

How could you make this method clever to create an error relevant to the actual error thrown by the database? Or should you never get to this point – should Rails validations have kicked in long before we get here?

—-
In a multi-user transactional system, it is possible for validation to pass before posting a transaction, but to fail during posting due to another transaction. So this type of catch is something every Rails app should handle on more or less every every db update.

—-
All the more reason to make this approach cleverer then. There’s more reasons for a potential ActiveRecord::StatementInvalid than just unique key issues. Anyone got some suggestions on improving it?

—-
Perhaps rerun the validations. This will update the error list properly. If the validations pass (they shouldn’t, surely), add an error to inform the user that something is broken, and use actionmailer send the stack trace to the site administrator.

—-
And it gets even more interesting when you have unique constraints across multiple columns. Then rails’ canned validations won’t work, and you’ll have to hand-craft validations that effectively duplicate the work the unique constraints were designed to do. It seems to me that rails needs to throw more fine-grained exceptions that better represent the kind of failure that occurred. Subclasses of StatementInvalid?, to maintain backward compatibility, of course.
—-
Finer grained exceptions, perhaps ones that integrate with the connection adapters to indicate what constraint was violated, is a good idea. I ran across this when attempting to detect a unique constraint when using a many to many mapping table with has_and_belongs_to_many that had a unique constraint that triggered when attempting to add to the collection with <<=.Regular validations won’t handle this case, I don’t think.
—-
Better try some like this:


class Account < ActiveRecord::Base                                                                                                                                                             

  def save
    begin
      super
    rescue ActiveRecord::StatementInvalid => error
      errors.add("account_error", "#{error}")
      return false
    end
  end

  ...

—-
The code above will return the bare database error message to fe. a Controller to be displayed in a view. But you don’t want to confront your user with these rude messages. This would be better:


class Account < ActiveRecord::Base
def save
begin
super
rescue ActiveRecord::StatementInvalid => error
@@my_log.debug(“Database destroy error: #{error}”) # log error
raise MyException.new(“Some clever and explaning user message here.”)
end
end
…

Now we still have the problem of finding out exactly what the database error was. This we must known in order to give our user an intelligent and helpful message. It would be nice to extract the database error number. Fe. a MySql error number 23000 indicates a delete with a foreign key constraint. Once you know that , you can inform your user to first delete related objects before deleting the current object.

Well, this isn’t a perfect solution, but here is something to help out with this problem.



def check_db_error(error, errorToCheckFor)
     return error.to_s.include?(errorToCheckFor)
end

# Would be used as ...

begin
     # Statement goes here...
rescue ActiveRecord::StatementInvalid => error
     if(check_db_error(error, "#42000"))
          render :text => "You're not allowed to do that!"
     end
end

Like I said, this isn’t much but its a good starting point. (#42000 is MySQL error for permission denied..)