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

Comment From: 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.

Comment From: wilkinsona

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

Comment From: 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).

Comment From: 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}).

Comment From: 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() {
    }
}

Comment From: 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.

Comment From: 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.

Comment From: 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.

Comment From: savinov

I've found the source of my problem, made sample to reproduce and created new issue: https://github.com/spring-projects/spring-boot/issues/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.

Comment From: sunkuet02

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

Comment From: 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.