Limitations of Using MySql with Grails

Since Grails uses Hibernate inherently, it is a good idea to understand the limitations before storming ahead and bashing your brain trying to figure out why things don’t work. The first issue I came across was with a domain object having a long text field, such as a “url” which can be at most 1024 characters.
class Merchant {
	String name
	String url
        static constraints = {
               name(blank: false, unique: true)
               url(maxSize: 1000, unique: true, url: true, blank: false)
        }
}\<span style="font-family: 'Lucida Grande', 'Lucida Sans Unicode', Tahoma, Verdana, sans-serif; line-height: 19px; white-space: normal; font-size: 13px;">After starting up the app using "grails run-app" we run into a problem with the hbm2ddl.SchemaUpdate saying that</span>
BLOB/TEXT column 'url' used in key specification without a key length

Columns with SQL type TEXT exceeding 256 characters will fail table creation because of MySQL Error 1170. Two immediate options for relief are: 1) hand-craft the schema DDL and generate your database schema manually, or 2) remove any constraints that might require an index (e.g. unique constraint).

If you choose option 1, you won’t be able to use the “create-drop” option in your DataSource.groovy configuration, or even “update” if you are introducing a new field. This slows down development considerably.

If you choose option 2, you will likely violate business rules and introduce data integrity issues.

The Solution

A proper solution (but not necessarily the most elegant) would be using a custom mapping in the domain class to override the length of the column to be used in the index. The obvious drawback of this is the duplication of column length in the constraints and the mapping.
class Merchant {
	String name
	String url
        static mapping = {
            url sqlType: "varchar(1000)"
        }
        static constraints = {
               name(blank: false, unique: true)
               url(maxSize: 1000, unique: true, url: true, blank: false)
        }
}

The consequences of this solution is that the column is now a VARCHAR and not TEXT. In your scenario, this might not be desirable, and of course you should do your own diligence in comparing the benefits/costs of using VARCHAR as opposed to TEXT. In most cases, the differences of using VARCHAR or TEXT are negligible.

Further reading: The Definitive Guide to Grails, Second Edition

August 20th, 2009 in Programming | tags: , , ,