PK Index on snapshot_event_entry exceeds max length on MSSQL

Axon Framework
1

Hey everybody,

we started using Axon Framework using a JPA eventstore on MSSQL and we are getting this warning when creating the schema:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_snapshot_6880CBDB4BF99B99' has maximum length of 1028 bytes.

We are creating the schema with Flyway but we are using the specs that we got from the Hibernate generated artifacts:

create table snapshot_event_entry
(
    aggregate_identifier nvarchar(255)  not null,
    sequence_number      bigint         not null,
    type                 nvarchar(255)  not null,
    event_identifier     nvarchar(255)  not null,
    meta_data            varbinary(MAX),
    payload              varbinary(MAX) not null,
    payload_revision     nvarchar(255),
    payload_type         nvarchar(255)  not null,
    time_stamp           nvarchar(255)  not null,
    primary key (aggregate_identifier, sequence_number, type)
);

I guess this is an issue only specific to MSSQL, but how can we overcome it?

Cheers,
Stefan

2

Hi Stefan,

the problem is that Hibernate will generate fields with 255 length by default. There is a key (primary, in this case) which combines a few fields. Both the aggregate_identifier and the type can most likely be a lot shorter than currently defined. Aggregate identifiers are often UUIDs, and thus have a fixed length. The type is the simple qualified classname of the Aggregate, so that’s also very likely to be no longer than 100 characters.

My recommendation would be to choose shorter lengths for the fields.

Alternatively, you can also add an auto-generated (sequential, e.g.) identifier field, and just have a unique key constraint on the aggregate_identifier, sequence_numer, and type fields.

2 Likes
3

Hi @allardbz, thanks a lot, I think we will go for just shortening the fields.