Exception: SQL Error: 3854, SQLState: HY000 ERROR 18360 — [nio-8080-exec-3] o.h.e.j.s.SqlExceptionHelper : Cannot convert string ‘\xAC\xED\x00\x05~r…’ from binary to utf8mb4

The error you are encountering suggests that there is a mismatch in the character encoding or the type of the column in your database. Specifically, the database is trying to convert a binary string (\xAC\xED\x00\x05~r...) into utf8mb4, but it fails because the data is binary and not text. For example You have Enum and the column in database store the string and you are passing Enum then you will get this error.

This typically occurs when:

1. A binary or serialized object is being stored in or retrieved from a text-based column like VARCHAR or TEXT.
2. The database column encoding is not configured correctly for the data being stored.

Key Cause

The data you’re attempting to save or fetch is likely serialized (e.g., Java serialized objects, binary data) and is being stored in a column intended for textual data. When Hibernate or your application attempts to retrieve this data, it tries to decode it as utf8mb4, leading to the error.

Solution

1. Verify Database Schema

• Ensure the column storing the binary data is of a binary type, such as BLOB or VARBINARY.
• If the column is a text type (e.g., VARCHAR, TEXT), change its type to BLOB if it contains serialized or binary data.

ALTER TABLE your_table MODIFY column_name BLOB;

2. Update Hibernate Mapping

• If you are using Hibernate, ensure the column is mapped correctly in your entity class

@Column(name = "column_name", columnDefinition = "BLOB")
private byte[] binaryData;

3. Check Character Encoding

• If the data is supposed to be text, ensure it is properly encoded in UTF-8 before being stored in the database. Check the database connection URL for proper encoding settings:

spring.datasource.url=jdbc:mysql://localhost:3306/yourdb?characterEncoding=utf8mb4&useUnicode=true

4. Handle Serialization

• If you are storing serialized objects, ensure that they are deserialized correctly. Use Java serialization utilities or libraries like Jackson if working with JSON.

5. Inspect Data

• Inspect the data being stored. Run a query to identify if invalid or corrupted data is already in the column:

SELECT column_name FROM your_table WHERE column_name LIKE '%\xAC\xED%';