Introduce support for generating unique names for embedded databases [SPR-8849]

[spring-projects-issues]

Julian Sareyka opened SPR-8849 and commented

Hi,
I use an embedded database (HSQL) in my integration test suite. Its a maven project with TestNG.

<jdbc:embedded-database id="mirDataSource">
	<jdbc:script location="classpath:db/schema.sql" />
	<jdbc:script location="classpath:db/tables_hsqldb.sql" />
	<jdbc:script location="classpath:db/package_verweildauer.sql" />
	<jdbc:script location="classpath:db/data.sql" />
</jdbc:embedded-database>

I have one default context configuration (which includes the database) and several extensions:
for instance:

@ContextConfiguration("/spring/default.xml")
public class PodcastControllerTest extends AbstractTestNGSpringContextTests {...}

// and

@ContextConfiguration({ "/spring/components/amq-broker.xml", "/spring/default.xml" })
public class SophoraAvDocumentTest extends AbstractTestNGSpringContextTests {...}

When executing the tests with maven (maven-surefire), all the tests with the first configuration reuse the same context (due to context caching). The first test-setup with the second configuration should create a fresh application context, but it fails with Database-Errors indicating, that the objects to create already exist. (I spent quite some time debugging the maven process).

Here is the maven surefire config:

<plugin>
	<groupId>org.apache.maven.plugins</groupId>
	<artifactId>maven-surefire-plugin</artifactId>
	<configuration>
		<threadCount>1</threadCount>
		<excludedGroups>slow,local</excludedGroups>
		<argLine>-Xmx1024m</argLine>
	</configuration>
</plugin>

My theory is, that both contexts connect to the same database instance, because they are in the same vm and the name of the db-instance is the same:
EmbeddedDatabaseFactory.databaseName = "testdb";

I did not find a way to assign the name with the jdbc:embedded-database-Tag.

So I created a custom EmbeddedDatabaseBuilder which assigns a random name (UUID) to every new instance:

EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL).setName(UUID.randomUUID().toString());
for (String sqlResource : this.scripts) {
    builder.addScript(sqlResource);
}
this.embeddedDatabase = builder.build();

With this one the errors are gone. Would it be a solution to add a configuration-option to jdbc:embedded-database for random db-instance names?

---

Affects: 3.1 GA

Issue Links:

• Introduce database-name attribute in <jdbc:embedded-database /> [SPR-12835] #17432 Introduce database-name attribute in <jdbc:embedded-database /> ("depends on")
• Document support for generating unique names for embedded databases in the reference manual [SPR-12839] #17437 Document support for generating unique names for embedded databases in the reference manual ("is depended on by")
• Embedded database connection closed by shutdown() method [SPR-11372] #15999 Embedded database connection closed by shutdown() method
• Using multiple declarations of jdbc:embedded-database creates only one H2 instance per JVM [SPR-8920] #13560 Using multiple declarations of jdbc:embedded-database creates only one H2 instance per JVM
• jdbc:datasource lacks 'name' attribute or related mechanism [SPR-7604] #12260 jdbc:datasource lacks 'name' attribute or related mechanism

6 votes, 7 watchers

[spring-projects-issues]

Chris Beams commented

Hi Julian,

I have a couple ideas here, but it would be good to see the failure in action before making too many assumptions. Would you be willing to put together a small reproduction project following the instructions here? https://github.com/SpringSource/spring-framework-issues#readme

Thanks,

Chris

[spring-projects-issues]

Mickaël LEDUQUE commented

I'm not the original reporter but I tried to provide a test project for this issue. If you don't see it, tell me.

[spring-projects-issues]

Rossen Stoyanchev commented

spring-attic/spring-framework-issues#16

[spring-projects-issues]

Sam Brannen commented

Hi guys,

@Mickaël, thanks for pull request!

Based on your work and Julian's suggested programmatic work-around, I committed a test suite that both demonstrates the problem and a configuration-based work-around using a SpEL expression.

See commit 04a682729020af09c70ebbf6a210f42b8cc1c36b for details.

Reproduce claims raised in SPR-8849

This commit introduces a test suite (Spr8849Tests) that demonstrates
the claims made in SPR-8849.

Specifically, if <jdbc:embedded-database id="xyz" /> is used to create
an embedded HSQL database in an XML configuration file and that
configuration file is imported in different sets of configuration files
that are used to load ApplicationContexts for different integration
tests, the embedded database will be initialized multiple times using
any nested <jdbc:script /> elements. If such a script is used to create
a table, for example, subsequent attempts to initialize the database
named "xyz" will fail since an embedded database named "xyz" already
exists in the JVM.

As a work-around, this test suite uses a SpEL expression to generate a
random string for each embedded database instance:

  id="#{T(java.util.UUID).randomUUID().toString()}"

See the Javadoc in Spr8849Tests for further information.

Issue: SPR-8849

- Sam

[spring-projects-issues]

Torsten Krah commented

But if those context does use e.g. DataSourceTransactionManager which does need a explicit reference to the above dataSource, how to setup this?

[spring-projects-issues]

Sam Brannen commented

Torsten Krah, if you generate random IDs in XML configuration, then there won't be a way to know the random ID. So in such cases, you won't be able to refer to the DataSource bean by ID.

However, if you use Java Config (i.e., an @Bean method to create your DataSource programmatically), your bean could retain a constant name (e.g., dataSource). In that scenario you would create the embedded database (and thus the DataSource) programmatically via the EmbeddedDatabaseBuilder.

Regards,

Sam

[spring-projects-issues]

Torsten Krah commented

So wouldn't it be better to just have 2 additional properties - the dbName for the actual database (so its not the "id" used for the name so i can still reference it by name in the context.xml and i would be able to use the SpEL expression from above - if those optional dbName is not given, if can fallback to the id to be backward compatible) and another property like customDriverProperties which are added 1:1 to the connection string from the database builder, to support custom stuff (e.g. H2 pgsql mode or MVCC support options)?

[spring-projects-issues]

Sam Brannen commented

Note that EmbeddedDatabaseBuilder already provides support for setting the name.

Providing an explicit attribute via <jdbc:embedded-database /> for setting the name would certainly be a welcome improvement for XML-based configuration; however, the need for having the name auto-generated still exists. Thus, introducing such automatic name generation support into both the Java builder API and XML namespace would remove the burden of coming up with such a SpEL expression from the developer.

In summary, I would recommend the following changes:

1. Introduce dbName attribute in <jdbc:embedded-database />, analogous to EmbeddedDatabaseBuilder.setName().
2. Introduce a boolean flag for automatically generating a random, unique name for the embedded database.
   • this must be configurable via the Java builder API and the XML namespace.

Regards,

Sam

[spring-projects-issues]

Sam Brannen commented

Regarding support for a customDriverProperties property, that is an unrelated topic.

If you would like to see that implemented, please open a new JIRA issue to suggest it formally.

Thanks,

Sam

[spring-projects-issues]

Torsten Krah commented

Thanks Sam, regarding your suggestions this sounds really good to me having it configurable at the jdbc namespace and at the builder API.
For the customDriverProperties i opened #17060, sorry for hijacking this one.

regards Torsten.

[spring-projects-issues]

Gregory bonk commented

This looks like my issue where the TestContext cache tries to re-create beans of embedded databases.

http://stackoverflow.com/questions/28157842/multiple-embedded-hsqldb-databases-in-junit-errors-during-build

[spring-projects-issues]

Sam Brannen commented

FYI: the ground work for this issue has just been completed in conjunction with #17431 and #17432.

[spring-projects-issues]

Sam Brannen commented

The previously proposed work-around has been updated as described in GitHub commit ab771df:

Refactor tests to use the new database-name attribute

This commit refactors the XML configuration used by the tests in the
Spr8849Tests test suite so that a unique database name is always
generated (via the new 'database-name' attribute that was introduced in
#17432) while reusing the same bean name (i.e., 'dataSource').

This is a much more robust alternative to the previous work-around
since the name of the DataSource does not randomly change across
application contexts, thus allowing proper autowiring by name and bean
referencing within XML configuration.

Consequently, the following viable solution now exists for setting a unique name for each embedded database:

<jdbc:embedded-database id="dataSource" database-name="#{T(java.util.UUID).randomUUID().toString()}">
	<jdbc:script location="..." />
</jdbc:embedded-database>

The above solution is, however, still considered a manual work-around until this issue (#13491) has been resolved.

[spring-projects-issues]

Sam Brannen commented

Completed as described in GitHub commit c0fbe0a:

Support unique names for embedded databases

Development teams often encounter errors with embedded databases if
their test suite inadvertently attempts to recreate additional
instances of the same database. This can happen quite easily if an XML
configuration file or @Configuration class is responsible for creating
an embedded database and the corresponding configuration is then reused
across multiple testing scenarios within the same test suite (i.e.,
within the same JVM process) -- for example, integration tests against
embedded databases whose ApplicationContext configuration only differs
with regard to which bean definition profiles are active.

The root cause of such errors is the fact that Spring's
EmbeddedDatabaseFactory (used internally by both the
jdbc:embedded-database XML namespace element and the
EmbeddedDatabaseBuilder for Java Config) will set the name of the
embedded database to "testdb" if not otherwise specified. For the case
of jdbc:embedded-database, the embedded database is typically
assigned a name equal to the bean's id. Thus, subsequent attempts to
create an embedded database will not result in a new database. Instead,
the same JDBC connection URL will be reused, and attempts to create a
new embedded database will actually point to an existing embedded
database created from the same configuration.

This commit addresses this common issue by introducing support for
generating unique names for embedded databases. This support can be
enabled via:

• EmbeddedDatabaseFactory.setGenerateUniqueDatabaseName()
• EmbeddedDatabaseBuilder.generateUniqueName()
• <jdbc:embedded-database generate-name="true" ... >

[spring-projects-issues]

Sam Brannen commented

The proper solution to the problem originally raised by this JIRA issue will be available in Spring Framework 4.2 RC1 as follows:

<jdbc:embedded-database id="mirDataSource" generate-name="true">
    <jdbc:script location="classpath:db/schema.sql" />
    <jdbc:script location="classpath:db/tables_hsqldb.sql" />
    <jdbc:script location="classpath:db/package_verweildauer.sql" />
    <jdbc:script location="classpath:db/data.sql" />
</jdbc:embedded-database>

Thus, all that's required to get it working as expected is the addition of generate-name="true". :)

[spring-projects-issues]

Sam Brannen commented

Out of curiosity, how do people feel about the generate-name attribute?

Is it not descriptive enough, too short?

Would something longer be a better choice?

• generate-unique-name
• generate-unique-db-name
• generate-unique-database-name

Thanks in advance for feedback!

- Sam

p.s. please keep in mind that #17432 introduced a new database-name attribute.

[spring-projects-issues]

Gregory bonk commented

Why couldn't the database just be reused instead of generating unique names and duplicate databases?

 List existingDataBases = org.hsqldb.DatabaseManager.getDatabaseURIs();

    boolean isExisting = false;
    String localDBName = StringUtils.lowerCase(this.databaseName);

    for (Object object : existingDataBases) {
        if (object.toString().contains(localDBName)) {
            isExisting = true;
            break;
        }
    }

    // Now populate the database
    if (!isExisting && this.databasePopulator != null) {

[spring-projects-issues]

Sam Brannen commented

Why couldn't the database just be reused instead of generating unique names and duplicate databases?

The unique name generation is a feature that you have to explicitly opt in for.

The default behavior is that the database will be reused. Nothing has changed in this regard. The only possible issue is if you have scripts that are used to populate the database. So in that sense, your proposal warrants consideration. Please open a new JIRA issue to track this proposal on its own.

Thanks,

Sam