Caused by: org.h2.jdbc.JdbcSQLDataException: Hexadecimal string contains non-hex character

Question

I'm trying to write tests using in-memory DB. I wrote an sql to clean and store data to DB. But I have an exception:

Caused by: org.h2.jdbc.JdbcSQLDataException: Hexadecimal string contains non-hex character: "e7485042-b46b-11e9-986a-b74e614de0b0"; SQL statement:
insert into users (user_id, name, created_on, modified_on) values ('e7485042-b46b-11e9-986a-b74e614de0b0', 'Ann', null, null) -- ('e7485042-b46b-11e9-986a-b74e614de0b0', 'Ann', NULL, NULL) [90004-199]

My sql:

insert into users (user_id, name, created_on, modified_on) values ('e7485042-b46b-11e9-986a-b74e614de0b0', 'Ann', null, null);

insert into product(product_id, name, created_on, modified_on) VALUES ('f3a775de-b46b-11e9-95e4-af440b6044e6', 'product1', '2019-08-01 17:51:51.000000', '2019-08-01 17:51:51.000000');

insert into products_users(user_id, product_id) VALUES ('e7485042-b46b-11e9-986a-b74e614de0b0', 'f3a775de-b46b-11e9-95e4-af440b6044e6');

My application.properties:

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Answer 1

Using spring.datasource.url=jdbc:h2:mem:testdb;MODE=MYSQL fixed it for me.

Or adding an annotation @Type to the UUID field should fix the issue:

@Id
@Type(type="uuid-char")
private UUID user_id;

Answer 2

The actual cause of this problem is the mapping between your object and the generated create table statement by hibernate (ddl-auto:create) used to create your h2 database schema.

If you enable the output of the those ddl statements using:

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=TRACE

you will most likely see that your UUID class has been mapped to a binary column in your database.

Hibernate: 
    create table <your_table> (
        id bigint generated by default as identity,
        ...,
        <your_object> binary(255),
        ...
        primary key (id)
    )

This means that your uuid-string is mapped onto a binary column and thus contains illegal characters. You need a varchar(<uuid-length>) column to store a uuid. There are several solution strategies, one of them is defining a type, see this StackOverflow answer. You can read on binary columns on the official MySQL reference site.