org.h2.jdbc.JdbcSQLSyntaxErrorException after H2 version upgrade

Question

I recently upgraded h2 version from 1.4.200 to 2.0.206. Some of the queries that used to work in the older version are not working properly after the upgrade.

CREATE TABLE SOMETABLE (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  SOURCE_ID VARCHAR(255) NOT NULL,
  MESSAGE VARCHAR(255) NOT NULL,
  PRIMARY KEY (`ID`)
);

CREATE TABLE IF NOT EXISTS SOMEOTHERTABLE (
    ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    CREATED_TIME TIMESTAMP NOT NULL,
    LAST_MODIFIED TIMESTAMP NOT NULL,
    HAS_FILE BOOLEAN(1) NOT NULL,
    PRIMARY KEY (ID)
);

For both these, I get similar errors

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "  CREATE TABLE SOMETABLE ( ID INT([*]11) NOT NULL AUTO_INCREMENT, SOURCE_ID VARCHAR(255) NOT NULL, MESSAGE VARCHAR(255) NOT NULL, PRIMARY KEY (`ID`) )"; expected "ARRAY, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ., )";

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "  CREATE TABLE IF NOT EXISTS SOMEOTHERTABLE ( ID VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, CREATED_TIME TIMESTAMP NOT NULL, LAST_MODIFIED TIMESTAMP NOT NULL, HAS_FILE BOOLEAN([*]1) NOT NULL, PRIMARY KEY (ID) )"; expected "ARRAY, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ., )";

It seems that in both these cases, having INT(11) and BOOLEAN(1) is the issue. Are those not allowed anymore in the new version? If so, how should I change those? Any help regarding this is appreciated.

Rhizoid

Answer 1

Why do you have such definitions? Documentation of H2 1.4.200 doesn't allow any parameters for these data types.

INT(11) is allowed only in MySQL and MariaDB compatibility modes, but the specified precision is ignored by H2. This definition is rejected in all other compatibility modes in H2 2.0, you need to use INT or INTEGER.

BOOLEAN(1) is not allowed at all, if it worked in 1.4.200, it was a bug in the parser. You need to use BOOLEAN.

AUTO_INCREMENT clause also should normally be used only in MySQL and MariaDB compatibility modes, but it works in Regular mode too. The proper clause is GENERATED BY DEFAULT AS IDENTITY and explicit NOT NULL constraint isn't required for primary key and identity columns, you can remove it. Constraints also should normally be specified after all other clauses, NOT NULL before identity options is actually accepted by H2, but this wrong order of clauses isn't documented and isn't supported.

Intertexture

Those were some pretty old code in our org and those were somehow allowed in older versions of h2. Thanks for the input! – Rhizoid

Specifically I added MODE=MYSQL to my connection string to use MySQL compatibility and get INT(11) working. – Dragonnade

This is the way. After upgrading flyway and h2, my tests are failing due to this. Just edit the connection string to add MODE=MYSQL, it will solve the issue. – Honk

Answer 2

I was facing the same issue when updating the h2 version from 1.4.200 to 2.0.206. The project is based on Spring Boot and uses Hibernate.

In my case the problem was, that i had an entity with a field referencing a table column called "VALUE".

@Column(name = "VALUE")
private BigDecimal value;

According to the docs https://h2database.com/html/advanced.html#keywords, VALUE is a reserved keyword. Changing the column reference "VALUE" to something like "VALUE1" solved the issue.

Role

Changing the column reference "VALUE" to "`VALUE`" (i.e. wrapping it with backticks) could also solve the issue. – Sorensen

Thanks, this works for me. It's so tricky. – Sinker

Answer 3

Had a similar issue with Hibernate and Spring Boot and as @Plumstone mentioned it was due to a reserved keyword (in my case constraint). Adding backticks to the name, solved the issue:

@Column(name = "`constraint`")
private String constraint;

Boaster

Answer 4

When I tried upgrading H2 from 1.4.200 to 2.2.224 I had to change things like int(5) to number(5) and since I'm using Oracle Syntax for some seed data, I had to add MODE=Oracle;DEFAULT_NULL_ORDERING=HIGH

Another Oracle specific feature that I needed to set was related to how Oracle deals with Pageable requests (uses limit in the SQL query to bring back the specified number of rows)

To make lmit work with H2,

import org.h2.engine.Mode;

    @BeforeEach
    void initializeInMemoryDb() {
        final Mode mode = Mode.getInstance("ORACLE");
        /*allow Pageable requests to use Oracle syntax*/
        mode.limit = true;
        
    }

Proudlove