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

Aribold

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.

Aribold

shouldn't you also address the max_value setting? – Hoarse

@W.Elbashier Look at the SequenceInformationExtractorOracleDatabaseImpl implementation. The Long resultSetMaxValue(ResultSet resultSet) method has already overridden. – Aribold

Had this issue with H2 for tests and postgres db in production. This worked for me as well with following Dialect: public class AppH2Dialect extends H2Dialect { @Override public SequenceInformationExtractor getSequenceInformationExtractor() { return AppSequenceInformationExtractor.INSTANCE; } } and the same SequenceInformationExtractor as mentioned in this answer. – Slavism

Answer 2

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

Maximalist

Thanks for this answer. Other readers might like to remove "PARTITION_COUNT" from the columns selected as it may not exist on all Oracle databases. See: stackoverflow.com/questions/50385126 – Mullis

Yes It worked, thanks. just have to remove "PARTITION_COUNT" for oracle as in my case. – Marseillaise

Answer 3

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.

Carnot

Marmite Bomber, thank you for reply. But I am afraid that in my case it will not be the acceptable solution because of another apps in our company that use the same database schema and due to this correction can be potentially affected. – Aribold

Yes, sharing a Hibernate schema with other applications provides increased risc. Do you at least use a different connection user as your competitor(s)? – Carnot

Each app uses its own weblogic data source, but almost all of these data sources use the same oracle DB user. – Aribold

Well with this setup, IMO you proved you can manage the potential risc quite well, so you should ask 1) what is the current value of the sequence (LAST_NUMBER) 2) what is the average increase per year and 3) at what time you will hit the limit of Long.MAX_VALUE. If it is more that 100 year in the future, I'd postpone the solution to that time;) – Carnot

I'm only wondering why this exception si so exceptional, While googling for "Could not fetch the SequenceInformation from the database" "java.sql.SQLException: Numeric Overflow" this is only the second existing match. Probalby people use realistic non default MAX_VALUES for sequences. – Carnot

Anyway as of 20.4.2020 Google find 56 pages with this exception, seems to get up. – Carnot

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

Electric

Changing dialect from Oracle to MySQL one also should assume changing database. I do not think that will be acceptable for somebody with a significant application. – Aribold

Answer 5

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();
  }
}

Wickman

Look at the Steve Ebersole's comment to this pull request. Actually, Hibernate does not use the methods Long getMinValue() and Long getMaxValue() in any meaningful way. So, my solution just allow to avoid the exception with minimal awkward extra coding. – Aribold

Answer 6

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;
    }
}

Bashemath