CODEWITHSUNDEEP
hibernatejpaspring-booth2
Dave Richardson
Dave Richardson

Reputation: 4985

H2: Sequence not found Exception, but exists in my schema

I am using Spring Boot, Hibernate/JPA and H2 to develop some entities.

If I create the table/sequence in the H2 public schema then all is OK and I am able to read/insert into the database from my application.

However, if I create the table/sequence in a schema I have created I am unable to insert because the sequence cannot be found. I can see it in the H2 database and it appears to be in the right schema, I can manually select from it but when trying to do an insert in my code I get:

Caused by: org.h2.jdbc.JdbcSQLException: Sequence "VENUE_SQ" not found; SQL statement:
call next value for VENUE_SQ [90036-191]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.191.jar:1.4.191]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.191.jar:1.4.191]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.191.jar:1.4.191]
    at org.h2.command.Parser.readSequence(Parser.java:5399) ~[h2-1.4.191.jar:1.4.191]
    at org.h2.command.Parser.readTerm(Parser.java:2806) ~[h2-1.4.191.jar:1.4.191]
etc...

Some DDL:

create schema MY_SCHEMA AUTHORIZATION MY_SCHEMA_OWNER;
set schema MY_SCHEMA;

create sequence VENUE_SQ;

CREATE TABLE VENUE (
   ID number not null,
   NAME varchar2(255) not null, etc...

Some DML:

INSERT INTO VENUE (ID, NAME, etc...

Some JPA

@Entity
@Table(name = "VENUE", schema = "MY_SCHEMA")
@SequenceGenerator(name = "ID",  sequenceName = "VENUE_SQ",
allocationSize = 1, schema = "MY_SCHEMA")
public class Venue {

    @Id
    @GeneratedValue(generator = "ID", strategy = GenerationType.SEQUENCE)
    @Column(name = "ID", nullable = false)
    private Long id;

    @Column(name = "NAME")
    private String name;

etc...

Hibernate 4.3.11

H2 1.4

Spring boot 1.3.3.RELEASE

UPDATE

I've managed to get this working by prefixing the sequence name with the schema in the @SequenceGenerator annotation. I don't believe this should be necessary as I have given the schema in the parameters for that annotation!

@SequenceGenerator(name = "ID",  sequenceName = "MY_SCHEMA.VENUE_SQ",
allocationSize = 1, schema = "MY_SCHEMA")

So, I'm not done with this problem yet as I would like to understand what the issue is.

UPDATE 2

See my answer below, this is related to a Hibernate bug in our version.

Upvotes: 2

Views: 12199

Answers (1)

Dave Richardson
Dave Richardson

Reputation: 4985

After my update I see that this is related to a hibernate bug, HHH-7232:

https://hibernate.atlassian.net/browse/HHH-7232

This only appears to affect some versions of Hibernate 4.3 and we pick up 4.3.11 from Spring Boot.

Upvotes: 1

Related Questions