Schema-validation: missing table [SEQUENCE_NAME] when using Hibernate Sequence Generator Strategy

Question

I have some problems with the hibernate id generation and a ms sql server.

I am using the GenerationType.SEQUENCE in my application to generate Ids with hibernate.

@Id
@SequenceGenerator(name = "SequenceGenerator", sequenceName = "SEQUENCE_NAME")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SequenceGenerator")
private Long id;

As DB I use a microsoft sql server 2017.

I create the sequence via Liquibase:

<createSequence incrementBy="50" sequenceName="SEQUENCE_NAME" startValue="100000"/>

While starting the Spring Boot Application I get the error:

Schema-validation: missing table [SEQUENCE_NAME]

When I manually query for the sequence it can be found:

SELECT COUNT(*) FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SEQUENCE_NAME]') AND type = 'SO'

I am a little bit confused that it says "missing table". With oracle it works fine. Does mssql not support id generation via sequences.

My Configuration:

spring:
  datasource:
    url: "jdbc:sqlserver://localhost:1433"
    username: sa
    password: ABc12345!
  jpa:
    properties:
      hibernate.dialect: org.hibernate.dialect.SQLServerDialect

I use this docker image: mcr.microsoft.com/mssql/server:2017-CU12-ubuntu

Yes, it's not supported for SQL Server. Refer to Database Support table at https://www.liquibase.org/documentation/changes/create_sequence.html — Nikolai Shevchenko, Apr 30 '19 at 09:34
Yes, but the sequence was created. I could query it afterwards. And also `Select NEXT VALUE FOR SEQUENCE_NAME` returned a new id. — Leon, Apr 30 '19 at 10:00
Hmm, maybe you've misconfigured hibernate... show your config file (application.properties/.yml by default) — Nikolai Shevchenko, Apr 30 '19 at 10:32
i mean not whole file, but those parts where JPA/Hibernate properties are placed — Nikolai Shevchenko, Apr 30 '19 at 10:42

score 4 · Accepted Answer · answered Apr 30 '19 at 17:24

4

I used the wrong dialect in the configuration. The correct one is:

spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.SQLServer2012Dialect

The other one is for older servers which doesn't support sequences.

answered Apr 30 '19 at 17:24

Leon

71
6

score 0 · Answer 2 · answered Apr 30 '19 at 11:29

0

Try adding default_schema to application.yml:

spring:
   jpa:
      properties:
         hibernate:
            default_schema: "dbo"

answered Apr 30 '19 at 11:29

Nikolai Shevchenko

7,083
8
33
42

This doesn't work. Problem is still the same. I now realized that the error also appears for the `hibernate_sequence` – Leon Apr 30 '19 at 11:46

Schema-validation: missing table [SEQUENCE_NAME] when using Hibernate Sequence Generator Strategy

2 Answers2