Sequence "HIBERNATE_SEQUENCE" not found for h2 test with GenerationType.AUTO

Question

I am trying to migrate one of our services to Spring Boot 2.0.3. While most of the tests are fine, one of them fails with error:

Caused by: org.h2.jdbc.JdbcSQLException: Sequence "HIBERNATE_SEQUENCE" not found; SQL statement:
call next value for hibernate_sequence [90036-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.command.Parser.readSequence(Parser.java:5970)
    at org.h2.command.Parser.readTerm(Parser.java:3131)
    at org.h2.command.Parser.readFactor(Parser.java:2587)

This is really confusing because all teh entities rely on the same generation id mechanism:

@GeneratedValue(strategy = GenerationType.AUTO)

It's a repository test and the repository itself is very straight-forward:

@Repository
public interface OrderDetailsRepository extends JpaRepository<OrderDetails, Long> {

    OrderDetails findFirstByOrderIdOrderByIdDesc(String orderId);
}

What can possible go wrong here?

PS: And, yes, there is both orderId and Id field present in the entity.

What happens if you swap to GenerationType.IDENTITY? I've had more luck with this than AUTO with hibernate. Although both should work fine. — Daisy Day, Aug 03 '18 at 17:29
It solves the problem but it's AUTO for the rest of the entities and I want them to be consistent. — yuranos, Aug 03 '18 at 21:36
Well, I can do that, but I'm a kinda guy who would like to figure out why it fails in one place but not the other. — yuranos, Aug 03 '18 at 22:02
Yep fair enough. https://www.thoughts-on-java.org/jpa-generate-primary-keys/ https://stackoverflow.com/questions/39807483/sequence-hibernate-sequence-not-found-sql-statement I think these links provide some insight. — Daisy Day, Aug 04 '18 at 06:45

score 11 · Answer 1 · answered Sep 06 '19 at 22:33

When you choose @GeneratedValue(strategy = GenerationType.AUTO) Hibernate selects a generation strategy based on the database-specific dialect. The problem in your case is hibernate can't find the HIBERNATE_SEQUENCE and thus can't create a new object for the sequence. Try adding a sequence like this and it should solve the problem, but could lead to inconsistencies with the data...

CREATE TABLE CUSTOMER(
  id int primary key,
);

CREATE SEQUENCE HIBERNATE_SEQUENCE START WITH 1 INCREMENT BY 1;

I would suggest using the GenerationType.SEQUENCEand try to recreate your id pattern with your custom db sequence. You can read more about the GenerationType's here

Kushwaha · Answer 2 · 2019-12-21T20:39:22.797

I encountered the same issue when written sample code for spring boot with h2. please find the details below of my findings. In your entity class sequence is not given and check your table as well i.e. have you given AUTO_INCREMENT for primary key?

Please follow as below. 1. Check your ddl once and set auto_increment for primary key (see below for id)

    CREATE TABLE EMPLOYEES (
  id INT AUTO_INCREMENT  PRIMARY KEY,
  first_name VARCHAR(250),
  last_name VARCHAR(250),
  email VARCHAR(250) DEFAULT NULL
);

Check your entity class and update primary key as below

public class Employee {
```
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
```
Please make a note that GenerationType is given IDENTITY you can give AUTO as well. Also if you are using h2 in-memory DB and table inserted few records while boot-start (if dml file available in resource) then hibernate insertion may give unique constraint because sequence 1,2,3..(depends on how many records inserted while startup) may have already used and as I said above hibernate will generate the sequence from 1 and will increment by 1 for every new insertion. So I would suggest don't insert records while boot startup better to insert programmatically.
For your learning you can use as given above but if it may use in production then better to implement your own logic to generate the sequence.

Thanks for the mention of spring.jpa.hibernate.ddl-auto I didn't check it. — Hexronimo, Apr 04 '20 at 09:18

score 3 · Answer 3 · answered Aug 01 '20 at 06:54

I had similar problem. If I understand things correctly It went down like this.

Before Spring upgrade I used AUTO - but it actually opted by default to IDENTITY strategy. I had auto incrementing PKs defined like this:

id            BIGINT AUTO_INCREMENT PRIMARY KEY

Everything was fine.

With spring upgrade I had to specify H2 dialect:

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

I've read that if you use Hibernate as your persistence provider, it selects a generation strategy based on the database specific dialect. For H2 it probably opted for global sequence (that's what AUTO should mean according to JPA spec) - and it didn't find the sequence. Solution is of course create the sequence (as suggested above) or manually override to originally auto selected IDENTITY.

CREATE SEQUENCE HIBERNATE_SEQUENCE START WITH 1 INCREMENT BY 1;

@GeneratedValue(strategy = GenerationType.IDENTITY)

I believe that root cause is that meaning of AUTO is/was not consistent/well defined/understood in time. Probably original 'auto' switch to IDENTITY was basically a bug.

Sequence "HIBERNATE_SEQUENCE" not found for h2 test with GenerationType.AUTO

3 Answers3