Envers generates primary key spanning all columns when generating DDL for @ElementCollection (without primary key)

Description

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.

Environment

None

Activity

Show:
Johannes Geiger
December 20, 2016, 11:20 AM

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))

Chris Cranford
December 21, 2016, 6:39 AM
Edited

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.

Chris Cranford
December 21, 2016, 6:54 AM

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.

Chris Cranford
December 21, 2016, 9:42 PM
Edited

In taking another look at this, I believe this is a workaround, but it involves a small model change.

  1. 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.

  2. 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:

  1. A new field is added to the audit-collection table

  2. 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.

Chris Cranford
January 9, 2017, 3:21 PM

, could I get an update from you on my comments above?

Assignee

Chris Cranford

Reporter

Johannes Geiger

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Critical
Configure