MySQL event publication registry schema leads to SQLException

[danstooamerican]

trafficstars

Hi,

I have tried to work with TransactionalEventListener but always received the SQLException Incorrect string value: '\xA7IL\x9D\xC2\x88...' for column 'id' at row 1 whenever the handler received an event.

I am working with MySQL and initialized my schema with the following command that can be found in the documentation (slightly adapted because I use the JPA starter).

create table jpa_event_publication (
    id VARCHAR(36) not null, 
    completion_date TIMESTAMP(6), 
    event_type varchar(512), listener_id varchar(512), 
    publication_date TIMESTAMP(6), 
    serialized_event varchar(4000), 
    primary key (id)
);

After digging deeper into the stack trace, I discovered that the prepared statement looks like this:

insert into jpa_event_publication (completion_date, event_type, listener_id, publication_date, serialized_event, id) values (..., x'dfa3fa1818e447c59e214abf60964755');

The x before the actual id is not a typo and actually causes the error.

I was able to resolve the error by changing the type of the id column in the database to BINARY(16) as is suggested here.

create table jpa_event_publication (
    id BINARY(16) not null, 
    completion_date TIMESTAMP(6), 
    event_type varchar(512), listener_id varchar(512), 
    publication_date TIMESTAMP(6), 
    serialized_event varchar(4000), 
    primary key (id)
);

[nkolosnjaji]

Is this still an issue with latest version? Can you provide small testable application which reproduces this issue ?

[danstooamerican]

Yes, this is still an issue in version 0.6.0-SNAPSHOT. I created a reproducer which includes a failing test case.

I used test containers to spin up a MySQL database for the test. The schema is initialized with Flyway.

[ifr1m]

@danstooamerican Thanks for providing a way to reproduce the issue.

I checked and the issue is not fixed in the latest version at the moment (1.0.2). Update: This manifests only if the user choses JPA and wants to control the database schema.

The problem is that the JPA provider (hibernate in this case) prefers to covert the UUID id column (from JpaEventPublication) to binary instead of char.

For the jdbc variant of the event publication the conversion is handled explicitly here, but for JPA we need a different approach.

Options:

1. In your project configure the JPA provider in such a way it prefers CHAR when persisting UUID values. For a setup using SpringBoot and Hibernate this can be done with spring.jpa.properties.hibernate.type.preferred_uuid_jdbc_type=CHAR
2. If possible don't use the jpa variant for event publication if you want to control the db schema. Use jdbc variant: spring-modulith-events-jdbc/spring-modulith-starter-jdbc. This should work along JPA if JPA is used for other DB interactions.
3. (Updated) Open a PR in spring-modulith project to use @jakarta.persistence.Convert and a UUID @jakarta.persistence.Converter for id column of JpaEventPublication. (this is a bit tricky as the @Convert does not work on @Id annotated columns - can be sidestepped using an @IdClass - will experiment to see if current tests pass using @IdClass and if there are no side effects using an @IdClass); Update: Tried this option and it does not look glorious. The converter imposes an uniform conversion (e.g. from UUID to String) for all RDBMS, which might be wrong in cases where JPA-first approach is used (instead of using an UUID db type a varchar type will be used for all RDBMS - db optimizsation is lost this way for Postgres or other DBs that support UUID) .It also complicates the entity as we are trying to address a JPA implementation particularity for MySQL from the JPA abstraction layer.

[raedbh]

It's specifically related to how Hibernate maps the UUID type to the SQL type.

Setting spring.jpa.properties.hibernate.type.preferred_uuid_jdbc_type to VARCHAR should fix the issue.