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

Question

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:

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

Answer 1

The database uses a cache of 32 entries for sequences, and auto-increment is internally implemented a sequence. If the system crashes without closing the database, at most this many numbers are lost. This is similar to how sequences work in other databases. Sequence values are not guaranteed to be generated without gaps in such cases.

So, did you really close the database? You should - it's not technically a problem if you don't, but closing the database will ensure such strange things will not occur. I can't reproduce the problem if I normally close the database (stop the H2 Console tool). Closing all connections will close the database, and the database is closed if the application is stopped normally (using a shutdown hook).

By the way, what is your exact database URL? It seems you are using jdbc:h2:tcp://... but I can't see the rest of the URL.

Answer 2

Don't close terminal. Terminal is parent process of h2-tcp-server. They are not detached. When you just close terminal, it's process closes all child processes, what means emergency server shutdown

Answer 3

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/