Grails
  1. Grails
  2. GRAILS-6217

Invalid join table inserts for Many-to-Many relation with List

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.1.1, 1.2.2
    • Fix Version/s: 2.1.1, 2.2-RC1
    • Component/s: Persistence
    • Labels:
      None
    • Environment:
      CentOS 5, MySQL 5 (InnoDB), HSQLDB
    • Testcase included:
      yes

      Description

      A many-to-many relationship between two domain classes where one or both of the domain class holds the relationship in a list appears to save incorrectly in the join table.

      If one side of the many-to-many relation has a List instead of a Set, then a duplicate key exception is generated during save:
      'org.hibernate.util.JDBCExceptionReporter - Duplicate entry '5-3' for key 1'

      If both sides of the many-to-many relation have a List instead of a Set, then there is no duplicate key exception. However, the join table has two entries added, with the idx row null for one of the lists:
      --------------------------------+

      game_id ref_id refs_idx games_idx

      --------------------------------+

      1 3 NULL 0
      1 3 0 NULL

      The save appears to succeed but loading the collection later results in a null index exception: 'org.hibernate.HibernateException: null index column for collection: Ref.games'

      1. manytomanylist.tar.gz
        772 kB
        Gabe Hamilton

        Activity

        Hide
        Dillon Bly added a comment -
        Ref.groovy
        class Ref {
        
            static hasMany = [whistles:Whistle, games:Game]
        
            String name
            List whistles
            List games
        
        }
        
        Whistle.groovy
        class Whistle {
        
            static hasMany = [refs:Ref]
        
            static belongsTo = Ref
        
            String name
        
        }
        
        Whistle.groovy
        class Whistle {
        
            static hasMany = [refs:Ref]
        
            static belongsTo = Ref
        
            String name
        
        }
        
        ManyToManyTests.groovy
            void testSaveManyToManyWithOneList() {
                def ref = new Ref(name: 'John')
                def whistle = new Whistle(name: 'Whistle')
                ref.addToWhistles(whistle)
                ref.save(flush:true) // duplicate key exception
            }
        
            void testSaveManyToManyWithTwoLists() {
                def ref = new Ref(name: 'John')
                def game = new Game(name: 'Match 2010')
                ref.addToGames(game)
                ref.save(flush:true)
        
                def refId = ref.id
                ref.discard()
        
                ref = Ref.get(refId)
                ref.games // will throw a null index column exception if many-to-many save was invalid
            }
        
        Show
        Dillon Bly added a comment - Ref.groovy class Ref { static hasMany = [whistles:Whistle, games:Game] String name List whistles List games } Whistle.groovy class Whistle { static hasMany = [refs:Ref] static belongsTo = Ref String name } Whistle.groovy class Whistle { static hasMany = [refs:Ref] static belongsTo = Ref String name } ManyToManyTests.groovy void testSaveManyToManyWithOneList() { def ref = new Ref(name: 'John') def whistle = new Whistle(name: 'Whistle') ref.addToWhistles(whistle) ref.save(flush: true ) // duplicate key exception } void testSaveManyToManyWithTwoLists() { def ref = new Ref(name: 'John') def game = new Game(name: 'Match 2010') ref.addToGames(game) ref.save(flush: true ) def refId = ref.id ref.discard() ref = Ref.get(refId) ref.games // will throw a null index column exception if many-to-many save was invalid }
        Hide
        Luis Daniel Ibáñez added a comment -

        Hit this one, got a Sorted Set holding one side. Commented it out and add a vote.

        Show
        Luis Daniel Ibáñez added a comment - Hit this one, got a Sorted Set holding one side. Commented it out and add a vote.
        Hide
        Daniel Glauser added a comment -

        I was able to get around this issue by making all the hasMany relationships backed by a SortedSet.

        Show
        Daniel Glauser added a comment - I was able to get around this issue by making all the hasMany relationships backed by a SortedSet.
        Hide
        Matt Stine added a comment -

        Unfortunately a SortedSet doesn't help me here, as I need to preserve the exact order in which elements are added.

        Also, I've noticed that when using the mapping DSL, if you specify the join tables and index columns as such:

        //in Colony.groovy
        List genes
        static belongsTo = Gene
        static hasMany = [genes:Gene]

        static mapping =

        { ... genes column:'colony_id', joinTable:'colonies_genes', indexColumn:[name:'gene_index'], fetch:'join' ... }

        //In Gene.groovy
        List colonies
        static hasMany = [colonies:Colony]

        static mapping =

        { ... colonies column:'gene_id', joinTable:'colonies_genes', indexColumn:[name:'gene_index'] ... }

        You will exactly two inserts that are duplicates...all of the columns are populated, rather than the index column being blank in one case. I was getting that behavior prior to adding the indexColumn on the Gene side of the relation.

        Show
        Matt Stine added a comment - Unfortunately a SortedSet doesn't help me here, as I need to preserve the exact order in which elements are added. Also, I've noticed that when using the mapping DSL, if you specify the join tables and index columns as such: //in Colony.groovy List genes static belongsTo = Gene static hasMany = [genes:Gene] static mapping = { ... genes column:'colony_id', joinTable:'colonies_genes', indexColumn:[name:'gene_index'], fetch:'join' ... } //In Gene.groovy List colonies static hasMany = [colonies:Colony] static mapping = { ... colonies column:'gene_id', joinTable:'colonies_genes', indexColumn:[name:'gene_index'] ... } You will exactly two inserts that are duplicates...all of the columns are populated, rather than the index column being blank in one case. I was getting that behavior prior to adding the indexColumn on the Gene side of the relation.
        Hide
        G V Dharnidhar added a comment -

        I am a newbie to grails and only thing I could do to overcome this issue was redifine my List and Set like this :-
        class Book

        { Long id String Title def belongsTo = Author def hasMany = [authors : Author] Set authors = new HashSet() // Do we need this? }

        This will give u DB like this :-
        book_id product_id book_idx product_idx
        4 3 null null

        Does anyone have any workaround, so that we can have List on both side instead of Set ??

        Show
        G V Dharnidhar added a comment - I am a newbie to grails and only thing I could do to overcome this issue was redifine my List and Set like this :- class Book { Long id String Title def belongsTo = Author def hasMany = [authors : Author] Set authors = new HashSet() // Do we need this? } This will give u DB like this :- book_id product_id book_idx product_idx 4 3 null null Does anyone have any workaround, so that we can have List on both side instead of Set ??
        Hide
        Manuel Niederkofler added a comment -

        A unique key violation occurred in my application when using a SortedSet for the many-to-many relationship. Grails version is 2.0.0. I think my problem is related to this issue. The problem in my case is solved when Set is used.

        Show
        Manuel Niederkofler added a comment - A unique key violation occurred in my application when using a SortedSet for the many-to-many relationship. Grails version is 2.0.0. I think my problem is related to this issue. The problem in my case is solved when Set is used.
        Hide
        Vadimo added a comment -

        Same situation with 2.0.1
        Having a list of many-to-many association results in a corrupt index

        User
        class User {
        	String email
        	String password
        	String name
        	
        	List<User> subscribers
        	List<User> publishers
        	
        	static hasMany = [subscribers: User, publishers: User]
        	static belongsTo = User
        	
        	static constraints = {
        		email blank: false, unique: true, email: true
        		password blank: false
        		subscribers nullable: true
        		publishers nullable: true
        	}
        
        	static mapping = {
        		password column: '`password`'
        		subscribers joinTable: [name: 'user_subscribers_publisher', column: 'subscriber_ID' , key: 'publisher_ID']
        		publishers joinTable: [name: 'user_subscribers_publisher', column: 'publisher_ID', key: 'subscriber_ID']
        	}
        

        SELECT * FROM USER_SUBSCRIBERS_PUBLISHER;

        SUBSCRIBER_ID PUBLISHER_ID PUBLISHERS_IDX SUBSCRIBERS_IDX
        4 1 null 0
        4 2 null 0
        Show
        Vadimo added a comment - Same situation with 2.0.1 Having a list of many-to-many association results in a corrupt index User class User { String email String password String name List<User> subscribers List<User> publishers static hasMany = [subscribers: User, publishers: User] static belongsTo = User static constraints = { email blank: false , unique: true , email: true password blank: false subscribers nullable: true publishers nullable: true } static mapping = { password column: '`password`' subscribers joinTable: [name: 'user_subscribers_publisher', column: 'subscriber_ID' , key: 'publisher_ID'] publishers joinTable: [name: 'user_subscribers_publisher', column: 'publisher_ID', key: 'subscriber_ID'] } SELECT * FROM USER_SUBSCRIBERS_PUBLISHER; SUBSCRIBER_ID PUBLISHER_ID PUBLISHERS_IDX SUBSCRIBERS_IDX 4 1 null 0 4 2 null 0
        Hide
        Gabe Hamilton added a comment - - edited

        Same issue in 2.0.4. Integration test BookTests fails and demonstrates the problem, attachment manytomanylist.tar.gz.

        Side note. When both sides of the relationship are Lists the save call passes in the test but as can be see in the controller one index column is null, which causes an error.

        Show
        Gabe Hamilton added a comment - - edited Same issue in 2.0.4. Integration test BookTests fails and demonstrates the problem, attachment manytomanylist.tar.gz. Side note. When both sides of the relationship are Lists the save call passes in the test but as can be see in the controller one index column is null, which causes an error.
        Hide
        Graeme Rocher added a comment -

        I spent quite a while trying to fix this, but Hibernate is pretty explicit with regards to many-to-manys in that the owning side has to take responsibility for persisting the association. Since the owning side is a Set in the example and not a List this means that the index is not created and then later the non-owning side tries to create the index but fails.

        I've updating Grails to provide a better error message that states:

        "Invalid association [Book->authors]. List collection types only supported on the owning side of a many-to-many relationship"

        What this basically means is that you have define the List only on the owning side of an association (the side that takes responsibility for persisting the association).

        Show
        Graeme Rocher added a comment - I spent quite a while trying to fix this, but Hibernate is pretty explicit with regards to many-to-manys in that the owning side has to take responsibility for persisting the association. Since the owning side is a Set in the example and not a List this means that the index is not created and then later the non-owning side tries to create the index but fails. I've updating Grails to provide a better error message that states: "Invalid association [Book->authors] . List collection types only supported on the owning side of a many-to-many relationship" What this basically means is that you have define the List only on the owning side of an association (the side that takes responsibility for persisting the association).

          People

          • Assignee:
            Graeme Rocher
            Reporter:
            Dillon Bly
          • Votes:
            11 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:
              Last Reviewed:

              Development