CODEWITHSUNDEEP
mysqlgrails
John Little
John Little

Reputation: 12338

Grails: cant create tables on mysql

I can run my grails app against the built in memory DB, but when I try to get it to work with MYSQL, it always fails to create the schema with the below error.

2014-04-11 16:05:16,445 [localhost-startStop-1] ERROR hbm2ddl.SchemaExport  - Unsuccessful: create table registration_code (id bigint not null auto_increment, date_created datetime not null, token varchar(255) not null, username varchar(255) not null, primary key (id)) type=InnoDB 
2014-04-11 16:05:16,448 [localhost-startStop-1] ERROR 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 'type=InnoDB' at line 1 
2014-04-11 16:05:16,449 [localhost-startStop-1] ERROR hbm2ddl.SchemaExport  - Unsuccessful: create table report (id bigint not null auto_increment, version bigint not null, category varchar(255) not null, description longtext not null, name varchar(255) not null unique, reportsql longtext not null, views integer not null, primary key (id)) type=InnoDB

This error tells me almost nothing.

It could be that its trying to create a mysql schema against he built in memory DB for some reason.

the registration_code table is presumably part of spring security module I have installed. the "report" table I can create by hand, it contains no reserved words or similar.

In mysql, I created a database with the same name as the application (I am guessing I have to do this). The user I'm using has global permissions on everything, and the created db.

Is there any way to debug this? e.g. any way to see what SQL grails is trying to use?

I have tried to call all my domain objects and fields things which are not reserved (e.g. secRoles instead of roles)

My datasource.groovy looks like this:

dataSource {
    dialect = org.hibernate.dialect.MySQLInnoDBDialect
    pooled = true
    jmxExport = true
    driverClassName = "com.mysql.jdbc.Driver"
    properties {
        maxActive = -1
        minEvictableIdleTimeMillis=1800000
        timeBetweenEvictionRunsMillis=1800000
        numTestsPerEvictionRun=3
        testOnBorrow=true
        testWhileIdle=true
        testOnReturn=true
        validationQuery="SELECT 1"
      }
}
environments {
    development {
        dataSource {
            dbCreate = "create"
            username = "myuser"
            password = "mypass"
            url = 'jdbc:mysql://localhost/'
        }

mysql 5.6 grails 2.3.6 Windows server.

Upvotes: 0

Views: 1720

Answers (1)

John Little
John Little

Reputation: 12338

OK, the solutions is this:

dialect = org.hibernate.dialect.MySQL5InnoDBDialect

rather than:

dialect = org.hibernate.dialect.MySQLInnoDBDialect

Shame that grails does not come with an example mysql connection in the default conf file.

Upvotes: 2

Related Questions