Hibernate could not fetch the SequenceInformation from the database

Question

I have recently updated hibernate in my application to 5.4.4.Final. And now, I have faced with the following exception during deployment.

ERROR [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl|[STANDBY] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)']
Could not fetch the SequenceInformation from the database
java.sql.SQLException: Numeric Overflow
        at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4136)
        at oracle.jdbc.driver.NumberCommonAccessor.getLong(NumberCommonAccessor.java:634)
        at oracle.jdbc.driver.GeneratedStatement.getLong(GeneratedStatement.java:206)
        at oracle.jdbc.driver.GeneratedScrollableResultSet.getLong(GeneratedScrollableResultSet.java:259)
        at oracle.jdbc.driver.GeneratedResultSet.getLong(GeneratedResultSet.java:558)
        at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_ForwardOnlyResultSet.getLong(Unknown Source)
        at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.resultSetMaxValue(SequenceInformationExtractorLegacyImpl.java:139)
        at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:61)
        at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.sequenceInformationList(JdbcEnvironmentImpl.java:403)
        at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.<init>(JdbcEnvironmentImpl.java:268)
        at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:114)
        at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
        at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101)
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
        at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152)
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
        at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:175)
        at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:118)
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:900)
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:931)
        at org.hibernate.jpa.HibernatePersistenceProvider.createContainerEntityManagerFactory(HibernatePersistenceProvider.java:141)
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:343)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:318)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1633)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1570)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:956)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:747)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:480)
        at com.sternkn.app.services.web.AppContextLoaderListener.<clinit>(AppContextLoaderListener.java:30)

I use the following persistence.xml.

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd"
   version="2.2">

   <persistence-unit name="appPersistenceUnit" transaction-type="RESOURCE_LOCAL">

      <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle12cDialect" />
            <property name="hibernate.id.new_generator_mappings" value="true"/>

            <property name="hibernate.cache.use_second_level_cache" value = "true"/>
            <property name="hibernate.cache.use_query_cache" value="false" />
            <property name="hibernate.cache.region.factory_class" value="ehcache"/>
            <property name="hibernate.cache.ehcache.missing_cache_strategy" value="create" />
            <property name="hibernate.cache.region_prefix" value="app_cache" />
            <property name="net.sf.ehcache.configurationResourceName" value="/META-INF/app-ehcache.xml" />
            <property name="hibernate.bytecode.provider" value="bytebuddy" />
        </properties>
    </persistence-unit>
</persistence>

After further investigation, I found out that the root cause is the following: hibernate uses the SequenceInformation interface for the sequences metadata manipulations

public interface SequenceInformation {
  Long getMinValue();
  Long getMaxValue();
  Long getIncrementValue();
  ...
}

However, my app uses the sequences like the following:

SQL> CREATE SEQUENCE SEQ_TEST START WITH 1 INCREMENT BY 1 NOCYCLE;
SQL> select MIN_VALUE, MAX_VALUE, INCREMENT_BY
from USER_SEQUENCES
where SEQUENCE_NAME = 'SEQ_TEST';

MIN_VALUE MAX_VALUE                    INCREMENT_BY
--------- ---------------------------- ------------
1         9999999999999999999999999999 1

The Long.MAX_VALUE is equal to 9223372036854775807, therefore I got the numeric overflow exception.

So, my questions:

• Is it a bug in hibernate?
• What will be the best way to solve it?

Now I see the following ways:

1. Fix the sequences declarations. It can be quite problematic in my case. And, by the way, it looks strange that hibernate tries to read metadata about all sequences, not only about used in my application.
2. Create custom dialect that will extend Oracle12cDialect and override getQuerySequencesString() and/or getSequenceInformationExtractor().

public class Oracle8iDialect extends Dialect {
  ...
  public String getQuerySequencesString() {
    return "select * from all_sequences";
  }

  public SequenceInformationExtractor getSequenceInformationExtractor() {
    return SequenceInformationExtractorOracleDatabaseImpl.INSTANCE;
  }
}

I can switch SequenceInformationExtractor to SequenceInformationExtractorNoOpImpl.INSTANCE and hibernate will not read sequences metadata. What impact will this decision have? Hibernate tries to validate allocationSize of @SequenceGenerator() by INCREMENT_BY. Are there other reasons?

Any suggestions will be appreciated.

UPDATE: This is HHH-13694

Answer 1

In the end, I came up to the following solution:

1. Create a sequence information extractor that extends SequenceInformationExtractorOracleDatabaseImpl:

public class AppSequenceInformationExtractor extends SequenceInformationExtractorOracleDatabaseImpl 
{
   /**
    * Singleton access
    */
   public static final AppSequenceInformationExtractor INSTANCE = new AppSequenceInformationExtractor();
   
   @Override
   protected Long resultSetMinValue(ResultSet resultSet) throws SQLException {
      return resultSet.getBigDecimal(super.sequenceMinValueColumn()).longValue();
   }
}

Yes, I understand that we can lose information about the overall magnitude and precision of this BigDecimal value as well as return a result with the opposite sign. But this is not important due to this Steve Ebersole's comment about the Long getMinValue() and Long getMaxValue() methods from the SequenceInformation interface:

I'm actually tempted to just drop these 2 methods from SequenceInformation. We never use them in any meaningful way. Or change the return type for these 2 methods from Long to BigInteger - it could be BigDecimal instead, but the value is implicitly an integer (in the whole number sense).

I guess at this point it is too late in the game to do either of these, so something like your change is fine - like I said, we never use these values anyway. We should definitely deprecate these 2 methods IMO.

So, this trick just allows to avoid the exception with minimal awkward extra coding.

2. Create a hibernate dialect that extends Oracle12cDialect:

public class AppOracleDialect extends Oracle12cDialect
{
   @Override
   public SequenceInformationExtractor getSequenceInformationExtractor() {
      return AppSequenceInformationExtractor.INSTANCE;
   }
   
   @Override
   public String getQuerySequencesString() {
      return "select * from user_sequences";
   }
}

3. And then use this dialect in the persistence.xml:

<property name="hibernate.dialect" value="com.my.app.AppOracleDialect" />

As for the method getQuerySequencesString() overriding and usage USER_SEQUENCES instead of ALL_SEQUENCES it's debatable (See HHH-13322 and HHH-14022). But, in my case, the USER_SEQUENCES usage is preferable.

Answer 2

For PostgreSQL, i used this class as the dialect until Hibernate fixes this problem:

package com.societe.dialect;

import org.hibernate.dialect.PostgreSQL95Dialect;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorNoOpImpl;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;

public class PostgresqlCustomDialect extends PostgreSQL95Dialect {

    public String getQuerySequencesString() {
        return "select * from all_sequences";
    }

    public SequenceInformationExtractor getSequenceInformationExtractor() {
        return SequenceInformationExtractorNoOpImpl.INSTANCE;
    }
}

Answer 3

I would propose a hybrid solution of what is proposed by @sternk and what is already existing in the hibernate repository as a pull request but not merged yet.

The accepted answer here changes the sign of the value once the BigDecimal is converted to long.

In order to see that the accepted answer doesn't work, you can execute the following simple unit test:

  @Test
  public void testSignChange() {

    final BigDecimal minValue = BigDecimal.valueOf(Long.MIN_VALUE);
    final BigDecimal minValueMinusTen = minValue.subtract(BigDecimal.TEN);
    final long minValueMinusTenLong = minValueMinusTen.longValue();
    System.out.println("Min value as big decimal: " + minValueMinusTen);
    System.out.println("Min value as long: " + minValueMinusTenLong);

    final BigDecimal maxValue = BigDecimal.valueOf(Long.MAX_VALUE);
    final BigDecimal maxValuePlusTen = maxValue.add(BigDecimal.TEN);
    final long maxValuePlusTenLong = maxValuePlusTen.longValue();
    System.out.println("Max value as big decimal: " + maxValuePlusTen);
    System.out.println("Max value as long: " + maxValuePlusTenLong);
  }

and you will see the output as

Min value as big decimal: -9223372036854775818
Min value as long: 9223372036854775798
Max value as big decimal: 9223372036854775817
Max value as long: -9223372036854775799

Hence, I would propose a solution that would handle both min and max values as follows:

public class SequenceInformationExtractor extends SequenceInformationExtractorOracleDatabaseImpl {

  public static final SequenceInformationExtractor INSTANCE = new SequenceInformationExtractor();

  private static final BigDecimal LONG_MIN_VALUE_AS_DECIMAL = BigDecimal.valueOf(Long.MIN_VALUE);

  private static final BigDecimal LONG_MAX_VALUE_AS_DECIMAL = BigDecimal.valueOf(Long.MAX_VALUE);

  @Override
  protected String sequenceMaxValueColumn() {
    return "max_value";
  }

  @Override
  public Long resultSetMinValue(final ResultSet resultSet) throws SQLException {
    final BigDecimal asDecimal = resultSet.getBigDecimal(this.sequenceMinValueColumn());
    if (asDecimal.compareTo(SequenceInformationExtractor.LONG_MIN_VALUE_AS_DECIMAL) < 0) {
      return Long.MIN_VALUE;
    }
    return asDecimal.longValue();
  }

  @Override
  public Long resultSetMaxValue(final ResultSet resultSet) throws SQLException {
    final BigDecimal asDecimal = resultSet.getBigDecimal(this.sequenceMaxValueColumn());
    if (asDecimal.compareTo(SequenceInformationExtractor.LONG_MAX_VALUE_AS_DECIMAL) > 0) {
      return Long.MAX_VALUE;
    }
    return asDecimal.longValue();
  }
}

Answer 4

add (or Change if already there) the dialect property as below in to "application.properties".

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

Answer 5

I solved the problem as follows. Created an extension for Oracle12cDialect. Limited the maximum/minimum value of columns to SQL

package ru.mvawork.hibernate;

import org.hibernate.dialect.Oracle12cDialect;

@SuppressWarnings("unused")
public class CustomOracleDialect extends Oracle12cDialect {

    @Override
    public String getQuerySequencesString() {
        return "select SEQUENCE_OWNER, SEQUENCE_NAME, greatest(MIN_VALUE,         -9223372036854775807) MIN_VALUE,\n"+
                "Least(MAX_VALUE, 9223372036854775808) MAX_VALUE, INCREMENT_BY,     CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE,\n"+
                "Least(greatest(LAST_NUMBER, -9223372036854775807), 9223372036854775808) LAST_NUMBER,\n"+
                "PARTITION_COUNT, SESSION_FLAG, KEEP_VALUE\n"+
                "from all_sequences";
    }

}

In the application.properties file referred to a dialect implementation

spring.jpa.properties.hibernate.dialect=ru.mvawork.hibernate.CustomOracleDialect

You can recreate sequences by limiting the minimum and maximum values. In my case, I can 't do it. The primary keys that I use have the dimension Number (12), which falls within the range limit from -9223372036854775807 to 9223372036854775808 with a large margin

Answer 6

You simple used the dafault MAX_VALUE of a sequence, which is too high for the Java LONG datatype.

Fortunatelly you may any time reset the MAX_VALUE with ALTER SEQUENCE to a lower number that will cause no problems.

Example

CREATE SEQUENCE SEQ_TEST START WITH 1 INCREMENT BY 1 NOCYCLE;


select MAX_VALUE from ALL_SEQUENCES where SEQUENCE_NAME = 'SEQ_TEST';

 MAX_VALUE
----------
9999999999999999999999999999


ALTER SEQUENCE SEQ_TEST
  MAXVALUE 9223372036854775807;

select MAX_VALUE from ALL_SEQUENCES where SEQUENCE_NAME = 'SEQ_TEST';

 MAX_VALUE
----------
9223372036854775807

and BTW

it looks strange that hibernate tries to read metadata about all sequences, not only about used in my application.

Hibernate uses select * from all_sequences as an Oracle Dialect to get the sequence information. Note that ALL_SEQUENCES does not mean all existing sequences, but all sequences, that your Hibernate database user (DBUSER from the connection pool) is granted to use - which is of course absolute correct.