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

Solution 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.

Solution 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

Solution 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.

Comments

[SternK]

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.

[Marmite Bomber]

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

[SternK]

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

[Marmite Bomber]

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

[Marmite Bomber]

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.

[Marmite Bomber]

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

[E_the_Shy]

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

[W. Elbashier]

shouldn't you also address the max_value setting?

[SternK]

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

[SternK]

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.

[SternK]

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.

[Vikram S]

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