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

Carping

Which software are you using in your screenshot? – Plumbery

Paul: camtasia studio. – Carping

Sorry! I mean the database explorer. :-) – Plumbery

This is H2's explorer :) – Carping

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.

Bitt

Hi Thomas, Im using this URL : jdbc:h2:tcp://localhost/~/autoin ... In my post Im just playing with the H2 Console tool (because I had a bug in my software but I was surprised when I discovered the problem is from H2 Database not from JPA) , "I can't reproduce the problem if I normally close the database (stop the H2 Console tool)" Okay I recorded a video in which I closed the console but still the problem persists, see : youtube.com/watch?v=pGuDjgbTr7o&feature=youtu.be – Carping

A video bug report, nice :-) When you closed the console window 'h2 - Raccourci' at 0:53 (I guess that was the server and the H2 Console tool but I'm not sure), I think that 'killed' the process and the database had no chance to normally close itself (no shutdown hook was called, the connection wasn't closed normally). – Bitt

if this is not a technical problem is there a chance to solve this problem ? I'm stuck at this stage and time is running out... – Carping

Well, you can just ignore the problem. You will lose at most 31 entries. Or you can use a smaller cache size (in this case you need to create the sequence manually). Or you close all connections, which will close the database, or let the shutdown hook close it. What you did is kill the process, which results in the observed behavior. It is not a bug. – Bitt

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

Dunlavy

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://mcmap.net/q/426718/-what-is-the-proper-way-to-close-h2
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/

Insertion