3 Replies Latest reply on Nov 8, 2010 12:40 PM by Adam Evans

    Strange issue on @ManyToOne

    Adam Evans Newbie

      I am using Hibernate 3.5.6-Final and prototyping envers for a project and have some strange behaviour (or what looks like strange behaviour based on my understanding).

       

      I have an jpa entity Activity

       

       

      {code}

      @Entity

      @Table(name = "activity")

      @Audited

      public class Activity {

        ...

        @JoinColumn(name = "location_id", referencedColumnName = "id", nullable = false)

        @ManyToOne(optional = false)

        private Location location

        ...

      }

      {code}

       

      And also class Location

       

       

      {code}

      @Entity

      @Table(name = "location")

      @Audited

      public class Location {


        ...

        @OneToMany(cascade = CascadeType.ALL, mappedBy = "location")

        private List<Activity> activityList = new ArrayList<Activity>();


        ...

      }

      {code}

       

      I have some tests setup

       

      1. I insert a sample      row of data using sql into the Activity and Location table.

      2. I assert that      number of revisions for activity = 0 which passes

      3. I make a update to      a activity and persist it using jpa

      4. I then get the      list of revisions and attempt to retrieve the first revision from      the list

      5. I get a EntityNotFoundException on the location entity

       

      Looking through the query logs it retrieves a activity with _revision 3

       

       

      {code:sql}

      select

      activity_v0_.id as id38_,

      activity_v0_._revision as column2_38_,

      _rev_type as column3_38_,

      activity_v0_.accreditation as accredit4_38_,

      activity_v0_.activity_type as activity5_38_,

      activity_v0_.advance_reg_required as advance6_38_,

      activity_v0_.contact_email as contact8_38_,

      activity_v0_.contact_name as contact9_38_,

      activity_v0_.contact_phoneas contact10_38_,

      activity_v0_.cost_information as cost11_38_,

      activity_v0_.created as created38_,

      activity_v0_.description as descrip13_38_,

      activity_v0_.difficulty as difficulty38_,

      activity_v0_.external_id as external15_38_,

      activity_v0_.factsheet_filename as factsheet16_38_,

      activity_v0_.factsheet_url as factsheet17_38_,

      activity_v0_.family_friendly as family18_38_,

      activity_v0_.is_free as is19_38_,

      activity_v0_.meeting_point as meeting20_38_,

      activity_v0_.prevent_xml_update as prevent21_38_,

      activity_v0_.priority as priority38_,

      activity_v0_.reg_details as reg23_38_,

      activity_v0_.short_description as short24_38_,

      activity_v0_.skill_level as skill25_38_,

      activity_v0_.slug as slug38_,

      activity_v0_.special_requirements as special27_38_,

      activity_v0_.status as status38_,

      activity_v0_.tagline as tagline38_,

      activity_v0_.title as title38_,

      activity_v0_.updated as updated38_,

      activity_v0_.url as url38_,

      activity_v0_.walk_distance as walk33_38_,

      activity_v0_.location_id as location34_38_,

      activity_v0_.organiser_partner_id as organiser35_38_,

      activity_v0_.owner_partner_id as owner36_38_

        from

      activity_versions activity_v0_

        where

      _rev_type<>1

        and

      _revision=(

        select max(_revision) from activity_versions activity_v1_

          where

        _revision<=3 and activity_v0_.id=activity_v1_.id

      )

      and activity_v0_.id=9

      {code}

       

      The below is the location query which is run but returns no results

       

       

      {code:sql}

      select

      location_v0_.id as id35_,

      location_v0_._revision as column2_35_,

      _rev_type as column3_35_,

      location_v0_.alt_name as alt4_35_,

      location_v0_.created as created35_,

      location_v0_.description as descript6_35_,

      location_v0_.external_id as external7_35_,

      location_v0_.name as name35_,

      location_v0_.prevent_xml_update as prevent9_35_,

      location_v0_.slug as slug35_,

      location_v0_.status as status35_,

      location_v0_.updated as updated35_,

      location_v0_.owner_partner_id as owner13_35_

        from

      location_versions location_v0_

        where

      _rev_type<>2 and _revision=(

        select max(_revision) from location_versions location_v1_

          where

        _revision<=3 and location_v0_.id=location_v1_.id

      )

      and location_v0_.id=5

      {code}

       

      In the location versions table I have the below:

       

       

      {code:sql}select id, _revision, _rev_type FROM location_versions where id = 5;{code}

       

       

      {code}

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

      | id | _revision | _rev_type |

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

      |  5 |         4 |         1 |

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

      {code}

       

      Looking at the above the javax.persistence.EntityNotFoundException: Unable to find Location with id 5 will be caused because the query is looking for a revision <3 the same as the activity but the location activity only has one revision entry with a value of 4.

       

      Has anyone seen this before or know what I am doing wrong? I'm assuming Envers will be looking for a Location with _revision 3 to match the Activity revision? Is there a way to prevent this?

        • 1. Re: Strange issue on @ManyToOne
          Adam Warski Master

          Hello,

           

          this is probably because you manually inserted data into the location/activity tables, without corresponding audit records in _aud tables. In general, if you have existing data, you should have a revision "1" where all the data is inserted.

           

          The reason for the error would be also more clear if you pasted whole table content (location, activity, location_aud, activity_aud).

           

          Adam

          • 2. Re: Strange issue on @ManyToOne
            Adam Evans Newbie

            Thanks for this,

             

            The problem looks to be as you said, inserting the test data manualy during build process and not populating the revision table accordingly. After inserting appropiate revision data with out test data initial prototyping tests pass as expected.

             

            Is there a way to enable envers on a existing database with no existing revision data?

            • 3. Re: Strange issue on @ManyToOne
              Adam Evans Newbie

              For anyone who has this problem in the future here's how I prepopulated the database.

               

              At first I went down the wrong route and created a stored procedure which selected all rows for each audited entity, iterate over each row and insert a entry into revinfo table and then insert a entry into the revisioned table as below. This looks to be wrong as I still had the same issue on associations. I use 'sql-maven-plugin' for builds so had it create the below stored procedure and added a step to call it after the data had imported. It felt quite messy, and it was.

               

               

              {code:sql}

              CREATE PROCEDURE `populate_activity_revision_data`()
              BEGIN
                      DECLARE _revid BIGINT;         -- Activity table stuff
                      DECLARE _id BIGINT;
                      DECLARE _owner_partner_id BIGINT;
                      DECLARE _location_id BIGINT;
                      DECLARE _project_id BIGINT;   -- not revisioned
                      DECLARE _last_updated_by_user_id BIGINT;  -- not revisioned
                      DECLARE _plug_id BIGINT;  -- not revisioned
                      DECLARE _image_id BIGINT;  -- not revisioned
                      DECLARE _title TEXT;
                      DECLARE _activity_type TEXT;
                      DECLARE _slug TEXT;
                      DECLARE _tagline TEXT;
                      DECLARE _short_description TEXT;
                      DECLARE _description TEXT;
                      DECLARE _is_free BIGINT;
                      DECLARE _family_friendly BIGINT;
                      DECLARE _cost_information TEXT;
                      DECLARE _url TEXT;
                      DECLARE _meeting_point TEXT;
                      DECLARE _advance_reg_required BIGINT;
                      DECLARE _reg_details TEXT;
                      DECLARE _special_requirements TEXT;
                      DECLARE _accreditation TEXT;
                      DECLARE _skill_level TEXT;
                      DECLARE _difficulty TEXT;
                      DECLARE _walk_distance TEXT;
                      DECLARE _factsheet_url TEXT;
                      DECLARE _factsheet_filename TEXT;
                      DECLARE _status TEXT;
                      DECLARE _contact_name TEXT;
                      DECLARE _contact_email TEXT;
                      DECLARE _contact_phone TEXT;
                      DECLARE _priority BIGINT;
                      DECLARE _created datetime;
                      DECLARE _updated datetime;
                      DECLARE _external_id TEXT;
                      DECLARE _prevent_xml_update BIGINT;

               

                      -- Loop stuff
                  DECLARE no_more_rows BOOLEAN;         DECLARE activity_cur CURSOR FOR
                      SELECT `id`, `owner_partner_id`, `location_id`, `project_id`, `last_updated_by_user_id`, `plug_id`, `image_id`, `title`, `activity_type`, `slug`, `tagline`, `short_description`, `description`, `is_free`, `family_friendly`, `cost_information`, `url`, `meeting_point`, `advance_reg_required`, `reg_details`, `special_requirements`, `accreditation`, `skill_level`, `difficulty`, `walk_distance`, `factsheet_url`, `factsheet_filename`, `status`, `contact_name`, `contact_email`, `contact_phone`, `priority`, `created`, `updated`, `external_id`, `prevent_xml_update` FROM activity;         -- Declare 'handlers' for exceptions
                      DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows=true;

               

                      set no_more_rows = false;

               

                  OPEN activity_cur;
                      _loop:WHILE(no_more_rows=false) DO

               

                          FETCH activity_cur INTO _id, _owner_partner_id, _location_id, _project_id, _last_updated_by_user_id, _plug_id, _image_id, _title, _activity_type, _slug, _tagline, _short_description, _description, _is_free, _family_friendly, _cost_information, _url, _meeting_point, _advance_reg_required, _reg_details, _special_requirements, _accreditation, _skill_level, _difficulty, _walk_distance, _factsheet_url, _factsheet_filename, _status, _contact_name, _contact_email, _contact_phone, _priority, _created, _updated, _external_id, _prevent_xml_update;

               

                         IF no_more_rows=true THEN
                                 LEAVE _loop;
                         END IF;

               

                          start transaction;
                                 INSERT INTO REVINFO(REVTSTMP) VALUES (UNIX_TIMESTAMP()*1000);
                                 SELECT LAST_INSERT_ID() INTO  _revid;
                                 INSERT INTO activity_versions (_revision, _rev_type, `id`, `owner_partner_id`, `location_id`, `title`, `activity_type`, `slug`, `tagline`, `short_description`, `description`, `is_free`, `family_friendly`, `cost_information`, `url`, `meeting_point`, `advance_reg_required`, `reg_details`, `special_requirements`, `accreditation`, `skill_level`, `difficulty`, `walk_distance`, `factsheet_url`, `factsheet_filename`, `status`, `contact_name`, `contact_email`, `contact_phone`, `priority`, `created`, `updated`, `external_id`, `prevent_xml_update`) VALUES (_revid, 0, _id, _owner_partner_id, _location_id, _title, _activity_type, _slug, _tagline, _short_description, _description, _is_free, _family_friendly, _cost_information, _url, _meeting_point, _advance_reg_required, _reg_details, _special_requirements, _accreditation, _skill_level, _difficulty, _walk_distance, _factsheet_url, _factsheet_filename, _status, _contact_name, _contact_email, _contact_phone, _priority, _created, _updated, _external_id, _prevent_xml_update);
                          commit;                      END WHILE _loop;
                  CLOSE activity_cur;

               

              END

              {code}

               

              It turned out the solution was much simpler than the above, note the 'populate-revision-data.sql' step snippet from my pom.xml:

               

               

              {code:xml}

              <execution>
                <id>create-data</id>
                <phase>process-test-resources</phase>
                <goals>
                  <goal>execute</goal>
                </goals>
                <configuration>
                  <fileset>
                    <basedir>${basedir}</basedir>
                    <includes>
                      <include>src/test/sql/test-data.sql</include>
                    </includes>
                  </fileset>
                </configuration>
              </execution>
                                         
              <execution>
                <id>create-revision-data</id>
                <phase>process-test-resources</phase>
                <goals>
                  <goal>execute</goal>
                </goals>
                <configuration>
                    <fileset>
                      <basedir>${basedir}</basedir>
                      <includes>
                        <include>src/test/sql/populate-revision-data.sql</include>
                      </includes>
                    </fileset>
                </configuration>
              </execution>

              {code}

               

              A short snippet of this file:

               

              {code:sql}

              INSERT INTO REVINFO (REV, REVTSTMP) VALUES(1, UNIX_TIMESTAMP()*1000);

               

              INSERT INTO activity_versions (_revision, _rev_type, `id`, `owner_partner_id`, `location_id`, `title`, `activity_type`, `slug`, `tagline`, `short_description`, `description`, `is_free`, `family_friendly`, `cost_information`, `url`, `meeting_point`, `advance_reg_required`, `reg_details`, `special_requirements`, `accreditation`, `skill_level`, `difficulty`, `walk_distance`, `factsheet_url`, `factsheet_filename`, `status`, `contact_name`, `contact_email`, `contact_phone`, `priority`, `created`, `updated`, `external_id`, `prevent_xml_update`)
                  SELECT 1 as _revision , 0 as _rev_type,`id`, `owner_partner_id`, `location_id`, `title`, `activity_type`, `slug`, `tagline`, `short_description`, `description`, `is_free`, `family_friendly`, `cost_information`, `url`, `meeting_point`, `advance_reg_required`, `reg_details`, `special_requirements`, `accreditation`, `skill_level`, `difficulty`, `walk_distance`, `factsheet_url`, `factsheet_filename`, `status`, `contact_name`, `contact_email`, `contact_phone`, `priority`, `created`, `updated`, `external_id`, `prevent_xml_update` FROM activity;

               

              INSERT INTO  activity_occurrence_versions (_revision, _rev_type, `id`, `activity_id`, `start`, `end`)
                  SELECT 1 as _revision,  0 as _rev_type, `id`, `activity_id`, `start`, `end` FROM  activity_occurrence;

               

              -- etc etc etc

               

              {code}

               

              Easy, all works as expected now and the same select insert can be used when enabling envers on a existing application.