9

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.

skaz
  • 21,962
  • 20
  • 69
  • 98
  • Have you been using the plugin successfully up till now? – David May 13 '12 at 05:23
  • @David It is still pretty new to me. I tried a few basic upgrades and they worked. – skaz May 13 '12 at 13:33
  • I was just thinking if maybe you have `dbcreate="..something.."` in your DataSource.groovy file it might be conflicting with the plugin. But if you've done upgrades already that seems to be less likely. – David May 15 '12 at 09:14
  • Did anyone manage to solve that issue? I have the exact same issue right now and I can't figure out what is happening. – Sebastien May 23 '12 at 16:04

3 Answers3

9

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.

Sebastien
  • 3,583
  • 4
  • 43
  • 82
  • 1
    Strange, this solution didn't work for me. I had multiple `addForeignConstraint`s and multiple `createIndex`s. Changing the key did allow it to work though... don't know if that will have any adverse effects later. – Weezle Aug 07 '12 at 16:35
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.

Craig Sherstan
  • 61
  • 4
1

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.

Community
  • 1
  • 1
Peter
  • 29,498
  • 21
  • 89
  • 122