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.

Answer 1

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.