CODEWITHSUNDEEP
mysqlgrails
pav
pav

Reputation: 53

Grails: error in SQL syntax when changing the hibernate dialect

I am using Grails with mySQL database and I am trying to change the database engine. As far as I researched this can be done best with

 dialect = "org.hibernate.dialect.[MyDialect]"

in the DataSource.groovy config. But when I set the dialect to org.hibernate.dialect.MySQLMyISAMDialect creation of my table fails with error:

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=MyISAM' at line 1

I also tried to alter the table later with:

sql.execute("ALTER TABLE book ENGINE = MYISAM;")

and this actually works but all the foreign keys are dropped for the table when changing the engine after the creation.

How should I avoid the error and change the engine normally?

Upvotes: 2

Views: 1331

Answers (1)

Burt Beckwith
Burt Beckwith

Reputation: 75671

Why would you want to use MyISAM??? It doesn't support foreign keys or transactions. And it's rarely faster than InnoDB since InnoDB uses row locks and MVCC, as opposed to MyISAM's full-table locks.

Having said that, you can get this to work. You must be using a newer version of MySQL since the type attribute was deprecated for a while and is now not supported - you have to use ENGINE instead. There's no dialect that supports this (note that org.hibernate.dialect.MySQL5InnoDBDialect does the right thing for InnoDB) so you need to create your own.

Create this class in src/groovy or src/java (change the package and/or class name):

package com.mycompany.myapp

import org.hibernate.dialect.MySQLMyISAMDialect

class MySQL5MyISAMDialect extends MySQLMyISAMDialect {
   String getTableTypeString() {
      " ENGINE=MyISAM"
   }
}

and reference it in DataSource.groovy as you showed in your question:

dialect = com.mycompany.myapp.MySQL5MyISAMDialect

Upvotes: 1

Related Questions