SQL syntax change with H2 version update-CodePudding

I am having the following script for H2 DB used in SpringBoot application tests:

create TABLE PARAMETER (
  ID long auto_increment,
  TYPE VARCHAR(100) not null,
  VALUE VARCHAR(100) not null,
  SORT_ORDER int not null
);

CREATE SEQUENCE PARAMETER_ID_SEQ MINVALUE 1 START WITH 1;

This script executes with previous H2 version <h2.version>1.4.196</h2.version>, but when updating to <h2.version>2.1.210</h2.version> the following error ocures and I cannot understand what the problem. Is there a new syntax with the upper version?

ERROR:

Reason: liquibase.exception.DatabaseException: Syntax error in SQL statement "create TABLE PARAMETER (\000a  ID long [*]auto_increment,\000a  TYPE VARCHAR(100) not null,\000a  VALUE VARCHAR(100) not null,\000a  SORT_ORDER int not null\000a);\000a\000aCREATE SEQUENCE PARAMETER_ID_SEQ MINVALUE 1 START WITH 1;"; expected "RAW, ARRAY, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
create TABLE PARAMETER (
  ID long auto_increment,
  TYPE VARCHAR(100) not null,
  VALUE VARCHAR(100) not null,
  SORT_ORDER int not null
);

CREATE SEQUENCE PARAMETER_ID_SEQ MINVALUE 1 START WITH 1; [42001-210] [Failed SQL: (42001) create TABLE PARAMETER (
  ID long auto_increment,
  TYPE VARCHAR(100) not null,
  VALUE VARCHAR(100) not null,
  SORT_ORDER int not null
);

CREATE SEQUENCE PARAMETER_ID_SEQ MINVALUE 1 START WITH 1;]

CodePudding user response：

There is no such data type as long in SQL, where did you find it? You need to use BIGINT. H2 accepts long too, but it depends on compatibility mode, for example, it isn't allowed in PostgreSQL compatibility mode.
AUTO_INCREMENT should also be used only in MySQL and MariaDB compatibility modes, H2 also accepts it in REGURAL and LEGACY modes, but normally you need to use GENERATED BY DEFAULT AS IDENTITY.
VALUE is a keyword in H2 and it also a reserved word in the SQL Standard (even in archaic SQL-92). You cannot use it as identfier without quotes, you need to write it as "VALUE" or "value" depending on case you want (quoted identifiers are case-sensitive by default). Actually there is a compatibility setting, you can add ;NON_KEYWORDS=VALUE to JDBC URL of H2, but it would be better to quote it in your scripts and application.