Grails

hasMany JoinTable issue - doing clear() does not delete records from the join table

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.3.7
  • Fix Version/s: None
  • Component/s: Persistence
  • Environment:
    Windows 7, Grails 1.3.7, Mysql

Description

I have a class

class Product{


    static hasMany = [competitorProducts : Product]

    static mapping = {
        competitorProducts joinTable:[name:'product_competitor_products', key:'product_id', column:'competitor_product_id']
    }

}

Now, lets say I add three competitor products to a product x using x.addToCompetitorProducts, It will insert three records into the product_competitor_products table.

Now, I do x.competitorProducts.clear() and then re-add the same three competitor products to x using x.addToCompetitorProducts

I dont know why, but it creates duplicate records of same competitor products for product x in the table. After calling save, there will be six records in the table, instead of just three. The three records will be duplicate.

Please note - It will work in tests and can not be reproduced in tests, That said
If you do following in tests, it will pass without any issues, It will show you just three competitor products if you debug the running application and inspect product.competitorProducts, but if you check in database, there will be six records and not three.

following test will pass, but if you run app in dev env, and do the same, there will be six records in db.

product.addToCompetitorProducts(p1)
product.addToCompetitorProducts(p2)
product.addToCompetitorProducts(p3)

product.save()

product = Product.get(id)
assert product.competitorProducts.size() == 3

product.competitorProducts.clear()

product.addToCompetitorProducts(p1)
product.addToCompetitorProducts(p2)
product.addToCompetitorProducts(p3)

product.save()

product = Product.get(id)
assert product.competitorProducts.size() == 3

Note - It works if you add different competitor records rather then the same after doing clear(), and it will create just three records in database, (removing old three records and adding three new)

Please look at these threads
http://grails.1312388.n4.nabble.com/hasMany-JoinTable-issue-does-not-delete-records-from-the-join-table-td3818727.html
http://grails.1312388.n4.nabble.com/Remove-from-collection-td1355730.html

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
sudhir added a comment -

Here's the code that you can use to reproduce the issue.

public class A implements Comparable<A>{

	String name
	
	SortedSet competitors
	
		static hasMany = [competitors : A]
	
		static mapping = {
			competitors joinTable:[name:'a_competitors', key:'a_id', column:'competitor_id']
		}
	
		int compareTo(obj) {
			name.compareTo(obj.name)
		}
	
}

Execute following code two/three times

		A sample = A.get(1)
		sample.competitors.clear()
		
		sample.addToCompetitors(A.get(2))
		sample.addToCompetitors(A.get(3))

As we clear the competitors every time the above code runs, after two/three runs, there should be just two records in the table 'a_competitors'
But there will be duplicate records.

Show
sudhir added a comment - Here's the code that you can use to reproduce the issue. public class A implements Comparable<A>{ String name SortedSet competitors static hasMany = [competitors : A] static mapping = { competitors joinTable:[name:'a_competitors', key:'a_id', column:'competitor_id'] } int compareTo(obj) { name.compareTo(obj.name) } } Execute following code two/three times A sample = A.get(1) sample.competitors.clear() sample.addToCompetitors(A.get(2)) sample.addToCompetitors(A.get(3)) As we clear the competitors every time the above code runs, after two/three runs, there should be just two records in the table 'a_competitors' But there will be duplicate records.
Hide
Permalink
sudhir added a comment -

To make it more worse,
Using removeFromCompetitors too does not work in my case, if I do removeFromCompetitors(p2) and then re-add the same object which was just removed, still it will create duplicate records in the table.

However If I add different object after doing removeFromCompetitor, it will delete the old record from table and insert new. IF there are duplicate records p2 and I do, removeFromCompetitors(p2) and then instead of again adding p2, add some other object p3, all the duplicate records of p2 will be deleted from join table.

So weather I use clear() or removeFromX, it does not make any difference.

Show
sudhir added a comment - To make it more worse, Using removeFromCompetitors too does not work in my case, if I do removeFromCompetitors(p2) and then re-add the same object which was just removed, still it will create duplicate records in the table. However If I add different object after doing removeFromCompetitor, it will delete the old record from table and insert new. IF there are duplicate records p2 and I do, removeFromCompetitors(p2) and then instead of again adding p2, add some other object p3, all the duplicate records of p2 will be deleted from join table. So weather I use clear() or removeFromX, it does not make any difference.
Hide
Permalink
sudhir added a comment -

Its weird, weird that clear() works and removes all the records from the set and delete from database too, if we don't do addToCompetitors quickly after calling clear()

Show
sudhir added a comment - Its weird, weird that clear() works and removes all the records from the set and delete from database too, if we don't do addToCompetitors quickly after calling clear()
Hide
Permalink
Peter Ledbrook added a comment -

I don't know whether this is a bug in Hibernate or Grails, but it appears to have a fairly straightfoward workaround: flush the session after clearing the collection:

A sample = A.get(1)
sample.competitors.clear()
sample.save(flush: true)
		
sample.addToCompetitors(A.get(2))
sample.addToCompetitors(A.get(3))
Show
Peter Ledbrook added a comment - I don't know whether this is a bug in Hibernate or Grails, but it appears to have a fairly straightfoward workaround: flush the session after clearing the collection: A sample = A.get(1) sample.competitors.clear() sample.save(flush: true ) sample.addToCompetitors(A.get(2)) sample.addToCompetitors(A.get(3))
Hide
Permalink
sudhir added a comment -

Yeah - I have been able to do workaround by flushing. But have you been able to reproduce the issue ?

Show
sudhir added a comment - Yeah - I have been able to do workaround by flushing. But have you been able to reproduce the issue ?
Hide
Permalink
Peter Ledbrook added a comment -

Yes, it's reproducible. If I have time, I'll attach an example, but if you can do that yourself I'd be grateful.

Show
Peter Ledbrook added a comment - Yes, it's reproducible. If I have time, I'll attach an example, but if you can do that yourself I'd be grateful.
Hide
Permalink
Dan Tanner added a comment -

Do the objects in the collection implement equals and hashcode based on their natural key? I've seen issues like this in the past where the equality of the Set objects using the id attribute wasn't clear to hibernate, so weird things like this happened. http://stackoverflow.com/questions/1638723/equals-and-hashcode-in-hibernate
Also, assuming that in the mapping you have cascade: "all-delete-orphan" ?

Show
Dan Tanner added a comment - Do the objects in the collection implement equals and hashcode based on their natural key? I've seen issues like this in the past where the equality of the Set objects using the id attribute wasn't clear to hibernate, so weird things like this happened. http://stackoverflow.com/questions/1638723/equals-and-hashcode-in-hibernate Also, assuming that in the mapping you have cascade: "all-delete-orphan" ?

People

  • Assignee:
    Unassigned
    Reporter:
    sudhir
Vote (2)
Watch (5)

Dates

  • Created:
    Updated:
    Last Reviewed: