Consider the following minimal example:
which leads to the following DDL created by Hibernate
Now, the problem is the primary key of the last table as Envers decided to include all columns of `testentity_strings` in its audit table primary key which can lead to unexpected problems when one of the columns is e.g. 1024 characters long.
DDL: create table TestEntity_strings_AUD (revision integer not null, TestEntity_id bigint not null, strings varchar(1024) not null, action tinyint, primary key (revision, TestEntity_id, strings))
I'm not exactly sure how a warning would be anymore helpful than the error you already get. Basically MySQL has a restriction that an index maximum size varies between 797 bytes to 3072 bytes, all based on the innodb_large_prefix setting, database character-set, and the type of database used.
I'm assuming you're likely using UTF8, innodb_large_prefix=1 with InnoDB, the strings column will consume 1024 * 3 = 3072 bytes alone. The additional columns basically cause the primary key to exceed the maximum allowed values. If you set the string's length to 1000 bytes instead, that should give you sufficient space for MySQL's primary key space constraints.
Just understand that the same error could happen without Envers. Any specially crafted primary key on an entity mapping will easily arrive at the same error message from MySQL, its just an indicator that the mapping on that database isn't supported.
Specific to Envers, it generates a separate entity-mapping for the @ElementCollection property. This entity-mapping requires that a primary key be specified in the XML mapping. The columns chosen are the only valid choices to insure a unique combination of values.
There presently isn't a workaround.
That said, I am curious if we could reuse the SETORDINAL logic here. If so, the mapping would need to be changed:
Then we'd use the ordinal-value rather than the String value for each element in the Set as part of the primary key instead, the same already works if the @ElementCollection was a Set and the element type was an @Embeddable class.
I'll look to see if extending that feature for basic-types would work.
Using SETORDINAL for this case would also mean that the @ElementCollection's change history would grow faster. This is because adding/removing entries would shift the positions of the remaining elements and any shifted element would need to be audited to record the new ordinal position in the set rather than comparing based on string-value. Enabling this feature for this use case would also break backward compatibility.
In taking another look at this, I believe this is a workaround, but it involves a small model change.
Change the @ElementCollection mapping as follows. We change the container type from List to Set to force Envers to use the SETORDINAL position functionality that uses the position in the Set to determine a pseudo primary-key for the collection table. But Envers will only use the SETORDINAL functionality if the element type of the collection is an Embeddable, so we introduce a new type called StringWrapper which is nothing more than an @Embeddable that wraps the String.
Add the StringWrapper embeddable class.
As a supplement to these changes, I'd look to perhaps introduce some helper methods on your entity which exposes the collection. Perhaps here, you abstract away the notion of the StringWrapper entirely and getStrings exposes an immutable collection, forcing all collection changes through the helper methods
The nice part about this solution is that the only table changes which get introduced are:
A new field is added to the audit-collection table
The primary key of the audit-collection table no longer uses the strings value but replaces it with the SETORDINAL field instead, avoiding the maximum primary key length problem entirely.
Would this work for you?
I honestly believe for the sake of backward compatibility, this is the best alternative to work around your specific dialect's concerns with the primary-key length issue without having to change expected behavior.
, could I get an update from you on my comments above?