I recently ran into a SQL exception creating a simple domain class called “Group” that looked like this:
class Group {
static constratints = {}
}
When I ran my application I got this not-exactly-helpful error:
| Compiling 1 source files.... | Running Grails application | Error 2012-08-14 09:30:01,325 [pool-5-thread-1] ERROR hbm2ddl.SchemaExport - Unsuccessful: create table group (id bigint generated by default as identity, version bigint not null, primary key (id)) | Error 2012-08-14 09:30:01,325 [pool-5-thread-1] ERROR hbm2ddl.SchemaExport - Syntax error in SQL statement "CREATE TABLE GROUP[*] (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, VERSION BIGINT NOT NULL, PRIMARY KEY (ID)) "; expected "identifier"; SQL statement: create table group (id bigint generated by default as identity, version bigint not null, primary key (id)) [42001-164] | Server running. Browse to http://localhost:8080/test
I should say that once I figured out the problem, then the error message did make sense. The problem turned out to be that I was trying to use a reserved word for the H2 database, in this case “Group”. And you cannot create a table with the same name as a keyword.
Luckily, this is pretty easy to fix and still use the same domain class. Just add a “static mapping” block so your class looks like this:
class Group {
static constratints = {}
static mapping = { table 'my_group' }
}
This will vary based on your database implementation. For H2, here are the list of keywords you cannot use (from H2 documentation):
CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DISTINCT, EXCEPT, EXISTS, FALSE, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, IS, JOIN, LIKE, LIMIT, MINUS, NATURAL, NOT, NULL, ON, ORDER, PRIMARY, ROWNUM, SELECT, SYSDATE, SYSTIME, SYSTIMESTAMP, TODAY, TRUE, UNION, UNIQUE, WHERE
Same thing with ‘User’ and PostgreSQL. This one bit me when doing a quick test of the Spring Security plugin with the suggested defaults.
You can usually escape reserved words using a backtick, this seemed to work here.
static mapping = {
table ‘`Group`’
}
The generated SQL will look something like this.
insert into “Group” (id, version, name) values (null, ?, ?)
I included the SQL form my list method but I am sure you knew what I meant :D Here is what the create statement looks like.
create table “Group” (id bigint generated by default as identity, version bigint not null, name varchar(255) not null, primary key (id));