Integration test fails when executing "mvn test" in a web project with JPA that has a schema.sql file

[matsev]

When having a web-based project with a JPA based repository, @WebAppConfiguration integration test fails when executing mvn clean test when there is a schema.sql file present , despite setting spring.jpa.hibernate.ddl-auto= as described in chapter 63.3 Initialize a database using Spring JDBC:

If you want to use the schema.sql initialization in a JPA app (with Hibernate) then ddl-auto=create-drop will lead to errors if Hibernate tries to create the same tables. To avoid those errors set ddl-auto explicitly to "" (preferable) or "none".

---

Step by step:

1. Clone the reference project from GitHub: https://github.com/matsev/springboot.sql.jpa.bug
2. Execute mvn clean test

Expected result:

• All tests should pass

Actual result:

BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration' 
[...]
BeanCreationException: Could not autowire field: private javax.sql.DataSource org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.dataSource;
[...]
BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration$NonEmbeddedConfiguration.class]: Initialization of bean failed;
[...]
BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed;
[...]
ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource URL [[...]/target/classes/schema.sql]: CREATE TABLE person ( id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255) ); nested exception is java.sql.SQLSyntaxErrorException: object name already exists: PERSON in statement [CREATE TABLE person ( id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255) )]

---

Comments:

• The test pass the schema.sql file is deleted and the spring.jpa.hibernate.ddl-auto= is removed from the application.properties file
• The test pass if executed from within IntelliJ
• The test pass if @WebAppConfiguration is removed from the PersonControllerTest class.
• Changing the value of spring.jpa.hibernate.ddl-auto to none does not prevent the test from failing

Environment:

• Spring Boot 1.1.5.RELEASE
• Maven 3.2.3
• Java 1.8.0_20

[wilkinsona]

Thanks for the detailed bug report. I've seen a couple of things that don't match 100% with your comments:

• The tests continue to fail if I remove @WebAppConfiguration
• The tests fail in my IDE if I run them at the same time. They pass when I run ApplicationTests or PersonControllerTest individually

The problem's due to schema.sql being executed twice, once for each test class, against the same database. It fails the second time as the tables already exist.

[wilkinsona]

A workaround is to set spring.datasource.continueOnError=true in application.properties.

[matsev]

@wilkinsona You are correct, when executing both tests from inside the IDE, PersonControllerTest fails (but it passes if I execute it alone).

I have also verified that continueOnError=true make the tests pass.

However, I still experience that both tests passes if I remove the @WebAppConfiguration annotation, regardless if executed from the IDE or using Maven from the command line (but perhaps it is not be relevant to the issue).

[dsyer]

@DirtiesContext would probably be a workaround as well. I think the preferred way of dealing with multiple contexts in a test suite is to set them up with unique database URLs (e.g. run each unique application context in a profile that picks out a url, or maybe use spring.datasource.url=jdbc:hsqldb:mem:${something)that_varies}).

[matsev]

@dsyer @DirtiesContext does not make the test pass (despite being added to both tests).

Personally, I do not think that creating unique application contexts (nor @DirtiesContext) is a desirable solution as it may significantly increase the turnaround time for the test suite, especially for large projects.

Moreover, one would not expect the spring.datasource.url configuration to be necessary, since one of the tests is the contextLoads() test created when generating a project from http://start.spring.io , i.e. it does not contain anything that suggests that a database is involved:

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
public class ApplicationTests {

    @Test
    public void contextLoads() {
    }
}

[dsyer]

You might not expect it, but since your test creates an application context it is going to initialize the database. Once you start doing heavy integration testing with a database you are going to have to deal with this problem (i.e. it's nothing really that we can help you with in Spring Boot). I recommend using profiles and being very careful not to create too many unique application contexts. Scrutinise each test that you end up with and see if it could be done with a shared application context, or one that is cheaper to start.

[wilkinsona]

You might also want to consider using a database migration tool such as Flyway. It's intelligent enough to only make a schema change if it hasn't already been applied to the database.

[wilkinsona]

The sample the reproduces the problem that this issue is about doesn't use @Sql so it's not clear that you actually do have the same problem.

If you do, then we've already investigated and offered three different solutions:

• Use spring-datasource.continueOnError (this is a solution if you can't write an idempotent script)
• Use Flyway or Liquibase
• Use profiles to avoid clashes

Unfortunately, re-opening the issue isn't going to change that.

[savinov]

I've found the source of my problem, made sample to reproduce and created new issue: #7416

Problem is the same: schema.sql executes twice, but the source of the problem maybe different, so take a look to the new issue.

[sunkuet02]

I am facing this problem as of now. Is there any work around solutions ?

[snicoll]

@sunkuet02 this issue is four years old so I am not sure what exact problem you're facing. If you think you've found a bug in Spring Boot, please create a new issue with a small sample we can run ourselves to reproduce the problem.