JsonType is not working with MariaDB

[antonsalim91]

Hi, I have an issue when trying to do update query using JPA query on a entity model that has field typed Map<String, Object>.

I followed the approach from https://vladmihalcea.com/jpa-query-setparameter-hibernate/, but no luck.

Database: MariaDB
Hibernate bersion: 6.1.7.Final
Spring Boot version: 3.0.5

This is sample entity model:

@Entity
class Device(
    @Column(nullable = false, updatable = false, unique = true)
    val macAddr: String,

    @Type(JsonType::class)
    @Column(updatable = true)
    var utilization: Map<String, Any>? = null
)

This is the dto model:

data class Dto(
   val macAddr: String
   val utilization: Map<String, Any>?
)

This is the code for update query:

entityManager.createQuery("UPDATE Device d " +
                    "SET d.utilization = :utilization " +
                    "WHERE d.macAddr = :macAddr")
                .setParameter("macAddr", dto.macAddr)
                .unwrap(org.hibernate.query.Query::class.java)
                .setParameter("utilization", dto.utilization, JsonType.INSTANCE)
                .executeUpdate()

When i run the code, I got the following error:

2023-04-24T14:41:25.410+08:00 TRACE 50564 --- [nio-8080-exec-2] o.h.o.j.bind                             : binding parameter [1] as [VARBINARY] - [[(cpu, {total_cpu_usage_percentage=0.096, temp_degrees_celcius=38, current_frequency=1800.00Mhz}), (memory, {available=1.79GB, used=106.59MB, percentage=0.067}), (disk, [{device=/dev/mmcblk0p6, used=2.93GB, free=2.69GB, percentage=0.522}])]]
2023-04-24T14:41:26.425+08:00 TRACE 50564 --- [nio-8080-exec-2] o.h.o.j.bind                             : binding parameter [2] as [VARCHAR] - [aa:bb:cc:dd:ee:ff]
2023-04-24T14:41:26.427+08:00  WARN 50564 --- [nio-8080-exec-2] o.m.j.m.s.ErrorPacket                    : Error: 1366-22007: Incorrect string value: '\xAC\xED\x00\x05sr...' for column `rtls`.`transceiver`.`utilization` at row 1
Caused by: java.sql.SQLSyntaxErrorException: (conn=4493) Incorrect string value: '\xAC\xED\x00\x05sr...' for column `testdb`.`device`.`utilization` at row 1
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:282) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:370) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:840) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:779) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:698) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:641) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:95) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:334) ~[mariadb-java-client-3.0.10.jar:?]
    at org.mariadb.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:311) ~[mariadb-java-client-3.0.10.jar:?]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-5.0.1.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:?]
    at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:84) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
    at org.hibernate.query.sqm.internal.SimpleUpdateQueryPlan.executeUpdate(SimpleUpdateQueryPlan.java:93) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
    at org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(QuerySqmImpl.java:728) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
    at org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(QuerySqmImpl.java:698) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
    ... 85 more

What could i have done wrong here?

[vladmihalcea]

The JsonType is only tested with MySQL, so it's not guaranteed to work on MariaDB.

If you want to add support for MariaDB, send me a Pull Request with the fix.

[vladmihalcea]

Closed as there is no support for MariaDB.