5 Replies Latest reply on Oct 4, 2015 6:38 AM by Anwer Alqatahan

    Hibernate 4.2.1.Final Envers store_data_at_delete issue

    Seth Katzman Newbie

      I am experiencing a problem after upgrading from Hibernate 4.1.12.Final to 4.2.1.Final.   Specifically, I am observing strange behavior while using the Envers store_data_at_delete property feature.

       

      Currently, I have three tables:

       

      1. User - Metadata about a user.

      2. Group - Metadata about a user group.

      3. UserGroupMember - ManyToOne mapping table that ties a given user to the one or more groups of which he or she may be a member.

       

      @Entity

      @Table(name = "enduser")

      @Audited

      public class User {

          @Id

          @Column(name = "id", nullable = false)

          @Type(type = CUSTOM_UUID_TYPE)

          private UUID id;

       

          // other fields and metadata...

      }

       

      @Table(name = "user_group")

      @Entity

      @Audited

      public class UserGroup {

          @Id

          @Column(name = "id", nullable = false)

          @Type(type = CUSTOM_UUID_TYPE)

          private UUID id;

       

          // other fields and metadata...

      }

       

      @Table(name = "user_group_member")

      @Entity

      @DbConstraint(constraintName = "uniqueidx_user_group_member", errorCode = UNIQUEIDX_USER_GROUP_MEMBER)

      @Audited

      public class UserGroupMember {

          @Id

          @Column(name = "id", nullable = false)

          @Type(type = CUSTOM_UUID_TYPE)

          private UUID id;

       

          @ManyToOne(targetEntity = UserGroup.class, fetch = FetchType.EAGER)

          @JoinColumn(name = "user_group_id")

          @NotNull

          @DbConstraint(constraintName = "fk_user_group_member_user_group_id", errorCode = FK_USER_GROUP_MEMBER_USER_GROUP_ID)

          private UserGroup userGroup;

       

          @ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)

          @JoinColumn(name = "user_id")

          @NotNull

          @DbConstraint(constraintName = "fk_user_group_member_user_id", errorCode = FK_USER_GROUP_MEMBER_USER_ID)

          private User user;

       

          // ...

      }

       

      Currently, there is a on delete cascade constraint on the user group table for the user group member table.  Deleting a user group deletes all user associations for that user group.  

       

      All three tables are setup for Envers auditing.  'org.hibernate.envers.store_data_at_delete' property is set to true to enabled auditing on entity deletion.  This did not cause errors when using Hibernate 4.1.12.Final, but no audit row entry was present on delete for this entity.   That being said, I also noticed in the 4.2.1.Final change log, issue HHH-8087 (https://hibernate.atlassian.net/browse/HHH-8087) which may relate to what observed in 4.1.12.Final.

       

      After upgrading to 4.2.1.Final, when I delete a user group that has a user associated with it, a new error is occurring on revision type 2 - DELETE for the UserGroupMember_AUD table.  Specifically, the deleted UserGroupMember id is being inserted as null in the audit table, but the audit table has a not null constraint causing a constraint violation.    Shouldn't the deleted audit row entry have the id present for the entity that is being deleted?

       

      The following details the PostgreSQL user_group_member and user_group_member_aud tables (revision number, user group id, user id, revision type, and id - the user group member id):

       

      Table "user_group_member"

          Column     | Type | Modifiers

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

      id            | uuid | not null

      user_group_id | uuid | not null

      user_id       | uuid | not null

      Indexes:

          "pk_user_group_member" PRIMARY KEY, btree (id)

          "uniqueidx_user_group_member" UNIQUE CONSTRAINT, btree (user_group_id, user_id)

      Foreign-key constraints:

          "fk_user_group_member_user_group_id" FOREIGN KEY (user_group_id) REFERENCES user_group(id) ON DELETE CASCADE

          "fk_user_group_member_user_id" FOREIGN KEY (user_id) REFERENCES enduser(id) ON DELETE CASCADE

       

      Table "user_group_member_aud"

          Column     |   Type   | Modifiers

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

      rev           | integer  | not null

      user_group_id | uuid     | not null

      user_id       | uuid     | not null

      revtype       | smallint |

      id            | uuid     | not null

      Indexes:

          "user_group_member_aud_pkey" PRIMARY KEY, btree (id, rev)

       

      Here is an except from the Hibernate logger (with show sql and pretty format enabled), note how it populates the user group id/user id foreign key IDs, but not the user group member id for the deleted user group member row.

       

       

      Hibernate Log

      *** Attempting to delete user group [b15f1cee-1eea-4a51-a9df-4fb90f8eb95c].

      Hibernate:

          select

              count(*) as col_0_0_

          from

              user_group usergroup0_

          where

              usergroup0_.id=?

              and 1=1

      Hibernate:

          select

              usergroup0_.id as id1_12_0_,

              usergroup0_.ldap_group as ldap3_12_0_,

              usergroup0_.name as name2_12_0_

          from

              user_group usergroup0_

          where

              usergroup0_.id=?

      Hibernate:

          select

              members0_.user_group_id as user3_12_1_,

              members0_.user_id as user2_14_1_,

              user1_.id as id1_2_0_,

              user1_.active as active2_2_0_,

              user1_.email as email3_2_0_,

              user1_.external_name as external4_2_0_,

              user1_.first_name as first5_2_0_,

              user1_.job_title as job6_2_0_,

              user1_.last_name as last7_2_0_,

              user1_.location as location8_2_0_,

              user1_.name as name9_2_0_,

              user1_.password as passwor10_2_0_,

              user1_.phone as phone11_2_0_

          from

              user_group_member members0_

          inner join

              enduser user1_

                  on members0_.user_id=user1_.id

          where

              members0_.user_group_id=?

      Hibernate:

          delete

          from

              user_group_member

          where

              user_group_id=?

      Hibernate:

          delete

          from

              user_group

          where

              id=?

      Hibernate:

          select

              nextval ('hibernate_sequence')

      Hibernate:

          insert

          into

              REVINFO

              (timestamp, username, id)

          values

              (?, ?, ?)

      Hibernate:

          insert

          into

              user_group_member_AUD

              (REVTYPE, REV, user_group_id, user_id)

          values

              (?, ?, ?, ?)

      2013-06-14 09:57:10,430 WARN [SqlExceptionHelper] - SQL Error: 0, SQLState: 23502

      2013-06-14 09:57:10,430 ERROR [SqlExceptionHelper] - Batch entry 0 insert into user_group_member_AUD (REVTYPE, REV, user_group_id, user_id) values ('2', '4900', 'b15f1cee-1eea-4a51-a9df-4fb90f8eb95c', '361d0717-7d8a-4af9-9b17-871190052d1c') was aborted.  Call getNextException to see the cause.

      2013-06-14 09:57:10,431 WARN [SqlExceptionHelper] - SQL Error: 0, SQLState: 23502

      2013-06-14 09:57:10,431 ERROR [SqlExceptionHelper] - ERROR: null value in column "id" violates not-null constraint

        Detail: Failing row contains (4900, b15f1cee-1eea-4a51-a9df-4fb90f8eb95c, 361d0717-7d8a-4af9-9b17-871190052d1c, 2, null).

      2013-06-14 09:57:10,433 ERROR [BatchingBatch] - HHH000315: Exception executing batch [could not perform addBatch]

      2013-06-14 09:57:10,442 INFO [DbConstraintNameRetriever] - retrieved constraintname:null from ex:org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint

        Detail: Failing row contains (4900, b15f1cee-1eea-4a51-a9df-4fb90f8eb95c, 361d0717-7d8a-4af9-9b17-871190052d1c, 2, null).

      2013-06-14 09:57:10,443 WARN [DbConstraintExceptionConverter] - could not retrieve constraint name from sql exception '{}'

      org.springframework.dao.DataIntegrityViolationException: could not perform addBatch; SQL [insert into user_group_member_AUD (REVTYPE, REV, user_group_id, user_id) values (?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not perform addBatch

      ...

       

       

      I understand why the error is occurring (due to the insert of the null value and the DB constraint).  However, I am unclear as to why the Envers insert statement is not populating the id field here.   This may be a bug related to the behavior present in HH-8087.  I am seeking assistance as to why this may be occurring (bug or otherwise).

       

      - Seth