Grails Database Migration Plugin Issues

Question

When I use the Grails Database Migration Plugin and run a dbm-gorm-diff (for example, after installing the Spring Security Facebook plugin) I have been getting problems like:

Error: Error executing SQL CREATE INDEX `FK609FD5A460CFCC39` ON `facebook_user`(`user_id`): Incorrect index name 'FK609FD5A460CFCC39'

It looks like the index in question is both a FK constraint and is then reused as an index later in the generated upgrade script. If i change the name, thus removing the duplicate, everything works fine. I am using Mysql. Am I doing something wrong?

Thanks.

Answer 1

I just found out that if I edit changelog.groovy to place addForeignConstraint's after createIndex's, it works like a charm. Yet another problem in the changelog generation script I guess.

Answer 2

I suspect this is actually related to MySQL and not to the plugin itself. See this bug: http://bugs.mysql.com/bug.php?id=55465

Sebastien's answer is a work around.

Answer 3

As per this question/answer, MYSQL automatically indexes foreign key columns. So when you add a foreign key constraint, you don't need to also define an index. I use the db migration plugin and just remove the 'index' entries for foreign keys that the dbm-gorm-diff generates.

I think this is a bit better than changing the name, since that likely creates more than one index on the same column which is just a waste of resources.