Bug since v1.1.2: cannot fetch a valid JSON from MySQL

[sp00m]

Originally posted against Spring Boot: spring-projects/spring-boot#32485

• Java 17
• Spring Boot 2.7.4
• MySQL 5.7
• MariaDB connector 1.1.2

MySQL:

CREATE TABLE `some_table`
(
    `id`   BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `json` JSON DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

INSERT INTO some_table (id, json) VALUE (1, '{}');

Mapping:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table("some_table")
public class SomeTable {

    @Id
    @Column("id")
    private Long id;

    @Column("json")
    private String json;

}

When fetching where some_table.id = 1, the returned SomeTable#json is expected to be "{}" but it actually is "123,125". Not sure where that cryptic 123,125 comes from. According to https://stackoverflow.com/a/21343591/1225328, "123 and 125 is ascii for { and }", but not sure how this helps.

According to wilkinsona:

This appears to be a regression in R2DBC Borca-SR2. Things work fine with SR1. More specifically, the problem appears to be in 1.1.2 of the MariaDB driver. The problem does not occur with 1.1.1-rc, the version included in Borca-SR1.

Replicated in https://github.com/sp00m/spring-boot-2.7.4-r2dbc-json-issue, see https://github.com/sp00m/spring-boot-2.7.4-r2dbc-json-issue/blob/master/src/test/java/base/SomeTableRepositoryTest.java#L42 which passes with Spring Boot v2.7.3, but fails with v2.7.4:

expected value: SomeTable(id=1, json={}); actual value: SomeTable(id=1, json=123,125)

[sp00m]

I've found a workaround in the meantime thanks to Spring Data's R2dbcCustomConversions:

Mapping:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table("some_table")
public class SomeTable {

    @Id
    @Column("id")
    private Long id;

    @Column("json")
    private JsonNode json; // Jackson's

}

Converter reading from byte[] to JsonNode:

@ReadingConverter
public class JsonNodeReadingConverter implements Converter<byte[], JsonNode> {

    @Override
    public JsonNode convert(byte[] source) {
        return OBJECT_MAPPER.readTree(source);
    }

}

Converter writing from JsonNode to String:

@WritingConverter
public class JsonNodeWritingConverter implements Converter<JsonNode, String> {

    @Override
    public String convert(JsonNode source) {
        return source.toString();
    }

}

FYI, writing from JsonNode to String because to byte[] fails with:

Cannot create a JSON value from a string with CHARACTER SET 'binary'

See branch workaround on https://github.com/sp00m/spring-boot-2.7.4-r2dbc-json-issue/compare/workaround.