Grails web app generates MySql Duplicate entry for remote DB but not for local test DB

Question

Hosted server: Tomcat 7, MySql 5.5.24

Local server: Tomcat 7, MySql 5.6

Grails 2.4.2

The issue only occurs when using the hosted database, not my local MySql server.

The error occurs in the following code.

@Override
    def save()
    {
        def comment = new Comment(request.JSON)
        def place = Place.get(params.placeId)
        place.addToComments(comment)
        place.save(flush: true) //Explodes here

        respond comment
    }

The error is

| Error 2014-08-13 18:19:01,678 [http-bio-8086-exec-5] ERROR spi.SqlExceptionHelper  - Duplicate entry '1' for key 'UK_fojv2ahcp1jompc2cg2gryqce'
| Error 2014-08-13 18:19:01,789 [http-bio-8086-exec-5] ERROR errors.GrailsExceptionResolver  - MySQLIntegrityConstraintViolationException occurred when processing request: [POST] /SecondAmendmentSupporters/places/1/comments/
Duplicate entry '1' for key 'UK_fojv2ahcp1jompc2cg2gryqce'. Stacktrace follows:
Message: Duplicate entry '1' for key 'UK_fojv2ahcp1jompc2cg2gryqce'
    Line | Method
->>  411 | handleNewInstance  in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    386 | getInstance        in     ''
|   1041 | createSQLException in com.mysql.jdbc.SQLError
|   4237 | checkErrorPacket   in com.mysql.jdbc.MysqlIO
|   4169 | checkErrorPacket . in     ''
|   2617 | sendCommand        in     ''
|   2778 | sqlQueryDirect . . in     ''
|   2834 | execSQL            in com.mysql.jdbc.ConnectionImpl
|   2156 | executeInternal .  in com.mysql.jdbc.PreparedStatement
|   2441 | executeUpdate      in     ''
|   2366 | executeUpdate . .  in     ''
|   2350 | executeUpdate      in     ''
|     39 | save . . . . . . . in secondamendmentsupporters.CommentController
|    198 | doFilter           in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter . . . . . in grails.plugin.cache.web.filter.AbstractFilter
|    895 | runTask            in java.util.concurrent.ThreadPoolExecutor$Worker
|    918 | run . . . . . . .  in     ''
^    695 | run                in java.lang.Thread

The association between Place and Comment is a many to one. Many comments to one place.

If you look at the request below, it looks as if the database is trying to add a new entry to the DB for the Place with ID 1 versus just updating it.

[POST] /SecondAmendmentSupporters/places/1/comments/

The only difference between the remote DB and the local DB is the MySql version. Could this be the problem?

EDIT

Place Domain

static hasMany =
    [
        comments:Comment
    ]

Comment Domain

static belongsTo = [place: Place]

EDIT

Turns out that grails is treating my foreign key place_id as an Index(MUL) locally but on my remote DB, it’s Unique(UNI) thus causing the duplicate id exception. Thoughts on how to fix this?

Answer 1

After many many hours, I was able to determine the difference between my local db and my remote db. Turns out that GORM set my foreign key 'place_id' as UNI instead of a MUL. I dropped my table in the remote DB and added the following mapping to my comment domain and problem solved.

static mapping = {place index: 'place_id'}