HSQL database occasionally executes schema.sql twice

Question

I have some non-deterministic behavior with HSQL database in Spring. On occasion, sequence is generated twice and DataSource initialization fails due to that.

The odd thing is, if it fails, it is always from the test which tries to read database entry that doesn't exist. The other tests which read existing entries don't fail.

I really don't understand what did i do wrong and why this happens.

HSQLDB version is 2.3.2

UPDATE: I have removed sequence entirely and now i again, on occasion, get error that table voucher is being created twice.

Here is my configuration:

@EnableJpaRepositories
@EnableTransactionManagement
@Configuration
@Profile("test")
public class TestDatabaseConfig {

@Bean
@Primary
public EntityManagerFactory entityManagerFactory() throws ClassNotFoundException {

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(false);
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan(ENTITIES_PACKAGE);
    factory.setDataSource(dataSource());
    factory.afterPropertiesSet();
    return factory.getObject();

}

@Bean
@Primary
public DataSource dataSource() {

    return new EmbeddedDatabaseBuilder().addDefaultScripts()
                                        .setType(EmbeddedDatabaseType.HSQL)
                                        .build();
}

@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) throws ClassNotFoundException {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory);
    return txManager;
    }
}

And the schema.sql:

CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1;
CREATE TABLE voucher (id INTEGER, code VARCHAR(64) NOT NULL UNIQUE, type VARCHAR(64) NOT NULL, state VARCHAR(64) NOT NULL, class_name VARCHAR(64), serial VARCHAR(64), consumption_user VARCHAR(255), creation_date TIMESTAMP DEFAULT current_timestamp, consumption_date TIMESTAMP, expiry_date TIMESTAMP)

data.sql:

-- VALID
INSERT INTO voucher (id, code, type, state, serial ) VALUES (1,'success', '1', 'E', 'serial: 123');

--ALREADY CONSUMED
INSERT INTO voucher (id, code, type, state) VALUES (2,'used', '1', 'U');

-- DATE EXPIRED
INSERT INTO voucher (id, code, type, state, expiry_date) VALUES (3,'expired', '1', 'E', DATE '2014-12-12');

And the error trace, at the top you can see sequence being generated twice sometimes:

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [schema.sql]: CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1; nested exception is java.sql.SQLSyntaxErrorException: object name already exists: VOUCHER_ID_SEQ in statement [CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189) ~[spring-beans-4.1.6.RELEASE.jar:4.1.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588) ~[spring-beans-4.1.6.RELEASE.jar:4.1.6.RELEASE] ... 158 common frames omitted Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [schema.sql]: CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1; nested exception is java.sql.SQLSyntaxErrorException: object name already exists: VOUCHER_ID_SEQ in statement [CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1] at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:474) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:208) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.initDatabase(EmbeddedDatabaseFactory.java:159) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.getDatabase(EmbeddedDatabaseFactory.java:132) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder.build(EmbeddedDatabaseBuilder.java:251) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at com.siemens.ott.TestDatabaseConfig.dataSource(TestDatabaseConfig.java:46) ~[test-classes/:na] at com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGLIB$$6a150586.CGLIB$dataSource$1() ~[spring-core-4.1.6.RELEASE.jar:na] at com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGLIB$$6a150586$$FastClassBySpringCGLIB$$125e7bce.invoke() ~[spring-core-4.1.6.RELEASE.jar:na] at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-4.1.6.RELEASE.jar:4.1.6.RELEASE] at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:309) ~[spring-context-4.1.6.RELEASE.jar:4.1.6.RELEASE] at com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGLIB$$6a150586.dataSource() ~[spring-core-4.1.6.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_45] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_45] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_45] at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_45] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162) ~[spring-beans-4.1.6.RELEASE.jar:4.1.6.RELEASE] ... 159 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: object name already exists: VOUCHER_ID_SEQ in statement [CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:459) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] ... 175 common frames omitted Caused by: org.hsqldb.HsqlException: object name already exists: VOUCHER_ID_SEQ

Answer 1

For anybody else that might get into this situation, the problem was in kind of unexpected place: loader = AnnotationConfigWebContextLoader.

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(classes = { TestConfiguration.class }, loader = AnnotationConfigWebContextLoader.class)
@ActiveProfiles("test")
@TestExecutionListeners(listeners = { DependencyInjectionTestExecutionListener.class })

and simply removing the culprit solved all issues. Now schema.sql is executed only once.