Silly GORM tricks, part III: SQL keywords as attributes

I was writing a very simple Grails application and ran into a problem when I accidentally used a SQL keyword as a property name. This post documents what happened, and how I (pretty easily) fixed it.

To illustrate the issue, consider a trivial Grails application called “messaging” with a single class called Message.


class Message {
  String from
  String to
  String text
}

This is supposed to represent a simple email message, with fields to represent the sender, the receiver, and the text of the message itself. It seemed quite logical at the time to use the words from, to, and text for the fields, but that leads to problems.

I added a MessageController with dynamic scaffolding (i.e., def scaffold = Message) and started the server. When I accessed the MessageController link, however, I got

org.hibernate.exception.SQLGrammarException: could not execute query

In order to see the actual problem, I modified my DataSource.groovy file to add “loggingSql = true” in the dataSource section. After restarting the server, in the console window I saw that the exception was caused by a SQL command generated by the scaffolded list method in MessageController:


Hibernate: 
    select
        this_.id as id0_0_,
        this_.version as version0_0_,
        this_.from as from0_0_,
        this_.text as text0_0_,
        this_.to as to0_0_ 
    from
        message this_ limit ?

This statement looks fine, and in fact there’s nothing wrong with it. I couldn’t see the problem until I switched to MySQL so that I could browse the database independently.

(Switching to another database is covered many places in the Grails literature. In short, it means adding the JDBC driver to the messaging/lib directory, creating the messaging database in MySQL, and changing the driverClassName, url, username, and password settings in DataSource.groovy.)

When I did that and checked the database with the MySQL client, I found the problem (or at least a symptom of it):

mysql> show tables;
Empty set (0.00 sec)

In other words, the problem was that the message table didn’t exist. Somehow the SQL used to generate the table in the first place didn’t work.

Logging the SQL as I did wasn’t sufficient to show me the CREATE TABLE statement. If, however, I go into Config.groovy and change the value of log4j.logger.org.hibernate to debug, I see in the resulting console:


[1125] hbm2ddl.SchemaExport 
    create table message (
        id bigint not null auto_increment,
        version bigint not null,
        from varchar(255) not null,
        text varchar(255) not null,
        to varchar(255) not null,
        primary key (id)
    )

followed immediately by

[1125] hbm2ddl.SchemaExport Unsuccessful: create table message ...
[1125] hbm2ddl.SchemaExport You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from varchar(255) not null, text varchar(255) not null, to varchar(255) not null' at line 1

The problem is that the word “from” is a SQL keyword. MySQL got upset when I tried to use it in the create table statement, as shown above.

How can I fix this? I could change the name of the from property, to, say, sender. Since Grails 1.0, however, GORM now has the ability to do custom ORM mappings, which feels like a cleaner way to solve the problem. Therefore, I added the following closure to my class:


static mapping = {
  from column: 'sender'
}

Now the generated create statement is:


[1157] hbm2ddl.SchemaExport 
    create table message (
        id bigint not null auto_increment,
        version bigint not null,
        sender varchar(255) not null,
        text varchar(255) not null,
        to varchar(255) not null,
        primary key (id)
    )

and the new error is

[1157] hbm2ddl.SchemaExport You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to varchar(255) not null, primary key (id))' at line 1

So apparently the word “to” is also a problem. I therefore modified the mapping closure to include it as well:


static mapping = {
  from column: 'sender'
  to column: 'receiver'
}

Now it all works as it should. The lesson appears to be either that I should keep a list of SQL keywords handy, or simply that the custom ORM DSL is a Good Thing(TM), and so are the logging settings. 🙂

11 responses to “Silly GORM tricks, part III: SQL keywords as attributes”

  1. true, true. I ran into similar errors (once when live coding on stage ;-)). I guess we should file JIRA feature request for better error-messages at this point and ask the tool providers (IDEA, netbeans, and eclipse) to issue respective warnings. Note that this applies to names of the domain class (i.e. table names) as well and the set of reserved names differ between sql dialects of the database vendor.

  2. There’s a page on that topic on the Grails site:
    http://grails.org/Reserved+Names

    But I guess it needs to be expanded a little 🙂

  3. Hi,

    the database vendor Mimer has a sql validator and a list of sql keywords:
    http://developer.mimer.com/validator/index.htm

  4. I’ve run into these a couple of times myself. I noticed that MySQL has the ability to “escape” table column names and table names. That means you could use a reserved word for a column or table name provided you write it surrounded with `backquotes` So it should be something that Hibernate does for us by selecting the database dialect. However, I haven’t seen this setting… check this out:

    http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

    … so it should be very doable to make the Hibernate/GORM MySQL dialect escape all table names and all column names automagically.

    FYI: I ran into this problem the first time when I was doing some MythTV hacking with some straight C to MySQL code… someone should raise name escaping as a feature/enhancement to the Hibernate/GORM guys since this is happening below the Grails layer.

    Just my two bits on the topic. You could see this as something people should just “know” about MySQL…

  5. Kalyan Dasika Avatar
    Kalyan Dasika

    Thank you for posting with a detailed explanation. I will sure remember this.

  6. I raised a JIRA for this when I had it…

    http://jira.codehaus.org/browse/GRAILS-2898

  7. Thank you for this post! It helped me immensely. Grails really shouldn’t fail silently like this.

  8. Database vendors seem to feel completely unrestrained with respect to SQL reserved words. As they add support for hierarchies, cubes, XML and what not the list grows into a dictionary of everyday English. In practice there is no standard.

  9. Thanks for the useful post. I have myself faced this several times. I am sure this is going to be real handy in future.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.