Search code examples
javadatabaseintellij-ideah2

How to fix the issue: "Values of types "BINARY(255)" and "CHARACTER VARYING(255)" are not comparable" while autopopulating data into H2 database?

While working on the Java project using technologies as provided below:

<properties>
    <java.version>11</java.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

<dependencies>
    <!-- Spring Boot Starter Web for building web, including RESTful, applications using Spring MVC -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Spring Boot Starter Data JPA for Spring Data JPA and Hibernate -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <!--<version>2.7.18</version>-->
    </dependency>
    <!-- Spring Boot Starter Test for testing Spring Boot applications -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>3.8.1</version>
        <scope>test</scope>
    </dependency>
    <!-- H2 Database for in-memory database (for testing purposes) -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.1.214</version> <!--Values of types "BINARY(255)" and "CHARACTER VARYING(255)" are not comparable-->
        <scope>runtime</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
    <!-- Jackson for JSON processing -->
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
    </dependency>
</dependencies>
<build>
    <plugins>
        <!-- Spring Boot Maven Plugin for packaging Spring Boot applications -->
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>

        <!-- Lombok Maven Plugin for delombok process -->
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <annotationProcessorPaths>
                    <path>
                        <groupId>org.projectlombok</groupId>
                        <artifactId>lombok</artifactId>
                        <version>1.18.24</version>
                    </path>
                </annotationProcessorPaths>
            </configuration>
        </plugin>
    </plugins>
</build>

The application.properties file is:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:schema.sql
spring.sql.init.data-locations=classpath:import.sql
server.port=8081
spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql=TRACE
spring.h2.console.path=/h2-console

I'm getting runtime error, as for instance:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BINARY(255)" and "CHARACTER VARYING(255)" are not comparable; SQL statement:
alter table backlog_item_tasks add constraint FKigou96u26bqhftbug6ba1akfa foreign key (tasks_id) references task [90110-214]

...

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BINARY(255)" and "CHARACTER VARYING(255)" are not comparable; SQL statement:
alter table backlog_item_tasks add constraint FK1jlaqd1fh1t60yjn2mu179ra1 foreign key (backlog_item_id) references backlog_item [90110-214]

...

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BINARY(255)" and "CHARACTER VARYING(255)" are not comparable; SQL statement:
alter table estimation_log_entry add constraint FK4qne2wpk2c906qwlb0qt17k7s foreign key (task_id) references task [90110-214]

Tried:

I'm trying to populate h2 database w/ required data via pre-prepared SQL-scripts, as for instance, import.sql (DML script):

INSERT INTO backlog (id, name, description)
VALUES ('uuid-1', 'Backlog 1', 'Description for backlog 1');
INSERT INTO backlog_item (id, status, story, story_points, summary, type, product_id, release_id, sprint_id, backlog_id)
VALUES ('uuid-2', 'Open', 'Story 1', 5, 'Summary for story 1', 'Feature', 'uuid-product', 'uuid-release', 'uuid-sprint',
        'uuid-1');
INSERT INTO task (id, name, description, hours_remaining, volunteer, backlog_item_id)
VALUES ('uuid-3', 'Task 1', 'Description for task 1', 10, 'Volunteer 1', 'uuid-2');

and, accordingly, schema.sql (DDL script):

CREATE TABLE backlog
(
    id          VARCHAR(255) PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    description TEXT
);

CREATE TABLE backlog_item
(
    id           VARCHAR(255) PRIMARY KEY,
    status       VARCHAR(255),
    story        TEXT,
    story_points INT,
    summary      TEXT,
    type         VARCHAR(255),
    product_id   VARCHAR(255),
    release_id   VARCHAR(255),
    sprint_id    VARCHAR(255),
    backlog_id   VARCHAR(255),
    FOREIGN KEY (backlog_id) REFERENCES backlog (id)
);

CREATE TABLE task
(
    id              VARCHAR(255) PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    description     TEXT,
    hours_remaining INT,
    volunteer       VARCHAR(255),
    backlog_item_id VARCHAR(255),
    FOREIGN KEY (backlog_item_id) REFERENCES backlog_item (id)
);

configuring application.properties as:

1. Simple In-Memory Database
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

The provided above mode is displaying only in browser via http://localhost:8081/h2-console/.

and:

3. Persistent Database in File
spring.datasource.url=jdbc:h2:file:./data/mydb`

Reproducing intermediate runtime errors as the following:

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported database file version or invalid file header in file "E:/IdeaProject/PDP1/data/mydb.mv.db" [90048-214]

...

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported database file version or invalid file header in file "E:/IdeaProject/PDP1/data/mydb.mv.db"

...

Caused by: org.h2.mvstore.MVStoreException: The write format 3 is larger than the supported format 2 [2.1.214/5]

UPD:

application.properties w/ the following jdbc:h2:file option:

spring.datasource.url=jdbc:h2:file:./data/mydb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

didn't work properly, as well, because of locking of the mydb.mv.db file.

Expected:

I expected to receive the H2 database w/ the content inside of IDEA (Intellij IDEA) in the following manner:

enter image description here

Probably, questions #1, #2 and #3, partly, related to it.

Solution

  • To fix the issue:

    "Values of types "BINARY(255)" and "CHARACTER VARYING(255)" are not comparable"

    while populating data into H2 database, you should:

    1. Add missing annotations to simple data fields requiring these fields, as:

    @Column(columnDefinition = "VARCHAR(255)")

    and (if you need per requirements, fields that represent relationships, as @ManyToOne, @OneToOne, etc.):

    @JoinColumn(name = "backlog_item_id", columnDefinition = "VARCHAR(255)")

    1. Upgrade the version of H2 dependency to the latest one, as for instance:

       <dependency>
     <groupId>com.h2database</groupId>
     <artifactId>h2</artifactId>
     <version>2.2.224</version>
 </dependency>

    2. Update application.properties w/ the following jdbc:h2:file option:

    jdbc:h2:file:./data/testdb;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE