Grails
  1. Grails
  2. GRAILS-8046

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

    Details

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

        Hide
        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
        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
        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
        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
        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
        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
        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
          • Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

            • Created:
              Updated:
              Last Reviewed:

              Development