Search code examples
javadatabaseh2

org.h2.jdbc.JdbcSQLSyntaxErrorException after H2 version upgrade

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.

Solution

  • 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.