JDBC Exception while trying to update associations in Many-To-Many Relationship

[dipayanb]

I have two domain classes:

package com.dipayan

class Author {

    String name
    static hasMany = [books: Book]

    static constraints = {
    }
}

And

package com.dipayan

class Book {

    String name
    int pageCount

    static belongsTo = Author
    static hasMany = [authors: Author]

    static constraints = {
    }
}

and I am using scaffolded controller for both which was generated from gson templates.
After that I inserted 4 authors and 4 books using POST. After the insertion the db content is given below:

mysql> show tables;
+-----------------------+
| Tables_in_person_test |
+-----------------------+
| author                |
| author_books          |
| book                  |
+-----------------------+
3 rows in set (0.00 sec)

mysql> select * from author;
+----+---------+-----------+
| id | version | name      |
+----+---------+-----------+
|  1 |       0 | Author #1 |
|  2 |       0 | Author #2 |
|  3 |       0 | Author #3 |
|  4 |       0 | Author #4 |
+----+---------+-----------+
4 rows in set (0.00 sec)

mysql> select * from book;
+----+---------+---------+------------+
| id | version | name    | page_count |
+----+---------+---------+------------+
|  1 |       0 | Book #1 |        400 |
|  2 |       0 | Book #2 |        400 |
|  3 |       0 | Book #3 |        400 |
|  4 |       0 | Book #4 |        400 |
+----+---------+---------+------------+
4 rows in set (0.00 sec)

After this I tried to create association for one author and three books by doing a PUT request which went through properly.

{
    "id": "1",
    "books" : [
        {"id": 2},
        {"id": 3},
        {"id": 4}
    ]
}

And the DB content is given below after this point.

mysql> select * from author_books;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       2 |         1 |
|       3 |         1 |
|       4 |         1 |
+---------+-----------+
3 rows in set (0.00 sec)

Now I tried associating a different Author with the same set of books with a similar PUT request and here the update fails with a JDBC Integrity constraint violation. Below is the given json and the db content after this point.

{
    "id": "2",
    "books" : [
        {"id": 2},
        {"id": 3},
        {"id": 4}
    ]
}

mysql> select * from author_books;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       2 |         1 |
|       3 |         1 |
|       4 |         1 |
+---------+-----------+
3 rows in set (0.00 sec)

Error message:

| Error 2013-07-25 22:14:34,990 [http-bio-8080-exec-1] ERROR util.JDBCExceptionReporter  - Duplicate entry '1-3' for key 'PRIMARY'
| Error 2013-07-25 22:14:34,994 [http-bio-8080-exec-1] ERROR events.PatchedDefaultFlushEventListener  - Could not synchronize database state with session
Message: Could not execute JDBC batch update
    Line | Method
->>   61 | update    in com.dipayan.AuthorController
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    195 | doFilter  in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter  in grails.plugin.cache.web.filter.AbstractFilter
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run       in java.lang.Thread

Caused by BatchUpdateException: Duplicate entry '1-3' for key 'PRIMARY'
->> 2054 | executeBatchSerially in com.mysql.jdbc.PreparedStatement
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|   1467 | executeBatch in     ''
|    297 | executeBatch in org.apache.commons.dbcp.DelegatingStatement
|     61 | update    in com.dipayan.AuthorController
|    195 | doFilter  in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter  in grails.plugin.cache.web.filter.AbstractFilter
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . in java.lang.Thread

Caused by MySQLIntegrityConstraintViolationException: Duplicate entry '1-3' for key 'PRIMARY'
->>  411 | handleNewInstance in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    386 | getInstance in     ''
|   1041 | createSQLException in com.mysql.jdbc.SQLError
|   4187 | checkErrorPacket in com.mysql.jdbc.MysqlIO
|   4119 | checkErrorPacket in     ''
|   2570 | sendCommand in     ''
|   2731 | sqlQueryDirect in     ''
|   2815 | execSQL   in com.mysql.jdbc.ConnectionImpl
|   2155 | executeInternal in com.mysql.jdbc.PreparedStatement
|   2458 | executeUpdate in     ''
|   2006 | executeBatchSerially in     ''
|   1467 | executeBatch in     ''
|    297 | executeBatch in org.apache.commons.dbcp.DelegatingStatement
|     61 | update    in com.dipayan.AuthorController
|    195 | doFilter  in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter  in grails.plugin.cache.web.filter.AbstractFilter
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . in java.lang.Thread
| Error 2013-07-25 22:14:35,004 [http-bio-8080-exec-1] ERROR errors.GrailsExceptionResolver  - MySQLIntegrityConstraintViolationException occurred when processing request: [PUT] /AuthorTest/api/Author/2
Duplicate entry '1-3' for key 'PRIMARY'. Stacktrace follows:
Message: Duplicate entry '1-3' for key 'PRIMARY'
    Line | Method
->>  411 | handleNewInstance    in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    386 | getInstance          in     ''
|   1041 | createSQLException . in com.mysql.jdbc.SQLError
|   4187 | checkErrorPacket     in com.mysql.jdbc.MysqlIO
|   4119 | checkErrorPacket . . in     ''
|   2570 | sendCommand          in     ''
|   2731 | sqlQueryDirect . . . in     ''
|   2815 | execSQL              in com.mysql.jdbc.ConnectionImpl
|   2155 | executeInternal . .  in com.mysql.jdbc.PreparedStatement
|   2458 | executeUpdate        in     ''
|   2006 | executeBatchSerially in     ''
|   1467 | executeBatch         in     ''
|    297 | executeBatch . . . . in org.apache.commons.dbcp.DelegatingStatement
|     61 | update               in com.dipayan.AuthorController
|    195 | doFilter . . . . . . in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter             in grails.plugin.cache.web.filter.AbstractFilter
|   1145 | runWorker . . . . .  in java.util.concurrent.ThreadPoolExecutor
|    615 | run                  in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . . . . . .  in java.lang.Thread

I am really in a fix as I am using this in production and is unable to understand if I am doing any wrong.

[dipayanb]

The exact scenario passes if the code for GrailsDomainDeserializer is changed as given below:

    private void bindOwner(value, GrailsDomainClassProperty property, T owner) {
        if (value == null) return

        if (value instanceof Map) {
            value = value.values()
        }

        if (property.manyToOne) {
            value.each {
                it[property.name] = owner
            }
        } else if (property.oneToMany) {
            def addToMethodName = getAddToMethodName(property)
            value."$addToMethodName" owner
        } else if (property.manyToMany) {
            /*
            def addToMethodName = getAddToMethodName(property)
            value.each {
                it."$addToMethodName" owner
            }*/
        } else {
            value."$property.name" = owner
        }
    }

This dirty fix is working for me for the time being.