HSQL org.hsqldb.HsqlException: invalid schema name

Question

I am using HSQL to run a number of unit tests on my java application. I am using Spring + Hibernate. I am having a problem when switching from MySQL to HSQL. The tests run perfectly on MySQL but whenever I change to HSQL I get the following exception:

Caused by: org.hsqldb.HsqlException: invalid schema name: LMS
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaHsqlName(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaName(Unknown Source)
at org.hsqldb.Session.getSchemaName(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readSimpleRangeVariable(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

My Spring configuration is the following:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver" />
    <property name="url" value="jdbc:hsqldb:file:lms" />
    <property name="username" value="SA"/>
    <property name="password" value=""/>
</bean>


<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />

    <property name="packagesToScan">
        <list>
            <value>com.dreamteam.lms.**.*</value>
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <!--<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>-->
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.generate_statistics">true</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
            <prop key="hibernate.cache.provider_class">net.sf.ehcache.hibernate.SingletonEhCacheProvider</prop>
        </props>
    </property>
</bean>

Sample Annotation on one of my classes:

@Entity
@Table(name = "answer", catalog = "lms")
public class Answer implements Cloneable, Serializable, IPojoGenEntity, IAnswer {
.
.

Any insight would be appreciated.

Regards Chris

Solution 1

make "create-schema.sql" file

CREATE SCHEMA lms;

add "dataSourceInitializer" bean

<bean id="dataSourceInitializer" class="org.springframework.jdbc.datasource.init.DataSourceInitializer">
    <property name="dataSource" ref="dataSource" />
    <property name="databasePopulator">
        <bean class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
            <property name="continueOnError" value="true" />
            <property name="scripts">
                <list>
                    <value>classpath:SQL/create-schema.sql</value>
                </list>
            </property>
        </bean>
    </property>
</bean> 

set "depends-on" attribute to "sessionFactory" bean

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" depends-on="dataSourceInitializer">
...

Solution 2

I use following bean to create schema during tests.

public class HSQLSchemaCreator {

    private String schemaName;

    private DataSource dataSource;

    public HSQLSchemaCreator(String schemaName, DataSource dataSource) {
        this.schemaName = schemaName;
        this.dataSource = dataSource;
    }


    @PostConstruct
    public void postConstruct() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("CREATE SCHEMA " + schemaName + " AUTHORIZATION DBA");
    }

}

spring configuration:

  <bean id="hsqlSchemaCreator" class="....HSQLSchemaCreator">
       <constructor-arg name="schemaName" value="..."/>
       <constructor-arg name="dataSource" ref="dataSource"/>
  </bean>

<!-- Override entityManagerFactory to depend on hsqlSchemaCreator for tests  -->
<bean id="entityManagerFactory"  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" depends-on="hsqlSchemaCreator">

and so on...

Solution 3

Just for the record I managed to solve this one by simply removing the 'catalog' attribute from my Hibernate entities. Hence,

@Entity
@Table(name = "answer", catalog = "lms")

became

@Entity
@Table(name = "answer")

Solution 4

Names for schemas, tables, columns, etc. are not (at least not by default) case sensitive in MySQL. HSQLDB is case sensitive, but it also converts all identifiers in query that are not quoted to the uppercase.

You can fastly test is this your problem by changing schema name to LMS everywhere (first in database). You can find more detailed story about HSQLDB and Hibernate from here: HSQLDB No such table Exception

View more solutions

Comments

[Kros]

Thanks for your comment however, this was not successful. I have changed all references to 'LMS'. I am starting to believe that the error message might be a misleading one. Next step, when I get some time will be to download the HSQL source code and debug.

[Psyrus]

Thank you. I had exact same issue and this resolved it. My catalog params were in lower case, but it tells me invalid schema name in upper case, therefore perhaps there was a case conflict.

[user2722077]

Hi, is there a way to move the xml configuration into java-config (annotations)? Maybe for Spring Data? Thx!