Object name already exists

Help
Mike D.
2012-10-16
2014-01-19
  • Mike D.

    Mike D. - 2012-10-16

    I'm trying to set up an HSQL database for testing, using version 2.2.9, Hibernate 3.6.9, and Spring 3.1.2.  We had been using a local postgresql database but are making a switch for testing.  I have 40-50 test classes with 200+ tests in total.  Each test class works fine if run individually from eclipse.  When I use Maven to compile and test everything I have test errors.  At some point it seems to be trying to run my init.sql script again and create tables again.  I'm getting this as my final cause:

    Caused by: org.hsqldb.HsqlException: object name already exists: DUAL_ASSET_ASSETID_SEQ
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.SchemaObjectSet.checkAdd(Unknown Source)
    at org.hsqldb.SchemaManager.checkSchemaObjectNotExists(Unknown Source)
    at org.hsqldb.StatementSchema.setOrCheckObjectName(Unknown Source)
    at org.hsqldb.StatementSchema.getResult(Unknown Source)
    at org.hsqldb.StatementSchema.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    … 52 more

    I tried using 'IF NOT EXISTS' when creating that table but when I go to create the sequence I'm getting the same error.  So it's trying to do more than just that one table, and I can't use 'IF NOT EXISTS' on a CREATE SEQUENCE statement so I'm stuck there.

    Is there any reason why my data seems to be loaded again?  I'm also occasionally getting this error:

    2012-10-16 10:55:48,489  WARN  org.springframework.jdbc.datasource.embedded.HsqlEmbeddedDatabaseConfigurer:shutdown:46 - Could not shutdown embedded database
    java.sql.SQLException: Database lock acquisition failure: attempt to connect while db opening /closing
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
    at org.springframework.jdbc.datasource.SimpleDriverDataSource.getConnectionFromDriver(SimpleDriverDataSource.java:140)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
    at org.springframework.jdbc.datasource.embedded.AbstractEmbeddedDatabaseConfigurer.shutdown(AbstractEmbeddedDatabaseConfigurer.java:40)
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.shutdownDatabase(EmbeddedDatabaseFactory.java:152)
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactoryBean.destroy(EmbeddedDatabaseFactoryBean.java:65)
    at org.springframework.beans.factory.support.DisposableBeanAdapter.destroy(DisposableBeanAdapter.java:211)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroyBean(DefaultSingletonBeanRegistry.java:498)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroySingleton(DefaultSingletonBeanRegistry.java:474)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.destroySingletons(DefaultSingletonBeanRegistry.java:442)
    at org.springframework.context.support.AbstractApplicationContext.destroyBeans(AbstractApplicationContext.java:1071)
    at org.springframework.context.support.AbstractApplicationContext.doClose(AbstractApplicationContext.java:1045)
    at org.springframework.context.support.AbstractApplicationContext$1.run(AbstractApplicationContext.java:963)
    Caused by: org.hsqldb.HsqlException: Database lock acquisition failure: attempt to connect while db opening /closing
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.DatabaseManager.getDatabase(Unknown Source)
    at org.hsqldb.DatabaseManager.newSession(Unknown Source)
    … 16 more

     
  • Fred Toussi

    Fred Toussi - 2012-10-16

    It is all in your Spring and Hibernate settings.

    Change your settings to avoid database shutdown between tests. You may have an explicit SHUTDOWN or the "shutdown" property on the URL.

    Without SHUTDOWN, the tests should work more like PostgreSQL, which runs as a server and is not shutdown.

     
  • Mike D.

    Mike D. - 2012-10-17

    I can't find that I'm using "shutdown" anywhere in my code or my config files.  Is having shutdown set to false the default or do I need to set it somewhere?  If I need to set it what's the best place to do that.

     
  • Fred Toussi

    Fred Toussi - 2012-10-17

    By default no shutdown is performed. If the connection URL, which starts with "jdbc:hsqldb:" has a ";shutdown=true" at the end, it forces a shutdown when the connection is closed and there is no other connection.

    Alternatively, you can run the HSQLDB server before you start the tests. This setup works more like PostgreSQL.

     
  • Mike D.

    Mike D. - 2012-10-17

    I don't have a connection url.  I'm using a jdbc:embedded bean with Spring 3.  I have this setup for HSQL

        <jdbc:embedded-database id="markit">
            <jdbc:script location="classpath:init.sql" />
        <jdbc:script location="classpath:data.sql" />
        </jdbc:embedded-database>
    

    We're trying to use HSQL for tests so it's simpler and we don't need to have anything special installed or setup on the machine so I would rather not set it up so I have to start a server first.

     
  • Fred Toussi

    Fred Toussi - 2012-10-17

    This Spring setting means it is using a memory database.

    With a mem: database, HSQLDB is either explicitly shutdown, or each test simply abandons the database and the database remains open.

    It is not clear to me what happens to the database after each individual test. If the database is shutdown, all data is lost, so the next test will not have a problem with  "object name already exists: DUAL_ASSET_ASSETID_SEQ". If the database is not shutdown, then this error is thrown. If the database is in the process of being shutdown, then the second error is thrown.

    If you can get the tests to run using a single main thread, then the shutdowns will complete before the next test opens a connection.

    Re HSQLDB Server, you can start it with Maven at the start of your tests.  This server can use a mem: database which keeps the data if no explicit shutdown is issued.

    BTW, I have taken on board your comment about CREATE SEQUENCE IF NOT EXISTS and this feature will be supported by the next snapshot jar.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks