MariaDB Exception Invalid utf8mb3 character string: 'ACED00'

Question

I am writing a native query and keep getting the "Invalid utf8mb3 character string: 'ACED00'" error. Here is my query

   @Query(value = "select " +
            "(case when bh.type in :writeOff then 'WRITE_OFF' " +
            "when bh.type in :refund then 'RETURN' end) as sector," +
            "sum(bh.amount) as size " +
            "from `phedon_balance_history` bh " +
            "where(bh.type in :writeOff or bh.type in :refund) " +
            "and(bh.status = :status) " +
            "and ((:startDate is null) or bh.creation_date between :startDate and :endDate) " +
            "group by (case when bh.type in :writeOff then 'WRITE_OFF' " +
            "when bh.type in :refund then 'RETURN'end)",nativeQuery = true)
    List<CommissionProjection> findCommissionStatistic(List<BalanceType> writeOff, List<BalanceType> refund, BalanceTransactionStatus status,
                                                       LocalDateTime startDate, LocalDateTime endDate);

I have tried changing the MariaDB version also tried to change the encoding in the dialect, however nothing helps, where is the issue?

How does the generated sql look like? And can you execute it in a SQL editor? — Simon Martinelli, Jul 22 '22 at 09:18

score 1 · Answer 1 · answered Sep 23 '22 at 06:29

I encountered the same exception when I was trying to use an enum value as a parameter.

public List<A> fetchByType(SomeTypeEnum type) {
    List<A> query = namedParameterJdbcTemplate.query(
        "SELECT * FROM a WHERE some_type = :type",
        Map.of("type", type), // SqlException here
        this::mapRow
    );
    return query;
}

The simple solution was to use toString and the problem was solved:

Map.of("type", type.toString())

MariaDB Exception Invalid utf8mb3 character string: 'ACED00'

1 Answers1