In H2 database, the auto_increment field is incremented by 32?

user_1154692 0
Viewed 10784

I have this simple Table (just for test) :

create table table 
(
key int not null primary key auto_increment,
name varchar(30)
);

Then I execute the following requests:

insert into table values ( null , 'one');// key=1
insert into table values ( null , 'two');// key=2

At this Stage all goes well, then I close The H2 Console and re-open it and re-execute this request :

insert into table values ( null , 'three');// key=33

Finally, here is all results:

enter image description here

I do not know how to solve this problem, if it is a real problem... pending a response from the author...

h2 auto-increment
3 Answers

This happens when a database "thinks" it got forced to close (an accident or emergency for example), and its related to "identity-cache"

In my case I was facing this issue while learning and playing with the H2 database with an SpringBoot application, the solution was that at the h2-console when finishing playing, execute the SHUTDOWN; command and after that you can safely stop your spring boot application without having this tremendous jump on your autogenerated fields.

Personal Note: This usually is not a problem if you are creating the new database on every application start, but when you persist the data (for example on a data.sql file like on the below properties) you are playing with on the h2 database and it persist even when restarting, then this happens, so close it safely with SHUTDOWN command.

spring.datasource.url=jdbc:h2:./src/main/resources/data;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE
spring.jpa.hibernate.ddl-auto=update

References:

  1. Solution https://stackoverflow.com/a/40135657/10195307
  2. Learn about identity-cache https://www.sqlshack.com/learn-to-avoid-an-identity-jump-issue-identity_cache-with-the-help-of-trace-command-t272/
user_101953070
Related