Hibernate not allowing to increment by 5 in sequence

Question

I have following db sequence

CREATE SEQUENCE phonebook_id_seq INCREMENT BY 5;

select nextval('phonebook_id_seq'); // 1,6,11,....

Java

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

However with Hibernate I am getting following error which says hibernate is expecting increment by 50. Why is so?

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: sequence [phonebook_id_seq] defined inconsistent increment-size; found [5] but expecting [50]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:396)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:371)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:336)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1692)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1630)
    ... 41 common frames omitted
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: sequence [phonebook_id_seq] defined inconsistent increment-size; found [5] but expecting [50]
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateSequence(AbstractSchemaValidator.java:191)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:100)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:68)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:191)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:313)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:889)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:360)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:384)
    ... 45 common frames omitted

Edit1 Added java code.

Answer 1

Normally, you would have to match the sequence increment size in the DB, and the attribute allocationSize in @SequenceGenerator.

In your example, it would be :

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

Hibernate will check the sequence definition in the DB with a query defined by org.hibernate.dialect.Dialect#getQuerySequencesString.

For example for PostgreSQL it will be

SELECT * FROM information_schema.sequences;

---

Since Hibernate 5.4, we can also configure the behaviour when a mismatch is detected. By default, it will throw a org.hibernate.MappingException.

This new property : hibernate.id.sequence.increment_size_mismatch_strategy (doc) (mapped by org.hibernate.cfg.AvailableSettings#SEQUENCE_INCREMENT_SIZE_MISMATCH_STRATEGY) makes it possible to change this behaviour :

hibernate.id.sequence.increment_size_mismatch_strategy (e.g. LOG, FIX or EXCEPTION (default value))

This setting defines the org.hibernate.id.SequenceMismatchStrategy used when Hibernate detects a mismatch between a sequence configuration in an entity mapping and its database sequence object counterpart.

The default value is given by the org.hibernate.id.SequenceMismatchStrategy#EXCEPTION, meaning that an Exception is thrown when detecting such a conflict.

So if you set it to 'FIX', Hibernate will configure allocationSize to 5 automatically. A warning will also be logged, like this :

WARN ...enhanced.SequenceStyleGenerator (SequenceStyleGenerator.java:270) - HHH000497: The increment size of the [SEQ_NAME] sequence is set to [5] in the entity mapping while the associated database sequence increment size is [5]. The database sequence increment size will take precedence to avoid identifier allocation conflicts.

Yes, the log message seems to be wrong in the case of 'FIX' value, it should print 50 for the first value...

Answer 2

The error message is quite clear. You have enabled (or not disabled) schema validation, so when the application starts Hibernate is comparing the database with what it expects from annotations and/or configuration. The phonebook_id_seq sequence is defined with an increment of 5 in the database but the annotation says 50 (or says nothing and has a default value of 50). Change the annotation to match the database or change the sequence or disable validation.

I'm guessing that validation is enabled with spring.jpa.hibernate.ddl-auto=validate based on the tags, but without seeing your code it is hard to tell for sure.