Embedded DataSources are not dropped when context is destroyed.

[jgdenoo]

For my tests i tried to use the new embedded-database feature form the jdbc namespace, but with Derby and H2 i ran in to some problem.
It seems that when i specify the following:

       <jdbc:embedded-database id="dataSource" type="HSQL">
		<jdbc:script location="classpath:schema.sql"/>
		<jdbc:script location="classpath:test-data.sql"/>
	</jdbc:embedded-database>

everytime a tests is run a new in memory database is created, (this is exactly what i want and expected.) But when i use this configuration with type=DERBY i run in to trouble.
If i run 1 testcase(consisting of multiple tests) there is no problem, but when i run multiple testcases only the first one will succeed. This is caused by SQL errors telling me the tables in the schema.sql are already present.
It seems that the in-memory derby database from the first test still exists and that spring will just reconnect to that database.

So my question is: Is this meant to be like this? Because you would not expect a different behavior by just choosing another database.
and are there any properties you can configure to make sure that you connect to a new empty database?

cheers
Job

[Sam Brannen]

Hi Job,

If i run 1 testcase(consisting of multiple tests) there is no problem, but when i run multiple testcases only the first one will succeed. This is caused by SQL errors telling me the tables in the schema.sql are already present.

As a quick work-around, you could simply add drop statements to your schema.sql script, for example:

DROP TABLE xxx if exists;
...

It seems that the in-memory derby database from the first test still exists and that spring will just reconnect to that database.

How are you configuring your tests?

Are you using the Spring TestContext Framework (i.e., @Configuration, SpringJUnit4ClassRunner, etc.)?

If so, you could try using @DirtiesContext at the class level to force your ApplicationContext to be closed after each test class or test method (see the classMode attribute I added to @DirtiesContext in Spring 3.0).

Is this meant to be like this? Because you would not expect a different behavior by just choosing another database.
and are there any properties you can configure to make sure that you connect to a new empty database?

AFAIK, no, there are no additional properties. It may be an issue with the implementation of DerbyEmbeddedDatabaseConfigurer or simply an issue with Derby's support for in-memory databases. The following is a note within the source code of DerbyEmbeddedDatabaseConfigurer.purgeDatabase():

// TODO: update this code once Derby adds a proper way to remove an in-memory db
// (see http://wiki.apache.org/db-derby/InMemoryBackEndPrimer for details)

Would you be willing to create a simplified version of your test case which reproduces the issue you're seeing? If so, please create a JIRA issue and attach your example project to it. Then we'll be able to take a closer look.

Thanks,

Sam

[jgdenoo]

Hi Sam,
thanx for the quick reply,

i will create a jira issue with an isolated test case as soon as possible.
I'm using the spring test-framework(annotations) and dirties-context don't fix the problem. The spring context is correctly destroyed, it is just derby and H2 that decide to stay in memory.
On the derby website i'm unable to find a command to properly shutdown an in-memory database.

As for the drop table if exists, i already tried, but derby does not support the conditional drop table statements.

i will update the thread as soon as the i created the jira-issue

[Paste]

is your problem solve?... i am also facing these problem kindly reply

[jgdenoo]

For the conditional drop table in derby you can use the following:

1. create this class in some package.

public class DerbyDropTable {
	public static void dropTable(String schema, String table) {
		try {
			Connection conn = DriverManager.getConnection("jdbc:default:connection");
			PreparedStatement ps = conn.prepareStatement("drop table " + schema + "." + table);
			ps.execute();
			ps.close();
		} catch (SQLException e) {
		}
	}
}

Make sure the class is on your classpath.

2. create a procedure in derby that call the created method: (should be created in your scripts before the drop calls are made)

create procedure DROP_TABLE
    ( schemaName varchar( 128 ), tableName varchar( 128 ) )
    parameter style java
    modifies sql data
    language java
    external name 'somepackage.DerbyDropTable.dropTable'
;

3. call the procedure for every table you want to drop:

call DROP_TABLE( 'SA', 'tablename' );

This works fine, it would be better if derby or spring had a way to shutdown the database, so i still will create the jira issue, but at least it offers a workaround.

cheers
Job de Noo

[Sam Brannen]

FYI: Spring does actually attempt to shutdown and purge the Derby database when the ApplicationContext is closed. I alluded to that earlier, but I suppose I could have been a bit clearer in my explanation.

For example, in DerbyEmbeddedDatabaseConfigurer, you can see the following:

public void shutdown(DataSource dataSource, String databaseName) {
	try {
		new EmbeddedDriver().connect(String.format(URL_TEMPLATE, databaseName, "shutdown=true"), new Properties());
	} catch (SQLException ex) {
		if (SHUTDOWN_CODE.equals(ex.getSQLState())) {
			purgeDatabase(databaseName);
		} else {
			logger.warn("Could not shutdown in-memory Derby database", ex);
		}
	}
}

/**
 * Purge the in-memory database, to prevent it from hanging around after
 * being shut down.
 */
private void purgeDatabase(String databaseName) {
	// TODO: update this code once Derby adds a proper way to remove an in-memory db
	// (see http://wiki.apache.org/db-derby/InMemoryBackEndPrimer for details)
	try {
		VFMemoryStorageFactory.purgeDatabase(new File(databaseName).getCanonicalPath());
	} catch (IOException ex) {
		logger.warn("Could not purge in-memory Derby database", ex);
	}
}

So perhaps the shutdown logic is incorrect.

Could you please post a link to the JIRA issue here in the forum once you've created it?

Thanks,

Sam

p.s. that's a pretty clever work-around you found with the custom procedure.

[jgdenoo]

He Sam,

sorry i completely forgot about this issue.
I rediscovered it when i ran into it again in another application today.
After diving a bit further into it, i established that it is not a problem in the embeddedDatabase tag at all, but rather an issue of cached application context-s, that i didn't know about.

Between 2 test classes that load an application-context this context is cached, and so not closed.
In my situation this leads too 2 possible problems with the embedded database tag;

1. i have more than one context that will create this embedded database, so it keeps one application context cached that contains an embedded derby database, and in the next tests it tries to load another derby database and fails.

1. Even if a tests loads the same context, but also has one different context configured in the contextlocation, the TestContextManager will see it as a new context and so tries to load a new context with a new EmbeddedDataSource, and fails

The only option i see is to make sure to use @DirtiesContext(classMode =ClassMode.AFTER_CLASS), but this is a bit easy to forget ;-).

Maybe some warning should be given in the documentation of the embeddedDatabase or test context, or maybe i just didn't read the documentation well enough ;-)

cheers
Job de Noo

[wgorder]

I ran into this today. Solved it by configuring the sure fire plugin to always fork. This may slow test execution down slightly but it solved the problem for me.

		<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-surefire-plugin</artifactId>
				<version>${maven.surefire.plugin.version}</version>
				<configuration>
				 	<forkMode>always</forkMode>
					<useSystemClassLoader>true</useSystemClassLoader>
				</configuration>
		</plugin>

[wgorder]

Ok so my last post helped with my maven builds but I still got errors in Eclipse. This looks to be an issue in Derby as Spring from what I can see is doing every thing it should. I just worked around it by creating a separate DDL that just drops the schema (this throws an exception if the schema does not exist). In Derby they don't support the 'if exists' syntax so I did this kludge. With this in place I was able to take out the fork-join stuff I previously mentioned.

       @Bean
        public DataSource dataSource()
        {
            try
            {
                new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.DERBY).setName("myDb")
                    .addScript("classpath:/myDb/ddl/myDb-drop.ddl").build();
            }
            catch (Exception e)
            {
                /*
                 * We only do this because embedded derby does not like to clean itself up
                 * occasionally causing tests to fail. This is a bug in derby not Spring.
                 */
            }

            return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.DERBY)
                .setName("myDb").addScript("classpath:/myDb/ddl/myDb.ddl").build();
        }

[altfatterz]

with the new surefire config it was solved with setting the reuseForks=false, executes each test class in its own JVM process, one after another.

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