Sequence "HIBERNATE_SEQUENCE" not found; SQL statement

Question

In my spring mvc app, i have the following object. I am trying to make a visual of data using devtool in my app.

@Entity
@Data
public class ConsultationRequest {
    @Id
    @GeneratedValue
    private Long id;

    private String name;

    private String email;

    private String purpose;

    private String programme;

    private int year;

    private String language;

    private String comments;
    @Enumerated(EnumType.STRING)
    private ConsultationStatus status;
}

Then i used the jpa to make the entity:

@Repository
public interface ConsultationRequestRepository extends JpaRepository<ConsultationRequest, Long> {

}

The problem is when i load my application, i face with 2 errors:

 Unsuccessful: drop sequence hibernate_sequence
[36morg.hibernate.tool.hbm2ddl.SchemaExport  Sequence "HIBERNATE_SEQUENCE" not found; SQL statement:

Then when i open the

http://localhost:8080/h2-console/

I cannot see the table. It seems that the in the boot process, table is not made.

Rohit Gaikwad · Answer 1 · 2017-06-16T09:06:43.087

93

Update your code as below:

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private Long id;

As you have not specified a sequence table name, hibernate will look for a sequence table named as hibernate_sequence and use it as default.

For Oracle/Postgres, increment fields used are sequence tables.
In MySql, there are increment fields that automatically increment.

edited Jun 16 '17 at 09:06

answered Oct 01 '16 at 15:04

Rohit Gaikwad

3,677
3
17
40

score 26 · Answer 2 · edited Jan 20 '21 at 18:07

26

If someone is getting this error with Spring Boot testing(with H2) , we have to use following in the application.properties(or whatever profile we are using) :

spring.jpa.hibernate.ddl-auto=create-drop

edited Jan 20 '21 at 18:07

Marv

3,517
2
22
47

answered Jul 28 '20 at 04:37

Ram Pratap

482
4
9

1

I actually use the property: spring.jpa.hibernate.ddl-auto=update. That doesn't override the data in case they were inserted with non-generated keys in data.sql – Tomas Antos Apr 19 '21 at 08:18
1

`create-drop` worked for POSTing data but not for loading from `data.sql` during startup with H2 database. The `update` value worked the opposite (worked fro data.sql but not for POST data) and the `none` value was the same as `update` – pamcevoy May 02 '21 at 16:15
1

This worked perfectly, thanks! – AdityaKapreShrewsburyBoston Sep 22 '21 at 19:32

score 10 · Answer 3 · answered Sep 07 '20 at 10:24

10

Setting the following property in the properties file helped me solve the hibernate_sequence problem for hibernate 5.4v

spring:
  jpa:
    hibernate:
      use-new-id-generator-mappings: false

answered Sep 07 '20 at 10:24

Amrut Prabhu

1,161
11
11

I had already wasted half a day on this, and tried everything. Nothing worked, until I have found this solution. When testing my DB Code, I use Liquibase to create the DB schema, and populate it with some data. In my tests, I was always getting this error: `Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "HIBERNATE_SEQUENCE" not found; SQL statement` This was the fix: `spring.jpa.hibernate.use-new-id-generator-mappings=false` – razvang Oct 28 '20 at 08:29
This also was the solution for me. I use liquibase as well to create the database tables with an intended target of PostgreSQL, and H2 for testing. The auto-increment works in PostgreSQL by creating a sequence, but not in H2. This was the solution for me, as the H2 db is not persistent and I just need a new next sequence ID for testing. – md_rasler Nov 21 '22 at 21:03

score 8 · Answer 4 · edited Aug 10 '17 at 08:27

8

Check persistence.xml

property name="hibernate.hbm2ddl.auto" value="create"

not hdm2ddl

This worked in my case.

edited Aug 10 '17 at 08:27

Matteo Baldi

5,613
10
39
51

answered Aug 10 '17 at 08:11

David

81
1
1

Rafael N. Siqueira · Answer 5 · 2021-11-03T21:15:08.403

3

SQL operation ERROR when start app spring-boot.

I added the setting in spring properties and solved: in the spring:jpa.hibernate.ddl-auto= create-drop to hibernate be able create or drop table automatically.

edited Nov 03 '21 at 21:15

answered Nov 03 '21 at 12:52

Rafael N. Siqueira

31
5

2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 03 '21 at 13:06

score 2 · Answer 6 · answered Mar 29 '19 at 14:25

If you use a 2nd cache with liquidbase, you have to add the sequence in the changelog like this:

<changeSet author="liquibase-docs"
    id="createSequence-example">
    <createSequence catalogName="cat" cycle="false"
        incrementBy="1" ordered="true" schemaName="public"
        sequenceName="hibernate_sequence" startValue="0" />
</changeSet>

score 0 · Answer 7 · answered Sep 27 '22 at 12:40

For spring-boot 2.7.x and h2 2.x you need to add MODE=LEGACY; in the database connection:

example application.yml:

spring:
  datasource:
    url: jdbc:h2:mem:test;MODE=LEGACY;

exemple application.properties:

spring.datasource.url=jdbc:h2:mem:test;MODE=LEGACY;

score 0 · Answer 8 · answered May 19 '23 at 08:03

If someone is having this problem while testing a repository, changing the code like below might help you:

From this:

@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

To this:

@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

score 0 · Answer 9 · answered Jun 05 '23 at 12:33

0

I ran into this issue while running some tests. Adding these annotations helped me fix the issue for me:

@Transactional
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_CLASS)

answered Jun 05 '23 at 12:33

Flavio Oliva

401
4
15

score 0 · Answer 10 · answered Jul 24 '23 at 05:03

0

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

with

spring.jpa.hibernate.use-new-id-generator-mappings=false

in test-config.properties works for me

answered Jul 24 '23 at 05:03

Monarch

1

score -1 · Answer 11 · answered Jun 19 '21 at 19:52

-1

For Mysql:

the auto-increment is not added, modify ur table:

ALTER TABLE table_name MODIFY COLUMN id BIGINT AUTO_INCREMENT=1

answered Jun 19 '21 at 19:52

Gru

67
1
7

Sequence "HIBERNATE_SEQUENCE" not found; SQL statement

11 Answers11

From this:

To this:

Linked