Audit Table Primary Key Issues

[mcauleyj]

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?

[adamw]

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

[mcauleyj]

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!

[adamw]

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