Grails

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

Details

  • Type: Bug Bug
  • Status: Closed 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'

  2. GZip Archive
    manytomanylist.tar.gz
    30/May/12 12:20 PM
    772 kB
    Gabe Hamilton

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Vote (11)
Watch (9)

Dates

  • Created:
    Updated:
    Resolved:
    Last Reviewed: