An extra special case

— June 15, 2008 at 10:31 PDT


A couple of months ago I ran into a weird issue in my current Rails project that made no sense at all. All we did was add a lock_version field to a model to enable optimistic locking and suddenly things started breaking in a big way. After a bit of digging we found it was because ActiveRecord wasn't properly quoting a table name when updating a record with optimistic locking. I submitted a patch for that issue (so it's fixed in Rails 2.1), but lately I've seen a few similar bugs having to do with table name quoting in various circumstances. The amusing thing to me is that all of these bugs have one thing in common: they were uncovered by creating a model named Reference.

At first I thought this was a pretty big coincidence, but after just a moment's thought it seemed pretty obvious. ActiveRecord pluralizes model names to form conventional table names, and references is a reserved keyword in MYSQL. I guess Reference is a word that makes a good model name, especially if you're building a big data graph and can't think of a more specific relationship name, and it's about the only noun that pluralizes into a reserved keyword that anyone would ever use. In our case, we could have done a rename refactoring to change the model class name to CharacterReference. Instead we used an override and changed the table name to t_references, since that seemed like the least effort for a temporary workaround until the fix got released with Rails 2.1.

All these various issues with table name quoting are indeed bugs in ActiveRecord and should be reported and fixed. (There's also a major reworking of the internals of ActiveRecord in progress that should deal with virtually all of these issues in one fell swoop.) But in the mean time, you might want to avoid using model names that generate SQL reserved words, or just override the table name to something else.

9 commentsactiverecord, rails

Comments
  1. Alex MacCaw2008-06-15 11:18:00

    I had the same problem with a column called 'key' - also a SQL keyword. I submitted a patch and got it fixed (I think only in edge though).

  2. Seth Ladd2008-06-15 16:18:40

    Thanks for the post. That was quite the tease with "there's a major reworking of ActiveRecord". Can you point to more info on this? Thanks!

  3. Jamie Lawrence2008-06-16 12:57:33

    Interesting. Almost 2 years ago I tried out Rails for a simple bibliography app (i.e., Papers, Authors, References). I kept getting a problem (and not knowing Rails I didn't really investigate) but it could easily have been this one. I gave up on that project but I'm glad I didn't let that experience put me off Rails altogether -- I took it up again 6 months ago and I'm enjoying it for other hobby-projects (which don't involve "References"). :-)

  4. zoomn2008-06-18 15:04:03

    Interesting post! But what I dont get: How did you end up with a such a table in a working rails app? I am guessing that MySQL would complain if you wanted to create a table named "references". Assuming that, you would have to specify another table name in rails upfront to make things work, right?!

    I am quite new to the whole Rails thing, so sorry if there's an obvious answer I dont see right away...:)

  5. Josh Susser2008-06-19 07:29:34

    @zoomn: You can create or use a table named "references" if you quote it in SQL. The issue in ActiveRecord is that the table name doesn't get quoted consistently, so some uses break.

  6. Josh Susser2008-06-19 07:40:57

    @Seth: Yes, that is a bit of a tease. You'll just have to stay tuned...

  7. Montana Harkin2008-06-25 09:29:51

    I've run into the same type of issues with CakePHP. Usually they are corrected quickly.

    I think most frameworks run into this issue. Seems like something that could be solved with a good set of unit tests and a dictionary. ;)

  8. Jason Kusar2008-06-27 05:20:33

    Sadly, this seems to break the oracle connection adapter if you're referencing other schemas. For example, I have 'settablename "ENT.persons"' in my model because the person table is owned by ENT. With rails 2.1, I get "table or view does not exist" because the quoting causes oracle to look for a table named ENT.persons in the current user's schema rather than looking for persons in ENT's schema. Do you know of any way to turn off this quoting?

    Thanks!

  9. Augustus2008-08-28 08:44:59

    Nice article, thank you. You ran into a really weird issue. And it is great that you discuss it because it can be useful for many people. The problem is that most frameworks run into this issue.

Sorry, comments for this article are closed.