org.h2.jdbc.JdbcSQLException: Table "ALL_SEQUENCES" not found

Question

Could someone please tell me the reasons for the below error.

I am using Hibernate in my project and face the below error during server startup

15:04:27.909 [localhost-startStop-1] ERROR o.h.tool.hbm2ddl.SchemaValidator - HHH000319: Could not get database metadata
org.h2.jdbc.JdbcSQLException: Table "ALL_SEQUENCES" not found; SQL statement:
 select sequence_name from all_sequences  union select synonym_name   from all_synonyms us, all_sequences asq  where asq.sequence_name = us.table_name    and asq.sequence_owner = us.table_owner [42102-168]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2-1.3.168.jar:1.3.168]
    at org.h2.message.DbException.get(DbException.java:169) ~[h2-1.3.168.jar:1.3.168]
    at org.h2.message.DbException.get(DbException.java:146) ~[h2-1.3.168.jar:1.3.168]
    at org.h2.command.Parser.readTableOrView(Parser.java:4770) ~[h2-1.3.168.jar:1.3.168]
    at org.h2.command.Parser.readTableFilter(Parser.java:1084) ~[h2-1.3.168.jar:1.3.168]
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1690) ~[h2-1.3.168.jar:1.3.168]

Answer 1

In my case, I was using multiple datasources in my application, and using H2 for tests. In this case, you have to set manually the dialect in JPA properties of the datasource, not via application.properties file.

Map<String, Object> properties = new HashMap<>();
            properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
            return builder
                    .dataSource(ds)
                    .packages("com.application.demo")
                    .properties(properties)
                    .persistenceUnit("persitenceUnit1")
                    .build();

In every datasource used.

In case it can help someone.

regards

Answer 2

I used multiple dataSources

OracleDb1Configuration

@Primary
@Bean(name = "oracleDb1EntityManager")
public LocalContainerEntityManagerFactoryBean oracleDb1EntityManagerFactory(EntityManagerFactoryBuilder builder) {
    return builder
            .dataSource(oracleDb1DataSource)
            .properties(hibernateProperties())
            .packages("com.fengxin58.user.ddd.domain.model.oracle.db1")//设置实体类所在位置
            .persistenceUnit("oracleDb1PersistenceUnit")
            .build();
}

private Map<String, Object> hibernateProperties() {

    String env = monitorService.env();
    if(log.isDebugEnabled()) {
        log.debug("current profile: {}", env);
    }
    Resource resource = null;
    if(EnvEnum.TEST.key().equals(env)) {
        resource = new ClassPathResource("hibernate-oracle-db1-test.properties");
    }else {
        resource = new ClassPathResource("hibernate-oracle-db1.properties");
    }
    try {
        Properties properties = PropertiesLoaderUtils.loadProperties(resource);
        return properties.entrySet().stream()
                .collect(Collectors.toMap(
                        e -> e.getKey().toString(),
                        e -> e.getValue())
                );
    } catch (IOException e) {
        return new HashMap<String, Object>();
    }
}

hibernate-oracle-db1-test.properties

hibernate.hbm2ddl.auto=update

application-test.yml oracle: db1: datasource: url: jdbc:h2:mem:test driver-class-name: org.h2.Driver username: root password: db2: datasource: url: jdbc:h2:mem:test driver-class-name: org.h2.Driver username: root password:

Answer 3

This happens when you use either a wrong dialect in your persistence-unit inside your persistence.xml, or you validate against the wrong database. For example, when you run your application against a local H2 database, the best choice would be to remove the dialect, since Hibernate can recognize the database without this property (if the Version of Hibernate is new enough to recognize newer databases). Another solution would be to remove the validate attribute, but I would not recommend that, since you have no database checks at startup then:

<properties>
    <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
    <property name="hibernate.hbm2ddl.auto" value="validate" />
</properties>

Answer 4

You have to ensure that the table and the sequence is created. If it is created, then it will work.

The table "ALL_SEQUENCES" is not created. Please check your database is it exists or not?

Your problem is the sequence is not created as well as the table is not created.

Solution:

Check your hibernate.cfg.xml. It is not configured well. For your clarification, I have given a cfg file below:

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:database/test</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"/>

        <property name="hibernate.default_schema">PUBLIC</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.H2Dialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

        <mapping class="au.com.ozblog.hibernate.h2.example.User"/>

    </session-factory>

</hibernate-configuration>