Grails & MySQL Tables

Discovered a 'gotcha' today with Grails when using it to create tables in a MySQL database - we had the following entry in our DataSources.groovy:

 development {
  dataSource {
   dbCreate = "create-drop"
   url = "jdbc:mysql://localhost/mydb"
  }
 }

Nothing obviously wrong with that - except that MySQL will use its default 'engine' to create the tables.

This is typically the MyISAM engine which offers fast reads, but no transaction protection.

The reason MySQL defaults to this engine is that it is often used to hold large amounts of relatively static and read-only data (a product list for a website for instance), often as part of a LAMP deployment. Such read-only deployments do not require transactions.

You can check which engine is associated with each table in MySQL via the following statement:

 SHOW TABLE STATUS;

If MySQL is creating your database tables with the MyISAM engine, then the following statement in your Grails Services will have no effect:

 static transactional = true

As such, Grails methods such as user.discard() appear not to work - the update has already been written to the database and cannot be rolled back.

This can also adversely affect Integration Tests if they try to roll-back database updates as part of a test-case scenario (in readiness for subsequent tests). You expect the test-case to have discarded whatever test data was written to the database, only to discover that it still exists!

The solution is quite easy - you need to add a dialect statement to the dataSource to tell MySQL to use a different engine; one that offers transaction protection, such as InnoDB:

 development {
  dataSource {
   dbCreate = "create-drop"
   dialect= "org.hibernate.dialect.MySQLInnoDBDialect"
   url = "jdbc:mysql://localhost/mydb"
  }
 }

The InnoDB engine also has the advantage of providing row-level locking (whereas MyISAM locks the whole table for each insert or update) and it also enforces foreign-keys constraints.

Consequently, web-applications with medium-to-large volumes of data are likely to perform faster if a transactional engine such as InnoDB is specified when using MySQL.

Note that you could also change the default storage engine used my MySQL via the my.cnf file:

 default-storage-engine=innodb