Sequence number, incorrect number

Question

In a spring boot application, I have this entity

@Data @NoArgsConstructor @AllArgsConstructor @Entity public class User {

@Id
@GeneratedValue(generator="user_id_seq")
@SequenceGenerator(name="user_id_seq",sequenceName="user_id_seq", allocationSize=1)
Long id
 ...

}

In mariadb, when I check the sequence

CREATE OR REPLACE SEQUENCE `user_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB

In db, I have only 5 user.

select id from `user` u 

This query return

1002 1004 1005 2007 3001

Why it's not 1, 2, 3, 4, 5

Is it beaucause of the cache 1000?

Answer 1

Yes, its because of the cache. The numbers in the cache are thrown away on shutdown or even table cache closing the sequence.

If there isn't a bug report already, please create one. Its a rather easy fix to save a single number on closing the table.