Table creation fails
grails, grails-orm, h2
Solution
I was using the wrong dialect; my DataSource.groovy had the following dialect set in the dataSource block:
dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
I added the following line for the development data source:
dialect = "org.hibernate.dialect.H2Dialect"
This has fixed the problem.
Problem
I'm using an H2 database for testing my Grails app. I have some simple domain classes like: ``` package mypackage class UserSession { User user String sessionTokenHash // last seen info String lastSeenIP Date lastSeenTime String lastSeenUserAgent String lastSeenURL } ``` however, the table doesn't seem to get created correctly. ``` hbm2ddl.SchemaExport Unsuccessful: create table user_session (id bigint not null auto_increment, version bigint not null, last_seenip varchar(255) not null, last_seen_time datetime not null, last_seenurl varchar(255) not null, last_seen_user_agent varchar(255) not null, session_token_hash varchar(255) not null, user_id bigint not null, primary key (id)) ENGINE=InnoDB hbm2ddl.SchemaExport Syntax error in SQL statement "CREATE TABLE USER_SESSION (ID BIGINT NOT NULL AUTO_INCREMENT, VERSION BIGINT NOT NULL, LAST_SEENIP VARCHAR(255) NOT NULL, LAST_SEEN_TIME DATETIME NOT NULL, LAST_SEENURL VARCHAR(255) NOT NULL, LAST_SEEN_USER_AGENT VARCHAR(255) NOT NULL, SESSION_TOKEN_HASH VARCHAR(255) NOT NULL, USER_ID BIGINT NOT NULL, PRIMARY KEY (ID)) ENGINE=[*]INNODB "; expected "identifier"; SQL statement: create table user_session (id bigint not null auto_increment, version bigint not null, last_seenip varchar(255) not null, last_seen_time datetime not null, last_seenurl varchar(255) not null, last_seen_user_agent varchar(255) not null, session_token_hash varchar(255) not null, user_id bigint not null, primary key (id)) ENGINE=InnoDB [42001-147] ``` It's set to use a temporary in-memory H2 database in `create-drop` mode.