h2 database problem with autoincrement on column generates gap

Question

When I start the h2 database for the first time and make some inserts the autogenerated id goes correctly as 1, 2, 3 and so on, but when I stop the springboot application and turn it on again then it starts again from like 30 positions above, basically exact same problem this post experienced In H2 database, the auto_increment field is incremented by 32?

I can avoid this problem by executing the "shutdown" command at the h2 console and after that shutdown my springboot app, but the thing is that I would like this to be solved in a much more elegant way via java code so that you dont have to go to the h2console and do that by yourself, also in production it would not be good.

This is an issue only when you persist the h2 database for example in a file, if you are creating a new one on each application start then this should not matter. Does anyone knows how can this be resolved?

I have tried some configurations on the spring properties without success, one solution I was thinking about was to make a @PreDestroy method which would execute a shutdown statement before the spring shutdown (via eclipse ide by the way), but im not sure if that will work or if it is the correct solution, im posting this in hopes somebody came into this issue recently and knows what to do since the other posts similar dont provide a solution, just an explanation of what causes the problem

My application properties:

spring.datasource.url=jdbc:h2:./src/main/resources/data;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE
spring.jpa.hibernate.ddl-auto=update
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false

other posts with this problem:

With Spring JPA and H2 database the ID isn't consequent after app restart

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

UPDATE:

When using the actuator endpoint for shutdown like on this guide https://www.baeldung.com/spring-boot-shutdown The bug doesnt happen, and I guess it is because it is shutting down the application in a "good way", but the thing is that I also dont think the solution is to call an endpoint for shutting down the application, there must be a way to just use the terminate button in eclipse and that this bug doesnt happen =(

Answer 1

You can disable cache of identity column generator with NO CACHE clause:

CREATE TABLE TEST(
    ID BIGINT GENERATED BY DEFAULT AS IDENTITY(NO CACHE) PRIMARY KEY,
    V INTEGER
);

If you use sequence instead of identity column, you need to disable its cache in the same way:

CREATE SEQUENCE TEST_SEQUENCE NO CACHE;

It slightly increases disk I/O, however. You should also understand that gaps will be still possible, if application can insert a row and rollback a transaction after that, generated values aren't reused.

Actually it is a very bad idea to abort an application with embedded database in a hard way, because it may cause data loss or database corruption. If you use DB_CLOSE_ON_EXIT=FALSE, you must always execute the SHUTDOWN command before termination of your application or close all connections (this is enough only if DB_CLOSE_DELAY isn't used).

If you don't have a reason to use DB_CLOSE_ON_EXIT=FALSE (it is only needed when application registers a shutdown hook and works with database during shutdown of JVM), it will be better to remove this connection option.

With properly closed database caches of sequence generators don't affect generated values.

NOTE: Remember that H2 generates random sequence name on first start up when using @GeneratedValue(strategy = GenerationType.IDENTITY) which you can check on the h2console, just use ALTER SEQUENCE paste_seq_name NO CACHE

example: ALTER SEQUENCE SYSTEM_SEQUENCE_63AF2D03_F992_40AD_88AA_6E0F10DA12CE NO CACHE