CODEWITHSUNDEEP
javaspringjpajdbc
annswerg
annswerg

Reputation: 279

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

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

Upvotes: 7

Views: 14888

Answers (4)

Brad Parks
Brad Parks

Reputation: 72101

This can also be caused by having a foreign key constraint on a table, that is incorrect (ie one table has UUID as the key, and the other has varchar as the key).

example:

Imagine you have

user.id => UUID
some_table.user_id => varchar(255)

You will get this error if you have the following constraint on some_table

 constraint fk_user_id foreign key (user_id) references user,

Upvotes: 1

skubski
skubski

Reputation: 1606

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.

Upvotes: 9

kmarabet
kmarabet

Reputation: 1117

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;

Upvotes: 12

annswerg
annswerg

Reputation: 279

I resolved this problem by adding spring.jpa.hibernate.ddl-auto=none to my application.properties file

Upvotes: -5

Related Questions