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

Question

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

Dunn

Answer 1

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

Nanna

We are using mode MSSQLServer, not MySQL. Anyways, this changed starting with H2 2.x? – Dunn

Yes, but this trick with adjustment of base value is enabled in MSSQLServer compatibility mode of H2, you may want to enable it. – Nanna

I had to enable the 'limit' in MSSQLServer compatibility mode, so I enabled other options too but none did the trick. Which one are you talking about? – Dunn

Sorry, I was wrong about this mode. You can change Mode.updateSequenceOnManualIdentityInsertion to true, but these flags aren't supported in any way and may be modified in any version of H2 without a warning. – Nanna

First I will try to fix all the tests without enabling this mode. I will enable it only if the right way takes way too long. Thanks for the help. – Dunn

In your comment 'GENERATED ALWAYS BY IDENTITY' should be 'GENERATED ALWAYS AS IDENTITY' – Ryley

Answer 2

If you have script that inserts initial data then don't insert the ID yourself.

example:

if you are using

insert into exercise (id, created_by, created_on, ...)

then replace with

insert into exercise (created_by, created_on, ...)

I faced the same case after upgrading to spring boot 2.7 which uses H2 2.1.212. and to solve I modified the initial data inside the data.sql by removing the ID from the insert

Mohawk

Thanks! Was scratching my head about this. After reading your answer I found some test data that did exactly this. Much appreciated 🙏 – Claw

Answer 3

I had the same issue when migrating to SpringBoot 2.7 and therefore changing version of H2 from 1.4.200 to 2.1.214. I needed to insert an ID from configuration at startup so letting H2 use the default value was not an option. Plus the code need to run on both H2 and MySQL DB. What worked in the end was adding Mode=MySQL to the datasource url.

spring:
  datasource:
    url: "jdbc:h2:mem:testdb;Mode=MySQL"

Corset

This seems to work for me for one of my projects, but does not work for other. H2 seems to want to insert a null into the ID column as part of the insert script. Got the error:Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ACC_PROV_GRP_ID"; SQL statement: insert into account_group (acc_grp_id, account_manager, is_active, last_request_timestamp, odc_account_id, operation_type) values (null, ?, ?, ?, ?, ?) [23502-220] [acc_grp_id is defined as auto_increment] – Bromide