3 Replies Latest reply on Apr 2, 2013 4:13 PM by Adam Warski

    Audit Table Primary Key Issues

    J McAuley Newbie

      Greetings!  I am having an issue where the audit table being automatically generated through Envers collects unnecessary columns into the primary key and I was hoping for some advice regarding how we might overcome this problem.  In particular, we have multiple data objects that include a "properties" field, which is of type Map<String, String>.  I have copied an example from our hbm2ddl logging output of the main table and audit table created in this case:

       

       

      create table TICKET_PROPERTIES (TICKET_ID integer not null, PROPERTY_VALUE varchar(255), PROPERTY_KEY varchar(255) not null, primary key (TICKET_ID, PROPERTY_KEY)) ENGINE=InnoDB

      create table TICKET_PROPERTIES_AUD (REV integer not null, TICKET_ID integer not null, PROPERTY_VALUE varchar(255) not null, PROPERTY_KEY varchar(255) not null, REVTYPE tinyint, primary key (REV, TICKET_ID, PROPERTY_VALUE, PROPERTY_KEY)) ENGINE=InnoDB

       

       

      The issue is that Envers includes the property_value in the primary key for the audit table; this causes a problem (specifically, going over the maximum key length) if we want to expand the length of the property_value column.  It is my understanding that the property_value really doesn't need to be in the primary key anyway, since the rev, data_id, and property_key together will be unique.  Is there perhaps some way of telling Envers what information should be included in the audit table's primary key?

        • 1. Re: Audit Table Primary Key Issues
          Adam Warski Master

          Hmm, well the audit table's key should be copied from the entity's key. Are the properties in any way part of the primary key of the entity?

           

          Adam

          • 2. Re: Audit Table Primary Key Issues
            J McAuley Newbie

            Hi Adam,

             

            Thanks for your prompt response.  The properties shouldn't be included in the primary key for the entity.  I checked this by forcing recreation of the main data table (for the same data type I used previously as an example) and here's the logging output:

             

            create table TICKET (ID integer not null, FIELD1 varchar(255), FIELD2 varchar(255), FIELD3 varchar(255), FIELD4 longtext, FIELD5 integer, FIELD6 varchar(255), ..., primary key (ID)) ENGINE=InnoDB

             

            I thought that the audit table utilized the primary key from the table being audited and tacked on REV so I was also surprised to see another column get pulled in for properties.  For the record, here's a snippet from our hbm.xml for mapping properties:

             

            <map name='properties' table='TICKET_PROPERTIES' cascade='all'>

                 <key column='TICKET_ID'/>

                 <map-key column='PROPERTY_KEY' type='java.lang.String'/>

                 <element column='PROPERTY_VALUE' type='java.lang.String'/>

            </map>

             

            For the record we are using Hibernate and Envers 4.1.6.  Any input is appreciated, thanks!

             

             

             

            • 3. Re: Audit Table Primary Key Issues
              Adam Warski Master

              Can you maybe post a full (but minimal ) example of an entity with its primary key, which causes problems?

               

              I have no idea why this could be happening.

               

              Adam