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.

Answer 1

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