H2 auto-increment not working after update from 1.4.200 to 2.1.212-CodePudding

We are upgrading an application from spring-boot 2.4.3 to 2.7.0. Hibernate core version is 5.6.9.Final. 2.7.0 is the first one to use H2 database 2.1.212 and we are using it for tests. I made some changes based on their guide to migrate to 2.0 but I'm facing one issue for which I haven't found a solution.

In some integration tests, we are using sql scripts to insert data before running the tests in that class. For example, the scripts insert 3 items in a table, then the test inserts a 4th one and updates one of the initial 3 which already existed.

The test which does the insert fails with the following error

DataIntegrityViolationException. Error message: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON studio.exercise(id) ( /* key:1 */ 1, 'Test Exercise', FALSE, 'TEST', NULL, 1, 'TEST', TIMESTAMP '2021-01-01 16:00:13', NULL, NULL)"; 
SQL statement: insert into exercise (id, created_by, created_on, modified_by, modified_on, archived, image, organization_id, title, type) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-212]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

It complains about the fact that it can't insert item with id 1 because another item with that id already exists (inserted by the script). But in the previous H2 database version this wasn't an issue.

I think that this thread is related to mine but I can't see the understand the proposed solution AUTO_INCREMENT in H2 database doesn't work when requesting with Postman

CodePudding user response：

AUTO_INCREMENT is a feature of MySQL and its forks.

New versions of H2 have only standard identity columns (GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY). H2 still accepts AUTO_INCREMENT and some other vendor-specific clauses in some compatibility modes, but they all are converted to identity columns anyway.

GENERATED BY DEFAULT AS IDENTITY columns also allow manual insertion of value (GENERATED ALWAYS BY IDENTITY clause doesn't allow it). When you insert such value it doesn't adjust base value of a sequence generator by default, that's why you see such exceptions. This behavior is correct for standard identity columns. Normally you shouldn't provide own values for them.

CREATE TABLE TEST(
    ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    A INTEGER,
    B INTEGER);
-- Bad insertion, may create problems in the future
INSERT INTO TEST(ID, A, B) VALUES (1, 10, 11), (2, 12, 13);
-- Correct insertion
INSERT INTO TEST(A, B) VALUES (10, 11), (12, 13);
-- Alternative syntax for correct insertion
INSERT INTO TEST(ID, A, B) VALUES (DEFAULT, 10, 11), (DEFAULT, 12, 13);

Base value of identity column generator can be fixed after insertion of rows with user-provided values:

-- Bad insertion
INSERT INTO TEST(ID, A, B) VALUES (1, 10, 11), (2, 12, 13);
-- Fix
ALTER TABLE TEST ALTER COLUMN ID RESTART WITH (SELECT MAX(ID) FROM TEST)   1;

In some compatibility modes H2 still behaves like MySQL and some other database systems, see their documentation for more details. In these modes insertion of user-provided value adjusts generator of identity column automatically like it was in old unsupported versions of H2.