10 Replies Latest reply on Jan 11, 2012 9:28 AM by Adam Warski

    @ManyToMany audit includes removed entities

    Karl Walsh Newbie

      Edit: Just wanted to bump this, would love to get clarification of any mistakes I might have made or confirmation that this is a bug?

       

      Essentially, I have two entities A and B, A has a many_to_many relationship with B, but it's not a bi-directional relationship. This gives me an A_B_AUD table for the join.

       

      If I add some B's to A, say two B's

       

      A -> B0, B1

       

      This gives me two entries in the A_B_AUD table for the same revision number (call this revision 1)

       

      If I update A and remove one of the B's

       

      A -> B0

       

      This gives me a new entry in the A_B_AUD table indicating that at this revision A had one B (call this revision 2)

       

      If I load the latest revision of A (revision 2) it contains both B's, despite the fact that at revision 2 I removed B1.

       

      Any Ideas?

       

      Original Post:

       

      Entities:

       

      @Entity

      @Audited

      public class Template {

          @Id

          Long id;


          @ManyToMany(cascade = CascadeType.ALL)

          @JoinTable(name = "template_section")

          List<Section> sections;


          ...

      }


      @Entity

      @Audited

      public class Section {

          @Id

          Long id;


          ...

      }

       

      Steps Taken

      -----

      1) Create a few sections


      Section A (rev 1)

      Section B (rev 2)

      Section C (rev 3)


      2) Create a template with these sections


      Template A (rev 4)

           Section A, Section B, SectionC (join table rev 4)


      3) Update the section and remove a section


      Template A (rev 5)

           Section A, Section B (join table rev 5)


      When I load revision 5 of the template I would expect a template with only two sections, instead it returns a template with all three. I think the key is in the generate SQL which specifies a revision <= 5.


      The template_sections_aud join table contains:

       

      REVSTATEMENTSECTIONS
      REVTYPE

      5

      110
      5120
      4110
      4120
      4130

       

      The generated SQL query to pull back the sections returns (column names changed to improve readability):

       

      TEMPLATE_SECTION_REVTEMPLATE_IDTEMPLATE_SECTION_IDSECTION_IDSECTION_REV
      51111
      51222
      41333

       

      Have I setup my relationship incorrectly? Why does the revisioned entity have all three sections?

        • 1. Re: @ManyToMany audit includes removed entities
          Karl Walsh Newbie

          Additionally, please see the generated SQL (Names and structure changed for readability). Please note the highlighted where clause dtrs_templ3_.rev<=51.If this read .rev=51 rather than .rev<=51 the query would do exactly what I would expect, returning only the sections that belong to revision 51 of the template.

           

          Hope this makes sense.

           

           

          select 
                    tsa.rev as template_section_rev, 
                    tsa.template as template_id, 
                    tsa.sections as template_section_id, 
                    sa.section_id as section_id, 
                    sa.rev as section_rev 
          from 
                    template_section_aud tsa, 
                    section_aud sa 
          where 
                    template_section_aud.sections=sa.section_id 
          and 
                    template_section_aud.template=1 
          and 
                    sa.rev=(
                              select 
                                        max(dts.rev) 
                              from 
                                        section_aud dts 
                              where 
                                        dts.rev<=51 
                              and 
                                        sa.section_id=dts.section_id
                    ) 
                    and 
                              tsa.rev=(
                                        select
                                                  max(templ.rev) 
                                        from 
                                                  dtrs_template_section_aud templ 
                                        where 
                                                  templ.rev<=51 
                                        and 
                                                  tsa.template=templ.template 
                                        and 
                                                  tsa.sections=templ.sections
                              ) 
                    and 
                              tsa.revtype<>2 
                    and 
                              section_aud.revtype<>2;
          
          
          • 2. Re: @ManyToMany audit includes removed entities
            Adam Warski Master

            It seems you are missing some entries with revtypes other than 0. The query is correct, as it's looking for the newest version. Do you have all of the listeners setup correctly?

             

            Adam

            • 3. Re: @ManyToMany audit includes removed entities
              Karl Walsh Newbie

              What elese would you expect to see in the A_B_AUD table? Deletes also?

               

              AFAIK the listeners are setup correctly, I took them right from the getting started guide. I'll double check again tomorrow morning.

               

              For now my problem is fixed, I changed the relationship to a bidrectional one (I essentially mimicked one of the envers manytomany test entities). The audit data generated appears to be identical as I posted above but the join correctly returns only the rows I would expect.

               

              I'll take another look at the tests and if I have a chance this week will put together a test demonstrating this behaviour.

               

              Thanks for the reply.

               

              Karl

              • 4. Re: @ManyToMany audit includes removed entities
                Karl Walsh Newbie

                Adam,

                 

                It appears I didn't fix the problem in this case, and the test-case I wrote around this was giving a false-positive.

                 

                In my test I explicitly call A.getBs().remove(B0) which worked as expected, giving a REVTYPE of 2 for that revision.

                 

                We're using spring to bind the B's to the A on form submission, and I assume spring is creating a new list of Bs each time. This means all the REVTYPEs are 0 in the audited join table.

                 

                Have you come across this before and if so is there an easy solution? At the moment I'm thinking we'll have to do our own binding in addition to springs binding so we can explicitly call list.remove(B) to get the correct REVTYPE.

                 

                Any thoughts?

                • 5. Re: @ManyToMany audit includes removed entities
                  Karl Walsh Newbie

                  Hi Adam, sorry to push this but I have hit the proverbial brick wall.

                   

                  Take the following:

                   

                  template.setSections(newArrayList(sectionA));

                   

                  And

                   

                  template.setSections(newArrayList(sectionA, sectionB));

                  template.getSections().remove(sectionB);

                   

                  Both statements have the same result, the template will only contain sectionA but Envers treats these differently, the second option will give me a REVTYPE of 2, the first option a REVTYPE of 0. Everything else appears to be identical.

                   

                  Is this something you would expect?

                   

                  Because we use spring to bind a template and some sections, we will only ever get a REVTYPE of 0 in the join table giving us false results when getting the entity at a particular revision number. I can overcome this with some additional binding in the controller, i.e looping over bound sections and adding removing them from the existing template but this is error prone and would also need to be done for all relationships, I would really like to avoid this workaround.

                   

                  Kind regards,

                   

                  Karl

                  • 6. Re: @ManyToMany audit includes removed entities
                    Adam Warski Master

                    Hmm well in the audit tables you should get both additions and deletions (so revtype 0/2). Not sure why Hibernate wouldn't detect those in the first case ... maybe you can try writing a test case (like in the Envers test suite), to try to reproduce the problem?

                     

                    Adam

                    • 7. Re: @ManyToMany audit includes removed entities
                      Karl Walsh Newbie

                      Thanks for thre reply Adam, turns out replacing saveOrUpdate(template) with merge(template) gives the correct revision entries.

                       

                      I'll get a test case together fo you after this weekend (xmas party ).

                       

                      Thanks again,

                       

                      Karl

                      • 8. Re: @ManyToMany audit includes removed entities
                        Adam Warski Master

                        That's very intersting . I'll be greatful for the test case!

                         

                        Adam

                        • 9. Re: @ManyToMany audit includes removed entities
                          Karl Walsh Newbie

                          Adam, I have finally created a Jira with a test-case for this. Turns out those jboss community updates were useful... as a reminder

                           

                          https://hibernate.onjira.com/browse/HHH-6942

                           

                          Feel free to get in touch directly by e-mail if you have any questions.