micronaut-data: Data JDBC don't handle UUID stored as BINARY(16) in MySQL

Task List

Steps to reproduce provided
Stacktrace (if present) provided
Example that reproduces the problem uploaded to Github
Full description of the issue provided (see below)

Hello!

I’m trying to migrate my application from JPA to JDBC in order to have more control of the queries and avoid the 1+N from the beginning. I found a problem regarding UUID column (not as id, just a regular column) that micronaut is unable to query this column (UUID -> VARBINARY(16)) and to convert back the response from VARBINARY(16) to UUID. For Micronaut Data JPA this works just fine.

This is the table I’m using to store some rows with UUID and to query them on a MySQL 8 database:

CREATE TABLE establishment (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid BINARY(16) NOT NULL,
  name VARCHAR(50) NOT NULL
);

This is the entity:

@Getter
@Setter
@MappedEntity
public class Establishment {

    @Id
    @GeneratedValue
    private Integer id;

    private UUID uuid;
    private String name;
}

The repository:

@JdbcRepository(dialect = Dialect.MYSQL)
public interface EstablishmentRepository extends PageableRepository<Establishment, Integer> {

    Optional<Establishment> findByUuid(UUID uuid);
}

Ok, now the use cases:

1 - If I run establishmentRepository.findByUuid(/*some valid uuid*/), nothing is found in the query, even tho the row exists in the database with that uuid. Micronaut Data on TRACE log even shows the UUID on the binding list, but the query returns nothing.

2 - If I run establishmentRepository.findById(1), then the row is found but it throws an error when trying to convert from binary to uuid, something like this:

Cannot convert type [class java.lang.String] with value [�;$jNƑZ��] to target type: UUID uuid. Consider defining a TypeConverter bean to handle this case.

I took the suggestion and tried to create a few TypeConverters, but without success.

MySQL suggests the use of BINARY(16) to store UUIDs, and it works just fine in JPA. Is this feature missing in JDBC? I can help with some PR, just need some guidance on where to start looking to fix this UUID conversion.

Environment Information

Operating System: Linux
Micronaut Version: 2.2.0
JDK Version: 11

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 23 (12 by maintainers)

Most upvoted comments

In the next version, this can be implemented in a better way using AttributeConverter see https://github.com/micronaut-projects/micronaut-data/blob/ab9fe12c0206f35f2347f2f5cb674e59a8b934ee/data-jdbc/src/test/java/io/micronaut/data/jdbc/mysql/MySqlUuidEntity.java

dstepanov on Sep 7, 2021

@athkalia Try something like this:

public class DataInitializer2 implements ApplicationEventListener<ApplicationStartupEvent> {
    @Override
    public void onApplicationEvent(ApplicationStartupEvent event) {
        ConversionService.SHARED.addConverter(...)
    }
}

dstepanov on May 11, 2021

This time for real. I found a way to beat the system. Create your own type, then the TypeConverter will “see” it and fire off.

@TypeDef(type = DataType.BYTE_ARRAY)
data class OptimizedUUID (val uuid: UUID)

@Repository
@JdbcRepository(dialect = Dialect.MYSQL)
interface UserAccountRepository : CrudRepository<UserAccount, OptimizedUUID> {
}

@Introspected
@MappedEntity
data class UserAccount (
    @field:Id
    var id: OptimizedUUID,
    var siteId: Int,
    var userId: OptimizedUUID,
    var username: String
)

@Factory
open class UUIDConverters {

    @Singleton
    open fun uuidByteArrayTypeConverter(): TypeConverter<OptimizedUUID, ByteArray> {
        return TypeConverter { uuidId, _, _ -> Optional.of(ToBytesTransformer.transform(uuidId.uuid)) }
    }

    @Singleton
    open fun byteArrayUuidTypeConverter(): TypeConverter<ByteArray, OptimizedUUID> {
        return TypeConverter { byteArrayId, _, _ -> Optional.of(OptimizedUUID(ToBytesTransformer.parse(byteArrayId))) }
    }
}

TRACE i.m.d.query - Binding parameter at position 1 to value OptimizedUUID(uuid=874b8832-1b8e-11ea-978f-2e728ce88125) with data type: BYTE_ARRAY

curl -X GET "http://localhost:8080/useraccount/874b8832-1b8e-11ea-978f-2e728ce88125"
{"id":"874b8832-1b8e-11ea-978f-2e728ce88125","siteId":10000,"userId":"7e76e3e9-a525-4225-9db1-c764cbd5e50f","username":"sara.smith"}

There you have it. Both of my UUIDs being converted. And the first one gets converted on the way in and the way out.

I wish the TypeConverter did not get overwritten so this would not be necessary. Maybe in a few weeks with the next release…

charlie-harvey on Feb 10, 2021