JUnit test starts before H2´s RUNSCRIPT finishes

Question

We have a Java project using Spring and MAVEN. In this project, we are using H2 database in memory to perform several tests on our DAO/Repository layer.

After a few tests run, BUT NOT ALWAYS, we got the following error:

org.h2.jdbc.JdbcSQLException: Table "WEATHER" not found; SQL statement:

If you perform the JUnit test alone, it will NEVER fail. There is no pattern on when the error will appear.

I suspect that the RUNSCRIPT statement below on the URL connection didn't finish and when the unit test begin, i.e, the execution is performed asynchronously.

Here is the connection Statement:

String jdbcUrl = "jdbc:h2:mem:WeatherAPI;MODE=MySQL;DB_CLOSE_ON_EXIT=TRUE;TRACE_LEVEL_SYSTEM_OUT=1;INIT=runscript from 'src/test/resources/sql/weatherapi.sql'"

The idea is that the database will be reset at each test.

Here is the snippet of the code to get a DataSource object:

private static java.sql.DataSource ds = null;

public static DataSource getDs() {
    if(this.ds==null) {
        try {
            this.ds = manualCreateDataSource();
        } catch (Exception e) {
            logger.error("Could not initialize Datasource", e);
            throw new RuntimeException("Could not initialize Datasource");
        }
    }

    return this.ds;
}

public static DataSource manualCreateDataSource() {

    String driverClass = "org.h2.jdbcx.JdbcDataSource";
    String jdbcUrl = "jdbc:h2:mem:WeatherAPI;MODE=MySQL;DB_CLOSE_ON_EXIT=TRUE;TRACE_LEVEL_SYSTEM_OUT=1;INIT=runscript from 'src/test/resources/sql/weatherapi.sql'";
    int maxPoolSize = 20;
    int minPoolSize = 5;
    int unreturnedConnectionTimeout = 10;
    int idleConnectionTestPeriod = 200;
    int maxIdleTime = 1000;
    int maxStatementsPerConnection = 5;

    ComboPooledDataSource ds = new ComboPooledDataSource();
    ds.setJdbcUrl(jdbcUrl);
    ds.setMaxPoolSize(maxPoolSize);
    ds.setMinPoolSize(minPoolSize);
    ds.setInitialPoolSize(minPoolSize);
    ds.setUnreturnedConnectionTimeout(unreturnedConnectionTimeout);
    ds.setIdleConnectionTestPeriod(idleConnectionTestPeriod);
    ds.setMaxIdleTime(maxIdleTime);
    ds.setMaxStatementsPerConnection(maxStatementsPerConnection);

    try {
        ds.setDriverClass(driverClass);
    } catch (PropertyVetoException e) {
        logger.error("error setting driver class", e);
    }

    return ds;
}

And here a snippet for the weatherapi.sql script:

CREATE SCHEMA IF NOT EXISTS `WeatherAPI`;
USE `WeatherAPI`;

DROP TABLE IF EXISTS `Weather`;
CREATE TABLE IF NOT EXISTS `Weather` (
    id int(11) NOT NULL AUTO_INCREMENT,
    location char(3) NOT NULL,
    period varchar(8) NOT NULL,
    duration char DEFAULT NULL,
    payload TEXT,
    created timestamp NULL DEFAULT NULL,
    lastmodified timestamp NULL DEFAULT NULL,
    version int(11) NOT NULL,  PRIMARY KEY (id)
);

Answer 1

I suspect this is a race condition. According to the documentation the script is executed for every single client that connects to the database. Since you are always dropping the Weather table before recreating it, it might happen that when a test A is already running and a second client B connects to the database, the table gets drop right under the nose of A. B might be another test that runs in parallel or a second thread in the same test.

If that is the case, you might try to use the RunScript tool in your manualCreateDataSource() method instead of the INIT parameter in the JDBC connection URL:

String jdbcUrl = "jdbc:h2:mem:WeatherAPI;MODE=MySQL;DB_CLOSE_ON_EXIT=TRUE;TRACE_LEVEL_SYSTEM_OUT=1;"
RunScript.execute(jdbcUrl, sa, "", "src/test/resources/sql/weatherapi.sql", null, false);

Additionally, you need to make getDs() thread-safe, by adding synchronized to it or better yet, by initializing the instance variable ds statically:

private static java.sql.DataSource ds = manualCreateDataSource();

public static DataSource getDs() {
    return ds;
}

Answer 2

The answer from hzpz in fact help me see what was happening: race conditions. The question I entered did not specify that I am using maven (I apologize for that), and I found out that maven surefire plugin was forking the tests, thus the race condition in fact appeared. I decide to turn off forking and configured maven-surefire plugin in this way:

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-surefire-plugin</artifactId>
    <configuration>
        <forkCount>1</forkCount>
        <reuseForks>false</reuseForks>
    </configuration>
</plugin>

There are several questions regarding the forking, but none relating H2's RUNSCRIPT with the race conditions.

Here more details on surefire plugin:

Maven Surefire Plugin - Class loading and forking